iceberg Table Function

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[​](#scenario-2 "Direct link to 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 bigint, 
  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[​](#scenario-3 "Direct link to 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 bigint, 
  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[​](#metadata-file-resolution "Direct link to Metadata File Resolution")

When using the `iceberg` table function 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:

### Candidate Search (in Priority Order)[​](#candidate-search "Direct link to Candidate Search (in Priority Order)")

1. **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.

2. **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)
3. **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[​](#most-recent-file "Direct link to 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 are table function settings (not global or query-level settings) and must be specified as shown below:

```
SELECT * FROM iceberg('s3://bucket/path/to/iceberg_table', 
    SETTINGS iceberg_metadata_table_uuid = 'a90eed4c-f74b-4e5b-b630-096fb9d09021');
```

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

## Metadata cache[​](#metadata-cache "Direct link to 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.

## Aliases[​](#aliases "Direct link to Aliases")

Table function `iceberg` is an alias to `icebergS3` now.

## Virtual Columns[​](#virtual-columns "Direct link to Virtual Columns")

- `_path` — Path to the file. Type: `LowCardinality(String)`.
- `_file` — Name of the file. Type: `LowCardinality(String)`.
- `_size` — Size of the file in bytes. Type: `Nullable(UInt64)`. If the file size is unknown, the value is `NULL`.
- `_time` — Last modified time of the file. Type: `Nullable(DateTime)`. If the time is unknown, the value is `NULL`.
- `_etag` — The etag of the file. Type: `LowCardinality(String)`. If the etag is unknown, the value is `NULL`.

## Writes into iceberg table[​](#writes-into-iceberg-table "Direct link to Writes into iceberg table")

Starting from version 25.7, ClickHouse supports modifications of user’s Iceberg tables.

Currently, this is an experimental feature, so you first need to enable it:

```
SET allow_insert_into_iceberg = 1;
```

### Creating table[​](#create-iceberg-table "Direct link to Creating table")

To create your own empty Iceberg table, use the same commands as for reading, but specify the schema explicitly.
Writes supports all data formats from iceberg specification, such as Parquet, Avro, ORC.

### Example[​](#example-iceberg-writes-create "Direct link to Example")

```
CREATE TABLE iceberg_writes_example
(
    x Nullable(String),
    y Nullable(Int32)
)
ENGINE = IcebergLocal('/home/scanhex12/iceberg_example/')
```

Note: To create a version hint file, enable the `iceberg_use_version_hint` setting.
If you want to compress the metadata.json file, specify the codec name in the `iceberg_metadata_compression_method` setting.

### INSERT[​](#writes-inserts "Direct link to INSERT")

After creating a new table, you can insert data using the usual ClickHouse syntax.

### Example[​](#example-iceberg-writes-insert "Direct link to Example")

```
INSERT INTO iceberg_writes_example VALUES ('Pavel', 777), ('Ivanov', 993);

SELECT *
FROM iceberg_writes_example
FORMAT VERTICAL;

Row 1:
──────
x: Pavel
y: 777

Row 2:
──────
x: Ivanov
y: 993
```

### DELETE[​](#iceberg-writes-delete "Direct link to DELETE")

Deleting extra rows in the merge-on-read format is also supported in ClickHouse.
This query will create a new snapshot with position delete files.