使用mysql存储存储hive的metastore

默认情况下，hive的元数据信息存储在内置的Derby数据中。Facebook将hive元数据存储在关系数据库
mysql中。配置过程如下：
1 安装好mysql
创建用户hadoop
afan@ubuntu:/usr/local/hadoop/hive$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.

Commands end with ;or \g.
Your MySQL connection id is 38
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rightsreserved.
This software comes with ABSOLUTELY NO WARRANTY. This is freesoftware,
and you are welcome to modify and redistribute it under the GPL v2license

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

mysql> CREATE USER 'hadoop'@'localhost' IDENTIFIEDBY 'hadoop';
Query OK, 0 rows affected (0.18 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO'hadoop'@'localhost' WITH
-> GRANT OPTION
-> ;
Query OK, 0 rows affected (0.03 sec)
mysql> exit;
Bye

切换用户
afan@ubuntu:/usr/local/hadoop/hive$ mysql -u hadoop -p
Enter password:
Welcome to the MySQL monitor. Commands end with ;or \g.
Your MySQL connection id is 39
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rightsreserved.
This software comes with ABSOLUTELY NO WARRANTY. This is freesoftware,
and you are welcome to modify and redistribute it under the GPL v2license

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

mysql> exit;
Bye

2修改配置文件hive-default.xml
<property>
<name>hive.metastore.local</name>
<value>true</value>
</property>

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>
jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true
</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>Username</value><!--In my case UserName is hadoop-->
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>UserPassword</value><!--In my case password is hadoop-->
</property>





3下载mysql jdbc driver，将其拷贝至hive安装目录lib文件夹下
4测试hive
afan@ubuntu:/usr/local/hadoop/hive/bin$ ./hive
Hive historyfile=/tmp/afan/hive_job_log_afan_201105150340_1990267250.txt
hive> create table temp(info INT);
OK
Time taken: 6.413 seconds
hive> show tables;
OK
temp
Time taken: 0.262 seconds
hive>
5登录mysql，查看hive数据库中的元数据信息
afan@ubuntu:/usr/local/hadoop/hive/bin$ mysql -u hadoop -p
Enter password:
Welcome to the MySQL monitor. Commands end with ;or \g.
Your MySQL connection id is 42
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rightsreserved.
This software comes with ABSOLUTELY NO WARRANTY. This is freesoftware,
and you are welcome to modify and redistribute it under the GPL v2license

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

mysql> show databases;
+--------------------+
|Database |
+--------------------+
| information_schema |
|hive |
|menu |
|mysql |
+--------------------+
4 rows in set (0.05 sec)

mysql> use hive
Reading table information for completion of table and columnnames
You can turn off this feature to get a quicker startup with-A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_hive |
+-----------------+
| BUCKETING_COLS |
|COLUMNS |
| DATABASE_PARAMS |
|DBS |
| PARTITION_KEYS |
|SDS |
|SD_PARAMS |
| SEQUENCE_TABLE |
|SERDES |
|SERDE_PARAMS |
|SORT_COLS |
|TABLE_PARAMS |
|TBLS |
+-----------------+
13 rows in set (0.00 sec)


mysql> select * from COLUMNS;
+-------+---------+-------------+-----------+-------------+
| SD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX |
+-------+---------+-------------+-----------+-------------+
| 1 | NULL |info |int | 0 |
+-------+---------+-------------+-----------+-------------+