ClickHouse/clickhouse-docs-embeddings huggingface dataset:
A practical introduction to primary indexes in ClickHouse

Now the rows on disk are first ordered by `fingerprint`, and for rows with the same fingerprint value, their `hash` value determines the final order.

Because data that differs only in small changes is getting the same fingerprint value, similar data is now stored on disk close to each other in the content column. And that is very good for the compression ratio of the content column, as a compression algorithm in general benefits from data locality (the more similar the data is the better the compression ratio is).

The compromise is that two fields (`fingerprint` and `hash`) are required for the retrieval of a specific row in order to optimally utilise the primary index that results from the compound `PRIMARY KEY (fingerprint, hash)`.

- [Introduction](#introduction)
  - [Data set](#data-set)- [Test machine](#test-machine)- [A full table scan](#a-full-table-scan)- [ClickHouse index design](#clickhouse-index-design)
    - [An index design for massive data scales](#an-index-design-for-massive-data-scales)- [A table with a primary key](#a-table-with-a-primary-key)- [Data is stored on disk ordered by primary key columns](#data-is-stored-on-disk-ordered-by-primary-key-columns)- [Data is organized into granules for parallel data processing](#data-is-organized-into-granules-for-parallel-data-processing)- [The primary index has one entry per granule](#the-primary-index-has-one-entry-per-granule)- [The primary index is used for selecting granules](#the-primary-index-is-used-for-selecting-granules)- [Mark files are used for locating granules](#mark-files-are-used-for-locating-granules)- [Using multiple primary indexes](#using-multiple-primary-indexes)
      - [Secondary key columns can (not) be inefficient](#secondary-key-columns-can-not-be-inefficient)- [Generic exclusion search algorithm](#generic-exclusion-search-algorithm)- [Note about data skipping index](#note-about-data-skipping-index)- [A need to use multiple primary indexes](#a-need-to-use-multiple-primary-indexes)- [Options for creating additional primary indexes](#options-for-creating-additional-primary-indexes)- [Option 1: Secondary Tables](#option-1-secondary-tables)- [Option 2: Materialized Views](#option-2-materialized-views)- [Option 3: Projections](#option-3-projections)- [Summary](#summary)- [Ordering key columns efficiently](#ordering-key-columns-efficiently)
        - [Efficient filtering on secondary key columns](#efficient-filtering-on-secondary-key-columns)- [Optimal compression ratio of data files](#optimal-compression-ratio-of-data-files)- [Summary](#summary-1)- [Identifying single rows efficiently](#identifying-single-rows-efficiently)
          - [A concrete example](#a-concrete-example)

Was this page helpful?
Creating tables in ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/starter_guides/creating-tables.md)

Like most databases, ClickHouse logically groups tables into **databases**. Use the `CREATE DATABASE` command to create a new database in ClickHouse:

```
CREATE DATABASE IF NOT EXISTS helloworld
```

Similarly, use `CREATE TABLE` to define a new table. If you don't specify the database name, the table will be in the
`default` database.

The following table named `my_first_table` is created in the `helloworld` database:

```
CREATE TABLE helloworld.my_first_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)
```

In the example above, `my_first_table` is a `MergeTree` table with four columns:

- `user_id`: a 32-bit unsigned integer
- `message`: a `String` data type, which replaces types like `VARCHAR`, `BLOB`, `CLOB` and others from other database systems
- `timestamp`: a `DateTime` value, which represents an instant in time
- `metric`: a 32-bit floating point number

Note

The table engine determines:

- How and where the data is stored
- Which queries are supported
- Whether or not the data is replicated

There are many engines to choose from, but for a simple table on a single-node ClickHouse server, [MergeTree](/docs/engines/table-engines/mergetree-family/mergetree) is your likely choice.

## A brief intro to primary keys[​](#a-brief-intro-to-primary-keys "Direct link to A brief intro to primary keys")

Before you go any further, it is important to understand how primary keys work in ClickHouse (the implementation
of primary keys might seem unexpected!):

- primary keys in ClickHouse are ***not unique*** for each row in a table

The primary key of a ClickHouse table determines how the data is sorted when written to disk. Every 8,192 rows or 10MB of
data (referred to as the **index granularity**) creates an entry in the primary key index file. This granularity concept
creates a **sparse index** that can easily fit in memory, and the granules represent a stripe of the smallest amount of
column data that gets processed during `SELECT` queries.

The primary key can be defined using the `PRIMARY KEY` parameter. If you define a table without a `PRIMARY KEY` specified,
then the key becomes the tuple specified in the `ORDER BY` clause. If you specify both a `PRIMARY KEY` and an `ORDER BY`, the primary key must be a prefix of the sort order.

The primary key is also the sorting key, which is a tuple of `(user_id, timestamp)`. Therefore, the data stored in each
column file will be sorted by `user_id`, then `timestamp`.

Tip

For more details, check out the [Modeling Data training module](https://learn.clickhouse.com/visitor_catalog_class/show/1328860/?utm_source=clickhouse&utm_medium=docs) in ClickHouse Academy.

- [A brief intro to primary keys](#a-brief-intro-to-primary-keys)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/developer/alternative-query-languages.md)

Besides standard SQL, ClickHouse supports various alternative query languages for querying data.

The currently supported dialects are:

- `clickhouse`: The default [SQL dialect](/docs/chdb/reference/sql-reference) of ClickHouse
- `prql`: [Pipelined Relational Query Language (PRQL)](https://prql-lang.org/)
- `kusto`: [Kusto Query Language (KQL)](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query)

Which query language is used is controlled by setting `dialect`.

## Standard SQL[​](#standard-sql "Direct link to Standard SQL")

Standard SQL is the default query language of ClickHouse.

```
SET dialect = 'clickhouse'
```

## Pipelined relational query language (PRQL)[​](#pipelined-relational-query-language-prql "Direct link to Pipelined relational query language (PRQL)")

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

To enable PRQL:

```
SET allow_experimental_prql_dialect = 1; -- this SET statement is required only for ClickHouse versions >= v25.1
SET dialect = 'prql'
```

Example PRQL query:

```
from trips
aggregate {
    ct = count this
    total_days = sum days
}
```

Under the hood, ClickHouse uses transpilation from PRQL to SQL to run PRQL queries.

## Kusto query language (KQL)[​](#kusto-query-language-kql "Direct link to Kusto query language (KQL)")

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

To enable KQL:

```
SET allow_experimental_kusto_dialect = 1; -- this SET statement is required only for ClickHouse versions >= 25.1
SET dialect = 'kusto'
```

```
numbers(10) | project number
```

```
┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘
```

Note that KQL queries may not be able to access all functions defined in ClickHouse.

- [Standard SQL](#standard-sql)- [Pipelined relational query language (PRQL)](#pipelined-relational-query-language-prql)- [Kusto query language (KQL)](#kusto-query-language-kql)

Was this page helpful?
Stored procedures and query parameters in ClickHouse

### Use of query parameters[​](#summary-query-parameters "Direct link to Use of query parameters")

Query parameters can be used for:

- Preventing SQL injection
- Parameterized queries with type safety
- Dynamic filtering in applications
- Reusable query templates

## Related documentation[​](#related-documentation "Direct link to Related documentation")

- [`CREATE FUNCTION`](/docs/sql-reference/statements/create/function) - User-Defined Functions
- [`CREATE VIEW`](/docs/sql-reference/statements/create/view) - Views including parameterized and materialized
- [SQL Syntax - Query Parameters](/docs/sql-reference/syntax#defining-and-using-query-parameters) - Complete parameter syntax
- [Cascading Materialized Views](/docs/guides/developer/cascading-materialized-views) - Advanced materialized view patterns
- [Executable UDFs](/docs/sql-reference/functions/udf) - External function execution

- [Alternatives to stored procedures in ClickHouse](#alternatives-to-stored-procedures)
  - [User-Defined Functions (UDFs)](#user-defined-functions)- [Parameterized views](#parameterized-views)- [Materialized views](#materialized-views)- [External orchestration](#external-orchestration)- [Alternatives to prepared statements in ClickHouse](#alternatives-to-prepared-statements-in-clickhouse)
    - [Syntax](#query-parameters-syntax)- [Parameter syntax](#parameter-syntax)- [Data type examples](#data-type-examples)- [Limitations of query parameters](#limitations-of-query-parameters)- [Security best practices](#security-best-practices)- [MySQL protocol prepared statements](#mysql-protocol-prepared-statements)- [Summary](#summary)
      - [ClickHouse alternatives to stored procedures](#summary-stored-procedures)- [Use of query parameters](#summary-query-parameters)- [Related documentation](#related-documentation)

Was this page helpful?
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
Configuring automatic TLS provisioning via ACME

clickhouse keeper-client -q "create '/clickhouse' ''"
clickhouse keeper-client -q "create '/clickhouse/acme' ''"
clickhouse keeper-client -q "create '/clickhouse/acme/acme-v02.api.letsencrypt.org' ''"
clickhouse keeper-client -q "create '/clickhouse/acme/acme-v02.api.letsencrypt.org/domains' ''"
clickhouse keeper-client -q "create '$ZK_BASE' ''"

clickhouse keeper-client -q "create '$ZK_BASE/certificate' \"$(cat $CERT_DIR/fullchain.pem)\""
clickhouse keeper-client -q "set '$ZK_BASE/certificate' \"$(cat $CERT_DIR/fullchain.pem)\""

clickhouse keeper-client -q "create '$ZK_BASE/private_key' \"$(cat $CERT_DIR/privkey.pem)\""
clickhouse keeper-client -q "set '$ZK_BASE/private_key' \"$(cat $CERT_DIR/privkey.pem)\""
```

- [Overview](#overview)
  - [Current limitations](#current-limitations)- [Configuration parameters](#configuration-parameters)- [Initial deployment](#initial-deployment)- [Keeper data structure](#keeper-data-structure)- [Migrating from other ACME clients](#migrating-from-other-acme-clients)

Was this page helpful?
Rebalancing data

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/sre/scaling-clusters.md)

ClickHouse doesn't support automatic shard rebalancing. However, there are ways to rebalance shards in order of preference:

1. Adjust the shard for the [distributed table](/docs/engines/table-engines/special/distributed), allowing writes to be biased to the new shard. This potentially will cause load imbalances and hot spots on the cluster but can be viable in most scenarios where write throughput isn't extremely high. It doesn't require the user to change their write target i.e. It can remain as the distributed table. This doesn't assist with rebalancing existing data.
2. As an alternative to (1), modify the existing cluster and write exclusively to the new shard until the cluster is balanced - manually weighting writes. This has the same limitations as (1).
3. If you need to rebalance existing data and you have partitioned your data, consider detaching partitions and manually relocating them to another node before reattaching to the new shard. This is more manual than subsequent techniques but may be faster and less resource-intensive. This is a manual operation and thus needs to consider the rebalancing of the data.
4. Export the data from the source cluster to the new cluster via an [INSERT FROM SELECT](/docs/sql-reference/statements/insert-into#inserting-the-results-of-select). This won't be performant on very large datasets and will potentially incur significant IO on the source cluster and use considerable network resources. This represents a last resort.

Was this page helpful?
title_wip

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

|  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- |
| Page Description|  |  |  |  | | --- | --- | --- | --- | | [TLS configuration](/docs/guides/sre/tls/configuring-tls) Learn how to configure ClickHouse to use static TLS certificates.|  |  | | --- | --- | | [ACME client configuration](/docs/guides/sre/configuring-tls-acme-client) Learn how to configure ClickHouse to use [ACME](https://datatracker.ietf.org/doc/html/rfc8555) (Automatic Certificate Management Environment) protocol for automatic TLS certificates renewal. | | | | | |

Was this page helpful?
Configuring TLS

The `<server>` section is used for incoming client connections on the secure Keeper port (`tcp_port_secure`). The `<client>` section is used for outgoing connections between Keeper nodes during Raft replication.

Note

The certificate paths above use `/etc/clickhouse-keeper/certs/` which is the typical path for standalone Keeper installations. If you installed Keeper using a different path, adjust accordingly. The certificates themselves are the same ones created in [step 2](#2-create-tls-certificates).

## Summary[​](#summary "Direct link to Summary")

This article focused on getting a ClickHouse environment configured with TLS. The settings will differ for different requirements in production environments; for example, certificate verification levels, protocols, ciphers, etc. But you should now have a good understanding of the steps involved in configuring and implementing secure connections.

- [Create a ClickHouse Deployment](#1-create-a-clickhouse-deployment)- [Create TLS certificates](#2-create-tls-certificates)- [Create and Configure a directory to store certificates and keys.](#3-create-and-configure-a-directory-to-store-certificates-and-keys)- [Configure the environment with basic clusters using ClickHouse Keeper](#4-configure-the-environment-with-basic-clusters-using-clickhouse-keeper)- [Configure TLS interfaces on ClickHouse nodes](#5-configure-tls-interfaces-on-clickhouse-nodes)- [Testing](#6-testing)- [Configure OpenSSL for standalone ClickHouse Keeper](#configure-openssl-for-standalone-clickhouse-keeper)- [Summary](#summary)

Was this page helpful?
Troubleshooting

- Check protocol settings for the HTTP API.
4. Secure connection settings.

   - Check:
     - The [tcp\_port\_secure](/docs/operations/server-configuration-parameters/settings#tcp_port_secure) setting.
     - Settings for [SSL certificates](/docs/operations/server-configuration-parameters/settings#openssl).
   - Use proper parameters while connecting. For example, use the `port_secure` parameter with `clickhouse_client`.
5. User settings:

   - You might be using the wrong user name or password.

## Query processing[​](#query-processing "Direct link to Query processing")

If ClickHouse isn't able to process the query, it sends an error description to the client. In the `clickhouse-client` you get a description of the error in the console. If you're using the HTTP interface, ClickHouse sends the error description in the response body. For example:

```
$ curl 'http://localhost:8123/' --data-binary "SELECT a"
Code: 47, e.displayText() = DB::Exception: Unknown identifier: a. Note that there are no tables (FROM clause) in your query, context: required_names: 'a' source_tables: table_aliases: private_aliases: column_aliases: public_columns: 'a' masked_columns: array_join_columns: source_columns: , e.what() = DB::Exception
```

If you start `clickhouse-client` with the `stack-trace` parameter, ClickHouse returns the server stack trace with the description of an error.

You might see a message about a broken connection. In this case, you can repeat the query. If the connection breaks every time you perform the query, check the server logs for errors.

## Efficiency of query processing[​](#efficiency-of-query-processing "Direct link to Efficiency of query processing")

If you see that ClickHouse is working too slowly, you need to profile the load on the server resources and network for your queries.

You can use the clickhouse-benchmark utility to profile queries. It shows the number of queries processed per second, the number of rows processed per second, and percentiles of query processing times.

- [Installation](#installation)
  - [Can't import GPG keys from keyserver.ubuntu.com with apt-key](#cant-import-gpg-keys-from-keyserverubuntucom-with-apt-key)- [Can't import GPG keys from keyserver.ubuntu.com with gpg](#cant-import-gpg-keys-from-keyserverubuntucom-with-gpg)- [Can't get deb packages from ClickHouse repository with apt-get](#cant-get-deb-packages-from-clickhouse-repository-with-apt-get)- [Can't update deb packages from ClickHouse repository with apt-get](#cant-update-deb-packages-from-clickhouse-repository-with-apt-get)- [You get different warnings with `apt-get update`](#you-get-different-warnings-with-apt-get-update)- [Can't get packages with Yum because of wrong signature](#cant-get-packages-with-yum-because-of-wrong-signature)- [Connecting to the server](#connecting-to-the-server)
    - [Server isn't running](#server-is-not-running)- [Configuration parameters](#configuration-parameters)- [Query processing](#query-processing)- [Efficiency of query processing](#efficiency-of-query-processing)

Was this page helpful?
Working with JOINs in ClickHouse

In order to calculate the concrete cost of each trade, we need to match the trades with their closest quote time.

This is easy and compact with the `ASOF JOIN`, where you use the `ON` clause for specifying an exact match condition and the `AND` clause for specifying the closest match condition - for a specific symbol (exact match) you're looking for the row with the ‘closest’ time from the `quotes` table at exactly or before the time (non-exact match) of a trade of that symbol:

```
SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time
FORMAT Vertical;
```

```
Row 1:
──────
symbol:             ABC
volume:             200
trade_time:         2023-02-22 14:09:05
closest_quote_time: 2023-02-22 14:09:00
quote_price:        32.11
final_price:        6422

Row 2:
──────
symbol:             ABC
volume:             300
trade_time:         2023-02-22 14:09:28
closest_quote_time: 2023-02-22 14:09:20
quote_price:        32.15
final_price:        9645
```

Note

The `ON` clause of the `ASOF JOIN` is required and specifies an exact match condition next to the non-exact match condition of the `AND` clause.

## Summary[​](#summary "Direct link to Summary")

This guide shows how ClickHouse supports all standard SQL JOIN types, plus specialized joins to power analytical queries.
See the documentation for the [JOIN](/docs/sql-reference/statements/select/join) statement for more details on JOINs.

- [Test Data and Resources](#test-data-and-resources)- [Join types supported in ClickHouse](#join-types-supported-in-clickhouse)- [INNER JOIN](#inner-join)- [(LEFT / RIGHT / FULL) OUTER JOIN](#left--right--full-outer-join)- [CROSS JOIN](#cross-join)- [(LEFT / RIGHT) SEMI JOIN](#left--right-semi-join)- [(LEFT / RIGHT) ANTI JOIN](#left--right-anti-join)- [(LEFT / RIGHT / INNER) ANY JOIN](#left--right--inner-any-join)- [ASOF JOIN](#asof-join)- [Summary](#summary)

Was this page helpful?
Selecting ClickHouse Data

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/starter_guides/writing-queries.md)

ClickHouse is a SQL database, and you query your data by writing the same type of `SELECT` queries you're already familiar with. For example:

```
SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp
```

Note

View the [SQL Reference](/docs/sql-reference/statements/select) for more details on the syntax and available clauses and options.

Notice the response comes back in a nice table format:

```
┌─user_id─┬─message────────────────────────────────────────────┬───────────timestamp─┬──metric─┐
│     102 │ Insert a lot of rows per batch                     │ 2022-03-21 00:00:00 │ 1.41421 │
│     102 │ Sort your data based on your commonly-used queries │ 2022-03-22 00:00:00 │   2.718 │
│     101 │ Hello, ClickHouse!                                 │ 2022-03-22 14:04:09 │      -1 │
│     101 │ Granules are the smallest chunks of data read      │ 2022-03-22 14:04:14 │ 3.14159 │
└─────────┴────────────────────────────────────────────────────┴─────────────────────┴─────────┘

4 rows in set. Elapsed: 0.008 sec.
```

Add a `FORMAT` clause to specify one of the [many supported output formats of ClickHouse](/docs/interfaces/formats#formats-overview):

```
SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp
FORMAT TabSeparated
```

In the above query, the output is returned as tab-separated:

```
Query id: 3604df1c-acfd-4117-9c56-f86c69721121

102 Insert a lot of rows per batch      2022-03-21 00:00:00     1.41421
102 Sort your data based on your commonly-used queries  2022-03-22 00:00:00     2.718
101 Hello, ClickHouse!  2022-03-22 14:04:09     -1
101 Granules are the smallest chunks of data read       2022-03-22 14:04:14     3.14159

4 rows in set. Elapsed: 0.005 sec.
```

Note

ClickHouse supports over 70 input and output formats, so between the thousands of functions and all the data formats, you can use ClickHouse to perform some impressive and fast ETL-like data transformations. In fact, you don't even
need a ClickHouse server up and running to transform data - you can use the `clickhouse-local` tool. View the [docs page of `clickhouse-local`](/docs/interfaces/cli) for details.

Was this page helpful?