通过Hive创建数据库成功，但没有在Mysql找到对应的test_db

Question

使用的是老师提供的OOTB环境，配置的话，环境里本来就配置好了，因此我只是确认一下配置没有错，就开始尝试Hive操作了
目录：/home/hadoop/app/hive-1.1.0-cdh5.15.1/lib
下有mysql-connector-java-5.1.27-bin.jar

启动hive并创建数据库

[hadoop@hadoop000 conf]$ hive
which: no hbase in (/home/hadoop/app/hive-1.1.0-cdh5.15.1/bin:/home/hadoop/app/hadoop-2.6.0-cdh5.15.1/bin:/home/hadoop/app/jdk1.8.0_91/bin:/home/hadoop/app/hive-1.1.0-cdh5.15.1/bin:/home/hadoop/app/hadoop-2.6.0-cdh5.15.1/bin:/home/hadoop/app/jdk1.8.0_91/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/hadoop/.local/bin:/home/hadoop/bin:/home/hadoop/.local/bin:/home/hadoop/bin)

Logging initialized using configuration in jar:file:/home/hadoop/app/hive-1.1.0-cdh5.15.1/lib/hive-common-1.1.0-cdh5.15.1.jar!/hive-log4j.properties
WARNING: Hive CLI is deprecated and migration to Beeline is recommended.
hive> create database test_db;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database test_db already exists
hive> quit;

进入Mysql查看数据库

[hadoop@hadoop000 conf]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.6.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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.00 sec)

mysql> 

查看Hive数据库

hive> show databases;
OK
default
hive
hive2
test_db
Time taken: 0.251 seconds, Fetched: 4 row(s)
hive> 

找到Hive的日志：/tmp/hadoop/hive.log

2021-04-27 17:20:03,490 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created HDFS directory: /tmp/hive/hadoop
2021-04-27 17:20:03,517 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created local directory: /tmp/38570fcf-9a53-4628-b1b0-83d08775ac93_resources
2021-04-27 17:20:03,521 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created HDFS directory: /tmp/hive/hadoop/38570fcf-9a53-4628-b1b0-83d08775ac93
2021-04-27 17:20:03,523 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created local directory: /tmp/hadoop/38570fcf-9a53-4628-b1b0-83d08775ac93
2021-04-27 17:20:03,526 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created HDFS directory: /tmp/hive/hadoop/38570fcf-9a53-4628-b1b0-83d08775ac93/_tmp_space.db
2021-04-27 17:20:03,529 INFO  [main]: session.SessionState (SessionState.java:start(587)) - No Tez session required at this point. hive.execution.engine=mr.
2021-04-27 17:21:27,140 INFO  [main]: ql.Driver (Driver.java:compile(499)) - Compiling command(queryId=hadoop_20210427172121_f1e6fd80-0011-4573-962a-9a068a8112ef): create database test_db
2021-04-27 17:21:32,476 INFO  [main]: ql.Driver (Driver.java:compile(572)) - Semantic Analysis Completed
2021-04-27 17:21:32,478 INFO  [main]: ql.Driver (Driver.java:getSchema(305)) - Returning Hive schema: Schema(fieldSchemas:null, properties:null)
2021-04-27 17:21:32,483 INFO  [main]: ql.Driver (Driver.java:compile(692)) - Completed compiling command(queryId=hadoop_20210427172121_f1e6fd80-0011-4573-962a-9a068a8112ef); Time taken: 5.76 seconds
2021-04-27 17:21:32,483 INFO  [main]: ql.Driver (Driver.java:checkConcurrency(225)) - Concurrency mode is disabled, not creating a lock manager
2021-04-27 17:21:32,483 INFO  [main]: ql.Driver (Driver.java:execute(1658)) - Executing command(queryId=hadoop_20210427172121_f1e6fd80-0011-4573-962a-9a068a8112ef): create database test_db
2021-04-27 17:21:32,491 INFO  [main]: ql.Driver (Driver.java:launchTask(2052)) - Starting task [Stage-0:DDL] in serial mode
2021-04-27 17:21:32,502 WARN  [main]: metastore.ObjectStore (ObjectStore.java:getDatabase(628)) - Failed to get database test_db, returning NoSuchObjectException
2021-04-27 17:21:32,693 INFO  [main]: ql.Driver (Driver.java:execute(1960)) - Completed executing command(queryId=hadoop_20210427172121_f1e6fd80-0011-4573-962a-9a068a8112ef); Time taken: 0.21 seconds
2021-04-27 17:21:32,693 INFO  [main]: ql.Driver (SessionState.java:printInfo(1087)) - OK
2021-04-27 17:25:23,177 INFO  [main]: session.SessionState (SessionState.java:dropPathAndUnregisterDeleteOnExit(792)) - Deleted directory: /tmp/hive/hadoop/38570fcf-9a53-4628-b1b0-83d08775ac93 on fs with scheme hdfs
2021-04-27 17:25:23,182 INFO  [main]: session.SessionState (SessionState.java:dropPathAndUnregisterDeleteOnExit(792)) - Deleted directory: /tmp/hadoop/38570fcf-9a53-4628-b1b0-83d08775ac93 on fs with scheme file
2021-04-27 17:29:09,934 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created local directory: /tmp/7ef00e5a-30e9-4d25-83cb-de1be5ba2e00_resources
2021-04-27 17:29:09,944 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created HDFS directory: /tmp/hive/hadoop/7ef00e5a-30e9-4d25-83cb-de1be5ba2e00
2021-04-27 17:29:09,947 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created local directory: /tmp/hadoop/7ef00e5a-30e9-4d25-83cb-de1be5ba2e00
2021-04-27 17:29:09,952 INFO  [main]: session.SessionState (SessionState.java:createPath(703)) - Created HDFS directory: /tmp/hive/hadoop/7ef00e5a-30e9-4d25-83cb-de1be5ba2e00/_tmp_space.db
2021-04-27 17:29:09,954 INFO  [main]: session.SessionState (SessionState.java:start(587)) - No Tez session required at this point. hive.execution.engine=mr.
2021-04-27 17:29:52,899 INFO  [main]: ql.Driver (Driver.java:compile(499)) - Compiling command(queryId=hadoop_20210427172929_df656480-ab95-4d4b-b5d8-de69a731a0c3): create database test_db
2021-04-27 17:29:56,069 INFO  [main]: ql.Driver (Driver.java:compile(572)) - Semantic Analysis Completed
2021-04-27 17:29:56,072 INFO  [main]: ql.Driver (Driver.java:getSchema(305)) - Returning Hive schema: Schema(fieldSchemas:null, properties:null)
2021-04-27 17:29:56,078 INFO  [main]: ql.Driver (Driver.java:compile(692)) - Completed compiling command(queryId=hadoop_20210427172929_df656480-ab95-4d4b-b5d8-de69a731a0c3); Time taken: 3.198 seconds
2021-04-27 17:29:56,078 INFO  [main]: ql.Driver (Driver.java:checkConcurrency(225)) - Concurrency mode is disabled, not creating a lock manager
2021-04-27 17:29:56,078 INFO  [main]: ql.Driver (Driver.java:execute(1658)) - Executing command(queryId=hadoop_20210427172929_df656480-ab95-4d4b-b5d8-de69a731a0c3): create database test_db
2021-04-27 17:29:56,087 INFO  [main]: ql.Driver (Driver.java:launchTask(2052)) - Starting task [Stage-0:DDL] in serial mode
2021-04-27 17:29:56,105 ERROR [main]: metastore.RetryingHMSHandler (RetryingHMSHandler.java:invokeInternal(193)) - AlreadyExistsException(message:Database test_db already exists)

Answer 1

Michael_PK 2021-04-27 22:26:44

通过Hive创建数据库成功，但没有在Mysql找到对应的test_db。这个是hive表的test_db，mysql怎么会有呢？mysql有的是你hivesitexml文件中指定的数据库名

• 提问者 weixin_慕神5384537 #1

  非常感谢！

• 慕粉1926212165 #2

  我这儿，通过Hive创建数据库成功，但mysql没有hive-site.xml文件中指定的数据库名 ？

• Michael_PK 回复 慕粉1926212165 #3

  我总结下，几个步骤：
  1） hive-site的配置
  2） mysql 驱动包
  3）mysql一定要有远程访问的权限，也就是要在mysql中进行对用户的赋权操作

Answer 2

Michael_PK 2021-04-27 22:25:47

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database test_db already exists

已经提示你这数据库已经存在了呢

还有一点：你的hivesite配置了吗？