Using Hive with MySQL

Before using Hive with a new or existing MySQL database; obtain the appropriate driver and .jar files, create a Hive user with sufficient permissions, and load the Hive database.

1. On the Ambari Server host, stage the appropriate MySQL connector for later deployment.
   1. On the Ambari Server host, Download the MySQL Connector/JDBC driver from MySQL.
   2. Runambari-server setup --jdbc-db=mysql --jdbc-driver=/path/to/mysql/mysql-connector-java.jar
   3. Confirm that mysql-connector-java.jar is in the Java share directory.
      ls /usr/share/java/mysql-connector-java.jar
   4. Make sure the .jar file has the appropriate permissions - 644.
   5. Execute the following command:
      ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar
2. Create a user for Hive and grant it permissions.
   using the MySQL database admin utility:

   # mysql -u root -p
   CREATE USER '[HIVE_USER]'@'localhost' IDENTIFIED BY '[HIVE_PASSWORD]';
   GRANT ALL PRIVILEGES ON *.* TO '[HIVE_USER]'@'localhost';
   CREATE USER '[HIVE_USER]'@'%' IDENTIFIED BY '[HIVE_PASSWORD]';
   GRANT ALL PRIVILEGES ON *.* TO '[HIVE_USER]'@'%';
   CREATE USER '[HIVE_USER]'@'[HIVE_METASTORE_FQDN]' IDENTIFIED BY '[HIVE_PASSWORD]';
   GRANT ALL PRIVILEGES ON *.* TO '[HIVE_USER]'@'[HIVE_METASTORE_FQDN]';
   FLUSH PRIVILEGES;

   Where[HIVE_USER] is the Hive user name, [HIVE_PASSWORD] is the Hive user password and [HIVE_METASTORE_FQDN] is the Fully Qualified Domain Name of the Hive Metastore host.
3. Create the Hive database.
   The Hive database must be created before loading the Hive database schema.

   # mysql -u root -p
   CREATE DATABASE [HIVE_DATABASE]

   Where [HIVE_DATABASE] is the Hive database name.