Integrating Apache Hive metastores with Snowflake¶

Prerequisites¶

The Hive connector for Snowflake has the following prerequisites:

Snowflake database and schemas:

Store the external tables that map to the Hive tables in the metastore.

Designated Snowflake user:

The connector is configured to execute operations on the external tables as this user.

Storage integration:

Storage integrations enable configuring secure access to external cloud storage without passing explicit cloud provider credentials such as secret keys or access tokens. Create a storage integration to access cloud storage locations referenced in Hive tables using CREATE STORAGE INTEGRATION.

The STORAGE_ALLOWED_LOCATIONS parameter for the storage integration must list the same storage containers as the ones referenced in the Location parameter of the Hive tables in your metastore.

Role:

The role must be assigned to the designated Snowflake user and include the following object privileges on the other Snowflake objects identified in this section:

Object	Privileges
Database	USAGE
Schema	USAGE , CREATE STAGE , CREATE EXTERNAL TABLE
Storage integration	USAGE

Step 1: Install the connector¶

Complete the following steps to install the connector:

1. Download the connector JAR file and configuration XML file from the Maven Central Repository:

   Sonatype (or https://repo1.maven.org/maven2/net/snowflake/snowflake-hive-metastore-connector/)

2. Copy the JAR file to the following directory:

   Amazon S3 or Google Cloud Storage:

   lib directory in the Hive classpath. The location can vary depending on the Hive installation. To determine the classpath, check the HIVE_AUX_JARS_PATH environment variable.

   Microsoft Azure HDInsight:

   hive directory in the user directory; for example, /usr/hdp/<hdinsight_version>/atlas/hook/hive/. The location could vary depending on the Azure HDInsight version and installation choices.

   Note

   An example custom script is available in the scripts folder on the GitHub project page. The script adds the JAR file and configuration files to the correct directories.

3. Create a file named snowflake-config.xml file in the following directory:

   Amazon S3 or Google Cloud Storage:

   conf directory in the Hive classpath.

   Microsoft Azure HDInsight:

   conf/conf.server directory in the Hive classpath.

4. Open the snowflake-config.xml file in a text editor and populate it with the following <name> properties and corresponding <values>:

   snowflake.jdbc.username

   Specifies the login name of the Snowflake user designated for refresh operations on the external tables.

   snowflake.jdbc.password

   Specifies the password for the login name.

   Note

   • You can set a placeholder for the password based on a system property or environment variable, depending on your Hadoop version. The configuration behaves like other Hadoop configurations. For more information, see the Hadoop documentation.

   • snowflake.jdbc.privateKey

   Alternatively, authenticate using key pair authentication. For instructions on generating the key pair and assigning the public key to a user, see Key-pair authentication and key-pair rotation.

   To pass the private key to Snowflake, add the snowflake.jdbc.privateKey property to the snowflake-config.xml file. Open the private key file (e.g. rsa_key.p8) in a text editor. Copy the lines between -----BEGIN RSA PRIVATE KEY----- and -----END RSA PRIVATE KEY----- as the property or environment variable value.

   snowflake.jdbc.account

   Specifies the name of your account (provided by Snowflake), e.g. xy12345.

   snowflake.jdbc.db

   Specifies an existing Snowflake database to use for the Hive metastore integration. See the Prerequisites section (in this topic) for more information.

   snowflake.jdbc.schema

   Specifies an existing Snowflake schema in the specified database. See the Prerequisites section (in this topic) for more information.

   To map multiple schemas in your Hive metastore to corresponding schemas in your Snowflake database, set the snowflake.hive-metastore-listener.schemas property in addition to the current property. Specify the default Snowflake schema in the snowflake.jdbc.schema property.

   snowflake.jdbc.role

   Specifies the access control role to use by the Hive connector. The role should be an existing role that has already been assigned to the specified user.

   If no role is specified here, then the Hive connector uses the default role for the specified user.

   snowflake.jdbc.connection

   Specifies the connection string for your Snowflake account in the following format:

   jdbc:snowflake://<account_identifier>.snowflakecomputing.com

   Where:

   <account_identifier>

   Unique identifier for your Snowflake account.

   The preferred format of the account identifier is as follows:

   organization_name-account_name

   Names of your Snowflake organization and account. For details, see Format 1 (preferred): Account name in your organization.

   Alternatively, specify your account locator along with the geographical region, and possibly cloud platform, where the account is hosted. For details, see Format 2: Account locator in a region.

   snowflake.hive-metastore-connector.integration

   Specifies the name of the storage integration object to use for secure access to the external storage locations referenced in Hive tables in the metastore. See the Prerequisites section (in this topic) for more information.

   snowflake.hive-metastore-listener.schemas

   Specifies a comma-separated list of Snowflake schemas that exist in the Snowflake database specified in snowflake.jdbc.db.

   When a table is created in the Hive metastore, the connector checks whether this property lists a Snowflake schema with the same name as the Hive schema/database that contains the new table:

   • If a Snowflake schema with the same name is listed, the connector creates an external table in this schema.

   • If a Snowflake schema with the same name is not listed, the connector creates an external table in the default schema, which is defined in the snowflake.jdbc.schema property.

   The external table has the same name as the new Hive table.

   Note

   Requires version 0.5.0 (or higher) of the Hive Connector.

   Optionally add the following property:

   snowflake.hive-metastore-listener.database-filter-regex

   Specifies the names of any databases in the Hive metastore to skip with the integration. Using this property enables you to control which databases to integrate with Snowflake. This option is especially useful when multiple tables have the same name across Hive databases. Currently, in this situation, the Hive connector creates the first table with the name in the Snowflake target database but skips additional tables with the same name.

   For example, suppose databases mydb1, mydb2, and mydb3 all contain a table named table1. You could omit all databases with the naming convention mydb<number> except for mydb1 by adding the regular expression mydb[^1] as the property value.

   Sample property node

   <configuration>
     ..
     <property>
       <name>snowflake.hive-metastore-listener.database-filter-regex</name>
       <value>mydb[^1]</value>
     </property>
   </configuration>
   

   Copy

   Sample snowflake-config.xml file

   <configuration>
     <property>
       <name>snowflake.jdbc.username</name>
       <value>jsmith</value>
     </property>
     <property>
       <name>snowflake.jdbc.password</name>
       <value>mySecurePassword</value>
     </property>
     <property>
       <name>snowflake.jdbc.role</name>
       <value>custom_role1</value>
     </property>
     <property>
       <name>snowflake.jdbc.account</name>
       <value>myaccount</value>
     </property>
     <property>
       <name>snowflake.jdbc.db</name>
       <value>mydb</value>
     </property>
     <property>
       <name>snowflake.jdbc.schema</name>
       <value>myschema</value>
     </property>
     <property>
       <name>snowflake.jdbc.connection</name>
       <value>jdbc:snowflake://myaccount.snowflakecomputing.com</value>
     </property>
     <property>
       <name>snowflake.hive-metastore-listener.integration</name>
       <value>s3_int</value>
     </property>
     <property>
       <name>snowflake.hive-metastore-listener.schemas</name>
       <value>myschema1,myschema2</value>
     </property>
   </configuration>
   

   Copy

5. Save the changes to the file.

6. Edit the existing Hive configuration file (hive-site.xml):

   Amazon S3 or Google Cloud Storage:

   Open the hive-site.xml file in a text editor. Add the connector to the configuration file as follows:

   <configuration>
    ...
    <property>
     <name>hive.metastore.event.listeners</name>
     <value>net.snowflake.hivemetastoreconnector.SnowflakeHiveListener</value>
    </property>
   </configuration>
   

   Copy

   Microsoft Azure HDInsight:

   Complete the steps in the Azure HDInsight documentation to edit the hive-site.xml file. Add the following custom property to the cluster configuration:

   hive.metastore.event.listeners=net.snowflake.hivemetastoreconnector.SnowflakeHiveListener

   Alternatively, add the custom property in the HDInsight Cluster Management Portal:

   1. Click the Hive tab in the left-hand menu » Configs » Advanced.

   2. Scroll down to the Custom Hive Site tab.

   3. Add the custom property.

   Note

   If there are other connectors already configured in this file, add the Hive connector for Snowflake in a comma-separated list in the <value> node.

7. Save the changes to the file.

8. Restart the Hive metastore service.

Step 2: Validate the installation¶

1. Create a new table in Hive.

2. Query the list of external tables in your Snowflake database and schema using SHOW EXTERNAL TABLES:

   SHOW EXTERNAL TABLES IN <database>.<schema>;
   

   Copy

   Where database and schema are the database and schema you specified in the snowflake-config.xml file in Step 1: Install the Connector (in this topic).

   The results should show an external table with the same name as the new Hive table.

Connector records are written to the Hive metastore logs. Queries executed by the connector can be viewed in the Snowflake QUERY_HISTORY view/function output similar to other queries.

Supported Hive operations and table types¶

Hive and Snowflake data types¶

The following table shows the mapping between Hive and Snowflake data types:

Supported file formats and options¶

The following data file formats and Hive file format options are supported:

• CSV

  The following options are supported using the SerDe (Serializer/Deserializer) properties:

  • field.delim / separatorChar

  • line.delim

  • escape.delim / escapeChar

• JSON

• AVRO

• ORC

• PARQUET

  The following options are supported using the table properties:

  • parquet.compression.

Unsupported Hive commands, features, and use cases¶

The connector does not support the following Hive commands, features, and use cases:

• Hive views

• ALTER statements other than TOUCH, ADD COLUMNS, and DROP COLUMNS

• Custom SerDe properties.

• Modifying an existing managed Hive table to become an external Hive table, or vice versa