ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Using MinIO

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/s3-minio.md)

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The feature documented here isn't available in ClickHouse Cloud services.
See the ClickHouse [Cloud Compatibility](/docs/whats-new/cloud-compatibility) guide for more information.

All S3 functions and tables and compatible with [MinIO](https://min.io/). You may experience superior throughput on self-hosted MinIO stores, especially in the event of optimal network locality.

Also backed merge tree configuration is compatible too, with some minor changes in configuration:

```
<clickhouse>
    <storage_configuration>
        ...
        <disks>
            <s3>
                <type>s3</type>
                <endpoint>https://min.io/tables//</endpoint>
                <access_key_id>your_access_key_id</access_key_id>
                <secret_access_key>your_secret_access_key</secret_access_key>
                <region></region>
                <metadata_path>/var/lib/clickhouse/disks/s3/</metadata_path>
            </s3>
            <s3_cache>
                <type>cache</type>
                <disk>s3</disk>
                <path>/var/lib/clickhouse/disks/s3_cache/</path>
                <max_size>10Gi</max_size>
            </s3_cache>
        </disks>
        ...
    </storage_configuration>
</clickhouse>
```

Tip

Note the double slash in the endpoint tag, this is needed to designate the bucket root.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/misc/index.md)

|  |  |  |  |
| --- | --- | --- | --- |
| Page|  |  |  | | --- | --- | --- | | [Visual Interfaces](/docs/interfaces/third-party/gui)| [Proxies](/docs/interfaces/third-party/proxy)| [Integrations](/docs/interfaces/third-party/integrations) | | | |

Was this page helpful?
Connect Apache NiFi to ClickHouse

Input

   Output
4. Under the "Properties" section of the `PutDatabaseRecord` processor, input the following values

   |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
   | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
   | Property Value Remark|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Record Reader `JSONTreeReader` Select the appropriate record reader|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Database Type Generic Leave as default|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Statement Type INSERT |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Database Connection Pooling Service ClickHouse JDBC Select the ClickHouse controller service|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Table Name tbl Input your table name here|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | Translate Field Names false Set to "false" so that field names inserted must match the column name|  |  |  | | --- | --- | --- | | Maximum Batch Size 1000 Maximum number of rows per insert. This value shouldn't be lower than the value of "Minimum Number of Records" in `MergeRecord` processor | | | | | | | | | | | | | | | | | | | | | | | |
5. To confirm that each insert contains multiple rows, check that the row count in the table is incrementing by at least the value of "Minimum Number of Records" defined in `MergeRecord`.
6. Congratulations - you have successfully loaded your data into ClickHouse using Apache NiFi !

- [Gather your connection details](#1-gather-your-connection-details)- [Download and run Apache NiFi](#2-download-and-run-apache-nifi)- [Download the ClickHouse JDBC driver](#3-download-the-clickhouse-jdbc-driver)- [Add `DBCPConnectionPool` Controller Service and configure its properties](#4-add-dbcpconnectionpool-controller-service-and-configure-its-properties)- [Read from a table using the `ExecuteSQL` processor](#5-read-from-a-table-using-the-executesql-processor)- [Write to a table using `MergeRecord` and `PutDatabaseRecord` processor](#6-write-to-a-table-using-mergerecord-and-putdatabaserecord-processor)

Was this page helpful?
Omni

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-visualization/omni-and-clickhouse.md)

Partner Integration

Omni can connect to ClickHouse Cloud or on-premise deployment via the official ClickHouse data source.

## 1. Gather your connection details[​](#1-gather-your-connection-details "Direct link to 1. Gather your connection details")

To connect to ClickHouse with HTTP(S) you need this information:

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter(s) Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `HOST` and `PORT` Typically, the port is 8443 when using TLS or 8123 when not using TLS.|  |  |  |  | | --- | --- | --- | --- | | `DATABASE NAME` Out of the box, there is a database named `default`, use the name of the database that you want to connect to.| `USERNAME` and `PASSWORD` Out of the box, the username is `default`. Use the username appropriate for your use case. | | | | | | | |

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console.
Select a service and click **Connect**:

Choose **HTTPS**. Connection details are displayed in an example `curl` command.

If you're using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

## 2. Create a ClickHouse data source[​](#2-create-a-clickhouse-data-source "Direct link to 2. Create a ClickHouse data source")

Navigate to Admin -> Connections and click the "Add Connection" button in the top right corner.

  

Select `ClickHouse`. Enter your credentials in the form.

  

Now you should can query and visualize data from ClickHouse in Omni.

- [1. Gather your connection details](#1-gather-your-connection-details)- [2. Create a ClickHouse data source](#2-create-a-clickhouse-data-source)

Was this page helpful?
pg_clickhouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/tools/data-integration/pg_clickhouse/introduction.md)

## Introduction[​](#introduction "Direct link to Introduction")

[pg\_clickhouse](https://github.com/clickHouse/pg_clickhouse "pg_clickhouse on GitHub"), an open-source PostgreSQL extension, runs analytics queries
on ClickHouse right from PostgreSQL without rewriting any SQL. It supports
PostgreSQL 13 and later and ClickHouse v23 and later.

Once [ClickPipes](/docs/integrations/clickpipes) starts syncing data ClickHouse,
use pg\_clickhouse to quickly and easily [import foreign tables](/docs/integrations/pg_clickhouse/reference#import-foreign-schema) into a
PostgreSQL schema. Then run your existing PostgreSQL queries to against those
tables, preserving your existing code base while pushing execution down to
ClickHouse.

## Getting Started[​](#getting-started "Direct link to Getting Started")

The simplest way to try pg\_clickhouse is the [Docker image](https://github.com/ClickHouse/pg_clickhouse/pkgs/container/pg_clickhouse "Latest Docker release"), which
contains the standard PostgreSQL Docker image with the pg\_clickhouse
extension:

```
docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
       -d ghcr.io/clickhouse/pg_clickhouse:18
docker exec -it pg_clickhouse psql -U postgres -c 'CREATE EXTENSION pg_clickhouse'
```

See the [tutorial](/docs/integrations/pg_clickhouse/tutorial "pg_clickhouse Tutorial") to get started importing ClickHouse tables and pushing down
queries.

## Test Case: TPC-H[​](#test-case-tpc-h "Direct link to Test Case: TPC-H")

This table compares [TPC-H](https://www.tpc.org/tpch/) query performance between regular PostgreSQL
tables and pg\_clickhouse connected to ClickHouse, both loaded at scaling
factor 1; ✔︎ indicates full pushdown, while a dash indicates a query
cancellation after 1m. All tests run on a MacBook Pro M4 Max with 36 GB of
memory.
pg_clickhouse Reference Documentation

### Pushdown Ordered Set Aggregates[​](#pushdown-ordered-set-aggregates "Direct link to Pushdown Ordered Set Aggregates")

These [ordered-set aggregate functions](https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE) map to ClickHouse [Parametric
aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-functions) by passing their *direct argument* as a parameter and
their `ORDER BY` expressions as arguments. For example, this PostgreSQL query:

```
SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1;
```

Maps to this ClickHouse query:

```
SELECT quantile(0.25)(a) FROM t1;
```

Note that the non-default `ORDER BY` suffixes `DESC` and `NULLS FIRST`
aren't supported and will raise an error.

- `percentile_cont(double)`: [quantile](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantile)
- `quantile(double)`: [quantile](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantile)
- `quantileExact(double)`: [quantileExact](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantileexact)

## Authors[​](#authors "Direct link to Authors")

[David E. Wheeler](https://justatheory.com/)

## Copyright[​](#copyright "Direct link to Copyright")

Copyright (c) 2025-2026, ClickHouse

- [Description](#description)- [Getting Started](#getting-started)- [Usage](#usage)- [Versioning Policy](#versioning-policy)- [DDL SQL Reference](#ddl-sql-reference)
          - [CREATE EXTENSION](#create-extension)- [ALTER EXTENSION](#alter-extension)- [DROP EXTENSION](#drop-extension)- [CREATE SERVER](#create-server)- [ALTER SERVER](#alter-server)- [DROP SERVER](#drop-server)- [CREATE USER MAPPING](#create-user-mapping)- [ALTER USER MAPPING](#alter-user-mapping)- [DROP USER MAPPING](#drop-user-mapping)- [IMPORT FOREIGN SCHEMA](#import-foreign-schema)- [CREATE FOREIGN TABLE](#create-foreign-table)- [ALTER FOREIGN TABLE](#alter-foreign-table)- [DROP FOREIGN TABLE](#drop-foreign-table)- [DML SQL Reference](#dml-sql-reference)
            - [EXPLAIN](#explain)- [SELECT](#select)- [PREPARE, EXECUTE, DEALLOCATE](#prepare-execute-deallocate)- [INSERT](#insert)- [COPY](#copy)- [LOAD](#load)- [SET](#set)- [ALTER ROLE](#alter-role)- [Preloading](#preloading)
              - [`session_preload_libraries`](#session_preload_libraries)- [`shared_preload_libraries`](#shared_preload_libraries)- [Data Types](#data-types)
                - [BYTEA](#bytea)- [Function and Operator Reference](#function-and-operator-reference)
                  - [Functions](#functions)- [Pushdown Functions](#pushdown-functions)- [Custom Functions](#custom-functions)- [Pushdown Casts](#pushdown-casts)- [Pushdown Aggregates](#pushdown-aggregates)- [Custom Aggregates](#custom-aggregates)- [Pushdown Ordered Set Aggregates](#pushdown-ordered-set-aggregates)- [Authors](#authors)- [Copyright](#copyright)

Was this page helpful?
pg_clickhouse Tutorial

Time: 66.245 ms
   ```

   This query sums up the number of taxi rides per borough that end at either
   the LaGuardia or JFK airport. Notice there are quite a few trips where the
   pickup neighborhood is unknown.

## Perform a join[​](#perform-a-join "Direct link to Perform a join")

Write some queries that join the `taxi_zone_dictionary` with your `trips`
table.

1. Start with a simple `JOIN` that acts similarly to the previous airport
   query above:

   ```
   taxi=# SELECT
       count(1) AS total,
       "Borough"
   FROM taxi.trips
   JOIN taxi.taxi_zone_dictionary
     ON trips.pickup_nyct2010_gid = toUInt64(taxi.taxi_zone_dictionary."LocationID")
   WHERE pickup_nyct2010_gid > 0
     AND dropoff_nyct2010_gid IN (132, 138)
   GROUP BY "Borough"
   ORDER BY total DESC;
    total | borough_name
   -------+---------------
     7053 | Manhattan
     6828 | Brooklyn
     4458 | Queens
     2670 | Bronx
      554 | Staten Island
       53 | EWR
   (6 rows)

   Time: 48.449 ms
   ```

   Note

   Notice the output of the above `JOIN` query is the same as the `dictGet`
   query above, (except that the `Unknown` values aren't included). Behind
   the scenes, ClickHouse is actually calling the `dictGet` function for
   the `taxi_zone_dictionary` dictionary, but the `JOIN` syntax is more
   familiar for SQL developers.

   ```
   taxi=# explain SELECT
           count(1) AS total,
           "Borough"
       FROM taxi.trips
       JOIN taxi.taxi_zone_dictionary
         ON trips.pickup_nyct2010_gid = toUInt64(taxi.taxi_zone_dictionary."LocationID")
       WHERE pickup_nyct2010_gid > 0
         AND dropoff_nyct2010_gid IN (132, 138)
       GROUP BY "Borough"
       ORDER BY total DESC;
                                 QUERY PLAN
   -----------------------------------------------------------------------
    Foreign Scan  (cost=1.00..5.10 rows=1000 width=40)
      Relations: Aggregate on ((trips) INNER JOIN (taxi_zone_dictionary))
   (2 rows)
   Time: 2.012 ms
   ```
2. This query returns rows for the the 1000 trips with the highest tip
   amount, then performs an inner join of each row with the dictionary:

   ```
   taxi=# SELECT *
   FROM taxi.trips
   JOIN taxi.taxi_zone_dictionary
       ON trips.dropoff_nyct2010_gid = taxi.taxi_zone_dictionary."LocationID"
   WHERE tip_amount > 0
   ORDER BY tip_amount DESC
   LIMIT 1000;
   ```

Note

Generally, we avoid using `SELECT *` in PostgreSQL and ClickHouse. You
should only retrieve the columns you actually need.

- [Overview](#overview)- [Start ClickHouse](#start-clickhouse)- [Create a Table](#create-a-table)- [Add the Data Set](#add-the-data-set)
        - [Install pg\_clickhouse](#install-pg_clickhouse)- [Connect pg\_clickhouse](#connect-pg_clickhouse)- [Analyze the data](#analyze-the-data)- [Create a Dictionary](#create-a-dictionary)- [Perform a join](#perform-a-join)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/dbms/postgresql/inserting-data.md)

We recommend reading [this guide](/docs/guides/inserting-data) to learn best practices on inserting data to ClickHouse to optimize for insert performance.

For bulk loading data from PostgreSQL, you can use:

- using [ClickPipes](/docs/integrations/clickpipes/postgres), the managed integration service for ClickHouse Cloud.
- `PeerDB by ClickHouse`, an ETL tool specifically designed for PostgreSQL database replication to both self-hosted ClickHouse and ClickHouse Cloud.
- The [Postgres Table Function](/docs/sql-reference/table-functions/postgresql) to read data directly. This is typically appropriate for if batch replication based on a known watermark, e.g. a timestamp. is sufficient or if it's a once-off migration. This approach can scale to 10's of millions of rows. Users looking to migrate larger datasets should consider multiple requests, each dealing with a chunk of the data. Staging tables can be used for each chunk prior to its partitions being moved to a final table. This allows failed requests to be retried. For further details on this bulk-loading strategy, see here.
- Data can be exported from Postgres in CSV format. This can then be inserted into ClickHouse from either local files or via object storage using table functions.

Was this page helpful?
Power BI

- Consistent, fast performance regardless of data volume
- Lower load on your ClickHouse cluster
- More predictable costs

Note

If your dashboards are slow, check ClickHouse's [`query_log`](/docs/operations/system-tables/query_log) to see what SQL queries Power BI is actually running. Common problems include nested subqueries, scanning entire tables, or inefficient joins. Once you identify the issue, create [materialized views](/docs/materialized-views) that solve those specific problems.

### Implementation best practices[​](#implementation-best-practices "Direct link to Implementation best practices")

#### Pre-aggregation strategy[​](#pre-aggregation-strategy "Direct link to Pre-aggregation strategy")

Create materialized views at multiple aggregation levels:

- Hourly aggregations for recent, detailed dashboards
- Daily aggregations for historical trends
- Monthly rollups for long-term reporting
- Keep raw data with appropriate TTL for ad-hoc analysis

#### Data modelling optimization[​](#data-modelling-optimization "Direct link to Data modelling optimization")

- Define `ORDER BY` keys that match your query patterns
- Use partitioning for time-series data
- Convert small dimension tables to dictionaries for efficient lookups
- Leverage projections for additional query optimization

## Known limitations[​](#known-limitations "Direct link to Known limitations")

### UInt64[​](#uint64 "Direct link to UInt64")

Unsigned integer types such as UInt64 or bigger won't be loaded into the dataset automatically, as Int64 is the maximum whole number type support by Power BI.

Note

To import the data properly, before hitting the "Load" button in the Navigator, click "Transform Data" first.

In this example, `pageviews` table has a UInt64 column, which is recognized as "Binary" by default.
"Transform Data" opens Power Query Editor, where we can reassign the type of the column, setting it as, for example,
Text.

  

Once finished, click "Close & Apply" in the top left corner, and proceed with loading the data.

- [Prerequisites](#prerequisites)
  - [Power BI Installation](#power-bi-installation)- [Gather your ClickHouse connection details](#gather-your-clickhouse-connection-details)- [Power BI desktop](#power-bi-desktop)
    - [Install the ODBC Driver](#install-the-odbc-driver)- [Find the ClickHouse Connector](#find-the-clickhouse-connector)- [Connect to ClickHouse](#connect-to-clickhouse)- [Query and Visualise Data](#query-and-visualise-data)- [Power BI service](#power-bi-service)- [ODBC driver (import only)](#odbc-driver-import-only)
        - [Create a new User DSN](#create-a-new-user-dsn)- [Get data into Power BI](#get-data-into-power-bi)- [Optimizing work with large datasets](#optimizing-work-with-large-datasets)
          - [Implementation best practices](#implementation-best-practices)- [Known limitations](#known-limitations)
            - [UInt64](#uint64)

Was this page helpful?
Connect QStudio to ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/sql-clients/qstudio.md)

Community Maintained

QStudio is a free SQL GUI, it allows running SQL scripts, easy browsing of tables, charting and exporting of results. It works on every operating system, with every database.

QStudio connects to ClickHouse using JDBC.

## 1. Gather your ClickHouse details[​](#1-gather-your-clickhouse-details "Direct link to 1. Gather your ClickHouse details")

QStudio uses JDBC over HTTP(S) to connect to ClickHouse; you need:

- endpoint
- port number
- username
- password

To connect to ClickHouse with HTTP(S) you need this information:

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter(s) Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `HOST` and `PORT` Typically, the port is 8443 when using TLS or 8123 when not using TLS.|  |  |  |  | | --- | --- | --- | --- | | `DATABASE NAME` Out of the box, there is a database named `default`, use the name of the database that you want to connect to.| `USERNAME` and `PASSWORD` Out of the box, the username is `default`. Use the username appropriate for your use case. | | | | | | | |

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console.
Select a service and click **Connect**:

Choose **HTTPS**. Connection details are displayed in an example `curl` command.

If you're using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

## 2. Download QStudio[​](#2-download-qstudio "Direct link to 2. Download QStudio")

QStudio is available at <https://www.timestored.com/qstudio/download/>

## 3. Add a database[​](#3-add-a-database "Direct link to 3. Add a database")

- When you first open QStudio click on the menu options **Server->Add Server** or on the add server button on the toolbar.
- Then set the details:

1. Server Type: Clickhouse.com
2. Note for Host you MUST include https://
   Host: <https://abc.def.clickhouse.cloud>
   Port: 8443
3. Username: default
   Password: `XXXXXXXXXXX`
4. Click Add

If QStudio detects that you don't have the ClickHouse JDBC driver installed, it will offer to download them for you:

## 4. Query ClickHouse[​](#4-query-clickhouse "Direct link to 4. Query ClickHouse")

- Open a query editor and run a query. You can run queries by
- Ctrl + e - Runs highlighted text
- Ctrl + Enter - Runs the current line
- An example query:

## Next steps[​](#next-steps "Direct link to Next steps")

See [QStudio](https://www.timestored.com/qstudio) to learn about the capabilities of QStudio, and the [ClickHouse documentation](https://clickhouse.com/docs) to learn about the capabilities of ClickHouse.

- [1. Gather your ClickHouse details](#1-gather-your-clickhouse-details)- [2. Download QStudio](#2-download-qstudio)- [3. Add a database](#3-add-a-database)- [4. Query ClickHouse](#4-query-clickhouse)- [Next steps](#next-steps)

Was this page helpful?
Connecting Retool to ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/tools/data-integration/retool/index.md)

Partner Integration

## 1. Gather your connection details[​](#1-gather-your-connection-details "Direct link to 1. Gather your connection details")

To connect to ClickHouse with HTTP(S) you need this information:

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter(s) Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `HOST` and `PORT` Typically, the port is 8443 when using TLS or 8123 when not using TLS.|  |  |  |  | | --- | --- | --- | --- | | `DATABASE NAME` Out of the box, there is a database named `default`, use the name of the database that you want to connect to.| `USERNAME` and `PASSWORD` Out of the box, the username is `default`. Use the username appropriate for your use case. | | | | | | | |

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console.
Select a service and click **Connect**:

Choose **HTTPS**. Connection details are displayed in an example `curl` command.

If you're using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

## 2. Create a ClickHouse resource[​](#2-create-a-clickhouse-resource "Direct link to 2. Create a ClickHouse resource")

Login to your Retool account and navigate to the *Resources* tab. Choose "Create New" -> "Resource":

  

Select "JDBC" from the list of available connectors:

  

In the setup wizard, make sure you select `com.clickhouse.jdbc.ClickHouseDriver` as the "Driver name":

  

Fill in your ClickHouse credentials in the following format: `jdbc:clickhouse://HOST:PORT/DATABASE?user=USERNAME&password=PASSWORD`.
If your instance requires SSL or you're using ClickHouse Cloud, add `&ssl=true` to the connection string, so it looks like `jdbc:clickhouse://HOST:PORT/DATABASE?user=USERNAME&password=PASSWORD&ssl=true`

  

After that, test your connection:

  

Now, you should be able to proceed to your app using your ClickHouse resource.

- [1. Gather your connection details](#1-gather-your-connection-details)- [2. Create a ClickHouse resource](#2-create-a-clickhouse-resource)

Was this page helpful?