MYSQL HIVE HADOOP

How to Configure MySQL Metastore for Hive? 

 

Hive by default comes with Derby as its metastore storage, which is suited only for testing purposes and in most of the production scenarios it is recommended to use MySQL as a metastore. This is a step by step guide on How to Configure MySQL Metastore for Hive in place of Derby Metastore (Default).
Assumptions : Basic knowledge of Unix is assumed and also It’s assumed that Hadoop and Hive configurations are in place.Hive with default metastore Derby is properly configured and tested out.

1-Install  MySQL - 

$ sudo apt-get install mysql-server

2. Install the MySQL Java Connector –

$ sudo apt-get install libmysql-java

ln -s /usr/share/java/mysql-connector-java.jar $HIVE_HOME/lib/mysql-connector-java.jar

4. Create the Initial database schema using the hive-schema-0.14.0.mysql.sql file ( or the file corresponding to your installed version of Hive) located in the $HIVE_HOME/scripts/metastore/upgrade/mysql directory.

mysql -u root -p

Enter password:

mysql> CREATE DATABASE metastore;

mysql> USE metastore;

mysql> SOURCE /usr/local/hive/scripts/metastore/upgrade/mysql/hive-schema-0.14.0.mysql.sql;

5. You also need a MySQL user account for Hive to use to access the metastore. It is very important to prevent this user account from creating or altering tables in the metastore database schema.

mysql> CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'hivepassword'; 

mysql> GRANT ALLon *.* to 'hiveuser'@localhost identified by 'hivepassword';

mysql>  flush privileges;

6. Create hive-site.xml ( If not already present) in $HIVE_HOME/conf folder with the configuration below –

 <configuration>

<property>

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>

<description>metadata is stored in a MySQL server</description

</property>

<property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

<description>MySQL JDBC driver class</description>

</property>

<property>

<name>javax.jdo.option.ConnectionUserName</name>

<value>hiveuser</value>

<description>user name for connecting to mysql server</description>

</property>

<property>

<name>javax.jdo.option.ConnectionPassword</name>

<value>hivepassword</value>

<description>password for connecting to mysql server</description>

</property>

</configuration>

7. We are all set now. Start Hadoop and the hive console.