ClickHouse/clickhouse-docs-embeddings huggingface dataset:
BYOC GCP private networking setup

**Contact ClickHouse Support**:

- Provide the Endpoint's PSC Connection IDs to ClickHouse Support
- Specify which ClickHouse services should allow access from this endpoint
- ClickHouse Support will add the Endpoint Connection IDs to the service allowlist

Connect to ClickHouse via PSC

### Connect to ClickHouse via PSC[​](#step-6-connect-via-psc-endpoint "Direct link to Connect to ClickHouse via PSC")

After the Endpoint Connection IDs is added to the allowlist, you can connect to your ClickHouse service using the PSC endpoint.

The PSC endpoint format is similar to the public endpoint, but includes a `p` subdomain. For example:

- **Public endpoint**: `h5ju65kv87.mhp0y4dmph.us-east1.gcp.clickhouse-byoc.com`
- **PSC endpoint**: `h5ju65kv87.p.mhp0y4dmph.us-east1.gcp.clickhouse-byoc.com`

- [Prerequisites](#common-prerequisites)
  - [Enable private load balancer for ClickHouse BYOC](#step-enable-private-load-balancer-for-clickhouse-byoc)- [Set up VPC peering](#gcp-vpc-peering)
    - [Create a peering connection](#step-1-create-a-peering-connection)- [Access ClickHouse service via peering connection](#step-2-access-ch-service-via-peering)- [Set up PSC (Private Service Connect)](#gcp-psc)
      - [Request PSC service setup](#step-1-request-psc-setup)- [Obtain GCP PSC service name and DNS name](#step-2-obtain-gcp-service-attachment-and-dns-name-for-private-service-connect)- [Create a PSC endpoint in your network](#step-3-create-endpoint)- [Set private DNS name for endpoint](#step-4-set-private-dns-name-for-endpoint)- [Add endpoint's PSC Connection ID to service allowlist](#step-5-add-endpoint-id-allowlist)- [Connect to ClickHouse via PSC](#step-6-connect-via-psc-endpoint)

Was this page helpful?
ClickHouse Cloud support services

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/features/11_support.md)

ClickHouse provides support services for our ClickHouse Cloud users and customers. Our objective is a support services team that represents the ClickHouse product – unparalleled performance, ease of use, and exceptionally fast, high-quality results. For details, [visit our ClickHouse Support Program](https://clickhouse.com/support/program/) page.

[Login to the Cloud console](https://console.clickhouse.cloud/support) and select **Help -> Support** from the menu options to open a new support case and view the status of your submitted cases.

You can also subscribe to our [status page](https://status.clickhouse.com) to get notified quickly about any incidents affecting our platform.

Note

Please note that only subscription customers have a service level agreement on support incidents. If you're not currently a ClickHouse Cloud user – while we will try to answer your question, we'd encourage you to go instead to one of our community resources:

- [ClickHouse community Slack channel](https://clickhouse.com/slack)
- [Other community options](https://github.com/ClickHouse/ClickHouse/blob/master/README.md#useful-links)

Was this page helpful?
How to Build ClickHouse on Linux

### Building in docker[​](#building-in-docker "Direct link to Building in docker")

You can run any build locally in an environment similar to CI using:

```
python -m ci.praktika run "BUILD_JOB_NAME"
```

where BUILD\_JOB\_NAME is the job name as shown in the CI report, e.g., "Build (arm\_release)", "Build (amd\_debug)"

This command pulls the appropriate Docker image `clickhouse/binary-builder` with all required dependencies,
and runs the build script inside it: `./ci/jobs/build_clickhouse.py`

The build output will be placed in `./ci/tmp/`.

It works on both AMD and ARM architectures and requires no additional dependencies other than Python with `requests` module available and Docker.

- [Assumptions](#assumptions)- [Install prerequisites](#install-prerequisites)- [Install the Clang compiler](#install-the-clang-compiler)- [Install the Rust compiler (optional)](#install-the-rust-compiler-optional)- [Build ClickHouse](#build-clickhouse)- [Running the ClickHouse Executable](#running-the-clickhouse-executable)- [Advanced options](#advanced-options)
              - [Minimal Build](#minimal-build)- [Running the ClickHouse Executable](#running-the-clickhouse-executable-1)- [Building on Any Linux](#building-on-any-linux)- [Building in docker](#building-in-docker)

Was this page helpful?
Dictionary

### Refreshing dictionaries[​](#refreshing-dictionaries "Direct link to Refreshing dictionaries")

We have specified a `LIFETIME` for the dictionary of `MIN 600 MAX 900`. LIFETIME is the update interval for the dictionary, with the values here causing a periodic reload at a random interval between 600 and 900s. This random interval is necessary in order to distribute the load on the dictionary source when updating on a large number of servers. During updates, the old version of a dictionary can still be queried, with only the initial load blocking queries. Note that setting `(LIFETIME(0))` prevents dictionaries from updating.
Dictionaries can be forcibly reloaded using the `SYSTEM RELOAD DICTIONARY` command.

For database sources such as ClickHouse and Postgres, you can set up a query that will update the dictionaries only if they really changed (the response of the query determines this), rather than at a periodic interval. Further details can be found [here](/docs/sql-reference/statements/create/dictionary/lifetime).

### Other dictionary types[​](#other-dictionary-types "Direct link to Other dictionary types")

ClickHouse also supports [Hierarchical](/docs/sql-reference/statements/create/dictionary/layouts/hierarchical), [Polygon](/docs/sql-reference/statements/create/dictionary/layouts/polygon) and [Regular Expression](/docs/sql-reference/statements/create/dictionary/layouts/regexp-tree) dictionaries.

### More reading[​](#more-reading "Direct link to More reading")

- [Dictionary best practices](/docs/dictionary/best-practices) — layout selection, dictionaries vs JOINs, monitoring
- [Using Dictionaries to Accelerate Queries](https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse)
- [Advanced Configuration for Dictionaries](/docs/sql-reference/statements/create/dictionary)

- [Speeding up joins using a Dictionary](#speeding-up-joins-using-a-dictionary)
  - [Example](#example)- [Query time enrichment](#query-time-enrichment)- [Index time enrichment](#index-time-enrichment)- [Advanced dictionary topics](#advanced-dictionary-topics)
        - [Refreshing dictionaries](#refreshing-dictionaries)- [Other dictionary types](#other-dictionary-types)- [More reading](#more-reading)

Was this page helpful?
MergeTree table engine

```
--- estimate number of bits in the filter
SELECT bfEstimateBmSize(4300, 0.0001) / 8 AS size_of_bloom_filter_in_bytes;

┌─size_of_bloom_filter_in_bytes─┐
│                         10304 │
└───────────────────────────────┘

--- estimate number of hash functions
SELECT bfEstimateFunctions(4300, bfEstimateBmSize(4300, 0.0001)) as number_of_hash_functions

┌─number_of_hash_functions─┐
│                       13 │
└──────────────────────────┘
```

Of course, you can also use those functions to estimate parameters for other conditions.
The functions above refer to the bloom filter calculator [here](https://hur.st/bloomfilter).

#### Token bloom filter[​](#token-bloom-filter "Direct link to Token bloom filter")

Note

With general availability (GA) of the `text` index starting from ClickHouse version 26.2, the `tokenbf_v1` index is no longer recommended for full text search.

See page ["Full-text search with text indexes"](/docs/engines/table-engines/mergetree-family/textindexes) for details.

```
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
```

#### Sparse grams bloom filter[​](#sparse-grams-bloom-filter "Direct link to Sparse grams bloom filter")

The sparse grams bloom filter is similar to `ngrambf_v1` but uses [sparse grams tokens](/docs/sql-reference/functions/string-functions#sparseGrams) instead of ngrams.

```
sparse_grams(min_ngram_length, max_ngram_length, min_cutoff_length, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)
```

### Text index[​](#text "Direct link to Text index")

Builds an inverted index over tokenized string data, enabling efficient and deterministic full-text search. See [here](/docs/engines/table-engines/mergetree-family/textindexes) for details.

#### Vector similarity[​](#vector-similarity "Direct link to Vector similarity")

Supports approximate nearest neighbor search, see [here](/docs/engines/table-engines/mergetree-family/annindexes) for details.

### Functions support[​](#functions-support "Direct link to Functions support")

Conditions in the `WHERE` clause contains calls of the functions that operate with columns. If the column is a part of an index, ClickHouse tries to use this index when performing the functions. ClickHouse supports different subsets of functions for using indexes.

Indexes of type `set` can be utilized by all functions. The other index types are supported as follows:
argMinIf

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/argMinIf.md)

## Description[​](#description "Direct link to Description")

The [`If`](/docs/sql-reference/aggregate-functions/combinators#-if) combinator can be applied to the [`argMin`](/docs/sql-reference/aggregate-functions/reference/argmin)
function to find the value of `arg` that corresponds to the minimum value of `val` for rows where the condition is true,
using the `argMinIf` aggregate combinator function.

The `argMinIf` function is useful when you need to find the value associated
with the minimum value in a dataset, but only for rows that satisfy a specific
condition.

## Example usage[​](#example-usage "Direct link to Example usage")

In this example, we'll create a table that stores product prices and their timestamps,
and we'll use `argMinIf` to find the lowest price for each product when it's in stock.

```
CREATE TABLE product_prices(
    product_id UInt32,
    price Decimal(10,2),
    timestamp DateTime,
    in_stock UInt8
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO product_prices VALUES
    (1, 10.99, '2024-01-01 10:00:00', 1),
    (1, 9.99, '2024-01-01 10:05:00', 1),
    (1, 11.99, '2024-01-01 10:10:00', 0),
    (2, 20.99, '2024-01-01 11:00:00', 1),
    (2, 19.99, '2024-01-01 11:05:00', 1),
    (2, 21.99, '2024-01-01 11:10:00', 1);

SELECT
    product_id,
    argMinIf(price, timestamp, in_stock = 1) AS lowest_price_when_in_stock
FROM product_prices
GROUP BY product_id;
```

The `argMinIf` function will find the price that corresponds to the earliest timestamp for each product,
but only considering rows where `in_stock = 1`. For example:

- Product 1: Among in-stock rows, 10.99 has the earliest timestamp (10:00:00)
- Product 2: Among in-stock rows, 20.99 has the earliest timestamp (11:00:00)

```
   ┌─product_id─┬─lowest_price_when_in_stock─┐
1. │          1 │                      10.99 │
2. │          2 │                      20.99 │
   └────────────┴────────────────────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`argMin`](/docs/sql-reference/aggregate-functions/reference/argmin)
- [`argMax`](/docs/sql-reference/aggregate-functions/reference/argmax)
- [`argMaxIf`](/docs/examples/aggregate-function-combinators/argMaxIf)
- [`If combinator`](/docs/sql-reference/aggregate-functions/combinators#-if)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?
avgIf

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/avgIf.md)

## Description[​](#description "Direct link to Description")

The [`If`](/docs/sql-reference/aggregate-functions/combinators#-if) combinator can be applied to the [`avg`](/docs/sql-reference/aggregate-functions/reference/avg)
function to calculate the arithmetic mean of values for rows where the condition is true,
using the `avgIf` aggregate combinator function.

## Example usage[​](#example-usage "Direct link to Example usage")

In this example, we'll create a table that stores sales data with success flags,
and we'll use `avgIf` to calculate the average sale amount for successful transactions.

```
CREATE TABLE sales(
    transaction_id UInt32,
    amount Decimal(10,2),
    is_successful UInt8
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO sales VALUES
    (1, 100.50, 1),
    (2, 200.75, 1),
    (3, 150.25, 0),
    (4, 300.00, 1),
    (5, 250.50, 0),
    (6, 175.25, 1);

SELECT
    avgIf(amount, is_successful = 1) AS avg_successful_sale
FROM sales;
```

The `avgIf` function will calculate the average amount only for rows where `is_successful = 1`.
In this case, it will average the amounts: 100.50, 200.75, 300.00, and 175.25.

```
   ┌─avg_successful_sale─┐
1. │              193.88 │
   └─────────────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`avg`](/docs/sql-reference/aggregate-functions/reference/avg)
- [`If combinator`](/docs/sql-reference/aggregate-functions/combinators#-if)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?
avgMap

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/avgMap.md)

## Description[​](#description "Direct link to Description")

The [`Map`](/docs/sql-reference/aggregate-functions/combinators#-map) combinator can be applied to the [`avg`](/docs/sql-reference/aggregate-functions/reference/avg)
function to calculate the arithmetic mean of values in a Map according to each key, using the `avgMap`
aggregate combinator function.

## Example usage[​](#example-usage "Direct link to Example usage")

In this example, we'll create a table that stores status codes and their counts for different timeslots,
where each row contains a Map of status codes to their corresponding counts. We'll use
`avgMap` to calculate the average count for each status code within each timeslot.

```
CREATE TABLE metrics(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO metrics VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [15, 25, 35])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [45, 55, 65])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [75, 85, 95])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [105, 115, 125]));

SELECT
    timeslot,
    avgMap(status),
FROM metrics
GROUP BY timeslot;
```

The `avgMap` function will calculate the average count for each status code within each timeslot. For example:

- In timeslot '2000-01-01 00:00:00':
  - Status 'a': 15
  - Status 'b': 25
  - Status 'c': (35 + 45) / 2 = 40
  - Status 'd': 55
  - Status 'e': 65
- In timeslot '2000-01-01 00:01:00':
  - Status 'd': 75
  - Status 'e': 85
  - Status 'f': (95 + 105) / 2 = 100
  - Status 'g': (115 + 125) / 2 = 120

```
   ┌────────────timeslot─┬─avgMap(status)───────────────────────┐
1. │ 2000-01-01 00:01:00 │ {'d':75,'e':85,'f':100,'g':120}      │
2. │ 2000-01-01 00:00:00 │ {'a':15,'b':25,'c':40,'d':55,'e':65} │
   └─────────────────────┴──────────────────────────────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`avg`](/docs/sql-reference/aggregate-functions/reference/avg)
- [`Map combinator`](/docs/sql-reference/aggregate-functions/combinators#-map)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?
countIf

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/countIf.md)

## Description[​](#description "Direct link to Description")

The [`If`](/docs/sql-reference/aggregate-functions/combinators#-if) combinator can be applied to the [`count`](/docs/sql-reference/aggregate-functions/reference/count)
function to count the number of rows where the condition is true,
using the `countIf` aggregate combinator function.

## Example usage[​](#example-usage "Direct link to Example usage")

In this example, we'll create a table that stores user login attempts,
and we'll use `countIf` to count the number of successful logins.

```
CREATE TABLE login_attempts(
    user_id UInt32,
    timestamp DateTime,
    is_successful UInt8
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO login_attempts VALUES
    (1, '2024-01-01 10:00:00', 1),
    (1, '2024-01-01 10:05:00', 0),
    (1, '2024-01-01 10:10:00', 1),
    (2, '2024-01-01 11:00:00', 1),
    (2, '2024-01-01 11:05:00', 1),
    (2, '2024-01-01 11:10:00', 0);

SELECT
    user_id,
    countIf(is_successful = 1) AS successful_logins
FROM login_attempts
GROUP BY user_id;
```

The `countIf` function will count only the rows where `is_successful = 1` for each user.

```
   ┌─user_id─┬─successful_logins─┐
1. │       1 │                 2 │
2. │       2 │                 2 │
   └─────────┴───────────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`count`](/docs/sql-reference/aggregate-functions/reference/count)
- [`If combinator`](/docs/sql-reference/aggregate-functions/combinators#-if)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?
maxMap

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/maxMap.md)

## Description[​](#description "Direct link to Description")

The [`Map`](/docs/sql-reference/aggregate-functions/combinators#-map) combinator can be applied to the [`max`](/docs/sql-reference/aggregate-functions/reference/max)
function to calculate the maximum value in a Map according to each key, using the `maxMap`
aggregate combinator function.

## Example usage[​](#example-usage "Direct link to Example usage")

In this example, we'll create a table that stores status codes and their counts for different timeslots,
where each row contains a Map of status codes to their corresponding counts. We'll use
`maxMap` to find the maximum count for each status code within each timeslot.

```
CREATE TABLE metrics(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO metrics VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [15, 25, 35])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [45, 55, 65])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [75, 85, 95])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [105, 115, 125]));

SELECT
    timeslot,
    maxMap(status),
FROM metrics
GROUP BY timeslot;
```

The `maxMap` function will find the maximum count for each status code within each timeslot. For example:

- In timeslot '2000-01-01 00:00:00':
  - Status 'a': 15
  - Status 'b': 25
  - Status 'c': max(35, 45) = 45
  - Status 'd': 55
  - Status 'e': 65
- In timeslot '2000-01-01 00:01:00':
  - Status 'd': 75
  - Status 'e': 85
  - Status 'f': max(95, 105) = 105
  - Status 'g': max(115, 125) = 125

```
   ┌────────────timeslot─┬─maxMap(status)───────────────────────┐
1. │ 2000-01-01 00:01:00 │ {'d':75,'e':85,'f':105,'g':125}      │
2. │ 2000-01-01 00:00:00 │ {'a':15,'b':25,'c':45,'d':55,'e':65} │
   └─────────────────────┴──────────────────────────────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`max`](/docs/sql-reference/aggregate-functions/reference/max)
- [`Map combinator`](/docs/sql-reference/aggregate-functions/combinators#-map)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?
minMap

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/minMap.md)

## Description[​](#description "Direct link to Description")

The [`Map`](/docs/sql-reference/aggregate-functions/combinators#-map) combinator can be applied to the [`min`](/docs/sql-reference/aggregate-functions/reference/min)
function to calculate the minimum value in a Map according to each key, using the `minMap`
aggregate combinator function.

## Example usage[​](#example-usage "Direct link to Example usage")

In this example, we'll create a table that stores status codes and their counts for different timeslots,
where each row contains a Map of status codes to their corresponding counts. We'll use
`minMap` to find the minimum count for each status code within each timeslot.

```
CREATE TABLE metrics(
    date Date,
    timeslot DateTime,
    status Map(String, UInt64)
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO metrics VALUES
    ('2000-01-01', '2000-01-01 00:00:00', (['a', 'b', 'c'], [15, 25, 35])),
    ('2000-01-01', '2000-01-01 00:00:00', (['c', 'd', 'e'], [45, 55, 65])),
    ('2000-01-01', '2000-01-01 00:01:00', (['d', 'e', 'f'], [75, 85, 95])),
    ('2000-01-01', '2000-01-01 00:01:00', (['f', 'g', 'g'], [105, 115, 125]));

SELECT
    timeslot,
    minMap(status),
FROM metrics
GROUP BY timeslot;
```

The `minMap` function will find the minimum count for each status code within each timeslot. For example:

- In timeslot '2000-01-01 00:00:00':
  - Status 'a': 15
  - Status 'b': 25
  - Status 'c': min(35, 45) = 35
  - Status 'd': 55
  - Status 'e': 65
- In timeslot '2000-01-01 00:01:00':
  - Status 'd': 75
  - Status 'e': 85
  - Status 'f': min(95, 105) = 95
  - Status 'g': min(115, 125) = 115

```
   ┌────────────timeslot─┬─minMap(status)───────────────────────┐
1. │ 2000-01-01 00:01:00 │ {'d':75,'e':85,'f':95,'g':115}       │
2. │ 2000-01-01 00:00:00 │ {'a':15,'b':25,'c':35,'d':55,'e':65} │
   └─────────────────────┴──────────────────────────────────────┘
```

## See also[​](#see-also "Direct link to See also")

- [`min`](/docs/sql-reference/aggregate-functions/reference/min)
- [`Map combinator`](/docs/sql-reference/aggregate-functions/combinators#-map)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?