SQL DDL: CREATE TABLE ... WITH

This feature requires StarTree release 0.16.0 or later, and must be enabled on demand — contact StarTree support to activate it.

As an alternative to the REST onboarding flow (/connections/browse → /tables/preview → /schemas → /tables), you can create an External Table directly with a single SQL statement:

CREATE TABLE trips_analytics WITH (
  type = 'iceberg',
  catalog_type = 'rest',
  catalog_uri = 'https://<catalog-host>/iceberg',
  schema_name = 'transportation',
  table_name = 'nyc_taxi_trips',
  storage.region = '<aws-region>',
  refresh_interval = '5m'
);

This is submitted as SQL DDL via POST /sql/ddl and is equivalent to running the full preview → schema → table REST flow — the controller infers the Pinot schema from the source table and pre-validates it against the catalog before creating the table.

Option reference

Recognized option names are case-insensitive. Unrecognized options pass through verbatim — with their original case preserved — into the generated table’s task config, so you can set advanced ExternalTableSyncTask keys (see Best Practices & Configs) directly in the WITH clause. Type those keys in their exact camelCase spelling (e.g. 'storeNestedSchema.enabled' = 'true'): the task config lookup is case-sensitive, and pass-through keys are also read at CREATE-time schema inference.

Option	Required	Description
`type`	Yes	`'iceberg'` for all Iceberg-catalog sources (Glue, S3 Tables, Unity Catalog, generic REST).
`catalog_type`	No (default `rest`)	`rest`, `glue`, or `s3tables`.
`catalog_uri`	Depends on `catalog_type`	Iceberg REST catalog endpoint.
`catalog_name`	No	Catalog/warehouse name.
`schema_name`	Yes	Source namespace/database.
`table_name`	No (defaults to the SQL table name)	Source table name in the catalog.
`auth_type`	No (auto-detected)	`oauth` (normalizes to OAuth2 client-credentials — see Unity Catalog: Authentication), `token` / `bearer`, or `none`.
`client_id`, `client_secret`, `oauth_scope`, `oauth_token_uri`	With `auth_type='oauth'`	OAuth2 client-credentials parameters.
`token`	With `auth_type='token'`	Bearer token.
`storage.provider`	No	Underlying object-store provider, e.g. `s3`.
`storage.region`, `storage.access_key`, `storage.secret_key`, `storage.session_token`, `storage.endpoint`	No	Storage credentials/region, when not vended by the catalog.
`refresh_interval`	No	Sync cadence, e.g. `'5m'` (mapped to a Quartz cron `0 /5 * * ?`), or a raw cron expression.
`snapshot_mode`	No	`latest`, `earliest`, or `snapshot_id`.
`snapshot_id`	With `snapshot_mode='snapshot_id'`	Specific Iceberg snapshot to start from.
`replication`	No	Table replication factor.
`enable_schema_evolution`, `enable_partition_pruning`, `enable_metadata_caching`	No	Accepted for forward compatibility; currently inert (no backend effect yet).

Any option not listed above is passed through unchanged to the External Table task config — for example executor = 'controller' to explicitly select the watcher-based sync path used by the observability endpoints.

Example: Unity Catalog with OAuth2

CREATE TABLE countries WITH (
  type = 'iceberg',
  catalog_type = 'rest',
  catalog_uri = 'https://<workspace-host>/api/2.1/unity-catalog/iceberg-rest',
  catalog_name = '<unity-catalog-name>',
  schema_name = 'default',
  table_name = 'countries',
  auth_type = 'oauth',
  client_id = '<service-principal-client-id>',
  client_secret = '<service-principal-secret>',
  storage.region = '<aws-region>',
  refresh_interval = '5m'
);

Limitations

The generated schema and table config aren’t visible for review before creation, unlike the REST flow’s /tables/preview step — use the REST flow instead when you need to inspect or tweak the inferred schema first.
As with the REST flow, the table is created immediately but data only appears once the controller’s watcher completes the first sync — see Observability to track progress.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

SQL DDL: CREATE TABLE ... WITH

Option reference

Example: Unity Catalog with OAuth2

Limitations

​Option reference

​Example: Unity Catalog with OAuth2

​Limitations

Option reference

Example: Unity Catalog with OAuth2

Limitations