关键字：Hive 存储过程、PL/HQL、HQL/SQL、Procedural SQL on Hadoop
目前版本的Hive中没有提供类似存储过程的功能，使用Hive做数据开发时候，一般是将一段一段的HQL语句封装在Shell或者其他脚本中，然后以命令行的方式调用，完成一个业务或者一张报表的统计分析。
好消息是，现在已经有了Hive存储过程的解决方案（HPL/SQL –Procedural SQL on Hadoop），并且在未来的Hive的版本（2.0）中，会将该模块集成进来。该解决方案不仅支持Hive，还支持在SparkSQL，其他NoSQL，甚至是RDBMS中使用类似于Oracle PL/SQL的功能，这将极大的方便数据开发者的工作，Hive中很多之前比较难实现的功能，现在可以很方便的实现，比如自定义变量、基于一个结果集的游标、循环等等。
HPL/SQL (previously known as PL/HQL) is an open source tool (Apache License 2.0) that implements procedural SQL language for Apache Hive, SparkSQL as well as any other SQL-on-Hadoop implementations, NoSQL and RDBMS.
HPL/SQL language is compatible to a large extent with Oracle PL/SQL, ANSI/ISO SQL/PSM (IBM DB2, MySQL, Teradata i.e), PostgreSQL PL/pgSQL (Netezza), Transact-SQL (Microsoft SQL Server and Sybase) that allows you leveraging existing SQL/DWH skills and familiar approach to implement data warehouse solutions on Hadoop. It also facilitates migration of existing business logic to Hadoop.
HPL/SQL is an efficient way to implement ETL processes in Hadoop.
官方网站是：http://www.hplsql.org
本文先尝试使用一下。
下载和配置HPL/SQL
从http://www.hplsql.org/download下载最新版本HPL/SQL 0.3.11安装包，并解压。
- 配置HADOOP_CLASSPATH
进入hplsql-0.3.11目录，编辑hplsql文件，根据你的实际环境配置HADOOP_CLASSPATH。
我的配置如下：
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/lib/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_CONF_DIR/" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/mapreduce/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/mapreduce/lib/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/hdfs/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/hdfs/lib/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/yarn/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/yarn/lib/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/common/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/share/hadoop/common/lib/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*" export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/conf" export HADOOP_OPTS="$HADOOP_OPTS -Djava.library.path=$HADOOP_HOME/lib/native"
- 配置和启动Hive的thrift服务HiveServer2
HPL/SQL与Hive通过thrift方式连接,因此，需要先启动HiveServer2.
编辑hive-site.xml
添加以下变量：
<property>
<name>hive.server2.thrift.bind.host</name>
<value>127.16.21.17</value>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
启动HiveServer2:
cd $HIVE_HOME/bin
./hiveserver2
Starting HiveServer2
- 配置HPL/SQL与Hive的连接
编辑hplsql-site.xml
修改以下变量：
<property>
<name>hplsql.conn.default</name>
<value>hive2conn</value>
<description>The default connection profile</description>
</property>
<property>
<name>hplsql.conn.hive2conn</name>
<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://127.16.21.17:10000</value>
<description>HiveServer2 JDBC connection</description>
</property>
使用hplsql执行HPL/SQL语句
cd hplsql-0.3.11
执行以下HPL/SQL语句，查看结果：
./hplsql -e "CURRENT_DATE + 1" ./hplsql -e "PRINT a || ', ' || b" -d a=Hello -d b=lxw1234.com ./hplsql -e "FOR i IN 1 .. 10 LOOP PRINT i; END LOOP;" [liuxiaowen@dev hplsql-0.3.11]$ ./hplsql -e "CURRENT_DATE + 1" 2015-09-17 [liuxiaowen@dev hplsql-0.3.11]$ ./hplsql -e "PRINT a || ', ' || b" -d a=Hello -d b=lxw1234.com Hello, lxw1234.com [liuxiaowen@dev hplsql-0.3.11]$ ./hplsql -e "FOR i IN 1 .. 10 LOOP PRINT i; END LOOP;" 1 2 3 4 5 6 7 8 9 10
使用hplsql创建自定义函数和游标
将下面的HPL/SQL语句保存到文件1.sql中：
CREATE FUNCTION hello(text STRING)
RETURNS STRING
BEGIN
RETURN 'Hello, ' || text || '!';
END;
FOR item IN (
SELECT siteid,cate FROM liuxiaowen.lxw_site_cates limit 10
)
LOOP
PRINT item.siteid || '|' || item.cate || '|' || hello(item.cate);
END LOOP;
上面的语句中，第一部分创建了一个自定义函数hello，传入一个字符串，返回Hello,字符串!
第二部分使用FOR .. LOOP游标，从Hive的liuxiaowen.lxw_site_cates表中获取10条记录，打印出两个字段的内容，并把cate字段传递给前面的函数hello，打印返回的结果,用单竖线拼接。
使用命令./hplsql -f 1.sql指定上面的HPL/SQL语句块：
[liuxiaowen@dev hplsql-0.3.11]$ ./hplsql -f 1.sql Open connection: jdbc:hive2://127.16.21.17:10000 (110 ms) Starting query Query executed successfully (13.51 sec) 1|社交|Hello, 社交! 2|社交|Hello, 社交! 3|电脑科技|Hello, 电脑科技! 4|电脑科技|Hello, 电脑科技! 5|手机通讯|Hello, 手机通讯! 6|手机通讯|Hello, 手机通讯! 7|手机通讯|Hello, 手机通讯! 8|游戏|Hello, 游戏! 9|手机通讯|Hello, 手机通讯! 10|手机通讯|Hello, 手机通讯!
Hive中，该表的10条记录为：
hive> select * from liuxiaowen.lxw_site_cates limit 10; OK 1 社交 2 社交 3 电脑科技 4 电脑科技 5 手机通讯 6 手机通讯 7 手机通讯 8 游戏 9 手机通讯 10 手机通讯 Time taken: 0.097 seconds, Fetched: 10 row(s)
用过Oracle、Mysql、DB2、SQLServer的同学应该都清楚，它们都提供了存储过程的功能，可以很方便的管理和开发SQL程序，HQL/SQL使得Hive和SparkSQL也都可以实现这点。
HQL/SQL支持的语法和功能非常强大，本文只做尝试性的试验，后续将继续介绍HQL/SQL的特性及用法。
更多关于Hive中使用存储过程的文章，阅读《Hive存储过程系列文章》。
您可以关注 lxw的大数据田地 ，或者 加入邮件列表 ，随时接收博客更新的通知邮件。
如果觉得本博客对您有帮助，请 赞助作者 。
转载请注明：lxw的大数据田地 » 在Hive中实现存储过程–HQL/SQL
