如何在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 语法

醉酒鞭名马,少年多浮夸! 岭南浣溪沙,呕吐酒肆下!挚友不肯放,数据玩的花! 温馨提示:要看高清无码套图,请使用手机打开并单击图片放大查看。


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

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

本文分享自微信公众号 - Hadoop实操(gh_c4c535955d0f)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-10-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL Shell的简单介绍(r12笔记第95天)

MySQL Shell是在官方版本5.7.12推出,工具的初衷本身都是为了解决一类问题,想必官方从很多方面了解到工具的使用情况,支持的开发语言太多,众口难调...

724100
来自专栏杨建荣的学习笔记

Oracle 12c中DBCA搭建备库体验(r11笔记第92天)

Oracle 12c中DBCA有一个特性看起来蛮有意思,就是直接通过DBCA来搭建Data Guard,当然这么说也有点噱头,我们来实际看看。 Ora...

42760
来自专栏张戈的专栏

MySQL错误修复记录:Table xx is marked as crashed and should be repaired

昨晚入睡后,收到松哥的 QQ 消息,说松松商城打开报错,于是手机 QQ 上打开了首页地址,发现有如下报错: ? MySQL server error repor...

668110
来自专栏杨建荣的学习笔记

宕机的那些事儿(r12笔记第44天)

DBA干了这么多年,一直以来有一个疑惑,那就是从半夜的电话中吵醒时,几乎清一色都是宕机类问题,每次我就忍不住想喊,大早上宕机,让不让人睡觉了。但是抱怨归抱怨...

44390
来自专栏杨建荣的学习笔记

使用shell自动化诊断性能问题(一)(r11笔记第41天)

一直以来要做性能分析的自动化工作,但是久久没有动笔,今天索性来更新一版。 首先我希望得到的一个基本效果就是后台去扫描数据库的DB time,如果超出了阈值,比如...

36560
来自专栏杨建荣的学习笔记

Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)

今天看到Franck Pachot‏ 发了一个Twitter,意思是Oracle里的SQL还能这么写。猛一看确实让人有些意外。 ? 禁不住诱惑,自己也尝试了一番...

30650
来自专栏张戈的专栏

MySQL主从报错解决:Failed to initialize the master info structure

大清早收到一个 MySQL 的自定义语言告警 :replication interrupt,看来是主从同步报错了。 登陆 MySQL,执行 show slave...

37850
来自专栏杨建荣的学习笔记

浅谈MySQL中的事务隔离级别(r11笔记第86天)

之前写了一篇浅谈事务(一),算是对事务的一个基本认识,今天来简单总结一下事务的隔离级别,虽然是老掉牙的知识点,重温一下还是值得的。 在MySQL中基...

34990
来自专栏杨建荣的学习笔记

相差数十倍的SQL性能分析(r11笔记第98天)

今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的SQL,但是有一点不同的是,他们提供了两份文件,一份是一个id列表,大概有3000多个id值,...

36380
来自专栏杨建荣的学习笔记

MySQL中的反连接(r12笔记第45天)

关于Oracle的半连接,反连接,我一直认为这是一个能讲很长时间的话题,所以在我的新书《Oracle DBA工作笔记》中讲性能优化的时候,我花了不少的笔墨做...

28650

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励