HPLSQL(Hadoop存储过程实现)的安装和简单应用

HPLSQL搭建所需环境：

 

• Java 1.6 or higher
• Hadoop 1.x. and 2.x

 

1.下载安装包并授权

Chmod +x <hplsql.dir>/hplsql

2.环境变量设置

vi hplsql  配置

Hadoop环境变量

和hadoopjar包的环境变量

export "HADOOP_CLASSPATH=/opt/cloudera/parcels/CDH/jars/*"

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"

3.HPLSQL安装

<hplsql_dir>/hplsql --version

HPL/SQL x.x.x

4.添加HPLSQL环境变量配置

export PATH=$PATH:<hplsql_dir>

4.1配置队列

如果hivesever2没有启动，需要配置并启动

4.2vi hplsql-site.xml

配置：

--设置默认连接

<property>

<name>hplsql.conn.default</name>

<value>hive2conn</value>

<description>The default connection profile</description>

</property>

--连接hive2

<property>

<name>hplsql.conn.hive2conn</name>

<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://127.16.21.17:10000</value>

<description>HiveServer2 JDBC connection</description>

</property>

--可以不添加

<property>

<name>hplsql.conn.hive.conn</name>

<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive://</value>

<description>Hive embedded JDBC (not requiring Hiveserver)</description>

</property>

--设置队列、资源获取、

<property>

<name>hplsql.conn.init.hive2conn</name>

<value>

         set mapred.job.queue.name=default;

         set hive.execution.engine=mr;

         use _default;

</value>

<description>Statements for execute after connection to the database</description>

</property>

 

<property>

<name>hplsql.conn.covert.hiveconn</name>

<value>true</value>

<description>Convert SQL statements before execution</description>

</property>

 

5.运行hplsql与应用

hplsql -e "CURRENT_DATE+1"

 

hplsql -e "SELECT * FROM src LIMIT 1"

or

 

②hplsql -f script.sql

Use HPL/SQL in Shell Scripts

Get a value from HPL/SQL script:

 

MDATE=$(hplsql -e "NVL(MIN_PARTITION_DATE(sales, local_dt, code='A'), '1970-01-01')")

START=$(hplsql -e 'CURRENT_DATE - 1')

 

可以通过 –main 参数指定要执行的部分

Hplsql –f script.sql –main funcation_name