DeltaLake table engine

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/integrations/deltalake.md)

This engine provides an integration with existing [Delta Lake](https://github.com/delta-io/delta) tables in S3, GCP and Azure storage and supports both reads and writes (from v25.10).

## Create a DeltaLake table[​](#create-table "Direct link to 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- 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](https://aws.amazon.com/) 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](/docs/cloud/data-sources/secure-s3) for configuration steps.

Engine parameters can be specified using [Named Collections](/docs/operations/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>')
```

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

**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[​](#insert-data "Direct link to 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_experimental_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.

### Data cache[​](#data-cache "Direct link to Data cache")

The `DeltaLake` table engine and table function support data caching, the same as `S3`, `AzureBlobStorage`, `HDFS` storages. See ["S3 table engine"](/docs/engines/table-engines/integrations/s3#data-cache) for more details.

## See also[​](#see-also "Direct link to See also")

- [deltaLake table function](/docs/sql-reference/table-functions/deltalake)

- [Create a DeltaLake table](#create-table)- [Write data using a DeltaLake table](#insert-data)
    - [Data cache](#data-cache)- [See also](#see-also)

Was this page helpful?