Erroir when using HPL/SQL function in Hive CTL

[Kangfei Zhao <zk...@gmail.com>]

Hi All，

I follow the steps in http://www.hplsql.org/udf run hplsql function in Hive
CTL. However it have the error and I cannot find ideas to fix it.

hive> add jar /usr/local/hive/lib/hive-hplsql-2.3.3.jar;
Added [/usr/local/hive/lib/hive-hplsql-2.3.3.jar] to class path
Added resources: [/usr/local/hive/lib/hive-hplsql-2.3.3.jar]
hive> add jar /usr/local/hive/lib/antlr-runtime-3.5.2.jar;
Added [/usr/local/hive/lib/antlr-runtime-3.5.2.jar] to class path
Added resources: [/usr/local/hive/lib/antlr-runtime-3.5.2.jar]
hive> add file /usr/local/hive/conf/hplsql-site.xml;
Added resources: [/usr/local/hive/conf/hplsql-site.xml]
hive> add file /usr/local/hive/script/hplsqlrc;
Added resources: [/usr/local/hive/script/hplsqlrc]
hive> CREATE TEMPORARY FUNCTION hplsql AS 'org.apache.hive.hplsql.Udf';
OK
Time taken: 0.01 seconds
hive> SELECT hplsql('hello(:1)', name) FROM users;
OK
Configuration file: file:/usr/local/hive/conf/hplsql-site.xml
Parser tree: (program (block (stmt (expr_stmt (expr (expr_func (ident
hello) ( (expr_func_params (func_param (expr (expr_atom (ident :1)))))
)))))))
INCLUDE file error: File 'hplsql_locals.sql' does not exist
Ln:1 Function not found: hello
NULL
Time taken: 0.549 seconds, Fetched: 1 row(s)

How can I fix the problem? Thank you!
Below is the configuration of 'hplsql-site.xml.' Here I didn't use
hiveserver2. I'm wondering if the hiveserve2 is required to be defalt
connection.

root@100.95.24.80:/usr/local/hive# cat conf/hplsql-site.xml
<configuration>
<property>
  <name>hplsql.conn.default</name>
  <value>hiveconn</value>
  <description>The default connection profile</description>
</property>
<property>
  <name>hplsql.conn.hiveconn</name>
  <value>org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://</value>
  <description>Hive embedded JDBC (not requiring HiveServer)</description>
</property>

<property>
  <name>hplsql.conn.init.hiveconn</name>
  <value>
     set mapred.job.queue.name=default;
     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.hive2conn</name>

<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://localhost:10000</value>
  <description>HiveServer2 JDBC connection</description>
</property>

<property>
  <name>hplsql.conn.init.hive2conn</name>
  <value>
     set mapred.job.queue.name=default;
     set hive.execution.engine=mr;
     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>/home/centos/soft/hive/tmp/plhql</value>
  <description>LOcation for managed temporary tables in HDFS</description>
</property>
<property>
<name>hive.server2.thrift.bind.host</name>
<value>localhost</value>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
</configuration>

Best,
Kangfei

*****************************************************************************
Department of Systems Engineering and Engineering Management
The Chinese University of Hong Kong
William M. Engineering Building, Rm 801
Tel:3943 8326
Email:kfzhao@se.cuhk.edu.hk <kf...@se.cuhk.edu.hk>;zkf1105@gmail.com
*****************************************************************************