hive metadata mysql存储

1>首先在mysql上创建一个账户供hive用：

grant all on hive.* to hive@'%' identified by 'hive' with grant option;

2>修改hive-site.xml，增加：

<property>
     <name>javax.jdo.option.ConnectionURL</name>
     <value>jdbc:mysql://MYSQLIP: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>hive</value>
</property>
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>hive</value>
    <description>password to use against metastore database</description>
</property>

3>拷贝myql驱动例如mysql-connector-java-5.1.20.jar到hive的lib目录下

4>启动hive, /bin/hive

运行show tables; 如果出错MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes，那么在mysql机器的上运行:

alter database hive character set latin1;

5>在hive机器的hive命令行里继续创建一个table，例如

create table test (c1 int, c2 int) row format delimited fields terminated by ',';

那么在mysql 数据库里查看，

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

mysql>  select * from TBLS;
+--------+-------------+-------+------------------+--------+-----------+-------+----------+---------------+--------------------+--------------------+
| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER  | RETENTION | SD_ID | TBL_NAME | TBL_TYPE      | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT |
+--------+-------------+-------+------------------+--------+-----------+-------+----------+---------------+--------------------+--------------------+
|      1 |  1358571295 |     1 |                0 | hadoop |         0 |     1 | test     | MANAGED_TABLE | NULL               | NULL               |
+--------+-------------+-------+------------------+--------+-----------+-------+----------+---------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql>