hive-mysql安装配置

默认情况下，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

hive.metastore.local
true
Thrift uri for the remote metastore. Used by metastore client to connect to remote metastore.

javax.jdo.option.ConnectionURL
jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true
JDBC connect string for a JDBC metastore

javax.jdo.option.ConnectionDriverName
com.mysql.jdbc.Driver
Driver class name for a JDBC metastore

javax.jdo.PersistenceManagerFactoryClass
org.datanucleus.jdo.JDOPersistenceManagerFactory
class implementing the jdo persistence

javax.jdo.option.DetachAllOnCommit
true
detaches all objects from session so that they can be used after transaction is committed

javax.jdo.option.NonTransactionalRead
true
reads outside of transactions

javax.jdo.option.ConnectionUserName
hadoop
username to use against metastore database

javax.jdo.option.ConnectionPassword
hadoop
password to use against metastore database

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;