DeltaLake table engine

This engine provides an integration with existing Delta Lake tables in S3, GCP and Azure storage and supports both reads and writes (writes for S3 and GCS from v25.10, for Azure from v26.9).

Create a DeltaLake table

By default the Delta Lake table must already exist in S3, GCP or Azure storage, and the commands below attach to it without DDL column definitions. With allow_delta_lake_create_table = 1, a CREATE TABLE with explicit columns against a location that has no _delta_log instead creates a new Delta Lake table by writing the initial commit through delta-kernel-rs (creating a partitioned table is not supported yet), and inside a Unity DataLakeCatalog database the table is also registered in the catalog.

S3
GCP
Azure

Syntax

CREATE TABLE table_name
ENGINE = DeltaLake(url, [aws_access_key_id, aws_secret_access_key,] [extra_credentials])

Engine parameters

url — Bucket url with path to the existing Delta Lake table.
aws_access_key_id, aws_secret_access_key - Long-term credentials for the AWS account user. You can use these to authenticate your requests. Parameter is optional. If credentials are not specified, they are used from the configuration file.
extra_credentials - Optional. Used to pass a role_arn for role-based access in ClickHouse Cloud. See Secure S3 for configuration steps.

Engine parameters can be specified using Named Collections.Example

CREATE TABLE deltalake
ENGINE = DeltaLake('http://mars-doc-test.s3.amazonaws.com/clickhouse-bucket-3/test_table/', 'ABC123', 'Abc+123')

Using named collections:

<clickhouse>
    <named_collections>
        <deltalake_conf>
            <url>http://mars-doc-test.s3.amazonaws.com/clickhouse-bucket-3/</url>
            <access_key_id>ABC123</access_key_id>
            <secret_access_key>Abc+123</secret_access_key>
        </deltalake_conf>
    </named_collections>
</clickhouse>

CREATE TABLE deltalake
ENGINE = DeltaLake(deltalake_conf, filename = 'test_table')

Syntax

-- Using HTTPS URL (recommended)
CREATE TABLE table_name
ENGINE = DeltaLake('https://storage.googleapis.com/<bucket>/<path>/', '<access_key_id>', '<secret_access_key>')

gsutil URI such as gs://clickhouse-docs-example-bucket is not supported, please use a URL starting https://storage.googleapis.com

Arguments

url — GCS bucket URL to the Delta Lake table. Must use https://storage.googleapis.com/<bucket>/<path>/ format (the GCS XML API endpoint), or gs://<bucket>/<path>/ which is auto-converted.
access_key_id — GCS Access Key. Create via Google Cloud Console → Cloud Storage → Settings → Interoperability.
secret_access_key — GCS secret.

Named collectionsYou can also use named collections. For example:

CREATE NAMED COLLECTION gcs_creds AS
access_key_id = '<access_key>',
secret_access_key = '<secret>';

CREATE TABLE gcpDeltaLake
ENGINE = DeltaLake(gcs_creds, url = 'https://storage.googleapis.com/<bucket>/<path>')

Syntax

CREATE TABLE table_name
ENGINE = DeltaLake(connection_string|storage_account_url, container_name, blobpath, [account_name, account_key, format, compression])

Arguments

connection_string — Azure connection string
storage_account_url — Azure storage account URL (e.g., https://account.blob.core.windows.net)
container_name — Azure container name
blobpath — Path to the Delta Lake table within the container
account_name — Azure storage account name
account_key — Azure storage account key

Write data using a DeltaLake table

Once you have created a table using the DeltaLake table engine, you can insert data into it with:

SET allow_delta_lake_writes = 1;

INSERT INTO deltalake(id, firstname, lastname, gender, age)
VALUES (1, 'John', 'Smith', 'M', 32);

Delta Lake writes are a Beta feature disabled by default and must be enabled with SET allow_delta_lake_writes = 1; (available from version 26.7; on earlier versions use SET allow_experimental_delta_lake_writes = 1;).

Writing using the table engine is supported only through delta kernel. Writes work for S3 and GCS, and for Azure from version 26.9. Azure workload identity authentication (extra_credentials(client_id = ..., tenant_id = ...)) is not supported by delta kernel.

Data cache

The DeltaLake table engine and table function support data caching, the same as S3, AzureBlobStorage, HDFS storages. See “S3 table engine” for more details.

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Create a DeltaLake table

Write data using a DeltaLake table

Data cache

See also

​Create a DeltaLake table

​Write data using a DeltaLake table

​Data cache

​See also

Create a DeltaLake table

Write data using a DeltaLake table

Data cache

See also