Creating an External Hive Metastore

This page shows how to create an external Hive metastore database. It assumes that you are familiar with creating a database service in your cloud provider.

Having an external detastore Database is necessary to make your Hive metadata persistent accross Data Mechanics applications and jobs. It is also a way to share Spark tables between Spark platforms.

Data Mechnanics supports the Hive local mode in which the persistent database is accessed by the local Hive service that is automatically spawned on the Spark driver of Data Mechanics Spark applications and jobs.

Creating the Hive metastore external database involves the following steps:

  1. Creating a database service in your cloud provider
  2. Configuring the database connectivity to ensure the Database is accessible from Data Mechanics
  3. Creating the Hive metastore database
  4. Creating the Hive schema

Creating a Database Service

On AWS you have many choices among which RDS. RDS allows you to create managed services for Aurora, MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server.

AWS instructions to create RDS and PostgreSQL can be found here.

You should consider carefully availability, backups, security and maintenance. Refer to the AWS documentation.

This page gives an example for RDS and PostgreSQL.

Configuring Connectivity

One way to insure connectivity is to create an inbound rule for TCP on port 5432 and to ensure the database is publicly accessible.

Inbound rule

Go to the VPC security group of the Database service and create an inbound rule similar to the following:

Hive Inbound Rule

Public Access

You can follow the instructions here. The connectivity of your RDS service should be like this:

Hive Public Access

Creating the Hive metastore database

One way to do it is through a database client tools like pgAdmin or Postico. From one of those tools create a connection to your database service and create a database that will store all the Hive metadata.

Creating the Hive schema

The last step to prepare the Hive metastore is to create the Hive schema. You need to retrieve the following two sql scripts:

Look for \i hive-txn-schema-2.3.0.postgres.sql; in the first script and replace it with the content of the second script. Then run the whole in your database client tool.

The above scripts are for Hive 2.3 and PostgreSQL. You will find scripts for other Hive versions and databases here

Regarding the Hive version, 2.3 is the Spark default and unless you have specific constraints you should choose this version.