Open Table Format Integration
ClickHouse clusters on the NetApp Instaclustr platform can be used with Open Table Formats, such as Apache Iceberg and Delta Lake, that provide a read-only, table-like interface to Apache Iceberg and Delta Lake. This enables you to execute ad-hoc queries directly on open table formats that are hosted in Object Storage solutions without the need for data ingestion.
This guide provides you with key preparation steps for querying from object store; and includes SELECT queries for supported table functions.
Key Preparation Before You Begin
1. Grant the ClickHouse Cluster Storage Access
AWS S3
Configure one of the following integrations before querying an Amazon S3 bucket:
- If the bucket is located in a region different from that of your cluster, configure an AWS S3 Region Integration for the bucket’s region.
- For a private bucket, you would also need to configure an AWS Private S3 Bucket Integration. Read-only access is sufficient for our use case. Your bucket policy must allow the ClickHouse cluster IAM role to perform s3:ListBucket and s3:GetObject.
- Do not include an access key ID or secret access key in your SQL query. When AWS Private S3 Bucket Integration is configured, ClickHouse automatically uses the AWS credentials associated with the cluster IAM role.
Azure Blob Storage
For Azure Blob Storage, configure a Domain Integration for the storage account domain. You may also need to update the storage account network and access policies to allow requests from the ClickHouse cluster.
2. Configure Credentials or a Named Collection
For AWS S3, a named collection is not required for authentication. The S3 URL can be passed directly to the table function, and ClickHouse uses the cluster IAM role automatically.
For Azure Blob Storage, named collections are optional but recommended because they eliminate the need to enter the same credentials or connection details repeatedly. If you are using named collections, however, they must exist on all nodes when running distributed (i.e. icebergS3Cluster, icebergAzureCluster, deltaLakeS3Cluster) queries.
The following example illustrates how to create a named collection for Azure Blob Storage.
Setting up a Named Collection for Azure Blob Storage
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE NAMED COLLECTION <NAME> ON CLUSTER '{cluster}' AS storage_account_url='https://<STORAGE_ACCOUNT_NAME>.blob.core.windows.net', account_name= '<STORAGE_ACCOUNT_NAME>', account_key= '<STORAGE_ACCOUNT_KEY>', container= '<CONTAINER_NAME>', blob_path= '<BLOB_PATH>', |
3. Potential Change to Egress Firewall Rules
Instaclustr-managed clusters restrict outbound traffic to reduce the risk of data exfiltration. AWS S3 Region, AWS Private S3 Bucket and Domain integrations add egress rules for the configured destinations. Review the destination and required permissions before applying an integration.
How to Perform Ad-Hoc Queries with Open Table Formats
This section explains how to query Open Table Formats using ClickHouse table functions.
The query structure is consistent across all scenarios. Only the table function name and parameters change based on:
- Storage Provider AWS S3 or Azure Blob Storage.
- Table Format Apache Iceberg or Delta Lake.
- Query Scope single node or cluster-wide.
- Supplying Credentials a named collection is used.
Use the 3 steps below to design your Open table query.
1
Choose Your Query Scope
Single Node
Use a single node table function when running the query on one ClickHouse node.
Cluster-wide
Use a cluster-wide table function to distribute the query across all nodes in the cluster.
2
Choose How Credentials Are Supplied
Using a Named Collection (Recommended)
Using a named collection for your storage will simplify queries by avoiding repeated credential configuration. See guidance above to Configure a Named Collection.
For single node queries:
|
1 |
SELECT * FROM <table_function>(<NAMED_COLLECTION>); |
For cluster-wide queries:
|
1 |
SELECT * FROM <table_function>('{cluster}', <NAMED_COLLECTION>); |
Without a Named Collection
Alternatively, the following example illustrates the query where credentials are input directly into the query.
For single node queries:
|
1 |
SELECT * FROM <table_function>(<PROVIDER_SPECIFIC_PARAMETERS>); |
For cluster-wide queries:
|
1 |
SELECT * FROM <table_function>('{cluster}', <PROVIDER_SPECIFIC_PARAMETERS>); |
Select the Appropriate Table Function
Use the tables below to determine the correct table function for your configuration.
Table Functions for AWS S3
| Table Format | Single Node Function | Cluster-Wide Function |
|---|---|---|
| Iceberg | icebergS3 | icebergS3Cluster |
| Delta Lake | deltaLakeS3 | deltaLakeS3Cluster |
Apache Iceberg
Single-node query:
|
1 2 3 4 |
SELECT * FROM icebergS3( '<BUCKET_URL>' ); |
Cluster-wide query:
|
1 2 3 4 5 |
SELECT * FROM icebergS3Cluster( '{cluster}', '<BUCKET_URL>' ); |
Delta Lake
Single-node query:
|
1 2 3 4 |
SELECT * FROM deltaLakeS3( '<BUCKET_URL>' ); |
Cluster-wide query:
|
1 2 3 4 5 |
SELECT * FROM deltaLakeS3Cluster( '{cluster}', '<BUCKET_URL>' ); |
Note that the URL must be the Iceberg or Delta Lake table location, not the bucket root. For private buckets, ClickHouse obtains temporary credentials from the cluster IAM role configured by the AWS Private S3 Bucket Integration. Hence, including access keys in the query is not required.
Table Functions for Azure Blob Storage
| Table Format | Single Node Function | Cluster-Wide Function |
|---|---|---|
| Iceberg | icebergAzure | icebergAzureCluster |
| Delta Lake | deltaLakeAzure | N/A |
Example Azure Iceberg query (Cluster-wide, No Named Collection):
|
1 2 3 4 5 6 7 8 |
SELECT * FROM icebergAzureCluster( `{cluster}`, <STORAGE_ACCOUNT_URL>, <CONTAINER>, <BLOB_PATH>, <STORAGE_ACCOUNT_NAME>, <STORAGE_ACCOUNT_KEY> ); |
Example Azure Delta Lake query (Single Node, No Named Collection):
|
1 2 3 4 5 6 7 |
SELECT * FROM deltaLakeAzure( <STORAGE_ACCOUNT_URL>, <CONTAINER>, <BLOB_PATH>, <STORAGE_ACCOUNT_NAME>, <STORAGE_ACCOUNT_KEY> ); |
Following these steps completes the setup. You can now query open table formats using ClickHouse table functions.
Limitations
- Open table format queries are read-only.
- The Iceberg or Delta Lake table and its metadata must already exist in object storage.
- This feature does not create or manage an Iceberg catalog.
For assistance with NetApp Instaclustr for ClickHouse support for Open Table Formats or any further inquiries, please contact Instaclustr Support.
