Hive Metastore: Onboarding via SQL DDL

This feature requires StarTree Cloud 0.18.0 or later. Contact StarTree support to enable the Hive Metastore catalog for your environment.

This page creates a Hive Metastore (HMS) External Table with one CREATE TABLE ... WITH statement through SQL DDL. Onboarding via API is the main Hive Metastore page. What’s supported, requirements, every option with its SQL DDL name, query behavior, limits and errors are all there. This page covers only what’s specific to SQL DDL.

Create the table

Send CREATE TABLE ... WITH (...) to POST /sql/ddl. DDL sets catalogType = hive and executor = controller for you. It infers the schema and creates an OFFLINE table.

Options. Use the names in the SQL DDL option column of the configuration reference.
type must match the table in HMS: iceberg for Iceberg tables, hive for native Parquet tables. type = 'hive' implies catalog_type = 'hive'.
snapshot_mode takes lowercase values: latest, earliest, snapshot_id. Setting snapshot_id implies snapshot_mode = 'snapshot_id'.
refresh_interval takes Ns / Nm (1–59), Nh (1–23), Nd (1–31), or a Quartz cron expression.
Other keys. Any option DDL doesn’t know is copied as-is into the task config, with a warning. Use this for keys such as 'enableDeletionVectors' = 'true'.
Azure. With storage.provider = 'azure', DDL also sets externalTableAccessPolicy = azure-sas. The S3 options are rejected with Azure, and the SAS options are rejected without it.
No effect. enable_schema_evolution and enable_metadata_caching are accepted with a warning and do nothing.

Change or remove

There is no ALTER for these options. To change one, drop the table and create it again. Also create a new table if the HMS table changes between native Parquet and Iceberg.

curl -X POST "$BASE_URL/sql/ddl" \
  -H "Content-Type: application/json" \
  -d '{"sql": "DROP TABLE hms_events"}'

Examples

POST /sql/ddl takes a JSON body with one field, sql. Add ?dryRun=true to compile and validate without creating anything. The easiest way to send a multi-line statement is to wrap a .sql file with jq:

jq -Rs '{sql: .}' create.sql > create.json
curl -X POST "$BASE_URL/sql/ddl" \
  -H "Content-Type: application/json" \
  --data-binary @create.json

Iceberg in HMS, no authentication

create.sql:

CREATE TABLE hms_events WITH (
  type = 'iceberg',
  catalog_type = 'hive',
  catalog_uri = 'thrift://hms.example.com:9083',
  schema_name = 'analytics',
  table_name = 'iceberg_events',
  storage.provider = 's3',
  storage.region = 'us-west-2',
  refresh_interval = '5m',
  snapshot_mode = 'latest'
);

The resulting sync task config:

{
  "task": {
    "taskTypeConfigsMap": {
      "ExternalTableSyncTask": {
        "catalogType": "hive",
        "executor": "controller",
        "schedule": "0 */5 * * * ?",
        "catalog.hive.metastoreUri": "thrift://hms.example.com:9083",
        "catalog.hive.table.namespace": "analytics",
        "catalog.hive.table.tableName": "iceberg_events",
        "catalog.hive.auth.storage.region": "us-west-2",
        "iceberg.snapshotProcessing.initialVersionSelector": "LATEST",
        "enablePartitionPruning": "false",
        "enableSnapshotConsistency": "true",
        "enableDeletionVectors": "false"
      }
    }
  }
}

If the Iceberg table has row deletes, add 'enableDeletionVectors' = 'true' to the WITH clause. Without it, deletes are not applied.

Kerberos-secured HMS

This uses Kerberos with a keytab, plus TLS with a custom truststore. With TLS on, sasl_qop must be auth. Stock Hive 2.3.9 can’t combine Kerberos and TLS on one listener; drop the TLS lines for it.

CREATE TABLE secure_events WITH (
  type = 'iceberg',
  catalog_type = 'hive',
  catalog_uri = 'thrift://hms.example.com:9083',
  schema_name = 'analytics',
  table_name = 'iceberg_events',
  metastore.auth_type = 'kerberos',
  metastore.principal = 'pinot@EXAMPLE.COM',
  metastore.server_principal = 'hive/_HOST@EXAMPLE.COM',
  metastore.keytab = '/etc/hms/pinot.keytab',
  metastore.sasl_qop = 'auth',
  metastore.tls_enabled = 'true',
  metastore.truststore_path = '/etc/hms/truststore.p12',
  metastore.truststore_type = 'PKCS12',
  metastore.truststore_password = 'srn2:secret#hms-tls',
  storage.provider = 's3',
  storage.region = 'us-west-2',
  refresh_interval = '5m'
);

To use a delegation token instead, replace the four Kerberos lines with:

  metastore.auth_type = 'delegation-token',
  metastore.delegation_token = 'srn2:secret#hms-token',

StarTree does not renew the token. Renew or replace it before it expires.

Native Hive Parquet, partitioned

The HMS table analytics.events is a Parquet table partitioned by event_day. It must already contain at least one Parquet file.

CREATE TABLE hive_events WITH (
  type = 'hive',
  catalog_uri = 'thrift://hms.example.com:9083',
  schema_name = 'analytics',
  table_name = 'events',
  storage.provider = 's3',
  storage.region = 'us-west-2',
  refresh_interval = '5m'
);

For Azure, replace the storage lines:

  storage.provider = 'azure',
  storage.sas_token = 'srn2:secret#events-sas',
  storage.credential_prefix = 'wasbs://events@myaccount.blob.core.windows.net/analytics/events',

The resulting sync task config (S3):

{
  "task": {
    "taskTypeConfigsMap": {
      "ExternalTableSyncTask": {
        "catalogType": "hive",
        "executor": "controller",
        "schedule": "0 */5 * * * ?",
        "catalog.hive.metastoreUri": "thrift://hms.example.com:9083",
        "catalog.hive.table.namespace": "analytics",
        "catalog.hive.table.tableName": "events",
        "catalog.hive.auth.storage.region": "us-west-2",
        "enablePartitionPruning": "false",
        "enableSnapshotConsistency": "false",
        "enableDeletionVectors": "false"
      }
    }
  }
}

If you write the table config JSON yourself, keep executor = controller, leave out enabled, and don’t set segmentGroupConfig, enableSnapshotConsistency = true or enableDeletionVectors = true.

Run the first sync

DDL does not run a sync. Wait for refresh_interval, or start one now:

curl -X POST "$BASE_URL/tasks/schedule?taskType=ExternalTableSyncTask&tableName=hms_events_OFFLINE"

Verify

Check the sync status and row count as described in Verify on the API page. If a statement fails, look up the message in Common errors.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

Hive Metastore: Onboarding via SQL DDL

Create the table

Change or remove

Examples

Iceberg in HMS, no authentication

Kerberos-secured HMS

Native Hive Parquet, partitioned

Run the first sync

Verify

​Create the table

​Change or remove

​Examples

​Iceberg in HMS, no authentication

​Kerberos-secured HMS

​Native Hive Parquet, partitioned

​Run the first sync

​Verify

Create the table

Change or remove

Examples

Iceberg in HMS, no authentication

Kerberos-secured HMS

Native Hive Parquet, partitioned

Run the first sync

Verify