HDInsight Emulator - Configuring the Hive Metastore in Local MySQL Database Server

1. Setup and configure MySQL Community Server 5.6.24 Windows (x86, 64-bit).
   Note: Minimum Supported Version is 5.6.17
   
2. Copy MySQL Connector Java Jar to Hive Lib folder.
   Jar Filename: mysql-connector-java-5.1.6.jar
   Hive Lib Folder: C:\hdp\hive-0.13.0.2.1.3.0-1981\lib\
   
3. Start MySQL Database Server.
   
4. Connect to MySQL Command Shell.

5. Create a new Database in MySQL to store Hive Metastore.

mysql> CREATE DATABASE metastore;

6. Use this newly created Database.

mysql> USE metastore;

7. Create the Database Schema using the schema.sql file as provided in Hive
   Location of .sql file: C:/hdp/hive-0.13.0.2.1.3.0-1981/scripts/metastore/upgrade/mysql/
   Schema filename: hive-schema-0.13.0.mysql.sql
   
mysql> SOURCE C:/hdp/hive-0.13.0.2.1.3.0-1981/scripts/metastore/upgrade/mysql/hive-schema-0.13.0.mysql.sql;

8. Create a MySQL User Account for Hive to access the Metastore.

mysql> CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'password';

9. Prevent the Hive user from Creating or Altering tables in the Metastore Database Schema.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON metastore.* TO 'hiveuser'@'%';
mysql> REVOKE ALTER,CREATE ON metastore.* FROM 'hiveuser'@'%';
mysql> FLUSH privileges;

10. Edit "hive-site.xml" located in Hive Conf folder
    Hive Conf Folder: C:\hdp\hive-0.13.0.2.1.3.0-1981\conf\
   
   



   

   


 javax.jdo.option.ConnectionURL
 jdbc:mysql://ARAVINDPC:3306/metastore?createDatabaseIfNotExist=true
 JDBC connect string for a JDBC metastore stored on MySQL



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



 javax.jdo.option.ConnectionUserName
 hiveuser
 Username to connect to MySQL Server



 javax.jdo.option.ConnectionPassword
 password
 Password to connect to MySQL Server



 datanucleus.autoCreateSchema
 false



 datanucleus.fixedDatastore
 true



  datanucleus.autoStartMechanism
  SchemaTable


   

11. Start Hadoop Cluster (HDFS) and get into Hive console.

The output on console will be as:
C:\hdp\hive-0.13.0.2.1.3.0-1981\bin>hive
...
hive>

12. Verify that Hive is communicating to MySQL.

The output on console will be as:
hive> show databases;
OK
...

Now, we are good to proceed further.

13. Test the Hive Metastore configuration with MySQL Server.

A. Create a table on the Hive console:
hive> create table employee(id int, name string, location string);
OK
...

B. View the Hive table on MySQL shell:
mysql> SELECT * FROM TBLS;
+-----------+-----------+-------+-----------+---------------+--------------------+----------+
| OWNER     | RETENTION | SD_ID | TBL_NAME  | TBL_TYPE      | VIEW_EXPANDED_TEXT | VIEW_OR |
+-----------+-----------+-------+-----------+---------------+--------------------+----------+
| aravindpc |         0 |     1 | employee  | MANAGED_TABLE | NULL               | NULL |
+-----------+-----------+-------+-----------+---------------+--------------------+----------+
...

In the MySQL shell, we can see the name of the above created Hive table.  Thus, we have configured the Hive Metastore to use the Local MySQL Database Server.

---

TROUBLESHOOTING: Use the following commands in sequence at Hive Bin prompt (in HDFS mode):

C:\hdp\hive-0.13.0.2.1.3.0-1981\bin> hadoop dfsadmin -safemode leave
C:\hdp\hive-0.13.0.2.1.3.0-1981\bin> stop_daemons
C:\hdp\hive-0.13.0.2.1.3.0-1981\bin> start_daemons

For SSL Error:
WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.




 
    javax.jdo.option.ConnectionURL
    jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true&useSSL=false
   
   
 

---

Ref:
http://java.dzone.com/articles/how-configure-mysql-metastore
https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin#AdminManualMetastoreAdmin-SupportedBackendDatabasesforMetastore
http://www.cloudera.com/content/cloudera/en/documentation/archives/cdh3/v3u6/CDH3-Installation-Guide/cdh3ig_topic_16_3.html
http://www.cloudera.com/content/cloudera/en/documentation/cdh4/v4-2-0/CDH4-Installation-Guide/cdh4ig_topic_18_4.html
https://www.edureka.co/blog/apache-hive-installation-on-ubuntu
http://stackoverflow.com/questions/1328538/how-do-i-escape-ampersands-in-xml-so-they-are-rendered-as-entities-in-html

---