軟件環(huán)境:
linux系統(tǒng): CentOS6.7
Hadoop版本: 2.6.5
zookeeper版本: 3.4.8
</br>
主機(jī)配置:
一共m1, m2, m3這三部機(jī), 每部主機(jī)的用戶名都為centos
192.168.179.201: m1
192.168.179.202: m2
192.168.179.203: m3
m1: Zookeeper, Namenode, DataNode, ResourceManager, NodeManager, Master, Worker
m2: Zookeeper, Namenode, DataNode, ResourceManager, NodeManager, Worker
m3: Zookeeper, DataNode, NodeManager, Worker
資料:
搭建教程:
http://lxw1234.com/archives/2015/09/487.htm
下載HPL/SQL地址:
http://www.hplsql.org/download
HPL/SQL官網(wǎng):
http://www.hplsql.org/doc
注意事項:
一定不能在HPL/SQL上使用Hive語法的語句,要使用MySQL或Oracle等其他HPL/SQL支持的數(shù)據(jù)庫(具體可去官網(wǎng)查看),不然會報找不到dual表或者dual表中無該字段的錯誤,切記
版本選擇:
在HPL/SQL0.3.17版本(必須是0.3.17或者0.3.17之后的版本)解決了強(qiáng)制讀From dual表的問題, 而本次安裝的是Hive2.1.1版本自帶的是HPLSQL0.3.31版本, 已解決強(qiáng)制讀From dual表的問題.
若要解決強(qiáng)制讀From dual表的問題,應(yīng)下載一個0.3.17或0.3.17之后版本的HPL/SQL, 然后將解壓后得到的hplsql-0.3.17.jar包放入$HIVE_HOME/lib包下, 并重命名為hive-hplsql-*.jar格式的包,如:hive-hplsql-0.3.17.jar
</br>
</br>
1.編輯hive-site.xml文件
HPL/SQL與Hive是通過thrift方式連接, 編輯hive-site.xml, 添加以下配置項
<property>
<name>hive.server2.thrift.bind.host</name>
<value>m1</value>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
</br>
2.編輯hplsql-site.xml文件
配置HPL/SQL與Hive的連接, 創(chuàng)建hplsql-site.xml文件(若已有則無需創(chuàng)建), 并將以下配置項拷貝到文件中
<configuration>
<property>
<name>hplsql.conn.default</name>
<value>hive2conn</value>
<description>The default connection profile</description>
</property>
<property>
<name>hplsql.conn.hiveconn</name>
<value>org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://</value>
<description>Hive embedded JDBC (not requiring HiveServer)</description>
</property>
<!-- 配置項hive.execution.engine默認(rèn)設(shè)置為mr,若使用spark作為引擎時,則設(shè)置為spark -->
<property>
<name>hplsql.conn.init.hiveconn</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.convert.hiveconn</name>
<value>true</value>
<description>Convert SQL statements before execution</description>
</property>
<property>
<name>hplsql.conn.hive2conn</name>
<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://m1:10000</value>
<description>HiveServer2 JDBC connection</description>
</property>
<!-- 配置項hive.execution.engine默認(rèn)設(shè)置為mr,若使用spark作為引擎時,則設(shè)置為spark -->
<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.convert.hive2conn</name>
<value>true</value>
<description>Convert SQL statements before execution</description>
</property>
<property>
<name>hplsql.conn.db2conn</name>
<value>com.ibm.db2.jcc.DB2Driver;jdbc:db2://localhost:50001/dbname;user;password</value>
<description>IBM DB2 connection</description>
</property>
<property>
<name>hplsql.conn.tdconn</name>
<value>com.teradata.jdbc.TeraDriver;jdbc:teradata://localhost/database=dbname,logmech=ldap;user;password</value>
<description>Teradata connection</description>
</property>
<property>
<name>hplsql.conn.mysqlconn</name>
<value>com.mysql.jdbc.Driver;jdbc:mysql://localhost/test;user;password</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>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>/home/centos/soft/hive/tmp/plhql</value>
<description>LOcation for managed temporary tables in HDFS</description>
</property>
<!-- 下面兩項需要按實際情況修改 -->
<property>
<name>hive.server2.thrift.bind.host</name>
<value>m1</value>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
</configuration>
</br>
3.配置dual表 (此步驟可跳過)
啟動Hive服務(wù),依照在hplsql-site.xml文件中的配置去創(chuàng)建(默認(rèn)是在default庫中創(chuàng)建了dual表)
use default;
create table dual(DUMMY VARCHAR(1));
</br>
4.在使用hplsql存儲過程前, 需先啟動HiveServer2和Metastore服務(wù)
sh $HIVE_HOME/bin/hive --service metastore
sh $HIVE_HOME/bin/hive --service hiveserver2
</br>
</br>
</br>
