Use MYSQL as the MetaStore of hive

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

 

<Property>
<Name> javax. JDO. Option. connectionurl </Name>
<Value> JDBC: mysql: // 10.20.151.10: 3306/hive? Characterencoding = UTF-8 </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_user </value>
<Description> username to use against MetaStore database </description>
</Property>
<Property>
<Name> javax. JDO. Option. connectionpassword </Name>
<Value> 123 </value>
<Description> password to use against MetaStore database </description>
</Property>

 

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 @ hadoop6/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 '<EOF> '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.

In addition, hive creates tables in the database to store metadata. We can see which tables are available.

Mysql> Use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> show tables;
+ ---------------- +
| Tables_in_hive |
+ ---------------- +
| Bucketing_cols |
| Columns |
| DBS |
| Partition_keys |
| SDS |
| Sd_params |
| Sequence_table |
| SerDes |
| Serde_params |
| Sort_cols |
| Table_params |
| Tsung |
+ ---------------- +
12 rows in SET (0.00 Sec)
Mysql> select * From Tsung;
+ -------- + ------------- + ------- + ---------------- + ---------- + ----------- + ------- + ---------- + ----------------- + ------------------ +
| Tbl_id | create_time | db_id | last_access_time | Owner | retention | sd_id | tbl_name | tbl_type | view_expanded_text | view_original_text |
+ -------- + ------------- + ------- + ---------------- + ---------- + ----------- + ------- + ---------- + ----------------- + ------------------ +
| 1 | 1307504073 | 1 | 0 | gpadmin1 | 0 | 1 | u_tmp1 | managed_table | null |
+ -------- + ------------- + ------- + ---------------- + ---------- + ----------- + ------- + ---------- + ----------------- + ------------------ +
1 row in SET (0.00 Sec)

Mysql>

 

The table u_tmp1 we just created can also be found.

 

In addition, there is a need to pay attention to the need to copy a jar package mysql-connector-java-5.1.15-bin.jar to the lib directory of hive, otherwise the execution of the statement will report an error, similar to the following

Hive> show tables;
Failed: Error in metadata: javax. JDO. jdofatalinternalexception: Error creating transactional connection Factory
Nestedthrowables:
Java. Lang. Reflect. invocationtargetexception
Failed: execution error, return code 1 from org.apache.hadoop.hive.ql.exe C. ddltask

 

I used RPM to install MySQL at the time, but I didn't find the mysql-connector-java-5.1.15-bin.jar. My colleague sent me a compressed package and decompressed it.