默认情况下，hive的元数据信息存储在内置的Derby数据中。Facebook将hive元数据存储在关系数据库
1、安装好mysql ，sudo apt-get install mysql-server
2、创建mysql用户hadoop
$ mysql -u root -p 进入root用户
mysql> CREATE USER ‘hadoop‘@‘localhost‘ IDENTIFIED BY ‘hadoop‘;
3、授权：mysql> GRANT ALL PRIVILEGES ON *.* TO‘hadoop‘@‘localhost‘ WITH GRANT OPTION;
4、登录到hadoop 用户 $ mysql -u hadoop -p
5、创建数据库hive
mysql>create database hive;
6、修改hive中hive-site.xml
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description>Thrift uri for the
remote metastore. Used by metastore client to connect to remote
metastore.</description>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</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.PersistenceManagerFactoryClass</name>
<value>org.datanucleus.jdo.JDOPersistenceManagerFactory</value>
<description>class implementing the jdo
persistence</description>
</property>
<property>
<name>javax.jdo.option.DetachAllOnCommit</name>
<value>true</value>
<description>detaches all objects
from session so that they can be used after transaction is
committed</description>
</property>
<property>
<name>javax.jdo.option.NonTransactionalRead</name>
<value>true</value>
<description>reads outside of
transactions</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hadoop</value>
<description>username to use
against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hadoop</value>
<description>password to use
against metastore
database</description>
</property>
</configuration>
7、将mysql jdbc driver拷贝到hive的lib下
8、测试：
hive> create table tmp(info int);
OK
Time taken: 0.66
seconds
hive> show tables;
OK
tmp
Time taken: 0.138
seconds
hive> exit;
hadoop@hadoop-VirtualBox:~/hive-0.10.0$ mysql -u
hadoop -p
Enter password:
Welcome to the MySQL monitor. Commands end
with ; or \g.
Your MySQL connection id is 50
Server version:
5.5.37-0ubuntu0.12.10.1 (Ubuntu)
Copyright (c) 2000, 2014, 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> use hive;
Reading table information for completion of table and
column names
You can turn off this feature to get a quicker startup with
-A
Database changed
mysql> show
tables;
+---------------------------+
| Tables_in_hive
|
+---------------------------+
| BUCKETING_COLS |
| CDS
|
| COLUMNS_V2 |
| DATABASE_PARAMS
|
| DBS |
| PARTITION_KEYS |
| SDS
|
| SD_PARAMS |
| SEQUENCE_TABLE
|
| SERDES |
| SERDE_PARAMS |
|
SKEWED_COL_NAMES |
| SKEWED_COL_VALUE_LOC_MAP |
|
SKEWED_STRING_LIST |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES
|
| SORT_COLS |
| TABLE_PARAMS
|
| TBLS |
+---------------------------+
19 rows
in set (0.01 sec)
mysql> select * from
COLUMNS_V2;
+-------+---------+-------------+-----------+-------------+
|
CD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX
|
+-------+---------+-------------+-----------+-------------+
| 1 |
NULL | info | int | 0
|
+-------+---------+-------------+-----------+-------------+
1 row in set
(0.01 sec)
mysql> select * from tbls;
ERROR 1146 (42S02): Table ‘hive.tbls‘
doesn‘t exist
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 | 1399452288 | 1 | 0 | hadoop | 0 | 1 |
tmp | MANAGED_TABLE | NULL | NULL
|
+--------+-------------+-------+------------------+--------+-----------+-------+----------+---------------+--------------------+--------------------+
1
row in set (0.02 sec)
mysql> exit;