ClickHouse/clickhouse-docs-embeddings huggingface dataset:
AWS PrivateLink setup to expose MSK for ClickPipes

Setup steps to expose a private MSK via MSK multi-VPC connectivity to ClickPipes.


## Overview[​](#overview "Direct link to Overview")

This guide will get you started with setting up a **MSK multi-VPC** to be used with [ClickPipes reverse private endpoint](/docs/integrations/clickpipes/aws-privatelink#msk-multi-vpc).

## Requirements[​](#requirements "Direct link to Requirements")

Your MSK cluster VPC must be located in one of our ClickPipes regions. See [ClickPipes regions](/docs/integrations/clickpipes/aws-privatelink#aws-privatelink-regions) for the list of supported regions.

## Enabling multi-VPC connectivity[​](#enabling-multi-vpc-connectivity "Direct link to Enabling multi-VPC connectivity")

1. Navigate to the MSK cluster.
   - Choose "Clusters" from the left navigation pane in the Amazon MSK console.
   - Select the specific MSK cluster you want to configure for multi-VPC connectivity.
2. Enable MSK multi-VPC connectivity
   - In the **Connectivity** tab, find the **Multi-VPC connectivity** section.
   - Click on **Edit**.
   - Enable the **Turn-on MSK multi-VPC connectivity** option.
   - Follow the instructions
3. Add ClickPipes account principal into a cluster’s policy
   - Navigate to the **Configuration** tab.
   - Click on **Edit** in the **Cluster policy** section.
   - Include `arn:aws:iam::072088201116:root` in the **IAM policy**. Example:

     ```
     {
         "Version": "2012-10-17",
         "Statement": [
             {
                 "Effect": "Allow",
                 "Principal": {
                     "AWS": [
                         "arn:aws:iam::072088201116:root"
                     ]
                 },
                 "Action": [
                     "kafka:CreateVpcConnection",
                     "kafka:GetBootstrapBrokers",
                     "kafka:DescribeCluster",
                     "kafka:DescribeClusterV2"
                 ]
             }
         ]
     }
     ```

Tip

Cluster IAM policy enables ClickPipes to initiate the connection from ClickPipes to your MSK cluster.
If you want to configure IAM authentication for your MSK cluster, please refer to the [IAM authentication documentation](/docs/integrations/clickpipes/kafka/best-practices#iam).

## Creating reverse private endpoint[​](#creating-reverse-private-endpoint "Direct link to Creating reverse private endpoint")

Follow reverse private endpoint creation steps in the [ClickPipes documentation](/docs/integrations/clickpipes/aws-privatelink#creating-clickpipe).

Apr 15, 2025 · 2 min read
title_wip

It's Pi Day! Let's calculate pi using ClickHouse SQL


## It's Pi Day! Let's calculate pi using SQL[​](#its-pi-day-lets-calculate-pi-using-sql "Direct link to It's Pi Day! Let's calculate pi using SQL")

Happy Pi Day! We thought it would be fun to calculate pi using SQL queries in ClickHouse. Here is what we came up with so far...

1. This one uses the ClickHouse `numbers_mt` table function to return 1B rows and only takes 40ms to compute the calculation:

```
SELECT 4 * sum(if(number % 2, -1, 1) / ((number * 2) + 1)) AS pi
FROM numbers_mt(1000000000.)

┌────────────────pi─┐
│ 3.141592652589797 │
└───────────────────┘

1 row in set. Elapsed: 0.432 sec. Processed 1.00 billion rows, 8.00 GB (2.32 billion rows/s., 18.53 GB/s.)
```

2. The following example also processes 1B numbers, just not as quickly:

```
SELECT 3 + (4 * sum(if((number % 2) = 0, if((number % 4) = 0, -1 / ((number * (number + 1)) * (number + 2)), 1 / ((number * (number + 1)) * (number + 2))), 0))) AS pi
FROM numbers_mt(2, 10000000000)

┌─────────────────pi─┐
│ 3.1415926525808087 │
└────────────────────┘

1 row in set. Elapsed: 9.825 sec. Processed 10.00 billion rows, 80.00 GB (1.02 billion rows/s., 8.14 GB/s.)
```

3. This one is obviously our favorite in ClickHouse (and the most accurate!):

```
SELECT pi()

┌──────────────pi()─┐
│ 3.141592653589793 │
└───────────────────┘

1 row in set. Elapsed: 0.008 sec.
```

4. Someone knew their trigonometry with this one:

```
SELECT 2 * asin(1) AS pi

┌────────────────pi─┐
│ 3.141592653589793 │
└───────────────────┘

1 row in set. Elapsed: 0.005 sec.
```

5. Here is a handy API that lets you specify the number of digits you want:

```
SELECT *
FROM url('https://api.pi.delivery/v1/pi?start=0&numberOfDigits=100', 'JSONEachRow')

┌───────────────content─┐
│ 3.1415926535897933e99 │
└───────────────────────┘

1 row in set. Elapsed: 0.556 sec.
```

6. This one is clever - it uses ClickHouse distance functions:

```
WITH random_points AS
    (
        SELECT (rand64(1) / pow(2, 64), rand64(2) / pow(2, 64)) AS point
        FROM numbers(1000000000)
    )
SELECT (4 * countIf(L2Norm(point) < 1)) / count() AS pi
FROM random_points


┌──────────pi─┐
│ 3.141627208 │
└─────────────┘

1 row in set. Elapsed: 4.742 sec. Processed 1.00 billion rows, 8.00 GB (210.88 million rows/s., 1.69 GB/s.)
```

7. If you're a physicist, you will be content with this one:

```
SELECT 22 / 7

┌─────divide(22, 7)─┐
│ 3.142857142857143 │
└───────────────────┘
```

8. Another indirect method (this one came from Alexey Milovidov) that is accurate to 7 decimal places - and it's quick:

```
WITH
    10 AS length,
    (number / 1000000000.) * length AS x
SELECT pow((2 * length) * avg(exp(-(x * x))), 2) AS pi
FROM numbers_mt(1000000000.)


┌─────────────────pi─┐
│ 3.1415926890388595 │
└────────────────────┘

1 row in set. Elapsed: 1.245 sec. Processed 1.00 billion rows, 8.00 GB (803.25 million rows/s., 6.43 GB/s.)
```

Note

If you have any more, we'd love for you to contribute. Thanks!

Mar 14, 2023 · 3 min read
title_wip

Are you getting the error "Cannot append data in format Parquet to file" error in ClickHouse? Let's take a look at how to resolve it.


## Resolving "Cannot Append Data in Parquet Format" Error in ClickHouse[​](#resolving-cannot-append-data-in-parquet-format-error-in-clickhouse "Direct link to Resolving \"Cannot Append Data in Parquet Format\" Error in ClickHouse")

Are you getting the error "Cannot append data in format Parquet to file" error in ClickHouse?

Typically the error is reported as:

`DB::Exception: Cannot append data in format Parquet to file, because this format doesn't support appends. (CANNOT_APPEND_TO_FILE)`

Suppose you create a table that uses the `File` table engine with the Parquet format.

```
CREATE TABLE parquet_test
(
    `x` UInt32,
    `y` String
)
ENGINE = File(Parquet)
```

You can write to the table once:

```
INSERT INTO parquet_test VALUES
   (1, 'Hello'),
   (2, 'Hi')
```

This creates a file named `data.Parquet` in the `data/default/parquet_test` folder. If you try to insert another batch:

```
INSERT INTO parquet_test VALUES
   (3, 'World'),
   (4, 'Bye')
```

...you get the following error:

```
Code: 641. DB::Exception: Received from localhost:9000. DB::Exception: Cannot append data in format Parquet to file, because this format doesn't support appends. You can allow to create a new file on each insert by enabling setting engine_file_allow_create_multiple_files. (CANNOT_APPEND_TO_FILE)
```

You can not append to Parquet files in ClickHouse. But you can tell ClickHouse to create a new file for every `INSERT` by enabling the [`engine_file_allow_create_multiple_files` setting](https://clickhouse.com/docs/operations/settings/settings#engine_file_allow_create_multiple_files). If enabled, on each insert a new file will be created with a name following this pattern:

`data.Parquet` -> `data.1.Parquet` -> `data.2.Parquet`, etc.:

Let's give it a try. We will put our two commands into a single file named `parquet.sql`:

```
SET engine_file_allow_create_multiple_files = 1;

INSERT INTO default.parquet_test VALUES  (3, 'World'), (4, 'Bye');
```

Run it using `clickhouse-client`:

```
./clickhouse client --queries-file parquet.sql
```

And now you will see two files in `data/default/parquet_test` (and a new file for each subsequent insert).

Note

The `engine_file_allow_create_multiple_files` setting applies to other data formats that are not appendable, like JSON and ORC.

Mar 25, 2023 · 2 min read
title_wip

Learn how to resolve the SSL Exception CERTIFICATE\_VERIFY\_FAILED error.


## Resolving code 210 SSL certificate verify error in ClickHouse[​](#resolving-ssl-certificate-verify-error-in-clickhouse "Direct link to Resolving code 210 SSL certificate verify error in ClickHouse")

The error is typically reported as:

`Code: 210. DB::NetException: SSL Exception: error:1000007d:SSL routines:OPENSSL_internal:CERTIFICATE_VERIFY_FAILED`

## Cause of the Error[​](#cause-of-the-error "Direct link to Cause of the Error")

This error occurs while trying to connect to a ClickHouse server using `clickhouse-client`. The cause of the error is either:

- the client configuration file `config.xml` is missing the root certificate in the machine CA default store, or
- there is a self-signed or internal CA certificate that is not configured

## Solution[​](#solution "Direct link to Solution")

If using an internal or self-signed CA, configure the CA root certificate in `config.xml` in the client directory (e.g. `/etc/clickhouse-client`) and disable the loading of the default root CA certificates from the default location.

Here is an example configuration:

```
<openSSL>
    <client>
        <loadDefaultCAFile>false</loadDefaultCAFile>
        <caConfig>/etc/clickhouse-server/certs/marsnet_ca.crt</caConfig>
        <cacheSessions>true</cacheSessions>
        <disableProtocols>sslv2,sslv3</disableProtocols>
        <preferServerCiphers>true</preferServerCiphers>
        <invalidCertificateHandler>
            <name>RejectCertificateHandler</name>
        </invalidCertificateHandler>
    </client>
</openSSL>
```

## Additional resources[​](#additional-resources "Direct link to Additional resources")

View <https://clickhouse.com/docs/interfaces/cli/#configuration_files>

May 2, 2023 · One min read
title_wip

Let's learn how to change your billing address in ClickHouse Cloud.


## Question[​](#question "Direct link to Question")

How do I change my Billing Contact in ClickHouse Cloud?

## Answer[​](#answer "Direct link to Answer")

To change the Billing Contact as an Admin, follow the steps below:

1. Invite a new user as an Admin to the Cloud Organization.
2. Once they accept the invite, go into the ClickHouse Cloud Console billing page (Admin->Billing) and find the section on "Billing contacts".
3. Use the Edit button to select the new Admin user as a Billing Contact.

Sep 8, 2023 · One min read
title_wip

This article explains how to change the prompt in your Clickhouse client and clickhouse-local terminal window from :) to a prefix followed by :)


If you don't like how `clickhouse client` and clickhouse-local display the prompt in your terminal window, it is possible to change it to add a prefix.
This article explains how to change the prompt to whatever you want.

The default prompt is your local computer name followed by `:)` :

The following are variables that you can use in a prompt: `{user}`, `{host}`

There are several ways to update the prompt and we'll go through them each.

## --prompt flag[​](#--prompt-flag "Direct link to --prompt flag")

The first way to change the flag is using the `--prompt`:

```
clickhouse --prompt 👉
```

This will add the finger emoji before the smiley face:

## Config file - top level[​](#config-file---top-level "Direct link to Config file - top level")

Alternatively, you can provide a prompt prefix in a `config.xml` file:

```
<config>
    <prompt>👉 </prompt>
</config>
```

```
clickhouse
```

We can use a config file with any name we like and pass it in using the `-C` flag:

```
<config>
    <prompt>🎄 </prompt>
</config>
```

```
clickhouse -C christmas.xml
```

Prefer your config files to be in YAML?
That works too:

```
prompt: 🟡
```

```
clickhouse -C christmas.yaml
```

## Config file - connections\_credentials[​](#config-file---connections_credentials "Direct link to Config file - connections_credentials")

Alternatively, you can specify a prompt per connection credentials.
This only makes sense when using clickhouse-client.

```
<config>
    <connections_credentials>
        <connection>
            <n>prod<n>
            <hostname>127.0.0.1</hostname>
            <port>9000</port>
            <secure>0</secure>
            <user>default</user>
            <prompt>\e[31m[PRODUCTION]\e[0m {user}@prod</prompt>
        </connection>
        <connection>
            <n>dev<n>
            <hostname>127.0.0.1</hostname>
            <port>9000</port>
            <secure>0</secure>
            <user>default</user>
            <prompt>\e[32m[DEVELOPMENT]\e[0m {user}@dev</prompt>
        </connection>
    </connections_credentials>
</config>
```

We can then try to connect with the `dev` connection:

```
clickhouse client -C connections.xml --connection dev
```

Or the `prod` one:

```
clickhouse client -C connections.xml --connection prod
```

And here's a YAML version:

```
connections_credentials:
  connection:
    - name: prod
      hostname: 127.0.0.1
      port: 9000
      secure: 0
      user: default
      prompt: "\e[35m[PRODUCTION]\e[0m {user}@{host}"
    - name: dev
      hostname: 127.0.0.1
      port: 9000
      secure: 0
      user: default
      prompt: "\e[34m[DEVELOPMENT]\e[0m {user}@{host}"
```

With the `dev` connnection:

```
clickhouse client -C connections.yaml --connection dev
```

And now `prod`:

```
clickhouse client -C connections.yaml --connection prod
```

Nov 16, 2023 · 3 min read
title_wip

Learn how to use the `FORMAT Null` option in ClickHouse to measure query processing time without returning any rows to the client.


## Question[​](#question "Direct link to Question")

My query is returning many rows but I'm only interested in the query processing time. How do I omit the query output and check for query processing time?

## Answer[​](#answer "Direct link to Answer")

Append `FORMAT Null` to your query to configure the output format to `Null`. This prevents data from being transmitted to the client.

For example:

```
SELECT
    customer_id,
    count() AS total,
    any(review_headline)
FROM amazon_reviews
GROUP BY customer_id
ORDER BY total DESC
FORMAT Null
```

The response will return the number of rows processed and the elapsed time, but 0 rows will be returned:

```
0 rows in set. Elapsed: 25.288 sec. Processed 222.04 million rows, 13.50 GB (8.78 million rows/s., 533.77 MB/s.)
```

Jun 7, 2023 · One min read
title_wip

Learn how to start, stop, and resume a ClickHouse Cloud service using API endpoints and cURL commands.


## How to start, stop and resume a Cloud service using the ClickHouse API and cURL[​](#how-to-start-stop-and-resume-a-cloud-service-using-the-clickhouse-api-and-curl "Direct link to How to start, stop and resume a Cloud service using the ClickHouse API and cURL")

## Question[​](#question "Direct link to Question")

How can a ClickHouse Cloud service be started, stopped and resumed using API endpoints?

## Answer[​](#answer "Direct link to Answer")

1. To wake up/resume a Cloud service from an idle state, you can ping the instance:

```
curl -X GET https://abc123.us-west-2.aws.clickhouse.cloud:8443/ping
```

2. To stop a Cloud service, use the `/state` endpoint along with the `stop` command. The syntax looks like:

```
curl -X PATCH https://api.clickhouse.cloud/v1/organizations/<org_uuid>/services/<service_uuid>/state -u <key_id>:<key_secret> -H "Content-Type: application/json" -d ''{"command": "<stop|start>"}''
```

For example, the following command stops the `2e2124ca-c5ac-459d-a6f2-abc123549d2a` service:

```
curl -X PATCH https://api.clickhouse.cloud/v1/organizations/123abcd0-e9b5-4f55-9e42-0fb04392445c/services/2e2124ca-c5ac-459d-a6f2-abc123549d2a/state -u abc123:ABC123 -H "Content-Type: application/json" -d '{"command": "stop"}'
```

The output looks like:

```
{"result":{"id":"2e2124ca-c5ac-459d-a6f2-abc123549d2a","name":"mars-s3","provider":"aws","regionId":"us-west-2","state":"stopping","endpoints":[{"protocol":"nativesecure","host":"abc123.us-west-2.aws.clickhouse.cloud","port":9440},{"protocol":"https","host":"abc123ntrb.us-west-2.aws.clickhouse.cloud","port":8443}],"tier":"production","idleScaling":true,"idleTimeoutMinutes":5,"minTotalMemoryGb":24,"maxTotalMemoryGb":48,"ipAccessList":[{"source":"[0.0.0.0/0](http://0.0.0.0/0)","description":"Anywhere"}],"createdAt":"2022-10-21T18:46:31Z"},"status":200}%
```

3. To start the service again, use the `start` command:

```
curl -X PATCH https://api.clickhouse.cloud/v1/organizations/123abcd0-e9b5-4f55-9e42-0fb04392445c/services/2e2124ca-c5ac-459d-a6f2-abc123549d2a/state -u abc123:ABC123 -H "Content-Type: application/json" -d '{"command": "start"}'
```

Note

Here are the various states that a service can be in:

```
"state":"stopping"
"state":"stopped"
"state":"starting"
"state":"running"
"state":"idle"
```

Note

A Cloud service that is **"idle"** is considered started, so a `start` command will not resume/wake it up. Use the `ping` endpoint shown in Step 1 to wake up a service.

Jun 7, 2023 · 2 min read
title_wip

A columnar database stores the data of each column independently. This allows reading data from disk only for those columns that are used in any given query.


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

A columnar database stores the data of each column independently. This allows reading data from disk only for those columns used in any given query. The cost is that operations that affect whole rows become proportionally more expensive. A columnar database is a synonym for a column-oriented database management system. ClickHouse is a typical example of such a system.

## Advantages of a columnar database[​](#advantages-of-a-columnar-database "Direct link to Advantages of a columnar database")

Key columnar database advantages are:

- Queries that use only a few columns out of many.
- Aggregating queries against large volumes of data.
- Column-wise data compression.

## Row-oriented vs column-oriented databases[​](#row-oriented-vs-column-oriented-databases "Direct link to Row-oriented vs column-oriented databases")

Here is an illustration of the difference between traditional row-oriented systems and columnar databases when building reports:

**Traditional row-oriented**

![Illustration of traditional row-oriented database storage](/docs/assets/images/row-oriented-3e6fd5aa48e3075202d242b4799da8fa.gif)

**Columnar**

![Illustration of columnar database storage](/docs/assets/images/column-oriented-d082e49b7743d4ded32c7952bfdb028f.gif)

A columnar database is the preferred choice for analytical applications because it allows having many columns in a table just in case but does not pay the cost for unused columns on read query execution time (a traditional OLTP database reads all of the data during queries as the data is stored in rows and not columns). Column-oriented databases are designed for big data processing and data warehousing. They often natively scale using distributed clusters of low-cost hardware to increase throughput. ClickHouse does it with a combination of [distributed](https://clickhouse.com/docs/engines/table-engines/special/distributed) and [replicated](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication) tables.

Sep 1, 2021 · 2 min read
title_wip

Queries to help grant specific permissions to users.


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

The following are common queries to help grant specific permissions to users.

## How do I grant the same permissions as the current user to another user?[​](#how-do-i-grant-the-same-permissions-as-the-current-user-to-another-user "Direct link to How do I grant the same permissions as the current user to another user?")

```
GRANT CURRENT GRANTS ON *.* TO another_user;
```

## How do I grant a specific permission to a user based on the grants of the current user?[​](#how-do-i-grant-a-specific-permission-to-a-user-based-on-the-grants-of-the-current-user "Direct link to How do I grant a specific permission to a user based on the grants of the current user?")

In the below example, `another_user` will be able to perform `SELECT` commands on all of the databases and tables of the current user.

```
GRANT CURRENT GRANTS(SELECT ON *.*) TO another_user;
```

## How do I grant a specific permission to a user for a specific database based on the grants of the current user?[​](#how-do-i-grant-a-specific-permission-to-a-user-for-a-specific-database-based-on-the-grants-of-the-current-user "Direct link to How do I grant a specific permission to a user for a specific database based on the grants of the current user?")

In the below example, `another_user` will be able to perform `INSERT` commands to all tables in `my_database`.

```
GRANT INSERT ON my_database.* TO another_user;
```

## How do I give access to all grants for a specific user based on the default user?[​](#how-do-i-give-access-to-all-grants-for-a-specific-user-based-on-the-default-user "Direct link to How do I give access to all grants for a specific user based on the default user?")

```
GRANT default_role TO another_user;
```

Sep 28, 2023 · One min read
title_wip

Learn how to validate that two ClickHouse queries produce identical result-sets using hash functions and comparison techniques.


## Question[​](#question "Direct link to Question")

How can I validate that two queries return the same resultsets?

## Answer[​](#answer "Direct link to Answer")

You can use the below approach:

```
WITH
    (
        SELECT sum(cityHash64(*))
        FROM
        (
            -- your query 1 here
            -- SELECT ...
        )
    ) AS q1_resultset_hash,
    (
        SELECT sum(cityHash64(*))
        FROM
        (
            -- your query 2 here
            -- SELECT ...
        )
    ) AS q2_resultset_hash
SELECT equals(q1_resultset_hash,q2_resultset_hash) as Q1_equals_Q2
```

The example uses a [CTE](https://clickhouse.com/docs/sql-reference/statements/select/with) to calculate sums of the [cityHash](https://clickhouse.com/docs/sql-reference/functions/hash-functions#cityhash64) value of each row in these two queries and will return `1` if the two resultsets are identical.

Using some integers sequence data and some pretty formatting:

```
WITH
    (
        SELECT sum(cityHash64(*))
        FROM
        (
            SELECT *
            FROM numbers(10)
            ORDER BY number DESC
        )
    ) AS q1_resultset_hash,
    (
        SELECT sum(cityHash64(*))
        FROM
        (
            SELECT *
            FROM numbers(10)
            ORDER BY number ASC
        )
    ) AS q2_resultset_hash
SELECT q1_resultset_hash = q2_resultset_hash AS Q1_equals_Q2
FORMAT Pretty
```

will return:

```
┏━━━━━━━━━━━━━━┓
┃ Q1_equals_Q2 ┃
┡━━━━━━━━━━━━━━┩
│            1 │
└──────────────┘
```

While this can be handy in many scenarios, it can't be considered as a silver bullet to validate equality of resultsets for all types and there are caveats to using it, for example if any row contains `NULL` values the above approach will fail.

May 4, 2023 · 2 min read
title_wip

A query to compare metrics between two queries in ClickHouse.


## Compare Metrics Between Two Queries[​](#compare-metrics-between-two-queries "Direct link to Compare Metrics Between Two Queries")

To compare metrics between two queries, you must first obtain the `query_id` for both queries.

You can then run the following query:

```
WITH
    initial_query_id = '9bc1b438-542e-450a-8284-0e3e2b3739c8' AS second,
    initial_query_id = '4ce13192-2849-48f9-b426-e04790c4f4aa' AS first
SELECT
    PE.1 AS metric,
    sumIf(PE.2, first) AS v1,
    sumIf(PE.2, second) AS v2,
    10 * log10(v2 / v1) AS dB,
    round(((v2 - v1) / if(v2 > v1, v2, v1)) * 100, 2) AS perc,
    bar(abs(perc), 0, 100, 33) AS bar
FROM clusterAllReplicas(default, system.query_log)
ARRAY JOIN ProfileEvents AS PE
WHERE (first OR second) AND (event_date >= (today() - 3)) AND (type = 2)
GROUP BY metric
HAVING (v1 != v2) AND (abs(perc) >= 0)
ORDER BY
    dB DESC,
    v2 DESC,
    metric ASC
FORMAT PrettyCompactMonoBlock
```

You will receive a table with metrics comparing the two queries: