Install and configure hive-mysql

By default, hive metadata is stored in the built-in Derby data. Facebook stores hive metadata in relational databases

1. install mysql and sudo apt-get install mysql-server

2. create a mysql User hadoop

$ Mysql-u root-p: Enter the root user

Mysql> create user 'hadoop '@ 'localhost' identified by 'hadoop ';

3. authorization: mysql> grant all privileges on *. * TO 'hadoop '@ 'localhost' with grant option;

4. log on to the hadoop user $ mysql-u hadoop-p

5. create a database hive

Mysql> create database hive;

6. modify the hive-site.xml in hive.

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. copy the mysql jdbc driver to the hive lib.

8. test:

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,201 4, 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-

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 |
| Tsung |
+ --------------------------- +
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 Tsung;
ERROR 1146 (42S02): Table 'hive. Tsung 'doesn' t exist
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 | 1399452288 | 1 | 0 | hadoop | 0 | 1 | tmp | MANAGED_TABLE | NULL |
+ -------- + ------------- + ------- + ---------------- + -------- + ----------- + ------- + ------------ + --------------- + ------------------ + -------------------- +
1 row in set (0.02 sec)

Mysql> exit;