Configure HIVE HPLSQL

[#1]

Assumption - HDP 2.5.3 and above versions and kerberized cluster.

Create a hplsql-site.xml as per the below template.

<configuration>
<property>
<name>hplsql.conn.default</name>
<value>hive2conn</value>
<description>The default connection profile</description>
</property>
<property>
<name>hplsql.conn.hiveconn</name>
<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://</value>
<description>HiveServer2 JDBC connection (embedded mode)</description>
</property>
<property>
<name>hplsql.conn.init.hiveconn</name>
<value>
set hive.execution.engine=mr;
use default;
</value>
<description>Statements for execute after connection to the database</description>
</property>
<property>
<name>hplsql.conn.convert.hiveconn</name><
<value>true</value>
<description>Convert SQL statements before execution</description><
</property>
<property>
<name>hplsql.conn.hive1conn</name>
<value>org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://</value>
<description>Hive embedded JDBC (not requiring HiveServer)</description>
</property>
<property>
<name>hplsql.conn.hive2conn</name>
<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://node1.field.hortonworks.com:10500/default;principal=hive/node1.field.hortonworks.com@REALM</value>
<description>HiveServer2 JDBC connection</description>
</property>
<property>
<name>hplsql.conn.init.hive2conn</name>
<value>
set hive.execution.engine=tez;
use default;
</value>
<description>Statements for execute after connection to the database</description>
</property>
<property>
<name>hplsql.conn.convert.hive2conn</name>
<value>true</value>
<description>Convert SQL statements before execution</description>
</property>
<property>
<name>hplsql.conn.db2conn</name>
<value>com.ibm.db2.jcc.DB2Driver;jdbc:db2://localhost:50001/dbname;user;password</value>
<description>IBM DB2 connection</description>
</property>
<property>
<name>hplsql.conn.tdconn</name>
<value>com.teradata.jdbc.TeraDriver;jdbc:teradata://localhost/database=dbname,logmech=ldap;user;password</value>
<description>Teradata connection</description>
</property>
<property>
<name>hplsql.conn.mysqlconn</name>
<value>com.mysql.jdbc.Driver;jdbc:mysql://localhost/test;user;password</value>
<description>MySQL connection</description>
</property>
<property>
<name>hplsql.dual.table</name>
<value>default.dual</value>
<description>Single row, single column table for internal operations</description>
</property>
<property>
<name>hplsql.insert.values</name>
<value>native</value>
<description>How to execute INSERT VALUES statement: native (default) and select</description>
</property>
<property>
<name>hplsql.onerror</name>
<value>exception</value>
<description>Error handling behavior: exception (default), seterror and stop</description>
</property>
<property>
<name>hplsql.temp.tables</name>
<value>native</value>
<description>Temporary tables: native (default) and managed</description>
</property>
<property>
<name>hplsql.temp.tables.schema</name>
<value></value>
<description>Schema for managed temporary tables</description>
</property>
<property>
<name>hplsql.temp.tables.location</name>
<value>/tmp/plhql</value>
<description>LOcation for managed temporary tables in HDFS</description>
</property>
</configuration>
	

Modify the LLAP hostname and the hive Principal based on the cluster environment in the following section

Note: This is a kerberized cluster

<property>
  <name>hplsql.conn.hive2conn</name>
  <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://<<LLAP_HOSTNAME>>:10500/default;principal=hive/<<LLAPHOSTNAME>>@<<KERBEROS_REALM>></value>
  <description>HiveServer2 JDBC connection</description>
</property>
<property>
  <name>hplsql.conn.init.hive2conn</name>
  <value>
     set hive.execution.engine=tez;
     use default;
  </value>
  <description>Statements for execute after connection to the database</description>
</property>

Update the hive-hplsql jar file with the modified hplsql-site.xml

cd /usr/hdp/current/hive-server2-hive2/lib;
/usr/jdk64/jdk1.8.0_77/bin/jar uf hive-hplsql-2.1.0.XXX.jar hplsql-site.xml; 

Note: Please refer to your JDK version path

Authenticate the user with the KDC

kinit <user principal>

Execute the HPLSQL code as below

./hplsql -f /root/myhpl.sql

If success then you must be seeing the logs as below,

Starting SQL statement
SQL statement executed successfully (128 ms)
Starting SQL statement
SQL statement executed successfully (145 ms)