hive的存储过程 hplsql配置

hive2.x自带hplsql
如果用的hive1.x需要下载hplsql

1.配置 hplsql-site.xml

在hive的conf下新建 hplsql-site.xml 并添加以下内容

<configuration>
<property>
  <name>hplsql.conn.default</name>
  <value>hive2conn</value>
  <description>The default connection profile</description>
</property>
<property>
  <name>hplsql.conn.hive2conn</name>
  <!--设置自己的主机名,设置用户名密码(随意写)-->
 <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://hadoop01:10000;root;123</value>
</property>
<property>
  <name>hplsql.conn.init.hiveconn2</name>
  <value>
     set mapred.job.queue.name=dev;
     set hive.execution.engine=mr;
     use default;
  </value>
</property>
<property>
  <name>hplsql.conn.convert.hive2conn</name>
  <value>true</value>
</property>
<property>
  <name>hplsql.conn.mysqlconn</name>
  <!--设置自己的主机名,设置自己mysql的用户名,密码-->
  <value>com.mysql.jdbc.Driver;jdbc:mysql://hadoop01:3306/hive;root;root</value>
  <description>MySQL connection</description>
</property>
<property>
  <name>hplsql.dual.table</name>
  <value>default.dual</value>
  <description>Single row, single column table for internal operations</description>
</property>
<property>
  <name>hplsql.insert.values</name>
  <value>default.dual</value>
  <description>Single row, single column table for internal operations</description>
</property>
<property>
  <name>hplsql.insert.values</name>
  <value>native</value>
  <description>How to execute INSERT VALUES statement: native (default) and select</description>
</property>
<property>
  <name>hplsql.onerror</name>
  <value>exception</value>
  <description>Error handling behavior: exception (default), seterror and stop</description>
</property>
<property>
  <name>hplsql.temp.tables</name>
  <value>native</value>
  <description>Temporary tables: native (default) and managed</description>
</property>
<property>
  <name>hplsql.temp.tables.schema</name>
  <value></value>
  <description>Schema for managed temporary tables</description>
</property>
<property>
  <name>hplsql.temp.tables.location</name>
  <value>/tmp/plhql</value>
  <description>LOcation for managed temporary tables in HDFS</description>
</property>
</configuration>

2. 配置hadoop下的core-site.xmi

vim $HADOOP_HOME/etc/hadoop/core-site.xml
添加以下内容

<property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
</property>

3. 使用hplsql 执行命令

• 3.1 使用hplsql -e

hplsql -e "CURRENT_DATE + 1"
hplsql -e "for i in 1..10 loop print i;end loop"

• 3.2 使用hplsql -f

vi ps.sql  写入以下内容

use db_name;
create procedure procedure_name()
begin
select * from db_name.tb_name;
end;

--------------------------------
执行:
 hplsql -f /root/hiveshell/ps.sql -main peocedure_name;

• 3.3 创建存储过程的格式

use db_name;
create procedure procedure_name()
begin
sql commmand...
end;

• 3.4 调用存储过程

include 存储过程路径 call procedure_name

eg：
vi ps1.sql  写入以下内容

include /root/hiveshell/ps.sql
call procedure_name();

--------------------------------------
执行:
hplsql -f /root/hiveshell/ps1.sql