微信搜索bigdata029 | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 订阅本站 | 赞助作者:赞助作者

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

Hive lxw1234@qq.com 6945℃ 2评论

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

喜欢 (10)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(2)个小伙伴在吐槽
  1. 牛!谢谢分享! :mrgreen:
    蓝月亮2015-09-16 15:57 回复
  2. 请问,类似SELECT siteid,hello(cate) FROM liuxiaowen.lxw_site_cates limit 10;这样的语句您能跑起来吗?就是select中带自定义函数的情况。
    martuis2017-02-16 10:30 回复