ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

`_shard_num` — Contains the `shard_num` value from the table `system.clusters`. Type: [UInt32](/docs/sql-reference/data-types/int-uint).

Note

Since [`remote`](/docs/sql-reference/table-functions/remote) and [`cluster](../../../sql-reference/table-functions/cluster.md) table functions internally create temporary Distributed table,` \_shard\_num` is available there too.

**See Also**

- [Virtual columns](/docs/engines/table-engines#table_engines-virtual_columns) description
- [`background_distributed_schedule_pool_size`](/docs/operations/server-configuration-parameters/settings#background_distributed_schedule_pool_size) setting
- [`shardNum()`](/docs/sql-reference/functions/other-functions#shardNum) and [`shardCount()`](/docs/sql-reference/functions/other-functions#shardCount) functions

- [Creating a table](#distributed-creating-a-table)
  - [From a table](#distributed-from-a-table)- [Distributed parameters](#distributed-parameters)- [Distributed settings](#distributed-settings)- [Clusters](#distributed-clusters)- [Writing data](#distributed-writing-data)- [Reading data](#distributed-reading-data)- [Virtual columns](#virtual-columns)

Was this page helpful?
title_wip

- `pool_size`
  - Description: Processes pool size. If size is 0, then there are no size restrictions
  - Default value: 16
- `max_command_execution_time`
  - Description: Max command execution time in seconds
  - Default value: 10

We can easily convert the `sentiment` table above to use `ExecutablePool` instead of `Executable`:

```
CREATE TABLE sentiment_pooled (
   id UInt64,
   sentiment Float32
)
ENGINE = ExecutablePool(
    'sentiment.py',
    TabSeparated,
    (SELECT id, comment FROM hackernews WHERE id > 0 AND comment != '' LIMIT 20000)
)
SETTINGS
    pool_size = 4;
```

ClickHouse will maintain 4 processes on-demand when your client queries the `sentiment_pooled` table.

- [Creating an `Executable` table](#creating-an-executable-table)- [Passing query results to a script](#passing-query-results-to-a-script)- [Creating an `ExecutablePool` table](#creating-an-executablepool-table)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/special/generate.md)

The GenerateRandom table engine produces random data for given table schema.

Usage examples:

- Use in test to populate reproducible large table.
- Generate random input for fuzzing tests.

## Usage in ClickHouse Server[​](#usage-in-clickhouse-server "Direct link to Usage in ClickHouse Server")

```
ENGINE = GenerateRandom([random_seed [,max_string_length [,max_array_length]]])
```

The `max_array_length` and `max_string_length` parameters specify maximum length of all
array or map columns and strings correspondingly in generated data.

Generate table engine supports only `SELECT` queries.

It supports all [DataTypes](/docs/sql-reference/data-types) that can be stored in a table except `AggregateFunction`.

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

**1.** Set up the `generate_engine_table` table:

```
CREATE TABLE generate_engine_table (name String, value UInt32) ENGINE = GenerateRandom(1, 5, 3)
```

**2.** Query the data:

```
SELECT * FROM generate_engine_table LIMIT 3
```

```
┌─name─┬──────value─┐
│ c4xJ │ 1412771199 │
│ r    │ 1791099446 │
│ 7#$  │  124312908 │
└──────┴────────────┘
```

## Details of Implementation[​](#details-of-implementation "Direct link to Details of Implementation")

- Not supported:
  - `ALTER`
  - `SELECT ... SAMPLE`
  - `INSERT`
  - Indices
  - Replication

- [Usage in ClickHouse Server](#usage-in-clickhouse-server)- [Example](#example)- [Details of Implementation](#details-of-implementation)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/special/null.md)

When writing data to a `Null` table, data is ignored.
When reading from a `Null` table, the response is empty.

The `Null` table engine is useful for data transformations where you no longer need the original data after it has been transformed.
For this purpose you can create a materialized view on a `Null` table.
The data written to the table will be consumed by the view, but the original raw data will be discarded.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/special/set.md)

Note

In ClickHouse Cloud, if your service was created with a version earlier than 25.4, you will need to set the compatibility to at least 25.4 using `SET compatibility=25.4`.

A data set that is always in RAM. It is intended for use on the right side of the `IN` operator (see the section "IN operators").

You can use `INSERT` to insert data in the table. New elements will be added to the data set, while duplicates will be ignored.
But you can't perform `SELECT` from the table. The only way to retrieve data is by using it in the right half of the `IN` operator.

Data is always located in RAM. For `INSERT`, the blocks of inserted data are also written to the directory of tables on the disk. When starting the server, this data is loaded to RAM. In other words, after restarting, the data remains in place.

For a rough server restart, the block of data on the disk might be lost or damaged. In the latter case, you may need to manually delete the file with damaged data.

### Limitations and settings[​](#join-limitations-and-settings "Direct link to Limitations and settings")

When creating a table, the following settings are applied:

#### Persistent[​](#persistent "Direct link to Persistent")

Disables persistency for the Set and [Join](/docs/engines/table-engines/special/join) table engines.

Reduces the I/O overhead. Suitable for scenarios that pursue performance and do not require persistence.

Possible values:

- 1 — Enabled.
- 0 — Disabled.

Default value: `1`.

- [Limitations and settings](#join-limitations-and-settings)

Was this page helpful?
title_wip

## External target tables[​](#external-target-tables "Direct link to External target tables")

It's possible to make a `TimeSeries` table use a manually created table:

```
CREATE TABLE data_for_my_table
(
    `id` UUID,
    `timestamp` DateTime64(3),
    `value` Float64
)
ENGINE = MergeTree
ORDER BY (id, timestamp);

CREATE TABLE tags_for_my_table ...

CREATE TABLE metrics_for_my_table ...

CREATE TABLE my_table ENGINE=TimeSeries DATA data_for_my_table TAGS tags_for_my_table METRICS metrics_for_my_table;
```

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

Here is a list of settings which can be specified while defining a `TimeSeries` table:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Name Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `tags_to_columns` Map Map specifying which tags should be put to separate columns in the [tags](#tags-table) table. Syntax: `{'tag1': 'column1', 'tag2' : column2, ...}`| `use_all_tags_column_to_generate_id` Bool true When generating an expression to calculate an identifier of a time series, this flag enables using the `all_tags` column in that calculation| `store_min_time_and_max_time` Bool true If set to true then the table will store `min_time` and `max_time` for each time series| `aggregate_min_time_and_max_time` Bool true When creating an inner target `tags` table, this flag enables using `SimpleAggregateFunction(min, Nullable(DateTime64(3)))` instead of just `Nullable(DateTime64(3))` as the type of the `min_time` column, and the same for the `max_time` column| `filter_by_min_time_and_max_time` Bool true If set to true then the table will use the `min_time` and `max_time` columns for filtering time series | | | | | | | | | | | | | | | | | | | | | | | |

# Functions

Here is a list of functions supporting a `TimeSeries` table as an argument:

- [timeSeriesData](/docs/sql-reference/table-functions/timeSeriesData)
- [timeSeriesTags](/docs/sql-reference/table-functions/timeSeriesTags)
- [timeSeriesMetrics](/docs/sql-reference/table-functions/timeSeriesMetrics)

- [Syntax](#syntax)- [Usage](#usage)- [Target tables](#target-tables)
      - [Data table](#data-table)- [Tags table](#tags-table)- [Metrics table](#metrics-table)- [Creation](#creation)- [Adjusting types of columns](#adjusting-column-types)- [The `id` column](#id-column)- [The `tags` and `all_tags` columns](#tags-and-all-tags)- [Table engines of inner target tables](#inner-table-engines)- [External target tables](#external-target-tables)- [Settings](#settings)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/special/view.md)

Used for implementing views (for more information, see the `CREATE VIEW query`). It does not store data, but only stores the specified `SELECT` query. When reading from a table, it runs this query (and deletes all unnecessary columns from the query).

Was this page helpful?
title_wip

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

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

The [`If`](/docs/sql-reference/aggregate-functions/combinators#-if) combinator can be applied to the [`any`](/docs/sql-reference/aggregate-functions/reference/any)
aggregate function to select the first encountered element from a given column
that matches the given condition.

## 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 `anyIf` to select the first `transaction_id`s which are above and
below an amount of 200.

We first create a table and insert data into it:

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

INSERT INTO sales VALUES
    (1, 100.00, 1),
    (2, 150.00, 1),
    (3, 155.00, 0),
    (4, 300.00, 1),
    (5, 250.50, 0),
    (6, 175.25, 1);
```

```
SELECT
    anyIf(transaction_id, amount < 200) AS tid_lt_200,
    anyIf(transaction_id, amount > 200) AS tid_gt_200
FROM sales;
```

```
┌─tid_lt_200─┬─tid_gt_200─┐
│          1 │          4 │
└────────────┴────────────┘
```

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

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

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

Was this page helpful?
title_wip

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

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

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

The `argMaxIf` function is useful when you need to find the value associated with
the maximum 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 use a sample dataset of product sales to demonstrate how
`argMaxIf` works. We'll find the product name that has the highest price, but
only for products that have been sold at least 10 times.

```
CREATE TABLE product_sales
(
    product_name String,
    price Decimal32(2),
    sales_count UInt32
) ENGINE = Memory;

INSERT INTO product_sales VALUES
    ('Laptop', 999.99, 10),
    ('Phone', 499.99, 15),
    ('Tablet', 299.99, 0),
    ('Watch', 1199.99, 5),
    ('Headphones', 79.99, 20);

SELECT argMaxIf(product_name, price, sales_count >= 10) AS most_expensive_popular_product
FROM product_sales;
```

The `argMaxIf` function will return the product name that has the highest price
among all products that have been sold at least 10 times (sales\_count >= 10).
In this case, it will return 'Laptop' since it has the highest price (999.99)
among the popular products.

```
   ┌─most_expensi⋯lar_product─┐
1. │ Laptop                   │
   └──────────────────────────┘
```

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

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

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

Was this page helpful?
title_wip

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

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

The [`Merge`](/docs/sql-reference/aggregate-functions/combinators#-state) combinator
can be applied to the [`avg`](/docs/sql-reference/aggregate-functions/reference/avg)
function to produce a final result by combining partial aggregate states.

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

The `Merge` combinator is closely related to the `State` combinator. Refer to
["avgState example usage"](/docs/examples/aggregate-function-combinators/avgState#example-usage)
for an example of both `avgMerge` and `avgState`.

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

- [`avg`](/docs/sql-reference/aggregate-functions/reference/avg)
- [`Merge`](/docs/sql-reference/aggregate-functions/combinators#-merge)
- [`MergeState`](/docs/sql-reference/aggregate-functions/combinators#-mergestate)

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

Was this page helpful?
title_wip

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

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

The [`Resample`](/docs/sql-reference/aggregate-functions/combinators#-resample)
combinator can be applied to the [`count`](/docs/sql-reference/aggregate-functions/reference/count)
aggregate function to count values of a specified key column in a fixed number
of intervals (`N`).

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

### Basic example[​](#basic-example "Direct link to Basic example")

Let's look at an example. We'll create a table which contains the `name`, `age` and
`wage` of employees, and we'll insert some data into it:

```
CREATE TABLE employee_data 
(
    name String,
    age UInt8,
    wage Float32
) 
ENGINE = MergeTree()
ORDER BY tuple()

INSERT INTO employee_data (name, age, wage) VALUES
    ('John', 16, 10.0),
    ('Alice', 30, 15.0),
    ('Mary', 35, 8.0),
    ('Evelyn', 48, 11.5),
    ('David', 62, 9.9),
    ('Brian', 60, 16.0);
```

Let's get the average wage of the people whose age lies in the intervals of `[30,60)`
and `[60,75)` (`[` is exclusive and `)` is inclusive). Since we use integer
representation for age, we get ages in the intervals `[30, 59]` and `[60,74]`.
To do so we apply the `Resample` combinator to the `avg` aggregate function.

```
WITH avg_wage AS
(
    SELECT avgResample(30, 75, 30)(wage, age) AS original_avg_wage
    FROM employee_data
)
SELECT
    arrayMap(x -> round(x, 3), original_avg_wage) AS avg_wage_rounded
FROM avg_wage;
```

```
┌─avg_wage_rounded─┐
│ [11.5,12.95]     │
└──────────────────┘
```

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

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

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

Was this page helpful?
title_wip

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

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

The [`Resample`](/docs/sql-reference/aggregate-functions/combinators#-resample)
combinator can be applied to the [`count`](/docs/sql-reference/aggregate-functions/reference/count)
aggregate function to count values of a specified key column in a fixed number
of intervals (`N`).

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

### Basic example[​](#basic-example "Direct link to Basic example")

Let's look at an example. We'll create a table which contains the `name`, `age` and
`wage` of employees, and we'll insert some data into it:

```
CREATE TABLE employee_data 
(
    name String,
    age UInt8,
    wage Float32
) 
ENGINE = MergeTree()
ORDER BY tuple()

INSERT INTO employee_data (name, age, wage) VALUES
    ('John', 16, 10.0),
    ('Alice', 30, 15.0),
    ('Mary', 35, 8.0),
    ('Evelyn', 48, 11.5),
    ('David', 62, 9.9),
    ('Brian', 60, 16.0);
```

Let's count all the people whose age lies in the intervals of `[30,60)`
and `[60,75)`. Since we use integer representation for age, we get ages in the
`[30, 59]` and `[60,74]` intervals. To do so we apply the `Resample` combinator
to `count`

```
SELECT countResample(30, 75, 30)(name, age) AS amount FROM employee_data
```

```
┌─amount─┐
│ [3,2]  │
└────────┘
```

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

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

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

Was this page helpful?
title_wip

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

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

The [`groupArrayDistinct`](/docs/sql-reference/aggregate-functions/combinators#-foreach) combinator
can be applied to the [`groupArray`](/docs/sql-reference/aggregate-functions/reference/sum) aggregate function to create an array
of distinct argument values.

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

For this example we'll make use of the `hits` dataset available in our [SQL playground](https://sql.clickhouse.com/).

Imagine you want to find out, for each distinct landing page domain (`URLDomain`)
on your website, what are all the unique User Agent OS codes (`OS`) recorded for
visitors landing on that domain. This could help you understand the variety of
operating systems interacting with different parts of your site.

```
SELECT
    URLDomain,
    groupArrayDistinct(OS) AS distinct_os_codes
FROM metrica.hits_v1
WHERE URLDomain != '' -- Consider only hits with a recorded domain
GROUP BY URLDomain
ORDER BY URLDomain ASC
LIMIT 20;
```

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

- [`groupArray`](/docs/sql-reference/aggregate-functions/reference/grouparray)
- [`Distinct combinator`](/docs/sql-reference/aggregate-functions/combinators#-distinct)

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

Was this page helpful?
title_wip

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

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

The [`Resample`](/docs/sql-reference/aggregate-functions/combinators#-resample)
combinator can be applied to the [`groupArray`](/docs/sql-reference/aggregate-functions/reference/sum) aggregate function to
divide the range of a specified key column into a fixed number of intervals (`N`)
and construct the resulting array by selecting one representative value
(corresponding to the minimum key) from the data points falling into each interval.
It creates a downsampled view of the data rather than collecting all values.

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

Let's look at an example. We'll create a table which contains the `name`, `age` and
`wage` of employees, and we'll insert some data into it:

```
CREATE TABLE employee_data 
(
    name String,
    age UInt8,
    wage Float32
) ENGINE = MergeTree()
ORDER BY tuple()

INSERT INTO employee_data (name, age, wage) VALUES
    ('John', 16, 10.0),
    ('Alice', 30, 15.0),
    ('Mary', 35, 8.0),
    ('Evelyn', 48, 11.5),
    ('David', 62, 9.9),
    ('Brian', 60, 16.0);
```

Let's get the names of the people whose age lies in the intervals of `[30,60)`
and `[60,75)`. Since we use integer representation for age, we get ages in the
`[30, 59]` and `[60,74]` intervals.

To aggregate names in an array, we use the `groupArray` aggregate function.
It takes one argument. In our case, it's the name column. The `groupArrayResample`
function should use the age column to aggregate names by age. To define the
required intervals, we pass `30`, `75`, `30` as arguments into the `groupArrayResample`
function:

```
SELECT groupArrayResample(30, 75, 30)(name, age) FROM employee_data
```

```
┌─groupArrayResample(30, 75, 30)(name, age)─────┐
│ [['Alice','Mary','Evelyn'],['David','Brian']] │
└───────────────────────────────────────────────┘
```

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

- [`groupArray`](/docs/sql-reference/aggregate-functions/reference/grouparray)
- [`Resample combinator`](/docs/sql-reference/aggregate-functions/combinators#-resample)

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

Was this page helpful?
title_wip

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

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

The [`SimpleState`](/docs/sql-reference/aggregate-functions/combinators#-simplestate) combinator can be applied to the [`max`](/docs/sql-reference/aggregate-functions/reference/max)
function to return the maximum value across all input values. It returns the
result with type `SimpleAggregateState`.

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

The example given in [`minSimpleState`](/docs/examples/aggregate-function-combinators/minSimpleState#example-usage)
demonstrates a usage of both `maxSimpleState` and `minSimpleState`.

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

- [`max`](/docs/sql-reference/aggregate-functions/reference/max)
- [`SimpleState combinator`](/docs/sql-reference/aggregate-functions/combinators#-simplestate)
- [`SimpleAggregateFunction type`](/docs/sql-reference/data-types/simpleaggregatefunction)

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

Was this page helpful?