Creating a Custom Hive Metastore

Qubole supports using a custom Hive metastore on the Qubole account. By default, each account comes with the Qubole Hive metastore but if you want to use a custom metastore, then you create a new metastore (if you do not have a metastore). Qubole Hive 2.1.1 and later versions support MySQL versions 5.7 and 8.0.

Note

Migrating Data from Qubole Hive Metastore to a Custom Hive Metastore describes how to migrate the data from the Qubole-managed Hive metastore to the custom-managed Hive metastore. Connecting to a Custom Hive Metastore describes how to connect to a custom metastore.

If you face any intermittent lock or dead lock issues, see Intermittent Lock and Deadlock Issues in Hive Metastore Migration to resolve them.

Create a custom Hive metastore by performing these steps:

  1. Log into MySQL and create the metastore database and tables as shown in the example below.

    > mysql -uroot
    ...
    mysql> CREATE DATABASE <database-name>;
    mysql> USE <database-name>;
    mysql> SOURCE <metastore-schema-script>
    
  2. Create a MySQL user and grant access to the metastore database as illustrated in the example below.

    mysql> CREATE USER 'hiveUser'@'%" IDENTIFIED BY 'hivePassword';
    mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hiveUser'@'%';
    mysql> GRANT ALL ON hive_metastore.* TO 'hiveUser'@'%';
    mysql> FLUSH PRIVILEGES
    
  3. Run the SQL scripts to create default tables for different Hive versions listed below:

  4. To continue configuring the custom Hive metastore, perform the steps described in Connecting to a Custom Hive Metastore.

Upgrading the Current Hive Metastore

To upgrade from a Hive 2.3-or-earlier-version-Hive metastore to version 3.1, use the following scripts as appropriate: