If you are using the hive apart from Spark, then copy the connector jar file into Hive’s lib folder as:

cp /usr/share/java/mysql-connector-java.jar $HIVE_HOME/lib/mysql-connector-java.jar

Now, we will use the script hive-schema-0.14.0.mysql.sql according to the version of Hive to create the initial schema for metastore database as below.

First, create the database as a metastore and load the initial schema as:

mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE PATH-TO-SCRIPT/hive-schema-0.14.0.mysql.sql;

mysql> CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hivepassword';
mysql> GRANT all on *.* to 'hiveuser'@localhost identified by 'hivepassword';
mysql> flush privileges;

Now, enter the below code and create the hive-site.xml file in $HIVE_HOME or the $SPARK_HOME/conf folder.

<configuration>
   <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://localhost/metastore?createDatabaseIfNotExist=true</value>
      <description>metadata is stored in a MySQL server</description>
   </property>
   <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
      <description>MySQL JDBC driver class</description>
   </property>
   <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>hiveuser</value>
      <description>user name for connecting to mysql server</description>
   </property>
   <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>hivepassword</value>
      <description>password for connecting to mysql server</description>
   </property>
</configuration>