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 (from v25.10).

Create a DeltaLake table

To create a DeltaLake table it must already exist in S3, GCP or Azure storage. The commands below do not take DDL parameters to create a new table.

S3

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')

GCP

Syntax

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

Unsupported gsutil URI

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 collections

You 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>')

Azure

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

Note

Writing using the table engine is supported only through delta kernel. Writes to Azure are not yet supported but work for S3 and GCS.

Delta Lake writes are a Beta feature 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).

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.

See also

• deltaLake table function