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

Table of contents

Reading Time: 2 minutes

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)

sudo apt-get install mysql-server

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&gt; CREATE DATABASE metastore;
mysql&gt; USE metastore;
mysql&gt; SOURCE PATH-TO-SCRIPT/hive-schema-0.14.0.mysql.sql;

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

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

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

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

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

mysql&gt; use metastore;
mysql&gt; select * from TBLS;

Keep blogging ….

High performance systems

Data Engineering, Strategy and Analytics

Intelligence Driven Decisioning - AI/ML

Cloud Engineering

Architecture Strategy, Audit & Academy

Platforms

KDP

KDSP

Products

Premon

Studio9

Tech Hub

Akka

Scala

Rust

Spark

Functional Java

Kafka

Flink

ML/AI

DevOps

Data Warehouse

Travel

Retail

Finance

Healthcare

Media and Publishing

Consumer Internet

Hi-tech & IoT

Case Studies

Blogs

Books

Community

Resources

OS contributions

Webinars

Knolx

Check out our open positions

Services

Go to Overview

Accelerators

Go to Overview

Platforms

Products

TechHub

Industries

Go to Overview

Travel

Insights

Go to Overview

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

Metastore DB

Metastore Service

Warehouse

Steps to setup MySQL metastore

Share the Knol:

Related

COMPANY

Sign up to our newsletter

Certificates

Partners

© 2023 Knoldus, Inc. All Rights Reserved.

Part of NashTech

Privacy Policy | Sitemap

Discover more from Knoldus Blogs

Check out our
open positions