在Hive中执行DDL之类的SQL语句时遇到的一个问题

作者：天齐

遇到的问题如下：

hive> create table ehr_base(id string);

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException
(message:For direct MetaStore DB connections, we don't support retries at the client level.)

在解决此问题之前，先看一下Hive的安装过程。

一、Hive的安装

将Hive的安装包"apache-hive-1.2.1-bin.tar.gz"上传到linux服务器。

解压安装包，将解压后的目录重命名为hive，进入到hive/conf目录，创建hive-site.xml文件：

[root@hadoop01 apps]# ls apache-hive-1.2.1-bin.tar.gz

apache-hive-1.2.1-bin.tar.gz

[root@hadoop01 apps]# tar -zxvf apache-hive-1.2.1-bin.tar.gz

[root@hadoop01 apps]# mv apache-hive-1.2.1-bin hive

[root@hadoop01 apps]# cd hive/

[root@hadoop01 hive]# ll

total 476

drwxr-xr-x. 3 root root   4096 Jan 31 22:07 bin

drwxr-xr-x. 2 root root   4096 Jan 31 23:33 conf

drwxr-xr-x. 4 root root   4096 Jan 31 22:07 examples

drwxr-xr-x. 7 root root   4096 Jan 31 22:07 hcatalog

drwxr-xr-x. 4 root root   4096 Jan 31 23:39 lib

-rw-rw-r--. 1 root root  24754 Apr 29  2015 LICENSE

-rw-rw-r--. 1 root root    397 Jun 19  2015 NOTICE

-rw-rw-r--. 1 root root   4366 Jun 19  2015 README.txt

-rw-rw-r--. 1 root root 421129 Jun 19  2015 RELEASE_NOTES.txt

drwxr-xr-x. 3 root root   4096 Jan 31 22:07 scripts

[root@hadoop01 apps]# cd hive/

[root@hadoop01 hive]# ll

total 476

drwxr-xr-x. 3 root root   4096 Jan 31 22:07 bin

drwxr-xr-x. 2 root root   4096 Jan 31 23:33 conf

drwxr-xr-x. 4 root root   4096 Jan 31 22:07 examples

drwxr-xr-x. 7 root root   4096 Jan 31 22:07 hcatalog

drwxr-xr-x. 4 root root   4096 Jan 31 23:39 lib

-rw-rw-r--. 1 root root  24754 Apr 29  2015 LICENSE

-rw-rw-r--. 1 root root    397 Jun 19  2015 NOTICE

-rw-rw-r--. 1 root root   4366 Jun 19  2015 README.txt

-rw-rw-r--. 1 root root 421129 Jun 19  2015 RELEASE_NOTES.txt

drwxr-xr-x. 3 root root   4096 Jan 31 22:07 scripts

[root@hadoop01 hive]# cd conf/

[root@hadoop01 conf]# vi hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<!--

   Licensed to the Apache Software Foundation (ASF) under one or more

   contributor license agreements.  See the NOTICE file distributed with

   this work for additional information regarding copyright ownership.

   The ASF licenses this file to You under the Apache License, Version 2.0

   (the "License"); you may not use this file except in compliance with

   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software

   distributed under the License is distributed on an "AS IS" BASIS,

   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

   See the License for the specific language governing permissions and

   limitations under the License.

-->

<configuration>

        <property>

          <name>javax.jdo.option.ConnectionURL</name>

          <value>jdbc:mysql://hadoop03:3306/hive?createDatabaseIfNotExist=true</value>

          <description>JDBC connect string for a JDBC metastore</description>

        </property>

        <property>

          <name>javax.jdo.option.ConnectionDriverName</name>

          <value>com.mysql.jdbc.Driver</value>

          <description>Driver class name for a JDBC metastore</description>

        </property>

        <property>

          <name>javax.jdo.option.ConnectionUserName</name>

          <value>hive</value>

          <description>username to use against metastore database</description>

        </property>

        <property>

          <name>javax.jdo.option.ConnectionPassword</name>

          <value>123456</value>

          <description>password to use against metastore database</description>

        </property>

</configuration>

二、拷贝MySQL驱动jar包到hive

[root@hadoop01 lib]# pwd

/root/apps/hive/lib

[root@hadoop01 lib]# ls mysql-connector-java-5.1.39.jar

mysql-connector-java-5.1.39.jar

[root@hadoop01 lib]#

三、在MySQL中创建Hive用户并授权

[root@hadoop01 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'hive'@'%' IDENTIFIED BY '';

Query OK, 0 rows affected (0.12 sec)

mysql> GRANT all privileges ON hive.* TO 'hive'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit;

Bye

[root@hadoop01 ~]# mysql -uhive -p

Enter password:

ERROR 1045 (28000): Access denied for user 'hive'@'localhost' (using password: YES)

[root@hadoop01 ~]# mysql -uhive -p123456

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'hive'@'localhost' (using password: YES)

[root@hadoop01 ~]# mysql -u hive -p 123456

Enter password:

ERROR 1045 (28000): Access denied for user 'hive'@'localhost' (using password: YES)

[root@hadoop01 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on hive.* to hive@' gc.localdomain' identified by '';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on hive.* to hive@' localhost' identified by '';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit;

Bye

[root@hadoop01 ~]# mysql -uhive -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.06 sec)

mysql>

四、启动Hive并建库建表

在创建表的时候报如下错误（其他类似DDL的sql语句都会报此错误）：

[root@hadoop01 hive]# pwd

/root/apps/hive

[root@hadoop01 hive]# bin/hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/root/apps/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/root/apps/spark/lib/spark-assembly-1.6.2-hadoop2.6.0.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.slf4j.impl.Log4jLoggerFactory]

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/root/apps/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/root/apps/spark/lib/spark-assembly-1.6.2-hadoop2.6.0.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.slf4j.impl.Log4jLoggerFactory]

Logging initialized using configuration in jar:file:/root/apps/hive/lib/hive-common-1.2.1.jar!/hive-log4j.properties

hive> show databases;

OK

default

Time taken: 3.135 seconds, Fetched: 1 row(s)

hive> create database db1;

OK

Time taken: 0.461 seconds

hive> show databases;

OK

db1

default

Time taken: 0.105 seconds, Fetched: 2 row(s)

hive> use db1;

OK

Time taken: 0.076 seconds

hive> create table tb_1(id string);

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException

(message:For direct MetaStore DB connections, we don't support retries at the client level.)

五、解决办法

经过网络查询，说是MySQL中的hive数据库字符编码有问题，需要连接mysql服务器，并执行SQL语句修改hive数据库的字符编码：

[root@hadoop01 soft]# mysql -uhive -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 27

Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter database hive character set latin1;

Query OK, 1 row affected (0.00 sec)

mysql>

再次建表，问题解决：

hive> create table tb_1(id string);

OK

Time taken: 3.018 seconds

hive> show tables;

OK

tb_1

Time taken: 0.13 seconds, Fetched: 1 row(s)

hive> show tables;

OK

tb_1

Time taken: 0.13 seconds, Fetched: 1 row(s)

hive> insert into tb_1 values('');

Query ID = root_20170131232220_8dd5fa31-97b3-4688-9110-a59840064045

Total jobs = 3

Launching Job 1 out of 3

Number of reduce tasks is set to 0 since there is no reduce operator

Starting Job = job_1485929365702_0001, Tracking URL = http://hadoop01:8088/proxy/application_1485929365702_0001/

Kill Command = /root/apps/hadoop/bin/hadoop job  -kill job_1485929365702_0001

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0

2017-01-31 23:23:32,336 Stage-1 map = 0%,  reduce = 0%

2017-01-31 23:24:03,840 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.44 sec

MapReduce Total cumulative CPU time: 4 seconds 440 msec

Ended Job = job_1485929365702_0001

Stage-4 is selected by condition resolver.

Stage-3 is filtered out by condition resolver.

Stage-5 is filtered out by condition resolver.

Moving data to: hdfs://ns1/user/hive/warehouse/db1.db/tb_1/.hive-staging_hive_2017-01-31_23-22-20_362_3014216082832716531-1/-ext-10000

Loading data to table db1.tb_1

[Warning] could not update stats.

MapReduce Jobs Launched:

Stage-Stage-1: Map: 1   Cumulative CPU: 4.44 sec   HDFS Read: 3133 HDFS Write: 72 SUCCESS

Total MapReduce CPU Time Spent: 4 seconds 440 msec

OK

Time taken: 130.905 seconds

hive> select * from tb_1;

OK

001

Time taken: 0.377 seconds, Fetched: 1 row(s)

hive>

补充：但是，也有说是mysql的驱动jar包版本有问题，我也更换了几次驱动jar包的版本，但是貌似都不起作用。

[文章结束]

最新文章

  1. [skill] strncpy里边有两个坑
  2. calender 软文
  3. C#的选择语句练习
  4. C#一维数组
  5. ECSHOP始终显示全部分类方法
  6. const,static,extern简介（重要）
  7. &lt;转&gt;写给浮躁的IT同仁(请不要做浮躁的人)
  8. Spark MLBase分布式机器学习系统入门：以MLlib实现Kmeans聚类算法
  9. 解决从VIM复制出来的代码格式错乱或对齐的问题
  10. 在navigationItem中添加搜索栏
  11. Flexible 弹性盒子模型之CSS align-items 属性
  12. SuperSocket+unity 网络笔记
  13. jQuery动态生成不规则表格前后端
  14. 使用JsonConfig控制JSON lib序列化
  15. span是没有value标签的，要向获得标签内部的值改怎么办。
  16. pdf.js 使用实例（app直接预览pdf格式的文档）
  17. Asp.Net Core Web Api图片上传(一)集成MongoDB存储实例教程
  18. ABAP on HANA之CDS Association和Path Expression
  19. JavaScricp（总回顾）
  20. PIVOT 行列相转

热门文章

  1. Windows删除无效服务
  2. Python 图形界面（GUI）设计
  3. Asp.net Mvc (Filter及其执行顺序)
  4. Python之上下文管理器
  5. 去除DataTable重复数据的三种方法（转）
  6. linux配置server笔记
  7. Swift3.0生成二维码、扫描二维码、相册读取二维码，兼容iOS7（结合ZXingObjC）
  8. selenium python学习笔记---添加等待时间
  9. Android SharedPreferences的应用
  10. android适配器Adapter