在Hive中执行DDL之类的SQL语句时遇到的一个问题
在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 '123456'; 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 '123456'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on hive.* to hive@' localhost' identified by '123456'; 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('001'); 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包的版本，但是貌似都不起作用。
[文章结束]
