HiveServer2 Clients连接hplsql
1.解压并配置环境变量 解压安装文件到/opt目录
$ tar -zxvf hplsql-0.3.13.tar.gz -C /opt
$ ln -s /opt/hplsql-0.3.13 /opt/hplsql
但我的路径:/usr/local/Cellar/hadoop/hplsql-0.3.31
修改环境变量
$ sudo vi /etc/profile
$ tar -zxvf hplsql-0.3.13.tar.gz -C /opt
$ ln -s /opt/hplsql-0.3.13 /opt/hplsql
但我的路径:/usr/local/Cellar/hadoop/hplsql-0.3.31
修改环境变量
$ sudo vi /etc/profile
export HADOOP_HOME=/usr/local/Cellar/hadoop/2.8.2/libexec
export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$HADOOP_HOME/bin:$PATH
export HADOOP_INSTALL=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export YARN_HOME=$HADOOP_HOME
HIVE_HOME=/usr/local/Cellar/apache-hive-2.3.2-bin
export PATH=$HIVE_HOME/bin:$PATH
export HPLSQL_HOME=/usr/local/Cellar/hadoop/hplsql-0.3.31/hplsql
export PATH=$HPLSQL_HOME/bin:$PATH
source /etc/profile
修改hplsql-site.xml配置文件
$ vim hplsql-site.xml 里面的内容如下
<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.hive.jdbc.HiveDriver;jdbc:hive2://</value>
<description>HiveServer2 JDBC connection (embedded mode)</description>
</property>
<property>
<name>hplsql.conn.init.hiveconn</name>
<value>
</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.hive1conn</name>
<value>org.apache.hadoop.hive.jdbc.HiveDriver;jdbc:hive://</value>
<description>Hive embedded JDBC (not requiring HiveServer)</description>
</property>
<!--Hive连接配置>
<property>
<name>hplsql.conn.hive2conn</name>
<value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://127.0.0.1:10000;root;root</value>
<description>HiveServer2 JDBC connection</description>
</property>
<!--Hive连接初始化语句-->
<property>
<name>hplsql.conn.init.hive2conn</name>
<value>
set mapred.job.queue.name=hive;
set hive.execution.engine=hive;
use hive;
</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/imooc;root;root</value>
<description>MySQL connection</description>
</property>
<property>
<name>hplsql.dual.table</name>
<value></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>
$ vi hplsql 修改内容如下:
$ vi hplsql
修改内容如下:
#!/bin/bash
#!/bin/bash
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/hadoop/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/hadoop/libexec/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/hadoop/libexec/etc/hadoop"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/hadoop/libexec/share/hadoop/common/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HADOOP_HOME/hadoop/libexec/share/hadoop/common/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/libexec/share/hadoop/mapreduce/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/libexec/share/hadoop/mapreduce/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/libexec/share/hadoop/hdfs/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/libexec/share/hadoop/hdfs/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/libexec/share/hadoop/yarn/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/libexec/share/hadoop/yarn/lib/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/usr/local/Cellar/hive/2.3.1/libexec/*"
export "HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/usr/local/Cellar/hive/2.3.1/libexec/conf"
#export HADOOP_OPTS="$HADOOP_OPTS -Djava.library.path=/usr/lib/hadoop/lib/native"
SCRIPTPATH=${0%/*}
java -cp $SCRIPTPATH:$HADOOP_CLASSPATH:$SCRIPTPATH/hplsql-0.3.31.jar:$SCRIPTPATH/antlr-runtime-4.5.jar $HADOOP_OPTS org.apache.hive.hplsql.Hplsql "$@"
hive --service hiveserver2
启动后测试是否连接上:
不用写jdbc程序,运行 bin/beeline.sh
然后输入 !connect jdbc:hive2://上面设置的ip地址:10000 hiveuser hiveuser 后面两个是你创建的用户名和密码
如果能连接上就表示 jdbc没有问题了
注: !connect jdbc:hive2://localhost:10000 hiveuser hiveuser 这里不要使用localhost,应该使用配置的ip
./beeline -u 'jdbc:hive2://localhost:10000/hive' -n mac 注意:hive是数据库名,mac是用户名,大部分人是root,而我的电脑是mac
若连接上了会出现下面的显示
Connected to: Apache Hive (version 2.3.1)
Driver: Hive JDBC (version 2.3.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.3.1 by Apache Hive
连接上了,再看是否显示的是Hive下面的表,即检查是否是连接在hive的数据仓库,上面连接上了会显示
jdbc:hive2://localhost:10000/hive> 再用Hive的命令查看表即可
jdbc:hive2://localhost:10000/hive> show tables;
Beeline连接上了,验证hplsql是否连接上,在hplsql的文件夹下执行下面命令,若输出系统现在正确的时间则表示成功
hplsql -e "CURRENT_DATE+1"
hplsql验证数据库下面的表是否连接正确
hplsql -e "SELECT * FROM users LIMIT 10" 或者执行文件 hplsql -f script.sql
连接的数据库是正确的,那么执行hplsql语句应该就是该数据库里的表,我的要加上数据库名才显示正确
hplsql -e "select * from hive.person"
显示正确如下:
Open connection: jdbc:hive2://localhost:10000 (1.19 sec)
Starting query
Query executed successfully (8.05 sec)
配置当中,遇见错误修改如下:
使用HiveServer2 and Beeline模式运行时,启动好HiveServer后运行
beeline -u jdbc:hive2://localhost:10000 -n root 连接server时
出现
java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException):
User root is not allowed to impersonate anonymous 错误。
- 修改hadoop 配置文件 etc/hadoop/core-site.xml,加入如下配置项,root是用户,而我的用户是mac
<property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value></property><property> <name>hadoop.proxyuser.root.groups</name> <value>*</value></property>
hadoop.proxyuser.root.hosts配置项名称中root部分为报错User:* 中的用户名部分
例如User: zhaoshb is not allowed to impersonate anonymous则需要将xml变更为如下格式
<property> <name>hadoop.proxyuser.zhaoshb.hosts</name> <value>*</value></property><property> <name>hadoop.proxyuser.zhaoshb.groups</name> <value>*</value></property>
- 重启hadoop
- 测试:./beeline -u 'jdbc:hive2://localhost:10000/userdb' -n username(替换为上述的用户名部分),userdb是hive中使用的数据库名称,而我的是hive,username为mac.故我的如下:
- ./beeline -u 'jdbc:hive2://localhost:10000/hive' -n mac
使用hplsql的步骤:
要使用hplsql,则要先启动下面两个才可以使用:因为它是基本hadoop平台的,连接hive的1. 启动hadoop,要使下面两个网站都可以打开才可用,且用jpsg看datanode和namenode,secondarynode有无启动
http://localhost:50070/dfshealth.html#tab-overview
http://localhost:8088/cluster
若未启动,则用format格式化,但一格式化hive数据仓库中的表里的数据都为0
2.在hive的路径下启动hive2
hive --service hiveserver2
若未启动成功,用beeline查看
beeline -u jdbc:hive2://localhost:10000 -n mac
或者下面:加上数据库hive
./beeline -u 'jdbc:hive2://localhost:10000/hive' -n mac
3.最后启动hplsql,在hplsql路径下
hplsql -e "CURRENT_DATE+1"
或者查看hive数据库中的表
hplsql -e "select * from hive.person"