Hive Metastore

This article describes how to create a connection to your Hive Metastore using a SQL command.

Establishing a Hive Metastore connection allows Upsolver to integrate with the Hive ecosystem, enabling you to create, modify, and query tables within Upsolver. These tables can also be queried from other Hive-compatible services and the Upsolver UI.

This documentation assumes that you have created the Amazon S3 connection s3 with the correct write permissions to the specified storage location. Refer to Upsolver's documentation on creating an Amazon S3 connection for more details.

CREATE HIVE_METASTORE CONNECTION <connection_name>  
    URI = 'thrift://<hostname>:<port>'  
    [ COMMENT = '<comment>' ]  
    [ DEFAULT_SCHEMA = '<default_schema>' ]  
    [ USER_NAME = '<username>' ]  
    [ PASSWORD = '<password>' ]  
    [ CLIENT_PROPERTIES = ( PROPERTY = 'VALUE' [, ...] ) ]  
    [ MAX_CONCURRENT_OPERATIONS = <integer> ]  
    [ DEFAULT_STORAGE_CONNECTION = <storage_connection_identifier> ]  
    [ DEFAULT_STORAGE_LOCATION = 's3://<bucket>/<prefix>/' ]

Connection Options

URI — Required

• Type: text The URI of your Hive Metastore service, usually in the thrift://<hostname>:<port> format.

COMMENT — Optional

• Type: text (Optional) A description or comment regarding this connection.

DEFAULT_SCHEMA — Optional

• Type: text (Optional) The default schema to use for tables under this connection.

USER_NAME — Optional

• Type: text (Optional) The username for authentication to Hive Metastore.

PASSWORD — Optional

• Type: text (Optional) The password for authentication to Hive Metastore.

CLIENT_PROPERTIES — Optional

• Type: text (Optional) Additional client properties for the Hive connection. Formatted as a list: CLIENT_PROPERTIES = ( PROPERTY = 'VALUE' [, ...] ).

MAX_CONCURRENT_OPERATIONS — Optional

• Type: integer (Optional) The maximum number of concurrent operations that can be used by Upsolver servers for this connection.

DEFAULT_STORAGE_CONNECTION — Optional

• Type: identifier (Optional) An Amazon S3 connection identifier with the appropriate credentials to write to the provided storage location.

DEFAULT_STORAGE_LOCATION — Optional

• Type: text (Optional) The Amazon S3 path that serves as the default storage location for the underlying files associated with tables created under this metastore connection.

Minimum Example

CREATE HIVE_METASTORE CONNECTION my_hive_connection  
    URI = 'thrift://hive.upsolver.com:9083'  
    DEFAULT_STORAGE_CONNECTION = s3  
    DEFAULT_STORAGE_LOCATION = 's3://test-bucket/prefix/';