Getting started with data lakes

The fastest way to start — especially for ad hoc queries or when you don’t use a catalog — is the icebergS3() table function. Point it at an Iceberg table in S3 and query immediately, no setup required.

Inspect the schema:

DESCRIBE icebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')

Run a query:

SELECT
    url,
    count() AS cnt
FROM icebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')
GROUP BY url
ORDER BY cnt DESC
LIMIT 5

ClickHouse reads the Iceberg metadata directly from S3 and infers the schema automatically. The same approach works for deltaLake(), hudi(), and paimon().

Learn more: Querying open table formats directly covers all four formats, cluster variants for distributed reads, and storage backend options (S3, Azure, HDFS, local).

When you don’t use a catalog but will query the same path repeatedly, create a table using the Iceberg table engine so you don’t need to pass the path every time. The data stays in S3 — no data is duplicated:

CREATE TABLE hits_iceberg
    ENGINE = IcebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')

Now query it like any ClickHouse table:

SELECT
    url,
    count() AS cnt
FROM hits_iceberg
GROUP BY url
ORDER BY cnt DESC
LIMIT 5

The table engine supports data caching, metadata caching, schema evolution, and time travel. See the Querying directly guide for details on table engine features and the support matrix for a full feature comparison.

If your organization uses a data catalog, this is the integration path we recommend. Catalogs centralize table metadata and discovery — instead of managing a table definition for every storage path, connect once with the DataLakeCatalog database engine. Every table in the catalog appears as a ClickHouse table, including tables added upstream after you create the connection.

Here’s an example connecting to AWS Glue:

CREATE DATABASE my_lake
ENGINE = DataLakeCatalog
SETTINGS
    catalog_type = 'glue',
    region = '<your-region>',
    aws_access_key_id = '<your-access-key>',
    aws_secret_access_key = '<your-secret-key>'

Each catalog type requires its own connection settings — see the Catalogs guides for the full list of supported catalogs and their configuration options.

Browse tables and query:

SHOW TABLES FROM my_lake;

SELECT count(*) FROM my_lake.`<database>.<table>`

Learn more: Connecting to a data catalog walks through a full Unity Catalog setup with Delta and Iceberg examples.

Regardless of which method you used above — table function, table engine, or DataLakeCatalog — the same ClickHouse SQL works across all of them. In production with a catalog, query through the DataLakeCatalog database; the other examples remain useful for quick tests and path-based access:

-- Table function
SELECT url, count() AS cnt
FROM icebergS3('https://datasets-documentation.s3.amazonaws.com/lake_formats/iceberg/')
GROUP BY url ORDER BY cnt DESC LIMIT 5

-- Table engine
SELECT url, count() AS cnt
FROM hits_iceberg
GROUP BY url ORDER BY cnt DESC LIMIT 5

-- Catalog
SELECT url, count() AS cnt
FROM my_lake.`<database>.<table>`
GROUP BY url ORDER BY cnt DESC LIMIT 5

The query syntax is identical — only the FROM clause changes. All ClickHouse SQL functions, joins, and aggregations work the same way regardless of the data source.

Querying Iceberg directly is convenient, but performance is bounded by network throughput and the file layout. For analytical workloads, load data into a native MergeTree table.

First, run a filtered query over the Iceberg table to get a baseline:

SELECT
    url,
    count() AS cnt
FROM hits_iceberg
WHERE counterid = 38
GROUP BY url
ORDER BY cnt DESC
LIMIT 5

This query scans the full dataset in S3 since Iceberg has no awareness of the counterid filter — expect it to take several seconds.

Now create a MergeTree table and load the data:

CREATE TABLE hits_clickhouse
(
    url String,
    eventtime DateTime,
    counterid UInt32
)
ENGINE = MergeTree()
ORDER BY (counterid, eventtime);

INSERT INTO hits_clickhouse
SELECT url, eventtime, counterid
FROM hits_iceberg

Re-run the same query against the MergeTree table:

SELECT
    url,
    count() AS cnt
FROM hits_clickhouse
WHERE counterid = 38
GROUP BY url
ORDER BY cnt DESC
LIMIT 5

Because counterid is the first column in the ORDER BY key, ClickHouse’s sparse primary index skips directly to the relevant granules — only reading the rows for counterid = 38 instead of scanning all 100 million rows. The result is a dramatic speedup.

The accelerating analytics guide takes this further with LowCardinality types, full-text indices, and optimized ordering keys, demonstrating a ~40x improvement on a 283 million row dataset.

Learn more: Accelerating analytics with MergeTree covers schema optimization, full-text indexing, and a complete before/after performance comparison.

ClickHouse can also write data back to Iceberg tables, enabling reverse ETL workflows — publishing aggregated results or subsets for consumption by other tools (Spark, Trino, DuckDB, etc.).

Create an Iceberg table for output:

CREATE TABLE output_iceberg
(
    url String,
    cnt UInt64
)
ENGINE = IcebergS3('https://your-bucket.s3.amazonaws.com/output/', 'access_key', 'secret_key')

Write aggregated results:

SET allow_experimental_insert_into_iceberg = 1;

INSERT INTO output_iceberg
SELECT
    url,
    count() AS cnt
FROM hits_clickhouse
GROUP BY url
ORDER BY cnt DESC

The resulting Iceberg table is readable by any Iceberg-compatible engine.

Learn more: Writing data to open table formats covers writing raw data and aggregated results using the UK Price Paid dataset, including schema considerations when mapping ClickHouse types to Iceberg.