目前版本的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存储过程系列文章》。