2.2. Using Hive with MySQL

To set up MySQL for use with Hive:

On the Ambari Server host, stage the appropriate MySQL connector for later deployment.
1. Install the connector.
  RHEL/CentOS/Oracle Linux
  yum install mysql-connector-java*
  SLES
  zypper install mysql-connector-java*
  Ubuntu
  apt-get install mysql-connector-java*
  Debian
  apt-get install mysql-connector-java*
2. Confirm that mysql-connector-java.jar is in the Java share directory.
  ls /usr/share/java/mysql-connector-java.jar
3. Make sure the .jar file has the appropriate permissions - 644.
4. Execute the following command:
  ambari-server setup --jdbc-db=mysql --jdbc-driver=/usr/share/java/mysql-connector-java.jar
Create a user for Hive and grant it permissions.
- Using the MySQL database admin utility:
  # mysql -u root -p
  CREATE USER ‘<HIVEUSER>’@’localhost’ IDENTIFIED BY ‘<HIVEPASSWORD>’;
  GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'localhost';
  CREATE USER ‘<HIVEUSER>’@’%’ IDENTIFIED BY ‘<HIVEPASSWORD>’;
  GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'%';
  CREATE USER '<HIVEUSER>'@'<HIVEMETASTOREFQDN>'IDENTIFIED BY '<HIVEPASSWORD>';
  GRANT ALL PRIVILEGES ON *.* TO '<HIVEUSER>'@'<HIVEMETASTOREFQDN>';
  FLUSH PRIVILEGES;
- Where <HIVEUSER> is the Hive user name, <HIVEPASSWORD> is the Hive user password and <HIVEMETASTOREFQDN> is the Fully Qualified Domain Name of the Hive Metastore host.
Create the Hive database.
The Hive database must be created before loading the Hive database schema.
# mysql -u root -p
CREATE DATABASE <HIVEDATABASE>
Where <HIVEDATABASE> is the Hive database name.
Load the Hive database schema.
- For a HDP 2.2 or later Stacks:
  Important
  Ambari sets up the Hive Metastore database schema automatically.
  You do not need to pre-load the Hive Metastore database schema into your MySQL database for a HDP 2.2 Stack.
- For HDP 2.1 Stack:
  You must pre-load the Hive database schema into your MySQL database using the schema script, as follows. mysql -u root -p <HIVEDATABASE> hive-schema-0.13.0.mysql.sql
  Find the hive-schema-0.13.0.mysql.sql file in the /var/lib/ambari-server/resources/stacks/HDP/2.1/services/HIVE/etc/ directory of the Ambari Server host after you have installed Ambari Server.

	Important
Ambari sets up the Hive Metastore database schema automatically. You do not need to pre-load the Hive Metastore database schema into your MySQL database for a HDP 2.2 Stack.

Legal notices