> ## 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.

# SQL DDL: CREATE TABLE ... WITH

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

As an alternative to the [REST onboarding flow](./overview#where-to-start) (`/connections/browse` → `/tables/preview` → `/schemas` → `/tables`), you can create an External Table directly with a single SQL statement:

```sql theme={null}
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 = 'us-west-2',
  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

All keys are case-insensitive. Unrecognized options pass through verbatim into the generated table's task config, so you can set advanced `ExternalTableSyncTask` keys (see [Best Practices & Configs](./best-practices-and-configs)) directly in the `WITH` clause.

| 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](./unity/onboarding-api#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).                                                                                   |

<Note>
  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](./observability).
</Note>

## Example: Unity Catalog with OAuth2

```sql theme={null}
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 = 'us-west-1',
  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](./observability) to track progress.