0043-如何在CDH中使用HPLSQL实现存储过程

温馨提示：要看高清无码套图，请使用手机打开并单击图片放大查看。

1.文档编写目的

目前版本的Hive中没有提供类似存储过程的功能，使用Hive做数据应用开发时候，一般有以下两种方法：

1. 将一段一段的HQL语句封装在Shell或者其他脚本中，然后以命令行的方式调用，完成一个业务或者一张报表的统计分析。
2. 使用HPL/SQL （Procedural SQL on Hadoop，在Hive的版本（2.0）中，会将该模块集成进来）。该解决方案不仅支持Hive，还支持在SparkSQL，其他NoSQL，甚至是RDBMS中使用类似于Oracle PL/SQL的功能，这将极大的方便数据开发者的工作，Hive中很多之前比较难实现的功能，现在可以很方便的实现，比如自定义变量、基于一个结果集的游标、循环等等。

本文档主要讲述如何使用HPL/SQL在Hive中实现存储过程。

• 内容概述

1.安装及配置HPL/SQL

2.自定义函数和游标

3.存储过程改造

4.总结

• 测试环境

1.CM和CDH版本为5.11.2

2.RedHat7.2

• 前置条件

1.CDH集群正常

2.HiveServer2服务安装且正常运行

3.JDK1.6以上

2.安装及配置HPL/SQL

1.从http://www.hplsql.org/download下载最新的HPL/SQL

2.解压安装包

[ec2-user@ip-172-31-22-86 hive-plsql]$ tar -zxvf hplsql-0.3.31.tar.gz 
hplsql-0.3.31/
hplsql-0.3.31/hplsql-0.3.31.jar
hplsql-0.3.31/hplsql
hplsql-0.3.31/antlr-runtime-4.5.jar
hplsql-0.3.31/hplsql.cmd
hplsql-0.3.31/LICENSE.txt
hplsql-0.3.31/hplsql-site.xml
hplsql-0.3.31/README.txt
[ec2-user@ip-172-31-22-86 hive-plsql]$ 

3.修改执行权限

进入hplsql-0.3.31目录，修改hplsql文件执行权限

[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ sudo chmod +x hplsql

4.配置CDH集群环境

编辑hplsql文件，根据你的实际环境配置HADOOP_CLASSPATH，配置如下：

#!/bin/bash
CDH_HOME=/opt/cloudera/parcels/CDH/lib
JAVA_HOME=/usr/java/jdk1.7.0_67-cloudera
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/lib
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/etc/hadoop/conf"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop-mapreduce/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop-mapreduce/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop-hdfs/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop-hdfs/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop-yarn/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hadoop-yarn/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hive/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$CDH_HOME/hive/conf"
export HADOOP_OPTS="$HADOOP_OPTS -Djava.library.path=$CDH_HOME/hadoop/lib/native"
SCRIPTPATH=`pwd`
java -cp $SCRIPTPATH:$HADOOP_CLASSPATH:$SCRIPTPATH/hplsql-0.3.31.jar:$SCRIPTPATH/antlr-runtime-4.5.jar $HADOOP_OPTS org.apache.hive.hplsql.Hplsql "$@"

以上标红部分需要根据你实际的环境进行配置。

5.测试配置是否生效

[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -version
/home/ec2-user/hive-plsql/hplsql-0.3.31
HPL/SQL 0.3.31
[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ 

6.配置和启动Hive的JDBC服务HiveServer2

HPL/SQL与Hive通过JDBC方式连接，因此需要先启动HiveServer2。在Cloudera Manager中确认HiveServer2实例已经启动

7.配置HPL/SQL与Hive的连接

编辑hplsql-site.xml，修改以下配置，以连接到HiveServer2服务（注意，hostname默认是localhost，如果是从HiveServer2的主机连接，可以不用做任何修改）

<property>
 <name>hplsql.conn.default</name>
 <value>hive2conn</value>
 <description>The default connection profile</description>
</property>
<property>
 <name>hplsql.conn.hiveconn</name>
 <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://localhost:10000;hive;hive</value>
 <description>HiveServer2 JDBC connection (embedded mode)</description>
</property>

8.使用hplsql执行HPL/SQL语句，测试是否正常运行

[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -e "CURRENT_DATE+1"
[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -e "SELECT * FROM test LIMIT 1" 

能够正确显示数据库列表，则说明与HiveServer2连接已经成功。创建测表，插入测试数据

[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -e "create table test_table (id string, names string) stored as textfile"
[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -e "insert into test_table values('1','a')"
[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -e "select * from test_table"

3.自定义函数和游标

将下面的HPL/SQL语句保存到1.hql文件中：

CREATE FUNCTION hello(text STRING)
 RETURNS STRING
BEGIN
 RETURN 'Hello, ' || text || '!';
END;
FOR item IN (
 SELECT s1,s2 FROM test limit 10
)
LOOP
PRINT item.s1 || '|' || item.s2 || '|' || hello(item.s1);
END LOOP;

上面的语句中，第一部分创建了一个自定义函数hello，传入一个字符串，返回Hello,字符串! 第二部分使用FOR .. LOOP游标，从Hive的表中获取10条记录，打印出两个字段的内容。

使用命令./hplsql -f 1.hql指定上面的HPL/SQL语句块：

[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ ./hplsql -f 1.hql 
...
Query executed successfully (208 ms)
name1|age1|Hello, name1!
name2|age2|Hello, name2!
name3|age3|Hello, name3!
name4|age4|Hello, name4!
name5|age5|Hello, name5!
name6|age6|Hello, name6!
name7|age7|Hello, name7!
name8|age8|Hello, name8!
name9|age9|Hello, name9!
name10|age10|Hello, name10!
[ec2-user@ip-172-31-22-86 hplsql-0.3.31]$ 

4.存储过程改造

如下是使用TPC-DS真实示例改造的存储过程

需要改造的存储过程为：

改造为Hive存储过程：

执行该存储过程

执行完毕

任务在集群的Yarn中查看

结果查看：

5.总结

Hive的存储过程工具HPL/SQL：

• 支持存储过程
• 支持控制流
• 支持异常处理
• 支持游标
• 支持package 语法

醉酒鞭名马，少年多浮夸！ 岭南浣溪沙，呕吐酒肆下！挚友不肯放，数据玩的花！

温馨提示：要看高清无码套图，请使用手机打开并单击图片放大查看。

您可能还想看

如何在CDH启用Kerberos的情况下安装及使用Sentry(一)

如何在CDH启用Kerberos的情况下安装及使用Sentry(二)

如何在CDH未启用认证的情况下安装及使用Sentry

如何实现CDH元数据库MySQL的主备

如何使用Sentry管理Hive外部表权限

如何在Kerberos与非Kerberos的CDH集群BDR不可用时复制数据

CDH网络要求(Lenovo参考架构)

大数据售前的中年危机

CENTOS6.5安装CDH5.12.1(一)

CENTOS6.5安装CDH5.12.1(二)

CENTOS7.2安装CDH5.10和Kudu1.2(一)

CENTOS7.2安装CDH5.10和Kudu1.2(二)

如何在CDH中安装Kudu&Spark2&Kafka

如何在CDH集群启用Kerberos

如何升级Cloudera Manager和CDH

如何卸载CDH(附一键卸载github源码)

如何迁移Cloudera Manager节点

推荐关注Hadoop实操，第一时间，分享更多Hadoop干货，欢迎转发和分享。

原创文章，欢迎转载，转载请注明：转载自微信公众号Hadoop实操