Use MySQL as the Metastore of Hive

First make sure that you have successfully installed HIVE and MYSQL to add the following content in the hive-site.xml, specify the METASTORE address and connection method lt; propertygt; lt;

First make sure that you have successfully installed HIVE and MYSQL to add the following content in the hive-site.xml, specify the METASTORE address and connection method lt; propertygt; lt;

First, make sure that you have successfully installed HIVE and MYSQL

Add the following to the hive-site.xml to specify the METASTORE address and Connection Method

Javax. jdo. option. ConnectionURL
Jdbc: mysql: // 10.20.151.10: 3306/hive? CharacterEncoding = UTF-8
JDBC connect string for a JDBC metastore


Javax. jdo. option. ConnectionDriverName
Com. mysql. jdbc. Driver
Driver class name for a JDBC metastore


Javax. jdo. option. ConnectionUserName
Hive_user
Username to use against metastore database


Javax. jdo. option. ConnectionPassword
123
Password to use against metastore database

Log on to the HIVE client and create a table.

[Gpadmin1 @ Hadoop5 hive-0.6.0] $ bin/hive
Hive history file =/tmp/gpadmin1/hive_job_log_gpadmin1_201106081130_1156785421.txt
Hive> show tables;
FAILED: Error in metadata: javax. jdo. JDOFatalDataStoreException: Unknown database 'hive'
NestedThrowables:
Com. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: Unknown database 'hive'
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exe c. DDLTask

When an error is reported, the prompt is obvious. The database named hive cannot be identified. Do you want to create it yourself? Try

[Intranet root @/var/lib/mysql]
# Mysql-u root-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 41
Server version: 5.5.12 MySQL Community Server (GPL)

Copyright (c) 2000,201 0, 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 |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)

Mysql> create database hive;
Query OK, 1 row affected (0.00 sec)

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Hive |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)

Log on to HIVE and check it out.

[Gpadmin1 @ hadoop5 hive-0.6.0] $ bin/hive
Hive history file =/tmp/gpadmin1/hive_job_log_gpadmin1_201106081130_544334815.txt
Hive> show table;
FAILED: Parse Error: line 0:-1 mismatched input' 'Expecting EXTENDED in show statement

Hive> show tables;
OK
Time taken: 5.173 seconds
Hive> create table u_tmp1 (id1 INT,
> Id2 int
>)
> ROW FORMAT DELIMITED
> Fields terminated ',';
OK
Time taken: 0.266 seconds
Hive> show tables;
OK
U_tmp1
Time taken: 0.197 seconds
Hive>

OK. This is the problem.