Shulou(Shulou.com)06/01 Report--

This article is about how to use hive stored procedures. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Introduction to hive stored procedures 1.x version of hive does not provide similar functions like stored procedures. When using Hive for data development, it is common to encapsulate HQL statements in Shell or other scripts, and then call them in a command line to complete the statistical analysis of a business or a report. The good news is that there is now a solution for Hive stored procedures (HPL/SQL-Procedural SQL on hadoop), and this module will be integrated in future versions of Hive (2.0). The solution not only supports Hive, but also supports the use of functions similar to Oracle PL/SQL in SparkSQL, other NoSQL, and even RDBMS, which will greatly facilitate the work of data developers. Many functions in Hive that were difficult to achieve before can now be easily implemented, such as custom variables, cursors based on a result set, loops and so on.

2. Hplsql installation and stored procedure cases

Download:

Download address on the official website: http://www.hplsql.org/download

Version 0.3.31 download address: http://www.hplsql.org/downloads/hplsql-0.3.31.tar.gz

Install hplsql:

[root@hadoop01 home] # tar-zxvf / home/hplsql-0.3.31.tar.gz-C / usr/local/

[root@hadoop01 home] # cd / usr/local/hplsql-0.3.31/

Configure hplsql:

Vi. / hplsql

The contents are as follows:

#! / bin/bash

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"

SCRIPTPATH=$ {0% Universe *}

Java-cp $SCRIPTPATH:$HADOOP_CLASSPATH:$SCRIPTPATH/hplsql-0.3.31.jar:$SCRIPTPATH/antlr-runtime-4.5.jar $HADOOP_OPTS org.apache.hive.hplsql.Hplsql "$@"

Configure services for hive2:

Vi. / conf/hive-site.xml

The additional content is as follows:

Hive.server2.thrift.bind.host

Hadoop01

Hive.server2.thrift.port

10000

Restart hiveserver2:

Nohup hive-- service hiveserver2 > hiveserver2.log 2 > & 1 &

Configure the connection between HPL/SQL and Hive:

Vi. / hplsql-site.xml

Use hplsql-e to execute the command:

[root@hadoop01 hplsql-0.3.31] #. / hplsql- e "CURRENT_DATE + 1"

2019-09-18

[root@hadoop01 hplsql-0.3.31] #. / hplsql- e "for i in 1. 10 loop print I to end loop;"

Hplsql-f executes the script:

Test table:

Hive > select * from qf24.u5

OK

7 yy

3 cc

2 bb

Create script hp.sql

Create function hello (text string)

ReturnS string

BEGIN

RETRUEN 'Hello,' | | text | |'!'

END

FOR item IN (

SELECT id,name FROM qf24.u5 limit 3

)

Loop

Println item.id | |'|'| | item.name | |'|'| | hello (item.name)

End loop

Test:

[root@hadoop01 hplsql-0.3.31] #. / hplsql- f / home/hivedata/hp.sql

7 | yy | Hello,yy!

3 | cc | Hello,cc!

2 | bb | Hello,bb!

Create the format of the stored procedure:

Use database

Create procedure

Begin

.

End

Call the stored procedure:

Include path/sp name

Call sp name

Case study:

Create a stored procedure:

Use qf24

Create procedure select_u5 ()

Begin

Select * from qf24.u5

End

Call:

Include file path (introduced in the code file)

Execute the execution file:

[root@hadoop01 hplsql-0.3.31] #. / hplsql- f / home/hivedata/ps.sql

Call the specified stored procedure or method:

[root@hadoop01 hplsql-0.3.31] #. / hplsql- f / home/hivedata/ps.sql-main select_u5

Thank you for reading! This is the end of the article on "how to use hive stored procedures". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.