Fabric OneLake

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/data_lake/reference/onelake_catalog.md)

Beta feature. [Learn more.](/docs/beta-and-experimental-features#beta-features)

ClickHouse supports integration with multiple catalogs (OneLake, Unity, Glue, Polaris, etc.). This guide will walk you through the steps to query your data stored in Microsoft OneLake using ClickHouse and [OneLake](https://learn.microsoft.com/en-us/fabric/onelake/onelake-overview).

Microsoft OneLake supports multiple table formats for their lakehouse. With ClickHouse, you can query Iceberg tables.

Note

As this feature is beta, you will need to enable it using:
`SET allow_database_iceberg = 1;`

## Gathering Requirements OneLake[​](#gathering-requirements "Direct link to Gathering Requirements OneLake")

Before querying your table in Microsoft Fabric, you'll need to collect the following information:

- A OneLake tenant ID (Your Entra ID)
- An Application (client) ID
- A client secret
- A warehouse ID and a data item ID

Your warehouse ID is your Workspace ID.

For Data Item ID - we recommend using your Lakehouse ID. In our testing, it doesn't work with a Warehouse ID.

See [Microsoft OneLake's documentation](http://learn.microsoft.com/en-us/fabric/onelake/table-apis/table-apis-overview#prerequisites) for help finding these values.

## Creating a connection between OneLake and ClickHouse[​](#creating-a-connection-between-unity-catalog-and-clickhouse "Direct link to Creating a connection between OneLake and ClickHouse")

With the required info above you can now create a connection between Microsoft OneLake and ClickHouse, but before that you need to enable catalogs:

```
SET allow_database_iceberg=1
```

### Connect to OneLake[​](#connect-onelake "Direct link to Connect to OneLake")

```
CREATE DATABASE onelake_catalog
ENGINE = DataLakeCatalog('https://onelake.table.fabric.microsoft.com/iceberg')
SETTINGS
catalog_type = 'onelake',
warehouse = 'warehouse_id/data_item_id',
onelake_tenant_id = '<tenant_id>',
oauth_server_uri = 'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token',
auth_scope = 'https://storage.azure.com/.default',
onelake_client_id = '<client_id>',
onelake_client_secret = '<client_secret>'
```

## Querying OneLake using ClickHouse[​](#querying-onelake-using-clickhouse "Direct link to Querying OneLake using ClickHouse")

Now that the connection is in place, you can start querying OneLake:

```
SHOW TABLES FROM onelake_catalog

Query id: 8f6124c4-45c2-4351-b49a-89dc13e548a7

   ┌─name──────────────────────────┐
1. │ year_2017.green_tripdata_2017 │
2. │ year_2018.green_tripdata_2018 │
3. │ year_2019.green_tripdata_2019 │
4. │ year_2020.green_tripdata_2020 │
5. │ year_2022.green_tripdata_2022 │
   └───────────────────────────────┘
```

If you're using the Iceberg client, only the Delta tables with Uniform-enabled will be shown:

To query a table:

```
SELECT *
FROM onelake_catalog.`year_2017.green_tripdata_2017`
LIMIT 1

Query id: db6b4bda-cc58-4ca1-8891-e0d14f02c890

Row 1:
──────
VendorID:              2
lpep_pickup_datetime:  2017-05-18 16:55:43.000000
lpep_dropoff_datetime: 2017-05-18 18:04:11.000000
store_and_fwd_flag:    N
RatecodeID:            2
PULocationID:          130
DOLocationID:          48
passenger_count:       2
trip_distance:         12.43
fare_amount:           52
extra:                 4.5
mta_tax:               0.5
tip_amount:            0
tolls_amount:          33
ehail_fee:             ᴺᵁᴸᴸ
improvement_surcharge: 0.3
total_amount:          90.3
payment_type:          2
trip_type:             1
congestion_surcharge:  ᴺᵁᴸᴸ
source_file:           green_tripdata_2017-05.parquet
```

Backticks required

Backticks are required because ClickHouse doesn't support more than one namespace.

To inspect the table DDL:

```
SHOW CREATE TABLE onelake_catalog.`year_2017.green_tripdata_2017`

Query id: 8bd5bd8e-83be-453e-9a88-32de12ba7f24