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.

Scan Options

The following parameters can be passed to iceberg_scan and iceberg_metadata:

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

ATTACH Options

A REST Catalog with OAuth2 authorization can be attached with just an ATTACH statement. See the complete list of ATTACH options for a REST Catalog below.

When attaching an AWS catalog with ENDPOINT_TYPE (s3_tables or glue), DuckDB applies AWS-appropriate defaults: stage_create_tables and remove_files_on_delete become false and purge_requested becomes true, unless you set them explicitly.

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);

Secret Options

The following options can only be passed to a CREATE SECRET statement and they require AUTHORIZATION_TYPE to be OAUTH2:

Settings

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
);