Writing data to open table formats

In the previous guides, you queried open table formats in place and loaded data into MergeTree for fast analytics. In many architectures, data also needs to flow in the other direction - from ClickHouse back into open table formats. Two common scenarios drive this:

Offloading to long-term storage - Data arrives in ClickHouse as a real-time analytics layer, powering dashboards and operational reporting. Once the data ages beyond its real-time window, it can be written out to Iceberg in object storage for durable, cost-effective retention in an interoperable format.
Reverse ETL - Transformations, aggregations, and enrichment performed inside ClickHouse produce derived datasets that downstream tools and other teams need to consume. Writing these results to Iceberg tables makes them available across the broader data ecosystem.

In both cases, INSERT INTO SELECT lets you move data from ClickHouse tables into Iceberg tables stored in object storage.

Writing to open table formats is currently supported for Iceberg tables only. Partial support for Delta Lake tables is under development. Tables must not be managed by a catalog.

Prepare a source dataset

For this guide, we’ll use the UK Price Paid dataset - a public record of every residential property transaction in England and Wales.

Create and populate a MergeTree table

CREATE DATABASE uk;

CREATE TABLE uk.uk_price_paid
(
    price UInt32,
    date Date,
    postcode1 LowCardinality(String),
    postcode2 LowCardinality(String),
    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new UInt8,
    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1 String,
    addr2 String,
    street LowCardinality(String),
    locality LowCardinality(String),
    town LowCardinality(String),
    district LowCardinality(String),
    county LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (postcode1, postcode2, addr1, addr2);

Populate the table directly from the public CSV source:

INSERT INTO uk.uk_price_paid
SELECT
    toUInt32(price_string) AS price,
    parseDateTimeBestEffortUS(time) AS date,
    splitByChar(' ', postcode)[1] AS postcode1,
    splitByChar(' ', postcode)[2] AS postcode2,
    transform(a, ['T', 'S', 'D', 'F', 'O'], ['terraced', 'semi-detached', 'detached', 'flat', 'other']) AS type,
    b = 'Y' AS is_new,
    transform(c, ['F', 'L', 'U'], ['freehold', 'leasehold', 'unknown']) AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM url(
    'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
    'CSV',
    'uuid_string String,
    price_string String,
    time String,
    postcode String,
    a String,
    b String,
    c String,
    addr1 String,
    addr2 String,
    street String,
    locality String,
    town String,
    district String,
    county String,
    d String,
    e String'
) SETTINGS max_http_get_redirects=10;

30906560 rows in set. Elapsed: 59.852 sec. Processed 30.91 million rows, 5.41 GB (516.39 thousand rows/s., 90.40 MB/s.)
Peak memory usage: 485.15 MiB.

Write data to an Iceberg table

Create the Iceberg table

For direct reads from an existing Iceberg table, we recommend the icebergS3 table function. This guide demonstrates creating and populating standalone Iceberg tables, so it uses the IcebergS3 table engine with an explicit schema. The example S3 locations already contain the completed tables; to run the CREATE and INSERT statements, replace both locations with empty, writable S3 prefixes and credentials you control. Note that the schema must be simplified compared to the MergeTree source. ClickHouse supports a richer type system than Iceberg and the underlying Parquet files - types such as Enum, LowCardinality, and UInt8 are not supported in Iceberg and must be mapped to compatible types.

CREATE TABLE uk.uk_iceberg
(
    price UInt32,
    date Date,
    postcode1 String,
    postcode2 String,
    type UInt32,
    is_new UInt32,
    duration UInt32,
    addr1 String,
    addr2 String,
    street String,
    locality String,
    town String,
    district String,
    county String
)
ENGINE = IcebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg_uk_price_paid/', '<aws_access_key>', '<aws_secret_key>', '<session_token>')

Insert a subset of data

Use INSERT INTO SELECT to write data from the MergeTree table into the Iceberg table. In this example, we write only London transactions: This example requires the allow_insert_into_iceberg setting. See the support matrix for current maturity and limitations.

SET allow_insert_into_iceberg = 1;

INSERT INTO uk.uk_iceberg SELECT *
FROM uk.uk_price_paid
WHERE town = 'LONDON'

2346741 rows in set. Elapsed: 1.419 sec. Processed 30.91 million rows, 153.43 MB (21.78 million rows/s., 108.15 MB/s.)
Peak memory usage: 371.60 MiB.

Query the Iceberg table

The data is now stored as Iceberg in object storage and can be queried from ClickHouse - or any other tool that reads Iceberg:

SELECT
    locality,
    count()
FROM uk.uk_iceberg
WHERE locality != ''
GROUP BY locality
ORDER BY count() DESC
LIMIT 10

┌─locality────┬─count()─┐
│ LONDON      │  896796 │
│ WALTHAMSTOW │    8610 │
│ LEYTON      │    3525 │
│ CHINGFORD   │    3133 │
│ HORNSEY     │    2794 │
│ STREATHAM   │    2760 │
│ WOOD GREEN  │    2443 │
│ ACTON       │    2155 │
│ LEYTONSTONE │    2102 │
│ EAST HAM    │    2085 │
└─────────────┴─────────┘

10 rows in set. Elapsed: 0.329 sec. Processed 457.86 thousand rows, 2.62 MB (1.39 million rows/s., 7.95 MB/s.)
Peak memory usage: 12.19 MiB.

Write aggregated results

Iceberg tables are not limited to storing raw rows. They can also hold the output of aggregations and transformations - the results of ETL processes performed inside ClickHouse. This is useful for publishing pre-computed summaries to a lakehouse for downstream consumption.

Create an Iceberg table for aggregates

CREATE TABLE uk.uk_avg_town
(
    price Float64,
    town String
)
ENGINE = IcebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg_uk_avg_town/', '<aws_access_key>', '<aws_secret_key>', '<session_token>')

Insert aggregated data

Compute average property prices by town and write the results directly into Iceberg:

INSERT INTO uk.uk_avg_town SELECT
    avg(price) AS price,
    town
FROM uk.uk_price_paid
GROUP BY town

1173 rows in set. Elapsed: 0.480 sec. Processed 30.91 million rows, 185.44 MB (64.34 million rows/s., 386.05 MB/s.)
Peak memory usage: 4.18 MiB.

Query the aggregated table

Other tools - and other ClickHouse instances - can now read this pre-computed dataset:

SELECT
    town,
    price
FROM uk.uk_avg_town
ORDER BY price DESC
LIMIT 10

┌─town───────────────┬──────────────price─┐
│ GATWICK            │ 28232811.583333332 │
│ THORNHILL          │             985000 │
│ VIRGINIA WATER     │  984633.2938574939 │
│ CHALFONT ST GILES  │  863347.7280187573 │
│ COBHAM             │    775251.47313278 │
│ PURFLEET-ON-THAMES │           772651.8 │
│ BEACONSFIELD       │  746052.9327405858 │
│ ESHER              │  686708.4969745865 │
│ KESTON             │  654541.1774842045 │
│ GERRARDS CROSS     │  639109.4084023251 │
└────────────────────┴────────────────────┘

10 rows in set. Elapsed: 0.210 sec.

ClickHouse guides

Use case guides

Prepare a source dataset

Create and populate a MergeTree table

Write data to an Iceberg table

Create the Iceberg table

Insert a subset of data

Query the Iceberg table

Write aggregated results

Create an Iceberg table for aggregates

Insert aggregated data

Query the aggregated table

​Prepare a source dataset

​Create and populate a MergeTree table

​Write data to an Iceberg table

​Create the Iceberg table

​Insert a subset of data

​Query the Iceberg table

​Write aggregated results

​Create an Iceberg table for aggregates

​Insert aggregated data

​Query the aggregated table

Prepare a source dataset

Create and populate a MergeTree table

Write data to an Iceberg table

Create the Iceberg table

Insert a subset of data

Query the Iceberg table

Write aggregated results

Create an Iceberg table for aggregates

Insert aggregated data

Query the aggregated table