For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/en/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/en/llms-full.txt, and this page is available as Markdown at https://docs.ovhcloud.com/en/guides/public-cloud/data-analytics/analytics/clickhouse-iceberg-delta-s3queue.md.

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

View as Markdown

Find out how to query Apache Iceberg and Delta Lake tables stored in OVHcloud Object Storage from ClickHouse, and load new bucket files automatically

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

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;
Warning

Use Iceberg tables in read-only mode from ClickHouse. Writing into an Iceberg table from ClickHouse is a beta feature: it is refused by default and must not be enabled. Keep writing your Iceberg tables with the tools of your data platform.

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

ErrorCause and solution
Access Denied when your data platform writes the tableThe S3 user does not have all the actions the writer needs. See Before you begin above.
Insert into iceberg is in betaWriting into Iceberg tables from ClickHouse is not supported. Write the table with your data platform.
Only Replicated database can be created through SQLDatabase engines such as DataLakeCatalog are not available. Use the icebergS3 and deltaLakeS3 table functions or the IcebergS3 and DeltaLake table engines.
HTTP response code: 403 … Please check your AWS credentials and permissionsWrong access key or secret key, or the S3 user has no access to the bucket.

Go further

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.

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

Was this page helpful?