ClickHouse - Query Iceberg and Delta Lake tables and ingest files continuously

Objective

If your data platform stores tables in open formats such as Apache Iceberg or Delta Lake in an OVHcloud Object Storage bucket, ClickHouse can query them in place, without copying them. ClickHouse can also watch a bucket and load every new file into a table automatically.

This guide explains how to query Iceberg and Delta Lake tables, and how to ingest files continuously with the S3Queue engine, from a Public Cloud Analytics with ClickHouse cluster.

Requirements

• A Public Cloud project in your OVHcloud account.
• A ClickHouse cluster running on OVHcloud Public Cloud and accepting incoming connections. See How to create a ClickHouse cluster and How to connect to a ClickHouse cluster.
• The password of the avnadmin user of the ClickHouse cluster.
• An OVHcloud Object Storage bucket created with the S3¹-compatible API, and an S3 user with access to it. See Object Storage - Getting started and Object Storage - Identity and access management.
• To follow the examples, read ClickHouse - Query files stored in OVHcloud Object Storage first: it explains how to build the URL of the files and how to store your S3 credentials in a named collection.

Instructions

Before you begin - Bucket and S3 user

• Create the bucket with the S3-compatible API.
• You can enable server-side encryption with OVHcloud-managed keys (SSE-OMK) on the bucket: it is transparent for ClickHouse and for the tools that write your tables.
• Give the S3 user access to the bucket. The tools that write Iceberg and Delta Lake tables (for example PyIceberg or delta-rs) need more permissions than reading and writing objects, such as multipart uploads. If they fail with Access Denied, give the S3 user all actions on this bucket only:

{
  "Statement": [
    {
      "Sid": "LakehouseBucket",
      "Effect": "Allow",
      "Action": ["s3:*"],
      "Resource": [
        "arn:aws:s3:::<bucket>",
        "arn:aws:s3:::<bucket>/*"
      ]
    }
  ]
}

In the IcebergS3 and DeltaLake table engine examples below, lake_s3 is a named collection that points to a folder of your bucket:

CREATE NAMED COLLECTION lake_s3 AS
    url = 'https://s3.<region>.io.cloud.ovh.net/<bucket>/lakehouse/',
    access_key_id = '<access_key>',
    secret_access_key = '<secret_key>';

Querying an Iceberg table

Pass the URL of the Iceberg table folder (the folder that contains its metadata and data folders) and your S3 keys to the icebergS3 table function. ClickHouse reads the latest version of the table and detects its schema:

SELECT city, count(), sum(amount)
FROM icebergS3('https://s3.<region>.io.cloud.ovh.net/<bucket>/lakehouse/warehouse/demo/sales/', '<access_key>', '<secret_key>')
GROUP BY city;

To query the table regularly, create a table with the IcebergS3 engine:

CREATE TABLE sales_iceberg
ENGINE = IcebergS3(lake_s3, filename = 'warehouse/demo/sales/');

SELECT count() FROM sales_iceberg;

ClickHouse reads the table directly from its folder in the bucket. Connecting ClickHouse to an Iceberg catalog (DataLakeCatalog database engine) is not available.

Querying a Delta Lake table

Pass the URL of the Delta Lake table folder (the folder that contains its _delta_log folder) and your S3 keys to the deltaLakeS3 table function, or create a table with the DeltaLake engine:

SELECT day, round(sum(amount), 1)
FROM deltaLakeS3('https://s3.<region>.io.cloud.ovh.net/<bucket>/lakehouse/delta/sales/', '<access_key>', '<secret_key>')
GROUP BY day
ORDER BY day;

CREATE TABLE sales_delta
ENGINE = DeltaLake(lake_s3, filename = 'delta/sales/');

Ingesting new files continuously with S3Queue

The S3Queue engine watches a folder of your bucket and processes each new file once. Combined with a materialized view, it loads every file dropped in the folder into a ClickHouse table, without any external job.

1. Create the table that receives the data:

   CREATE TABLE events (id UInt64, kind String)
   ENGINE = MergeTree
   ORDER BY id;

2. Create the queue on the folder to watch. mode = 'unordered' processes the files in any order, each file once:

   CREATE TABLE events_queue (id UInt64, kind String)
   ENGINE = S3Queue('https://s3.<region>.io.cloud.ovh.net/<bucket>/queue/*.csv', '<access_key>', '<secret_key>', 'CSVWithNames')
   SETTINGS mode = 'unordered';

3. Create a materialized view that moves the rows of the queue into the table:

   CREATE MATERIALIZED VIEW events_mv TO events
   AS SELECT * FROM events_queue;

From now on, each CSV file written in the queue folder is loaded into events automatically:

SELECT kind, count() FROM events GROUP BY kind;

Troubleshooting

Go further

• ClickHouse documentation - iceberg table functions
• ClickHouse documentation - deltaLake table functions
• ClickHouse documentation - S3Queue table engine
• ClickHouse - Query files stored in OVHcloud Object Storage

We would love to help answer questions and appreciate any feedback you may have.

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

Are you on Discord? Connect to our channel at https://discord.gg/ovhcloud and interact directly with the team that builds our Analytics service!

Join our community of users.

¹: S3 is a trademark of Amazon Technologies, Inc. OVHcloud's service is not sponsored by, endorsed by, or otherwise affiliated with Amazon Technologies, Inc.