如何让你的Oracle SQL/SP运行在Hive上？hplsql执行Oracle存储过程

关于hplsql的配置安装请见：https://blog.csdn.net/qq_34901049/article/details/107582460

基本环境：

已有基础集群环境（三个节点）： 
CentOS 6.8
Java8
hadoop2.7.2
hive 1.2.1

使用hplsql版本：
hplsql-0.3.31

前面提到hplsql看起来有助于企业从传统RDB业务架构升级到完全分布式中的Oracle SP转换执行。即实现Oracle SP on Hive的效果。

hplsql作为Apache Hive下的一个子模块，借助Antlr解析Oracle SP后运行在hive上；其若能提供完好的功能支持，将带来巨大的价值。而在官网（ http://hplsql.org/home）可以看到最新版本发布于2017年九月；对应github（ https://github.com/apache/hive/tree/master/hplsql）上发现该子项目最后针对源码的更新是在半年前，并且最近两年并不活跃（此文章撰写于2020年7月25日）。据说bug很多。。。

故而，以下针对具体操作场景拆分测试该工具的可行性。主要包括行级数据更新（insert、update、delete），以及流程控制语句、异常处理。最后试了试游标，发现不支持

来看看基本数据操作测试：增删改查。

注意确保对应服务已开启，如hdfs、yarn、hiveserver2

1 先来试试官网给出的例子（ http://hplsql.org/include）
setMessage.sql

CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
SET result = 'Hello, ' || name || '!';
END;

inct.sql

INCLUDE setMessage.sql
DECLARE str STRING;
CALL set_message('this is SH', str);
PRINT str;

pass

2 试试最简单的查询

create or replace procedure proc_ht
as
begin  
  select * from action;
end proc_ht;

call proc_ht;

pass

3 试试数据更新
注意在做数据更新前需要做额外配置，其中包含基本配置以及建表DDL的相应变化，详见：如何配置hive支持行级数据更新操作？

create or replace procedure proc_updateT(id in accountInfo.id%type)
as
begin
  update accountInfo
     set age = age + 10
   where accountInfo.id = id;       
end proc_updateT;

call proc_updateT(1);

select *from accountInfo;

pass

4 试试数据删除

不知道为什么这里采用传参的方式发现对应要删除的数据未被match上，干脆写成字面量3（where accountInfo.id = 3;）却可以。

select *from accountInfo;

create or replace procedure proc_delt(uid in accountInfo.id%type)
as
begin
  delete from accountInfo
  #where accountInfo.id = uid; 
  where accountInfo.id = 3;       
end proc_delt;

call proc_delt(3);
commit;

select *from accountInfo;

pass

5 数据插入测试

select *from accountInfo;

create or replace procedure proc_inst
as
begin
insert into accountInfo values(4,'bt',18);
exception
when others then
        DBMS_OUTPUT.PUT_LINE('msg: '|| sqlerrm);

end proc_inst;

call proc_inst;

select *from accountInfo;

pass

6 if、while语句判断测试（经测不支持i++、i:=i+1操作）

create or replace procedure loopifT(num in int)
as
i number;
begin
DBMS_OUTPUT.PUT_LINE('num: '||num);
if num>0 then
        i=1;
        while i<=num loop
                DBMS_OUTPUT.PUT_LINE('i: '||i);
                i=i+1;
        end loop;
end if;
end loopifT;

call loopifT(5);

pass

7 游标cursor
此处不支持cursor，但支持sys_refcursor.

create or replace procedure getCurResult(acc_cur out sys_refcursor)
as 
begin
        open acc_cur  for select * from accountInfo;  
end getCurResult;

create or replace procedure proc_cursor
as
account_cur sys_refcursor;
account_rec accountInfo%ROWTYPE;
begin
call getCurResult(account_cur);
while true loop
        fetch account_cur into account_rec;
        exit when account_cur%notfound;
        DBMS_OUTPUT.PUT_LINE('account.id: '|| account_rec.id || '  account.name: '|| account_rec.name || '  account.age: '|| account_rec.age);
end loop;

exception
when others then
        DBMS_OUTPUT.PUT_LINE('msg: '|| sqlerrm);
                if account_cur%ISOPEN then
                        close account_cur;
                end if;

close account_cur;

end proc_cursor;

call proc_cursor;

8 查询变量赋值（into）及API示例

create or replace procedure proc_ht(str in varchar2)
as
uid int;
begin  
  select id into uid  from accountInfo;

DBMS_OUTPUT.PUT_LINE('uid: '|| uid);

DBMS_OUTPUT.PUT_LINE('substr: '|| substr(str,3,5));

DBMS_OUTPUT.PUT_LINE('substr: '|| substr(str,instr(str,'h'),2));

DBMS_OUTPUT.PUT_LINE('upper: '|| upper(str))

DBMS_OUTPUT.PUT_LINE('trim: '|| trim('  td d ')||'|')

end proc_ht;

call proc_ht('hello SH!');

总结：

基于上述测试，在hive已配置行级数据更新支持的前提下，运用hplsql运行Oracle SP且伴随简单数据操作如insert、update、delete是可行的。此外其支持基本流程控制语句、异常处理、sys_refcursor等（更多支持参考官网：http://hplsql.org/plsql）。总体来看，该工具对Oracle SP提供了大部分的兼容支持，以使得简单Oracle SP可直接运行在Hive上，而Hive亦可配置Hive on Spark以达到对应场景下更好的运算效率；如此处的Hive on Tez。

以上毕竟基于简单测试，实际可行性还得要回到复杂的生产环境下具体问题具体分析。