> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Nessie catalog

> In this guide, we will walk you through the steps to query your data using ClickHouse and the Nessie Catalog.

export const ExperimentalBadge = () => {
  return <div className="experimentalBadge">
            <div className="experimentalIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.25" d="M5.5 2H10.5" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M9.50015 2V6.19625L13.4283 12.7425C13.4738 12.8183 13.4985 12.9049 13.4996 12.9934C13.5008 13.0818 13.4785 13.169 13.435 13.246C13.3914 13.323 13.3283 13.3871 13.2519 13.4317C13.1755 13.4764 13.0886 13.4999 13.0002 13.5H3.00015C2.91164 13.5 2.8247 13.4766 2.74822 13.432C2.67174 13.3874 2.60847 13.3233 2.56487 13.2463C2.52126 13.1693 2.49889 13.082 2.50004 12.9935C2.50119 12.905 2.52582 12.8184 2.5714 12.7425L6.50015 6.19625V2" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M4.47656 9.56754C5.30344 9.41254 6.47656 9.47942 7.99969 10.25C10.0153 11.2707 11.4216 11.0569 12.2184 10.7282" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>
        </div>
            Experimental feature. <u><a href="/docs/beta-and-experimental-features#experimental-features">Learn more.</a></u>
        </div>;
};

<Note>
  Integration with the Nessie Catalog works with Iceberg tables only.
  This integration supports both AWS S3 and other cloud storage providers.
</Note>

ClickHouse supports integration with multiple catalogs (Unity, Glue, REST, Polaris, etc.). This guide will walk you through the steps to query your data using ClickHouse and the [Nessie](https://projectnessie.org/) catalog.

Nessie is an open-source transactional catalog for data lakes that provides:

* **Git-inspired** data version control with branches and commits
* **Cross-table transactions** and visibility guarantees
* **REST API** compliance with the Iceberg REST catalog specification
* **Open data lake** approach supporting Hive, Spark, Dremio, Trino, and more
* **Production-ready** deployment on Docker or Kubernetes

<Note>
  As this feature is experimental, you will need to enable it using:
  `SET allow_experimental_database_iceberg = 1;`
</Note>

<h2 id="local-development-setup">
  Local Development Setup
</h2>

For local development and testing, you can use a containerized Nessie setup. This approach is ideal for learning, prototyping, and development environments.

<h3 id="local-prerequisites">
  Prerequisites
</h3>

1. **Docker and Docker Compose**: Ensure Docker is installed and running
2. **Sample Setup**: You can use the official Nessie docker-compose setup

<h3 id="setting-up-local-nessie-catalog">
  Setting up Local Nessie Catalog
</h3>

You can use the official [Nessie docker-compose setup](https://projectnessie.org/guides/) which provides a complete environment with Nessie, in-memory version store, and MinIO for object storage.

**Step 1:** Create a new folder in which to run the example, then create a file `docker-compose.yml` with the following configuration:

```yaml theme={null}
version: '3.8'

services:
  nessie:
    image: ghcr.io/projectnessie/nessie:latest
    ports:
      - "19120:19120"
    environment:
      - nessie.version.store.type=IN_MEMORY
      - nessie.catalog.default-warehouse=warehouse
      - nessie.catalog.warehouses.warehouse.location=s3://my-bucket/
      - nessie.catalog.service.s3.default-options.endpoint=http://minio:9000/
      - nessie.catalog.service.s3.default-options.access-key=urn:nessie-secret:quarkus:nessie.catalog.secrets.access-key
      - nessie.catalog.service.s3.default-options.path-style-access=true
      - nessie.catalog.service.s3.default-options.auth-type=STATIC
      - nessie.catalog.secrets.access-key.name=admin
      - nessie.catalog.secrets.access-key.secret=password
      - nessie.catalog.service.s3.default-options.region=us-east-1
      - nessie.server.authentication.enabled=false
    depends_on:
      minio:
        condition: service_healthy
    networks:
      - iceberg_net

  minio:
    image: quay.io/minio/minio
    ports:
      - "9002:9000"
      - "9003:9001"
    environment:
      - MINIO_ROOT_USER=admin
      - MINIO_ROOT_PASSWORD=password
      - MINIO_REGION=us-east-1
    healthcheck:
      test: ["CMD", "mc", "ready", "local"]
      interval: 5s
      timeout: 10s
      retries: 5
      start_period: 30s
    entrypoint: >
      /bin/sh -c "
      minio server /data --console-address ':9001' &
      sleep 10;
      mc alias set myminio http://localhost:9000 admin password;
      mc mb myminio/my-bucket --ignore-existing;
      tail -f /dev/null"
    networks:
      - iceberg_net

  clickhouse:
    image: clickhouse/clickhouse-server:head
    container_name: nessie-clickhouse
    user: '0:0'  # Ensures root permissions
    ports:
      - "8123:8123"
      - "9000:9000"
    volumes:
      - clickhouse_data:/var/lib/clickhouse
      - ./clickhouse/data_import:/var/lib/clickhouse/data_import  # Mount dataset folder
    networks:
      - iceberg_net
    environment:
      - CLICKHOUSE_DB=default
      - CLICKHOUSE_USER=default
      - CLICKHOUSE_DO_NOT_CHOWN=1
      - CLICKHOUSE_PASSWORD=
    depends_on:
      nessie:
        condition: service_started
      minio:
        condition: service_healthy

volumes:
  clickhouse_data:

networks:
  iceberg_net:
    driver: bridge
```

**Step 2:** Run the following command to start the services:

```bash theme={null}
docker compose up -d
```

**Step 3:** Wait for all services to be ready. You can check the logs:

```bash theme={null}
docker-compose logs -f
```

<Note>
  The Nessie setup uses an in-memory version store and requires that sample data be loaded into the Iceberg tables first. Make sure the environment has created and populated the tables before attempting to query them through ClickHouse.
</Note>

<h3 id="connecting-to-local-nessie-catalog">
  Connecting to Local Nessie Catalog
</h3>

Connect to your ClickHouse container:

```bash theme={null}
docker exec -it nessie-clickhouse clickhouse-client
```

Then create the database connection to the Nessie catalog:

```sql theme={null}
SET allow_experimental_database_iceberg = 1;

CREATE DATABASE demo
ENGINE = DataLakeCatalog('http://nessie:19120/iceberg', 'admin', 'password')
SETTINGS catalog_type = 'rest', storage_endpoint = 'http://minio:9002/my-bucket', warehouse = 'warehouse'
```

<h2 id="querying-nessie-catalog-tables-using-clickhouse">
  Querying Nessie catalog tables using ClickHouse
</h2>

Now that the connection is in place, you can start querying via the Nessie catalog. For example:

```sql theme={null}
USE demo;

SHOW TABLES;
```

If your setup includes sample data (such as the taxi dataset), you should see tables like:

```response theme={null}
┌─name──────────┐
│ default.taxis │
└───────────────┘
```

<Note>
  If you don't see any tables, this usually means:

  1. The environment hasn't created the sample tables yet
  2. The Nessie catalog service isn't fully initialized
  3. The sample data loading process hasn't completed

  You can check the Nessie logs to see the catalog activity:

  ```bash theme={null}
  docker-compose logs nessie
  ```
</Note>

To query a table (if available):

```sql theme={null}
SELECT count(*) FROM `default.taxis`;
```

```response theme={null}
┌─count()─┐
│ 2171187 │
└─────────┘
```

<Info>
  **Backticks required**

  Backticks are required because ClickHouse doesn't support more than one namespace.
</Info>

To inspect the table DDL:

```sql theme={null}
SHOW CREATE TABLE `default.taxis`;
```

```response theme={null}
┌─statement─────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE demo.`default.taxis`                                                             │
│ (                                                                                             │
│     `VendorID` Nullable(Int64),                                                               │
│     `tpep_pickup_datetime` Nullable(DateTime64(6)),                                           │
│     `tpep_dropoff_datetime` Nullable(DateTime64(6)),                                          │
│     `passenger_count` Nullable(Float64),                                                      │
│     `trip_distance` Nullable(Float64),                                                        │
│     `RatecodeID` Nullable(Float64),                                                           │
│     `store_and_fwd_flag` Nullable(String),                                                    │
│     `PULocationID` Nullable(Int64),                                                           │
│     `DOLocationID` Nullable(Int64),                                                           │
│     `payment_type` Nullable(Int64),                                                           │
│     `fare_amount` Nullable(Float64),                                                          │
│     `extra` Nullable(Float64),                                                                │
│     `mta_tax` Nullable(Float64),                                                              │
│     `tip_amount` Nullable(Float64),                                                           │
│     `tolls_amount` Nullable(Float64),                                                         │
│     `improvement_surcharge` Nullable(Float64),                                                │
│     `total_amount` Nullable(Float64),                                                         │
│     `congestion_surcharge` Nullable(Float64),                                                 │
│     `airport_fee` Nullable(Float64)                                                           │
│ )                                                                                             │
│ ENGINE = Iceberg('http://localhost:9002/my-bucket/default/taxis/', 'admin', '[HIDDEN]')      │
└───────────────────────────────────────────────────────────────────────────────────────────────┘
```

<h2 id="loading-data-from-your-data-lake-into-clickhouse">
  Loading data from your Data Lake into ClickHouse
</h2>

If you need to load data from the Nessie catalog into ClickHouse, start by creating a local ClickHouse table:

```sql theme={null}
CREATE TABLE taxis
(
    `VendorID` Int64,
    `tpep_pickup_datetime` DateTime64(6),
    `tpep_dropoff_datetime` DateTime64(6),
    `passenger_count` Float64,
    `trip_distance` Float64,
    `RatecodeID` Float64,
    `store_and_fwd_flag` String,
    `PULocationID` Int64,
    `DOLocationID` Int64,
    `payment_type` Int64,
    `fare_amount` Float64,
    `extra` Float64,
    `mta_tax` Float64,
    `tip_amount` Float64,
    `tolls_amount` Float64,
    `improvement_surcharge` Float64,
    `total_amount` Float64,
    `congestion_surcharge` Float64,
    `airport_fee` Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(tpep_pickup_datetime)
ORDER BY (VendorID, tpep_pickup_datetime, PULocationID, DOLocationID);
```

Then load the data from your Nessie catalog table via an `INSERT INTO SELECT`:

```sql theme={null}
INSERT INTO taxis 
SELECT * FROM demo.`default.taxis`;
```