Polaris catalog

In this guide, we will walk you through the steps to query your data using ClickHouse and the Snowflake Polaris catalog.

ClickHouse supports integration with multiple catalogs (Unity, Glue, Polaris, etc.). In this guide, we will walk you through the steps to query your data using ClickHouse and the Apache Polaris Catalog. Apache Polaris supports Iceberg tables and Delta Tables (via Generic Tables). This integration only supports Iceberg tables at this time.

As this feature is experimental, you will need to enable it using: SET allow_experimental_database_unity_catalog = 1;

Prerequisites

To connect to the Polaris catalog, you will need:

• Snowflake Open Catalog (hosted Polaris) or self-hosted Polaris Catalog
• Your Polaris catalog URI (for example, https://<account-id>.<region>.aws.snowflakecomputing.com/polaris/api/catalog/v1 or http://polaris:8181/api/catalog/v1/oauth/tokens)
• Catalog credentials (client ID and client secret)
• The OAuth tokens URI for your Polaris instance
• Storage endpoint for the object store where your Iceberg data lives (for example, S3)
• ClickHouse version 26.1+

For Open Catalog, Snowflake’s managed Polaris offering, your URI will include /polaris while for self-hosted, it may not.

1. Creating a connection between Polaris and ClickHouse

   Create a database that connects ClickHouse to your Polaris catalog:

   CREATE DATABASE polaris_catalog
   ENGINE = DataLakeCatalog('https://<catalog_uri>/api/catalog/v1')
   SETTINGS
       catalog_type = 'rest',
       catalog_credential = '<client-id>:<client-secret>',
       warehouse = 'snowflake',
       auth_scope = 'PRINCIPAL_ROLE:ALL',
       oauth_server_uri = 'https://<catalog_uri>/api/catalog/v1/oauth/tokens',
       storage_endpoint = '<storage_endpoint>'
   
2. Query the Polaris catalog using ClickHouse

   Once the connection is in place, you can query Polaris:

   Query

   USE polaris_catalog;
   SHOW TABLES;
   

   To query a table:

   Query

   SELECT count(*) FROM `polaris_db.my_iceberg_table`;
   

   Backticks are required, for example, schema.table.

   To inspect the table DDL:

   SHOW CREATE TABLE `polaris_db.my_iceberg_table`;
   
3. Loading data from Polaris into ClickHouse

   To load data from Polaris into a ClickHouse table, create the target table with your desired schema, then insert from the Polaris table:

   Query

   CREATE TABLE my_clickhouse_table
   (
       -- define columns to match your Iceberg table
       `id` Int64,
       `name` String,
       `event_time` DateTime64(3)
   )
   ENGINE = MergeTree
   ORDER BY id;
   
   INSERT INTO my_clickhouse_table
   SELECT * FROM polaris_catalog.`polaris_db.my_iceberg_table`;