ClickHouse/clickhouse-docs-embeddings huggingface dataset:
Introspection functions

│ 626293730 │ ['./build_normal_debug/./src/Processors/Executors/ExecutionThreadContext.cpp:63']                                                                                                                                        │
│ 626169525 │ ['./build_normal_debug/./src/Processors/Executors/PipelineExecutor.cpp:213']                                                                                                                                             │
│ 626170308 │ ['./build_normal_debug/./src/Processors/Executors/PipelineExecutor.cpp:178']                                                                                                                                             │
│ 626166348 │ ['./build_normal_debug/./src/Processors/Executors/PipelineExecutor.cpp:329']                                                                                                                                             │
│ 626163461 │ ['./build_normal_debug/./src/Processors/Executors/PipelineExecutor.cpp:84']                                                                                                                                              │
│ 626323536 │ ['./build_normal_debug/./src/Processors/Executors/PullingAsyncPipelineExecutor.cpp:85']                                                                                                                                  │
│ 626323277 │ ['./build_normal_debug/./src/Processors/Executors/PullingAsyncPipelineExecutor.cpp:112']                                                                                                                                 │
│ 626323133 │ ['./build_normal_debug/./contrib/libcxx/include/type_traits:3682']                                                                                                                                                       │
│ 626323041 │ ['./build_normal_debug/./contrib/libcxx/include/tuple:1415']                                                                                                                                                             │
└───────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
Functions for generating random numbers

Generates a random [UTF-8](https://en.wikipedia.org/wiki/UTF-8) string with the specified number of codepoints.
No codepoints from unassigned [planes](https://en.wikipedia.org/wiki/Plane_(Unicode)) (planes 4 to 13) are returned.
It is still possible that the client interacting with ClickHouse server is not able to display the produced UTF-8 string correctly.

**Syntax**

```
randomStringUTF8(length)
```

**Arguments**

- `length` — Length of the string in code points. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns a string filled with random UTF-8 codepoints. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Usage example**

```
SELECT randomStringUTF8(13)
```

```
┌─randomStringUTF8(13)─┐
│ 𘤗𙉝д兠庇󡅴󱱎󦐪􂕌𔊹𓰛       │
└──────────────────────┘
```

- [fuzzBits](#fuzzBits)- [rand](#rand)- [rand64](#rand64)- [randBernoulli](#randBernoulli)- [randBinomial](#randBinomial)- [randCanonical](#randCanonical)- [randChiSquared](#randChiSquared)- [randConstant](#randConstant)- [randExponential](#randExponential)- [randFisherF](#randFisherF)- [randLogNormal](#randLogNormal)- [randNegativeBinomial](#randNegativeBinomial)- [randNormal](#randNormal)- [randPoisson](#randPoisson)- [randStudentT](#randStudentT)- [randUniform](#randUniform)- [randomFixedString](#randomFixedString)- [randomPrintableASCII](#randomPrintableASCII)- [randomString](#randomString)- [randomStringUTF8](#randomStringUTF8)

Was this page helpful?
Tuple functions

**Syntax**

```
tupleToNameValuePairs(tuple)
```

**Arguments**

- `tuple` — Named tuple with any types of values. [`Tuple(n1 T1[, n2 T2, ...])`](/docs/sql-reference/data-types/tuple)

**Returned value**

Returns an array with `(name, value)` pairs. [`Array(Tuple(String, T))`](/docs/sql-reference/data-types/array)

**Examples**

**Named tuple**

```
SELECT tupleToNameValuePairs(tuple(1593 AS user_ID, 2502 AS session_ID))
```

```
[('1', 1593), ('2', 2502)]
```

**Unnamed tuple**

```
SELECT tupleToNameValuePairs(tuple(3, 2, 1))
```

```
[('1', 3), ('2', 2), ('3', 1)]
```

## untuple[​](#untuple "Direct link to untuple")

Performs syntactic substitution of [tuple](/docs/sql-reference/data-types/tuple) elements in the call location.

The names of the result columns are implementation-specific and subject to change. Do not assume specific column names after `untuple`.

**Syntax**

```
untuple(x)
```

You can use the `EXCEPT` expression to skip columns as a result of the query.

**Arguments**

- `x` — A `tuple` function, column, or tuple of elements. [Tuple](/docs/sql-reference/data-types/tuple).

**Returned value**

- None.

**Examples**

Input table:

```
┌─key─┬─v1─┬─v2─┬─v3─┬─v4─┬─v5─┬─v6────────┐
│   1 │ 10 │ 20 │ 40 │ 30 │ 15 │ (33,'ab') │
│   2 │ 25 │ 65 │ 70 │ 40 │  6 │ (44,'cd') │
│   3 │ 57 │ 30 │ 20 │ 10 │  5 │ (55,'ef') │
│   4 │ 55 │ 12 │  7 │ 80 │ 90 │ (66,'gh') │
│   5 │ 30 │ 50 │ 70 │ 25 │ 55 │ (77,'kl') │
└─────┴────┴────┴────┴────┴────┴───────────┘
```

Example of using a `Tuple`-type column as the `untuple` function parameter:

Query:

```
SELECT untuple(v6) FROM kv;
```

Result:

```
┌─_ut_1─┬─_ut_2─┐
│    33 │ ab    │
│    44 │ cd    │
│    55 │ ef    │
│    66 │ gh    │
│    77 │ kl    │
└───────┴───────┘
```

Example of using an `EXCEPT` expression:

Query:

```
SELECT untuple((* EXCEPT (v2, v3),)) FROM kv;
```

Result:

```
┌─key─┬─v1─┬─v4─┬─v5─┬─v6────────┐
│   1 │ 10 │ 30 │ 15 │ (33,'ab') │
│   2 │ 25 │ 40 │  6 │ (44,'cd') │
│   3 │ 57 │ 10 │  5 │ (55,'ef') │
│   4 │ 55 │ 80 │ 90 │ (66,'gh') │
│   5 │ 30 │ 25 │ 55 │ (77,'kl') │
└─────┴────┴────┴────┴───────────┘
```

## Distance functions[​](#distance-functions "Direct link to Distance functions")

All supported functions are described in [distance functions documentation](/docs/sql-reference/functions/distance-functions).

- [dotProduct](#dotProduct)- [flattenTuple](#flattenTuple)- [tuple](#tuple)- [tupleConcat](#tupleConcat)- [tupleDivide](#tupleDivide)- [tupleDivideByNumber](#tupleDivideByNumber)- [tupleElement](#tupleElement)- [tupleHammingDistance](#tupleHammingDistance)- [tupleIntDiv](#tupleIntDiv)- [tupleIntDivByNumber](#tupleIntDivByNumber)- [tupleIntDivOrZero](#tupleIntDivOrZero)- [tupleIntDivOrZeroByNumber](#tupleIntDivOrZeroByNumber)- [tupleMinus](#tupleMinus)- [tupleModulo](#tupleModulo)- [tupleModuloByNumber](#tupleModuloByNumber)- [tupleMultiply](#tupleMultiply)- [tupleMultiplyByNumber](#tupleMultiplyByNumber)- [tupleNames](#tupleNames)- [tupleNegate](#tupleNegate)- [tuplePlus](#tuplePlus)- [tupleToNameValuePairs](#tupleToNameValuePairs)- [untuple](#untuple)- [Distance functions](#distance-functions)

Was this page helpful?
Functions for working with ULIDs

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/ulid-functions.md)

Note

The documentation below is generated from the `system.functions` system table.

## ULIDStringToDateTime[​](#ULIDStringToDateTime "Direct link to ULIDStringToDateTime")

Introduced in: v23.3.0

This function extracts the timestamp from a [ULID](https://github.com/ulid/spec).

**Syntax**

```
ULIDStringToDateTime(ulid[, timezone])
```

**Arguments**

- `ulid` — Input ULID. [`String`](/docs/sql-reference/data-types/string) or [`FixedString(26)`](/docs/sql-reference/data-types/fixedstring)
- `timezone` — Optional. Timezone name for the returned value. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Timestamp with milliseconds precision. [`DateTime64(3)`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Usage example**

```
SELECT ULIDStringToDateTime('01GNB2S2FGN2P93QPXDNB4EN2R')
```

```
┌─ULIDStringToDateTime('01GNB2S2FGN2P93QPXDNB4EN2R')─┐
│                            2022-12-28 00:40:37.616 │
└────────────────────────────────────────────────────┘
```

## generateULID[​](#generateULID "Direct link to generateULID")

Introduced in: v23.2.0

Generates a [Universally Unique Lexicographically Sortable Identifier (ULID)](https://github.com/ulid/spec).

**Syntax**

```
generateULID([x])
```

**Arguments**

- `x` — Optional. An expression resulting in any of the supported data types. The resulting value is discarded, but the expression itself if used for bypassing [common subexpression elimination](/docs/sql-reference/functions/overview#common-subexpression-elimination) if the function is called multiple times in one query. [`Any`](/docs/sql-reference/data-types)

**Returned value**

Returns a ULID. [`FixedString(26)`](/docs/sql-reference/data-types/fixedstring)

**Examples**

**Usage example**

```
SELECT generateULID()
```

```
┌─generateULID()─────────────┐
│ 01GNB2S2FGN2P93QPXDNB4EN2R │
└────────────────────────────┘
```

**Usage example if it is needed to generate multiple values in one row**

```
SELECT generateULID(1), generateULID(2)
```

```
┌─generateULID(1)────────────┬─generateULID(2)────────────┐
│ 01GNB2SGG4RHKVNT9ZGA4FFMNP │ 01GNB2SGG4V0HMQVH4VBVPSSRB │
└────────────────────────────┴────────────────────────────┘
```

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

- [UUID](/docs/sql-reference/functions/uuid-functions)

- [ULIDStringToDateTime](#ULIDStringToDateTime)- [generateULID](#generateULID)- [See also](#see-also)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/functions/uniqtheta-functions.md)

uniqTheta functions work for two uniqThetaSketch objects to do set operation calculations such as ∪ / ∩ / × (union/intersect/not), it is to return a new uniqThetaSketch object contain the result.

A uniqThetaSketch object is to be constructed by aggregation function uniqTheta with -State.

UniqThetaSketch is a data structure storage of approximate values set.
For more information, see: [Theta Sketch Framework](https://datasketches.apache.org/docs/Theta/ThetaSketches.html#theta-sketch-framework).

## uniqThetaUnion[​](#uniqthetaunion "Direct link to uniqThetaUnion")

Two uniqThetaSketch objects to do union calculation(set operation ∪), the result is a new uniqThetaSketch.

```
uniqThetaUnion(uniqThetaSketch,uniqThetaSketch)
```

**Arguments**

- `uniqThetaSketch` – uniqThetaSketch object.

**Example**

```
SELECT finalizeAggregation(uniqThetaUnion(a, b)) AS a_union_b, finalizeAggregation(a) AS a_cardinality, finalizeAggregation(b) AS b_cardinality
FROM
(SELECT arrayReduce('uniqThetaState',[1,2]) AS a, arrayReduce('uniqThetaState',[2,3,4]) AS b );
```

```
┌─a_union_b─┬─a_cardinality─┬─b_cardinality─┐
│         4 │             2 │             3 │
└───────────┴───────────────┴───────────────┘
```

## uniqThetaIntersect[​](#uniqthetaintersect "Direct link to uniqThetaIntersect")

Two uniqThetaSketch objects to do intersect calculation(set operation ∩), the result is a new uniqThetaSketch.

```
uniqThetaIntersect(uniqThetaSketch,uniqThetaSketch)
```

**Arguments**

- `uniqThetaSketch` – uniqThetaSketch object.

**Example**

```
SELECT finalizeAggregation(uniqThetaIntersect(a, b)) AS a_intersect_b, finalizeAggregation(a) AS a_cardinality, finalizeAggregation(b) AS b_cardinality
FROM
(SELECT arrayReduce('uniqThetaState',[1,2]) AS a, arrayReduce('uniqThetaState',[2,3,4]) AS b );
```

```
┌─a_intersect_b─┬─a_cardinality─┬─b_cardinality─┐
│             1 │             2 │             3 │
└───────────────┴───────────────┴───────────────┘
```

## uniqThetaNot[​](#uniqthetanot "Direct link to uniqThetaNot")

Two uniqThetaSketch objects to do a\_not\_b calculation(set operation ×), the result is a new uniqThetaSketch.

```
uniqThetaNot(uniqThetaSketch,uniqThetaSketch)
```

**Arguments**

- `uniqThetaSketch` – uniqThetaSketch object.

**Example**

```
SELECT finalizeAggregation(uniqThetaNot(a, b)) AS a_not_b, finalizeAggregation(a) AS a_cardinality, finalizeAggregation(b) AS b_cardinality
FROM
(SELECT arrayReduce('uniqThetaState',[2,3,4]) AS a, arrayReduce('uniqThetaState',[1,2]) AS b );
```

```
┌─a_not_b─┬─a_cardinality─┬─b_cardinality─┐
│       2 │             3 │             2 │
└─────────┴───────────────┴───────────────┘
```

**See Also**

- [uniqThetaSketch](/docs/sql-reference/aggregate-functions/reference/uniqthetasketch)

- [uniqThetaUnion](#uniqthetaunion)- [uniqThetaIntersect](#uniqthetaintersect)- [uniqThetaNot](#uniqthetanot)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/operators/exists.md)

The `EXISTS` operator checks how many records are in the result of a subquery. If it is empty, then the operator returns `0`. Otherwise, it returns `1`.

`EXISTS` can also be used in a [WHERE](/docs/sql-reference/statements/select/where) clause.

Tip

References to main query tables and columns are not supported in a subquery.

**Syntax**

```
EXISTS(subquery)
```

**Example**

Query checking existence of values in a subquery:

```
SELECT EXISTS(SELECT * FROM numbers(10) WHERE number > 8), EXISTS(SELECT * FROM numbers(10) WHERE number > 11)
```

Result:

```
┌─in(1, _subquery1)─┬─in(1, _subquery2)─┐
│                 1 │                 0 │
└───────────────────┴───────────────────┘
```

Query with a subquery returning several rows:

```
SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 8);
```

Result:

```
┌─count()─┐
│      10 │
└─────────┘
```

Query with a subquery that returns an empty result:

```
SELECT count() FROM numbers(10) WHERE EXISTS(SELECT number FROM numbers(10) WHERE number > 11);
```

Result:

```
┌─count()─┐
│       0 │
└─────────┘
```

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/operators/distributed-ddl.md)

By default, the `CREATE`, `DROP`, `ALTER`, and `RENAME` queries affect only the current server where they are executed. In a cluster setup, it is possible to run such queries in a distributed manner with the `ON CLUSTER` clause.

For example, the following query creates the `all_hits` `Distributed` table on each host in `cluster`:

```
CREATE TABLE IF NOT EXISTS all_hits ON CLUSTER cluster (p Date, i Int32) ENGINE = Distributed(cluster, default, hits)
```

In order to run these queries correctly, each host must have the same cluster definition (to simplify syncing configs, you can use substitutions from ZooKeeper). They must also connect to the ZooKeeper servers.

The local version of the query will eventually be executed on each host in the cluster, even if some hosts are currently not available.

References

The order for executing queries within a single host is guaranteed.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/index.md)

Users interact with ClickHouse using SQL statements. ClickHouse supports common SQL statements like [SELECT](/docs/sql-reference/statements/select) and [CREATE](/docs/sql-reference/statements/create), but it also provides specialized statements like [KILL](/docs/sql-reference/statements/kill) and [OPTIMIZE](/docs/sql-reference/statements/optimize).

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/apply-deleted-mask.md)

```
ALTER TABLE [db].name [ON CLUSTER cluster] APPLY DELETED MASK [IN PARTITION partition_id]
```

The command applies mask created by [lightweight delete](/docs/sql-reference/statements/delete) and forcefully removes rows marked as deleted from disk. This command is a heavyweight mutation, and it semantically equals to query `ALTER TABLE [db].name DELETE WHERE _row_exists = 0`.

Note

It only works for tables in the [`MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) family (including [replicated](/docs/engines/table-engines/mergetree-family/replication) tables).

**See also**

- [Lightweight deletes](/docs/sql-reference/statements/delete)
- [Heavyweight deletes](/docs/sql-reference/statements/alter/delete)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/apply-patches.md)

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

```
ALTER TABLE [db.]table [ON CLUSTER cluster] APPLY PATCHES [IN PARTITION partition_id]
```

The command manually triggers the physical materialization of patch parts created by [lightweight `UPDATE`](/docs/sql-reference/statements/update) statements. It forcefully applies pending patches to the data parts by rewriting only the affected columns.

Note

- It only works for tables in the [`MergeTree`](/docs/engines/table-engines/mergetree-family/mergetree) family (including [replicated](/docs/engines/table-engines/mergetree-family/replication) tables).
- This is a mutation operation and executes asynchronously in the background.

## When to use APPLY PATCHES[​](#when-to-use "Direct link to When to use APPLY PATCHES")

Tip

Generally, you should not need to use `APPLY PATCHES`

Patch parts are normally applied automatically during merges when the [`apply_patches_on_merge`](/docs/operations/settings/merge-tree-settings#apply_patches_on_merge) setting is enabled (default). However, you may want to manually trigger patch application in these scenarios:

- To reduce the overhead of applying patches during `SELECT` queries
- To consolidate multiple patch parts before they accumulate
- To prepare data for backup or export with patches already materialized
- When `apply_patches_on_merge` is disabled and you want to control when patches are applied

## Examples[​](#examples "Direct link to Examples")

Apply all pending patches for a table:

```
ALTER TABLE my_table APPLY PATCHES;
```

Apply patches only for a specific partition:

```
ALTER TABLE my_table APPLY PATCHES IN PARTITION '2024-01';
```

Combine with other operations:

```
ALTER TABLE my_table APPLY PATCHES, UPDATE column = value WHERE condition;
```

## Monitoring patch application[​](#monitor "Direct link to Monitoring patch application")

You can monitor the progress of patch application using the [`system.mutations`](/docs/operations/system-tables/mutations) table:

```
SELECT * FROM system.mutations
WHERE table = 'my_table' AND command LIKE '%APPLY PATCHES%';
```

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

- [Lightweight `UPDATE`](/docs/sql-reference/statements/update) - Create patch parts with lightweight updates
- [`apply_patches_on_merge` setting](/docs/operations/settings/merge-tree-settings#apply_patches_on_merge) - Control automatic patch application during merges

- [When to use APPLY PATCHES](#when-to-use)- [Examples](#examples)- [Monitoring patch application](#monitor)- [See also](#see-also)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/comment.md)

Adds, modifies, or removes a table comment, regardless of whether it was set
before or not. The comment change is reflected in both [`system.tables`](/docs/operations/system-tables/tables)
and in the `SHOW CREATE TABLE` query.

## Syntax[​](#syntax "Direct link to Syntax")

```
ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY COMMENT 'Comment'
```

## Examples[​](#examples "Direct link to Examples")

To create a table with a comment:

```
CREATE TABLE table_with_comment
(
    `k` UInt64,
    `s` String
)
ENGINE = Memory()
COMMENT 'The temporary table';
```

To modify the table comment:

```
ALTER TABLE table_with_comment 
MODIFY COMMENT 'new comment on a table';
```

To view the modified comment:

```
SELECT comment 
FROM system.tables 
WHERE database = currentDatabase() AND name = 'table_with_comment';
```

```
┌─comment────────────────┐
│ new comment on a table │
└────────────────────────┘
```

To remove the table comment:

```
ALTER TABLE table_with_comment MODIFY COMMENT '';
```

To verify that the comment was removed:

```
SELECT comment 
FROM system.tables 
WHERE database = currentDatabase() AND name = 'table_with_comment';
```

```
┌─comment─┐
│         │
└─────────┘
```

## Caveats[​](#caveats "Direct link to Caveats")

For Replicated tables, the comment can be different on different replicas.
Modifying the comment applies to a single replica.

The feature is available since version 23.9. It does not work in previous
ClickHouse versions.

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

- [`COMMENT`](/docs/sql-reference/statements/create/table#comment-clause) clause
- [`ALTER DATABASE ... MODIFY COMMENT`](/docs/sql-reference/statements/alter/database-comment)

- [Syntax](#syntax)- [Examples](#examples)- [Caveats](#caveats)- [Related content](#related-content)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/constraint.md)

Constraints could be added or deleted using following syntax:

```
ALTER TABLE [db].name [ON CLUSTER cluster] ADD CONSTRAINT [IF NOT EXISTS] constraint_name CHECK expression;
ALTER TABLE [db].name [ON CLUSTER cluster] DROP CONSTRAINT [IF EXISTS] constraint_name;
```

See more on [constraints](/docs/sql-reference/statements/create/table#constraints).

Queries will add or remove metadata about constraints from table, so they are processed immediately.

Tip

Constraint check **will not be executed** on existing data if it was added.

All changes on replicated tables are broadcast to ZooKeeper and will be applied on other replicas as well.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/database-comment.md)

Adds, modifies, or removes a database comment, regardless of whether it was set
before or not. The comment change is reflected in both [`system.databases`](/docs/operations/system-tables/databases)
and the `SHOW CREATE DATABASE` query.

## Syntax[​](#syntax "Direct link to Syntax")

```
ALTER DATABASE [db].name [ON CLUSTER cluster] MODIFY COMMENT 'Comment'
```

## Examples[​](#examples "Direct link to Examples")

To create a `DATABASE` with a comment:

```
CREATE DATABASE database_with_comment ENGINE = Memory COMMENT 'The temporary database';
```

To modify the comment:

```
ALTER DATABASE database_with_comment 
MODIFY COMMENT 'new comment on a database';
```

To view the modified comment:

```
SELECT comment 
FROM system.databases 
WHERE name = 'database_with_comment';
```

```
┌─comment─────────────────┐
│ new comment on database │
└─────────────────────────┘
```

To remove the database comment:

```
ALTER DATABASE database_with_comment 
MODIFY COMMENT '';
```

To verify that the comment was removed:

```
SELECT comment 
FROM system.databases 
WHERE  name = 'database_with_comment';
```

```
┌─comment─┐
│         │
└─────────┘
```

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

- [`COMMENT`](/docs/sql-reference/statements/create/table#comment-clause) clause
- [`ALTER TABLE ... MODIFY COMMENT`](/docs/sql-reference/statements/alter/comment)

- [Syntax](#syntax)- [Examples](#examples)- [Related content](#related-content)

Was this page helpful?