Iceberg table engine

Use the Iceberg Table Function for direct access to an existing Iceberg table. Use the Iceberg Table Engine when you need a persistent ClickHouse table or want to create a new standalone Iceberg table with an explicit schema on a writable backend.The Iceberg Table Engine is available but may have limitations. ClickHouse wasn’t originally designed to support tables with externally changing schemas, which can affect the functionality of the Iceberg Table Engine. As a result, some features that work with regular tables may be unavailable or may not function correctly, especially when using the old analyzer.

This engine provides a data integration with Apache Iceberg tables in Amazon S3, Azure, HDFS and locally stored tables.

Create table

Without an explicit schema, the Iceberg table must already exist in storage. To create a new standalone Iceberg table on a writable backend, specify its schema in the CREATE TABLE statement.

CREATE TABLE iceberg_table_s3
    ENGINE = IcebergS3(url, [, NOSIGN | access_key_id, secret_access_key, [session_token]], format, [,compression], [,extra_credentials])

CREATE TABLE iceberg_table_azure
    ENGINE = IcebergAzure(connection_string|storage_account_url, container_name, blobpath, [account_name, account_key, format, compression])

CREATE TABLE iceberg_table_hdfs
    ENGINE = IcebergHDFS(path_to_table, [,format] [,compression_method])

CREATE TABLE iceberg_table_local
    ENGINE = IcebergLocal(path_to_table, [,format] [,compression_method])

Engine arguments

Description of the arguments coincides with description of arguments in engines S3, AzureBlobStorage, HDFS and File correspondingly. format stands for the format of data files in the Iceberg table. For IcebergS3, an optional extra_credentials parameter can be used to pass a role_arn for role-based access in ClickHouse Cloud. See Secure S3 for configuration steps. Engine parameters can be specified using Named Collections

Example

CREATE TABLE iceberg_table ENGINE=IcebergS3('http://test.s3.amazonaws.com/clickhouse-bucket/test_table', 'test', 'test')

Using named collections:

<clickhouse>
    <named_collections>
        <iceberg_conf>
            <url>http://test.s3.amazonaws.com/clickhouse-bucket/</url>
            <access_key_id>test</access_key_id>
            <secret_access_key>test</secret_access_key>
        </iceberg_conf>
    </named_collections>
</clickhouse>

CREATE TABLE iceberg_table ENGINE=IcebergS3(iceberg_conf, filename = 'test_table')

Aliases

The Iceberg table engine auto-detects the storage backend from the disk setting and dispatches to IcebergS3, IcebergAzure, or IcebergLocal accordingly. When no disk is specified, it defaults to the IcebergS3 implementation.

Data types

The following table shows how Iceberg data types are mapped to ClickHouse data types during schema inference (for reading purposes).

Primitive types

Iceberg type	ClickHouse type	Notes
`boolean`	`Bool`
`int`	`Int32`
`long`, `bigint`	`Int64`
`float`	`Float32`
`double`	`Float64`
`date`	`Date32`
`time`	`Int64`	Microseconds since midnight
`timestamp`	`DateTime64(6)`	Microseconds, no timezone
`timestamptz`	`DateTime64(6, 'UTC')`	Microseconds, UTC timezone
`timestamp_ns`	`DateTime64(9)`	Nanoseconds, no timezone (since Iceberg v3 only)
`timestamptz_ns`	`DateTime64(9, 'UTC')`	Nanoseconds, UTC timezone (since Iceberg v3 only)
`string`, `binary`	`String`
`uuid`	`UUID`
`fixed(N)`	`FixedString(N)`
`decimal(P, S)`	`Decimal(P, S)`

Complex types

Iceberg type	ClickHouse type
`list`	`Array`
`map`	`Map`
`struct`	`Tuple`

Schema and write compatibility limitations

The data type mappings above apply to reads. The following limitations apply when ClickHouse creates or evolves Iceberg schemas and writes data:

ClickHouse cannot create an Iceberg schema containing Bool, Decimal, FixedString, Int8, UInt8, Int16, or UInt16, or add or modify a column to one of these types. The operation fails with an unsupported-type exception. This does not prevent inserting Bool or Decimal values into existing Iceberg columns.
When ClickHouse creates or evolves an Iceberg schema, it maps every DateTime and DateTime64 column to Iceberg timestamp, which has microsecond precision and no timezone. ClickHouse cannot generate timestamptz, timestamp_ns, or timestamptz_ns schema types, so higher precision and timezone semantics are not represented in the Iceberg schema.
ClickHouse cannot write to an Iceberg table that uses a decimal, fixed, timestamp_ns, or timestamptz_ns column as a direct partition field. The operation fails with an unsupported-type exception.
For data files containing types whose bounds ClickHouse cannot serialize, including Bool and Decimal, ClickHouse omits all lower and upper column bounds from the Iceberg manifest entry. Column sizes and null counts are still included, and the data remains correct, but readers cannot use manifest-level min-max pruning for those files.

Schema evolution

ClickHouse supports reading Iceberg tables whose schema has evolved over time. This includes tables where columns have been added, removed, or reordered, as well as columns changed from required to nullable. Additionally, the following type casts are supported:

int -> long
float -> double
decimal(P, S) -> decimal(P’, S) where P’ > P.

Currently, it is not possible to change nested structures or the types of elements within arrays and maps. To read a table where the schema has changed after its creation with dynamic schema inference, set allow_dynamic_metadata_for_data_lakes = true when creating the table.

Partition pruning

ClickHouse supports partition pruning during SELECT queries for Iceberg tables, which helps optimize query performance by skipping irrelevant data files. To enable partition pruning, set use_iceberg_partition_pruning = 1. For more information about iceberg partition pruning address https://iceberg.apache.org/spec/#partitioning

`DROP PARTITION`

ALTER TABLE ... DROP PARTITION <value> removes every data file belonging to a single partition and creates a new snapshot that no longer references them. It is currently supported for local and object-storage Iceberg tables, but not for catalog-backed tables. Enable allow_insert_into_iceberg to use this operation. The operation is supported only for Iceberg format-version 2 tables with a single, non-evolved partition spec. Each manifest containing the selected partition must contain no files from other partitions. If a manifest is shared by the selected partition and another partition, the operation fails without changing the table. The operation also rejects affected manifests containing equality-delete files. The partition value follows the same rules as for MergeTree. For a single-column partition, pass a scalar literal; for a multi-column partition, pass a tuple of values:

ALTER TABLE iceberg_table DROP PARTITION 2;
ALTER TABLE iceberg_table DROP PARTITION (2, 5);

For a partition defined with a transform, you can supply either the already-transformed partition-key value as a literal, or the same transform expression applied to a raw source value. The supported transforms are identity, icebergBucket, icebergTruncate, icebergYear, icebergMonth, icebergDay, and icebergHour; the PARTITION BY aliases toYearNumSinceEpoch, toMonthNumSinceEpoch, toRelativeDayNum, and toRelativeHourNum are accepted and evaluated as these transforms. For a single-column partition the transform-expression form must be wrapped in tuple(...):

ALTER TABLE iceberg_table DROP PARTITION 0;
ALTER TABLE iceberg_table DROP PARTITION tuple(icebergBucket(4, 'apple'));

The operation rejects explicitly set iceberg_snapshot_id, iceberg_timestamp_ms, or iceberg_metadata_file_path settings. It modifies the current table state, not a historical snapshot or an explicitly selected metadata version. The DROP PARTITION ID '...' and DROP PARTITION ALL forms are not supported. Dropping a partition that does not exist is a no-op. The operation does not physically delete the data files. Earlier snapshots retain access to the removed rows and remain available to time-travel queries until those snapshots expire and their files are cleaned up.

Time travel

ClickHouse supports time travel for Iceberg tables, allowing you to query historical data with a specific timestamp or snapshot ID.

Manifest file compaction

Over time, frequent writes to an Iceberg table can accumulate a large number of small manifest files in the current snapshot’s manifest list. A long manifest list slows down query planning, because every manifest file has to be read to discover the data files. ClickHouse can compact these manifest files into fewer, larger ones using the OPTIMIZE TABLE ... MANIFEST statement:

OPTIMIZE TABLE example_table MANIFEST SETTINGS allow_experimental_iceberg_compaction = 1;

This produces a new snapshot (a replace operation) that references the same data files through a consolidated set of manifest files. No data files are rewritten and no rows are added, deleted, or deduplicated — only the manifest layer is rearranged.

Requirements and behavior

The feature is experimental and gated behind the allow_experimental_iceberg_compaction setting. The statement throws an exception if the setting is not enabled.
Compaction is only attempted when the number of manifest files in the current snapshot’s manifest list exceeds the threshold given by the iceberg_manifest_min_count_to_compact setting (default 100, the documented default of the Iceberg table property commit.manifest.min-count-to-merge). If the current count is less than or equal to the threshold, compaction is skipped and no new snapshot is created. Set the threshold lower to compact more eagerly.
OPTIMIZE TABLE ... MANIFEST is supported only for Iceberg tables. Running it against any other table engine throws an exception.
OPTIMIZE TABLE ... MANIFEST is supported only for Iceberg format-version 2 tables. Running it against a format-version 1 table throws an exception, and so does running it against a format-version 3 table, because the v3 row-lineage first_row_id metadata is not yet round-tripped through the manifest rewrite.
OPTIMIZE TABLE ... MANIFEST is not supported for encrypted Iceberg tables whose data files contain per-file key_metadata. Preserving this encryption metadata across a manifest rewrite is not yet implemented, so the statement throws a NOT_IMPLEMENTED exception.

Processing of tables with deleted rows

ClickHouse supports reading Iceberg tables that use the following deletion methods:

Position deletes
Equality deletes (supported from version 25.8+)
Deletion vectors (introduced in v3)

Deletion vector support is read-only. ClickHouse does not write, update, or compact deletion vectors. ALTER TABLE ... DELETE and ALTER TABLE ... UPDATE are not supported for Iceberg format-version 3 tables.

Basic usage

 SELECT * FROM example_table ORDER BY 1
SETTINGS iceberg_timestamp_ms = 1714636800000

 SELECT * FROM example_table ORDER BY 1
SETTINGS iceberg_snapshot_id = 3547395809148285433

Note: You cannot specify both iceberg_timestamp_ms and iceberg_snapshot_id parameters in the same query.

Important considerations

Snapshots are typically created when:
- New data is written to the table
- Some kind of data compaction is performed
Schema changes typically don’t create snapshots - This leads to important behaviors when using time travel with tables that have undergone schema evolution.

Example scenarios

These scenarios use Spark to illustrate schema changes made by an external Iceberg writer.

Scenario 1: Schema changes without new snapshots

Consider this sequence of operations:

-- Create a table with two columns
CREATE TABLE IF NOT EXISTS spark_catalog.db.time_travel_example (
order_number int,
product_code string
  )
USING iceberg
OPTIONS ('format-version'='2')

-- Insert data into the table
INSERT INTO spark_catalog.db.time_travel_example VALUES
    (1, 'Mars')

  ts1 = now() // A piece of pseudo code

-- Alter table to add a new column
ALTER TABLE spark_catalog.db.time_travel_example ADD COLUMN (price double)

ts2 = now()

-- Insert data into the table
INSERT INTO spark_catalog.db.time_travel_example VALUES (2, 'Venus', 100)

ts3 = now()

-- Query the table at each timestamp
  SELECT * FROM spark_catalog.db.time_travel_example TIMESTAMP AS OF ts1;

+------------+------------+
|order_number|product_code|
+------------+------------+
|           1|        Mars|
+------------+------------+
  SELECT * FROM spark_catalog.db.time_travel_example TIMESTAMP AS OF ts2;

+------------+------------+
|order_number|product_code|
+------------+------------+
|           1|        Mars|
+------------+------------+

  SELECT * FROM spark_catalog.db.time_travel_example TIMESTAMP AS OF ts3;

+------------+------------+-----+
|order_number|product_code|price|
+------------+------------+-----+
|           1|        Mars| NULL|
|           2|       Venus|100.0|
+------------+------------+-----+

Query results at different timestamps:

At ts1 & ts2: Only the original two columns appear
At ts3: All three columns appear, with NULL for the price of the first row

Scenario 2: Historical vs. current schema differences

A time travel query at a current moment might show a different schema than the current table:

-- Create a table
CREATE TABLE IF NOT EXISTS spark_catalog.db.time_travel_example_2 (
order_number int,
product_code string
  )
USING iceberg
OPTIONS ('format-version'='2')

-- Insert initial data into the table
INSERT INTO spark_catalog.db.time_travel_example_2 VALUES (2, 'Venus');

-- Alter table to add a new column
ALTER TABLE spark_catalog.db.time_travel_example_2 ADD COLUMN (price double);

ts = now();

-- Query the table at a current moment but using timestamp syntax

  SELECT * FROM spark_catalog.db.time_travel_example_2 TIMESTAMP AS OF ts;

    +------------+------------+
    |order_number|product_code|
    +------------+------------+
    |           2|       Venus|
    +------------+------------+

-- Query the table at a current moment
  SELECT * FROM spark_catalog.db.time_travel_example_2;
    +------------+------------+-----+
    |order_number|product_code|price|
    +------------+------------+-----+
    |           2|       Venus| NULL|
    +------------+------------+-----+

This happens because ALTER TABLE doesn’t create a new snapshot but for the current table Spark takes value of schema_id from the latest metadata file, not a snapshot.

Scenario 3: Historical vs. current schema differences

The second one is that while doing time travel you can’t get state of table before any data was written to it:

-- Create a table
CREATE TABLE IF NOT EXISTS spark_catalog.db.time_travel_example_3 (
order_number int,
product_code string
  )
USING iceberg
OPTIONS ('format-version'='2');

ts = now();

-- Query the table at a specific timestamp
  SELECT * FROM spark_catalog.db.time_travel_example_3 TIMESTAMP AS OF ts; -- Finises with error: Cannot find a snapshot older than ts.

In ClickHouse the behavior is consistent with Spark. You can mentally replace Spark Select queries with ClickHouse Select queries and it will work the same way.

Metadata file resolution

When using the Iceberg table engine in ClickHouse, the system needs to locate the correct metadata.json file that describes the Iceberg table structure. Here’s how this resolution process works:

Candidates search

Direct Path Specification:

If you set iceberg_metadata_file_path, the system will use this exact path by combining it with the Iceberg table directory path.
When this setting is provided, all other resolution settings are ignored.

Table UUID Matching:

If iceberg_metadata_table_uuid is specified, the system will:
- Look only at .metadata.json files in the metadata directory
- Filter for files containing a table-uuid field matching your specified UUID (case-insensitive)

Default Search:

If neither of the above settings are provided, all .metadata.json files in the metadata directory become candidates

Selecting the most recent file

After identifying candidate files using the above rules, the system determines which one is the most recent:

If iceberg_recent_metadata_file_by_last_updated_ms_field is enabled:
- The file with the largest last-updated-ms value is selected
Otherwise:
- The file with the highest version number is selected
- (Version appears as V in filenames formatted as V.metadata.json or V-uuid.metadata.json)

Note: All mentioned settings (unless explicitly specified otherwise) are engine-level settings and must be specified during table creation as shown below:

CREATE TABLE example_table ENGINE = Iceberg(
    's3://bucket/path/to/iceberg_table'
) SETTINGS iceberg_metadata_table_uuid = '6f6f6407-c6a5-465f-a808-ea8900e35a38';

Note: While Iceberg Catalogs typically handle metadata resolution, the Iceberg table engine in ClickHouse directly interprets files stored in S3 as Iceberg tables, which is why understanding these resolution rules is important.

Data cache

Iceberg table engine and table function support data caching same as S3, AzureBlobStorage, HDFS storages. See here.

Metadata cache

Iceberg table engine and table function support metadata cache storing the information of manifest files, manifest list and metadata json. The cache is stored in memory. This feature is controlled by setting use_iceberg_metadata_files_cache, which is enabled by default.

Asynchronous metadata prefetching

Asynchronous metadata prefetching can be enabled at Iceberg table creation by setting iceberg_metadata_async_prefetch_period_ms. If set to 0 (default) or if metadata caching is not enabled, the asynchronous prefetching is disabled. In order to enable this feature, a non-zero value of milliseconds should be given. It represents interval between prefetching cycles. If enabled, the server will run a recurring background operation to list the remote catalog and to detect new metadata version. It will then parse it and recursively walk the snapshot, fetching active manifest list files and manifest files. The files already available at the metadata cache, won’t be downloaded again. At the end of each prefetching cycle, the latest metadata snapshot is available at the metadata cache.

CREATE TABLE example_table ENGINE = Iceberg(
    's3://bucket/path/to/iceberg_table'
) SETTINGS
    iceberg_metadata_async_prefetch_period_ms = 60000;

In order to make the most of asynchronous metadata prefetching at read operations, iceberg_metadata_staleness_ms parameter should be specified as Query or Session parameter. By default (0 - not specified) in the context of each query, the server will fetch latest metadata from the remote catalog. By specifying tolerance to metadata staleness, the server is allowed to use the cached version of metadata snapshot without calling the remote catalog. If there’s metadata version in cache, and it has been downloaded within the given window of staleness, it will be used to process the query. Otherwise the latest version will be fetched from the remote catalog.

SELECT count() FROM icebench_table WHERE ...
SETTINGS iceberg_metadata_staleness_ms=120000

Note: Asynchronous metadata prefetching runs at ICEBERG_SCEDULE_POOL, which is server-side threadpool for background operations on active Iceberg tables. The size of this threadpool is controlled by iceberg_background_schedule_pool_size server configuration parameter (default is 10). Note: Current expectation is that metadata cache size is sufficient to hold the latest metadata snapshot in full for all active tables, if asynchronous prefetching is enabled.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Create table

Engine arguments

Example

Aliases

Data types

Primitive types

Complex types

Schema and write compatibility limitations

Schema evolution

Partition pruning

`DROP PARTITION`

Time travel

Manifest file compaction

Requirements and behavior

Processing of tables with deleted rows

Basic usage

Important considerations

Example scenarios

Scenario 1: Schema changes without new snapshots

Scenario 2: Historical vs. current schema differences

Scenario 3: Historical vs. current schema differences

Metadata file resolution

Candidates search

Selecting the most recent file

Data cache

Metadata cache

Asynchronous metadata prefetching

See also

​Create table

​Engine arguments

​Example

​Aliases

​Data types

​Primitive types

​Complex types

​Schema and write compatibility limitations

​Schema evolution

​Partition pruning

​DROP PARTITION

​Time travel

​Manifest file compaction

​Requirements and behavior

​Processing of tables with deleted rows

​Basic usage

​Important considerations

​Example scenarios

​Scenario 1: Schema changes without new snapshots

​Scenario 2: Historical vs. current schema differences

​Scenario 3: Historical vs. current schema differences

​Metadata file resolution

​Candidates search

​Selecting the most recent file

​Data cache

​Metadata cache

​Asynchronous metadata prefetching

​See also

Create table

Engine arguments

Example

Aliases

Data types

Primitive types

Complex types

Schema and write compatibility limitations

Schema evolution

Partition pruning

`DROP PARTITION`

Time travel

Manifest file compaction

Requirements and behavior

Processing of tables with deleted rows

Basic usage

Important considerations

Example scenarios

Scenario 1: Schema changes without new snapshots

Scenario 2: Historical vs. current schema differences

Scenario 3: Historical vs. current schema differences

Metadata file resolution

Candidates search

Selecting the most recent file

Data cache

Metadata cache

Asynchronous metadata prefetching

See also