Configure a SQL Server Database as Remote Hive Metastore

access_time 2 years ago visibility2241 comment 0

In one of my previous post, I showed how to configure Apache Hive 3.0.0 in Windows 10.

Apache Hive 3.0.0 Installation on Windows 10 Step by Step Guide

I didn’t configure Hive metastore thus by default Hive will use embedded mode for metastore. The metadata is stored in Apache Derby database.

However only one user can be active at a time thus won’t be suitable for scenarios that require multiple sessions.  In this post, I’m going to show you how to configure a SQL Server database as a remote metastore for HiveServer2.


Please configure Hive 3.0.0 first by following the above post.

Create a metastore database in SQL Server

Run the following commands to create a database and also login and user for Hive:

create database hive_metastore;
create login hive with password='hive';
use hive_metastore
create user hive for login hive;
alter role db_owner add member hive;

The above script will also add user hive into role db_owner.

Download SQL Server JDBC driver

The binary doesn’t include JDBC driver for SQL Server. Thus we need to install it ourselves.

Download SQL Server JDBC driver from Microsoft website:

Download Microsoft JDBC Driver for SQL Server

Copy the suitable JAR file (mssql-jdbc-*.jre8.jar) into $HIVE_HOME/lib folder.

For Ubuntu or other Linux users, use the following command to download:

cp mssql-jdbc-7.2.2.jre8.jar $HIVE_HOME/lib

* Replace the URL to your own download URL.

*auth folder is only required if you want to use Windows Authentication in Windows systems.

Configure Hive file hive-site.xml

Edit hive-default.xml file (or create one if not existing) in folder $HIVE_HOME/conf.

* You can create the configuration file by using template file hive-default.xml.template.

cp $HIVE_HOME/conf/hive-default.xml.template $HIVE_HOME/conf/hive-site.xml

The above command needs to run in Cygwin terminal.

We need configure the following properties accordingly.

  • javax.jdo.option.ConnectionDriverName
  • javax.jdo.option.ConnectionURL
  • javax.jdo.option.ConnectionUserName
  • javax.jdo.option.ConnectionPassword
  • hive.metastore.uris
  • hive.metastore.db.type

For my environment, they are configured as the following:

   <description>Driver class name for a JDBC metastore</description> </property>
     JDBC connect string for a JDBC metastore.
     To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.
     For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.
   </description> </property>
    <description>Username to use against metastore database</description>
   <description>password to use against metastore database</description> </property>
   <description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description> </property>
     Expects one of [derby, oracle, mysql, mssql, postgres].
     Type of database used by the metastore. Information schema &amp; JDBCStorageHandler depend on it.
   </description> </property>

Initialize database structure

Run the following command in Cygwin:

$HIVE_HOME/bin/schematool -dbType mssql -initSchema

If your code runs successfully, the following output will be generated:

Metastore connection URL:        jdbc:sqlserver://localhost:1433;DatabaseName=hive_metastore;
Metastore Connection Driver :
Metastore connection User:       hive
Starting metastore schema initialization to 3.0.0
Initialization script hive-schema-3.0.0.mssql.sql
Initialization script completed schemaTool completed

You can also verify the schema in database:


Start Hive metastore service

$HIVE_HOME/bin/hive --service metastore

And then you can use either beeline or hive CLI to interact with Hive databases:

$HIVE_HOME/bin/beeline -u jdbc:hive2:// 

Start HiveServer2 service

$HIVE_HOME/bin/hive --service hiveserver2 start

Have fun!

info Last modified by Raymond at 6 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.

Learn more arrow_forward

More from Kontext

local_offer hive local_offer hdfs

visibility 204
thumb_up 0
access_time 8 months ago

In Hive, there are two types of tables can be created - internal and external table. Internal tables are also called managed tables. Different features are available to different types. This article lists some of the common differences.  By default, Hive creates internal tables. These tables' ...

local_offer hive local_offer SQL local_offer hive-sql-ddl

visibility 8
thumb_up 0
access_time 2 months ago

This page shows how to create bucketed sorted Hive tables via Hive SQL (HQL). CLUSTERD BY is used to create bucketed table Example: CREATE TABLE IF NOT EXISTS hql.transactions_bucketed(txn_id BIGINT, cust_id INT, amount DECIMAL(20,2),txn_type STRING, created_date DATE) COMMENT 'A table to ...

local_offer SQL local_offer hive

visibility 4568
thumb_up 1
access_time 11 months ago

In different databases, the syntax of selecting top N records are slightly different. They may also differ from ISO standards.

About column

Articles about Apache Hadoop installation, performance tuning and general tutorials.

*The yellow elephant logo is a registered trademark of Apache Hadoop.

rss_feed Subscribe RSS