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.
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*Confirm that
mysql-connector-java.jaris in the Java share directory.ls /usr/share/java/mysql-connector-java.jarMake sure the .jar file has the appropriate permissions - 644.
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 -pCREATE 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 -pCREATE DATABASE<HIVEDATABASE>Where <HIVEDATABASE> is the Hive database name.
Load the Hive database schema.
For a HDP 2.2 or later Stacks:
![[Important]](../common/images/admon/important.png)
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.sqlFind the
hive-schema-0.13.0.mysql.sqlfile 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.