DeltaLake

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.

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

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

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

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

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

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

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

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

Syntax

ENGINE = DeltaLake(url [, access_key_id, secret_access_key])

Related

• DeltaLakeS3
• DeltaLakeAzure
• DeltaLakeLocal
• Iceberg
• Hudi