CREATE EXTERNAL COLLECTION on a Catalog

Operational Insights

reference

Use CREATE EXTERNAL COLLECTION to register an Iceberg table from an external catalog in Operational Insights, enabling SQL++ queries against the data in object storage.

An external collection created on an Iceberg catalog maps to a specific table in that catalog. The collection name is used in queries and does not need to match the Iceberg table name, which is supplied in the WITH clause. The link in the AT clause is used to read data files in object storage, and may differ from the link used to authenticate the catalog.

For an overview of supported catalog types and authentication, see Iceberg Tables.

To read data from external cloud storage, exclusive permissions are required. For more information, see AWS and GCS.

Syntax

CREATE EXTERNAL COLLECTION <collection-name>
ON <catalog-name>
AT <link-name>
WITH {
    "table-format": "iceberg",
    "tableName": "<iceberg-table-name>",
    "namespace": "<iceberg-namespace>"
    [, "decimal-to-double": "true" ]
    [, "date-to-int": "true" ]
    [, "time-to-int": "true" ]
    [, "timestamp-to-long": "true" ]
    [, "snapshotId": "<snapshot-id>" ]
    [, "snapshotTimestamp": "<timestamp-ms>" ]
};

Arguments

collection-name: The name of the collection in Operational Insights. Used in queries — does not need to match the Iceberg table name.

ON catalog-name: The name of the catalog this collection is created on.

AT link-name: The link used to read data files in object storage. This may differ from the link used when creating the catalog.

WITH: A JSON object with the following properties.

Property Description Required

Property	Description	Required
`table-format`	The table format type. Must be `"iceberg"`.	Yes
`tableName`	The actual Iceberg table name in the catalog.	Yes
`namespace`	The Iceberg namespace the table belongs to.	Yes
`decimal-to-double`	When `"true"`, converts Iceberg `DECIMAL` types to `DOUBLE`. Operational Insights does not support the `DECIMAL` type. If the data contains decimal values, set this property to `"true"`.	No
`date-to-int`	When `"true"`, returns Iceberg `DATE` values as `INTEGER` (epoch days since 1970-01-01) rather than the native `DATE` type.	No
`time-to-int`	When `"true"`, returns Iceberg `TIME` values as `INTEGER` (milliseconds since midnight) rather than the native `TIME` type.	No
`timestamp-to-long`	When `"true"`, returns Iceberg `TIMESTAMP` and `TIMESTAMP_NANO` values as `BIGINT` rather than `DATETIME`. `TIMESTAMP` returns microseconds since epoch. `TIMESTAMP_NANO` returns nanoseconds since epoch. `DATETIME` is parsed at millisecond precision — set this to `"true"` if microsecond or nanosecond precision is required.	No
`snapshotId`	Snapshot ID to pin this collection to for time travel. Cannot be used together with `snapshotTimestamp`. See Use Time Travel.	No
`snapshotTimestamp`	Snapshot timestamp to pin this collection to. Accepts milliseconds since Unix epoch, a date string in `yyyy-MM-dd` format, or a datetime string in `yyyy-MM-ddTHH:mm:ss` format. Operational Insights reads from the most recent snapshot at or before this timestamp. Cannot be used together with `snapshotId`. See Use Time Travel.	No

table-format

The table format type. Must be "iceberg".

Yes

tableName

The actual Iceberg table name in the catalog.

Yes

namespace

The Iceberg namespace the table belongs to.

Yes

decimal-to-double

When "true", converts Iceberg DECIMAL types to DOUBLE. Operational Insights does not support the DECIMAL type. If the data contains decimal values, set this property to "true".

date-to-int

When "true", returns Iceberg DATE values as INTEGER (epoch days since 1970-01-01) rather than the native DATE type.

time-to-int

When "true", returns Iceberg TIME values as INTEGER (milliseconds since midnight) rather than the native TIME type.

timestamp-to-long

When "true", returns Iceberg TIMESTAMP and TIMESTAMP_NANO values as BIGINT rather than DATETIME. TIMESTAMP returns microseconds since epoch. TIMESTAMP_NANO returns nanoseconds since epoch. DATETIME is parsed at millisecond precision — set this to "true" if microsecond or nanosecond precision is required.

snapshotId

Snapshot ID to pin this collection to for time travel. Cannot be used together with snapshotTimestamp. See Use Time Travel.

snapshotTimestamp

Snapshot timestamp to pin this collection to. Accepts milliseconds since Unix epoch, a date string in yyyy-MM-dd format, or a datetime string in yyyy-MM-ddTHH:mm:ss format. Operational Insights reads from the most recent snapshot at or before this timestamp. Cannot be used together with snapshotId. See Use Time Travel.

Example

CREATE EXTERNAL COLLECTION usersTable
ON myGlueCatalog
AT myS3Link
WITH {
    "table-format": "iceberg",
    "namespace": "pharmacy_namespace",
    "tableName": "users",
    "decimal-to-double": "true"
};

For AI agents:

CREATE EXTERNAL COLLECTION on a Catalog

Syntax

Arguments

Example

See Also