Over a million developers have joined DZone.

Big Data Specifications, Part I: Configuring a MySQL Metastore in Apache Hive

DZone's Guide to

Big Data Specifications, Part I: Configuring a MySQL Metastore in Apache Hive

Apache Hive lets users load, analyze, and query data. One way to implement these queries is to configure a MySQL metastore.

· Big Data Zone ·
Free Resource

How to Simplify Apache Kafka. Get eBook.

Apache Hive is used as a data warehouse over Hadoop to provide users a way to load, analyze, and query the data from various resources. Data is stored in databases or file systems like HDFS (Hadoop Distributed File System). Hive can use Spark SQL or HiveQL for the implementation of queries.

Now, Hive uses its metastore, which contains the following information,

  • IDs of tables.
  • IDs of databases.
  • Time of creation.
  • Table names.
  • Type of the table.
  • Its owners' names.

Hive metastore is constructed with the following.

Metastore DB

Metastore DB is defined as a Relational Database Management System (RDBMS), which contains the metadata for the schema and the two major types of tables:

  1. Managed tables.
  2. External tables.

Metastore Service

Metastore runs a background service as a metastore service, which is used to perform the database operations, manage the metastore data, and store the data into Hive tables.


Hive basically uses the HDFS to store the data retrieved to the tables, usually under the directory user/hive/warehouse.

Steps to Set Up a MySQL Metastore

Install the MySQL server.

Install the MySQL Java connector.

sudo apt-get install libmysql-java

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

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

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;

Then, create a user for Hive and grant the permissions to it:

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.

      <description>metadata is stored in a MySQL server</description>
      <description>MySQL JDBC driver class</description>
      <description>user name for connecting to mysql server</description>
      <description>password for connecting to mysql server</description>

After we create a table in Hive, we can see the metadata by executing the following queries in MySQL:

mysql> use metastore;
mysql> select * from TBLS;

You're done!

12 Best Practices for Modern Data Ingestion. Download White Paper.

hive ,hadoop ,mysql ,metastores ,big data

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}