Create an Apache Iceberg™ table in Snowflake¶

Create Apache Iceberg™ tables in Snowflake for different Catalog options. You can create an Iceberg table by using the CREATE ICEBERG TABLE command.

Note

To create an Iceberg table, you must have a running warehouse that is specified as the current warehouse for your session. Errors might occur if no running warehouse is specified when you create an Iceberg table. For more information, see Working with Warehouses.
To create an Iceberg table that works with Snowflake Open Catalog, see Use Apache Iceberg™ tables with Snowflake Open Catalog in Snowflake.

Snowflake-managed¶

To create an Iceberg table with Snowflake as the catalog, you must specify an external volume and a base location (directory on the external volume) where Snowflake can write table data and metadata. For instructions on creating an external volume, see Configure an external volume.

To define table columns, you can use Iceberg data types. For more information, see Data types for Apache Iceberg™ tables.

The following example creates an Iceberg table with Snowflake as the Iceberg catalog.

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table (
    boolean_col boolean,
    int_col int,
    long_col long,
    float_col float,
    double_col double,
    decimal_col decimal(10,5),
    string_col string,
    fixed_col fixed(10),
    binary_col binary,
    date_col date,
    time_col time,
    timestamp_ntz_col timestamp_ntz(6),
    timestamp_ltz_col timestamp_ltz(6)
  )
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_ext_vol'
  BASE_LOCATION = 'my/relative/path/from/extvol';

Copy

Note

Alternatively, use variant syntax. For more information, see CREATE TABLE … AS SELECT and CREATE ICEBERG TABLE … LIKE.

After you create a table that uses Snowflake as the catalog, you can take actions such as:

For more information, see Manage Apache Iceberg™ tables.

External catalog¶

To create an Iceberg table that uses an external catalog, or no catalog at all, you must specify an external volume and a catalog integration. If you use an external Iceberg catalog, you might also need to specify additional parameters. For example, when you use AWS Glue as the catalog, you must specify a catalog table name.

When you create an Iceberg table that uses an external catalog, Snowflake performs an initial metadata refresh. You can also manually refresh the table metadata using the ALTER ICEBERG TABLE … REFRESH command to synchronize the metadata with the most recent table changes. For more information, see Refresh the table metadata.

Note

The CREATE ICEBERG TABLE command supports different options for different external catalogs. The examples in this section specify only some of the available options. To view the full syntax, see the following pages:

CREATE ICEBERG TABLE (AWS Glue as the Iceberg catalog)

CREATE ICEBERG TABLE (Iceberg files in object storage)

CREATE ICEBERG TABLE (Delta files in object storage)

CREATE ICEBERG TABLE (Iceberg REST catalog)

You can also configure data governance (for example, masking or row access policies) for externally managed tables by using ALTER ICEBERG TABLE.

AWS Glue¶

Important

You must configure a catalog integration for AWS Glue to establish a trust relationship between Snowflake and the Glue Data Catalog. For instructions, see Configure a catalog integration for AWS Glue.

The following example creates an Iceberg table that uses the AWS Glue Data Catalog, specifying the name of a catalog integration that has been configured for AWS Glue (glueCatalogInt) and a value for the CATALOG_TABLE_NAME property.

CREATE ICEBERG TABLE myGlueTable
  EXTERNAL_VOLUME='glueCatalogVolume'
  CATALOG='glueCatalogInt'
  CATALOG_TABLE_NAME='myGlueTable';

Copy

After you create a table with AWS Glue as the catalog, you can take actions such as:

Iceberg files in object storage¶

The following example creates an Iceberg table from Iceberg metadata in external cloud storage, specifying a relative path to the table metadata on the external volume (METADATA_FILE_PATH).

CREATE ICEBERG TABLE myIcebergTable
  EXTERNAL_VOLUME='icebergMetadataVolume'
  CATALOG='icebergCatalogInt'
  METADATA_FILE_PATH='path/to/metadata/v1.metadata.json';

Copy

Delta files in object storage¶

The following example command creates an Iceberg table from Delta table files in object storage.

The example specifies an external volume associated with the cloud location of the Delta table files, a catalog integration configured for Delta, and a value for the required BASE_LOCATION parameter.

CREATE ICEBERG TABLE my_delta_iceberg_table
  CATALOG = delta_catalog_integration
  EXTERNAL_VOLUME = delta_external_volume
  BASE_LOCATION = 'relative/path/from/ext/vol/';

Copy

If the Delta table uses a partitioning scheme, Snowflake automatically interprets the scheme from the Delta log.

Apache Iceberg™ REST catalog¶

The following example creates a table that uses a remote Iceberg REST catalog.

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG = 'my_rest_catalog_integration'
  CATALOG_TABLE_NAME = 'my_remote_table';

Copy