ClickHouse/clickhouse-docs-embeddings huggingface dataset:
quantilesTimingArrayIf

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

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

The [`Array`](/docs/sql-reference/aggregate-functions/combinators#-array) and [`If`](/docs/sql-reference/aggregate-functions/combinators#-if)
combinator can be applied to the [`quantilesTiming`](/docs/sql-reference/aggregate-functions/reference/quantiletiming)
function to calculate quantiles of timing values in arrays for rows where the condition is true,
using the `quantilesTimingArrayIf` aggregate combinator function.

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

In this example, we'll create a table that stores API response times for different endpoints,
and we'll use `quantilesTimingArrayIf` to calculate response time quantiles for successful requests.

```
CREATE TABLE api_responses(
    endpoint String,
    response_times_ms Array(UInt32),
    success_rate Float32
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO api_responses VALUES
    ('orders', [82, 94, 98, 87, 103, 92, 89, 105], 0.98),
    ('products', [45, 52, 48, 51, 49, 53, 47, 50], 0.95),
    ('users', [120, 125, 118, 122, 121, 119, 123, 124], 0.92);

SELECT
    endpoint,
    quantilesTimingArrayIf(0, 0.25, 0.5, 0.75, 0.95, 0.99, 1.0)(response_times_ms, success_rate >= 0.95) as response_time_quantiles
FROM api_responses
GROUP BY endpoint;
```

The `quantilesTimingArrayIf` function will calculate quantiles only for endpoints with a success rate above 95%.
The returned array contains the following quantiles in order:

- 0 (minimum)
- 0.25 (first quartile)
- 0.5 (median)
- 0.75 (third quartile)
- 0.95 (95th percentile)
- 0.99 (99th percentile)
- 1.0 (maximum)

```
   ┌─endpoint─┬─response_time_quantiles─────────────────────────────────────────────┐
1. │ orders   │ [82, 87, 92, 98, 103, 104, 105]                                     │
2. │ products │ [45, 47, 49, 51, 52, 52, 53]                                        │
3. │ users    │ [nan, nan, nan, nan, nan, nan, nan]                                 │
   └──────────┴─────────────────────────────────────────────────────────────────────┘
```

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

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

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

Was this page helpful?
quantilesTimingIf

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

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

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

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

In this example, we'll create a table that stores API response times for different endpoints,
and we'll use `quantilesTimingIf` to calculate response time quantiles for successful requests.

```
CREATE TABLE api_responses(
    endpoint String,
    response_time_ms UInt32,
    is_successful UInt8
) ENGINE = MergeTree
ORDER BY ();

INSERT INTO api_responses VALUES
    ('orders', 82, 1),
    ('orders', 94, 1),
    ('orders', 98, 1),
    ('orders', 87, 1),
    ('orders', 103, 1),
    ('orders', 92, 1),
    ('orders', 89, 1),
    ('orders', 105, 1),
    ('products', 45, 1),
    ('products', 52, 1),
    ('products', 48, 1),
    ('products', 51, 1),
    ('products', 49, 1),
    ('products', 53, 1),
    ('products', 47, 1),
    ('products', 50, 1),
    ('users', 120, 0),
    ('users', 125, 0),
    ('users', 118, 0),
    ('users', 122, 0),
    ('users', 121, 0),
    ('users', 119, 0),
    ('users', 123, 0),
    ('users', 124, 0);

SELECT
    endpoint,
    quantilesTimingIf(0, 0.25, 0.5, 0.75, 0.95, 0.99, 1.0)(response_time_ms, is_successful = 1) as response_time_quantiles
FROM api_responses
GROUP BY endpoint;
```

The `quantilesTimingIf` function will calculate quantiles only for successful requests (is\_successful = 1).
The returned array contains the following quantiles in order:

- 0 (minimum)
- 0.25 (first quartile)
- 0.5 (median)
- 0.75 (third quartile)
- 0.95 (95th percentile)
- 0.99 (99th percentile)
- 1.0 (maximum)

```
   ┌─endpoint─┬─response_time_quantiles─────────────────────────────────────────────┐
1. │ orders   │ [82, 87, 92, 98, 103, 104, 105]                                     │
2. │ products │ [45, 47, 49, 51, 52, 52, 53]                                        │
3. │ users    │ [nan, nan, nan, nan, nan, nan, nan]                                 │
   └──────────┴─────────────────────────────────────────────────────────────────────┘
```

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

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

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

Was this page helpful?
sumMap

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

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

The [`Map`](/docs/sql-reference/aggregate-functions/combinators#-map) combinator can be applied to the [`sum`](/docs/sql-reference/aggregate-functions/reference/sum)
function to calculate the sum of values in a Map according to each key, using the `sumMap`
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
`sumMap` to calculate the total 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,
    sumMap(status),
FROM metrics
GROUP BY timeslot;
```

The `sumMap` function will calculate the total 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 = 80
  - Status 'd': 55
  - Status 'e': 65
- In timeslot '2000-01-01 00:01:00':
  - Status 'd': 75
  - Status 'e': 85
  - Status 'f': 95 + 105 = 200
  - Status 'g': 115 + 125 = 240

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

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

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

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

Was this page helpful?
Does ClickHouse support distributed joins?

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/faq/general/distributed-join.md)

Yes, ClickHouse supports distributed joins on a cluster.

When the data is co-located on the cluster (e.g., the join is performed by the user identifier, which is also a sharding key), ClickHouse provides a way to perform the join without data movement on the network.

When the data isn't co-located, ClickHouse allows a broadcast join, when parts of the joined data are distributed across the nodes of the cluster.

As of 2025, ClickHouse does not perform shuffle joins, meaning neither side of the join is redistributed across the cluster network according to the join keys.

Tip

For information on joins in ClickHouse more generally, see the ["JOIN clause"](/docs/sql-reference/statements/select/join#supported-types-of-join) page.

Was this page helpful?
Tutorials and example datasets

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

Tip

These tutorials work with any ClickHouse deployment, including [ClickHouse Cloud](/docs/getting-started/quick-start/cloud).

We have a lot of resources for helping you get started and learn how ClickHouse works:

- If you need to get ClickHouse up and running, check out our [Quick Start](/docs/get-started/quick-start)
- The [ClickHouse Tutorial](/docs/tutorial) analyzes a dataset of New York City taxi rides

In addition, the sample datasets provide a great experience on working with ClickHouse,
learning important techniques and tricks, and seeing how to take advantage of the many powerful
functions in ClickHouse. The sample datasets include:
Installation instructions

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/getting-started/install/install.mdx)

### ClickHouse Cloud

Start building instantly without having to size and scale your cluster. We take care of sizing, scaling
, security, reliability and upgrades.

Get started free

Recommended

  

Alternatively, select a platform, distribution and install method below to view install
instructions for Opensource ClickHouse:

### ClickHouse CLI

![card icon](/docs/img/DebianUbuntu.svg)

### Debian/Ubuntu

![card icon](/docs/img/redhat.svg)

### Redhat

![card icon](/docs/img/linux.svg)

### Other

![card icon](/docs/img/nixos.svg)

### NixOS

![card icon](/docs/img/apple.png)

### MacOS

![card icon](/docs/img/windows.svg)

### Windows

![card icon](/docs/img/docker.svg)

### Docker

Was this page helpful?
Integrating BigQuery with ClickHouse Cloud

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/bigquery/01_overview.md)

Private preview

Note

You can sign up for the Private Preview waitlist [here](https://clickhouse.com/cloud/clickpipes/bigquery-connector).

The BigQuery ClickPipe provides a fully-managed and resilient way to ingest data from BigQuery into ClickHouse Cloud. In Private Preview, it supports the **initial load** replication method to help you bulk load BigQuery datasets for exploration and prototyping. **CDC** will be supported in the future — in the meantime, we recommend using the [Google Cloud Storage ClickPipe](/docs/integrations/clickpipes/object-storage/gcs/overview) to continuously sync BigQuery data exports into ClickHouse Cloud once the initial load is completed.

BigQuery ClickPipes can be deployed and managed manually using the ClickPipes UI, as well as programmatically using [OpenAPI](https://clickhouse.com/docs/cloud/manage/api/swagger#tag/ClickPipes/paths/~1v1~1organizations~1%7BorganizationId%7D~1services~1%7BserviceId%7D~1clickpipes/post) and [Terraform](https://registry.terraform.io/providers/ClickHouse/clickhouse/3.8.1-alpha1/docs/resources/clickpipe).

## Features[​](#features "Direct link to Features")

### Initial load[​](#initial-load "Direct link to Initial load")

The BigQuery ClickPipe will load selected tables in a BigQuery [dataset](https://docs.cloud.google.com/bigquery/docs/datasets-intro) into the ClickHouse destination tables in a single batch operation. Once the ingestion task completes, the ClickPipe stops automatically. The initial load ingestion process requires a user-provided Google Cloud Storage (GCS) bucket for staging. In the future, the intermediary bucket will be provided and managed by ClickPipes.

Note

ClickPipes relies on batch extract jobs to fetch data from BigQuery into the staging GCS bucket. This operations incurs **no processing charges** in BigQuery.

### CDC (Change Data Capture)[​](#cdc "Direct link to CDC (Change Data Capture)")

CDC is **not supported** in Private Preview, but will be supported in the future. In the meantime, we recommend using the [Google Cloud Storage ClickPipe](/docs/integrations/clickpipes/object-storage/gcs/overview) to continuously sync BigQuery data exports into ClickHouse Cloud once the initial load is completed.

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

[BigQuery data types](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types).
Integrating BigQuery with ClickHouse Cloud

#### BigQuery[​](#bigquery "Direct link to BigQuery")

The service account must have the following BigQuery roles:

- [`roles/bigquery.dataViewer`](https://docs.cloud.google.com/bigquery/docs/access-control#bigquery.dataViewer)
- [`roles/bigquery.jobUser`](https://docs.cloud.google.com/bigquery/docs/access-control#bigquery.jobUser)

To further scope access, we recommend using [IAM conditions](https://docs.cloud.google.com/bigquery/docs/conditions) to restrict the resources the role has access to. For example, you can restrict the `dataViewer` role to the specific dataset containing the tables you want to sync:

```
resource.name.startsWith("projects/<PROJECT_ID>/datasets/<DATASET_NAME>")
```

#### Cloud Storage[​](#cloud-storage "Direct link to Cloud Storage")

The service account must have the following Cloud Storage roles:

- [`roles/storage.objectAdmin`](https://docs.cloud.google.com/storage/docs/access-control/iam-roles#storage.objectAdmin)
- [`roles/storage.bucketViewer`](https://docs.cloud.google.com/storage/docs/access-control/iam-roles#storage.bucketViewer)

To further scope access, we recommend using [IAM conditions](https://docs.cloud.google.com/bigquery/docs/conditions) to restrict the resources the role has access to. For example, you can restrict the `objectAdmin` and `bucketViewer` roles to the dedicated bucket created for ClickPipes syncs.

```
resource.name.startsWith("projects/_/buckets/<BUCKET_NAME>")
```

- [Features](#features)
  - [Initial load](#initial-load)- [CDC (Change Data Capture)](#cdc)- [Data type mapping](#data-type-mapping)- [Access control](#access-control)
      - [Authentication](#authentication)- [Permissions](#permissions)

Was this page helpful?
Integrating Google Cloud Storage with ClickHouse Cloud

### Compatibility[​](#compatibility "Direct link to Compatibility")

The GCS ClickPipe uses on the Cloud Storage [XML API](https://docs.cloud.google.com/storage/docs/interoperability) for interoperability, which requires using the `https://storage.googleapis.com/` bucket prefix (instead of `gs://`) and using [HMAC keys](https://docs.cloud.google.com/storage/docs/authentication/hmackeys) for authentication.

### View support[​](#view-support "Direct link to View support")

Materialized views on the target table are also supported. ClickPipes will create staging tables not only for the target table, but also any dependent materialized view.

We don't create staging tables for non-materialized views. This means that if you have a target table with one of more downstream materialized views, those materialized views should avoid selecting data via a view from the target table. Otherwise, you may find that you're missing data in the materialized view.

- [Supported formats](#supported-formats)- [Features](#features)
    - [One-time ingestion](#one-time-ingestion)- [Continuous ingestion](#continuous-ingestion)- [File pattern matching](#file-pattern-matching)- [Exactly-once semantics](#exactly-once-semantics)- [Virtual columns](#virtual-columns)- [Access control](#access-control)
      - [Permissions](#permissions)- [Authentication](#authentication)- [Network access](#network-access)- [Advanced settings](#advanced-settings)
        - [Scaling](#scaling)- [Known limitations](#known-limitations)
          - [File size](#file-size)- [Compatibility](#compatibility)- [View support](#view-support)

Was this page helpful?
Configuring unordered mode for continuous ingestion

Create a ClickPipe with unordered mode

## Create a ClickPipe with unordered mode[​](#create-clickpipe "Direct link to Create a ClickPipe with unordered mode")

**1.** In the ClickHouse Cloud console, navigate to **Data Sources > Create ClickPipe** and select **Amazon S3**. Enter the details to connect to your S3 bucket. Under **Authentication method**, choose **IAM role** and provide the ARN of the role you created in the previous step.

**2.** Under **Incoming data**, toggle on **Continuous ingestion**. Select **Any order** as the ingestion mode and provide the **SQS queue URL** for the queue connected to your bucket.

**3.** Under **Parse information**, define a **Sorting key** for the target table. Make any necessary adjustments to the mapped schema, then configure a role for the ClickPipes database user.

**4.** Review the configuration and click **Create ClickPipe**. ClickPipes will perform an initial scan of your bucket to load all existing files that match the specified path, and will then begin processing files as new `ObjectCreated:*` events arrive in the queue.

- [How it works](#how-it-works)- [Create an Amazon SQS queue](#create-sqs-queue)- [Configure an IAM role](#configure-iam-role)- [Create a ClickPipe with unordered mode](#create-clickpipe)

Was this page helpful?
ClickHouse C# client

## Limitations[​](#limitations "Direct link to Limitations")

### AggregateFunction columns[​](#aggregatefunction-columns "Direct link to AggregateFunction columns")

Columns of type `AggregateFunction(...)` can't be queried or inserted directly.

To insert:

```
INSERT INTO t VALUES (uniqState(1));
```

To select:

```
SELECT uniqMerge(c) FROM t;
```

---

- [Migration guide](#migration-guide)- [Supported .NET versions](#supported-net-versions)- [Installation](#installation)- [Quick start](#quick-start)- [Configuration](#configuration)
          - [Connection settings](#connection-settings)- [Data format & serialization](#data-format-serialization)- [Session management](#session-management)- [Security](#security)- [HTTP client configuration](#http-client-configuration)- [Logging & debugging](#logging-debugging)- [Custom settings & roles](#custom-settings-roles)- [Connection string examples](#connection-string-examples)- [QueryOptions](#query-options)- [InsertOptions](#insert-options)- [ClickHouseClient](#clickhouse-client)
            - [Creating a client](#creating-a-client)- [Executing queries](#executing-queries)- [Inserting data](#inserting-data)- [Reading data](#reading-data)- [SQL parameters](#sql-parameters)- [Query ID](#query-id)- [Raw streaming](#raw-streaming)- [Raw stream insert](#raw-stream-insert)- [More examples](#more-examples)- [ADO.NET](#ado-net)
              - [Lifetime management with ClickHouseDataSource](#ado-net-datasource)- [Using ClickHouseCommand](#ado-net-command)- [Using ClickHouseDataReader](#ado-net-reader)- [Best practices](#best-practices)
                - [Connection lifetime and pooling](#best-practices-connection-lifetime)- [DateTime handling](#best-practice-datetime)- [Async inserts](#async-inserts)- [Sessions](#best-practices-sessions)- [Supported data types](#supported-data-types)
                  - [Type mapping: reading from ClickHouse](#clickhouse-native-type-map-reading)- [Type mapping: writing to ClickHouse](#clickhouse-native-type-map-writing)- [Nested type handling](#nested-type-handling)- [Logging and diagnostics](#logging-and-diagnostics)
                    - [Quick start](#logging-quick-start)- [Categories and emitters](#logging-categories)- [Debug mode: network tracing and diagnostics](#logging-debugmode)- [OpenTelemetry](#opentelemetry)
                      - [Enabling tracing](#opentelemetry-enabling)- [Span attributes](#opentelemetry-attributes)- [Configuration options](#opentelemetry-configuration)- [TLS configuration](#tls-configuration)
                        - [Custom certificate validation](#custom-certificate-validation)- [ORM support](#orm-support)
                          - [Dapper](#orm-support-dapper)- [Linq2db](#orm-support-linq2db)- [Entity Framework Core](#orm-support-ef-core)- [Limitations](#limitations)
                            - [AggregateFunction columns](#aggregatefunction-columns)

Was this page helpful?
Dot

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

Community Maintained

[Dot](https://www.getdot.ai/) is your **AI Data Analyst**.
It connects directly to ClickHouse so you can ask data questions in natural language, discover data, test hypotheses, and answer why questions — directly in Slack, Microsoft Teams, ChatGPT or the native Web UI.

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

- A ClickHouse database, either self-hosted or in [ClickHouse Cloud](https://clickhouse.com/cloud)
- A [Dot](https://www.getdot.ai/) account

## Connecting Dot to ClickHouse[​](#connecting-dot-to-clickhouse "Direct link to Connecting Dot to ClickHouse")

  

1. In the Dot UI, go to **Settings → Connections**.
2. Click on **Add new connection** and select **ClickHouse**.
3. Provide your connection details:
   - **Host**: ClickHouse server hostname or ClickHouse Cloud endpoint
   - **Port**: `8443` (ClickHouse Cloud HTTPS) or `8123` (self-hosted HTTP)
   - **Username / Password**: user with read access
   - **Database**: optionally set a default schema
4. Click **Connect**.

Dot uses **query-pushdown**: ClickHouse handles the heavy number-crunching at scale, while Dot ensures correct and trusted answers.

## Highlights[​](#highlights "Direct link to Highlights")

Dot makes data accessible through conversation:

- **Ask in natural language**: Get answers without writing SQL.
- **Why analysis**: Ask follow-up questions to understand trends and anomalies.
- **Works where you work**: Slack, Microsoft Teams, ChatGPT, or the web app.
- **Trusted results**: Dot validates queries against your schemas and definitions to minimize errors.
- **Scalable**: Built on query-pushdown, pairing Dot’s intelligence with ClickHouse’s speed.

## Security and governance[​](#security "Direct link to Security and governance")

Dot is enterprise-ready:

- **Permissions & roles**: Inherits ClickHouse user access controls
- **Row-level security**: Supported if configured in ClickHouse
- **TLS / SSL**: Enabled by default for ClickHouse Cloud; configure manually for self-hosted
- **Governance & validation**: Training/validation space helps prevent hallucinations
- **Compliance**: SOC 2 Type I certified

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

- Dot website: <https://www.getdot.ai/>
- Documentation: <https://docs.getdot.ai/>
- Dot app: <https://app.getdot.ai/>

Now you can use **ClickHouse + Dot** to analyze your data conversationally — combining Dot’s AI assistant with ClickHouse’s fast, scalable analytics engine.

- [Pre-requisites](#pre-requisites)- [Connecting Dot to ClickHouse](#connecting-dot-to-clickhouse)- [Highlights](#highlights)- [Security and governance](#security)- [Additional resources](#additional-resources)

Was this page helpful?