Other functions

hostName

Returns the name of the host on which this function was executed. If the function executes on a remote server (distributed processing), the remote server name is returned. If the function executes in the context of a distributed table, it generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

hostName()

Returned value

Host name. String.

getMacro

Returns a named value from the macros section of the server configuration.

Syntax

getMacro(name);

Arguments

name — Macro name to retrieve from the <macros> section. String.

Returned value

Value of the specified macro. String.

Example

Example <macros> section in the server configuration file:

<macros>
    <test>Value</test>
</macros>

Query:

SELECT getMacro('test');

Result:

┌─getMacro('test')─┐
│ Value            │
└──────────────────┘

The same value can be retrieved as follows:

SELECT * FROM system.macros
WHERE macro = 'test';

┌─macro─┬─substitution─┐
│ test  │ Value        │
└───────┴──────────────┘

fqdn

Returns the fully qualified domain name of the ClickHouse server.

Syntax

fqdn();

Aliases: fullHostName, FQDN.

Returned value

String with the fully qualified domain name. String.

Example

SELECT FQDN();

Result:

┌─FQDN()──────────────────────────┐
│ clickhouse.ru-central1.internal │
└─────────────────────────────────┘

basename

Extracts the tail of a string following its last slash or backslash. This function if often used to extract the filename from a path.

basename(expr)

Arguments

expr — A value of type String. Backslashes must be escaped.

Returned Value

A string that contains:

The tail of the input string after its last slash or backslash. If the input string ends with a slash or backslash (e.g. / or c:\), the function returns an empty string.
The original string if there are no slashes or backslashes.

Example

Query:

SELECT 'some/long/path/to/file' AS a, basename(a)

Result:

┌─a──────────────────────┬─basename('some\\long\\path\\to\\file')─┐
│ some\long\path\to\file │ file                                   │
└────────────────────────┴────────────────────────────────────────┘

Query:

SELECT 'some\\long\\path\\to\\file' AS a, basename(a)

Result:

┌─a──────────────────────┬─basename('some\\long\\path\\to\\file')─┐
│ some\long\path\to\file │ file                                   │
└────────────────────────┴────────────────────────────────────────┘

Query:

SELECT 'some-file-name' AS a, basename(a)

Result:

┌─a──────────────┬─basename('some-file-name')─┐
│ some-file-name │ some-file-name             │
└────────────────┴────────────────────────────┘

visibleWidth

Calculates the approximate width when outputting values to the console in text format (tab-separated). This function is used by the system to implement Pretty formats.

NULL is represented as a string corresponding to NULL in Pretty formats.

Syntax

visibleWidth(x)

Example

Query:

SELECT visibleWidth(NULL)

Result:

┌─visibleWidth(NULL)─┐
│                  4 │
└────────────────────┘

toTypeName

Returns the type name of the passed argument.

If NULL is passed, the function returns type Nullable(Nothing), which corresponds to ClickHouse's internal NULL representation.

Syntax

toTypeName(value)

Arguments

value — A value of arbitrary type.

Returned value

The data type name of the input value. String.

Example

Query:

SELECT toTypeName(123);

Result:

┌─toTypeName(123)─┐
│ UInt8           │
└─────────────────┘

blockSize

In ClickHouse, queries are processed in blocks (chunks). This function returns the size (row count) of the block the function is called on.

Syntax

blockSize()

Example

Query:

DROP TABLE IF EXISTS test;
CREATE TABLE test (n UInt8) ENGINE = Memory;

INSERT INTO test
SELECT * FROM system.numbers LIMIT 5;

SELECT blockSize()
FROM test;

Result:

   ┌─blockSize()─┐
1. │           5 │
2. │           5 │
3. │           5 │
4. │           5 │
5. │           5 │
   └─────────────┘

byteSize

Returns an estimation of uncompressed byte size of its arguments in memory.

Syntax

byteSize(argument [, ...])

Arguments

argument — Value.

Returned value

Estimation of byte size of the arguments in memory. UInt64.

Examples

For String arguments, the function returns the string length + 8 (length).

Query:

SELECT byteSize('string');

Result:

┌─byteSize('string')─┐
│                 15 │
└────────────────────┘

Query:

CREATE TABLE test
(
    `key` Int32,
    `u8` UInt8,
    `u16` UInt16,
    `u32` UInt32,
    `u64` UInt64,
    `i8` Int8,
    `i16` Int16,
    `i32` Int32,
    `i64` Int64,
    `f32` Float32,
    `f64` Float64
)
ENGINE = MergeTree
ORDER BY key;

INSERT INTO test VALUES(1, 8, 16, 32, 64,  -8, -16, -32, -64, 32.32, 64.64);

SELECT key, byteSize(u8) AS `byteSize(UInt8)`, byteSize(u16) AS `byteSize(UInt16)`, byteSize(u32) AS `byteSize(UInt32)`, byteSize(u64) AS `byteSize(UInt64)`, byteSize(i8) AS `byteSize(Int8)`, byteSize(i16) AS `byteSize(Int16)`, byteSize(i32) AS `byteSize(Int32)`, byteSize(i64) AS `byteSize(Int64)`, byteSize(f32) AS `byteSize(Float32)`, byteSize(f64) AS `byteSize(Float64)` FROM test ORDER BY key ASC FORMAT Vertical;

Result:

Row 1:
──────
key:               1
byteSize(UInt8):   1
byteSize(UInt16):  2
byteSize(UInt32):  4
byteSize(UInt64):  8
byteSize(Int8):    1
byteSize(Int16):   2
byteSize(Int32):   4
byteSize(Int64):   8
byteSize(Float32): 4
byteSize(Float64): 8

If the function has multiple arguments, the function accumulates their byte sizes.

Query:

SELECT byteSize(NULL, 1, 0.3, '');

Result:

┌─byteSize(NULL, 1, 0.3, '')─┐
│                         19 │
└────────────────────────────┘

materialize

Turns a constant into a full column containing a single value. Full columns and constants are represented differently in memory. Functions usually execute different code for normal and constant arguments, although the result should typically be the same. This function can be used to debug this behavior.

Syntax

materialize(x)

Parameters

x — A constant. Constant.

Returned value

A column containing a single value x.

Example

In the example below the countMatches function expects a constant second argument. This behaviour can be debugged by using the materialize function to turn a constant into a full column, verifying that the function throws an error for a non-constant argument.

Query:

SELECT countMatches('foobarfoo', 'foo');
SELECT countMatches('foobarfoo', materialize('foo'));

Result:

2
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type of argument #2 'pattern' of function countMatches, expected constant String, got String

ignore

Accepts arbitrary arguments and unconditionally returns 0. The argument is still evaluated internally, making it useful for eg. benchmarking.

Syntax

ignore([arg1[, arg2[, ...]])

Arguments

Accepts arbitrarily many arguments of arbitrary type, including NULL.

Returned value

Returns 0.

Example

Query:

SELECT ignore(0, 'ClickHouse', NULL);

Result:

┌─ignore(0, 'ClickHouse', NULL)─┐
│                             0 │
└───────────────────────────────┘

sleep

Used to introduce a delay or pause in the execution of a query. It is primarily used for testing and debugging purposes.

Syntax

sleep(seconds)

Arguments

seconds: UInt* or Float The number of seconds to pause the query execution to a maximum of 3 seconds. It can be a floating-point value to specify fractional seconds.

Returned value

This function does not return any value.

Example

SELECT sleep(2);

This function does not return any value. However, if you run the function with clickhouse client you will see something similar to:

SELECT sleep(2)

Query id: 8aa9943e-a686-45e1-8317-6e8e3a5596ac

┌─sleep(2)─┐
│        0 │
└──────────┘

1 row in set. Elapsed: 2.012 sec.

This query will pause for 2 seconds before completing. During this time, no results will be returned, and the query will appear to be hanging or unresponsive.

Implementation details

The sleep() function is generally not used in production environments, as it can negatively impact query performance and system responsiveness. However, it can be useful in the following scenarios:

Testing: When testing or benchmarking ClickHouse, you may want to simulate delays or introduce pauses to observe how the system behaves under certain conditions.
Debugging: If you need to examine the state of the system or the execution of a query at a specific point in time, you can use sleep() to introduce a pause, allowing you to inspect or collect relevant information.
Simulation: In some cases, you may want to simulate real-world scenarios where delays or pauses occur, such as network latency or external system dependencies.

It's important to use the sleep() function judiciously and only when necessary, as it can potentially impact the overall performance and responsiveness of your ClickHouse system.

sleepEachRow

Pauses the execution of a query for a specified number of seconds for each row in the result set.

Syntax

sleepEachRow(seconds)

Arguments

seconds: UInt* or Float* The number of seconds to pause the query execution for each row in the result set to a maximum of 3 seconds. It can be a floating-point value to specify fractional seconds.

Returned value

This function returns the same input values as it receives, without modifying them.

Example

SELECT number, sleepEachRow(0.5) FROM system.numbers LIMIT 5;

┌─number─┬─sleepEachRow(0.5)─┐
│      0 │                 0 │
│      1 │                 0 │
│      2 │                 0 │
│      3 │                 0 │
│      4 │                 0 │
└────────┴───────────────────┘

But the output will be delayed, with a 0.5-second pause between each row.

The sleepEachRow() function is primarily used for testing and debugging purposes, similar to the sleep() function. It allows you to simulate delays or introduce pauses in the processing of each row, which can be useful in scenarios such as:

Testing: When testing or benchmarking ClickHouse's performance under specific conditions, you can use sleepEachRow() to simulate delays or introduce pauses for each row processed.
Debugging: If you need to examine the state of the system or the execution of a query for each row processed, you can use sleepEachRow() to introduce pauses, allowing you to inspect or collect relevant information.
Simulation: In some cases, you may want to simulate real-world scenarios where delays or pauses occur for each row processed, such as when dealing with external systems or network latencies.

Like the sleep() function, it's important to use sleepEachRow() judiciously and only when necessary, as it can significantly impact the overall performance and responsiveness of your ClickHouse system, especially when dealing with large result sets.

currentDatabase

Returns the name of the current database. Useful in table engine parameters of CREATE TABLE queries where you need to specify the database.

Syntax

currentDatabase()

Returned value

Returns the current database name. String.

Example

Query:

SELECT currentDatabase()

Result:

┌─currentDatabase()─┐
│ default           │
└───────────────────┘

currentUser

Returns the name of the current user. In case of a distributed query, the name of the user who initiated the query is returned.

Syntax

currentUser()

Aliases: user(), USER(), current_user(). Aliases are case insensitive.

Returned values

The name of the current user. String.
In distributed queries, the login of the user who initiated the query. String.

Example

SELECT currentUser();

Result:

┌─currentUser()─┐
│ default       │
└───────────────┘

currentSchemas

Returns a single-element array with the name of the current database schema.

Syntax

currentSchemas(bool)

Alias: current_schemas.

Arguments

bool: A boolean value. Bool.

Note

The boolean argument is ignored. It only exists for the sake of compatibility with the implementation of this function in PostgreSQL.

Returned values

Returns a single-element array with the name of the current database

Example

SELECT currentSchemas(true);

Result:

['default']

colorSRGBToOKLCH

Converts a colour encoded in the sRGB colour space to the perceptually uniform OKLCH colour space.

If any input channel is outside [0...255] or the gamma value is non-positive, the behaviour is implementation-defined.

Note

OKLCH is a cylindrical version of the OKLab colour space. Its three coordinates are L (lightness in range [0...1]), C (chroma >= 0) and H (hue in degrees [0...360])**.
OKLab/OKLCH is designed to be perceptually uniform while remaining cheap to compute.

Syntax

colorSRGBToOKLCH(tuple [, gamma])

Arguments

tuple - Three numeric values R, G, B in the range [0...255]. Tuple.
gamma - Optional numeric value. Exponent that is used to linearize sRGB by applying (x / 255)^gamma to each channel x. Defaults to 2.2.

Returned values

A tuple (L, C, H) of type Tuple(Float64, Float64, Float64).

Implementation details

The conversion consists of three stages:

sRGB to Linear sRGB
Linear sRGB to OKLab
OKLab to OKLCH.

Gamma is used at the first stage, when computing linear sRGB. For that we normalize sRGB values and take them in power of gamma. Observe, that this lacks some precision due to floating-point rounding. This design choice was made in order to be able to quickly compute values for different gammas, and since the difference does not changed the perception of the color significantly.

Two stages involve matrix multiplication and trigonometry conversions respectively. For more details on maths please see an article on OKLab color space: https://bottosson.github.io/posts/OKLab/

In order to have some references for colors in OKLCH space, and how they correspond to sRGB colors please see https://OKLCH.com/

Example

SELECT colorSRGBToOKLCH((128, 64, 32), 2.2) AS lch;

Result:

┌─lch─────────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.10442699545678624,45.907345481930236) │
└─────────────────────────────────────────────────────────────┘

colorOKLCHToSRGB

Converts a colour from the OKLCH perceptual colour space to the familiar sRGB colour space.

If L is outside [0...1], C is negative, or H is outside [0...360], the result is implementation-defined.

Note

OKLCH is a cylindrical version of the OKLab colour space. Its three coordinates are L (lightness in range [0...1]), C (chroma >= 0) and H (hue in degrees [0...360])**. OKLab/OKLCH is designed to be perceptually uniform while remaining cheap to compute.

Syntax

colorOKLCHToSRGB(tuple [, gamma])

Arguments

tuple - Three numeric values L, C, H, presented as tuple where L is in range [0...1], C >= 0 and H is in range [0...360]. Tuple.
gamma - Optional numeric value. Exponent that is used to transform linear sRGB back to sRGB by applying (x ^ (1 / gamma)) * 255 for each channel x. Defaults to 2.2.

Returned values

A tuple (R, G, B) of type Tuple(Float64, Float64, Float64).

Note

This function returns floating-point numbers, rather than integer values, to avoid forcing rounding. Users can perform the rounding themselves.

Implementation details

The conversion is inverse of colorSRGBToOKLCH:

OKLCH to OKLab.
OKLab to Linear sRGB
Linear sRGB to sRGB

Second argument gamma is used at the last stage. Note, that all three channels are clipped in range [0...1] right before computing linear sRGB, and then set in power 1 / gamma. In case gamma is 0, 1 / gamma is changed for 1'000'000. Thus, regardless of the input we normally will have returned floats in range [0...255].

As in case of colorSRGBToOKLCH, two other stages involve trigonometry conversions and matrix multiplication respectively. For more details on maths please see see an article on OKLab color space: https://bottosson.github.io/posts/oklab/

In order to have some references for colors in OKLCH space, and how they correspond to sRGB colors please see https://oklch.com/

Example

SELECT colorOKLCHToSRGB((0.4466, 0.0991, 45.44), 2.2) AS rgb
WITH colorOKLCHToSRGB((0.7, 0.1, 54)) as t SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB

Result:

┌─rgb──────────────────────────────────────────────────────┐
│ (127.03349738778945,66.06672044472008,37.11802592155851) │
└──────────────────────────────────────────────────────────┘

┌─RGB──────────┐
│ (205,139,97) │
└──────────────┘

isConstant

Returns whether the argument is a constant expression.

A constant expression is an expression whose result is known during query analysis, i.e. before execution. For example, expressions over literals are constant expressions.

This function is mostly intended for development, debugging and demonstration.

Syntax

isConstant(x)

Arguments

x — Expression to check.

Returned values

1 if x is constant. UInt8.
0 if x is non-constant. UInt8.

Examples

Query:

SELECT isConstant(x + 1) FROM (SELECT 43 AS x)

Result:

┌─isConstant(plus(x, 1))─┐
│                      1 │
└────────────────────────┘

Query:

WITH 3.14 AS pi SELECT isConstant(cos(pi))

Result:

┌─isConstant(cos(pi))─┐
│                   1 │
└─────────────────────┘

Query:

SELECT isConstant(number) FROM numbers(1)

Result:

┌─isConstant(number)─┐
│                  0 │
└────────────────────┘

hasColumnInTable

Given the database name, the table name, and the column name as constant strings, returns 1 if the given column exists, otherwise 0.

Syntax

hasColumnInTable(\['hostname'\[, 'username'\[, 'password'\]\],\] 'database', 'table', 'column')

Parameters

database : name of the database. String literal
table : name of the table. String literal
column : name of the column. String literal
hostname : remote server name to perform the check on. String literal
username : username for remote server. String literal
password : password for remote server. String literal

Returned value

1 if the given column exists.
0, otherwise.

Implementation details

For elements in a nested data structure, the function checks for the existence of a column. For the nested data structure itself, the function returns 0.

Example

Query:

SELECT hasColumnInTable('system','metrics','metric')

SELECT hasColumnInTable('system','metrics','non-existing_column')

hasThreadFuzzer

Returns whether Thread Fuzzer is effective. It can be used in tests to prevent runs from being too long.

Syntax

hasThreadFuzzer();

bar

Builds a bar chart.

bar(x, min, max, width) draws a band with width proportional to (x - min) and equal to width characters when x = max.

Arguments

x — Size to display.
min, max — Integer constants. The value must fit in Int64.
width — Constant, positive integer, can be fractional.

The band is drawn with accuracy to one eighth of a symbol.

Example:

SELECT
    toHour(EventTime) AS h,
    count() AS c,
    bar(c, 0, 600000, 20) AS bar
FROM test.hits
GROUP BY h
ORDER BY h ASC

┌──h─┬──────c─┬─bar────────────────┐
│  0 │ 292907 │ █████████▋         │
│  1 │ 180563 │ ██████             │
│  2 │ 114861 │ ███▋               │
│  3 │  85069 │ ██▋                │
│  4 │  68543 │ ██▎                │
│  5 │  78116 │ ██▌                │
│  6 │ 113474 │ ███▋               │
│  7 │ 170678 │ █████▋             │
│  8 │ 278380 │ █████████▎         │
│  9 │ 391053 │ █████████████      │
│ 10 │ 457681 │ ███████████████▎   │
│ 11 │ 493667 │ ████████████████▍  │
│ 12 │ 509641 │ ████████████████▊  │
│ 13 │ 522947 │ █████████████████▍ │
│ 14 │ 539954 │ █████████████████▊ │
│ 15 │ 528460 │ █████████████████▌ │
│ 16 │ 539201 │ █████████████████▊ │
│ 17 │ 523539 │ █████████████████▍ │
│ 18 │ 506467 │ ████████████████▊  │
│ 19 │ 520915 │ █████████████████▎ │
│ 20 │ 521665 │ █████████████████▍ │
│ 21 │ 542078 │ ██████████████████ │
│ 22 │ 493642 │ ████████████████▍  │
│ 23 │ 400397 │ █████████████▎     │
└────┴────────┴────────────────────┘

transform

Transforms a value according to the explicitly defined mapping of some elements to other ones. There are two variations of this function:

transform(x, array_from, array_to, default)

x – What to transform.

array_from – Constant array of values to convert.

array_to – Constant array of values to convert the values in 'from' to.

default – Which value to use if 'x' is not equal to any of the values in 'from'.

array_from and array_to must have equally many elements.

Signature:

For x equal to one of the elements in array_from, the function returns the corresponding element in array_to, i.e. the one at the same array index. Otherwise, it returns default. If multiple matching elements exist array_from, it returns the element corresponding to the first of them.

transform(T, Array(T), Array(U), U) -> U

T and U can be numeric, string, or Date or DateTime types. The same letter (T or U) means that types must be mutually compatible and not necessarily equal. For example, the first argument could have type Int64, while the second argument could have type Array(UInt16).

Example:

SELECT
    transform(SearchEngineID, [2, 3], ['Yandex', 'Google'], 'Other') AS title,
    count() AS c
FROM test.hits
WHERE SearchEngineID != 0
GROUP BY title
ORDER BY c DESC

┌─title─────┬──────c─┐
│ Yandex    │ 498635 │
│ Google    │ 229872 │
│ Other     │ 104472 │
└───────────┴────────┘

transform(x, array_from, array_to)

Similar to the other variation but has no 'default' argument. In case no match can be found, x is returned.

Example:

SELECT
    transform(domain(Referer), ['yandex.ru', 'google.ru', 'vkontakte.ru'], ['www.yandex', 'example.com', 'vk.com']) AS s,
    count() AS c
FROM test.hits
GROUP BY domain(Referer)
ORDER BY count() DESC
LIMIT 10

┌─s──────────────┬───────c─┐
│                │ 2906259 │
│ www.yandex     │  867767 │
│ ███████.ru     │  313599 │
│ mail.yandex.ru │  107147 │
│ ██████.ru      │  100355 │
│ █████████.ru   │   65040 │
│ news.yandex.ru │   64515 │
│ ██████.net     │   59141 │
│ example.com    │   57316 │
└────────────────┴─────────┘

formatReadableDecimalSize

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KB, MB, etc.) as string.

The opposite operations of this function are parseReadableSize, parseReadableSizeOrZero, and parseReadableSizeOrNull.

Syntax

formatReadableDecimalSize(x)

Example

Query:

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableDecimalSize(filesize_bytes) AS filesize

Result:

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.02 KB   │
│        1048576 │ 1.05 MB   │
│      192851925 │ 192.85 MB │
└────────────────┴────────────┘

formatReadableSize

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KiB, MiB, etc.) as string.

The opposite operations of this function are parseReadableSize, parseReadableSizeOrZero, and parseReadableSizeOrNull.

Syntax

formatReadableSize(x)

Alias: FORMAT_BYTES.

Note

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values

Example

Query:

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableSize(filesize_bytes) AS filesize

Result:

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.00 KiB   │
│        1048576 │ 1.00 MiB   │
│      192851925 │ 183.92 MiB │
└────────────────┴────────────┘

formatReadableQuantity

Given a number, this function returns a rounded number with suffix (thousand, million, billion, etc.) as string.

Syntax

formatReadableQuantity(x)

Note

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values

Example

Query:

SELECT
    arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
    formatReadableQuantity(number) AS number_for_humans

Result:

┌─────────number─┬─number_for_humans─┐
│           1024 │ 1.02 thousand     │
│        1234000 │ 1.23 million      │
│     4567000000 │ 4.57 billion      │
│ 98765432101234 │ 98.77 trillion    │
└────────────────┴───────────────────┘

formatReadableTimeDelta

Given a time interval (delta) in seconds, this function returns a time delta with year/month/day/hour/minute/second/millisecond/microsecond/nanosecond as string.

Syntax

formatReadableTimeDelta(column[, maximum_unit, minimum_unit])

Note

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values

Arguments

column — A column with a numeric time delta.
maximum_unit — Optional. Maximum unit to show.
- Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years.
- Default value: years.
minimum_unit — Optional. Minimum unit to show. All smaller units are truncated.
- Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years.
- If explicitly specified value is bigger than maximum_unit, an exception will be thrown.
- Default value: seconds if maximum_unit is seconds or bigger, nanoseconds otherwise.

Example

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed) AS time_delta

┌────elapsed─┬─time_delta ─────────────────────────────────────────────────────┐
│        100 │ 1 minute and 40 seconds                                         │
│      12345 │ 3 hours, 25 minutes and 45 seconds                              │
│  432546534 │ 13 years, 8 months, 17 days, 7 hours, 48 minutes and 54 seconds │
└────────────┴─────────────────────────────────────────────────────────────────┘

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed, 'minutes') AS time_delta

┌────elapsed─┬─time_delta ─────────────────────────────────────────────────────┐
│        100 │ 1 minute and 40 seconds                                         │
│      12345 │ 205 minutes and 45 seconds                                      │
│  432546534 │ 7209108 minutes and 54 seconds                                  │
└────────────┴─────────────────────────────────────────────────────────────────┘

SELECT
    arrayJoin([100, 12345, 432546534.00000006]) AS elapsed,
    formatReadableTimeDelta(elapsed, 'minutes', 'nanoseconds') AS time_delta

┌────────────elapsed─┬─time_delta─────────────────────────────────────┐
│                100 │ 1 minute and 40 seconds                        │
│              12345 │ 205 minutes and 45 seconds                     │
│ 432546534.00000006 │ 7209108 minutes, 54 seconds and 60 nanoseconds │
└────────────────────┴────────────────────────────────────────────────┘

parseReadableSize

Given a string containing a byte size and B, KiB, KB, MiB, MB, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes.
If the function is unable to parse the input value, it throws an exception.

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSize(x)

Arguments

x : Readable size with ISO/IEC 80000-13 or decimal byte unit (String).

Returned value

Number of bytes, rounded up to the nearest integer (UInt64).

Example

SELECT
    arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB']) AS readable_sizes,  
    parseReadableSize(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
└────────────────┴─────────┘

parseReadableSizeOrNull

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSizeOrNull(x)

Arguments

x : Readable size with ISO/IEC 80000-13 or decimal byte unit (String).

Returned value

Number of bytes, rounded up to the nearest integer, or NULL if unable to parse the input (Nullable(UInt64)).

Example

SELECT
    arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes,  
    parseReadableSizeOrNull(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
│ invalid        │    ᴺᵁᴸᴸ │
└────────────────┴─────────┘

parseReadableSizeOrZero

Given a string containing a byte size and B, KiB, KB, MiB, MB, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it returns 0.

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize. Syntax

parseReadableSizeOrZero(x)

Arguments

x : Readable size with ISO/IEC 80000-13 or decimal byte unit (String).

Returned value

Number of bytes, rounded up to the nearest integer, or 0 if unable to parse the input (UInt64).

Example

SELECT
    arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes,  
    parseReadableSizeOrZero(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
│ invalid        │       0 │
└────────────────┴─────────┘

parseTimeDelta

Parse a sequence of numbers followed by something resembling a time unit.

Syntax

parseTimeDelta(timestr)

Arguments

timestr — A sequence of numbers followed by something resembling a time unit.

Returned value

A floating-point number with the number of seconds.

Example

SELECT parseTimeDelta('11s+22min')

┌─parseTimeDelta('11s+22min')─┐
│                        1331 │
└─────────────────────────────┘

SELECT parseTimeDelta('1yr2mo')

┌─parseTimeDelta('1yr2mo')─┐
│                 36806400 │
└──────────────────────────┘

least

Returns the smallest arguments of one or more input arguments. NULL arguments are ignored.

Syntax

least(a, b)

Note

Version 24.12 introduced a backwards-incompatible change such that NULL values are ignored, while previously it returned NULL if one of the arguments was NULL. To retain the previous behavior, set setting least_greatest_legacy_null_behavior (default: false) to true.

greatest

Returns the largest arguments of one or more input arguments. NULL arguments are ignored.

Syntax

greatest(a, b)

Note

uptime

Returns the server's uptime in seconds. If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

uptime()

Returned value

Time value of seconds. UInt32.

Example

Query:

SELECT uptime() AS Uptime;

Result:

┌─Uptime─┐
│  55867 │
└────────┘

version

Returns the current version of ClickHouse as a string in the form of:

Major version
Minor version
Patch version
Number of commits since the previous stable release.

major_version.minor_version.patch_version.number_of_commits_since_the_previous_stable_release

If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise, it produces a constant value.

Syntax

version()

Arguments

None.

Returned value

Current version of ClickHouse. String.

Implementation details

None.

Example

Query:

SELECT version()

Result:

┌─version()─┐
│ 24.2.1.1  │
└───────────┘

buildId

Returns the build ID generated by a compiler for the running ClickHouse server binary. If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

buildId()

blockNumber

Returns a monotonically increasing sequence number of the block containing the row. The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate.

Syntax

blockNumber()

Returned value

Sequence number of the data block where the row is located. UInt64.

Example

Query:

SELECT blockNumber()
FROM
(
    SELECT *
    FROM system.numbers
    LIMIT 10
) SETTINGS max_block_size = 2

Result:

┌─blockNumber()─┐
│             7 │
│             7 │
└───────────────┘
┌─blockNumber()─┐
│             8 │
│             8 │
└───────────────┘
┌─blockNumber()─┐
│             9 │
│             9 │
└───────────────┘
┌─blockNumber()─┐
│            10 │
│            10 │
└───────────────┘
┌─blockNumber()─┐
│            11 │
│            11 │
└───────────────┘

rowNumberInBlock

Returns for each block processed by rowNumberInBlock the number of the current row. The returned number starts for each block at 0.

Syntax

rowNumberInBlock()

Returned value

Ordinal number of the row in the data block starting from 0. UInt64.

Example

Query:

SELECT rowNumberInBlock()
FROM
(
    SELECT *
    FROM system.numbers_mt
    LIMIT 10
) SETTINGS max_block_size = 2

Result:

┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘

rowNumberInAllBlocks

Returns a unique row number for each row processed by rowNumberInAllBlocks. The returned numbers start at 0.

Syntax

rowNumberInAllBlocks()

Returned value

Ordinal number of the row in the data block starting from 0. UInt64.

Example

Query:

SELECT rowNumberInAllBlocks()
FROM
(
    SELECT *
    FROM system.numbers_mt
    LIMIT 10
)
SETTINGS max_block_size = 2

Result:

┌─rowNumberInAllBlocks()─┐
│                      0 │
│                      1 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      4 │
│                      5 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      2 │
│                      3 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      6 │
│                      7 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      8 │
│                      9 │
└────────────────────────┘

normalizeQuery

Replaces literals, sequences of literals and complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs) with placeholder ?.

Syntax

normalizeQuery(x)

Arguments

x — Sequence of characters. String.

Returned value

Sequence of characters with placeholders. String.

Example

Query:

SELECT normalizeQuery('[1, 2, 3, x]') AS query;

Result:

┌─query────┐
│ [?.., x] │
└──────────┘

normalizeQueryKeepNames

Replaces literals, sequences of literals with placeholder ? but does not replace complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs). This helps better analyze complex query logs.

Syntax

normalizeQueryKeepNames(x)

Arguments

x — Sequence of characters. String.

Returned value

Sequence of characters with placeholders. String.

Example

Query:

SELECT normalizeQuery('SELECT 1 AS aComplexName123'), normalizeQueryKeepNames('SELECT 1 AS aComplexName123');

Result:

┌─normalizeQuery('SELECT 1 AS aComplexName123')─┬─normalizeQueryKeepNames('SELECT 1 AS aComplexName123')─┐
│ SELECT ? AS `?`                               │ SELECT ? AS aComplexName123                            │
└───────────────────────────────────────────────┴────────────────────────────────────────────────────────┘

normalizedQueryHash

Returns identical 64bit hash values without the values of literals for similar queries. Can be helpful to analyze query logs.

Syntax

normalizedQueryHash(x)

Arguments

x — Sequence of characters. String.

Returned value

Hash value. UInt64.

Example

Query:

SELECT normalizedQueryHash('SELECT 1 AS `xyz`') != normalizedQueryHash('SELECT 1 AS `abc`') AS res;

Result:

┌─res─┐
│   1 │
└─────┘

normalizedQueryHashKeepNames

Like normalizedQueryHash it returns identical 64bit hash values without the values of literals for similar queries but it does not replace complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs) with a placeholder before hashing. Can be helpful to analyze query logs.

Syntax

normalizedQueryHashKeepNames(x)

Arguments

x — Sequence of characters. String.

Returned value

Hash value. UInt64.

Example

SELECT normalizedQueryHash('SELECT 1 AS `xyz123`') != normalizedQueryHash('SELECT 1 AS `abc123`') AS normalizedQueryHash;
SELECT normalizedQueryHashKeepNames('SELECT 1 AS `xyz123`') != normalizedQueryHashKeepNames('SELECT 1 AS `abc123`') AS normalizedQueryHashKeepNames;

Result:

┌─normalizedQueryHash─┐
│                   0 │
└─────────────────────┘
┌─normalizedQueryHashKeepNames─┐
│                            1 │
└──────────────────────────────┘

neighbor

Deprecated feature

The window function that provides access to a row at a specified offset before or after the current row of a given column.

Syntax

neighbor(column, offset[, default_value])

The result of the function depends on the affected data blocks and the order of data in the block.

Note

Only returns neighbor inside the currently processed data block. Because of this error-prone behavior the function is DEPRECATED, please use proper window functions instead.

The order of rows during calculation of neighbor() can differ from the order of rows returned to the user. To prevent that you can create a subquery with ORDER BY and call the function from outside the subquery.

Arguments

column — A column name or scalar expression.
offset — The number of rows to look before or ahead of the current row in column. Int64.
default_value — Optional. The returned value if offset is beyond the block boundaries. Type of data blocks affected.

Returned values

Value of column with offset distance from current row, if offset is not outside the block boundaries.
The default value of column or default_value (if given), if offset is outside the block boundaries.

Note

The return type will be that of the data blocks affected or the default value type.

Example

Query:

SELECT number, neighbor(number, 2) FROM system.numbers LIMIT 10;

Result:

┌─number─┬─neighbor(number, 2)─┐
│      0 │                   2 │
│      1 │                   3 │
│      2 │                   4 │
│      3 │                   5 │
│      4 │                   6 │
│      5 │                   7 │
│      6 │                   8 │
│      7 │                   9 │
│      8 │                   0 │
│      9 │                   0 │
└────────┴─────────────────────┘

Query:

SELECT number, neighbor(number, 2, 999) FROM system.numbers LIMIT 10;

Result:

┌─number─┬─neighbor(number, 2, 999)─┐
│      0 │                        2 │
│      1 │                        3 │
│      2 │                        4 │
│      3 │                        5 │
│      4 │                        6 │
│      5 │                        7 │
│      6 │                        8 │
│      7 │                        9 │
│      8 │                      999 │
│      9 │                      999 │
└────────┴──────────────────────────┘

This function can be used to compute year-over-year metric value:

Query:

WITH toDate('2018-01-01') AS start_date
SELECT
    toStartOfMonth(start_date + (number * 32)) AS month,
    toInt32(month) % 100 AS money,
    neighbor(money, -12) AS prev_year,
    round(prev_year / money, 2) AS year_over_year
FROM numbers(16)

Result:

┌──────month─┬─money─┬─prev_year─┬─year_over_year─┐
│ 2018-01-01 │    32 │         0 │              0 │
│ 2018-02-01 │    63 │         0 │              0 │
│ 2018-03-01 │    91 │         0 │              0 │
│ 2018-04-01 │    22 │         0 │              0 │
│ 2018-05-01 │    52 │         0 │              0 │
│ 2018-06-01 │    83 │         0 │              0 │
│ 2018-07-01 │    13 │         0 │              0 │
│ 2018-08-01 │    44 │         0 │              0 │
│ 2018-09-01 │    75 │         0 │              0 │
│ 2018-10-01 │     5 │         0 │              0 │
│ 2018-11-01 │    36 │         0 │              0 │
│ 2018-12-01 │    66 │         0 │              0 │
│ 2019-01-01 │    97 │        32 │           0.33 │
│ 2019-02-01 │    28 │        63 │           2.25 │
│ 2019-03-01 │    56 │        91 │           1.62 │
│ 2019-04-01 │    87 │        22 │           0.25 │
└────────────┴───────┴───────────┴────────────────┘

runningDifference

Calculates the difference between two consecutive row values in the data block. Returns 0 for the first row, and for subsequent rows the difference to the previous row.

Note

Only returns differences inside the currently processed data block. Because of this error-prone behavior the function is DEPRECATED, please use proper window functions instead.

The result of the function depends on the affected data blocks and the order of data in the block.

The order of rows during calculation of runningDifference() can differ from the order of rows returned to the user. To prevent that you can create a subquery with ORDER BY and call the function from outside the subquery.

Syntax

runningDifference(x)

Example

Query:

SELECT
    EventID,
    EventTime,
    runningDifference(EventTime) AS delta
FROM
(
    SELECT
        EventID,
        EventTime
    FROM events
    WHERE EventDate = '2016-11-24'
    ORDER BY EventTime ASC
    LIMIT 5
)

Result:

┌─EventID─┬───────────EventTime─┬─delta─┐
│    1106 │ 2016-11-24 00:00:04 │     0 │
│    1107 │ 2016-11-24 00:00:05 │     1 │
│    1108 │ 2016-11-24 00:00:05 │     0 │
│    1109 │ 2016-11-24 00:00:09 │     4 │
│    1110 │ 2016-11-24 00:00:10 │     1 │
└─────────┴─────────────────────┴───────┘

Please note that the block size affects the result. The internal state of runningDifference state is reset for each new block.

Query:

SELECT
    number,
    runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1

Result:

┌─number─┬─diff─┐
│      0 │    0 │
└────────┴──────┘
┌─number─┬─diff─┐
│  65536 │    0 │
└────────┴──────┘

Query:

set max_block_size=100000 -- default value is 65536!

SELECT
    number,
    runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1

Result:

┌─number─┬─diff─┐
│      0 │    0 │
└────────┴──────┘

runningDifferenceStartingWithFirstValue

Note

This function is DEPRECATED (see the note for runningDifference).

Same as runningDifference, but returns the value of the first row as the value on the first row.

runningConcurrency

Calculates the number of concurrent events. Each event has a start time and an end time. The start time is included in the event, while the end time is excluded. Columns with a start time and an end time must be of the same data type. The function calculates the total number of active (concurrent) events for each event start time.

Tip

Events must be ordered by the start time in ascending order. If this requirement is violated the function raises an exception. Every data block is processed separately. If events from different data blocks overlap then they can not be processed correctly.

Syntax

runningConcurrency(start, end)

Arguments

start — A column with the start time of events. Date, DateTime, or DateTime64.
end — A column with the end time of events. Date, DateTime, or DateTime64.

Returned values

The number of concurrent events at each event start time. UInt32

Example

Consider the table:

┌──────start─┬────────end─┐
│ 2021-03-03 │ 2021-03-11 │
│ 2021-03-06 │ 2021-03-12 │
│ 2021-03-07 │ 2021-03-08 │
│ 2021-03-11 │ 2021-03-12 │
└────────────┴────────────┘

Query:

SELECT start, runningConcurrency(start, end) FROM example_table;

Result:

┌──────start─┬─runningConcurrency(start, end)─┐
│ 2021-03-03 │                              1 │
│ 2021-03-06 │                              2 │
│ 2021-03-07 │                              3 │
│ 2021-03-11 │                              2 │
└────────────┴────────────────────────────────┘

MACNumToString

Interprets a UInt64 number as a MAC address in big endian format. Returns the corresponding MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form) as string.

Syntax

MACNumToString(num)

MACStringToNum

The inverse function of MACNumToString. If the MAC address has an invalid format, it returns 0.

Syntax

MACStringToNum(s)

MACStringToOUI

Given a MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form), returns the first three octets as a UInt64 number. If the MAC address has an invalid format, it returns 0.

Syntax

MACStringToOUI(s)

getSizeOfEnumType

Returns the number of fields in Enum. An exception is thrown if the type is not Enum.

Syntax

getSizeOfEnumType(value)

Arguments:

value — Value of type Enum.

Returned values

The number of fields with Enum input values.

Example

SELECT getSizeOfEnumType( CAST('a' AS Enum8('a' = 1, 'b' = 2) ) ) AS x

┌─x─┐
│ 2 │
└───┘

blockSerializedSize

Returns the size on disk without considering compression.

blockSerializedSize(value[, value[, ...]])

Arguments

value — Any value.

Returned values

The number of bytes that will be written to disk for block of values without compression.

Example

Query:

SELECT blockSerializedSize(maxState(1)) AS x

Result:

┌─x─┐
│ 2 │
└───┘

toColumnTypeName

Returns the internal name of the data type that represents the value.

Syntax

toColumnTypeName(value)

Arguments:

value — Any type of value.

Returned values

The internal data type name used to represent value.

Example

Difference between toTypeName and toColumnTypeName:

SELECT toTypeName(CAST('2018-01-01 01:02:03' AS DateTime))

Result:

┌─toTypeName(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ DateTime                                            │
└─────────────────────────────────────────────────────┘

Query:

SELECT toColumnTypeName(CAST('2018-01-01 01:02:03' AS DateTime))

Result:

┌─toColumnTypeName(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ Const(UInt32)                                             │
└───────────────────────────────────────────────────────────┘

The example shows that the DateTime data type is internally stored as Const(UInt32).

dumpColumnStructure

Outputs a detailed description of data structures in RAM

dumpColumnStructure(value)

Arguments:

value — Any type of value.

Returned values

A description of the column structure used for representing value.

Example

SELECT dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))

┌─dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ DateTime, Const(size = 1, UInt32(size = 1))                  │
└──────────────────────────────────────────────────────────────┘

defaultValueOfArgumentType

Returns the default value for the given data type.

Does not include default values for custom columns set by the user.

Syntax

defaultValueOfArgumentType(expression)

Arguments:

expression — Arbitrary type of value or an expression that results in a value of an arbitrary type.

Returned values

0 for numbers.
Empty string for strings.
ᴺᵁᴸᴸ for Nullable.

Example

Query:

SELECT defaultValueOfArgumentType( CAST(1 AS Int8) )

Result:

┌─defaultValueOfArgumentType(CAST(1, 'Int8'))─┐
│                                           0 │
└─────────────────────────────────────────────┘

Query:

SELECT defaultValueOfArgumentType( CAST(1 AS Nullable(Int8) ) )

Result:

┌─defaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))─┐
│                                                  ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────────┘

defaultValueOfTypeName

Returns the default value for the given type name.

Does not include default values for custom columns set by the user.

defaultValueOfTypeName(type)

Arguments:

type — A string representing a type name.

Returned values

0 for numbers.
Empty string for strings.
ᴺᵁᴸᴸ for Nullable.

Example

Query:

SELECT defaultValueOfTypeName('Int8')

Result:

┌─defaultValueOfTypeName('Int8')─┐
│                              0 │
└────────────────────────────────┘

Query:

SELECT defaultValueOfTypeName('Nullable(Int8)')

Result:

┌─defaultValueOfTypeName('Nullable(Int8)')─┐
│                                     ᴺᵁᴸᴸ │
└──────────────────────────────────────────┘

indexHint

This function is intended for debugging and introspection. It ignores its argument and always returns 1. The arguments are not evaluated.

But during index analysis, the argument of this function is assumed to be not wrapped in indexHint. This allows to select data in index ranges by the corresponding condition but without further filtering by this condition. The index in ClickHouse is sparse and using indexHint will yield more data than specifying the same condition directly.

Syntax

SELECT * FROM table WHERE indexHint(<expression>)

Returned value

1. Uint8.

Example

Here is the example of test data from the table ontime.

Table:

SELECT count() FROM ontime

┌─count()─┐
│ 4276457 │
└─────────┘

The table has indexes on the fields (FlightDate, (Year, FlightDate)).

Create a query which does not use the index:

SELECT FlightDate AS k, count() FROM ontime GROUP BY k ORDER BY k

ClickHouse processed the entire table (Processed 4.28 million rows).

Result:

┌──────────k─┬─count()─┐
│ 2017-01-01 │   13970 │
│ 2017-01-02 │   15882 │
........................
│ 2017-09-28 │   16411 │
│ 2017-09-29 │   16384 │
│ 2017-09-30 │   12520 │
└────────────┴─────────┘

To apply the index, select a specific date:

SELECT FlightDate AS k, count() FROM ontime WHERE k = '2017-09-15' GROUP BY k ORDER BY k

ClickHouse now uses the index to process a significantly smaller number of rows (Processed 32.74 thousand rows).

Result:

┌──────────k─┬─count()─┐
│ 2017-09-15 │   16428 │
└────────────┴─────────┘

Now wrap the expression k = '2017-09-15' in function indexHint:

Query:

SELECT
    FlightDate AS k,
    count()
FROM ontime
WHERE indexHint(k = '2017-09-15')
GROUP BY k
ORDER BY k ASC

ClickHouse used the index the same way as previously (Processed 32.74 thousand rows). The expression k = '2017-09-15' was not used when generating the result. In example, the indexHint function allows to see adjacent dates.

Result:

┌──────────k─┬─count()─┐
│ 2017-09-14 │    7071 │
│ 2017-09-15 │   16428 │
│ 2017-09-16 │    1077 │
│ 2017-09-30 │    8167 │
└────────────┴─────────┘

replicate

Creates an array with a single value.

Note

This function is used for the internal implementation of arrayJoin.

Syntax

replicate(x, arr)

Arguments

x — The value to fill the result array with.
arr — An array. Array.

Returned value

An array of the lame length as arr filled with value x. Array.

Example

Query:

SELECT replicate(1, ['a', 'b', 'c']);

Result:

┌─replicate(1, ['a', 'b', 'c'])─┐
│ [1,1,1]                       │
└───────────────────────────────┘

revision

Returns the current ClickHouse server revision.

Syntax

revision()

Returned value

The current ClickHouse server revision. UInt32.

Example

Query:

SELECT revision();

Result:

┌─revision()─┐
│      54485 │
└────────────┘

filesystemAvailable

Returns the amount of free space in the filesystem hosting the database persistence. The returned value is always smaller than total free space (filesystemUnreserved) because some space is reserved for the operating system.

Syntax

filesystemAvailable()

Returned value

The amount of remaining space available in bytes. UInt64.

Example

Query:

SELECT formatReadableSize(filesystemAvailable()) AS "Available space";

Result:

┌─Available space─┐
│ 30.75 GiB       │
└─────────────────┘

filesystemUnreserved

Returns the total amount of the free space on the filesystem hosting the database persistence. (previously filesystemFree). See also filesystemAvailable.

Syntax

filesystemUnreserved()

Returned value

The amount of free space in bytes. UInt64.

Example

Query:

SELECT formatReadableSize(filesystemUnreserved()) AS "Free space";

Result:

┌─Free space─┐
│ 32.39 GiB  │
└────────────┘

filesystemCapacity

Returns the capacity of the filesystem in bytes. Needs the path to the data directory to be configured.

Syntax

filesystemCapacity()

Returned value

Capacity of the filesystem in bytes. UInt64.

Example

Query:

SELECT formatReadableSize(filesystemCapacity()) AS "Capacity";

Result:

┌─Capacity──┐
│ 39.32 GiB │
└───────────┘

initializeAggregation

Calculates the result of an aggregate function based on a single value. This function can be used to initialize aggregate functions with combinator -State. You can create states of aggregate functions and insert them to columns of type AggregateFunction or use initialized aggregates as default values.

Syntax

initializeAggregation (aggregate_function, arg1, arg2, ..., argN)

Arguments

aggregate_function — Name of the aggregation function to initialize. String.
arg — Arguments of aggregate function.

Returned value(s)

Result of aggregation for every row passed to the function.

The return type is the same as the return type of function, that initializeAggregation takes as first argument.

Example

Query:

SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000));

Result:

┌─uniqMerge(state)─┐
│                3 │
└──────────────────┘

Query:

SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5));

Result:

┌─finalizeAggregation(state)─┬─toTypeName(state)─────────────┐
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
│                          2 │ AggregateFunction(sum, UInt8) │
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
└────────────────────────────┴───────────────────────────────┘

Example with AggregatingMergeTree table engine and AggregateFunction column:

CREATE TABLE metrics
(
    key UInt64,
    value AggregateFunction(sum, UInt64) DEFAULT initializeAggregation('sumState', toUInt64(0))
)
ENGINE = AggregatingMergeTree
ORDER BY key

INSERT INTO metrics VALUES (0, initializeAggregation('sumState', toUInt64(42)))

See Also

arrayReduce

finalizeAggregation

Given a state of aggregate function, this function returns the result of aggregation (or finalized state when using a -State combinator).

Syntax

finalizeAggregation(state)

Arguments

state — State of aggregation. AggregateFunction.

Returned value(s)

Value/values that was aggregated.

Note

The return type is equal to that of any types which were aggregated.

Examples

Query:

SELECT finalizeAggregation(( SELECT countState(number) FROM numbers(10)));

Result:

┌─finalizeAggregation(_subquery16)─┐
│                               10 │
└──────────────────────────────────┘

Query:

SELECT finalizeAggregation(( SELECT sumState(number) FROM numbers(10)));

Result:

┌─finalizeAggregation(_subquery20)─┐
│                               45 │
└──────────────────────────────────┘

Note that NULL values are ignored.

Query:

SELECT finalizeAggregation(arrayReduce('anyState', [NULL, 2, 3]));

Result:

┌─finalizeAggregation(arrayReduce('anyState', [NULL, 2, 3]))─┐
│                                                          2 │
└────────────────────────────────────────────────────────────┘

Combined example:

Query:

WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
    number,
    finalizeAggregation(one_row_sum_state) AS one_row_sum,
    runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(10);

Result:

┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│      0 │           0 │              0 │
│      1 │           1 │              1 │
│      2 │           2 │              3 │
│      3 │           3 │              6 │
│      4 │           4 │             10 │
│      5 │           5 │             15 │
│      6 │           6 │             21 │
│      7 │           7 │             28 │
│      8 │           8 │             36 │
│      9 │           9 │             45 │
└────────┴─────────────┴────────────────┘

See Also

arrayReduce
initializeAggregation

runningAccumulate

Accumulates the states of an aggregate function for each row of a data block.

Note

The state is reset for each new block of data. Because of this error-prone behavior the function is DEPRECATED, please use proper window functions instead.

Syntax

runningAccumulate(agg_state[, grouping]);

Arguments

agg_state — State of the aggregate function. AggregateFunction.
grouping — Grouping key. Optional. The state of the function is reset if the grouping value is changed. It can be any of the supported data types for which the equality operator is defined.

Returned value

Each resulting row contains a result of the aggregate function, accumulated for all the input rows from 0 to the current position. runningAccumulate resets states for each new data block or when the grouping value changes.

Type depends on the aggregate function used.

Examples

Consider how you can use runningAccumulate to find the cumulative sum of numbers without and with grouping.

Query:

SELECT k, runningAccumulate(sum_k) AS res FROM (SELECT number AS k, sumState(k) AS sum_k FROM numbers(10) GROUP BY k ORDER BY k);

Result:

┌─k─┬─res─┐
│ 0 │   0 │
│ 1 │   1 │
│ 2 │   3 │
│ 3 │   6 │
│ 4 │  10 │
│ 5 │  15 │
│ 6 │  21 │
│ 7 │  28 │
│ 8 │  36 │
│ 9 │  45 │
└───┴─────┘

The subquery generates sumState for every number from 0 to 9. sumState returns the state of the sum function that contains the sum of a single number.

The whole query does the following:

For the first row, runningAccumulate takes sumState(0) and returns 0.
For the second row, the function merges sumState(0) and sumState(1) resulting in sumState(0 + 1), and returns 1 as a result.
For the third row, the function merges sumState(0 + 1) and sumState(2) resulting in sumState(0 + 1 + 2), and returns 3 as a result.
The actions are repeated until the block ends.

The following example shows the groupping parameter usage:

Query:

SELECT
    grouping,
    item,
    runningAccumulate(state, grouping) AS res
FROM
(
    SELECT
        toInt8(number / 4) AS grouping,
        number AS item,
        sumState(number) AS state
    FROM numbers(15)
    GROUP BY item
    ORDER BY item ASC
);

Result:

┌─grouping─┬─item─┬─res─┐
│        0 │    0 │   0 │
│        0 │    1 │   1 │
│        0 │    2 │   3 │
│        0 │    3 │   6 │
│        1 │    4 │   4 │
│        1 │    5 │   9 │
│        1 │    6 │  15 │
│        1 │    7 │  22 │
│        2 │    8 │   8 │
│        2 │    9 │  17 │
│        2 │   10 │  27 │
│        2 │   11 │  38 │
│        3 │   12 │  12 │
│        3 │   13 │  25 │
│        3 │   14 │  39 │
└──────────┴──────┴─────┘

As you can see, runningAccumulate merges states for each group of rows separately.

joinGet

The function lets you extract data from the table the same way as from a dictionary. Gets the data from Join tables using the specified join key.

Note

Only supports tables created with the ENGINE = Join(ANY, LEFT, <join_keys>) statement.

Syntax

joinGet(join_storage_table_name, `value_column`, join_keys)

Arguments

join_storage_table_name — an identifier indicating where the search is performed.
value_column — name of the column of the table that contains required data.
join_keys — list of keys.

Note

The identifier is searched for in the default database (see setting default_database in the config file). To override the default database, use USE db_name or specify the database and the table through the separator db_name.db_table as in the example.

Returned value

Returns a list of values corresponded to the list of keys.

Note

If a certain key does not exist in source table then 0 or null will be returned based on join_use_nulls setting during table creation. More info about join_use_nulls in Join operation.

Example

Input table:

CREATE DATABASE db_test;
CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1, 11)(2, 12)(4, 13);
SELECT * FROM db_test.id_val;

┌─id─┬─val─┐
│  4 │  13 │
│  2 │  12 │
│  1 │  11 │
└────┴─────┘

Query:

SELECT number, joinGet(db_test.id_val, 'val', toUInt32(number)) FROM numbers(4);

Result:

   ┌─number─┬─joinGet('db_test.id_val', 'val', toUInt32(number))─┐
1. │      0 │                                                  0 │
2. │      1 │                                                 11 │
3. │      2 │                                                 12 │
4. │      3 │                                                  0 │
   └────────┴────────────────────────────────────────────────────┘

Setting join_use_nulls can be used during table creation to change the behaviour of what gets returned if no key exists in the source table.

CREATE DATABASE db_test;
CREATE TABLE db_test.id_val_nulls(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id) SETTINGS join_use_nulls=1;
INSERT INTO db_test.id_val_nulls VALUES (1, 11)(2, 12)(4, 13);
SELECT * FROM db_test.id_val_nulls;

┌─id─┬─val─┐
│  4 │  13 │
│  2 │  12 │
│  1 │  11 │
└────┴─────┘

Query:

SELECT number, joinGet(db_test.id_val_nulls, 'val', toUInt32(number)) FROM numbers(4);

Result:

   ┌─number─┬─joinGet('db_test.id_val_nulls', 'val', toUInt32(number))─┐
1. │      0 │                                                     ᴺᵁᴸᴸ │
2. │      1 │                                                       11 │
3. │      2 │                                                       12 │
4. │      3 │                                                     ᴺᵁᴸᴸ │
   └────────┴──────────────────────────────────────────────────────────┘

joinGetOrNull

Like joinGet but returns NULL when the key is missing instead of returning the default value.

Syntax

joinGetOrNull(join_storage_table_name, `value_column`, join_keys)

Arguments

join_storage_table_name — an identifier indicating where the search is performed.
value_column — name of the column of the table that contains required data.
join_keys — list of keys.

Note

Returned value

Returns a list of values corresponded to the list of keys.

Note

If a certain key does not exist in source table then NULL is returned for that key.

Example

Input table:

CREATE DATABASE db_test;
CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1, 11)(2, 12)(4, 13);
SELECT * FROM db_test.id_val;

┌─id─┬─val─┐
│  4 │  13 │
│  2 │  12 │
│  1 │  11 │
└────┴─────┘

Query:

SELECT number, joinGetOrNull(db_test.id_val, 'val', toUInt32(number)) FROM numbers(4);

Result:

   ┌─number─┬─joinGetOrNull('db_test.id_val', 'val', toUInt32(number))─┐
1. │      0 │                                                     ᴺᵁᴸᴸ │
2. │      1 │                                                       11 │
3. │      2 │                                                       12 │
4. │      3 │                                                     ᴺᵁᴸᴸ │
   └────────┴──────────────────────────────────────────────────────────┘

catboostEvaluate

Not supported in ClickHouse Cloud

Note

This function is not available in ClickHouse Cloud.

Evaluate an external catboost model. CatBoost is an open-source gradient boosting library developed by Yandex for machine learning. Accepts a path to a catboost model and model arguments (features). Returns Float64.

Syntax

catboostEvaluate(path_to_model, feature_1, feature_2, ..., feature_n)

Example

SELECT feat1, ..., feat_n, catboostEvaluate('/path/to/model.bin', feat_1, ..., feat_n) AS prediction
FROM data_table

Prerequisites

Build the catboost evaluation library

Before evaluating catboost models, the libcatboostmodel.<so|dylib> library must be made available. See CatBoost documentation how to compile it.

Next, specify the path to libcatboostmodel.<so|dylib> in the clickhouse configuration:

<clickhouse>
...
    <catboost_lib_path>/path/to/libcatboostmodel.so</catboost_lib_path>
...
</clickhouse>

For security and isolation reasons, the model evaluation does not run in the server process but in the clickhouse-library-bridge process. At the first execution of catboostEvaluate(), the server starts the library bridge process if it is not running already. Both processes communicate using a HTTP interface. By default, port 9012 is used. A different port can be specified as follows - this is useful if port 9012 is already assigned to a different service.

<library_bridge>
    <port>9019</port>
</library_bridge>

Train a catboost model using libcatboost

See Training and applying models for how to train catboost models from a training data set.

throwIf

Throw an exception if argument x is true.

Syntax

throwIf(x[, message[, error_code]])

Arguments

x - the condition to check.
message - a constant string providing a custom error message. Optional.
error_code - A constant integer providing a custom error code. Optional.

To use the error_code argument, configuration parameter allow_custom_error_code_in_throwif must be enabled.

Example

SELECT throwIf(number = 3, 'Too many') FROM numbers(10);

Result:

↙ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 19.14.1):
Code: 395. DB::Exception: Received from localhost:9000. DB::Exception: Too many.

identity

Returns its argument. Intended for debugging and testing. Allows to cancel using index, and get the query performance of a full scan. When the query is analyzed for possible use of an index, the analyzer ignores everything in identity functions. Also disables constant folding.

Syntax

identity(x)

Example

Query:

SELECT identity(42);

Result:

┌─identity(42)─┐
│           42 │
└──────────────┘

getSetting

Returns the current value of a custom setting.

Syntax

getSetting('custom_setting');

Parameter

custom_setting — The setting name. String.

Returned value

The setting's current value.

Example

SET custom_a = 123;
SELECT getSetting('custom_a');

Result:

See Also

Custom Settings

getSettingOrDefault

Returns the current value of a custom setting or returns the default value specified in the 2nd argument if the custom setting is not set in the current profile.

Syntax

getSettingOrDefault('custom_setting', default_value);

Parameter

custom_setting — The setting name. String.
default_value — Value to return if custom_setting is not set. Value may be of any data type or Null.

Returned value

The setting's current value or default_value if setting is not set.

Example

SELECT getSettingOrDefault('custom_undef1', 'my_value');
SELECT getSettingOrDefault('custom_undef2', 100);
SELECT getSettingOrDefault('custom_undef3', NULL);

Result:

my_value
100
NULL

See Also

Custom Settings

isDecimalOverflow

Checks whether the Decimal value is outside its precision or outside the specified precision.

Syntax

isDecimalOverflow(d, [p])

Arguments

d — value. Decimal.
p — precision. Optional. If omitted, the initial precision of the first argument is used. This parameter can be helpful to migrate data from/to another database or file. UInt8.

Returned values

1 — Decimal value has more digits then allowed by its precision,
0 — Decimal value satisfies the specified precision.

Example

Query:

SELECT isDecimalOverflow(toDecimal32(1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(1000000000, 0)),
       isDecimalOverflow(toDecimal32(-1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(-1000000000, 0));

Result:

1    1    1    1

countDigits

Returns number of decimal digits need to represent a value.

Syntax

countDigits(x)

Arguments

x — Int or Decimal value.

Returned value

Number of digits. UInt8.

Note

For Decimal values takes into account their scales: calculates result over underlying integer type which is (value * scale). For example: countDigits(42) = 2, countDigits(42.000) = 5, countDigits(0.04200) = 4. I.e. you may check decimal overflow for Decimal64 with countDecimal(x) > 18. It's a slow variant of isDecimalOverflow.

Example

Query:

SELECT countDigits(toDecimal32(1, 9)), countDigits(toDecimal32(-1, 9)),
       countDigits(toDecimal64(1, 18)), countDigits(toDecimal64(-1, 18)),
       countDigits(toDecimal128(1, 38)), countDigits(toDecimal128(-1, 38));

Result:

10    10    19    19    39    39

errorCodeToName

The textual name of an error code. LowCardinality(String).

Syntax

errorCodeToName(1)

Result:

UNSUPPORTED_METHOD

tcpPort

Returns native interface TCP port number listened by this server. If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

tcpPort()

Arguments

None.

Returned value

The TCP port number. UInt16.

Example

Query:

SELECT tcpPort();

Result:

┌─tcpPort()─┐
│      9000 │
└───────────┘

See Also

tcp_port

currentProfiles

Returns a list of the current settings profiles for the current user.

The command SET PROFILE could be used to change the current setting profile. If the command SET PROFILE was not used the function returns the profiles specified at the current user's definition (see CREATE USER).

Syntax

currentProfiles()

Returned value

List of the current user settings profiles. Array(String).

enabledProfiles

Returns settings profiles, assigned to the current user both explicitly and implicitly. Explicitly assigned profiles are the same as returned by the currentProfiles function. Implicitly assigned profiles include parent profiles of other assigned profiles, profiles assigned via granted roles, profiles assigned via their own settings, and the main default profile (see the default_profile section in the main server configuration file).

Syntax

enabledProfiles()

Returned value

List of the enabled settings profiles. Array(String).

defaultProfiles

Returns all the profiles specified at the current user's definition (see CREATE USER statement).

Syntax

defaultProfiles()

Returned value

List of the default settings profiles. Array(String).

currentRoles

Returns the roles assigned to the current user. The roles can be changed by the SET ROLE statement. If no SET ROLE statement was not, the function currentRoles returns the same as defaultRoles.

Syntax

currentRoles()

Returned value

A list of the current roles for the current user. Array(String).

enabledRoles

Returns the names of the current roles and the roles, granted to some of the current roles.

Syntax

enabledRoles()

Returned value

List of the enabled roles for the current user. Array(String).

defaultRoles

Returns the roles which are enabled by default for the current user when he logs in. Initially these are all roles granted to the current user (see GRANT), but that can be changed with the SET DEFAULT ROLE statement.

Syntax

defaultRoles()

Returned value

List of the default roles for the current user. Array(String).

getServerPort

Returns the server port number. When the port is not used by the server, throws an exception.

Syntax

getServerPort(port_name)

Arguments

port_name — The name of the server port. String. Possible values:
- 'tcp_port'
- 'tcp_port_secure'
- 'http_port'
- 'https_port'
- 'interserver_http_port'
- 'interserver_https_port'
- 'mysql_port'
- 'postgresql_port'
- 'grpc_port'
- 'prometheus.port'

Returned value

The number of the server port. UInt16.

Example

Query:

SELECT getServerPort('tcp_port');

Result:

┌─getServerPort('tcp_port')─┐
│ 9000                      │
└───────────────────────────┘

queryID

Returns the ID of the current query. Other parameters of a query can be extracted from the system.query_log table via query_id.

In contrast to initialQueryID function, queryID can return different results on different shards (see the example).

Syntax

queryID()

Returned value

The ID of the current query. String

Example

Query:

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT queryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

Result:

┌─count()─┐
│ 3       │
└─────────┘

initialQueryID

Returns the ID of the initial current query. Other parameters of a query can be extracted from the system.query_log table via initial_query_id.

In contrast to queryID function, initialQueryID returns the same results on different shards (see example).

Syntax

initialQueryID()

Returned value

The ID of the initial current query. String

Example

Query:

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

Result:

┌─count()─┐
│ 1       │
└─────────┘

initialQueryStartTime

Returns the start time of the initial current query.

initialQueryStartTime returns the same results on different shards (see example).

Syntax

initialQueryStartTime()

Returned value

The start time of the initial current query. DateTime

Example

Query:

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryStartTime() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

Result:

┌─count()─┐
│ 1       │
└─────────┘

partitionID

Computes the partition ID.

Note

This function is slow and should not be called for large amount of rows.

Syntax

partitionID(x[, y, ...]);

Arguments

x — Column for which to return the partition ID.
y, ... — Remaining N columns for which to return the partition ID (optional).

Returned Value

Partition ID that the row would belong to. String.

Example

Query:

DROP TABLE IF EXISTS tab;

CREATE TABLE tab
(
  i int,
  j int
)
ENGINE = MergeTree
PARTITION BY i
ORDER BY tuple();

INSERT INTO tab VALUES (1, 1), (1, 2), (1, 3), (2, 4), (2, 5), (2, 6);

SELECT i, j, partitionID(i), _partition_id FROM tab ORDER BY i, j;

Result:

┌─i─┬─j─┬─partitionID(i)─┬─_partition_id─┐
│ 1 │ 1 │ 1              │ 1             │
│ 1 │ 2 │ 1              │ 1             │
│ 1 │ 3 │ 1              │ 1             │
└───┴───┴────────────────┴───────────────┘
┌─i─┬─j─┬─partitionID(i)─┬─_partition_id─┐
│ 2 │ 4 │ 2              │ 2             │
│ 2 │ 5 │ 2              │ 2             │
│ 2 │ 6 │ 2              │ 2             │
└───┴───┴────────────────┴───────────────┘

shardNum

Returns the index of a shard which processes a part of data in a distributed query. Indices are started from 1. If a query is not distributed then constant value 0 is returned.

Syntax

shardNum()

Returned value

Shard index or constant 0. UInt32.

Example

In the following example a configuration with two shards is used. The query is executed on the system.one table on every shard.

Query:

CREATE TABLE shard_num_example (dummy UInt8)
    ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT dummy, shardNum(), shardCount() FROM shard_num_example;

Result:

┌─dummy─┬─shardNum()─┬─shardCount()─┐
│     0 │          2 │            2 │
│     0 │          1 │            2 │
└───────┴────────────┴──────────────┘

See Also

Distributed Table Engine

shardCount

Returns the total number of shards for a distributed query. If a query is not distributed then constant value 0 is returned.

Syntax

shardCount()

Returned value

Total number of shards or 0. UInt32.

See Also

shardNum() function example also contains shardCount() function call.

getOSKernelVersion

Returns a string with the current OS kernel version.

Syntax

getOSKernelVersion()

Arguments

None.

Returned value

The current OS kernel version. String.

Example

Query:

SELECT getOSKernelVersion();

Result:

┌─getOSKernelVersion()────┐
│ Linux 4.15.0-55-generic │
└─────────────────────────┘

zookeeperSessionUptime

Returns the uptime of the current ZooKeeper session in seconds.

Syntax

zookeeperSessionUptime()

Arguments

None.

Returned value

Uptime of the current ZooKeeper session in seconds. UInt32.

Example

Query:

SELECT zookeeperSessionUptime();

Result:

┌─zookeeperSessionUptime()─┐
│                      286 │
└──────────────────────────┘

generateRandomStructure

Generates random table structure in a format column1_name column1_type, column2_name column2_type, ....

Syntax

generateRandomStructure([number_of_columns, seed])

Arguments

number_of_columns — The desired number of columns in the result table structure. If set to 0 or Null, the number of columns will be random from 1 to 128. Default value: Null.
seed - Random seed to produce stable results. If seed is not specified or set to Null, it is randomly generated.

All arguments must be constant.

Returned value

Randomly generated table structure. String.

Examples

Query:

SELECT generateRandomStructure()

Result:

┌─generateRandomStructure()─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ c1 Decimal32(5), c2 Date, c3 Tuple(LowCardinality(String), Int128, UInt64, UInt16, UInt8, IPv6), c4 Array(UInt128), c5 UInt32, c6 IPv4, c7 Decimal256(64), c8 Decimal128(3), c9 UInt256, c10 UInt64, c11 DateTime │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query:

SELECT generateRandomStructure(1)

Result:

┌─generateRandomStructure(1)─┐
│ c1 Map(UInt256, UInt16)    │
└────────────────────────────┘

Query:

SELECT generateRandomStructure(NULL, 33)

Result:

┌─generateRandomStructure(NULL, 33)─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ c1 DateTime, c2 Enum8('c2V0' = 0, 'c2V1' = 1, 'c2V2' = 2, 'c2V3' = 3), c3 LowCardinality(Nullable(FixedString(30))), c4 Int16, c5 Enum8('c5V0' = 0, 'c5V1' = 1, 'c5V2' = 2, 'c5V3' = 3), c6 Nullable(UInt8), c7 String, c8 Nested(e1 IPv4, e2 UInt8, e3 UInt16, e4 UInt16, e5 Int32, e6 Map(Date, Decimal256(70))) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Note: the maximum nesting depth of complex types (Array, Tuple, Map, Nested) is limited to 16.

This function can be used together with generateRandom to generate completely random tables.

structureToCapnProtoSchema

Converts ClickHouse table structure to CapnProto schema.

Syntax

structureToCapnProtoSchema(structure)

Arguments

structure — Table structure in a format column1_name column1_type, column2_name column2_type, ....
root_struct_name — Name for root struct in CapnProto schema. Default value - Message;

Returned value

CapnProto schema. String.

Examples

Query:

SELECT structureToCapnProtoSchema('column1 String, column2 UInt32, column3 Array(String)') FORMAT RawBLOB

Result:

@0xf96402dd754d0eb7;

struct Message
{
    column1 @0 : Data;
    column2 @1 : UInt32;
    column3 @2 : List(Data);
}

Query:

SELECT structureToCapnProtoSchema('column1 Nullable(String), column2 Tuple(element1 UInt32, element2 Array(String)), column3 Map(String, String)') FORMAT RawBLOB

Result:

@0xd1c8320fecad2b7f;

struct Message
{
    struct Column1
    {
        union
        {
            value @0 : Data;
            null @1 : Void;
        }
    }
    column1 @0 : Column1;
    struct Column2
    {
        element1 @0 : UInt32;
        element2 @1 : List(Data);
    }
    column2 @1 : Column2;
    struct Column3
    {
        struct Entry
        {
            key @0 : Data;
            value @1 : Data;
        }
        entries @0 : List(Entry);
    }
    column3 @2 : Column3;
}

Query:

SELECT structureToCapnProtoSchema('column1 String, column2 UInt32', 'Root') FORMAT RawBLOB

Result:

@0x96ab2d4ab133c6e1;

struct Root
{
    column1 @0 : Data;
    column2 @1 : UInt32;
}

structureToProtobufSchema

Converts ClickHouse table structure to Protobuf schema.

Syntax

structureToProtobufSchema(structure)

Arguments

structure — Table structure in a format column1_name column1_type, column2_name column2_type, ....
root_message_name — Name for root message in Protobuf schema. Default value - Message;

Returned value

Protobuf schema. String.

Examples

Query:

SELECT structureToProtobufSchema('column1 String, column2 UInt32, column3 Array(String)') FORMAT RawBLOB

Result:

syntax = "proto3";

message Message
{
    bytes column1 = 1;
    uint32 column2 = 2;
    repeated bytes column3 = 3;
}

Query:

SELECT structureToProtobufSchema('column1 Nullable(String), column2 Tuple(element1 UInt32, element2 Array(String)), column3 Map(String, String)') FORMAT RawBLOB

Result:

syntax = "proto3";

message Message
{
    bytes column1 = 1;
    message Column2
    {
        uint32 element1 = 1;
        repeated bytes element2 = 2;
    }
    Column2 column2 = 2;
    map<string, bytes> column3 = 3;
}

Query:

SELECT structureToProtobufSchema('column1 String, column2 UInt32', 'Root') FORMAT RawBLOB

Result:

syntax = "proto3";

message Root
{
    bytes column1 = 1;
    uint32 column2 = 2;
}

formatQuery

Returns a formatted, possibly multi-line, version of the given SQL query.

Throws an exception if the query is not well-formed. To return NULL instead, function formatQueryOrNull() may be used.

Syntax

formatQuery(query)
formatQueryOrNull(query)

Arguments

query - The SQL query to be formatted. String

Returned value

The formatted query. String.

Example

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

Result:

┌─formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3')─┐
│ SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)            │
└───────────────────────────────────────────────────────────────┘

formatQuerySingleLine

Like formatQuery() but the returned formatted string contains no line breaks.

Throws an exception if the query is not well-formed. To return NULL instead, function formatQuerySingleLineOrNull() may be used.

Syntax

formatQuerySingleLine(query)
formatQuerySingleLineOrNull(query)

Arguments

query - The SQL query to be formatted. String

Returned value

The formatted query. String.

Example

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

Result:

┌─formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3')─┐
│ SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)                          │
└─────────────────────────────────────────────────────────────────────────┘

variantElement

Extracts a column with specified type from a Variant column.

Syntax

variantElement(variant, type_name, [, default_value])

Arguments

variant — Variant column. Variant.
type_name — The name of the variant type to extract. String.
default_value - The default value that will be used if variant doesn't have variant with specified type. Can be any type. Optional.

Returned value

Subcolumn of a Variant column with specified type.

Example

CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT v, variantElement(v, 'String'), variantElement(v, 'UInt64'), variantElement(v, 'Array(UInt64)') FROM test;

┌─v─────────────┬─variantElement(v, 'String')─┬─variantElement(v, 'UInt64')─┬─variantElement(v, 'Array(UInt64)')─┐
│ ᴺᵁᴸᴸ          │ ᴺᵁᴸᴸ                        │                        ᴺᵁᴸᴸ │ []                                 │
│ 42            │ ᴺᵁᴸᴸ                        │                          42 │ []                                 │
│ Hello, World! │ Hello, World!               │                        ᴺᵁᴸᴸ │ []                                 │
│ [1,2,3]       │ ᴺᵁᴸᴸ                        │                        ᴺᵁᴸᴸ │ [1,2,3]                            │
└───────────────┴─────────────────────────────┴─────────────────────────────┴────────────────────────────────────┘

variantType

Returns the variant type name for each row of Variant column. If row contains NULL, it returns 'None' for it.

Syntax

variantType(variant)

Arguments

variant — Variant column. Variant.

Returned value

Enum8 column with variant type name for each row.

Example

CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT variantType(v) FROM test;

┌─variantType(v)─┐
│ None           │
│ UInt64         │
│ String         │
│ Array(UInt64)  │
└────────────────┘

SELECT toTypeName(variantType(v)) FROM test LIMIT 1;

┌─toTypeName(variantType(v))──────────────────────────────────────────┐
│ Enum8('None' = -1, 'Array(UInt64)' = 0, 'String' = 1, 'UInt64' = 2) │
└─────────────────────────────────────────────────────────────────────┘

minSampleSizeConversion

Calculates minimum required sample size for an A/B test comparing conversions (proportions) in two samples.

Syntax

minSampleSizeConversion(baseline, mde, power, alpha)

Uses the formula described in this article. Assumes equal sizes of treatment and control groups. Returns the sample size required for one group (i.e. the sample size required for the whole experiment is twice the returned value).

Arguments

baseline — Baseline conversion. Float.
mde — Minimum detectable effect (MDE) as percentage points (e.g. for a baseline conversion 0.25 the MDE 0.03 means an expected change to 0.25 ± 0.03). Float.
power — Required statistical power of a test (1 - probability of Type II error). Float.
alpha — Required significance level of a test (probability of Type I error). Float.

Returned value

A named Tuple with 3 elements:

"minimum_sample_size" — Required sample size. Float64.
"detect_range_lower" — Lower bound of the range of values not detectable with the returned required sample size (i.e. all values less than or equal to "detect_range_lower" are detectable with the provided alpha and power). Calculated as baseline - mde. Float64.
"detect_range_upper" — Upper bound of the range of values not detectable with the returned required sample size (i.e. all values greater than or equal to "detect_range_upper" are detectable with the provided alpha and power). Calculated as baseline + mde. Float64.

Example

The following query calculates the required sample size for an A/B test with baseline conversion of 25%, MDE of 3%, significance level of 5%, and the desired statistical power of 80%:

SELECT minSampleSizeConversion(0.25, 0.03, 0.80, 0.05) AS sample_size;

Result:

┌─sample_size───────────────────┐
│ (3396.077603219163,0.22,0.28) │
└───────────────────────────────┘

minSampleSizeContinuous

Calculates minimum required sample size for an A/B test comparing means of a continuous metric in two samples.

Syntax

minSampleSizeContinous(baseline, sigma, mde, power, alpha)

Alias: minSampleSizeContinous

Uses the formula described in this article. Assumes equal sizes of treatment and control groups. Returns the required sample size for one group (i.e. the sample size required for the whole experiment is twice the returned value). Also assumes equal variance of the test metric in treatment and control groups.

Arguments

baseline — Baseline value of a metric. Integer or Float.
sigma — Baseline standard deviation of a metric. Integer or Float.
mde — Minimum detectable effect (MDE) as percentage of the baseline value (e.g. for a baseline value 112.25 the MDE 0.03 means an expected change to 112.25 ± 112.25*0.03). Integer or Float.
power — Required statistical power of a test (1 - probability of Type II error). Integer or Float.
alpha — Required significance level of a test (probability of Type I error). Integer or Float.

Returned value

A named Tuple with 3 elements:

"minimum_sample_size" — Required sample size. Float64.
"detect_range_lower" — Lower bound of the range of values not detectable with the returned required sample size (i.e. all values less than or equal to "detect_range_lower" are detectable with the provided alpha and power). Calculated as baseline * (1 - mde). Float64.
"detect_range_upper" — Upper bound of the range of values not detectable with the returned required sample size (i.e. all values greater than or equal to "detect_range_upper" are detectable with the provided alpha and power). Calculated as baseline * (1 + mde). Float64.

Example

The following query calculates the required sample size for an A/B test on a metric with baseline value of 112.25, standard deviation of 21.1, MDE of 3%, significance level of 5%, and the desired statistical power of 80%:

SELECT minSampleSizeContinous(112.25, 21.1, 0.03, 0.80, 0.05) AS sample_size;

Result:

┌─sample_size───────────────────────────┐
│ (616.2931945826209,108.8825,115.6175) │
└───────────────────────────────────────┘

connectionId

Retrieves the connection ID of the client that submitted the current query and returns it as a UInt64 integer.

Syntax

connectionId()

Alias: connection_id.

Parameters

None.

Returned value

The current connection ID. UInt64.

Implementation details

This function is most useful in debugging scenarios or for internal purposes within the MySQL handler. It was created for compatibility with MySQL's CONNECTION_ID function It is not typically used in production queries.

Example

Query:

SELECT connectionId();

getClientHTTPHeader

Get the value of an HTTP header.

If there is no such header or the current request is not performed via the HTTP interface, the function returns an empty string. Certain HTTP headers (e.g., Authentication and X-ClickHouse-*) are restricted.

The function requires the setting allow_get_client_http_header to be enabled. The setting is not enabled by default for security reasons, because some headers, such as Cookie, could contain sensitive info.

HTTP headers are case sensitive for this function.

If the function is used in the context of a distributed query, it returns non-empty result only on the initiator node.

showCertificate

Shows information about the current server's Secure Sockets Layer (SSL) certificate if it has been configured. See Configuring SSL-TLS for more information on how to configure ClickHouse to use OpenSSL certificates to validate connections.

Syntax

showCertificate()

Returned value

Map of key-value pairs relating to the configured SSL certificate. Map(String, String).

Example

Query:

SELECT showCertificate() FORMAT LineAsString;

Result:

{'version':'1','serial_number':'2D9071D64530052D48308473922C7ADAFA85D6C5','signature_algo':'sha256WithRSAEncryption','issuer':'/CN=marsnet.local CA','not_before':'May  7 17:01:21 2024 GMT','not_after':'May  7 17:01:21 2025 GMT','subject':'/CN=chnode1','pkey_algo':'rsaEncryption'}

lowCardinalityIndices

Returns the position of a value in the dictionary of a LowCardinality column. Positions start at 1. Since LowCardinality have per-part dictionaries, this function may return different positions for the same value in different parts.

Syntax

lowCardinalityIndices(col)

Arguments

col — a low cardinality column. LowCardinality.

Returned value

The position of the value in the dictionary of the current part. UInt64.

Example

Query:

DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityIndices(s) FROM test;

Result:

   ┌─s──┬─lowCardinalityIndices(s)─┐
1. │ ab │                        1 │
2. │ cd │                        2 │
3. │ ab │                        1 │
4. │ ab │                        1 │
5. │ df │                        3 │
   └────┴──────────────────────────┘
    ┌─s──┬─lowCardinalityIndices(s)─┐
 6. │ ef │                        1 │
 7. │ cd │                        2 │
 8. │ ab │                        3 │
 9. │ cd │                        2 │
10. │ ef │                        1 │
    └────┴──────────────────────────┘

lowCardinalityKeys

Returns the dictionary values of a LowCardinality column. If the block is smaller or larger than the dictionary size, the result will be truncated or extended with default values. Since LowCardinality have per-part dictionaries, this function may return different dictionary values in different parts.

Syntax

lowCardinalityKeys(col)

Arguments

col — a low cardinality column. LowCardinality.

Returned value

The dictionary keys. UInt64.

Example

Query:

DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityKeys(s) FROM test;

Result:

   ┌─s──┬─lowCardinalityKeys(s)─┐
1. │ ef │                       │
2. │ cd │ ef                    │
3. │ ab │ cd                    │
4. │ cd │ ab                    │
5. │ ef │                       │
   └────┴───────────────────────┘
    ┌─s──┬─lowCardinalityKeys(s)─┐
 6. │ ab │                       │
 7. │ cd │ ab                    │
 8. │ ab │ cd                    │
 9. │ ab │ df                    │
10. │ df │                       │
    └────┴───────────────────────┘

displayName

Returns the value of display_name from config or server Fully Qualified Domain Name (FQDN) if not set.

Syntax

displayName()

Returned value

Value of display_name from config or server FQDN if not set. String.

Example

The display_name can be set in config.xml. Taking for example a server with display_name configured to 'production':

<!-- It is the name that will be shown in the clickhouse-client.
     By default, anything with "production" will be highlighted in red in query prompt.
-->
<display_name>production</display_name>

Query:

SELECT displayName();

Result:

┌─displayName()─┐
│ production    │
└───────────────┘

transactionID

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Returns the ID of a transaction.

Note

This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:

<clickhouse>
  <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>

For more information see the page Transactional (ACID) support.

Syntax

transactionID()

Returned value

Returns a tuple consisting of start_csn, local_tid and host_id. Tuple.
start_csn: Global sequential number, the newest commit timestamp that was seen when this transaction began. UInt64.
local_tid: Local sequential number that is unique for each transaction started by this host within a specific start_csn. UInt64.
host_id: UUID of the host that has started this transaction. UUID.

Example

Query:

BEGIN TRANSACTION;
SELECT transactionID();
ROLLBACK;

Result:

┌─transactionID()────────────────────────────────┐
│ (32,34,'0ee8b069-f2bb-4748-9eae-069c85b5252b') │
└────────────────────────────────────────────────┘

transactionLatestSnapshot

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Returns the newest snapshot (Commit Sequence Number) of a transaction that is available for reading.

Note

This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:

<clickhouse>
  <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>

For more information see the page Transactional (ACID) support.

Syntax

transactionLatestSnapshot()

Returned value

Returns the latest snapshot (CSN) of a transaction. UInt64

Example

Query:

BEGIN TRANSACTION;
SELECT transactionLatestSnapshot();
ROLLBACK;

Result:

┌─transactionLatestSnapshot()─┐
│                          32 │
└─────────────────────────────┘

transactionOldestSnapshot

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Returns the oldest snapshot (Commit Sequence Number) that is visible for some running transaction.

Note

This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:

<clickhouse>
  <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>

For more information see the page Transactional (ACID) support.

Syntax

transactionOldestSnapshot()

Returned value

Returns the oldest snapshot (CSN) of a transaction. UInt64

Example

Query:

BEGIN TRANSACTION;
SELECT transactionLatestSnapshot();
ROLLBACK;

Result:

┌─transactionOldestSnapshot()─┐
│                          32 │
└─────────────────────────────┘

getSubcolumn

Takes a table expression or identifier and constant string with the name of the sub-column, and returns the requested sub-column extracted from the expression.

Syntax

getSubcolumn(col_name, subcol_name)

Arguments

col_name — Table expression or identifier. Expression, Identifier.
subcol_name — The name of the sub-column. String.

Returned value

Returns the extracted sub-column.

Example

Query:

CREATE TABLE t_arr (arr Array(Tuple(subcolumn1 UInt32, subcolumn2 String))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_arr VALUES ([(1, 'Hello'), (2, 'World')]), ([(3, 'This'), (4, 'is'), (5, 'subcolumn')]);
SELECT getSubcolumn(arr, 'subcolumn1'), getSubcolumn(arr, 'subcolumn2') FROM t_arr;

Result:

   ┌─getSubcolumn(arr, 'subcolumn1')─┬─getSubcolumn(arr, 'subcolumn2')─┐
1. │ [1,2]                           │ ['Hello','World']               │
2. │ [3,4,5]                         │ ['This','is','subcolumn']       │
   └─────────────────────────────────┴─────────────────────────────────┘

getTypeSerializationStreams

Enumerates stream paths of a data type.

Note

This function is intended for use by developers.

Syntax

getTypeSerializationStreams(col)

Arguments

col — Column or string representation of a data-type from which the data type will be detected.

Returned value

Returns an array with all the serialization sub-stream paths.Array(String).

Examples

Query:

SELECT getTypeSerializationStreams(tuple('a', 1, 'b', 2));

Result:

   ┌─getTypeSerializationStreams(('a', 1, 'b', 2))─────────────────────────────────────────────────────────────────────────┐
1. │ ['{TupleElement(1), Regular}','{TupleElement(2), Regular}','{TupleElement(3), Regular}','{TupleElement(4), Regular}'] │
   └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query:

SELECT getTypeSerializationStreams('Map(String, Int64)');

Result:

   ┌─getTypeSerializationStreams('Map(String, Int64)')────────────────────────────────────────────────────────────────┐
1. │ ['{ArraySizes}','{ArrayElements, TupleElement(keys), Regular}','{ArrayElements, TupleElement(values), Regular}'] │
   └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

globalVariable

Takes a constant string argument and returns the value of the global variable with that name. This function is intended for compatibility with MySQL and not needed or useful for normal operation of ClickHouse. Only few dummy global variables are defined.

Syntax

globalVariable(name)

Arguments

name — Global variable name. String.

Returned value

Returns the value of variable name.

Example

Query:

SELECT globalVariable('max_allowed_packet');

Result:

┌─globalVariable('max_allowed_packet')─┐
│                             67108864 │
└──────────────────────────────────────┘

getMaxTableNameLengthForDatabase

Returns the maximum table name length in a specified database.

Syntax

getMaxTableNameLengthForDatabase(database_name)

Arguments

database_name — The name of the specified database. String.

Returned value

Returns the length of the maximum table name.

Example

Query:

SELECT getMaxTableNameLengthForDatabase('default');

Result:

┌─getMaxTableNameLengthForDatabase('default')─┐
│                                         206 │
└─────────────────────────────────────────────┘

getServerSetting

Returns the current value of one of the server settings

Syntax

getServerSetting('server_setting');

Parameter

server_setting — The setting name. String.

Returned value

The server setting's current value.

Example

SELECT getServerSetting('allow_use_jemalloc_memory');

Result:

┌─getServerSetting('allow_use_jemalloc_memory')─┐
│ true                                          │
└───────────────────────────────────────────────┘

getMergeTreeSetting

Returns the current value of one of the merge tree settings

Syntax

getMergeTreeSetting('merge_tree_setting');

Parameter

merge_tree_setting — The setting name. String.

Returned value

The merge tree setting's current value.

Example

SELECT getMergeTreeSetting('index_granularity');

Result:

┌─getMergeTree(index_granularity')─┐
│                     8192         │
└──────────────────────────────────┘

FQDN

Introduced in: v20.1

Returns the fully qualified domain name of the ClickHouse server.

Syntax

fqdn()

Arguments

None. Returned value

Returns the fully qualified domain name of the ClickHouse server. String

Examples

Usage example

SELECT fqdn()

┌─FQDN()──────────────────────────┐
│ clickhouse.us-east-2.internal │
└─────────────────────────────────┘

MACNumToString

Introduced in: v1.1

Interprets a UInt64 number as a MAC address in big endian format. Returns the corresponding MAC address in format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form) as string.

Syntax

MACNumToString(num)

Arguments

num — UInt64 number. UInt64

Returned value

Returns a MAC address in format AA:BB:CC:DD:EE:FF. String

Examples

Usage example

SELECT MACNumToString(149809441867716) AS mac_address;

┌─mac_address───────┐
│ 88:00:11:22:33:44 │
└───────────────────┘

MACStringToNum

Introduced in: v1.1

The inverse function of MACNumToString. If the MAC address has an invalid format, it returns 0.

Syntax

MACStringToNum(s)

Arguments

s — MAC address string. String

Returned value

Returns a UInt64 number. UInt64

Examples

Usage example

SELECT MACStringToNum('01:02:03:04:05:06') AS mac_numeric;

1108152157446

MACStringToOUI

Introduced in: v1.1

Syntax

MACStringToOUI(s)

Arguments

s — MAC address string. String

Returned value

First three octets as UInt64 number. UInt64

Examples

Usage example

SELECT MACStringToOUI('00:50:56:12:34:56') AS oui;

__filterContains

Introduced in: v25.10

Special function for JOIN runtime filtering.

Syntax

__filterContains(filter_name, key)

Arguments

filter_name — Internal name of runtime filter. It is built by BuildRuntimeFilterStep. String
key — Value of any type that is checked to be present in the filter

Returned value

True if the key was found in the filter Bool

Examples

Example

This function is not supposed to be used in user queries. It might be added to query plan during optimization.

__patchPartitionID

Introduced in: v25.5

Internal function. Receives the name of a part and a hash of patch part's column names. Returns the name of partition of patch part. The argument must be a correct name of part, the behaviour is undefined otherwise.

Syntax

Arguments

None. Returned value

Examples

bar

Introduced in: v1.1

Builds a bar chart. Draws a band with width proportional to (x - min) and equal to width characters when x = max. The band is drawn with accuracy to one eighth of a symbol.

Syntax

bar(x, min, max[, width])

Arguments

x — Size to display. (U)Int* or Float* or Decimal
min — The minimum value. const Int64
max — The maximum value. const Int64
width — Optional. The width of the bar in characters. The default is 80. const (U)Int* or const Float* or const Decimal

Returned value

Returns a unicode-art bar string. String

Examples

Usage example

SELECT
toHour(EventTime) AS h,
count() AS c,
bar(c, 0, 600000, 20) AS bar
FROM test.hits
GROUP BY h
ORDER BY h ASC

┌──h─┬──────c─┬─bar────────────────┐
│  0 │ 292907 │ █████████▋         │
│  1 │ 180563 │ ██████             │
│  2 │ 114861 │ ███▋               │
│  3 │  85069 │ ██▋                │
│  4 │  68543 │ ██▎                │
│  5 │  78116 │ ██▌                │
│  6 │ 113474 │ ███▋               │
│  7 │ 170678 │ █████▋             │
│  8 │ 278380 │ █████████▎         │
│  9 │ 391053 │ █████████████      │
│ 10 │ 457681 │ ███████████████▎   │
│ 11 │ 493667 │ ████████████████▍  │
│ 12 │ 509641 │ ████████████████▊  │
│ 13 │ 522947 │ █████████████████▍ │
│ 14 │ 539954 │ █████████████████▊ │
│ 15 │ 528460 │ █████████████████▌ │
│ 16 │ 539201 │ █████████████████▊ │
│ 17 │ 523539 │ █████████████████▍ │
│ 18 │ 506467 │ ████████████████▊  │
│ 19 │ 520915 │ █████████████████▎ │
│ 20 │ 521665 │ █████████████████▍ │
│ 21 │ 542078 │ ██████████████████ │
│ 22 │ 493642 │ ████████████████▍  │
│ 23 │ 400397 │ █████████████▎     │
└────┴────────┴────────────────────┘

blockNumber

Introduced in: v1.1

Returns a monotonically increasing sequence number of the block containing the row. The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate.

Syntax

blockNumber()

Arguments

None. Returned value

Sequence number of the data block where the row is located. UInt64

Examples

Basic usage

SELECT blockNumber()
FROM
(
    SELECT *
    FROM system.numbers
    LIMIT 10
) SETTINGS max_block_size = 2

┌─blockNumber()─┐
│             7 │
│             7 │
└───────────────┘
┌─blockNumber()─┐
│             8 │
│             8 │
└───────────────┘
┌─blockNumber()─┐
│             9 │
│             9 │
└───────────────┘
┌─blockNumber()─┐
│            10 │
│            10 │
└───────────────┘
┌─blockNumber()─┐
│            11 │
│            11 │
└───────────────┘

blockSerializedSize

Introduced in: v20.3

Returns the uncompressed size in bytes of a block of values on disk.

Syntax

blockSerializedSize(x1[, x2[, ...]])

Arguments

x1[, x2, ...] — Any number of values for which to get the uncompressed size of the block. Any

Returned value

Returns the number of bytes that will be written to disk for a block of values without compression. UInt64

Examples

Usage example

SELECT blockSerializedSize(maxState(1)) AS x;

┌─x─┐
│ 2 │
└───┘

blockSize

Introduced in: v1.1

In ClickHouse, queries are processed in blocks (chunks). This function returns the size (row count) of the block the function is called on.

Syntax

blockSize()

Arguments

None. Returned value

Returns the number of rows in the current block. UInt64

Examples

Usage example

SELECT blockSize()
FROM system.numbers LIMIT 5

┌─blockSize()─┐
│           5 │
│           5 │
│           5 │
│           5 │
│           5 │
└─────────────┘

buildId

Introduced in: v20.5

Syntax

buildId()

Arguments

None. Returned value

Returns the build ID. String

Examples

Usage example

SELECT buildId()

┌─buildId()────────────────────────────────┐
│ AB668BEF095FAA6BD26537F197AC2AF48A927FB4 │
└──────────────────────────────────────────┘

byteSize

Introduced in: v21.1

Returns an estimation of the uncompressed byte size of its arguments in memory. For String arguments, the function returns the string length + 8 (length). If the function has multiple arguments, the function accumulates their byte sizes.

Syntax

byteSize(arg1[, arg2, ...])

Arguments

arg1[, arg2, ...] — Values of any data type for which to estimate the uncompressed byte size. Any

Returned value

Returns an estimation of the byte size of the arguments in memory. UInt64

Examples

Usage example

SELECT byteSize('string')

┌─byteSize('string')─┐
│                 15 │
└────────────────────┘

Multiple arguments

SELECT byteSize(NULL, 1, 0.3, '')

┌─byteSize(NULL, 1, 0.3, '')─┐
│                         19 │
└────────────────────────────┘

catboostEvaluate

Introduced in: v22.9

Evaluate an external catboost model. CatBoost is an open-source gradient boosting library developed by Yandex for machine learning. Accepts a path to a catboost model and model arguments (features).

Prerequisites

Build the catboost evaluation library

Before evaluating catboost models, the libcatboostmodel.<so|dylib> library must be made available. See CatBoost documentation how to compile it.

Next, specify the path to libcatboostmodel.<so|dylib> in the clickhouse configuration:

<clickhouse>
...
    <catboost_lib_path>/path/to/libcatboostmodel.so</catboost_lib_path>
...
</clickhouse>

<library_bridge>
    <port>9019</port>
</library_bridge>

Train a catboost model using libcatboost

See Training and applying models for how to train catboost models from a training data set.

Syntax

catboostEvaluate(path_to_model, feature_1[, feature_2, ..., feature_n])

Arguments

path_to_model — Path to catboost model. const String
feature — One or more model features/arguments. Float*

Returned value

Returns the model evaluation result. Float64

Examples

catboostEvaluate

SELECT catboostEvaluate('/root/occupy.bin', Temperature, Humidity, Light, CO2, HumidityRatio) AS prediction FROM occupancy LIMIT 1

4.695691092573497

colorOKLCHToSRGB

Introduced in: v25.7

Converts a colour from the OKLCH perceptual colour space to the familiar sRGB colour space.

If L is outside the range [0...1], C is negative, or H is outside the range [0...360], the result is implementation-defined.

Note

OKLCH is a cylindrical version of the OKLab colour space. It's three coordinates are L (the lightness in the range [0...1]), C (chroma >= 0) and H (hue in degrees from [0...360])**. OKLab/OKLCH is designed to be perceptually uniform while remaining cheap to compute.

The conversion is the inverse of colorSRGBToOKLCH:

OKLCH to OKLab.
OKLab to Linear sRGB
Linear sRGB to sRGB

The second argument gamma is used at the last stage.

For references of colors in OKLCH space, and how they correspond to sRGB colors please see https://oklch.com/.

Syntax

colorOKLCHToSRGB(tuple [, gamma])

Arguments

tuple — A tuple of three numeric values L, C, H, where L is in the range [0...1], C >= 0 and H is in the range [0...360]. Tuple(Float64, Float64, Float64)
gamma — Optional. The exponent that is used to transform linear sRGB back to sRGB by applying (x ^ (1 / gamma)) * 255 for each channel x. Defaults to 2.2. Float64

Returned value

Returns a tuple (R, G, B) representing sRGB color values. Tuple(Float64, Float64, Float64)

Examples

Convert OKLCH to sRGB

SELECT colorOKLCHToSRGB((0.4466, 0.0991, 45.44), 2.2) AS rgb
WITH colorOKLCHToSRGB((0.7, 0.1, 54)) as t SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB

┌─rgb──────────────────────────────────────────────────────┐
│ (127.03349738778945,66.06672044472008,37.11802592155851) │
└──────────────────────────────────────────────────────────┘
┌─RGB──────────┐
│ (205,139,97) │
└──────────────┘

colorSRGBToOKLCH

Introduced in: v25.7

Converts a colour encoded in the sRGB colour space to the perceptually uniform OKLCH colour space.

If any input channel is outside [0...255] or the gamma value is non-positive, the behaviour is implementation-defined.

Note

OKLCH is a cylindrical version of the OKLab colour space. It's three coordinates are L (the lightness in the range [0...1]), C (chroma >= 0) and H (the hue in degrees from [0...360]). OKLab/OKLCH is designed to be perceptually uniform while remaining cheap to compute.

The conversion consists of three stages:

sRGB to Linear sRGB
Linear sRGB to OKLab
OKLab to OKLCH.

For references of colors in the OKLCH space, and how they correspond to sRGB colors, please see https://OKLCH.com/.

Syntax

colorSRGBToOKLCH(tuple[, gamma])

Arguments

tuple — Tuple of three values R, G, B in the range [0...255]. Tuple(UInt8, UInt8, UInt8)
gamma — Optional. Exponent that is used to linearize sRGB by applying (x / 255)^gamma to each channel x. Defaults to 2.2. Float64

Returned value

Returns a tuple (L, C, H) representing the OKLCH color space values. Tuple(Float64, Float64, Float64)

Examples

Convert sRGB to OKLCH

SELECT colorSRGBToOKLCH((128, 64, 32), 2.2) AS lch

┌─lch─────────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.10442699545678624,45.907345481930236) │
└─────────────────────────────────────────────────────────────┘

connectionId

Introduced in: v21.3

Returns the connection ID of the client that submitted the current query. This function is most useful in debugging scenarios. It was created for compatibility with MySQL's CONNECTION_ID function. It is not typically used in production queries.

Syntax

connectionId()

Arguments

None. Returned value

Returns the connection ID of the current client. UInt64

Examples

Usage example

SELECT connectionId();

┌─connectionId()─┐
│              0 │
└────────────────┘

countDigits

Introduced in: v20.8

Returns the number of decimal digits needed to represent a value.

Note

This function takes into account the scales of decimal values i.e., it calculates the result over the underlying integer type which is (value * scale).

For example:

countDigits(42) = 2
countDigits(42.000) = 5
countDigits(0.04200) = 4

Tip

You can check decimal overflow for Decimal64 with countDigits(x) > 18, although it is slower than isDecimalOverflow.

Syntax

countDigits(x)

Arguments

x — An integer or decimal value. (U)Int* or Decimal

Returned value

Returns the number of digits needed to represent x. UInt8

Examples

Usage example

SELECT countDigits(toDecimal32(1, 9)), countDigits(toDecimal32(-1, 9)),
       countDigits(toDecimal64(1, 18)), countDigits(toDecimal64(-1, 18)),
       countDigits(toDecimal128(1, 38)), countDigits(toDecimal128(-1, 38));

┌─countDigits(toDecimal32(1, 9))─┬─countDigits(toDecimal32(-1, 9))─┬─countDigits(toDecimal64(1, 18))─┬─countDigits(toDecimal64(-1, 18))─┬─countDigits(toDecimal128(1, 38))─┬─countDigits(toDecimal128(-1, 38))─┐
│                             10 │                              10 │                              19 │                               19 │                               39 │                                39 │
└────────────────────────────────┴─────────────────────────────────┴─────────────────────────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────────────────────────┘

currentDatabase

Introduced in: v1.1

Returns the name of the current database. Useful in table engine parameters of CREATE TABLE queries where you need to specify the database.

Also see the SET statement.

Syntax

currentDatabase()

Arguments

None. Returned value

Returns the current database name. String

Examples

Usage example

SELECT currentDatabase()

┌─currentDatabase()─┐
│ default           │
└───────────────────┘

currentProfiles

Introduced in: v21.9

Returns an array of the setting profiles for the current user.

Syntax

currentProfiles()

Arguments

None. Returned value

Returns an array of setting profiles for the current user. Array(String)

Examples

Usage example

SELECT currentProfiles();

┌─currentProfiles()─────────────────────────────┐
│ ['default', 'readonly_user', 'web_analytics'] │
└───────────────────────────────────────────────┘

currentQueryID

Introduced in: v

Returns current Query id.

Syntax

currentQueryID()

Arguments

None. Returned value

Examples

Example

SELECT currentQueryID();

┌─currentQueryID()─────────────────────┐
│ 1280d0e8-1a08-4524-be6e-77975bb68e7d │
└──────────────────────────────────────┘

currentRoles

Introduced in: v21.9

Returns an array of the roles which are assigned to the current user.

Syntax

currentRoles()

Arguments

None. Returned value

Returns an array of the roles which are assigned to the current user. Array(String)

Examples

Usage example

SELECT currentRoles();

┌─currentRoles()─────────────────────────────────┐
│ ['sql-console-role:jane.smith@clickhouse.com'] │
└────────────────────────────────────────────────┘

currentSchemas

Introduced in: v23.7

Same as function currentDatabase but

accepts a boolean argument which is ignored
returns the database name as an array with a single value.

Function currentSchemas only exists for compatibility with PostgreSQL. Please use currentDatabase instead.

Also see the SET statement.

Syntax

currentSchemas(bool)

Arguments

bool — A boolean value, which is ignored. Bool

Returned value

Returns a single-element array with the name of the current database. Array(String)

Examples

Usage example

SELECT currentSchemas(true)

┌─currentSchemas(true)─┐
│ ['default']          │
└──────────────────────┘

currentUser

Introduced in: v20.1

Returns the name of the current user. In case of a distributed query, the name of the user who initiated the query is returned.

Syntax

currentUser()

Arguments

None. Returned value

Returns the name of the current user, otherwise the login of the user who initiated the query. String

Examples

Usage example

SELECT currentUser()

┌─currentUser()─┐
│ default       │
└───────────────┘

defaultProfiles

Introduced in: v21.9

Returns an array of default setting profile names for the current user.

Syntax

defaultProfiles()

Arguments

None. Returned value

Returns an array of default setting profile names for the current user. Array(String)

Examples

Usage example

SELECT defaultProfiles();

┌─defaultProfiles()─┐
│ ['default']       │
└───────────────────┘

defaultRoles

Introduced in: v21.9

Returns an array of default roles for the current user.

Syntax

defaultRoles()

Arguments

None. Returned value

Returns an array of default roles for the current user. Array(String)

Examples

Usage example

SELECT defaultRoles();

┌─defaultRoles()─────────────────────────────────┐
│ ['sql-console-role:jane.smith@clickhouse.com'] │
└────────────────────────────────────────────────┘

defaultValueOfArgumentType

Introduced in: v1.1

Returns the default value for a given data type. Does not include default values for custom columns set by the user.

Syntax

defaultValueOfArgumentType(expression)

Arguments

expression — Arbitrary type of value or an expression that results in a value of an arbitrary type. Any

Returned value

Returns 0 for numbers, an empty string for strings or NULL for Nullable types. UInt8 or String or NULL

Examples

Usage example

SELECT defaultValueOfArgumentType(CAST(1 AS Int8));

┌─defaultValueOfArgumentType(CAST(1, 'Int8'))─┐
│                                           0 │
└─────────────────────────────────────────────┘

Nullable example

SELECT defaultValueOfArgumentType(CAST(1 AS Nullable(Int8)));

┌─defaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))─┐
│                                                  ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────────┘

defaultValueOfTypeName

Introduced in: v1.1

Returns the default value for the given type name.

Syntax

defaultValueOfTypeName(type)

Arguments

type — A string representing a type name. String

Returned value

Returns the default value for the given type name: 0 for numbers, an empty string for strings, or NULL for Nullable UInt8 or String or NULL

Examples

Usage example

SELECT defaultValueOfTypeName('Int8');

┌─defaultValueOfTypeName('Int8')─┐
│                              0 │
└────────────────────────────────┘

Nullable example

SELECT defaultValueOfTypeName('Nullable(Int8)');

┌─defaultValueOfTypeName('Nullable(Int8)')─┐
│                                     ᴺᵁᴸᴸ │
└──────────────────────────────────────────┘

displayName

Introduced in: v22.11

Returns the value of display_name from config or the server's Fully Qualified Domain Name (FQDN) if not set.

Syntax

displayName()

Arguments

None. Returned value

Returns the value of display_name from config or server FQDN if not set. String

Examples

Usage example

SELECT displayName();

┌─displayName()─┐
│ production    │
└───────────────┘

dumpColumnStructure

Introduced in: v1.1

Outputs a detailed description of the internal structure of a column and its data type.

Syntax

dumpColumnStructure(x)

Arguments

x — Value for which to get the description of. Any

Returned value

Returns a description of the column structure used for representing the value. String

Examples

Usage example

SELECT dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'));

┌─dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ DateTime, Const(size = 1, UInt32(size = 1))                  │
└──────────────────────────────────────────────────────────────┘

enabledProfiles

Introduced in: v21.9

Returns an array of setting profile names which are enabled for the current user.

Syntax

enabledProfiles()

Arguments

None. Returned value

Returns an array of setting profile names which are enabled for the current user. Array(String)

Examples

Usage example

SELECT enabledProfiles();

┌─enabledProfiles()─────────────────────────────────────────────────┐
│ ['default', 'readonly_user', 'web_analytics', 'batch_processing'] │
└───────────────────────────────────────────────────────────────────┘

enabledRoles

Introduced in: v21.9

Returns an array of the roles which are enabled for the current user.

Syntax

enabledRoles()

Arguments

None. Returned value

Returns an array of role names which are enabled for the current user. Array(String)

Examples

Usage example

SELECT enabledRoles();

┌─enabledRoles()─────────────────────────────────────────────────┐
│ ['general_data', 'sql-console-role:jane.smith@clickhouse.com'] │
└────────────────────────────────────────────────────────────────┘

errorCodeToName

Introduced in: v20.12

Returns the textual name of a numeric ClickHouse error code. The mapping from numeric error codes to error names is available here.

Syntax

errorCodeToName(error_code)

Arguments

error_code — ClickHouse error code. (U)Int* or Float* or Decimal

Returned value

Returns the textual name of error_code. String

Examples

Usage example

SELECT errorCodeToName(252);

┌─errorCodeToName(252)─┐
│ TOO_MANY_PARTS       │
└──────────────────────┘

file

Introduced in: v21.3

Reads a file as a string and loads the data into the specified column. The file content is not interpreted.

Also see the file table function.

Syntax

file(path[, default])

Arguments

path — The path of the file relative to the user_files_path. Supports wildcards *, **, ?, {abc,def} and {N..M} where N, M are numbers and 'abc', 'def' are strings. String
default — The value returned if the file does not exist or cannot be accessed. String or NULL

Returned value

Returns the file content as a string. String

Examples

Insert files into a table

INSERT INTO table SELECT file('a.txt'), file('b.txt');

filesystemAvailable

Introduced in: v20.1

Returns the amount of free space in the filesystem hosting the database persistence. The returned value is always smaller than the total free space (filesystemUnreserved) because some space is reserved for the operating system.

Syntax

filesystemAvailable([disk_name])

Arguments

disk_name — Optional. The disk name to find the amount of free space for. If omitted, uses the default disk. String or FixedString

Returned value

Returns the amount of remaining space available in bytes. UInt64

Examples

Usage example

SELECT formatReadableSize(filesystemAvailable()) AS "Available space";

┌─Available space─┐
│ 30.75 GiB       │
└─────────────────┘

filesystemCapacity

Introduced in: v20.1

Returns the capacity of the filesystem in bytes. Needs the path to the data directory to be configured.

Syntax

filesystemCapacity([disk_name])

Arguments

disk_name — Optional. The disk name to get the capacity for. If omitted, uses the default disk. String or FixedString

Returned value

Returns the capacity of the filesystem in bytes. UInt64

Examples

Usage example

SELECT formatReadableSize(filesystemCapacity()) AS "Capacity";

┌─Capacity──┐
│ 39.32 GiB │
└───────────┘

filesystemUnreserved

Introduced in: v22.12

Returns the total amount of free space on the filesystem hosting the database persistence (previously filesystemFree). See also filesystemAvailable.

Syntax

filesystemUnreserved([disk_name])

Arguments

disk_name — Optional. The disk name for which to find the total amount of free space. If omitted, uses the default disk. String or FixedString

Returned value

Returns the amount of free space in bytes. UInt64

Examples

Usage example

SELECT formatReadableSize(filesystemUnreserved()) AS "Free space";

┌─Free space─┐
│ 32.39 GiB  │
└────────────┘

finalizeAggregation

Introduced in: v1.1

Given an aggregation state, this function returns the result of aggregation (or the finalized state when using a -State combinator).

Syntax

finalizeAggregation(state)

Arguments

state — State of aggregation. AggregateFunction

Returned value

Returns the finalized result of aggregation. Any

Examples

Usage example

SELECT finalizeAggregation(arrayReduce('maxState', [1, 2, 3]));

┌─finalizeAggregation(arrayReduce('maxState', [1, 2, 3]))─┐
│                                                       3 │
└─────────────────────────────────────────────────────────┘

Combined with initializeAggregation

WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
    number,
    finalizeAggregation(one_row_sum_state) AS one_row_sum,
    runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(5);

┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│      0 │           0 │              0 │
│      1 │           1 │              1 │
│      2 │           2 │              3 │
│      3 │           3 │              6 │
│      4 │           4 │             10 │
└────────┴─────────────┴────────────────┘

formatQuery

Introduced in: v

Returns a formatted, possibly multi-line, version of the given SQL query. Throws in case of a parsing error. [example:multiline]

Syntax

formatQuery(query)

Arguments

query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)

formatQueryOrNull

Introduced in: v

Returns a formatted, possibly multi-line, version of the given SQL query. Returns NULL in case of a parsing error. [example:multiline]

Syntax

formatQueryOrNull(query)

Arguments

query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuery('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT
    a,
    b
FROM tab
WHERE (a > 3) AND (b < 3)

formatQuerySingleLine

Introduced in: v

Like formatQuery() but the returned formatted string contains no line breaks. Throws in case of a parsing error. [example:multiline]

Syntax

formatQuerySingleLine(query)

Arguments

query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)

formatQuerySingleLineOrNull

Introduced in: v

Like formatQuery() but the returned formatted string contains no line breaks. Returns NULL in case of a parsing error. [example:multiline]

Syntax

formatQuerySingleLineOrNull(query)

Arguments

query — The SQL query to be formatted. String

Returned value

The formatted query String

Examples

multiline

SELECT formatQuerySingleLine('select a,    b FRom tab WHERE a > 3 and  b < 3');

SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)

formatReadableDecimalSize

Introduced in: v22.11

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KB, MB, etc.) as a string.

The opposite operations of this function are parseReadableSize.

Syntax

formatReadableDecimalSize(x)

Arguments

x — Size in bytes. UInt64

Returned value

Returns a readable, rounded size with suffix as a string. String

Examples

Format file sizes

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableDecimalSize(filesize_bytes) AS filesize

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.02 KB    │
│        1048576 │ 1.05 MB    │
│      192851925 │ 192.85 MB  │
└────────────────┴────────────┘

formatReadableQuantity

Introduced in: v20.10

Given a number, this function returns a rounded number with suffix (thousand, million, billion, etc.) as a string.

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values.

Syntax

formatReadableQuantity(x)

Arguments

x — A number to format. UInt64

Returned value

Returns a rounded number with suffix as a string. String

Examples

Format numbers with suffixes

SELECT
    arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
    formatReadableQuantity(number) AS number_for_humans

┌─────────number─┬─number_for_humans─┐
│           1024 │ 1.02 thousand     │
│        1234000 │ 1.23 million      │
│     4567000000 │ 4.57 billion      │
│ 98765432101234 │ 98.77 trillion    │
└────────────────┴───────────────────┘

formatReadableSize

Introduced in: v1.1

Given a size (number of bytes), this function returns a readable, rounded size with suffix (KiB, MiB, etc.) as string.

The opposite operations of this function are parseReadableSize, parseReadableSizeOrZero, and parseReadableSizeOrNull. This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values.

Syntax

formatReadableSize(x)

Arguments

x — Size in bytes. UInt64

Returned value

Returns a readable, rounded size with suffix as a string. String

Examples

Format file sizes

SELECT
    arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
    formatReadableSize(filesize_bytes) AS filesize

┌─filesize_bytes─┬─filesize───┐
│              1 │ 1.00 B     │
│           1024 │ 1.00 KiB   │
│        1048576 │ 1.00 MiB   │
│      192851925 │ 183.92 MiB │
└────────────────┴────────────┘

formatReadableTimeDelta

Introduced in: v20.12

Given a time interval (delta) in seconds, this function returns a time delta with year/month/day/hour/minute/second/millisecond/microsecond/nanosecond as a string.

This function accepts any numeric type as input, but internally it casts them to Float64. Results might be suboptimal with large values.

Syntax

formatReadableTimeDelta(column[, maximum_unit, minimum_unit])

Arguments

column — A column with a numeric time delta. Float64
maximum_unit — Optional. Maximum unit to show. Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years. Default value: years. const String
minimum_unit — Optional. Minimum unit to show. All smaller units are truncated. Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years. If explicitly specified value is bigger than maximum_unit, an exception will be thrown. Default value: seconds if maximum_unit is seconds or bigger, nanoseconds otherwise. const String

Returned value

Returns a time delta as a string. String

Examples

Usage example

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed) AS time_delta

┌────elapsed─┬─time_delta─────────────────────────────────────────────────────┐
│        100 │ 1 minute and 40 seconds                                        │
│      12345 │ 3 hours, 25 minutes and 45 seconds                             │
│  432546534 │ 13 years, 8 months, 17 days, 7 hours, 48 minutes and 54 seconds│
└────────────┴────────────────────────────────────────────────────────────────┘

With maximum unit

SELECT
    arrayJoin([100, 12345, 432546534]) AS elapsed,
    formatReadableTimeDelta(elapsed, 'minutes') AS time_delta

┌────elapsed─┬─time_delta─────────────────────────────────────────────────────┐
│        100 │ 1 minute and 40 seconds                                         │
│      12345 │ 205 minutes and 45 seconds                                      │
│  432546534 │ 7209108 minutes and 54 seconds                                  │
└────────────┴─────────────────────────────────────────────────────────────────┘

generateRandomStructure

Introduced in: v23.5

Generates random table structure in the format column1_name column1_type, column2_name column2_type, ....

Syntax

generateRandomStructure([number_of_columns, seed])

Arguments

number_of_columns — The desired number of columns in the resultant table structure. If set to 0 or Null, the number of columns will be random from 1 to 128. Default value: Null. UInt64
seed — Random seed to produce stable results. If seed is not specified or set to Null, it is randomly generated. UInt64

Returned value

Randomly generated table structure. String

Examples

Usage example

SELECT generateRandomStructure()

c1 Decimal32(5), c2 Date, c3 Tuple(LowCardinality(String), Int128, UInt64, UInt16, UInt8, IPv6), c4 Array(UInt128), c5 UInt32, c6 IPv4, c7 Decimal256(64), c8 Decimal128(3), c9 UInt256, c10 UInt64, c11 DateTime

with specified number of columns

SELECT generateRandomStructure(1)

c1 Map(UInt256, UInt16)

with specified seed

SELECT generateRandomStructure(NULL, 33)

c1 DateTime, c2 Enum8('c2V0' = 0, 'c2V1' = 1, 'c2V2' = 2, 'c2V3' = 3), c3 LowCardinality(Nullable(FixedString(30))), c4 Int16, c5 Enum8('c5V0' = 0, 'c5V1' = 1, 'c5V2' = 2, 'c5V3' = 3), c6 Nullable(UInt8), c7 String, c8 Nested(e1 IPv4, e2 UInt8, e3 UInt16, e4 UInt16, e5 Int32, e6 Map(Date, Decimal256(70)))

generateSerialID

Introduced in: v

Generates and returns sequential numbers starting from the previous counter value. This function takes a string argument - a series identifier, and an optional starting value.

The server should be configured with Keeper. The series are stored in Keeper nodes under the path, which can be configured in series_keeper_path in the server configuration.

Syntax

generateSerialID('series_identifier'[, start_value])

Arguments

series_identifier — Series identifier, (a short constant String) - start_value — Optional starting value for the counter. Defaults to 0. Note: this value is only used when creating a new series and is ignored if the series already exists

Returned value

Returns sequential numbers starting from the previous counter value.

Examples

first call

SELECT generateSerialID('id1')

┌─generateSerialID('id1')──┐
│                        1 │
└──────────────────────────┘

second call

SELECT generateSerialID('id1')

┌─generateSerialID('id1')──┐
│                        2 │
└──────────────────────────┘

column call

SELECT *, generateSerialID('id1') FROM test_table

┌─CounterID─┬─UserID─┬─ver─┬─generateSerialID('id1')──┐
│         1 │      3 │   3 │                        3 │
│         1 │      1 │   1 │                        4 │
│         1 │      2 │   2 │                        5 │
│         1 │      5 │   5 │                        6 │
│         1 │      4 │   4 │                        7 │
└───────────┴────────┴─────┴──────────────────────────┘

with start value

SELECT generateSerialID('id2', 100)

┌─generateSerialID('id2', 100)──┐
│                           100 │
└───────────────────────────────┘

with start value second call

SELECT generateSerialID('id2', 100)

┌─generateSerialID('id2', 100)──┐
│                           101 │
└───────────────────────────────┘

getClientHTTPHeader

Introduced in: v24.5

Gets the value of an HTTP header. If there is no such header or the current request is not performed via the HTTP interface, the function returns an empty string. Certain HTTP headers (e.g., Authentication and X-ClickHouse-*) are restricted.

Note

Setting allow_get_client_http_header is required

HTTP headers are case sensitive for this function. If the function is used in the context of a distributed query, it returns non-empty result only on the initiator node.

Syntax

getClientHTTPHeader(name)

Arguments

name — The HTTP header name. String

Returned value

Returns the value of the header. String

Examples

Usage example

SELECT getClientHTTPHeader('Content-Type');

┌─getClientHTTPHeader('Content-Type')─┐
│ application/x-www-form-urlencoded   │
└─────────────────────────────────────┘

getMacro

Introduced in: v20.1

Returns the value of a macro from the server configuration file. Macros are defined in the <macros> section of the configuration file and can be used to distinguish servers by convenient names even if they have complicated hostnames. If the function is executed in the context of a distributed table, it generates a normal column with values relevant to each shard.

Syntax

getMacro(name)

Arguments

name — The name of the macro to retrieve. const String

Returned value

Returns the value of the specified macro. String

Examples

Basic usage

SELECT getMacro('test');

┌─getMacro('test')─┐
│ Value            │
└──────────────────┘

getMaxTableNameLengthForDatabase

Introduced in: v

Returns the maximum table name length in a specified database.

Syntax

getMaxTableNameLengthForDatabase(database_name)

Arguments

database_name — The name of the specified database. String

Returned value

Returns the length of the maximum table name, an Integer

Examples

typical

SELECT getMaxTableNameLengthForDatabase('default');

┌─getMaxTableNameLengthForDatabase('default')─┐
            │                                         206 │
            └─────────────────────────────────────────────┘

getMergeTreeSetting

Introduced in: v25.6

Returns the current value of a MergeTree setting.

Syntax

getMergeTreeSetting(setting_name)

Arguments

setting_name — The setting name. String

Returned value

Returns the merge tree setting's current value.

Examples

Usage example

SELECT getMergeTreeSetting('index_granularity');

┌─getMergeTreeSetting('index_granularity')─┐
│                                     8192 │
└──────────────────────────────────────────┘

getOSKernelVersion

Introduced in: v21.11

Returns a string with the OS kernel version.

Syntax

getOSKernelVersion()

Arguments

None. Returned value

Returns the current OS kernel version. String

Examples

Usage example

SELECT getOSKernelVersion();

┌─getOSKernelVersion()────┐
│ Linux 4.15.0-55-generic │
└─────────────────────────┘

getServerPort

Introduced in: v21.10

Returns the server's port number for a given protocol.

Syntax

getServerPort(port_name)

Arguments

port_name — The name of the port. String

Returned value

Returns the server port number. UInt16

Examples

Usage example

SELECT getServerPort('tcp_port');

┌─getServerPort('tcp_port')─┐
│                      9000 │
└───────────────────────────┘

getServerSetting

Introduced in: v25.6

Returns the currently set value, given a server setting name.

Syntax

getServerSetting(setting_name')

Arguments

setting_name — The server setting name. String

Returned value

Returns the server setting's current value. Any

Examples

Usage example

SELECT getServerSetting('allow_use_jemalloc_memory');

┌─getServerSetting('allow_use_jemalloc_memory')─┐
│ true                                          │
└───────────────────────────────────────────────┘

getSetting

Introduced in: v20.7

Returns the current value of a setting.

Syntax

getSetting(setting_name)

Arguments

setting_Name — The setting name. const String

Returned value

Returns the setting's current value. Any

Examples

Usage example

SELECT getSetting('enable_analyzer');
SET enable_analyzer = false;
SELECT getSetting('enable_analyzer');

┌─getSetting('⋯_analyzer')─┐
│ true                     │
└──────────────────────────┘
┌─getSetting('⋯_analyzer')─┐
│ false                    │
└──────────────────────────┘

getSettingOrDefault

Introduced in: v24.10

Returns the current value of a setting or returns the default value specified in the second argument if the setting is not set in the current profile.

Syntax

getSettingOrDefault(setting_name, default_value)

Arguments

setting_name — The setting name. String
default_value — Value to return if custom_setting is not set. Value may be of any data type or Null.

Returned value

Returns the current value of the specified setting or default_value if the setting is not set.

Examples

Usage example

SELECT getSettingOrDefault('custom_undef1', 'my_value');
SELECT getSettingOrDefault('custom_undef2', 100);
SELECT getSettingOrDefault('custom_undef3', NULL);

my_value
100
NULL

getSizeOfEnumType

Introduced in: v1.1

Returns the number of fields in the given Enum.

Syntax

getSizeOfEnumType(x)

Arguments

x — Value of type Enum. Enum

Returned value

Returns the number of fields with Enum input values. UInt8/16

Examples

Usage example

SELECT getSizeOfEnumType(CAST('a' AS Enum8('a' = 1, 'b' = 2))) AS x;

┌─x─┐
│ 2 │
└───┘

getSubcolumn

Introduced in: v

Receives the expression or identifier and constant string with the name of subcolumn.

Returns requested subcolumn extracted from the expression.

Syntax

Arguments

None. Returned value

Examples

getSubcolumn

SELECT getSubcolumn(array_col, 'size0'), getSubcolumn(tuple_col, 'elem_name')

getTypeSerializationStreams

Introduced in: v22.6

Enumerates stream paths of a data type. This function is intended for developmental use.

Syntax

getTypeSerializationStreams(col)

Arguments

col — Column or string representation of a data-type from which the data type will be detected. Any

Returned value

Returns an array with all the serialization sub-stream paths. Array(String)

Examples

tuple

SELECT getTypeSerializationStreams(tuple('a', 1, 'b', 2))

['{TupleElement(1), Regular}','{TupleElement(2), Regular}','{TupleElement(3), Regular}','{TupleElement(4), Regular}']

map

SELECT getTypeSerializationStreams('Map(String, Int64)')

['{ArraySizes}','{ArrayElements, TupleElement(keys), Regular}','{ArrayElements, TupleElement(values), Regular}']

globalVariable

Introduced in: v20.5

Syntax

globalVariable(name)

Arguments

name — Global variable name. String

Returned value

Returns the value of variable name. Any

Examples

globalVariable

SELECT globalVariable('max_allowed_packet')

67108864

hasColumnInTable

Introduced in: v1.1

Checks if a specific column exists in a database table. For elements in a nested data structure, the function checks for the existence of a column. For the nested data structure itself, the function returns 0.

Syntax

hasColumnInTable([hostname[, username[, password]],]database, table, column)

Arguments

database — Name of the database. const String
table — Name of the table. const String
column — Name of the column. const String
hostname — Optional. Remote server name to perform the check on. const String
username — Optional. Username for remote server. const String
password — Optional. Password for remote server. const String

Returned value

Returns 1 if the given column exists, 0 otherwise. UInt8

Examples

Check an existing column

SELECT hasColumnInTable('system','metrics','metric')

Check a non-existing column

SELECT hasColumnInTable('system','metrics','non-existing_column')

hasThreadFuzzer

Introduced in: v20.6

Returns whether the thread fuzzer is enabled. THis function is only useful for testing and debugging.

Syntax

hasThreadFuzzer()

Arguments

None. Returned value

Returns whether Thread Fuzzer is effective. UInt8

Examples

Check Thread Fuzzer status

SELECT hasThreadFuzzer()

┌─hasThreadFuzzer()─┐
│                 0 │
└───────────────────┘

hostName

Introduced in: v20.5

Syntax

hostName()

Arguments

None. Returned value

Returns the host name. String

Examples

Usage example

SELECT hostName()

┌─hostName()─┐
│ clickhouse │
└────────────┘

icebergBucket

Introduced in: v25.5

Implements logic of iceberg bucket transform: https://iceberg.apache.org/spec/#bucket-transform-details.

Syntax

icebergBucket(N, value)

Arguments

N — modulo, positive integer, always constant. - value — Integer, bool, decimal, float, string, fixed_string, uuid, date, time or datetime value.

Returned value

Int32

Examples

Example

SELECT icebergBucket(5, 1.0 :: Float32)

icebergHash

Introduced in: v25.5

Implements logic of iceberg hashing transform: https://iceberg.apache.org/spec/#appendix-b-32-bit-hash-requirements.

Syntax

icebergHash(N, value)

Arguments

value — Integer, bool, decimal, float, string, fixed_string, uuid, date, time, datetime.

Returned value

Int32

Examples

Example

SELECT icebergHash(1.0 :: Float32)

-142385009

icebergTruncate

Introduced in: v25.3

Implements logic of iceberg truncate transform: https://iceberg.apache.org/spec/#truncate-transform-details.

Syntax

icebergTruncate(N, value)

Arguments

value — The value to transform. String or (U)Int* or Decimal

Returned value

The same type as the argument

Examples

Example

SELECT icebergTruncate(3, 'iceberg')

ice

identity

Introduced in: v1.1

This function returns the argument you pass to it, which is useful for debugging and testing. It lets you bypass index usage to see full scan performance instead. The query analyzer ignores anything inside identity functions when looking for indexes to use, and it also disables constant folding.

Syntax

identity(x)

Arguments

x — Input value. Any

Returned value

Returns the input value unchanged. Any

Examples

Usage example

SELECT identity(42)

ignore

Introduced in: v1.1

Accepts arbitrary arguments and unconditionally returns 0.

Syntax

ignore(x)

Arguments

x — An input value which is unused and passed only so as to avoid a syntax error. Any

Returned value

Always returns 0. UInt8

Examples

Usage example

SELECT ignore(0, 'ClickHouse', NULL)

┌─ignore(0, 'ClickHouse', NULL)─┐
│                             0 │
└───────────────────────────────┘

indexHint

Introduced in: v1.1

This function is intended for debugging and introspection. It ignores its argument and always returns 1. The arguments are not evaluated.

Syntax

indexHint(expression)

Arguments

expression — Any expression for index range selection. Expression

Returned value

Returns 1 in all cases. UInt8

Examples

Usage example with date filtering

SELECT FlightDate AS k, count() FROM ontime WHERE indexHint(k = '2025-09-15') GROUP BY k ORDER BY k ASC;

┌──────────k─┬─count()─┐
│ 2025-09-14 │    7071 │
│ 2025-09-15 │   16428 │
│ 2025-09-16 │    1077 │
│ 2025-09-30 │    8167 │
└────────────┴─────────┘

initialQueryID

Introduced in: v1.1

Returns the ID of the initial current query. Other parameters of a query can be extracted from field initial_query_id in system.query_log.

In contrast to queryID function, initialQueryID returns the same results on different shards.

Syntax

initialQueryID()

Arguments

None. Returned value

Returns the ID of the initial current query. String

Examples

Usage example

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

┌─count(DISTINCT t)─┐
│                 1 │
└───────────────────┘

initialQueryStartTime

Introduced in: v25.4

Returns the start time of the initial current query. initialQueryStartTime returns the same results on different shards.

Syntax

initialQueryStartTime()

Arguments

None. Returned value

Returns the start time of the initial current query. DateTime

Examples

Usage example

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryStartTime() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

┌─count(DISTINCT t)─┐
│                 1 │
└───────────────────┘

initializeAggregation

Introduced in: v20.6

Calculates the result of an aggregate function based on a single value. This function can be used to initialize aggregate functions with combinator -State. You can create states of aggregate functions and insert them to columns of type AggregateFunction or use initialized aggregates as default values.

Syntax

initializeAggregation(aggregate_function, arg1[, arg2, ...])

Arguments

aggregate_function — Name of the aggregation function to initialize. String
arg1[, arg2, ...] — Arguments of the aggregate function. Any

Returned value

Returns the result of aggregation for every row passed to the function. The return type is the same as the return type of the function that initializeAggregation takes as a first argument. Any

Examples

Basic usage with uniqState

SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000));

┌─uniqMerge(state)─┐
│                3 │
└──────────────────┘

Usage with sumState and finalizeAggregation

SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5));

┌─finalizeAggregation(state)─┬─toTypeName(state)─────────────┐
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
│                          2 │ AggregateFunction(sum, UInt8) │
│                          0 │ AggregateFunction(sum, UInt8) │
│                          1 │ AggregateFunction(sum, UInt8) │
└────────────────────────────┴───────────────────────────────┘

isConstant

Introduced in: v20.3

Returns whether the argument is a constant expression. A constant expression is an expression whose result is known during query analysis, i.e. before execution. For example, expressions over literals are constant expressions. This function is mostly intended for development, debugging and demonstration.

Syntax

isConstant(x)

Arguments

x — An expression to check. Any

Returned value

Returns 1 if x is constant, 0 if x is non-constant. UInt8

Examples

Constant expression

SELECT isConstant(x + 1)
FROM (SELECT 43 AS x)

┌─isConstant(plus(x, 1))─┐
│                      1 │
└────────────────────────┘

Constant with function

WITH 3.14 AS pi
SELECT isConstant(cos(pi))

┌─isConstant(cos(pi))─┐
│                   1 │
└─────────────────────┘

Non-constant expression

SELECT isConstant(number)
FROM numbers(1)

┌─isConstant(number)─┐
│                  0 │
└────────────────────┘

Behavior of the now() function

SELECT isConstant(now())

┌─isConstant(now())─┐
│                 1 │
└───────────────────┘

isDecimalOverflow

Introduced in: v20.8

Checks if a decimal number has too many digits to fit properly in a Decimal data type with given precision.

Syntax

isDecimalOverflow(value[, precision])

Arguments

value — Decimal value to check. Decimal
precision — Optional. The precision of the Decimal type. If omitted, the initial precision of the first argument is used. UInt8

Returned value

Returns 1 if the decimal value has more digits than allowed by its precision, 0 if the decimal value satisfies the specified precision. UInt8

Examples

Usage example

SELECT isDecimalOverflow(toDecimal32(1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(1000000000, 0)),
       isDecimalOverflow(toDecimal32(-1000000000, 0), 9),
       isDecimalOverflow(toDecimal32(-1000000000, 0));

┌─isDecimalOverflow(toDecimal32(1000000000, 0), 9)─┬─isDecimalOverflow(toDecimal32(1000000000, 0))─┬─isDecimalOverflow(toDecimal32(-1000000000, 0), 9)─┬─isDecimalOverflow(toDecimal32(-1000000000, 0))─┐
│                                                1 │                                             1 │                                                 1 │                                              1 │
└──────────────────────────────────────────────────┴───────────────────────────────────────────────┴───────────────────────────────────────────────────┴────────────────────────────────────────────────┘

joinGet

Introduced in: v18.16

Allows you to extract data from a table the same way as from a dictionary. Gets data from Join tables using the specified join key.

Note

Only supports tables created with the ENGINE = Join(ANY, LEFT, <join_keys>) statement.

Syntax

joinGet(join_storage_table_name, value_column, join_keys)

Arguments

join_storage_table_name — An identifier which indicates where to perform the search. The identifier is searched in the default database (see parameter default_database in the config file). To override the default database, use the USE database_name query or specify the database and the table through a dot, like database_name.table_name. String
value_column — The name of the column of the table that contains required data. const String
join_keys — A list of join keys. Any

Returned value

Returns list of values corresponded to list of keys. Any

Examples

Usage example

CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGet(db_test.id_val, 'val', toUInt32(1));

┌─joinGet(db_test.id_val, 'val', toUInt32(1))─┐
│                                          11 │
└─────────────────────────────────────────────┘

Usage with table from current database

USE db_test;
SELECT joinGet(id_val, 'val', toUInt32(2));

┌─joinGet(id_val, 'val', toUInt32(2))─┐
│                                  12 │
└─────────────────────────────────────┘

Using arrays as join keys

CREATE TABLE some_table (id1 UInt32, id2 UInt32, name String) ENGINE = Join(ANY, LEFT, id1, id2);
INSERT INTO some_table VALUES (1, 11, 'a') (2, 12, 'b') (3, 13, 'c');

SELECT joinGet(some_table, 'name', 1, 11);

┌─joinGet(some_table, 'name', 1, 11)─┐
│ a                                  │
└────────────────────────────────────┘

joinGetOrNull

Introduced in: v20.4

Allows you to extract data from a table the same way as from a dictionary. Gets data from Join tables using the specified join key. Unlike joinGet it returns NULL when the key is missing.

Note

Only supports tables created with the ENGINE = Join(ANY, LEFT, <join_keys>) statement.

Syntax

joinGetOrNull(join_storage_table_name, value_column, join_keys)

Arguments

join_storage_table_name — An identifier which indicates where to perform the search. The identifier is searched in the default database (see parameter default_database in the config file). To override the default database, use the USE database_name query or specify the database and the table through a dot, like database_name.table_name. String
value_column — The name of the column of the table that contains required data. const String
join_keys — A list of join keys. Any

Returned value

Returns a list of values corresponding to the list of keys, or NULL if a key is not found. Any

Examples

Usage example

CREATE TABLE db_test.id_val(`id` UInt32, `val` UInt32) ENGINE = Join(ANY, LEFT, id);
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13);

SELECT joinGetOrNull(db_test.id_val, 'val', toUInt32(1)), joinGetOrNull(db_test.id_val, 'val', toUInt32(999));

┌─joinGetOrNull(db_test.id_val, 'val', toUInt32(1))─┬─joinGetOrNull(db_test.id_val, 'val', toUInt32(999))─┐
│                                                11 │                                                ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────┴─────────────────────────────────────────────────────┘

lowCardinalityIndices

Introduced in: v18.12

Syntax

lowCardinalityIndices(col)

Arguments

col — A low cardinality column. LowCardinality

Returned value

The position of the value in the dictionary of the current part. UInt64

Examples

Usage examples

DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityIndices(s) FROM test;

┌─s──┬─lowCardinalityIndices(s)─┐
│ ab │                        1 │
│ cd │                        2 │
│ ab │                        1 │
│ ab │                        1 │
│ df │                        3 │
└────┴──────────────────────────┘
┌─s──┬─lowCardinalityIndices(s)─┐
│ ef │                        1 │
│ cd │                        2 │
│ ab │                        3 │
│ cd │                        2 │
│ ef │                        1 │
└────┴──────────────────────────┘

lowCardinalityKeys

Introduced in: v18.12

Syntax

lowCardinalityKeys(col)

Arguments

col — A low cardinality column. LowCardinality

Returned value

Returns the dictionary keys. UInt64

Examples

lowCardinalityKeys

DROP TABLE IF EXISTS test;
CREATE TABLE test (s LowCardinality(String)) ENGINE = Memory;

-- create two parts:

INSERT INTO test VALUES ('ab'), ('cd'), ('ab'), ('ab'), ('df');
INSERT INTO test VALUES ('ef'), ('cd'), ('ab'), ('cd'), ('ef');

SELECT s, lowCardinalityKeys(s) FROM test;

┌─s──┬─lowCardinalityKeys(s)─┐
│ ef │                       │
│ cd │ ef                    │
│ ab │ cd                    │
│ cd │ ab                    │
│ ef │                       │
└────┴───────────────────────┘
┌─s──┬─lowCardinalityKeys(s)─┐
│ ab │                       │
│ cd │ ab                    │
│ ab │ cd                    │
│ ab │ df                    │
│ df │                       │
└────┴───────────────────────┘

materialize

Introduced in: v1.1

Syntax

materialize(x)

Arguments

x — A constant. Any

Returned value

Returns a full column containing the constant value. Any

Examples

Usage example

-- In the example below the `countMatches` function expects a constant second argument.
-- This behaviour can be debugged by using the `materialize` function to turn a constant into a full column,
-- verifying that the function throws an error for a non-constant argument.

SELECT countMatches('foobarfoo', 'foo');
SELECT countMatches('foobarfoo', materialize('foo'));

2
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type of argument #2 'pattern' of function countMatches, expected constant String, got String

minSampleSizeContinuous

Introduced in: v23.10

Calculates the minimum required sample size for an A/B test comparing means of a continuous metric in two samples.

Syntax

minSampleSizeContinuous(baseline, sigma, mde, power, alpha)

Arguments

baseline — Baseline value of a metric. (U)Int* or Float*
sigma — Baseline standard deviation of a metric. (U)Int* or Float*
mde — Minimum detectable effect (MDE) as percentage of the baseline value (e.g. for a baseline value 112.25 the MDE 0.03 means an expected change to 112.25 ± 112.25*0.03). (U)Int* or Float*
power — Required statistical power of a test (1 - probability of Type II error). (U)Int* or Float*
alpha — Required significance level of a test (probability of Type I error). (U)Int* or Float*

Returned value

Returns a named Tuple with 3 elements: minimum_sample_size, detect_range_lower and detect_range_upper. These are respectively: the required sample size, the lower bound of the range of values not detectable with the returned required sample size, calculated as baseline * (1 - mde), and the upper bound of the range of values not detectable with the returned required sample size, calculated as baseline * (1 + mde) (Float64). Tuple(Float64, Float64, Float64)

Examples

minSampleSizeContinuous

SELECT minSampleSizeContinuous(112.25, 21.1, 0.03, 0.80, 0.05) AS sample_size

(616.2931945826209,108.8825,115.6175)

minSampleSizeConversion

Introduced in: v22.6

Calculates minimum required sample size for an A/B test comparing conversions (proportions) in two samples.

Syntax

minSampleSizeConversion(baseline, mde, power, alpha)

Arguments

baseline — Baseline conversion. Float*
mde — Minimum detectable effect (MDE) as percentage points (e.g. for a baseline conversion 0.25 the MDE 0.03 means an expected change to 0.25 ± 0.03). Float*
power — Required statistical power of a test (1 - probability of Type II error). Float*
alpha — Required significance level of a test (probability of Type I error). Float*

Returned value

Returns a named Tuple with 3 elements: minimum_sample_size, detect_range_lower, detect_range_upper. These are, respectively: the required sample size, the lower bound of the range of values not detectable with the returned required sample size, calculated as baseline - mde, the upper bound of the range of values not detectable with the returned required sample size, calculated as baseline + mde. Tuple(Float64, Float64, Float64)

Examples

minSampleSizeConversion

SELECT minSampleSizeConversion(0.25, 0.03, 0.80, 0.05) AS sample_size

(3396.077603219163,0.22,0.28)

neighbor

Introduced in: v20.1

Returns a value from a column at a specified offset from the current row. This function is deprecated and error-prone because it operates on the physical order of data blocks which may not correspond to the logical order expected by users. Consider using proper window functions instead.

The function can be enabled by setting allow_deprecated_error_prone_window_functions = 1.

Syntax

neighbor(column, offset[, default_value])

Arguments

column — The source column. Any
offset — The offset from the current row. Positive values look forward, negative values look backward. Integer
default_value — Optional. The value to return if the offset goes beyond the data bounds. If not specified, uses the default value for the column type. Any

Returned value

Returns a value from the specified offset, or default if out of bounds. Any

Examples

Usage example

SELECT number, neighbor(number, 2) FROM system.numbers LIMIT 10;

┌─number─┬─neighbor(number, 2)─┐
│      0 │                   2 │
│      1 │                   3 │
│      2 │                   4 │
│      3 │                   5 │
│      4 │                   6 │
│      5 │                   7 │
│      6 │                   8 │
│      7 │                   9 │
│      8 │                   0 │
│      9 │                   0 │
└────────┴─────────────────────┘

With default value

SELECT number, neighbor(number, 2, 999) FROM system.numbers LIMIT 10;

┌─number─┬─neighbor(number, 2, 999)─┐
│      0 │                        2 │
│      1 │                        3 │
│      2 │                        4 │
│      3 │                        5 │
│      4 │                        6 │
│      5 │                        7 │
│      6 │                        8 │
│      7 │                        9 │
│      8 │                      999 │
│      9 │                      999 │
└────────┴──────────────────────────┘

nested

Introduced in: v

This is a function used internally by the ClickHouse engine and not meant to be used directly.

Returns the array of tuples from multiple arrays.

The first argument must be a constant array of Strings determining the names of the resulting Tuple. The other arguments must be arrays of the same size.

Syntax

Arguments

None. Returned value

Examples

nested

SELECT nested(['keys', 'values'], ['key_1', 'key_2'], ['value_1','value_2'])

normalizeQuery

Introduced in: v20.8

Replaces literals, sequences of literals and complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs) with placeholder ?.

Syntax

normalizeQuery(x)

Arguments

x — Sequence of characters. String

Returned value

Returns the given sequence of characters with placeholders. String

Examples

Usage example

SELECT normalizeQuery('[1, 2, 3, x]') AS query

┌─query────┐
│ [?.., x] │
└──────────┘

normalizeQueryKeepNames

Introduced in: v21.2

Replaces literals and sequences of literals with placeholder ? but does not replace complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs). This helps better analyze complex query logs.

Syntax

normalizeQueryKeepNames(x)

Arguments

x — Sequence of characters. String

Returned value

Returns the given sequence of characters with placeholders. String

Examples

Usage example

SELECT normalizeQuery('SELECT 1 AS aComplexName123'), normalizeQueryKeepNames('SELECT 1 AS aComplexName123')

┌─normalizeQuery('SELECT 1 AS aComplexName123')─┬─normalizeQueryKeepNames('SELECT 1 AS aComplexName123')─┐
│ SELECT ? AS `?`                               │ SELECT ? AS aComplexName123                            │
└───────────────────────────────────────────────┴────────────────────────────────────────────────────────┘

normalizedQueryHash

Introduced in: v20.8

Returns identical 64 bit hash values without the values of literals for similar queries. Can be helpful in analyzing query logs.

Syntax

normalizedQueryHash(x)

Arguments

x — Sequence of characters. String

Returned value

Returns a 64 bit hash value. UInt64

Examples

Usage example

SELECT normalizedQueryHash('SELECT 1 AS `xyz`') != normalizedQueryHash('SELECT 1 AS `abc`') AS res

┌─res─┐
│   1 │
└─────┘

normalizedQueryHashKeepNames

Introduced in: v21.2

Like normalizedQueryHash it returns identical 64 bit hash values without the values of literals for similar queries, but it does not replace complex aliases (containing whitespace, more than two digits or at least 36 bytes long such as UUIDs) with a placeholder before hashing. Can be helpful in analyzing query logs.

Syntax

normalizedQueryHashKeepNames(x)

Arguments

x — Sequence of characters. String

Returned value

Returns a 64 bit hash value. UInt64

Examples

Usage example

SELECT normalizedQueryHash('SELECT 1 AS `xyz123`') != normalizedQueryHash('SELECT 1 AS `abc123`') AS normalizedQueryHash;
SELECT normalizedQueryHashKeepNames('SELECT 1 AS `xyz123`') != normalizedQueryHashKeepNames('SELECT 1 AS `abc123`') AS normalizedQueryHashKeepNames;

┌─normalizedQueryHash─┐
│                   0 │
└─────────────────────┘
┌─normalizedQueryHashKeepNames─┐
│                            1 │
└──────────────────────────────┘

parseReadableSize

Introduced in: v24.6

Given a string containing a byte size and B, KiB, KB, MiB, MB, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it throws an exception.

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSize(x)

Arguments

x — Readable size with ISO/IEC 80000-13 or decimal byte unit. String

Returned value

Returns the number of bytes, rounded up to the nearest integer. UInt64

Examples

Usage example

SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB']) AS readable_sizes, parseReadableSize(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
└────────────────┴─────────┘

parseReadableSizeOrNull

Introduced in: v24.6

Given a string containing a byte size and B, KiB, KB, MiB, MB, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it returns NULL.

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSizeOrNull(x)

Arguments

x — Readable size with ISO/IEC 80000-13 or decimal byte unit. String

Returned value

Returns the number of bytes, rounded up to the nearest integer, or NULL if unable to parse the input Nullable(UInt64)

Examples

Usage example

SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrNull(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
│ invalid        │    ᴺᵁᴸᴸ │
└────────────────┴─────────┘

parseReadableSizeOrZero

Introduced in: v24.6

Given a string containing a byte size and B, KiB, KB, MiB, MB, etc. as a unit (i.e. ISO/IEC 80000-13 or decimal byte unit), this function returns the corresponding number of bytes. If the function is unable to parse the input value, it returns 0.

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSizeOrZero(x)

Arguments

x — Readable size with ISO/IEC 80000-13 or decimal byte unit. String

Returned value

Returns the number of bytes, rounded up to the nearest integer, or 0 if unable to parse the input. UInt64

Examples

Usage example

SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrZero(readable_sizes) AS sizes;

┌─readable_sizes─┬───sizes─┐
│ 1 B            │       1 │
│ 1 KiB          │    1024 │
│ 3 MB           │ 3000000 │
│ 5.314 KiB      │    5442 │
│ invalid        │       0 │
└────────────────┴─────────┘

parseTimeDelta

Introduced in: v22.7

Parse a sequence of numbers followed by something resembling a time unit.

The time delta string uses these time unit specifications:

years, year, yr, y
months, month, mo
weeks, week, w
days, day, d
hours, hour, hr, h
minutes, minute, min, m
seconds, second, sec, s
milliseconds, millisecond, millisec, ms
microseconds, microsecond, microsec, μs, µs, us
nanoseconds, nanosecond, nanosec, ns

Multiple time units can be combined with separators (space, ;, -, +, ,, :).

The length of years and months are approximations: year is 365 days, month is 30.5 days.

Syntax

parseTimeDelta(timestr)

Arguments

timestr — A sequence of numbers followed by something resembling a time unit. String

Returned value

The number of seconds. Float64

Examples

Usage example

SELECT parseTimeDelta('11s+22min')

┌─parseTimeDelta('11s+22min')─┐
│                        1331 │
└─────────────────────────────┘

Complex time units

SELECT parseTimeDelta('1yr2mo')

┌─parseTimeDelta('1yr2mo')─┐
│                 36806400 │
└──────────────────────────┘

partitionId

Introduced in: v21.4

Computes the partition ID.

Note

This function is slow and should not be called for large numbers of rows.

Syntax

partitionId(column1[, column2, ...])

Arguments

column1, column2, ... — Column for which to return the partition ID.

Returned value

Returns the partition ID that the row belongs to. String

Examples

Usage example

DROP TABLE IF EXISTS tab;

CREATE TABLE tab
(
  i int,
  j int
)
ENGINE = MergeTree
PARTITION BY i
ORDER BY tuple();

INSERT INTO tab VALUES (1, 1), (1, 2), (1, 3), (2, 4), (2, 5), (2, 6);

SELECT i, j, partitionId(i), _partition_id FROM tab ORDER BY i, j;

┌─i─┬─j─┬─partitionId(i)─┬─_partition_id─┐
│ 1 │ 1 │ 1              │ 1             │
│ 1 │ 2 │ 1              │ 1             │
│ 1 │ 3 │ 1              │ 1             │
│ 2 │ 4 │ 2              │ 2             │
│ 2 │ 5 │ 2              │ 2             │
│ 2 │ 6 │ 2              │ 2             │
└───┴───┴────────────────┴───────────────┘

queryID

Introduced in: v21.9

Returns the ID of the current query. Other parameters of a query can be extracted from field query_id in the system.query_log table.

In contrast to initialQueryID function, queryID can return different results on different shards.

Syntax

queryID()

Arguments

None. Returned value

Returns the ID of the current query. String

Examples

Usage example

CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT queryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());

┌─count(DISTINCT t)─┐
│                 3 │
└───────────────────┘

revision

Introduced in: v22.7

Returns the current ClickHouse server revision.

Syntax

revision()

Arguments

None. Returned value

Returns the current ClickHouse server revision. UInt32

Examples

Usage example

SELECT revision()

┌─revision()─┐
│      54485 │
└────────────┘

rowNumberInAllBlocks

Introduced in: v1.1

Returns a unique row number for each row processed.

Syntax

rowNumberInAllBlocks()

Arguments

None. Returned value

Returns the ordinal number of the row in the data block starting from 0. UInt64

Examples

Usage example

SELECT rowNumberInAllBlocks()
FROM
(
    SELECT *
    FROM system.numbers_mt
    LIMIT 10
)
SETTINGS max_block_size = 2

┌─rowNumberInAllBlocks()─┐
│                      0 │
│                      1 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      4 │
│                      5 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      2 │
│                      3 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      6 │
│                      7 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│                      8 │
│                      9 │
└────────────────────────┘

rowNumberInBlock

Introduced in: v1.1

For each block processed by rowNumberInBlock, returns the number of the current row.

The returned number starts from 0 for each block.

Syntax

rowNumberInBlock()

Arguments

None. Returned value

Returns the ordinal number of the row in the data block starting from 0. UInt64

Examples

Usage example

SELECT rowNumberInBlock()
FROM
(
    SELECT *
    FROM system.numbers_mt
    LIMIT 10
) SETTINGS max_block_size = 2

┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│                  0 │
│                  1 │
└────────────────────┘

runningAccumulate

Introduced in: v1.1

Accumulates the states of an aggregate function for each row of a data block.

Deprecated

The state is reset for each new block of data. Due to this error-prone behavior the function has been deprecated, and you are advised to use window functions instead. You can use setting allow_deprecated_error_prone_window_functions to allow usage of this function.

Syntax

runningAccumulate(agg_state[, grouping])

Arguments

agg_state — State of the aggregate function. AggregateFunction
grouping — Optional. Grouping key. The state of the function is reset if the grouping value is changed. It can be any of the supported data types for which the equality operator is defined. Any

Returned value

Returns the accumulated result for each row. Any

Examples

Usage example with initializeAggregation

WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
    number,
    finalizeAggregation(one_row_sum_state) AS one_row_sum,
    runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(5);

┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│      0 │           0 │              0 │
│      1 │           1 │              1 │
│      2 │           2 │              3 │
│      3 │           3 │              6 │
│      4 │           4 │             10 │
└────────┴─────────────┴────────────────┘

runningConcurrency

Introduced in: v21.3

Requirements

Deprecated

It is advised to use window functions instead.

Syntax

runningConcurrency(start, end)

Arguments

start — A column with the start time of events. Date or DateTime or DateTime64
end — A column with the end time of events. Date or DateTime or DateTime64

Returned value

Returns the number of concurrent events at each event start time. UInt32

Examples

Usage example

SELECT start, runningConcurrency(start, end) FROM example_table;

┌──────start─┬─runningConcurrency(start, end)─┐
│ 2025-03-03 │                              1 │
│ 2025-03-06 │                              2 │
│ 2025-03-07 │                              3 │
│ 2025-03-11 │                              2 │
└────────────┴────────────────────────────────┘

runningDifference

Introduced in: v1.1

Calculates the difference between two consecutive row values in the data block. Returns 0 for the first row, and for subsequent rows the difference to the previous row.

Deprecated

Only returns differences inside the currently processed data block. Because of this error-prone behavior, the function is deprecated. It is advised to use window functions instead.

You can use setting allow_deprecated_error_prone_window_functions to allow usage of this function.

The result of the function depends on the affected data blocks and the order of data in the block. The order of rows during calculation of runningDifference() can differ from the order of rows returned to the user. To prevent that you can create a subquery with ORDER BY and call the function from outside the subquery. Please note that the block size affects the result. The internal state of runningDifference state is reset for each new block.

Syntax

runningDifference(x)

Arguments

x — Column for which to calculate the running difference. Any

Returned value

Returns the difference between consecutive values, with 0 for the first row.

Examples

Usage example

SELECT
    EventID,
    EventTime,
    runningDifference(EventTime) AS delta
FROM
(
    SELECT
        EventID,
        EventTime
    FROM events
    WHERE EventDate = '2025-11-24'
    ORDER BY EventTime ASC
    LIMIT 5
);

┌─EventID─┬───────────EventTime─┬─delta─┐
│    1106 │ 2025-11-24 00:00:04 │     0 │
│    1107 │ 2025-11-24 00:00:05 │     1 │
│    1108 │ 2025-11-24 00:00:05 │     0 │
│    1109 │ 2025-11-24 00:00:09 │     4 │
│    1110 │ 2025-11-24 00:00:10 │     1 │
└─────────┴─────────────────────┴───────┘

Block size impact example

SELECT
    number,
    runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1;

┌─number─┬─diff─┐
│      0 │    0 │
└────────┴──────┘
┌─number─┬─diff─┐
│  65536 │    0 │
└────────┴──────┘

runningDifferenceStartingWithFirstValue

Introduced in: v1.1

Calculates the difference between consecutive row values in a data block, but unlike runningDifference, it returns the actual value of the first row instead of 0.

Deprecated

Only returns differences inside the currently processed data block. Because of this error-prone behavior, the function is deprecated. It is advised to use window functions instead.

You can use setting allow_deprecated_error_prone_window_functions to allow usage of this function.

Syntax

runningDifferenceStartingWithFirstValue(x)

Arguments

x — Column for which to calculate the running difference. Any

Returned value

Returns the difference between consecutive values, with the first row's value for the first row. Any

Examples

Usage example

SELECT
    number,
    runningDifferenceStartingWithFirstValue(number) AS diff
FROM numbers(5);

┌─number─┬─diff─┐
│      0 │    0 │
│      1 │    1 │
│      2 │    1 │
│      3 │    1 │
│      4 │    1 │
└────────┴──────┘

serverUUID

Introduced in: v20.1

Returns the random and unique UUID (v4) generated when the server is first started. The UUID is persisted, i.e. the second, third, etc. server start return the same UUID.

Syntax

serverUUID()

Arguments

None. Returned value

Returns the random UUID of the server. UUID

Examples

Usage example

SELECT serverUUID();

┌─serverUUID()─────────────────────────────┐
│ 7ccc9260-000d-4d5c-a843-5459abaabb5f     │
└──────────────────────────────────────────┘

shardCount

Introduced in: v21.9

Returns the total number of shards for a distributed query. If a query is not distributed then constant value 0 is returned.

Syntax

shardCount()

Arguments

None. Returned value

Returns the total number of shards or 0. UInt32

Examples

Usage example

-- See shardNum() example above which also demonstrates shardCount()
CREATE TABLE shard_count_example (dummy UInt8)
ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT shardCount() FROM shard_count_example;

┌─shardCount()─┐
│            2 │
│            2 │
└──────────────┘

shardNum

Introduced in: v21.9

Returns the index of a shard which processes a part of data in a distributed query. Indices begin from 1. If a query is not distributed then a constant value 0 is returned.

Syntax

shardNum()

Arguments

None. Returned value

Returns the shard index or a constant 0. UInt32

Examples

Usage example

CREATE TABLE shard_num_example (dummy UInt8)
ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT dummy, shardNum(), shardCount() FROM shard_num_example;

┌─dummy─┬─shardNum()─┬─shardCount()─┐
│     0 │          1 │            2 │
│     0 │          2 │            2 │
└───────┴────────────┴──────────────┘

showCertificate

Introduced in: v22.6

Syntax

showCertificate()

Arguments

None. Returned value

Returns map of key-value pairs relating to the configured SSL certificate. Map(String, String)

Examples

Usage example

SELECT showCertificate() FORMAT LineAsString;

{'version':'1','serial_number':'2D9071D64530052D48308473922C7ADAFA85D6C5','signature_algo':'sha256WithRSAEncryption','issuer':'/CN=marsnet.local CA','not_before':'May  7 17:01:21 2024 GMT','not_after':'May  7 17:01:21 2025 GMT','subject':'/CN=chnode1','pkey_algo':'rsaEncryption'}

sleep

Introduced in: v1.1

Pauses the execution of a query by the specified number of seconds. The function is primarily used for testing and debugging purposes.

The sleep() function should generally not be used in production environments, as it can negatively impact query performance and system responsiveness. However, it can be useful in the following scenarios:

Testing: When testing or benchmarking ClickHouse, you may want to simulate delays or introduce pauses to observe how the system behaves under certain conditions.
Debugging: If you need to examine the state of the system or the execution of a query at a specific point in time, you can use sleep() to introduce a pause, allowing you to inspect or collect relevant information.
Simulation: In some cases, you may want to simulate real-world scenarios where delays or pauses occur, such as network latency or external system dependencies.

Caution

It's important to use the sleep() function judiciously and only when necessary, as it can potentially impact the overall performance and responsiveness of your ClickHouse system.

For security reasons, the function can only be executed in the default user profile (with allow_sleep enabled).

Syntax

sleep(seconds)

Arguments

seconds — The number of seconds to pause the query execution to a maximum of 3 seconds. It can be a floating-point value to specify fractional seconds. const UInt* or const Float*

Returned value

Returns 0. UInt8

Examples

Usage example

-- This query will pause for 2 seconds before completing.
-- During this time, no results will be returned, and the query will appear to be hanging or unresponsive.
SELECT sleep(2);

┌─sleep(2)─┐
│        0 │
└──────────┘
1 row in set. Elapsed: 2.012 sec.

sleepEachRow

Introduced in: v1.1

Pauses the execution of a query for a specified number of seconds for each row in the result set.

Testing: When testing or benchmarking ClickHouse's performance under specific conditions, you can use sleepEachRow() to simulate delays or introduce pauses for each row processed.
Debugging: If you need to examine the state of the system or the execution of a query for each row processed, you can use sleepEachRow() to introduce pauses, allowing you to inspect or collect relevant information.
Simulation: In some cases, you may want to simulate real-world scenarios where delays or pauses occur for each row processed, such as when dealing with external systems or network latencies.

Caution

Syntax

sleepEachRow(seconds)

Arguments

seconds — The number of seconds to pause the query execution for each row in the result set to a maximum of 3 seconds. It can be a floating-point value to specify fractional seconds. const UInt* or const Float*

Returned value

Returns 0 for each row. UInt8

Examples

Usage example

-- The output will be delayed, with a 0.5-second pause between each row.
SELECT number, sleepEachRow(0.5) FROM system.numbers LIMIT 5;

┌─number─┬─sleepEachRow(0.5)─┐
│      0 │                 0 │
│      1 │                 0 │
│      2 │                 0 │
│      3 │                 0 │
│      4 │                 0 │
└────────┴───────────────────┘

structureToCapnProtoSchema

Introduced in: v

Function that converts ClickHouse table structure to CapnProto format schema

Syntax

Arguments

None. Returned value

Examples

random

SELECT structureToCapnProtoSchema('s String, x UInt32', 'MessageName') format TSVRaw

struct MessageName
{
    s @0 : Data;
    x @1 : UInt32;
}

structureToProtobufSchema

Introduced in: v

Function that converts ClickHouse table structure to Protobuf format schema

Syntax

Arguments

None. Returned value

Examples

random

SELECT structureToProtobufSchema('s String, x UInt32', 'MessageName') format TSVRaw

syntax = "proto3";

message MessageName
{
    bytes s = 1;
    uint32 x = 2;
}

tcpPort

Introduced in: v20.12

Returns the native interface TCP port number listened to by the server. If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise it produces a constant value.

Syntax

tcpPort()

Arguments

None. Returned value

Returns the TCP port number. UInt16

Examples

Usage example

SELECT tcpPort()

┌─tcpPort()─┐
│      9000 │
└───────────┘

throwIf

Introduced in: v1.1

Throw an exception if argument x is true. To use the error_code argument, configuration parameter allow_custom_error_code_in_throw must be enabled.

Syntax

throwIf(x[, message[, error_code]])

Arguments

x — The condition to check. Any
message — Optional. Custom error message. const String
error_code — Optional. Custom error code. const Int8/16/32

Returned value

Returns 0 if the condition is false, throws an exception if the condition is true. UInt8

Examples

Usage example

SELECT throwIf(number = 3, 'Too many') FROM numbers(10);

↙ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 19.14.1):
Code: 395. DB::Exception: Received from localhost:9000. DB::Exception: Too many.

toColumnTypeName

Introduced in: v1.1

Returns the internal name of the data type of the given value. Unlike function toTypeName, the returned data type potentially includes internal wrapper columns like Const and LowCardinality.

Syntax

toColumnTypeName(value)

Arguments

value — Value for which to return the internal data type. Any

Returned value

Returns the internal data type used to represent the value. String

Examples

Usage example

SELECT toColumnTypeName(CAST('2025-01-01 01:02:03' AS DateTime));

┌─toColumnTypeName(CAST('2025-01-01 01:02:03', 'DateTime'))─┐
│ Const(UInt32)                                             │
└───────────────────────────────────────────────────────────┘

toTypeName

Introduced in: v1.1

Returns the type name of the passed argument. If NULL is passed, the function returns type Nullable(Nothing), which corresponds to ClickHouse's internal NULL representation.

Syntax

toTypeName(x)

Arguments

x — A value of arbitrary type. Any

Returned value

Returns the data type name of the input value. String

Examples

Usage example

SELECT toTypeName(123)

┌─toTypeName(123)─┐
│ UInt8           │
└─────────────────┘

transactionID

Introduced in: v22.6

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Returns the ID of a transaction.

Note

This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:

<clickhouse>
    <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>

For more information see the page Transactional (ACID) support.

Syntax

transactionID()

Arguments

None. Returned value

Returns a tuple consisting of start_csn, local_tid and host_id.

start_csn: Global sequential number, the newest commit timestamp that was seen when this transaction began.
local_tid: Local sequential number that is unique for each transaction started by this host within a specific start_csn.
host_id: UUID of the host that has started this transaction. Tuple(UInt64, UInt64, UUID)

Examples

Usage example

BEGIN TRANSACTION;
SELECT transactionID();
ROLLBACK;

┌─transactionID()────────────────────────────────┐
│ (32,34,'0ee8b069-f2bb-4748-9eae-069c85b5252b') │
└────────────────────────────────────────────────┘

transactionLatestSnapshot

Introduced in: v22.6

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Returns the newest snapshot (Commit Sequence Number) of a transaction that is available for reading.

Note

This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:

<clickhouse>
    <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>

For more information see the page Transactional (ACID) support.

Syntax

transactionLatestSnapshot()

Arguments

None. Returned value

Returns the latest snapshot (CSN) of a transaction. UInt64

Examples

Usage example

BEGIN TRANSACTION;
SELECT transactionLatestSnapshot();
ROLLBACK;

┌─transactionLatestSnapshot()─┐
│                          32 │
└─────────────────────────────┘

transactionOldestSnapshot

Introduced in: v22.6

Experimental feature. Learn more.

Not supported in ClickHouse Cloud

Returns the oldest snapshot (Commit Sequence Number) that is visible for some running transaction.

Note

This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:

<clickhouse>
    <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>

For more information see the page Transactional (ACID) support.

Syntax

transactionOldestSnapshot()

Arguments

None. Returned value

Returns the oldest snapshot (CSN) of a transaction. UInt64

Examples

Usage example

BEGIN TRANSACTION;
SELECT transactionOldestSnapshot();
ROLLBACK;

┌─transactionOldestSnapshot()─┐
│                          32 │
└─────────────────────────────┘

transform

Introduced in: v1.1

Transforms a value according to the explicitly defined mapping of some elements to other elements.

There are two variations of this function:

transform(x, array_from, array_to, default) - transforms x using mapping arrays with a default value for unmatched elements
transform(x, array_from, array_to) - same transformation but returns the original x if no match is found

The function searches for x in array_from and returns the corresponding element from array_to at the same index. If x is not found in array_from, it returns either the default value (4-parameter version) or the original x (3-parameter version). If multiple matching elements exist in array_from, it returns the element corresponding to the first match.

Requirements:

array_from and array_to must have the same number of elements
For 4-parameter version: transform(T, Array(T), Array(U), U) -> U where T and U can be different compatible types
For 3-parameter version: transform(T, Array(T), Array(T)) -> T where all types must be the same

Syntax

transform(x, array_from, array_to[, default])

Arguments

x — Value to transform. (U)Int* or Decimal or Float* or String or Date or DateTime
array_from — Constant array of values to search for matches. Array((U)Int*) or Array(Decimal) or Array(Float*) or Array(String) or Array(Date) or Array(DateTime)
array_to — Constant array of values to return for corresponding matches in array_from. Array((U)Int*) or Array(Decimal) or Array(Float*) or Array(String) or Array(Date) or Array(DateTime)
default — Optional. Value to return if x is not found in array_from. If omitted, returns x unchanged. (U)Int* or Decimal or Float* or String or Date or DateTime

Returned value

Returns the corresponding value from array_to if x matches an element in array_from, otherwise returns default (if provided) or x (if default not provided). Any

Examples

transform(T, Array(T), Array(U), U) -> U

SELECT
transform(SearchEngineID, [2, 3], ['Yandex', 'Google'], 'Other') AS title,
count() AS c
FROM test.hits
WHERE SearchEngineID != 0
GROUP BY title
ORDER BY c DESC

┌─title─────┬──────c─┐
│ Yandex    │ 498635 │
│ Google    │ 229872 │
│ Other     │ 104472 │
└───────────┴────────┘

transform(T, Array(T), Array(T)) -> T

SELECT
transform(domain(Referer), ['yandex.ru', 'google.ru', 'vkontakte.ru'], ['www.yandex', 'example.com', 'vk.com']) AS s, count() AS c
FROM test.hits
GROUP BY domain(Referer)
ORDER BY count() DESC
LIMIT 10

┌─s──────────────┬───────c─┐
│                │ 2906259 │
│ www.yandex     │  867767 │
│ ███████.ru     │  313599 │
│ mail.yandex.ru │  107147 │
│ ██████.ru      │  100355 │
│ █████████.ru   │   65040 │
│ news.yandex.ru │   64515 │
│ ██████.net     │   59141 │
│ example.com    │   57316 │
└────────────────┴─────────┘

uniqThetaIntersect

Introduced in: v22.9

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

Syntax

uniqThetaIntersect(uniqThetaSketch,uniqThetaSketch)

Arguments

uniqThetaSketch — uniqThetaSketch object. Tuple or Array or Date or DateTime or String or (U)Int* or Float* or Decimal

Returned value

A new uniqThetaSketch containing the intersect result. UInt64

Examples

Usage 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

Introduced in: v22.9

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

Syntax

uniqThetaNot(uniqThetaSketch,uniqThetaSketch)

Arguments

uniqThetaSketch — uniqThetaSketch object. Tuple or Array or Date or DateTime or String or (U)Int* or Float* or Decimal

Returned value

Returns a new uniqThetaSketch containing the a_not_b result. UInt64

Examples

Usage 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 │
└─────────┴───────────────┴───────────────┘

uniqThetaUnion

Introduced in: v22.9

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

Syntax

uniqThetaUnion(uniqThetaSketch,uniqThetaSketch)

Arguments

uniqThetaSketch — uniqThetaSketch object. Tuple or Array or Date or DateTime or String or (U)Int* or Float* or Decimal

Returned value

Returns a new uniqThetaSketch containing the union result. UInt64

Examples

Usage 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 │
└───────────┴───────────────┴───────────────┘

uptime

Introduced in: v1.1

Syntax

uptime()

Arguments

None. Returned value

Returns the server uptime in seconds. UInt32

Examples

Usage example

SELECT uptime() AS Uptime

┌─Uptime─┐
│  55867 │
└────────┘

variantElement

Introduced in: v25.2

Extracts a column with specified type from a Variant column.

Syntax

variantElement(variant, type_name[, default_value])

Arguments

variant — Variant column. Variant
type_name — The name of the variant type to extract. String
default_value — The default value that will be used if variant doesn't have variant with specified type. Can be any type. Optional. Any

Returned value

Returns a column with the specified variant type extracted from the Variant column. Any

Examples

Usage example

CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT v, variantElement(v, 'String'), variantElement(v, 'UInt64'), variantElement(v, 'Array(UInt64)') FROM test;

┌─v─────────────┬─variantElement(v, 'String')─┬─variantElement(v, 'UInt64')─┬─variantElement(v, 'Array(UInt64)')─┐
│ ᴺᵁᴸᴸ          │ ᴺᵁᴸᴸ                        │                        ᴺᵁᴸᴸ │ []                                 │
│ 42            │ ᴺᵁᴸᴸ                        │                          42 │ []                                 │
│ Hello, World! │ Hello, World!               │                        ᴺᵁᴸᴸ │ []                                 │
│ [1,2,3]       │ ᴺᵁᴸᴸ                        │                        ᴺᵁᴸᴸ │ [1,2,3]                            │
└───────────────┴─────────────────────────────┴─────────────────────────────┴────────────────────────────────────┘

variantType

Introduced in: v24.2

Returns the variant type name for each row of Variant column. If row contains NULL, it returns 'None' for it.

Syntax

variantType(variant)

Arguments

variant — Variant column. Variant

Returned value

Returns an Enum column with variant type name for each row. Enum

Examples

Usage example

CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT variantType(v) FROM test;

┌─variantType(v)─┐
│ None           │
│ UInt64         │
│ String         │
│ Array(UInt64)  │
└────────────────┘

version

Introduced in: v1.1

Returns the current version of ClickHouse as a string in the form: major_version.minor_version.patch_version.number_of_commits_since_the_previous_stable_release. If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise, it produces a constant value.

Syntax

version()

Arguments

None. Returned value

Returns the current version of ClickHouse. String

Examples

Usage example

SELECT version()

┌─version()─┐
│ 24.2.1.1  │
└───────────┘

visibleWidth

Introduced in: v1.1

Calculates the approximate width when outputting values to the console in text format (tab-separated). This function is used by the system to implement Pretty formats. NULL is represented as a string corresponding to NULL in Pretty formats.

Syntax

visibleWidth(x)

Arguments

x — A value of any data type. Any

Returned value

Returns the approximate width of the value when displayed in text format. UInt64

Examples

Calculate visible width of NULL

SELECT visibleWidth(NULL)

┌─visibleWidth(NULL)─┐
│                  4 │
└────────────────────┘

zookeeperSessionUptime

Introduced in: v21.11

Returns the uptime of the current ZooKeeper session in seconds.

Syntax

zookeeperSessionUptime()

Arguments

None. Returned value

Returns the uptime of the current ZooKeeper session in seconds. UInt32

Examples

Usage example

SELECT zookeeperSessionUptime();

┌─zookeeperSessionUptime()─┐
│                      286 │
└──────────────────────────┘

hostName​

getMacro​

fqdn​

basename​

visibleWidth​

toTypeName​

blockSize​

byteSize​

materialize​

ignore​

sleep​

sleepEachRow​

currentDatabase​

currentUser​

currentSchemas​

colorSRGBToOKLCH​

colorOKLCHToSRGB​

isConstant​

hasColumnInTable​

hasThreadFuzzer​

bar​

transform​

transform(x, array_from, array_to, default)​

transform(x, array_from, array_to)​

formatReadableDecimalSize​

formatReadableSize​

formatReadableQuantity​

formatReadableTimeDelta​

parseReadableSize​

parseReadableSizeOrNull​

parseReadableSizeOrZero​

parseTimeDelta​

least​

greatest​

uptime​

version​

buildId​

blockNumber​

rowNumberInBlock​

rowNumberInAllBlocks​

normalizeQuery​

normalizeQueryKeepNames​

normalizedQueryHash​

normalizedQueryHashKeepNames​

neighbor​

runningDifference​

runningDifferenceStartingWithFirstValue​

runningConcurrency​

MACNumToString​

MACStringToNum​

MACStringToOUI​

getSizeOfEnumType​

blockSerializedSize​

toColumnTypeName​

dumpColumnStructure​

defaultValueOfArgumentType​

defaultValueOfTypeName​

indexHint​

replicate​

revision​

filesystemAvailable​

filesystemUnreserved​

filesystemCapacity​

initializeAggregation​

finalizeAggregation​

runningAccumulate​

joinGet​

joinGetOrNull​

catboostEvaluate​

throwIf​

identity​

getSetting​

getSettingOrDefault​

isDecimalOverflow​

countDigits​

errorCodeToName​

tcpPort​

currentProfiles​

enabledProfiles​

defaultProfiles​

hostName

getMacro

fqdn

basename

visibleWidth

toTypeName

blockSize

byteSize

materialize

ignore

sleep

sleepEachRow

currentDatabase

currentUser

currentSchemas

colorSRGBToOKLCH

colorOKLCHToSRGB

isConstant

hasColumnInTable

hasThreadFuzzer

bar

transform

transform(x, array_from, array_to, default)

transform(x, array_from, array_to)

formatReadableDecimalSize

formatReadableSize

formatReadableQuantity

formatReadableTimeDelta

parseReadableSize

parseReadableSizeOrNull

parseReadableSizeOrZero

parseTimeDelta

least

greatest

uptime

version

buildId

blockNumber

rowNumberInBlock

rowNumberInAllBlocks

normalizeQuery

normalizeQueryKeepNames

normalizedQueryHash

normalizedQueryHashKeepNames

neighbor

runningDifference

runningDifferenceStartingWithFirstValue

runningConcurrency

MACNumToString

MACStringToNum

MACStringToOUI

getSizeOfEnumType

blockSerializedSize

toColumnTypeName

dumpColumnStructure

defaultValueOfArgumentType

defaultValueOfTypeName

indexHint

replicate

revision

filesystemAvailable

filesystemUnreserved

filesystemCapacity

initializeAggregation

finalizeAggregation

runningAccumulate

joinGet

joinGetOrNull

catboostEvaluate

throwIf

identity

getSetting

getSettingOrDefault

isDecimalOverflow

countDigits

errorCodeToName

tcpPort

currentProfiles

enabledProfiles

defaultProfiles