在Hive中实现存储过程–HQL/SQL

关键字：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