> ## Documentation Index
> Fetch the complete documentation index at: https://docs.startree.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Hive Metastore: Onboarding via SQL DDL

> Create a Hive Metastore External Table with one CREATE TABLE ... WITH statement. Examples for Iceberg in HMS, Kerberos, and native Hive Parquet.

> [!WARNING]
>
> 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](https://docs.startree.ai/corecapabilities/external-table/sql-ddl.md#hive-metastore).

[Onboarding via API](https://docs.startree.ai/corecapabilities/external-table/hive/onboarding-api.md) 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](https://docs.startree.ai/corecapabilities/external-table/hive/onboarding-api.md#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.

```bash theme={null}
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`:

```bash theme={null}
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`:

```sql theme={null}
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:

```json theme={null}
{
  "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.

```sql theme={null}
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:

```sql theme={null}
  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.

```sql theme={null}
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:

```sql theme={null}
  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):

```json theme={null}
{
  "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:

```bash theme={null}
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](https://docs.startree.ai/corecapabilities/external-table/hive/onboarding-api.md#verify) on the API page. If a statement fails, look up the message in [Common errors](https://docs.startree.ai/corecapabilities/external-table/hive/onboarding-api.md#common-errors).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.