hive2.0.1执行存储过程

hadoop 同时被 2 个专栏收录
5 篇文章 0 订阅
12 篇文章 0 订阅

1、编写过程sql
基于上篇文章的test_db库,vi test.sql,新增:

use test_db;
begin
insert into t_test2(id,name) values(2,'你好');
insert into t_test2(id,name) values(3,'你好');
insert into t_test2(id,name) values(4,'你好');
delete from t_test2 where id=1;
select * from t_test2;
end;

2、执行sql

hplsql -f test.sql,报错:
java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000: java.net.ConnectException: Connection refused (Connection refused)
...
Caused by: java.net.ConnectException: Connection refused (Connection refused)

3、解决方案
在hive的conf下新建 hplsql-site.xml 并添加以下内容,即

vi /data/server/apache-hive-2.0.1-bin/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>
  <!--设置hive节点的主机名,配置用户名密码(用户名建议写root,密码随意写)-->
 <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://node1:10000;root;123456</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>
  <!--数据库连接格式 com.mysql.jdbc.Driver;jdbc:mysql://hive的数据库IP:hive的数据库端口/hive库名;数据库用户名;数据库密码-->
  <value>com.mysql.jdbc.Driver;jdbc:mysql://192.168.1.166:3306/hive;root;123456</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>

4、重新执行sql

[root@node1 ~]# hplsql -f test.sql
which: no hbase in (/usr/lib64/qt-3.3/bin:/data/server/apache-hive-2.0.1-bin/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/java/jdk1.8.0_172-amd64/bin:/data/server/hadoop-2.6.0/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/java/jdk1.8.0_172-amd64/bin:/data/server/hadoop-2.6.0/Sbin:/root/bin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/data/server/apache-hive-2.0.1-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/data/server/hadoop-2.6.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Open connection: jdbc:hive2://node1:10000 (238 ms)
Starting SQL statement
SQL statement executed successfully (238 ms)
Starting SQL statement
SQL statement executed successfully (238 ms)
Starting SQL statement
SQL statement executed successfully (238 ms)
Starting SQL statement
SQL statement executed successfully (238 ms)
Starting SQL statement
SQL statement executed successfully (238 ms)
Starting query
Query executed successfully (216 ms)
2    你好
4    你好
3    你好

以上,成功!

参考资料:

http://lxw1234.com/archives/2015/09/490.htm

https://blog.csdn.net/smile0198/article/details/52343997

  • 0
    点赞
  • 1
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值