Hive-mysql installation Configuration

By default, the metadata information for hive is stored in the built-in Derby data. Facebook stores hive metadata in a relational database

1. Installing MySQL, sudo apt-get install Mysql-server

2. Create MySQL user Hadoop

$ mysql-u root-p into root user

mysql> CREATE USER ' hadoop ' @ ' localhost ' identified by ' Hadoop ';

3. Authorized:mysql> Grant all privileges on * * to ' hadoop ' @ ' localhost ' with GRANT OPTION;

4. Login to Hadoop user $ mysql-u hadoop-p

5. Create DATABASE Hive

Mysql>create database hive;

6. Modify Hive-site.xml in Hive

<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 to session so, they can be used after transaction is Committed</descripti On>
</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. Copy MySQL JDBC driver to 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;
[Email protected]:~/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), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names 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
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 |
+---------------------------+
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;