This page lists the options of the iceberg extension: the parameters accepted by the Iceberg functions, the options of the ATTACH and CREATE SECRET statements used to connect to a catalog, and the global settings.

ATTACH Options

To make an Iceberg Catalog (not just a single table) known to the system, you'll need to use the ATTACH statement. The options provided to the ATTACH are divided into categories:

Some parameters enable others, see the list of associated additional parameters below this table.

Endpoint Type

For commonly used Iceberg Catalog providers, the ENDPOINT_TYPE parameter can be used to set certain parameters to default values. Acting as a shorthand for connecting to these catalogs.

S3 Tables

The parameters set by using the S3_TABLES ENDPOINT_TYPE are:

Glue

When using the GLUE ENDPOINT_TYPE, the SECRET parameter of type VARCHAR has to be set to the path of an existing S3/AWS SECRET. The parameters set by using the GLUE ENDPOINT_TYPE are:

Authorization

To configure the Authorization layer for connecting to an Iceberg REST Catalog, you'll need to pass the AUTHORIZATION_TYPE parameter. These are the supported options:

OAUTH2 Authorization Options

The additional parameters that can be provided when the AUTHORIZATION_TYPE is set to OAUTH2 are the following.

SIGV4 Authorization Options

The additional parameters that can be provided when the AUTHORIZATION_TYPE is set to SIGV4 are the following.

ICEBERG SECRET Options

Every option mentioned in the ATTACH Options section and its sub-sections can be used in the creation of an ICEBERG SECRET. When such an ICEBERG SECRET exists it can be inferred by subsequent ATTACH statements, or provided explicitly through the SECRET ATTACH option.

Working with an Attached Catalog

Once a catalog is attached, you can run the full set of read and write operations against its tables:

• Reading and metadata: SELECT, time travel with the AT clause, and the iceberg_metadata, iceberg_snapshots, and statistics functions. See the Functions and Settings Reference.
• Writing: CREATE/DROP SCHEMA and TABLE, partitioning, INSERT, UPDATE, DELETE, MERGE INTO, ALTER TABLE, table properties, and COPY FROM DATABASE. See Writing to Iceberg.

Metadata functions accept a fully qualified table name, e.g.:

SELECT * FROM iceberg_snapshots(my_catalog.default.t);

Settings

Scan Options

The following parameters can be passed to iceberg_scan, iceberg_column_stats, iceberg_metadata, iceberg_partition_stats, iceberg_snapshots:

iceberg_snapshots does not take allow_moved_paths, snapshot_from_id or snapshot_from_timestamp as parameters.

Selecting Metadata Versions

By default, the iceberg extension will look for a version-hint.text file to identify the proper metadata version to use. This can be overridden by explicitly supplying a version number via the version parameter to the functions of the iceberg extension:

SELECT *
FROM iceberg_snapshots(
    'data/iceberg/lineitem_iceberg',
    version = '1'
);

By default, iceberg functions will look for both v{version}.metadata.json and {version}.metadata.json files, or v{version}.gz.metadata.json and {version}.gz.metadata.json when metadata_compression_codec = 'gzip' is specified. Other compression codecs are not supported.

If any text file is provided through the version parameter, it is opened and treated as a version hint file:

SELECT *
FROM iceberg_snapshots(
    'data/iceberg/lineitem_iceberg',
    version = 'version-hint.txt'
);

The iceberg extension will open this file and use the entire content of the file as a provided version number. Note that the entire content of the version-hint.txt file will be treated as a literal version name, with no encoding, escaping or trimming. This includes any whitespace, or unsafe characters which will be explicitly passed formatted into filenames in the logic described below.

Working with Alternative Metadata Naming Conventions

The iceberg extension can handle different metadata naming conventions by specifying them as a comma-delimited list of format strings via the version_name_format parameter. Each format string must contain two %s parameters. The first is the location of the version number in the metadata filename and the second is the location of the filename extension specified by the metadata_compression_codec. The behavior described above is provided by the default value of "v%s%s.metadata.gz,%s%smetadata.gz. If you had an alternatively named metadata file, e.g., rev-2.metadata.json.gz, the table can be read via the follow statement:

SELECT *
FROM iceberg_snapshots(
    'data/iceberg/alternative_metadata_gz_naming',
    version = '2',
    version_name_format = 'rev-%s.metadata.json%s',
    metadata_compression_codec = 'gzip'
);

“Guessing” Metadata Versions

By default, either a table version number or a version-hint.text must be provided for the iceberg extension to read a table. This is typically provided by an external data catalog. In the event neither is present, the iceberg extension can attempt to guess the latest version by passing ? as the version parameter:

SELECT count(*)
FROM iceberg_scan(
    'data/iceberg/lineitem_iceberg_no_hint',
    version = '?',
    allow_moved_paths = true
);

The “latest” version is assumed to be the filename that is lexicographically largest when sorting the filenames. Collations are not considered. This behavior is not enabled by default as it may potentially violate ACID constraints. It can be enabled by setting unsafe_enable_version_guessing to true. When this is set, iceberg functions will attempt to guess the latest version by default before failing.

SET unsafe_enable_version_guessing = true;
SELECT count(*)
FROM iceberg_scan(
    'data/iceberg/lineitem_iceberg_no_hint',
    allow_moved_paths = true
);