BigData Specifications – Part 1 : Configuring MySql Metastore in Apache Hive


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 into 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,
  • And its owner’s names

Hive metastore is constructed with the following,

Metastore DB

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

  • Managed tables,
  • External tables

Metastore Service

Metastore runs a background service as metastore service which is used to perform the database operations and manage the metastore data and storing of the data into Hive Tables.

Warehouse

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

Steps to setup MySQL metastore

Install the MySQL server (Optional if already installed)

Install 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  the script according to the version of hive to create the initial schema for metastore database as below,
First create the database as 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 just put the below code and create the hive-site.xml file in $HIVE_HOME or $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>

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;

Keep blogging ….


KNOLDUS-advt-sticker

About Harsh Sharma Khandal

Harsh is a Sr. Software Consultant at Knoldus Software LLP with 4 year of experience. He is a fan of programming standards and conventions. He has good knowledge of Scala, Java, 3D Modeling and 3D animation. His current passions include utilizing the power of Scala, Akka and Play to make reactive applications. He is a technologist and is never too far away from the keyboard. He believes in standard coding practices. His focus always remains on practical work. He has Master's in Computer Applications from Rajasthan Technical University, Kota. His hobbies include reading books and writing the code in multiple ways to find the best way it can be represented.
This entry was posted in Scala and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s