ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

Conditional and array functions make queries significantly simpler. The following query computes the tags (with more than 10000 occurrences) with the largest percentage increase from 2022 to 2023. Note how the following ClickHouse query is succinct thanks to the conditionals, array functions, and ability to reuse aliases in the HAVING and SELECT clauses.

```
--ClickHouse
SELECT  arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag,
        countIf(toYear(CreationDate) = 2023) AS count_2023,
        countIf(toYear(CreationDate) = 2022) AS count_2022,
        ((count_2023 - count_2022) / count_2022) * 100 AS percent_change
FROM stackoverflow.posts
WHERE toYear(CreationDate) IN (2022, 2023)
GROUP BY tag
HAVING (count_2022 > 10000) AND (count_2023 > 10000)
ORDER BY percent_change DESC
LIMIT 5

┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐
│ next.js       │       13788 │         10520 │   31.06463878326996 │
│ spring-boot │         16573 │         17721 │  -6.478189718413183 │
│ .net          │       11458 │         12968 │ -11.644046884639112 │
│ azure         │       11996 │         14049 │ -14.613139725247349 │
│ docker        │       13885 │         16877 │  -17.72826924216389 │
└─────────────┴────────────┴────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.247 sec. Processed 5.08 million rows, 155.73 MB (20.58 million rows/s., 630.61 MB/s.)
Peak memory usage: 403.04 MiB.
```

```
--Postgres
SELECT
        tag,
        SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) AS count_2023,
        SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) AS count_2022,
        ((SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) - SUM(CASE WHEN year = 2022 THEN count ELSE 0 END))
        / SUM(CASE WHEN year = 2022 THEN count ELSE 0 END)::float) * 100 AS percent_change
FROM (
        SELECT
        unnest(string_to_array(Tags, '|')) AS tag,
        EXTRACT(YEAR FROM CreationDate) AS year,
        COUNT(*) AS count
        FROM public.posts
        WHERE EXTRACT(YEAR FROM CreationDate) IN (2022, 2023)
        AND Tags <> ''
        GROUP BY tag, year
) AS yearly_counts
GROUP BY tag
HAVING SUM(CASE WHEN year = 2022 THEN count ELSE 0 END) > 10000
   AND SUM(CASE WHEN year = 2023 THEN count ELSE 0 END) > 10000
ORDER BY percent_change DESC
LIMIT 5;

        tag     | count_2023 | count_2022 |   percent_change
-------------+------------+------------+---------------------
 next.js        |       13712 |         10370 |   32.22757955641273
 spring-boot |          16482 |         17474 |  -5.677005837243905
 .net           |       11376 |         12750 | -10.776470588235295
 azure          |       11938 |         13966 | -14.520979521695546
 docker         |       13832 |         16701 | -17.178612059158134
(5 rows)

Time: 116750.131 ms (01:56.750)
```

[Click here for Part 3](/docs/migrations/postgresql/data-modeling-techniques)

- [Deduplication using CDC](#deduplication-cdc)- [Optimize queries in ClickHouse](#optimize-queries-in-clickhouse)

Was this page helpful?
Amazon Redshift to ClickHouse migration guide

### Cons[​](#cons-2 "Direct link to Cons")

- Two steps in the process (unload from Redshift then load into ClickHouse).

### Tutorial[​](#tutorial-1 "Direct link to Tutorial")

Export data into an S3 bucket using UNLOAD

#### Export data into an S3 bucket using UNLOAD[​](#export-data-into-an-s3-bucket-using-unload "Direct link to Export data into an S3 bucket using UNLOAD")

Using Redshift's [UNLOAD](https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) feature, export the data into an existing private S3 bucket:

It will generate part files containing the raw data in S3

Create the table in ClickHouse

#### Create the table in ClickHouse[​](#create-the-table-in-clickhouse "Direct link to Create the table in ClickHouse")

Create the table in ClickHouse:

```
CREATE TABLE users
(
  username String,
  firstname String,
  lastname String
)
ENGINE = MergeTree
ORDER BY username
```

Alternatively, ClickHouse can try to infer the table structure using `CREATE TABLE ... EMPTY AS SELECT`:

```
CREATE TABLE users
ENGINE = MergeTree ORDER BY username
EMPTY AS
SELECT * FROM s3('https://your-bucket.s3.amazonaws.com/unload/users/*', '<aws_access_key>', '<aws_secret_access_key>', 'CSV')
```

This works especially well when the data is in a format that contains information about data types, like Parquet.

Load S3 files into ClickHouse

#### Load S3 files into ClickHouse[​](#load-s3-files-into-clickhouse "Direct link to Load S3 files into ClickHouse")

Load the S3 files into ClickHouse using an `INSERT INTO ... SELECT` statement:

```
INSERT INTO users SELECT *
FROM s3('https://your-bucket.s3.amazonaws.com/unload/users/*', '<aws_access_key>', '<aws_secret_access_key>', 'CSV')
```

```
Query id: 2e7e219a-6124-461c-8d75-e4f5002c8557

Ok.

0 rows in set. Elapsed: 0.545 sec. Processed 49.99 thousand rows, 2.34 MB (91.72 thousand rows/s., 4.30 MB/s.)
```

Note

This example used CSV as the pivot format. However, for production workloads we recommend Apache Parquet as the best option for large migrations since it comes with compression and can save some storage costs while reducing transfer times. (By default, each row group is compressed using SNAPPY). ClickHouse also leverages Parquet's column orientation to speed up data ingestion.

- [Introduction](#introduction)- [Push Data from Redshift to ClickHouse](#push-data-from-redshift-to-clickhouse)
    - [Pros](#pros)- [Cons](#cons)- [Pull Data from Redshift to ClickHouse](#pull-data-from-redshift-to-clickhouse)
      - [Pros](#pros-1)- [Cons](#cons-1)- [Tutorial](#tutorial)- [Pivot Data from Redshift to ClickHouse using S3](#pivot-data-from-redshift-to-clickhouse-using-s3)
        - [Pros](#pros-2)- [Cons](#cons-2)- [Tutorial](#tutorial-1)

Was this page helpful?
Amazon Redshift SQL translation guide

Redshift supports a couple of convenience features for sorting keys. The first is
automatic sorting keys (using `SORTKEY AUTO`). While this may be appropriate for
getting started, explicit sorting keys ensure the best performance and storage
efficiency when the sorting key is optimal. The second is the `INTERLEAVED` sort key,
which gives equal weight to a subset of columns in the sort key to improve
performance when a query uses one or more secondary sort columns. ClickHouse
supports explicit [projections](/docs/data-modeling/projections), which achieve the
same end-result with a slightly different setup.

You should be aware that the “primary key” concept represents different things
in ClickHouse and Redshift. In Redshift, the primary key resembles the traditional
RDMS concept intended to enforce constraints. However, they're not strictly
enforced in Redshift and instead act as hints for the query planner and data
distribution among nodes. In ClickHouse, the primary key denotes columns used
to construct the sparse primary index, used to ensure the data is ordered on
disk, maximizing compression while avoiding pollution of the primary index and
wasting memory.

- [Data types](#data-types)- [DDL syntax](#compression)
    - [Sorting keys](#sorting-keys)

Was this page helpful?
Snowflake SQL translation guide

Finally, ClickHouse offers the unique ability to store the intermediate
[state of aggregate functions](/docs/sql-reference/data-types/aggregatefunction). This
state is implementation-specific, but allows the result of an aggregation to be
stored and later queried (with corresponding merge functions). Typically, this
feature is used via a materialized view and, as demonstrated below, offers the
ability to improve performance of specific queries with minimal storage cost by
storing the incremental result of queries over inserted data (more details here).

- [Data types](#data-types)
  - [Numerics](#numerics)- [Strings](#strings)- [Semi-structured types](#semi-structured-data)- [Type reference](#type-reference)

Was this page helpful?
Basics

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/native-protocol/basics.md)

Note

Client protocol reference is in progress.

Most examples are only in Go.

This document describes binary protocol for ClickHouse TCP clients.

## Varint[​](#varint "Direct link to Varint")

For lengths, packet codes and other cases the *unsigned varint* encoding is used.
Use [binary.PutUvarint](https://pkg.go.dev/encoding/binary#PutUvarint) and [binary.ReadUvarint](https://pkg.go.dev/encoding/binary#ReadUvarint).

Note

*Signed* varint isn't used.

## String[​](#string "Direct link to String")

Variable length strings are encoded as *(length, value)*, where *length* is [varint](#varint) and *value* is utf8 string.

References

Validate length to prevent OOM:

`0 ≤ len < MAX`

- Encode- Decode

```
s := "Hello, world!"

// Writing string length as uvarint.
buf := make([]byte, binary.MaxVarintLen64)
n := binary.PutUvarint(buf, uint64(len(s)))
buf = buf[:n]

// Writing string value.
buf = append(buf, s...)
```

```
r := bytes.NewReader([]byte{
    0xd, 0x48, 0x65, 0x6c, 0x6c, 0x6f, 0x2c,
    0x20, 0x77, 0x6f, 0x72, 0x6c, 0x64, 0x21,
})

// Read length.
n, err := binary.ReadUvarint(r)
if err != nil {
        panic(err)
}

// Check n to prevent OOM or runtime exception in make().
const maxSize = 1024 * 1024 * 10 // 10 MB
if n > maxSize || n < 0 {
    panic("invalid n")
}

buf := make([]byte, n)
if _, err := io.ReadFull(r, buf); err != nil {
        panic(err)
}

fmt.Println(string(buf))
// Hello, world!
```

- Hex dump- Base64- Go

```
00000000  0d 48 65 6c 6c 6f 2c 20  77 6f 72 6c 64 21        |.Hello, world!|
```

```
DUhlbGxvLCB3b3JsZCE
```

```
data := []byte{
    0xd, 0x48, 0x65, 0x6c, 0x6c, 0x6f, 0x2c,
    0x20, 0x77, 0x6f, 0x72, 0x6c, 0x64, 0x21,
}
```

## Integers[​](#integers "Direct link to Integers")

Tip

ClickHouse uses **Little Endian** for fixed size integers.

### Int32[​](#int32 "Direct link to Int32")

```
v := int32(1000)

// Encode.
buf := make([]byte, 8)
binary.LittleEndian.PutUint32(buf, uint32(v))

// Decode.
d := int32(binary.LittleEndian.Uint32(buf))
fmt.Println(d) // 1000
```

- Hex dump- Base64

```
00000000  e8 03 00 00 00 00 00 00                           |........|
```

```
6AMAAAAAAAA
```

## Boolean[​](#boolean "Direct link to Boolean")

Booleans are represented by single byte, `1` is `true` and `0` is `false`.

- [Varint](#varint)- [String](#string)- [Integers](#integers)
      - [Int32](#int32)- [Boolean](#boolean)

Was this page helpful?
Client packets

### Settings[​](#settings "Direct link to Settings")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| field type value description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | key String `send_logs_level` Key of setting|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | value String `trace` Value of setting|  |  |  |  | | --- | --- | --- | --- | | important Bool `true` Can be ignored or not | | | | | | | | | | | | | | | |

Encoded as list, blank key and value denotes end of list.

### Stage[​](#stage "Direct link to Stage")

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| value name description|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 0 FetchColumns Only fetch column types|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 1 WithMergeableState Until mergeable state|  |  |  | | --- | --- | --- | | 2 Complete Until full completeness (should be default) | | | | | | | | | | | |

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

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| field type description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | info BlockInfo Encoded block info|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | columns UVarInt Columns count|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | rows UVarInt Rows count|  |  |  | | --- | --- | --- | | columns [[]Column](#column) Columns with data | | | | | | | | | | | | | | |

### Column[​](#column "Direct link to Column")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| field type value description|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | name String `foo` Column name|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | type String `DateTime64(9)` Column type|  |  |  |  | | --- | --- | --- | --- | | data bytes ~ Column data | | | | | | | | | | | | | | | |

## Cancel[​](#cancel "Direct link to Cancel")

No packet body. Server should cancel query.

## Ping[​](#ping "Direct link to Ping")

No packet body. Server should [respond with pong](/docs/native-protocol/server#pong).

- [Hello](#hello)
  - [Protocol version](#protocol-version)- [Defaults](#defaults)- [Query](#query)
    - [Client info](#client-info)- [Settings](#settings)- [Stage](#stage)- [Data](#data)
      - [Column](#column)- [Cancel](#cancel)- [Ping](#ping)

Was this page helpful?
CityHash

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/native-protocol/hash.md)

ClickHouse uses **one of the previous** versions of [CityHash from Google](https://github.com/google/cityhash).

References

CityHash has changed the algorithm after we have added it into ClickHouse.

CityHash documentation specifically notes that the user shouldn't rely on
specific hash values and shouldn't save it anywhere or use it as a sharding key.

But as we exposed this function to the user, we had to fix the version of CityHash (to 1.0.2). And now we guarantee that the behaviour of CityHash functions available in SQL won't change.

— Alexey Milovidov

Note

Current version of Google's CityHash [differs](https://github.com/ClickHouse/ClickHouse/issues/8354) from ClickHouse `cityHash64` variant.

Don't use `farmHash64` to get Google's CityHash value! [FarmHash](https://opensource.googleblog.com/2014/03/introducing-farmhash.html) is a successor to CityHash, but they're not fully compatible.

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| String ClickHouse64 CityHash64 FarmHash64|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | `Moscow` 12507901496292878638 5992710078453357409 5992710078453357409|  |  |  |  | | --- | --- | --- | --- | | `How can you write a big system without C++? -Paul Glick` 6237945311650045625 749291162957442504 11716470977470720228 | | | | | | | | | | | |

Also see [Introducing CityHash](https://opensource.googleblog.com/2011/04/introducing-cityhash.html) for description and
reasoning behind creation. TL;DR **non-cryptographic** hash that is faster than [MurmurHash](https://en.wikipedia.org/wiki/MurmurHash), but more complex.

## Implementations[​](#implementations "Direct link to Implementations")

### Go[​](#go "Direct link to Go")

You can use [go-faster/city](https://github.com/go-faster/city) Go package that implements both variants.

- [Implementations](#implementations)
  - [Go](#go)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/observability/build-your-own/demo-application.md)

The OpenTelemetry project includes a [demo application](https://opentelemetry.io/docs/demo/). A maintained fork of this application with ClickHouse as a data source for logs and traces can be found [here](https://github.com/ClickHouse/opentelemetry-demo). The [official demo instructions](https://opentelemetry.io/docs/demo/docker-deployment/) can be followed to deploy this demo with docker. In addition to the [existing components](https://opentelemetry.io/docs/demo/collector-data-flow-dashboard/), an instance of ClickHouse will be deployed and used for the storage of logs and traces.

Was this page helpful?
Using Grafana and ClickHouse for Observability

The plugin provides several out-of-the-box dashboards, including an example dashboard, "Simple ClickHouse OTel dashboarding," for logging and tracing data conforming to the OTel specification. This requires users to conform to the default column names for OTel and can be installed from the data source configuration.

We provide some simple tips for building visualizations below.

### Time series[​](#time-series "Direct link to Time series")

Along with statistics, line charts are the most common form of visualization used in observability use cases. The Clickhouse plugin will automatically render a line chart if a query returns a `datetime` named `time` and a numeric column. For example:

```
SELECT
 $__timeInterval(Timestamp) as time,
 quantile(0.99)(Duration)/1000000 AS p99
FROM otel_traces
WHERE
 $__timeFilter(Timestamp)
 AND ( Timestamp  >= $__fromTime AND Timestamp <= $__toTime )
GROUP BY time
ORDER BY time ASC
LIMIT 100000
```

### Multi-line charts[​](#multi-line-charts "Direct link to Multi-line charts")

Multi-line charts will be automatically rendered for a query provided the following conditions are met:

- field 1: datetime field with an alias of time
- field 2: value to group by. This should be a String.
- field 3+: the metric values

For example:

```
SELECT
  $__timeInterval(Timestamp) as time,
  ServiceName,
  quantile(0.99)(Duration)/1000000 AS p99
FROM otel_traces
WHERE $__timeFilter(Timestamp)
AND ( Timestamp  >= $__fromTime AND Timestamp <= $__toTime )
GROUP BY ServiceName, time
ORDER BY time ASC
LIMIT 100000
```

### Visualizing geo data[​](#visualizing-geo-data "Direct link to Visualizing geo data")

We have explored enriching observability data with geo coordinates using IP dictionaries in earlier sections. Assuming you have `latitude` and `longitude` columns, observability can be visualized using the `geohashEncode` function. This produces geo hashes compatible with the Grafana Geo Map chart. An example query and visualization are shown below:

```
WITH coords AS
        (
        SELECT
                Latitude,
                Longitude,
                geohashEncode(Longitude, Latitude, 4) AS hash
        FROM otel_logs_v2
        WHERE (Longitude != 0) AND (Latitude != 0)
        )
SELECT
        hash,
        count() AS heat,
        round(log10(heat), 2) AS adj_heat
FROM coords
GROUP BY hash
```

- [OpenTelemetry Integration](#open-telemetry-integration)- [Logs](#logs)
    - [Logs to traces](#logs-to-traces)- [Traces](#traces)
      - [View trace details](#view-trace-details)- [Traces to logs](#traces-to-logs)- [Dashboards](#dashboards)
        - [Time series](#time-series)- [Multi-line charts](#multi-line-charts)- [Visualizing geo data](#visualizing-geo-data)

Was this page helpful?
Managing data

```
CREATE TABLE otel_logs_merged
ENGINE = Merge('default', 'otel_logs_v[2|3]')

SELECT Status, count() AS c
FROM otel_logs_merged
GROUP BY Status
ORDER BY c DESC
LIMIT 5

┌─Status─┬────────c─┐
│   200  │ 38319300 │
│   304  │  1360912 │
│   302  │   799340 │
│   404  │   420044 │
│   301  │   270212 │
└────────┴──────────┘

5 rows in set. Elapsed: 0.073 sec. Processed 41.46 million rows, 82.92 MB (565.43 million rows/s., 1.13 GB/s.)
```

This can be updated whenever a new table is added using the `EXCHANGE` table syntax. For example, to add a v4 table we can create a new table and exchange this atomically with the previous version.

```
CREATE TABLE otel_logs_merged_temp
ENGINE = Merge('default', 'otel_logs_v[2|3|4]')

EXCHANGE TABLE otel_logs_merged_temp AND otel_logs_merged

SELECT Status, count() AS c
FROM otel_logs_merged
GROUP BY Status
ORDER BY c DESC
LIMIT 5

┌─Status─┬────────c─┐
│   200  │ 39259996 │
│   304  │  1378564 │
│   302  │   820118 │
│   404  │   429220 │
│   301  │   276960 │
└────────┴──────────┘

5 rows in set. Elapsed: 0.068 sec. Processed 42.46 million rows, 84.92 MB (620.45 million rows/s., 1.24 GB/s.)
```

- [Partitions](#partitions)
  - [Applications](#applications)- [Query performance](#query-performance)- [Data management with TTL (Time-to-live)](#data-management-with-ttl-time-to-live)
    - [Table level TTL](#table-level-ttl)- [Column level TTL](#column-level-ttl)- [Recompressing data](#recompressing-data)- [Storage tiers](#storage-tiers)- [Managing schema changes](#managing-schema-changes)
          - [Use default values](#use-default-values)- [Create new tables](#create-new-tables)

Was this page helpful?
title_wip

Contains information about memory allocations done via the jemalloc allocator in different size classes (bins) aggregated from all arenas.

[Reference](/docs/operations/system-tables/jemalloc_bins)

### Prometheus[​](#prometheus "Direct link to Prometheus")

All `jemalloc` related metrics from `asynchronous_metrics` are also exposed using the Prometheus endpoint in both ClickHouse and Keeper.

[Reference](/docs/operations/server-configuration-parameters/settings#prometheus)

### `jmst` 4LW command in Keeper[​](#jmst-4lw-command-in-keeper "Direct link to jmst-4lw-command-in-keeper")

Keeper supports the `jmst` 4LW command which returns [basic allocator statistics](https://github.com/jemalloc/jemalloc/wiki/Use-Case%3A-Basic-Allocator-Statistics):

```
echo jmst | nc localhost 9181
```

- [Sampling allocations and flushing heap profiles](#sampling-allocations-and-flushing-heap-profiles)- [Analyzing heap profiles](#analyzing-heap-profiles)
    - [Examples](#examples)- [Generating a flame graph](#generating-flame-graph)- [Controlling allocation profiler during runtime](#controlling-allocation-profiler-during-runtime)- [Additional options for the profiler](#additional-options-for-profiler)- [Other resources](#other-resources)
          - [System table `asynchronous_metrics`](#system-table-asynchronous_metrics)- [System table `jemalloc_bins`](#system-table-jemalloc_bins)- [Prometheus](#prometheus)- [`jmst` 4LW command in Keeper](#jmst-4lw-command-in-keeper)

Was this page helpful?