備注:
Hive 版本 2.1.1
文章目錄
- 一. HPL/SQL概述
- 二.HPL/SQL測驗案例
- 2.1 測驗執行簡單sql
- 2.2 測驗函式和回圈
- 2.3 測驗include套用存盤程序
- 2.4 測驗package
- 2.4 測驗游標
- 2.5 測驗游標2
- 參考
一. HPL/SQL概述
Hive 2.0版本開始，已經有了Hive存盤程序的解決方案（HPL/SQL –Procedural SQL on hadoop），該解決方案不僅支持Hive，還支持在SparkSQL，其他NoSQL，甚至是RDBMS中使用類似于Oracle PL/SQL的功能，這將極大的方便資料開發者的作業，Hive中很多之前比較難實作的功能，現在可以很方便的實作，比如自定義變數、基于一個結果集的游標、回圈等等，
測驗hplsql命令:
[root@hp1 sql]# find / -name *hplsql*
/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hive/lib/hive-hplsql-2.1.1-cdh6.3.1.jar
/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hive/lib/hive-hplsql.jar
/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hive/bin/ext/hplsql.sh
/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hive/bin/hplsql
/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hive/bin/hplsql.cmd
/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/hive-hplsql-2.1.1-cdh6.3.1.jar
[root@hp1 sql]#
[root@hp1 sql]# /opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/lib/hive/bin/hplsql -e "CURRENT_DATE + 1"
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2020-12-18
為什么選擇HPL/SQL
Hadoop在資料倉庫中的作用是巨大的，但是要實作全面的ETL、報告、分析和資料挖掘程序，您不僅需要分布式處理引擎(如MapReduce、Spark或Tez)，還需要一種表達全面業務規則的方法，
-
業務邏輯驅動和高級錯誤處理
HPL/SQL允許您使用變數、運算式、控制流陳述句和迭代來實作業務邏輯，HPL/SQL支持使用例外和條件處理程式進行錯誤處理，您可以開發管理和控制分布式行程的程式，但同時不會成為系統的瓶頸， -
使hadoop上的sql更加動態
HPL/SQL的關鍵特性之一是它允許您使SQL更加動態，您可以使用高級運算式、各種內置函式、條件來根據用戶配置、以前查詢的結果、來自檔案或非hadoop資料源的資料等動態生成SQL，
3.利用現有的程序SQL技能
傳統的資料庫管理系統提供廣泛用于實作高級資料操作場景和作業流的程序性SQL語言，這種方法對于資料庫開發人員和資料分析人員來說既簡單又熟悉，
與Python、Java或Linux shell腳本相比，HPL/SQL使Hadoop能夠為BI分析師和開發人員提供更廣泛的受眾，
-
ETL框架
HPL/SQL提供函式和陳述句，使典型的ETL開發效率更高， -
可讀性和可維護性
與Bash腳本、Java、Python或Scala程式相比，HPL/SQL對于BI/SQL開發人員來說簡潔、可讀和可維護， -
集成和多語言持久性
Hadoop擴展了使用RDBMS產品構建的傳統資料倉庫，這意味著你必須集成多個系統，包括Hadoop、RDBMS、NoSQL和其他系統，
HPL/SQL允許您在一個腳本中處理多個系統，因此您可以針對不同型別的作業負載采用最好的方法，并輕松地集成它們， -
兼容性和遷移
HPL/SQL試圖盡可能地支持所有廣泛使用的程序語言的語法，您不需要從頭開始學習一種新的程序化語言，這有助于開發新代碼以及將現有代碼庫遷移到Hadoop， -
Hadoop快速啟動
HPL/SQL提供了開始使用Hadoop的最快方式，之后，您可以使用Spark、Tez、Storm、Flink和其他框架重新設計和實作高級資料處理作業流，但現在您可以使用您當前的技能和現有的代碼在Hadoop上運行業務邏輯，
二.HPL/SQL測驗案例
2.1 測驗執行簡單sql
[root@hp1 ~]# more b.sql
select count(*) from test.ods_fact_sale;
執行b.sql
[root@hp1 ~]# hplsql -f b.sql
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://localhost:10000 (248 ms)
Starting query
Query executed successfully (444.87 sec)
767830000
2.2 測驗函式和回圈
測驗一個函式和for 回圈的例子
代碼:
CREATE FUNCTION hello(text STRING)
RETURNS STRING
BEGIN
RETURN 'Hello, ' || text || '!';
END;
FOR item IN (
SELECT empno,ename FROM test.emp limit 10
)
LOOP
PRINT item.empno || '|' || item.ename || '|' || hello(item.empno);
END LOOP;
測驗記錄:
[root@hp1 ~]# hplsql -f a.sql
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://localhost:10000 (691 ms)
Starting query
Query executed successfully (639 ms)
7369|smith|Hello, 7369!
7499|allen|Hello, 7499!
7876|adams|Hello, 7876!
7900|james|Hello, 7900!
7902|ford|Hello, 7902!
7934|miller|Hello, 7934!
7521|ward|Hello, 7521!
7566|jones|Hello, 7566!
7654|martin|Hello, 7654!
7698|blake|Hello, 7698!
[root@hp1 ~]#
2.3 測驗include套用存盤程序
[root@hp1 ~]# more set_message.sql
CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
print 'name='||name
SET result = 'Hello, ' || name || '!';
END;
[root@hp1 ~]# more include.sql
INCLUDE set_message.sql
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;
[root@hp1 ~]#
測驗記錄:
[root@hp1 ~]# hplsql -f include.sql
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
name=world
Hello, world!
[root@hp1 ~]#
2.4 測驗package
代碼:
create or replace package users as
session_count int := 0;
function get_count() return int;
procedure add(name varchar(100));
end;
create or replace package body users as
function get_count() return int
is
begin
return session_count;
end;
procedure add(name varchar(100))
is
begin
-- ...
session_count = session_count + 1;end;
end;
users.add('John');
users.add('Sarah');
users.add('Paul');
print 'Number of users: ' || users.get_count();
測驗記錄:
create or replace package users as
session_count int := 0;
function get_count() return int;
procedure add(name varchar(100));
end;
create or replace package body users as
function get_count() return int
is
begin
return session_count;
end;
procedure add(name varchar(100))
is
begin
-- ...
session_count = session_count + 1;end;
end;
users.add('John');
users.add('Sarah');
users.add('Paul');
print 'Number of users: ' || users.get_count();
2.4 測驗游標
代碼:
CREATE PROCEDURE spOpenIssues
DYNAMIC RESULT SETS 1
BEGIN
DECLARE cur CURSOR WITH RETURN FOR
SELECT empno,ename FROM test.emp;
OPEN cur;
END;
DECLARE id INT;
DECLARE name VARCHAR(30);
CALL spOpenIssues;
ALLOCATE c1 CURSOR FOR PROCEDURE spOpenIssues;
FETCH c1 INTO id, name;
WHILE (SQLCODE = 0)
DO
PRINT id || ' - ' || name;
FETCH c1 INTO id, name;
END WHILE;
CLOSE c1;
測驗記錄:
[root@hp1 ~]# hplsql -f cursor_test.sql
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://localhost:10000 (255 ms)
Starting query
Query executed successfully (102 ms)
7369 - smith
7499 - allen
7876 - adams
7900 - james
7902 - ford
7934 - miller
7521 - ward
7566 - jones
7654 - martin
7698 - blake
7782 - clark
7788 - scott
7839 - king
7844 - turner
2.5 測驗游標2
用我自己plsql習慣的方式來寫游標
代碼:
vim c.sql
cursor emp_cur is
select empno,ename from test.emp;
for rc in emp_cur loop
dbms_output.put_line(rc.empno||'-'||rc.ename);
end loop;
測驗記錄:
[root@hp1 ~]# hplsql -f c.sql
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://localhost:10000 (250 ms)
Starting query
Query executed successfully (111 ms)
7369 smith
7499 allen
7876 adams
7900 james
7902 ford
7934 miller
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7788 scott
7839 king
7844 turner
-
[root@hp1 ~]#
最后多了一個’-’ 好奇怪，而且前面的輸出都沒有連接符
替換成如下就沒問題了
for rc in (select empno,ename from test.emp) loop
dbms_output.put_line(rc.empno||'-'||rc.ename);
end loop;
測驗記錄:
[root@hp1 ~]# hplsql -f c.sql
WARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.1-1.cdh6.3.1.p0.1470567/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://localhost:10000 (235 ms)
Starting query
Query executed successfully (97 ms)
7369-smith
7499-allen
7876-adams
7900-james
7902-ford
7934-miller
7521-ward
7566-jones
7654-martin
7698-blake
7782-clark
7788-scott
7839-king
7844-turner
[root@hp1 ~]#
參考
1.http://www.hplsql.org/home
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/246972.html
標籤：其他