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*
```
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.
- You must pre-load the Hive database schema into your MySQL database using the schema script.
- When using HDP 2.1 Stack:
```
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.

Legal notices