Create an Iceberg table

This topic covers how to create Iceberg tables in Snowflake for different Iceberg 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.

Create an Iceberg table with Snowflake as the catalog

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 for Iceberg tables.

To define table columns, you can use Iceberg data types. For more information, see Iceberg table data types.

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 an Iceberg table.

Create an Iceberg table that uses an 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 command to synchronize the metadata with the most recent table changes. For more information, see Refresh the table metadata.

Create an Iceberg table with AWS Glue as the catalog

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:

Create an Iceberg table from 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

After you create a table with from files in object storage, you can take actions such as:

Language: English