DataLakeCatalog

The DataLakeCatalog database engine enables you to connect ClickHouse to external data catalogs and query open table format data without the need for data duplication. This transforms ClickHouse into a powerful query engine that works seamlessly with your existing data lake infrastructure.

Supported catalogs

The DataLakeCatalog engine supports the following data catalogs:

AWS Glue Catalog - For Iceberg tables in AWS environments
Databricks Unity Catalog - For Delta Lake and Iceberg tables
Hive Metastore - Traditional Hadoop ecosystem catalog
REST Catalogs - Any catalog supporting the Iceberg REST specification

Creating a database

You will need to enable the relevant settings below to use the DataLakeCatalog engine:

SET allow_database_iceberg = 1;
SET allow_database_unity_catalog = 1;
SET allow_database_glue_catalog = 1;
SET allow_experimental_database_hms_catalog = 1;
SET allow_experimental_database_paimon_rest_catalog = 1;

Databases with the DataLakeCatalog engine can be created using the following syntax:

CREATE DATABASE database_name
ENGINE = DataLakeCatalog(catalog_endpoint[, user, password])
SETTINGS
catalog_type,
[...]

The following settings are supported:

Setting	Description
`catalog_type`	Type of catalog: `glue`, `unity` (Delta, or Delta and Iceberg with `use_unity_catalog_v2`), `rest` (Iceberg), `hive`, `onelake` (Iceberg), `delta_sharing` (Iceberg, flat namespaces), `horizon` (Snowflake Horizon Iceberg REST)
`warehouse`	The warehouse/database name to use in the catalog.
`catalog_credential`	Authentication credential for the catalog (e.g., API key or token)
`use_unity_catalog_v2`	For `catalog_type = 'unity'`: use the new implementation, which serves both Delta Lake and Iceberg tables. Default: `false`. Set it on `CREATE`, or change it for an existing database with `ALTER DATABASE ... MODIFY SETTING`.
`auth_header`	Custom HTTP header for authentication with the catalog service
`auth_scope`	OAuth2 scope for authentication (if using OAuth)
`storage_endpoint`	Endpoint URL for the underlying storage
`oauth_server_uri`	URI of the OAuth2 authorization server for authentication
`vended_credentials`	Boolean indicating whether to use vended credentials from the catalog (supports AWS S3 and Azure ADLS Gen2)
`aws_access_key_id`	AWS access key ID for S3/Glue access (if not using vended credentials)
`aws_secret_access_key`	AWS secret access key for S3/Glue access (if not using vended credentials)
`aws_role_arn`	ARN of the IAM role to assume for AWS/Glue access. When set, ClickHouse uses AWS STS `AssumeRole` with base credentials from `aws_access_key_id` and `aws_secret_access_key` when both are provided, or from the default AWS credential chain otherwise (the role must trust the identity the server runs under).
`aws_role_session_name`	Session name used for the AWS STS `AssumeRole` call. Optional; defaults to `ClickHouseSession`.
`aws_external_id`	External ID passed to AWS STS `AssumeRole`, matching the `sts:ExternalId` condition on the role’s trust policy. Use this when the role is owned by a third party, such as ClickHouse Cloud.
`region`	AWS region for the service (e.g., `us-east-1`)
`dlf_access_key_id`	Access key ID for DLF access
`dlf_access_key_secret`	Access key Secret for DLF access
`force_add_bucket`	When constructing object-storage URLs from the catalog-provided table location and `storage_endpoint`, prepend the bucket/container name even if the endpoint already contains it. Default: `false`. Set to `true` for catalogs that hand back paths without the bucket and require it to be added at the URL-construction step (Polaris-style paths).

Examples

See below sections for examples of using the DataLakeCatalog engine:

Unity Catalog
Unity Catalog with Delta Lake and Iceberg tables With use_unity_catalog_v2 = 1, the unity catalog serves both Delta Lake and Iceberg tables, detecting the format of each table.

CREATE DATABASE database_name
ENGINE = DataLakeCatalog('https://<workspace>.cloud.databricks.com/api/2.1/unity-catalog')
SETTINGS
    catalog_type = 'unity',
    use_unity_catalog_v2 = 1,
    warehouse = 'my_catalog',
    catalog_credential = '<client_id>:<client_secret>';
SHOW TABLES FROM database_name;
SELECT count() FROM database_name.`schema_name.table_name`;

catalog_credential accepts an OAuth service principal as <client_id>:<client_secret>, a personal access token (dapi...), or a pre-obtained OAuth token.

Glue Catalog
OneLake Catalog Can be used by enabling allow_database_iceberg.

CREATE DATABASE database_name
ENGINE = DataLakeCatalog(catalog_endpoint)
SETTINGS
    catalog_type = 'onelake',
    warehouse = warehouse,
    onelake_tenant_id = tenant_id,
    oauth_server_uri = server_uri,
    auth_scope = auth_scope,
    onelake_client_id = client_id,
    onelake_client_secret = client_secret;
SHOW TABLES IN database_name;
SELECT count() from database_name.table_name;

To authenticate without sharing a client secret, set onelake_bearer_token to a pre-obtained bearer token (scoped to https://storage.azure.com) instead of onelake_client_id/onelake_client_secret. ClickHouse does not refresh the token, so the database must be recreated after it expires.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Supported catalogs

Creating a database

Examples

​Supported catalogs

​Creating a database

​Examples

Supported catalogs

Creating a database

Examples