DeltaLake table engine

This engine provides a read-only integration with existing Delta Lake tables in Amazon S3.

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

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