Hortonwork hdp3.1プラットフォーム構成hplsqlおよびテストの使用

一、hplsqlインストール構成

Hortonwork hdp3.0プラットフォームに付属するhive 3はhplsqlを統合しており、インストールする必要はありませんが、手動でプロファイルhplsql-siteを新規作成する必要があります.xml、対応するhive-hpsql-xxxを更新します.jarファイル(xxxはバージョン番号).

プロファイルのサンプル

主にhplsqlのデフォルト接続列hplsqlを修正する.conn.default,hiveの接続構成情報hplsql.conn.hive 2 conn、そして後でテストするmysqlデータベースの接続情報hplsql.conn.mysqlconn.

  hplsql.conn.default
  hive2conn
  The default connection profile


  hplsql.conn.hiveconn
  org.apache.hive.jdbc.HiveDriver;jdbc:hive2://
  HiveServer2 JDBC connection (embedded mode)


  hplsql.conn.init.hiveconn
  
  
  Statements for execute after connection to the database


  hplsql.conn.convert.hiveconn
  true
  Convert SQL statements before execution


  hplsql.conn.hive1conn
  org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://
  Hive embedded JDBC (not requiring HiveServer)


  hplsql.conn.hive2conn
  org.apache.hive.jdbc.HiveDriver;jdbc:hive2://hd4:10000;hive;hive
  HiveServer2 JDBC connection


  hplsql.conn.init.hive2conn
  
  
  Statements for execute after connection to the database


  hplsql.conn.convert.hive2conn
  true
  Convert SQL statements before execution


  hplsql.conn.db2conn
  com.ibm.db2.jcc.DB2Driver;jdbc:db2://localhost:50001/dbname;user;password
  IBM DB2 connection


  hplsql.conn.tdconn
  com.teradata.jdbc.TeraDriver;jdbc:teradata://localhost/database=dbname,logmech=ldap;user;password
  Teradata connection


  hplsql.conn.mysqlconn
  com.mysql.jdbc.Driver;jdbc:mysql://hd5/cpdds_etl;root;root123
  MySQL connection


  hplsql.dual.table
  default.dual
  Single row, single column table for internal operations


  hplsql.insert.values
  native
  How to execute INSERT VALUES statement: native (default) and select


  hplsql.onerror
  exception
  Error handling behavior: exception (default), seterror and stop


  hplsql.temp.tables
  native
  Temporary tables: native (default) and managed


  hplsql.temp.tables.schema
  
  Schema for managed temporary tables


  hplsql.temp.tables.location
  /tmp/plhql
  LOcation for managed temporary tables in HDFS

hive-hpsql-xxxを更新します。JArファイル

jar uf hive-hplsql-3.1.0.3.0.1.0-187.jar hplsql-site.xml

 
 

二、hplsqlテスト

確立関数

--Create a function without parameters:
CREATE FUNCTION hello()
 RETURNS STRING
BEGIN
 RETURN 'Hello, world';
END;
 
-- Call the function
PRINT hello();


--Create a function with a parameter:
CREATE FUNCTION hello2(text STRING)
 RETURNS STRING
BEGIN
 RETURN 'Hello, ' || text || '!';
END;
 
-- Call the function
PRINT hello2('world');

ストレージプロセス

を確立する.

CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
 SET result = 'Hello, ' || name || '!';
END;
 
-- Now call the procedure and print the results
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;
 
Result:
--
Hello, world!

実行ファイル

を作成する

INCLUDE set_message.sql
 
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;
 
Result:
--
Hello, world!

shell実行によりsql結果

を得る

MDATE=$(hplsql -e "NVL(MIN_PARTITION_DATE(sales, local_dt, code='A'), '1970-01-01')")
START=$(hplsql -e 'CURRENT_DATE - 1')

sqlファイル

を実行する

Hplsql –f script.sql
--  –main  ， script.sql 
Hplsql –f script.sql –main funcation_name

テーブルマッピング、mysqlのログテーブル

にログを記録
まずmysqlをlibディレクトリにコピーします.

cp /usr/share/java/mysql-connector-java.jar .

 

——logtomysql.sql 
MAP OBJECT log TO cpdds_etl.hive_task_log AT mysqlconn;
 
DECLARE
start_time VARCHAR(20);
end_time VARCHAR(20);
return_code INT;
BEGIN
start_time = SYSDATE || '';
SELECT COUNT(1) FROM ods_test.tb_mail;
return_code = SQLCODE;
end_time = SYSDATE || '';
INSERT INTO log (`proc_name`,`start_time`,`end_time`,`return_code`)
VALUES(start_time,end_time,return_code);
EXCEPTION WHEN OTHERS THEN
return_code = SQLCODE;
end_time = SYSDATE || '';
INSERT INTO log (`proc_name`,`start_time`,`end_time`,`return_code`)
VALUES('testlog',start_time,end_time,return_code);
DBMS_OUTPUT.PUT_LINE('SQL execute error,return code : ' || return_code);
END

—— 
hplsql -f logtomysql.sql

 

カーソル

を使用

FOR item IN (
    SELECT v_mail_code, d_clct_date  
    FROM ods_test.tb_mail
    ORDER BY v_mail_code)
LOOP
  DBMS_OUTPUT.PUT_LINE('v_mail_code = ' || item.v_mail_code || ', d_clct_date = ' || item.d_clct_date);
END LOOP;

三、参考文書

hplsqlの詳細については、http://www.hplsql.org/