> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Documentation for the "other" functions category
# Other functions
export const DeprecatedBadge = () => {
return
;
};
The function documentation below is generated from the `system.functions` system table.
{/*AUTOGENERATED_START*/}
FQDN
Introduced in: v20.1.0
Returns the fully qualified domain name of the ClickHouse server.
**Syntax**
```sql theme={null}
FQDN()
```
**Aliases**: `fullHostName`
**Arguments**
* None.
**Returned value**
Returns the fully qualified domain name of the ClickHouse server. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT fqdn()
```
```response title=Response theme={null}
┌─FQDN()──────────────────────────┐
│ clickhouse.us-east-2.internal │
└─────────────────────────────────┘
```
MACNumToString
Introduced in: v1.1.0
Interprets a [`UInt64`](/reference/data-types/int-uint) 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**
```sql theme={null}
MACNumToString(num)
```
**Arguments**
* `num` — UInt64 number. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns a MAC address in format AA:BB:CC:DD:EE:FF. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT MACNumToString(149809441867716) AS mac_address;
```
```response title=Response theme={null}
┌─mac_address───────┐
│ 88:00:11:22:33:44 │
└───────────────────┘
```
MACStringToNum
Introduced in: v1.1.0
The inverse function of MACNumToString. If the MAC address has an invalid format, it returns 0.
**Syntax**
```sql theme={null}
MACStringToNum(s)
```
**Arguments**
* `s` — MAC address string. [`String`](/reference/data-types/string)
**Returned value**
Returns a UInt64 number. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT MACStringToNum('01:02:03:04:05:06') AS mac_numeric;
```
```response title=Response theme={null}
1108152157446
```
MACStringToOUI
Introduced in: v1.1.0
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**
```sql theme={null}
MACStringToOUI(s)
```
**Arguments**
* `s` — MAC address string. [`String`](/reference/data-types/string)
**Returned value**
First three octets as UInt64 number. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT MACStringToOUI('00:50:56:12:34:56') AS oui;
```
```response title=Response theme={null}
20566
```
authenticatedUser
Introduced in: v25.11.0
If the session user has been switched using the EXECUTE AS command, this function returns the name of the original user that was used for authentication and creating the session.
Alias: authUser()
**Syntax**
```sql theme={null}
authenticatedUser()
```
**Aliases**: `authUser`
**Arguments**
* None.
**Returned value**
The name of the authenticated user. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
EXECUTE as u1;
SELECT currentUser(), authenticatedUser();
```
```response title=Response theme={null}
┌─currentUser()─┬─authenticatedUser()─┐
│ u1 │ default │
└───────────────┴─────────────────────┘
```
bar
Introduced in: v1.1.0
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**
```sql theme={null}
bar(x, min, max[, width])
```
**Arguments**
* `x` — Size to display. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `min` — The minimum value. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `max` — The maximum value. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
* `width` — Optional. The width of the bar in characters. The default is `80`. [`const (U)Int*`](/reference/data-types/int-uint) or [`const Float*`](/reference/data-types/float) or [`const Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a unicode-art bar string. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
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
```
```response title=Response theme={null}
┌──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.0
Returns a monotonically increasing sequence number of the [block](/resources/develop-contribute/introduction/architecture#block) containing the row.
The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate.
**Syntax**
```sql theme={null}
blockNumber()
```
**Arguments**
* None.
**Returned value**
Sequence number of the data block where the row is located. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT blockNumber()
FROM
(
SELECT *
FROM system.numbers
LIMIT 10
) SETTINGS max_block_size = 2
```
```response title=Response theme={null}
┌─blockNumber()─┐
│ 7 │
│ 7 │
└───────────────┘
┌─blockNumber()─┐
│ 8 │
│ 8 │
└───────────────┘
┌─blockNumber()─┐
│ 9 │
│ 9 │
└───────────────┘
┌─blockNumber()─┐
│ 10 │
│ 10 │
└───────────────┘
┌─blockNumber()─┐
│ 11 │
│ 11 │
└───────────────┘
```
blockSerializedSize
Introduced in: v20.3.0
Returns the uncompressed size in bytes of a block of values on disk.
**Syntax**
```sql theme={null}
blockSerializedSize(x1[, x2[, ...]])
```
**Arguments**
* `x1[, x2, ...]` — Any number of values for which to get the uncompressed size of the block. [`Any`](/reference/data-types)
**Returned value**
Returns the number of bytes that will be written to disk for a block of values without compression. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT blockSerializedSize(maxState(1)) AS x;
```
```response title=Response theme={null}
┌─x─┐
│ 2 │
└───┘
```
blockSize
Introduced in: v1.1.0
In ClickHouse, queries are processed in [blocks](/resources/develop-contribute/introduction/architecture#block) (chunks).
This function returns the size (row count) of the block the function is called on.
**Syntax**
```sql theme={null}
blockSize()
```
**Arguments**
* None.
**Returned value**
Returns the number of rows in the current block. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT blockSize()
FROM system.numbers LIMIT 5
```
```response title=Response theme={null}
┌─blockSize()─┐
│ 5 │
│ 5 │
│ 5 │
│ 5 │
│ 5 │
└─────────────┘
```
buildId
Introduced in: v20.5.0
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**
```sql theme={null}
buildId()
```
**Arguments**
* None.
**Returned value**
Returns the build ID. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT buildId()
```
```response title=Response theme={null}
┌─buildId()────────────────────────────────┐
│ AB668BEF095FAA6BD26537F197AC2AF48A927FB4 │
└──────────────────────────────────────────┘
```
byteSize
Introduced in: v21.1.0
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**
```sql theme={null}
byteSize(arg1[, arg2, ...])
```
**Arguments**
* `arg1[, arg2, ...]` — Values of any data type for which to estimate the uncompressed byte size. [`Any`](/reference/data-types)
**Returned value**
Returns an estimation of the byte size of the arguments in memory. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT byteSize('string')
```
```response title=Response theme={null}
┌─byteSize('string')─┐
│ 15 │
└────────────────────┘
```
**Multiple arguments**
```sql title=Query theme={null}
SELECT byteSize(NULL, 1, 0.3, '')
```
```response title=Response theme={null}
┌─byteSize(NULL, 1, 0.3, '')─┐
│ 19 │
└────────────────────────────┘
```
catboostEvaluate
Introduced in: v22.9.0
Evaluate an external catboost model. [CatBoost](https://catboost.ai) 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**
1. Build the catboost evaluation library
Before evaluating catboost models, the `libcatboostmodel.` library must be made available. See [CatBoost documentation](https://catboost.ai/docs/concepts/c-plus-plus-api_dynamic-c-pluplus-wrapper.html) how to compile it.
Next, specify the path to `libcatboostmodel.` in the clickhouse configuration:
```xml theme={null}
...
/path/to/libcatboostmodel.so
...
```
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.
```xml theme={null}
9019
```
2. Train a catboost model using libcatboost
See [Training and applying models](https://catboost.ai/docs/features/training.html#training) for how to train catboost models from a training data set.
**Syntax**
```sql theme={null}
catboostEvaluate(path_to_model, feature_1[, feature_2, ..., feature_n])
```
**Arguments**
* `path_to_model` — Path to catboost model. [`const String`](/reference/data-types/string)
* `feature` — One or more model features/arguments. [`Float*`](/reference/data-types/float)
**Returned value**
Returns the model evaluation result. [`Float64`](/reference/data-types/float)
**Examples**
**catboostEvaluate**
```sql title=Query theme={null}
SELECT catboostEvaluate('/root/occupy.bin', Temperature, Humidity, Light, CO2, HumidityRatio) AS prediction FROM occupancy LIMIT 1
```
```response title=Response theme={null}
4.695691092573497
```
colorOKLABToSRGB
Introduced in: v26.2.0
Converts a color from the OKLab perceptual color space to the sRGB color space.
The input color is specified in the OKLab color space. If the input values are outside
the typical OKLab ranges, the result is implementation-defined.
OKLab uses three components:
* L: perceptual lightness (typically in the range \[0..1])
* a: green-red opponent axis
* b: blue-yellow opponent axis
The a and b components are theoretically unbounded, but in practice are between -0.4 and 0.4.
OKLab is designed to be perceptually uniform
while remaining inexpensive to compute.
The conversion is intended to be the inverse of colorSRGBToOKLAB and consists of
the following stages:
1. Conversion from OKLab to linear sRGB.
2. Conversion from linear sRGB to gamma-encoded sRGB.
The optional gamma argument specifies the exponent used when converting from linear
sRGB to gamma-encoded RGB values. If not specified, a default gamma value is used
for consistency with colorSRGBToOKLAB.
For more information about the OKLab color space and its relationship to sRGB, see [https://developer.mozilla.org/en-US/docs/Web/CSS/Reference/Values/color\_value/oklab](https://developer.mozilla.org/en-US/docs/Web/CSS/Reference/Values/color_value/oklab)
.
**Syntax**
```sql theme={null}
colorOKLABToSRGB(tuple [, gamma])
```
**Arguments**
* `tuple` — A tuple of three numeric values `L`, `a`, `b`, where `L` is in the range `[0...1]`. [`Tuple(Float64, Float64, Float64)`](/reference/data-types/tuple)
* `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`](/reference/data-types/float)
**Returned value**
Returns a tuple (R, G, B) representing sRGB color values. [`Tuple(Float64, Float64, Float64)`](/reference/data-types/tuple)
**Examples**
**Convert OKLAB to sRGB (Float)**
```sql title=Query theme={null}
SELECT colorOKLABToSRGB((0.4466, 0.0991, 0.44)) AS rgb;
```
```response title=Response theme={null}
┌─rgb──────────────────────┐
│ (198.07056923258935,0,0) │
└──────────────────────────┘
```
**Convert OKLAB to sRGB (UInt8)**
```sql title=Query theme={null}
WITH colorOKLABToSRGB((0.7, 0.1, 0.54)) AS t
SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB;
```
```response title=Response theme={null}
┌─RGB──────────┐
│ (255,0,0) │
└──────────────┘
```
colorOKLCHToSRGB
Introduced in: v25.7.0
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.
**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`](#colorSRGBToOKLCH):
1. OKLCH to OKLab.
2. OKLab to Linear sRGB
3. 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/](https://oklch.com/).
**Syntax**
```sql theme={null}
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)`](/reference/data-types/tuple)
* `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`](/reference/data-types/float)
**Returned value**
Returns a tuple (R, G, B) representing sRGB color values. [`Tuple(Float64, Float64, Float64)`](/reference/data-types/tuple)
**Examples**
**Convert OKLCH to sRGB**
```sql title=Query theme={null}
SELECT colorOKLCHToSRGB((0.6, 0.12, 40)) AS rgb;
```
```response title=Response theme={null}
┌─rgb───────────────────────────────────────────────────────┐
│ (186.02058688365264,100.68677189684993,71.67819977081575) │
└───────────────────────────────────────────────────────────┘
```
**Convert OKLCH to sRGB (UInt8)**
```sql title=Query theme={null}
WITH colorOKLCHToSRGB((0.6, 0.12, 40)) AS t
SELECT tuple(toUInt8(t.1), toUInt8(t.2), toUInt8(t.3)) AS RGB;
```
```response title=Response theme={null}
┌─RGB──────────┐
│ (186,100,71) │
└──────────────┘
```
colorSRGBToOKLAB
Introduced in: v26.2.0
Converts a colour encoded in the **sRGB** colour space to the perceptually uniform **OKLAB** colour space.
If any input channel is outside `[0...255]` or the gamma value is non-positive, the behaviour is implementation-defined.
**OKLAB** is a perceptually uniform color space.
Its three coordinates are `L` (the lightness in the range `[0...1]`), `a (Green-Red axis)` and `b (Blue-Yellow axis)`.
OKLab is designed to be perceptually uniform while remaining cheap to compute.
The conversion consists of two stages:
1. sRGB to Linear sRGB
2. Linear sRGB to OKLab
**Syntax**
```sql theme={null}
colorSRGBToOKLAB(tuple[, gamma])
```
**Arguments**
* `tuple` — Tuple of three values R, G, B in the range `[0...255]`. [`Tuple(UInt8, UInt8, UInt8)`](/reference/data-types/tuple)
* `gamma` — Optional. Exponent that is used to linearize sRGB by applying `(x / 255)^gamma` to each channel `x`. Defaults to `2.2`. [`Float64`](/reference/data-types/float)
**Returned value**
Returns a tuple (L, a, b) representing the OKLAB color space values. [`Tuple(Float64, Float64, Float64)`](/reference/data-types/tuple)
**Examples**
**Convert sRGB to OKLAB**
```sql title=Query theme={null}
SELECT colorSRGBToOKLAB((128, 64, 32), 2.2) AS lab;
```
```response title=Response theme={null}
┌─lab──────────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.07266246769242975,0.07500108778529994) │
└──────────────────────────────────────────────────────────────┘
```
colorSRGBToOKLCH
Introduced in: v25.7.0
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.
**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:
1. sRGB to Linear sRGB
2. Linear sRGB to OKLab
3. OKLab to OKLCH.
For references of colors in the OKLCH space, and how they correspond to sRGB colors, please see [https://OKLCH.com/](https://OKLCH.com/).
**Syntax**
```sql theme={null}
colorSRGBToOKLCH(tuple[, gamma])
```
**Arguments**
* `tuple` — Tuple of three values R, G, B in the range `[0...255]`. [`Tuple(UInt8, UInt8, UInt8)`](/reference/data-types/tuple)
* `gamma` — Optional. Exponent that is used to linearize sRGB by applying `(x / 255)^gamma` to each channel `x`. Defaults to `2.2`. [`Float64`](/reference/data-types/float)
**Returned value**
Returns a tuple (L, C, H) representing the OKLCH color space values. [`Tuple(Float64, Float64, Float64)`](/reference/data-types/tuple)
**Examples**
**Convert sRGB to OKLCH**
```sql title=Query theme={null}
SELECT colorSRGBToOKLCH((128, 64, 32), 2.2) AS lch;
```
```response title=Response theme={null}
┌─lch───────────────────────────────────────────────────────┐
│ (0.4436238384931984,0.1044269954567863,45.90734548193018) │
└───────────────────────────────────────────────────────────┘
```
connectionId
Introduced in: v21.3.0
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**
```sql theme={null}
connectionId()
```
**Arguments**
* None.
**Returned value**
Returns the connection ID of the current client. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT connectionId();
```
```response title=Response theme={null}
┌─connectionId()─┐
│ 0 │
└────────────────┘
```
countDigits
Introduced in: v20.8.0
Returns the number of decimal digits needed to represent a value.
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`
You can check decimal overflow for `Decimal64` with `countDigits(x) > 18`,
although it is slower than [`isDecimalOverflow`](#isDecimalOverflow).
**Syntax**
```sql theme={null}
countDigits(x)
```
**Arguments**
* `x` — An integer or decimal value. [`(U)Int*`](/reference/data-types/int-uint) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns the number of digits needed to represent `x`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
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));
```
```response title=Response theme={null}
┌─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.0
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](/reference/statements/use).
**Syntax**
```sql theme={null}
currentDatabase()
```
**Aliases**: `current_database`, `SCHEMA`, `DATABASE`
**Arguments**
* None.
**Returned value**
Returns the current database name. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT currentDatabase()
```
```response title=Response theme={null}
┌─currentDatabase()─┐
│ default │
└───────────────────┘
```
**SQL standard syntax without parentheses**
```sql title=Query theme={null}
SELECT CURRENT_DATABASE
```
```response title=Response theme={null}
┌─CURRENT_DATABASE─┐
│ default │
└──────────────────┘
```
currentProfiles
Introduced in: v21.9.0
Returns an array of the setting profiles for the current user.
**Syntax**
```sql theme={null}
currentProfiles()
```
**Arguments**
* None.
**Returned value**
Returns an array of setting profiles for the current user. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT currentProfiles();
```
```response title=Response theme={null}
┌─currentProfiles()─────────────────────────────┐
│ ['default', 'readonly_user', 'web_analytics'] │
└───────────────────────────────────────────────┘
```
Introduced in: v21.9.0
Returns an array of the roles which are assigned to the current user.
**Syntax**
```sql theme={null}
currentRoles()
```
**Arguments**
* None.
**Returned value**
Returns an array of the roles which are assigned to the current user. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT currentRoles();
```
```response title=Response theme={null}
┌─currentRoles()─────────────────────────────────┐
│ ['sql-console-role:jane.smith@clickhouse.com'] │
└────────────────────────────────────────────────┘
```
currentSchemas
Introduced in: v23.7.0
Same as function [`currentDatabase`](#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](/reference/statements/use).
**Syntax**
```sql theme={null}
currentSchemas(bool)
```
**Aliases**: `current_schemas`
**Arguments**
* `bool` — A boolean value, which is ignored. [`Bool`](/reference/data-types/boolean)
**Returned value**
Returns a single-element array with the name of the current database. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT currentSchemas(true)
```
```response title=Response theme={null}
┌─currentSchemas(true)─┐
│ ['default'] │
└──────────────────────┘
```
currentUser
Introduced in: v20.1.0
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**
```sql theme={null}
currentUser()
```
**Aliases**: `current_user`, `user`
**Arguments**
* None.
**Returned value**
Returns the name of the current user, otherwise the login of the user who initiated the query. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT currentUser()
```
```response title=Response theme={null}
┌─currentUser()─┐
│ default │
└───────────────┘
```
**SQL standard syntax without parentheses**
```sql title=Query theme={null}
SELECT CURRENT_USER
```
```response title=Response theme={null}
┌─CURRENT_USER─┐
│ default │
└──────────────┘
```
defaultProfiles
Introduced in: v21.9.0
Returns an array of default setting profile names for the current user.
**Syntax**
```sql theme={null}
defaultProfiles()
```
**Arguments**
* None.
**Returned value**
Returns an array of default setting profile names for the current user. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT defaultProfiles();
```
```response title=Response theme={null}
┌─defaultProfiles()─┐
│ ['default'] │
└───────────────────┘
```
defaultRoles
Introduced in: v21.9.0
Returns an array of default roles for the current user.
**Syntax**
```sql theme={null}
defaultRoles()
```
**Arguments**
* None.
**Returned value**
Returns an array of default roles for the current user. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT defaultRoles();
```
```response title=Response theme={null}
┌─defaultRoles()─────────────────────────────────┐
│ ['sql-console-role:jane.smith@clickhouse.com'] │
└────────────────────────────────────────────────┘
```
defaultValueOfArgumentType
Introduced in: v1.1.0
Returns the default value for a given data type.
Does not include default values for custom columns set by the user.
**Syntax**
```sql theme={null}
defaultValueOfArgumentType(expression)
```
**Arguments**
* `expression` — Arbitrary type of value or an expression that results in a value of an arbitrary type. [`Any`](/reference/data-types)
**Returned value**
Returns `0` for numbers, an empty string for strings or `NULL` for Nullable types. [`UInt8`](/reference/data-types/int-uint) or [`String`](/reference/data-types/string) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT defaultValueOfArgumentType(CAST(1 AS Int8));
```
```response title=Response theme={null}
┌─defaultValueOfArgumentType(CAST(1, 'Int8'))─┐
│ 0 │
└─────────────────────────────────────────────┘
```
**Nullable example**
```sql title=Query theme={null}
SELECT defaultValueOfArgumentType(CAST(1 AS Nullable(Int8)));
```
```response title=Response theme={null}
┌─defaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))─┐
│ ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────────┘
```
defaultValueOfTypeName
Introduced in: v1.1.0
Returns the default value for the given type name.
**Syntax**
```sql theme={null}
defaultValueOfTypeName(type)
```
**Arguments**
* `type` — A string representing a type name. [`String`](/reference/data-types/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`](/reference/data-types/int-uint) or [`String`](/reference/data-types/string) or [`NULL`](/reference/syntax#null)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT defaultValueOfTypeName('Int8');
```
```response title=Response theme={null}
┌─defaultValueOfTypeName('Int8')─┐
│ 0 │
└────────────────────────────────┘
```
**Nullable example**
```sql title=Query theme={null}
SELECT defaultValueOfTypeName('Nullable(Int8)');
```
```response title=Response theme={null}
┌─defaultValueOfTypeName('Nullable(Int8)')─┐
│ ᴺᵁᴸᴸ │
└──────────────────────────────────────────┘
```
displayName
Introduced in: v22.11.0
Returns the value of `display_name` from [config](/concepts/features/configuration/server-config/configuration-files) or the server's Fully Qualified Domain Name (FQDN) if not set.
**Syntax**
```sql theme={null}
displayName()
```
**Arguments**
* None.
**Returned value**
Returns the value of `display_name` from config or server FQDN if not set. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT displayName();
```
```response title=Response theme={null}
┌─displayName()─┐
│ production │
└───────────────┘
```
dumpColumnStructure
Introduced in: v1.1.0
Outputs a detailed description of the internal structure of a column and its data type.
**Syntax**
```sql theme={null}
dumpColumnStructure(x)
```
**Arguments**
* `x` — Value for which to get the description of. [`Any`](/reference/data-types)
**Returned value**
Returns a description of the column structure used for representing the value. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'));
```
```response title=Response theme={null}
┌─dumpColumnStructure(CAST('2018-01-01 01:02:03', 'DateTime'))─┐
│ DateTime, Const(size = 1, UInt32(size = 1)) │
└──────────────────────────────────────────────────────────────┘
```
enabledProfiles
Introduced in: v21.9.0
Returns an array of setting profile names which are enabled for the current user.
**Syntax**
```sql theme={null}
enabledProfiles()
```
**Arguments**
* None.
**Returned value**
Returns an array of setting profile names which are enabled for the current user. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT enabledProfiles();
```
```response title=Response theme={null}
┌─enabledProfiles()─────────────────────────────────────────────────┐
│ ['default', 'readonly_user', 'web_analytics', 'batch_processing'] │
└───────────────────────────────────────────────────────────────────┘
```
enabledRoles
Introduced in: v21.9.0
Returns an array of the roles which are enabled for the current user.
**Syntax**
```sql theme={null}
enabledRoles()
```
**Arguments**
* None.
**Returned value**
Returns an array of role names which are enabled for the current user. [`Array(String)`](/reference/data-types/array)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT enabledRoles();
```
```response title=Response theme={null}
┌─enabledRoles()─────────────────────────────────────────────────┐
│ ['general_data', 'sql-console-role:jane.smith@clickhouse.com'] │
└────────────────────────────────────────────────────────────────┘
```
errorCodeToName
Introduced in: v20.12.0
Returns the textual name of a numeric ClickHouse error code.
The mapping from numeric error codes to error names is available [here](https://github.com/ClickHouse/ClickHouse/blob/master/src/Common/ErrorCodes.cpp).
**Syntax**
```sql theme={null}
errorCodeToName(error_code)
```
**Arguments**
* `error_code` — ClickHouse error code. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns the textual name of `error_code`. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT errorCodeToName(252);
```
```response title=Response theme={null}
┌─errorCodeToName(252)─┐
│ TOO_MANY_PARTS │
└──────────────────────┘
```
file
Introduced in: v21.3.0
Reads a file as a string and loads the data into the specified column.
The file content is not interpreted.
Also see the [`file`](/reference/functions/table-functions/file) table function.
**Syntax**
```sql theme={null}
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`](/reference/data-types/string)
* `default` — The value returned if the file does not exist or cannot be accessed. [`String`](/reference/data-types/string) or [`NULL`](/reference/syntax#null)
**Returned value**
Returns the file content as a string. [`String`](/reference/data-types/string)
**Examples**
**Insert files into a table**
```sql title=Query theme={null}
INSERT INTO table SELECT file('a.txt'), file('b.txt');
```
```response title=Response theme={null}
```
filesystemAvailable
Introduced in: v20.1.0
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`](/reference/functions/regular-functions/other-functions#filesystemUnreserved)) because some space is reserved for the operating system.
**Syntax**
```sql theme={null}
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`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the amount of remaining space available in bytes. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT formatReadableSize(filesystemAvailable()) AS "Available space";
```
```response title=Response theme={null}
┌─Available space─┐
│ 30.75 GiB │
└─────────────────┘
```
filesystemCapacity
Introduced in: v20.1.0
Returns the capacity of the filesystem in bytes.
Needs the [path](/reference/settings/server-settings/settings#path) to the data directory to be configured.
**Syntax**
```sql theme={null}
filesystemCapacity([disk_name])
```
**Arguments**
* `disk_name` — Optional. The disk name to get the capacity for. If omitted, uses the default disk. [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the capacity of the filesystem in bytes. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT formatReadableSize(filesystemCapacity()) AS "Capacity";
```
```response title=Response theme={null}
┌─Capacity──┐
│ 39.32 GiB │
└───────────┘
```
filesystemUnreserved
Introduced in: v22.12.0
Returns the total amount of free space on the filesystem hosting the database persistence (previously `filesystemFree`).
See also [`filesystemAvailable`](#filesystemAvailable).
**Syntax**
```sql theme={null}
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`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring)
**Returned value**
Returns the amount of free space in bytes. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT formatReadableSize(filesystemUnreserved()) AS "Free space";
```
```response title=Response theme={null}
┌─Free space─┐
│ 32.39 GiB │
└────────────┘
```
finalizeAggregation
Introduced in: v1.1.0
Given an aggregation state, this function returns the result of aggregation (or the finalized state when using a [-State](/reference/functions/aggregate-functions/combinators#-state) combinator).
**Syntax**
```sql theme={null}
finalizeAggregation(state)
```
**Arguments**
* `state` — State of aggregation. [`AggregateFunction`](/reference/data-types/aggregatefunction)
**Returned value**
Returns the finalized result of aggregation. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT finalizeAggregation(arrayReduce('maxState', [1, 2, 3]));
```
```response title=Response theme={null}
┌─finalizeAggregation(arrayReduce('maxState', [1, 2, 3]))─┐
│ 3 │
└─────────────────────────────────────────────────────────┘
```
**Combined with initializeAggregation**
```sql title=Query theme={null}
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);
```
```response title=Response theme={null}
┌─number─┬─one_row_sum─┬─cumulative_sum─┐
│ 0 │ 0 │ 0 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 3 │
│ 3 │ 3 │ 6 │
│ 4 │ 4 │ 10 │
└────────┴─────────────┴────────────────┘
```
flipCoordinates
Introduced in: v25.11.0
Flips the x and y coordinates of geometric objects. This operation swaps latitude and longitude, which is useful for converting between different coordinate systems or correcting coordinate order.
For a Point, it swaps the x and y coordinates. For complex geometries (LineString, Polygon, MultiPolygon, Ring, MultiLineString), it recursively applies the transformation to each coordinate pair.
The function supports both individual geometry types (Point, Ring, Polygon, MultiPolygon, LineString, MultiLineString) and the Geometry variant type.
**Syntax**
```sql theme={null}
flipCoordinates(geometry)
```
**Arguments**
* `geometry` — The geometry to transform. Supported types: Point (Tuple(Float64, Float64)), Ring (Array(Point)), Polygon (Array(Ring)), MultiPolygon (Array(Polygon)), LineString (Array(Point)), MultiLineString (Array(LineString)), or Geometry (a variant containing any of these types).
**Returned value**
The geometry with flipped coordinates. The return type matches the input type. [`Point`](/reference/data-types/geo#point) or [`Ring`](/reference/data-types/geo#ring) or [`Polygon`](/reference/data-types/geo#polygon) or [`MultiPolygon`](/reference/data-types/geo#multipolygon) or [`LineString`](/reference/data-types/geo#linestring) or [`MultiLineString`](/reference/data-types/geo#multilinestring) or [`Geometry`](/reference/data-types/geo)
**Examples**
**basic\_point**
```sql title=Query theme={null}
SELECT flipCoordinates((1.0, 2.0));
```
```response title=Response theme={null}
(2.0, 1.0)
```
**ring**
```sql title=Query theme={null}
SELECT flipCoordinates([(1.0, 2.0), (3.0, 4.0)]);
```
```response title=Response theme={null}
[(2.0, 1.0), (4.0, 3.0)]
```
**polygon**
```sql title=Query theme={null}
SELECT flipCoordinates([[(1.0, 2.0), (3.0, 4.0)], [(5.0, 6.0), (7.0, 8.0)]]);
```
```response title=Response theme={null}
[[(2.0, 1.0), (4.0, 3.0)], [(6.0, 5.0), (8.0, 7.0)]]
```
**geometry\_wkt**
```sql title=Query theme={null}
SELECT flipCoordinates(readWkt('POINT(10 20)'));
```
```response title=Response theme={null}
(20, 10)
```
**geometry\_polygon\_wkt**
```sql title=Query theme={null}
SELECT flipCoordinates(readWkt('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))'));
```
```response title=Response theme={null}
[[(0, 0), (0, 5), (5, 5), (5, 0), (0, 0)]]
```
formatQuery
Introduced in: v23.10.0
Returns a formatted, possibly multi-line, version of the given SQL query. Throws in case of a parsing error.
\[example:multiline]
**Syntax**
```sql theme={null}
formatQuery(query)
```
**Arguments**
* `query` — The SQL query to be formatted. [String](/reference/data-types/string)
**Returned value**
The formatted query [`String`](/reference/data-types/string)
**Examples**
**multiline**
```sql title=Query theme={null}
SELECT formatQuery('select a, b FRom tab WHERE a > 3 and b < 3');
```
```response title=Response theme={null}
SELECT
a,
b
FROM tab
WHERE (a > 3) AND (b < 3)
```
formatQueryOrNull
Introduced in: v23.11.0
Returns a formatted, possibly multi-line, version of the given SQL query. Returns NULL in case of a parsing error.
\[example:multiline]
**Syntax**
```sql theme={null}
formatQueryOrNull(query)
```
**Arguments**
* `query` — The SQL query to be formatted. [String](/reference/data-types/string)
**Returned value**
The formatted query [`String`](/reference/data-types/string)
**Examples**
**multiline**
```sql title=Query theme={null}
SELECT formatQuery('select a, b FRom tab WHERE a > 3 and b < 3');
```
```response title=Response theme={null}
SELECT
a,
b
FROM tab
WHERE (a > 3) AND (b < 3)
```
formatQuerySingleLine
Introduced in: v23.10.0
Like formatQuery() but the returned formatted string contains no line breaks. Throws in case of a parsing error.
\[example:multiline]
**Syntax**
```sql theme={null}
formatQuerySingleLine(query)
```
**Arguments**
* `query` — The SQL query to be formatted. [String](/reference/data-types/string)
**Returned value**
The formatted query [`String`](/reference/data-types/string)
**Examples**
**multiline**
```sql title=Query theme={null}
SELECT formatQuerySingleLine('select a, b FRom tab WHERE a > 3 and b < 3');
```
```response title=Response theme={null}
SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)
```
formatQuerySingleLineOrNull
Introduced in: v23.11.0
Like formatQuery() but the returned formatted string contains no line breaks. Returns NULL in case of a parsing error.
\[example:multiline]
**Syntax**
```sql theme={null}
formatQuerySingleLineOrNull(query)
```
**Arguments**
* `query` — The SQL query to be formatted. [`String`](/reference/data-types/string)
**Returned value**
The formatted query [`String`](/reference/data-types/string)
**Examples**
**multiline**
```sql title=Query theme={null}
SELECT formatQuerySingleLine('select a, b FRom tab WHERE a > 3 and b < 3');
```
```response title=Response theme={null}
SELECT a, b FROM tab WHERE (a > 3) AND (b < 3)
```
formatReadableDecimalSize
Introduced in: v22.11.0
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`](#parseReadableSize).
**Syntax**
```sql theme={null}
formatReadableDecimalSize(x)
```
**Arguments**
* `x` — Size in bytes. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns a readable, rounded size with suffix as a string. [`String`](/reference/data-types/string)
**Examples**
**Format file sizes**
```sql title=Query theme={null}
SELECT
arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
formatReadableDecimalSize(filesize_bytes) AS filesize
```
```response title=Response theme={null}
┌─filesize_bytes─┬─filesize───┐
│ 1 │ 1.00 B │
│ 1024 │ 1.02 KB │
│ 1048576 │ 1.05 MB │
│ 192851925 │ 192.85 MB │
└────────────────┴────────────┘
```
formatReadableQuantity
Introduced in: v20.10.0
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**
```sql theme={null}
formatReadableQuantity(x)
```
**Arguments**
* `x` — A number to format. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns a rounded number with suffix as a string. [`String`](/reference/data-types/string)
**Examples**
**Format numbers with suffixes**
```sql title=Query theme={null}
SELECT
arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
formatReadableQuantity(number) AS number_for_humans
```
```response title=Response theme={null}
┌─────────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.0
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`](#parseReadableSize), [`parseReadableSizeOrZero`](#parseReadableSizeOrZero), and [`parseReadableSizeOrNull`](#parseReadableSizeOrNull).
This function accepts any numeric type as input, but internally it casts them to `Float64`. Results might be suboptimal with large values.
**Syntax**
```sql theme={null}
formatReadableSize(x)
```
**Aliases**: `FORMAT_BYTES`
**Arguments**
* `x` — Size in bytes. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Returns a readable, rounded size with suffix as a string. [`String`](/reference/data-types/string)
**Examples**
**Format file sizes**
```sql title=Query theme={null}
SELECT
arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
formatReadableSize(filesize_bytes) AS filesize
```
```response title=Response theme={null}
┌─filesize_bytes─┬─filesize───┐
│ 1 │ 1.00 B │
│ 1024 │ 1.00 KiB │
│ 1048576 │ 1.00 MiB │
│ 192851925 │ 183.92 MiB │
└────────────────┴────────────┘
```
formatReadableTimeDelta
Introduced in: v20.12.0
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**
```sql theme={null}
formatReadableTimeDelta(column[, maximum_unit, minimum_unit])
```
**Arguments**
* `column` — A column with a numeric time delta. [`Float64`](/reference/data-types/float)
* `maximum_unit` — Optional. Maximum unit to show. Acceptable values: `nanoseconds`, `microseconds`, `milliseconds`, `seconds`, `minutes`, `hours`, `days`, `months`, `years`. Default value: `years`. [`const String`](/reference/data-types/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`](/reference/data-types/string)
**Returned value**
Returns a time delta as a string. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
arrayJoin([100, 12345, 432546534]) AS elapsed,
formatReadableTimeDelta(elapsed) AS time_delta
```
```response title=Response theme={null}
┌────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**
```sql title=Query theme={null}
SELECT
arrayJoin([100, 12345, 432546534]) AS elapsed,
formatReadableTimeDelta(elapsed, 'minutes') AS time_delta
```
```response title=Response theme={null}
┌────elapsed─┬─time_delta─────────────────────────────────────────────────────┐
│ 100 │ 1 minute and 40 seconds │
│ 12345 │ 205 minutes and 45 seconds │
│ 432546534 │ 7209108 minutes and 54 seconds │
└────────────┴─────────────────────────────────────────────────────────────────┘
```
fuzzQuery
Introduced in: v26.2.0
Parses the given query string and applies random AST mutations (fuzzing) to it. Returns the fuzzed query as a string. Non-deterministic: each call may produce a different result. Requires `allow_fuzz_query_functions = 1`.
**Syntax**
```sql theme={null}
fuzzQuery(query)
```
**Arguments**
* `query` — The SQL query to be fuzzed. [String](/reference/data-types/string)
**Returned value**
The fuzzed query string [`String`](/reference/data-types/string)
**Examples**
**basic**
```sql title=Query theme={null}
SET allow_fuzz_query_functions = 1; SELECT fuzzQuery('SELECT 1');
```
```response title=Response theme={null}
```
generateRandomStructure
Introduced in: v23.5.0
Generates random table structure in the format `column1_name column1_type, column2_name column2_type, ...`.
**Syntax**
```sql theme={null}
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`](/reference/data-types/int-uint)
* `seed` — Random seed to produce stable results. If seed is not specified or set to `Null`, it is randomly generated. [`UInt64`](/reference/data-types/int-uint)
**Returned value**
Randomly generated table structure. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT generateRandomStructure()
```
```response title=Response theme={null}
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**
```sql title=Query theme={null}
SELECT generateRandomStructure(1)
```
```response title=Response theme={null}
c1 Map(UInt256, UInt16)
```
**with specified seed**
```sql title=Query theme={null}
SELECT generateRandomStructure(NULL, 33)
```
```response title=Response theme={null}
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: v25.1.0
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`](/reference/settings/server-settings/settings#series_keeper_path) in the server configuration.
**Syntax**
```sql theme={null}
generateSerialID(series_identifier[, start_value])
```
**Arguments**
* `series_identifier` — Series identifier [`const String`](/reference/data-types/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 [`UInt*`](/reference/data-types/int-uint)
**Returned value**
Returns sequential numbers starting from the previous counter value. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**first call**
```sql title=Query theme={null}
SELECT generateSerialID('id1')
```
```response title=Response theme={null}
┌─generateSerialID('id1')──┐
│ 1 │
└──────────────────────────┘
```
**second call**
```sql title=Query theme={null}
SELECT generateSerialID('id1')
```
```response title=Response theme={null}
┌─generateSerialID('id1')──┐
│ 2 │
└──────────────────────────┘
```
**column call**
```sql title=Query theme={null}
SELECT *, generateSerialID('id1') FROM test_table
```
```response title=Response theme={null}
┌─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**
```sql title=Query theme={null}
SELECT generateSerialID('id2', 100)
```
```response title=Response theme={null}
┌─generateSerialID('id2', 100)──┐
│ 100 │
└───────────────────────────────┘
```
**with start value second call**
```sql title=Query theme={null}
SELECT generateSerialID('id2', 100)
```
```response title=Response theme={null}
┌─generateSerialID('id2', 100)──┐
│ 101 │
└───────────────────────────────┘
```
getClientHTTPHeader
Introduced in: v24.5.0
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.
**Setting `allow_get_client_http_header` is required**
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.
**Syntax**
```sql theme={null}
getClientHTTPHeader(name)
```
**Arguments**
* `name` — The HTTP header name. [`String`](/reference/data-types/string)
**Returned value**
Returns the value of the header. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getClientHTTPHeader('Content-Type');
```
```response title=Response theme={null}
┌─getClientHTTPHeader('Content-Type')─┐
│ application/x-www-form-urlencoded │
└─────────────────────────────────────┘
```
getMacro
Introduced in: v20.1.0
Returns the value of a macro from the server configuration file.
Macros are defined in the [``](/reference/settings/server-settings/settings#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**
```sql theme={null}
getMacro(name)
```
**Arguments**
* `name` — The name of the macro to retrieve. [`const String`](/reference/data-types/string)
**Returned value**
Returns the value of the specified macro. [`String`](/reference/data-types/string)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT getMacro('test');
```
```response title=Response theme={null}
┌─getMacro('test')─┐
│ Value │
└──────────────────┘
```
getMaxTableNameLengthForDatabase
Introduced in: v25.1.0
Returns the maximum table name length in a specified database.
**Syntax**
```sql theme={null}
getMaxTableNameLengthForDatabase(database_name)
```
**Arguments**
* `database_name` — The name of the specified database. [`String`](/reference/data-types/string)
**Returned value**
Returns the length of the maximum table name, an Integer
**Examples**
**typical**
```sql title=Query theme={null}
SELECT getMaxTableNameLengthForDatabase('default');
```
```response title=Response theme={null}
┌─getMaxTableNameLengthForDatabase('default')─┐
│ 206 │
└─────────────────────────────────────────────┘
```
getMergeTreeSetting
Introduced in: v25.6.0
Returns the current value of a MergeTree setting.
**Syntax**
```sql theme={null}
getMergeTreeSetting(setting_name)
```
**Arguments**
* `setting_name` — The setting name. [`String`](/reference/data-types/string)
**Returned value**
Returns the merge tree setting's current value.
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getMergeTreeSetting('index_granularity');
```
```response title=Response theme={null}
┌─getMergeTreeSetting('index_granularity')─┐
│ 8192 │
└──────────────────────────────────────────┘
```
getOSKernelVersion
Introduced in: v21.11.0
Returns a string with the OS kernel version.
**Syntax**
```sql theme={null}
getOSKernelVersion()
```
**Arguments**
* None.
**Returned value**
Returns the current OS kernel version. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getOSKernelVersion();
```
```response title=Response theme={null}
┌─getOSKernelVersion()────┐
│ Linux 4.15.0-55-generic │
└─────────────────────────┘
```
getServerPort
Introduced in: v21.10.0
Returns the server's port number for a given protocol.
**Syntax**
```sql theme={null}
getServerPort(port_name)
```
**Arguments**
* `port_name` — The name of the port. [`String`](/reference/data-types/string)
**Returned value**
Returns the server port number. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getServerPort('tcp_port');
```
```response title=Response theme={null}
┌─getServerPort('tcp_port')─┐
│ 9000 │
└───────────────────────────┘
```
getServerSetting
Introduced in: v25.6.0
Returns the currently set value, given a server setting name.
**Syntax**
```sql theme={null}
getServerSetting(setting_name')
```
**Arguments**
* `setting_name` — The server setting name. [`String`](/reference/data-types/string)
**Returned value**
Returns the server setting's current value. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getServerSetting('allow_use_jemalloc_memory');
```
```response title=Response theme={null}
┌─getServerSetting('allow_use_jemalloc_memory')─┐
│ true │
└───────────────────────────────────────────────┘
```
getSetting
Introduced in: v20.7.0
Returns the current value of a setting.
**Syntax**
```sql theme={null}
getSetting(setting_name)
```
**Arguments**
* `setting_Name` — The setting name. [`const String`](/reference/data-types/string)
**Returned value**
Returns the setting's current value. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getSetting('enable_analyzer');
SET enable_analyzer = false;
SELECT getSetting('enable_analyzer');
```
```response title=Response theme={null}
┌─getSetting('⋯_analyzer')─┐
│ true │
└──────────────────────────┘
┌─getSetting('⋯_analyzer')─┐
│ false │
└──────────────────────────┘
```
getSettingOrDefault
Introduced in: v24.10.0
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**
```sql theme={null}
getSettingOrDefault(setting_name, default_value)
```
**Arguments**
* `setting_name` — The setting name. [`String`](/reference/data-types/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**
```sql title=Query theme={null}
SELECT getSettingOrDefault('custom_undef1', 'my_value');
SELECT getSettingOrDefault('custom_undef2', 100);
SELECT getSettingOrDefault('custom_undef3', NULL);
```
```response title=Response theme={null}
my_value
100
NULL
```
getSizeOfEnumType
Introduced in: v1.1.0
Returns the number of fields in the given [`Enum`](/reference/data-types/enum).
**Syntax**
```sql theme={null}
getSizeOfEnumType(x)
```
**Arguments**
* `x` — Value of type `Enum`. [`Enum`](/reference/data-types/enum)
**Returned value**
Returns the number of fields with `Enum` input values. [`UInt8/16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT getSizeOfEnumType(CAST('a' AS Enum8('a' = 1, 'b' = 2))) AS x;
```
```response title=Response theme={null}
┌─x─┐
│ 2 │
└───┘
```
getSubcolumn
Introduced in: v23.3.0
Receives the expression or identifier and constant string with the name of subcolumn.
Returns requested subcolumn extracted from the expression.
**Syntax**
```sql theme={null}
getSubcolumn(nested_value, subcolumn_name)
```
**Arguments**
* None.
**Returned value**
**Examples**
**getSubcolumn**
```sql title=Query theme={null}
SELECT getSubcolumn(array_col, 'size0'), getSubcolumn(tuple_col, 'elem_name')
```
```response title=Response theme={null}
```
getTypeSerializationStreams
Introduced in: v22.6.0
Enumerates stream paths of a data type.
This function is intended for developmental use.
**Syntax**
```sql theme={null}
getTypeSerializationStreams(col)
```
**Arguments**
* `col` — Column or string representation of a data-type from which the data type will be detected. [`Any`](/reference/data-types)
**Returned value**
Returns an array with all the serialization sub-stream paths. [`Array(String)`](/reference/data-types/array)
**Examples**
**tuple**
```sql title=Query theme={null}
SELECT getTypeSerializationStreams(tuple('a', 1, 'b', 2))
```
```response title=Response theme={null}
['{TupleElement(1), Regular}','{TupleElement(2), Regular}','{TupleElement(3), Regular}','{TupleElement(4), Regular}']
```
**map**
```sql title=Query theme={null}
SELECT getTypeSerializationStreams('Map(String, Int64)')
```
```response title=Response theme={null}
['{ArraySizes}','{ArrayElements, TupleElement(keys), Regular}','{ArrayElements, TupleElement(values), Regular}']
```
globalVariable
Introduced in: v20.5.0
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**
```sql theme={null}
globalVariable(name)
```
**Arguments**
* `name` — Global variable name. [`String`](/reference/data-types/string)
**Returned value**
Returns the value of variable `name`. [`Any`](/reference/data-types)
**Examples**
**globalVariable**
```sql title=Query theme={null}
SELECT globalVariable('max_allowed_packet')
```
```response title=Response theme={null}
67108864
```
hasColumnInTable
Introduced in: v1.1.0
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**
```sql theme={null}
hasColumnInTable([hostname[, username[, password]],]database, table, column)
```
**Arguments**
* `database` — Name of the database. [`const String`](/reference/data-types/string)
* `table` — Name of the table. [`const String`](/reference/data-types/string)
* `column` — Name of the column. [`const String`](/reference/data-types/string)
* `hostname` — Optional. Remote server name to perform the check on. [`const String`](/reference/data-types/string)
* `username` — Optional. Username for remote server. [`const String`](/reference/data-types/string)
* `password` — Optional. Password for remote server. [`const String`](/reference/data-types/string)
**Returned value**
Returns `1` if the given column exists, `0` otherwise. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Check an existing column**
```sql title=Query theme={null}
SELECT hasColumnInTable('system','metrics','metric')
```
```response title=Response theme={null}
1
```
**Check a non-existing column**
```sql title=Query theme={null}
SELECT hasColumnInTable('system','metrics','non-existing_column')
```
```response title=Response theme={null}
0
```
hasThreadFuzzer
Introduced in: v20.6.0
Returns whether the thread fuzzer is enabled.
THis function is only useful for testing and debugging.
**Syntax**
```sql theme={null}
hasThreadFuzzer()
```
**Arguments**
* None.
**Returned value**
Returns whether Thread Fuzzer is effective. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Check Thread Fuzzer status**
```sql title=Query theme={null}
SELECT hasThreadFuzzer()
```
```response title=Response theme={null}
┌─hasThreadFuzzer()─┐
│ 0 │
└───────────────────┘
```
highlightQuery
Introduced in: v26.5.0
Parses a ClickHouse SQL query string and returns an array of highlighted ranges for syntax highlighting.
Each range is a named tuple with the beginning position (in bytes), the end position, and the highlight type.
The highlight types describe the syntactic role of the fragment (keyword, identifier, function, etc.)
and can be used to assign colors in a UI. Inside LIKE and REGEXP string patterns, metacharacters
and escape characters are highlighted separately.
**Syntax**
```sql theme={null}
highlightQuery(query)
```
**Arguments**
* `query` — A ClickHouse SQL query string. String.
**Returned value**
An array of named tuples `(begin UInt64, end UInt64, type Enum8(...))` representing highlighted ranges. [`Array(Tuple(begin UInt64, end UInt64, type Enum8(...)))`](/reference/data-types/array)
**Examples**
**simple**
```sql title=Query theme={null}
SELECT highlightQuery('SELECT 1')
```
```response title=Response theme={null}
[(0,6,'keyword'),(7,8,'number')]
```
hostName
Introduced in: v20.5.0
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**
```sql theme={null}
hostName()
```
**Aliases**: `hostname`
**Arguments**
* None.
**Returned value**
Returns the host name. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT hostName()
```
```response title=Response theme={null}
┌─hostName()─┐
│ clickhouse │
└────────────┘
```
icebergBucket
Introduced in: v25.5.0
Implements logic for the [iceberg bucket transform](https://iceberg.apache.org/spec/#bucket-transform-details.)
**Syntax**
```sql theme={null}
icebergBucket(N, value)
```
**Arguments**
* `N` — The number of buckets, modulo. [`const (U)Int*`](/reference/data-types/int-uint)
* `value` — The source value to transform. [`(U)Int*`](/reference/data-types/int-uint) or [`Bool`](/reference/data-types/boolean) or [`Decimal`](/reference/data-types/decimal) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string) or [`FixedString`](/reference/data-types/fixedstring) or [`UUID`](/reference/data-types/uuid) or [`Date`](/reference/data-types/date) or [`Time`](/reference/data-types/time) or [`DateTime`](/reference/data-types/datetime)
**Returned value**
Returns a 32-bit hash of the source value. [`Int32`](/reference/data-types/int-uint)
**Examples**
**Example**
```sql title=Query theme={null}
SELECT icebergBucket(5, 1.0 :: Float32)
```
```response title=Response theme={null}
4
```
icebergTruncate
Introduced in: v25.3.0
Implements logic of iceberg truncate transform: [https://iceberg.apache.org/spec/#truncate-transform-details](https://iceberg.apache.org/spec/#truncate-transform-details).
**Syntax**
```sql theme={null}
icebergTruncate(N, value)
```
**Arguments**
* `value` — The value to transform. [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
The same type as the argument
**Examples**
**Example**
```sql title=Query theme={null}
SELECT icebergTruncate(3, 'iceberg')
```
```response title=Response theme={null}
ice
```
identity
Introduced in: v1.1.0
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**
```sql theme={null}
identity(x)
```
**Arguments**
* `x` — Input value. [`Any`](/reference/data-types)
**Returned value**
Returns the input value unchanged. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT identity(42)
```
```response title=Response theme={null}
42
```
ignore
Introduced in: v1.1.0
Accepts arbitrary arguments and unconditionally returns `0`.
**Syntax**
```sql theme={null}
ignore(x)
```
**Arguments**
* `x` — An input value which is unused and passed only so as to avoid a syntax error. [`Any`](/reference/data-types)
**Returned value**
Always returns `0`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT ignore(0, 'ClickHouse', NULL)
```
```response title=Response theme={null}
┌─ignore(0, 'ClickHouse', NULL)─┐
│ 0 │
└───────────────────────────────┘
```
indexHint
Introduced in: v1.1.0
This function is intended for debugging and introspection.
It ignores its argument and always returns 1.
The arguments are not evaluated.
During index analysis, the argument of this function is assumed to not be wrapped in `indexHint`.
This allows you 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.
When you run:
```sql theme={null}
SELECT * FROM test WHERE key = 123;
```
ClickHouse does two things:
1. Uses the index to find which granules (blocks of \~8192 rows) might contain `key = 123`
2. Reads those granules and filters them row-by-row to return only rows where `key = 123`
So even if it reads 8,192 rows from disk, it only returns the 1 row that actually matches.
With `indexHint`, when you run:
```sql theme={null}
SELECT * FROM test WHERE indexHint(key = 123);
```
ClickHouse does only one thing:
1. Uses the index to find which granules might contain key = 123 and returns all rows from those granules **without** filtering.
It returns all 8,192 rows, including rows where `key = 456`, `key = 789`, etc. (Everything that happened to be stored in the same granule.)
`indexHint()` is not for performance. It's for debugging and understanding how ClickHouse's index works:
* Which granules does my condition select?
* How many rows are in those granules?
* Is my index being used effectively?
Note: It is not possible to optimize a query with the `indexHint` function. The `indexHint` function does not optimize the query, as it does not provide any additional information for the query analysis. Having an expression inside the `indexHint` function is not anyhow better than without the `indexHint` function. The `indexHint` function can be used only for introspection and debugging purposes and it does not improve performance. If you see the usage of `indexHint` by anyone other than ClickHouse contributors, it is likely a mistake and you should remove it.
**Syntax**
```sql theme={null}
indexHint(expression)
```
**Arguments**
* `expression` — Any expression for index range selection. [`Expression`](/reference/data-types/special-data-types/expression)
**Returned value**
Returns `1` in all cases. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example with date filtering**
```sql title=Query theme={null}
SELECT FlightDate AS k, count() FROM ontime WHERE indexHint(k = '2025-09-15') GROUP BY k ORDER BY k ASC;
```
```response title=Response theme={null}
┌──────────k─┬─count()─┐
│ 2025-09-14 │ 7071 │
│ 2025-09-15 │ 16428 │
│ 2025-09-16 │ 1077 │
│ 2025-09-30 │ 8167 │
└────────────┴─────────┘
```
initialQueryID
Introduced in: v1.1.0
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`](/reference/system-tables/query_log).
In contrast to [`queryID`](/reference/functions/regular-functions/other-functions#queryID) function, `initialQueryID` returns the same results on different shards.
**Syntax**
```sql theme={null}
initialQueryID()
```
**Aliases**: `initial_query_id`
**Arguments**
* None.
**Returned value**
Returns the ID of the initial current query. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
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());
```
```response title=Response theme={null}
┌─count(DISTINCT t)─┐
│ 1 │
└───────────────────┘
```
initialQueryStartTime
Introduced in: v25.4.0
Returns the start time of the initial current query.
`initialQueryStartTime` returns the same results on different shards.
**Syntax**
```sql theme={null}
initialQueryStartTime()
```
**Aliases**: `initial_query_start_time`
**Arguments**
* None.
**Returned value**
Returns the start time of the initial current query. [`DateTime`](/reference/data-types/datetime)
**Examples**
**Usage example**
```sql title=Query theme={null}
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());
```
```response title=Response theme={null}
┌─count(DISTINCT t)─┐
│ 1 │
└───────────────────┘
```
initializeAggregation
Introduced in: v20.6.0
Calculates the result of an aggregate function based on a single value.
This function can be used to initialize aggregate functions with combinator [-State](/reference/functions/aggregate-functions/combinators#-state).
You can create states of aggregate functions and insert them to columns of type [`AggregateFunction`](/reference/data-types/aggregatefunction) or use initialized aggregates as default values.
**Syntax**
```sql theme={null}
initializeAggregation(aggregate_function, arg1[, arg2, ...])
```
**Arguments**
* `aggregate_function` — Name of the aggregation function to initialize. [`String`](/reference/data-types/string)
* `arg1[, arg2, ...]` — Arguments of the aggregate function. [`Any`](/reference/data-types)
**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`](/reference/data-types)
**Examples**
**Basic usage with uniqState**
```sql title=Query theme={null}
SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000));
```
```response title=Response theme={null}
┌─uniqMerge(state)─┐
│ 3 │
└──────────────────┘
```
**Usage with sumState and finalizeAggregation**
```sql title=Query theme={null}
SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5));
```
```response title=Response theme={null}
┌─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.0
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](/reference/syntax#literals) are constant expressions.
This function is mostly intended for development, debugging and demonstration.
**Syntax**
```sql theme={null}
isConstant(x)
```
**Arguments**
* `x` — An expression to check. [`Any`](/reference/data-types)
**Returned value**
Returns `1` if `x` is constant, `0` if `x` is non-constant. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Constant expression**
```sql title=Query theme={null}
SELECT isConstant(x + 1)
FROM (SELECT 43 AS x)
```
```response title=Response theme={null}
┌─isConstant(plus(x, 1))─┐
│ 1 │
└────────────────────────┘
```
**Constant with function**
```sql title=Query theme={null}
WITH 3.14 AS pi
SELECT isConstant(cos(pi))
```
```response title=Response theme={null}
┌─isConstant(cos(pi))─┐
│ 1 │
└─────────────────────┘
```
**Non-constant expression**
```sql title=Query theme={null}
SELECT isConstant(number)
FROM numbers(1)
```
```response title=Response theme={null}
┌─isConstant(number)─┐
│ 0 │
└────────────────────┘
```
**Behavior of the now() function**
```sql title=Query theme={null}
SELECT isConstant(now())
```
```response title=Response theme={null}
┌─isConstant(now())─┐
│ 1 │
└───────────────────┘
```
isDecimalOverflow
Introduced in: v20.8.0
Checks if a decimal number has too many digits to fit properly in a Decimal data type with given precision.
**Syntax**
```sql theme={null}
isDecimalOverflow(value[, precision])
```
**Arguments**
* `value` — Decimal value to check. [`Decimal`](/reference/data-types/decimal)
* `precision` — Optional. The precision of the Decimal type. If omitted, the initial precision of the first argument is used. [`UInt8`](/reference/data-types/int-uint)
**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`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT isDecimalOverflow(toDecimal32(1000000000, 0), 9),
isDecimalOverflow(toDecimal32(1000000000, 0)),
isDecimalOverflow(toDecimal32(-1000000000, 0), 9),
isDecimalOverflow(toDecimal32(-1000000000, 0));
```
```response title=Response theme={null}
┌─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.0
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.
Only supports tables created with the `ENGINE = Join(ANY, LEFT, )` [statement](/reference/engines/table-engines/special/join).
**Syntax**
```sql theme={null}
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`](/reference/data-types/string)
* `value_column` — The name of the column of the table that contains required data. [`const String`](/reference/data-types/string)
* `join_keys` — A list of join keys. [`Any`](/reference/data-types)
**Returned value**
Returns list of values corresponded to list of keys. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
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));
```
```response title=Response theme={null}
┌─joinGet(db_test.id_val, 'val', toUInt32(1))─┐
│ 11 │
└─────────────────────────────────────────────┘
```
**Usage with table from current database**
```sql title=Query theme={null}
USE db_test;
SELECT joinGet(id_val, 'val', toUInt32(2));
```
```response title=Response theme={null}
┌─joinGet(id_val, 'val', toUInt32(2))─┐
│ 12 │
└─────────────────────────────────────┘
```
**Using arrays as join keys**
```sql title=Query theme={null}
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);
```
```response title=Response theme={null}
┌─joinGet(some_table, 'name', 1, 11)─┐
│ a │
└────────────────────────────────────┘
```
joinGetOrNull
Introduced in: v20.4.0
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`](#joinGet) it returns `NULL` when the key is missing.
Only supports tables created with the `ENGINE = Join(ANY, LEFT, )` [statement](/reference/engines/table-engines/special/join).
**Syntax**
```sql theme={null}
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`](/reference/data-types/string)
* `value_column` — The name of the column of the table that contains required data. [`const String`](/reference/data-types/string)
* `join_keys` — A list of join keys. [`Any`](/reference/data-types)
**Returned value**
Returns a list of values corresponding to the list of keys, or `NULL` if a key is not found. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
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));
```
```response title=Response theme={null}
┌─joinGetOrNull(db_test.id_val, 'val', toUInt32(1))─┬─joinGetOrNull(db_test.id_val, 'val', toUInt32(999))─┐
│ 11 │ ᴺᵁᴸᴸ │
└───────────────────────────────────────────────────┴─────────────────────────────────────────────────────┘
```
lowCardinalityIndices
Introduced in: v18.12.0
Returns the position of a value in the dictionary of a [LowCardinality](/reference/data-types/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**
```sql theme={null}
lowCardinalityIndices(col)
```
**Arguments**
* `col` — A low cardinality column. [`LowCardinality`](/reference/data-types/lowcardinality)
**Returned value**
The position of the value in the dictionary of the current part. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage examples**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─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.0
Returns the dictionary values of a [LowCardinality](/reference/data-types/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**
```sql theme={null}
lowCardinalityKeys(col)
```
**Arguments**
* `col` — A low cardinality column. [`LowCardinality`](/reference/data-types/lowcardinality)
**Returned value**
Returns the dictionary keys. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**lowCardinalityKeys**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─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.0
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**
```sql theme={null}
materialize(x)
```
**Arguments**
* `x` — A constant. [`Any`](/reference/data-types)
**Returned value**
Returns a full column containing the constant value. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
-- 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'));
```
```response title=Response theme={null}
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.0
Calculates the minimum required sample size for an A/B test comparing means of a continuous metric in two samples.
Uses the formula described in [this article](https://towardsdatascience.com/required-sample-size-for-a-b-testing-6f6608dd330a).
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.
**Syntax**
```sql theme={null}
minSampleSizeContinuous(baseline, sigma, mde, power, alpha)
```
**Aliases**: `minSampleSizeContinous`
**Arguments**
* `baseline` — Baseline value of a metric. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `sigma` — Baseline standard deviation of a metric. [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/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*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `power` — Required statistical power of a test (1 - probability of Type II error). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float)
* `alpha` — Required significance level of a test (probability of Type I error). [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/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)`](/reference/data-types/tuple)
**Examples**
**minSampleSizeContinuous**
```sql title=Query theme={null}
SELECT minSampleSizeContinuous(112.25, 21.1, 0.03, 0.80, 0.05) AS sample_size
```
```response title=Response theme={null}
(616.2931945826209,108.8825,115.6175)
```
minSampleSizeConversion
Introduced in: v22.6.0
Calculates minimum required sample size for an A/B test comparing conversions (proportions) in two samples.
Uses the formula described in [this article](https://towardsdatascience.com/required-sample-size-for-a-b-testing-6f6608dd330a). 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).
**Syntax**
```sql theme={null}
minSampleSizeConversion(baseline, mde, power, alpha)
```
**Arguments**
* `baseline` — Baseline conversion. [`Float*`](/reference/data-types/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*`](/reference/data-types/float)
* `power` — Required statistical power of a test (1 - probability of Type II error). [`Float*`](/reference/data-types/float)
* `alpha` — Required significance level of a test (probability of Type I error). [`Float*`](/reference/data-types/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)`](/reference/data-types/tuple)
**Examples**
**minSampleSizeConversion**
```sql title=Query theme={null}
SELECT minSampleSizeConversion(0.25, 0.03, 0.80, 0.05) AS sample_size
```
```response title=Response theme={null}
(3396.077603219163,0.22,0.28)
```
neighbor
Introduced in: v20.1.0
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**
```sql theme={null}
neighbor(column, offset[, default_value])
```
**Arguments**
* `column` — The source column. [`Any`](/reference/data-types)
* `offset` — The offset from the current row. Positive values look forward, negative values look backward. [`Integer`](/reference/data-types/int-uint)
* `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`](/reference/data-types)
**Returned value**
Returns a value from the specified offset, or default if out of bounds. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT number, neighbor(number, 2) FROM system.numbers LIMIT 10;
```
```response title=Response theme={null}
┌─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**
```sql title=Query theme={null}
SELECT number, neighbor(number, 2, 999) FROM system.numbers LIMIT 10;
```
```response title=Response theme={null}
┌─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 │
└────────┴──────────────────────────┘
```
normalizeQuery
Introduced in: v20.8.0
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**
```sql theme={null}
normalizeQuery(x)
```
**Arguments**
* `x` — Sequence of characters. [`String`](/reference/data-types/string)
**Returned value**
Returns the given sequence of characters with placeholders. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT normalizeQuery('[1, 2, 3, x]') AS query
```
```response title=Response theme={null}
┌─query────┐
│ [?.., x] │
└──────────┘
```
normalizeQueryKeepNames
Introduced in: v21.2.0
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**
```sql theme={null}
normalizeQueryKeepNames(x)
```
**Arguments**
* `x` — Sequence of characters. [`String`](/reference/data-types/string)
**Returned value**
Returns the given sequence of characters with placeholders. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT normalizeQuery('SELECT 1 AS aComplexName123'), normalizeQueryKeepNames('SELECT 1 AS aComplexName123')
```
```response title=Response theme={null}
┌─normalizeQuery('SELECT 1 AS aComplexName123')─┬─normalizeQueryKeepNames('SELECT 1 AS aComplexName123')─┐
│ SELECT ? AS `?` │ SELECT ? AS aComplexName123 │
└───────────────────────────────────────────────┴────────────────────────────────────────────────────────┘
```
normalizedQueryHash
Introduced in: v20.8.0
Returns identical 64 bit hash values without the values of literals for similar queries.
Can be helpful in analyzing query logs.
**Syntax**
```sql theme={null}
normalizedQueryHash(x)
```
**Arguments**
* `x` — Sequence of characters. [`String`](/reference/data-types/string)
**Returned value**
Returns a 64 bit hash value. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT normalizedQueryHash('SELECT 1 AS `xyz`') != normalizedQueryHash('SELECT 1 AS `abc`') AS res
```
```response title=Response theme={null}
┌─res─┐
│ 1 │
└─────┘
```
normalizedQueryHashKeepNames
Introduced in: v21.2.0
Like [`normalizedQueryHash`](#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**
```sql theme={null}
normalizedQueryHashKeepNames(x)
```
**Arguments**
* `x` — Sequence of characters. [`String`](/reference/data-types/string)
**Returned value**
Returns a 64 bit hash value. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─normalizedQueryHash─┐
│ 0 │
└─────────────────────┘
┌─normalizedQueryHashKeepNames─┐
│ 1 │
└──────────────────────────────┘
```
obfuscateQuery
Introduced in: v26.4.0
Obfuscates a SQL query by replacing identifiers with random words and literals with random values while preserving query structure.
This function is useful for anonymizing queries before logging or sharing them for debugging purposes.
Different rows will produce different obfuscated results even for the same input query, which helps
maintain privacy when working with multiple queries.
The optional `tag` parameter prevents common subexpression elimination when the same function call
is used multiple times in a query. This ensures that each invocation produces a different obfuscated result.
Features:
* Replaces table names, column names, and aliases with random words
* Replaces numeric and string literals with random values
* Preserves the overall query structure and SQL syntax
* Produces different results for different rows
**Syntax**
```sql theme={null}
obfuscateQuery(query[, tag])
```
**Arguments**
* `query` — The SQL query to obfuscate. [`String`](/reference/data-types/string)
* `tag` — Optional. A value to prevent common subexpression elimination when the same function call is used multiple times.
**Returned value**
The obfuscated query with identifiers and literals replaced while preserving the original query structure. [`String`](/reference/data-types/string)
**Examples**
**Basic usage**
```sql title=Query theme={null}
SELECT obfuscateQuery('SELECT name, age FROM users WHERE age > 30')
```
```response title=Response theme={null}
SELECT fruit, number FROM table WHERE number > 12
```
**With tag to prevent common subexpression elimination**
```sql title=Query theme={null}
SELECT obfuscateQuery('SELECT * FROM t', 1), obfuscateQuery('SELECT * FROM t', 2)
```
```response title=Response theme={null}
SELECT a FROM b, SELECT c FROM d
```
**Different rows produce different results**
```sql title=Query theme={null}
SELECT obfuscateQuery('SELECT 1') AS a, obfuscateQuery('SELECT 1') AS b
```
```response title=Response theme={null}
A B
```
obfuscateQueryWithSeed
Introduced in: v26.4.0
Obfuscates a SQL query using a specified seed for deterministic results.
Unlike `obfuscateQuery()`, this function produces deterministic results when given the same seed.
This is useful when you need consistent obfuscation across multiple runs or when you want to
reproduce the same obfuscated query for testing or debugging purposes.
Features:
* Deterministic obfuscation based on the provided seed
* Same seed always produces the same obfuscated result
* Different seeds produce different results
* Preserves query structure like obfuscateQuery()
Use cases:
* Reproducible test cases
* Consistent anonymization across multiple runs
* Debugging with consistent obfuscated queries
**Syntax**
```sql theme={null}
obfuscateQueryWithSeed(query, seed)
```
**Arguments**
* `query` — The SQL query to obfuscate. [`String`](/reference/data-types/string)
* `seed` — The seed for obfuscation. The same seed produces deterministic results. [`Integer`](/reference/data-types/int-uint) or [`String`](/reference/data-types/string)
**Returned value**
The obfuscated query, deterministically generated based on the provided seed. [`String`](/reference/data-types/string)
**Examples**
**Deterministic obfuscation with integer seed**
```sql title=Query theme={null}
SELECT obfuscateQueryWithSeed('SELECT name FROM users', 42)
```
```response title=Response theme={null}
SELECT fruit FROM table
```
**Deterministic obfuscation with string seed**
```sql title=Query theme={null}
SELECT obfuscateQueryWithSeed('SELECT id, value FROM data', 'myseed')
```
```response title=Response theme={null}
SELECT a, b FROM c
```
**Same seed produces same result**
```sql title=Query theme={null}
SELECT obfuscateQueryWithSeed('SELECT 1', 100) = obfuscateQueryWithSeed('SELECT 1', 100)
```
```response title=Response theme={null}
true
```
parseReadableSize
Introduced in: v24.6.0
Given a string containing a byte size and `B`, `KiB`, `KB`, `MiB`, `MB`, etc. as a unit (i.e. [ISO/IEC 80000-13](https://en.wikipedia.org/wiki/ISO/IEC_80000) 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`](#formatReadableSize) and [`formatReadableDecimalSize`](#formatReadableDecimalSize).
**Syntax**
```sql theme={null}
parseReadableSize(x)
```
**Arguments**
* `x` — Readable size with ISO/IEC 80000-13 or decimal byte unit. [`String`](/reference/data-types/string)
**Returned value**
Returns the number of bytes, rounded up to the nearest integer. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB']) AS readable_sizes, parseReadableSize(readable_sizes) AS sizes;
```
```response title=Response theme={null}
┌─readable_sizes─┬───sizes─┐
│ 1 B │ 1 │
│ 1 KiB │ 1024 │
│ 3 MB │ 3000000 │
│ 5.314 KiB │ 5442 │
└────────────────┴─────────┘
```
parseReadableSizeOrNull
Introduced in: v24.6.0
Given a string containing a byte size and `B`, `KiB`, `KB`, `MiB`, `MB`, etc. as a unit (i.e. [ISO/IEC 80000-13](https://en.wikipedia.org/wiki/ISO/IEC_80000) 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`](#formatReadableSize) and [`formatReadableDecimalSize`](#formatReadableDecimalSize).
**Syntax**
```sql theme={null}
parseReadableSizeOrNull(x)
```
**Arguments**
* `x` — Readable size with ISO/IEC 80000-13 or decimal byte unit. [`String`](/reference/data-types/string)
**Returned value**
Returns the number of bytes, rounded up to the nearest integer, or `NULL` if unable to parse the input [`Nullable(UInt64)`](/reference/data-types/nullable)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrNull(readable_sizes) AS sizes;
```
```response title=Response theme={null}
┌─readable_sizes─┬───sizes─┐
│ 1 B │ 1 │
│ 1 KiB │ 1024 │
│ 3 MB │ 3000000 │
│ 5.314 KiB │ 5442 │
│ invalid │ ᴺᵁᴸᴸ │
└────────────────┴─────────┘
```
parseReadableSizeOrZero
Introduced in: v24.6.0
Given a string containing a byte size and `B`, `KiB`, `KB`, `MiB`, `MB`, etc. as a unit (i.e. [ISO/IEC 80000-13](https://en.wikipedia.org/wiki/ISO/IEC_80000) 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`](#formatReadableSize) and [`formatReadableDecimalSize`](#formatReadableDecimalSize).
**Syntax**
```sql theme={null}
parseReadableSizeOrZero(x)
```
**Arguments**
* `x` — Readable size with ISO/IEC 80000-13 or decimal byte unit. [`String`](/reference/data-types/string)
**Returned value**
Returns the number of bytes, rounded up to the nearest integer, or `0` if unable to parse the input. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT arrayJoin(['1 B', '1 KiB', '3 MB', '5.314 KiB', 'invalid']) AS readable_sizes, parseReadableSizeOrZero(readable_sizes) AS sizes;
```
```response title=Response theme={null}
┌─readable_sizes─┬───sizes─┐
│ 1 B │ 1 │
│ 1 KiB │ 1024 │
│ 3 MB │ 3000000 │
│ 5.314 KiB │ 5442 │
│ invalid │ 0 │
└────────────────┴─────────┘
```
parseTimeDelta
Introduced in: v22.7.0
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**
```sql theme={null}
parseTimeDelta(timestr)
```
**Arguments**
* `timestr` — A sequence of numbers followed by something resembling a time unit. [`String`](/reference/data-types/string)
**Returned value**
The number of seconds. [`Float64`](/reference/data-types/float)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT parseTimeDelta('11s+22min')
```
```response title=Response theme={null}
┌─parseTimeDelta('11s+22min')─┐
│ 1331 │
└─────────────────────────────┘
```
**Complex time units**
```sql title=Query theme={null}
SELECT parseTimeDelta('1yr2mo')
```
```response title=Response theme={null}
┌─parseTimeDelta('1yr2mo')─┐
│ 36806400 │
└──────────────────────────┘
```
partitionId
Introduced in: v21.4.0
Computes the [partition ID](/reference/engines/table-engines/mergetree-family/custom-partitioning-key).
This function is slow and should not be called for large numbers of rows.
**Syntax**
```sql theme={null}
partitionId(column1[, column2, ...])
```
**Aliases**: `partitionID`
**Arguments**
* `column1, column2, ...` — Column for which to return the partition ID.
**Returned value**
Returns the partition ID that the row belongs to. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─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.0
Returns the ID of the current query.
Other parameters of a query can be extracted from field `query_id` in the [`system.query_log`](/reference/system-tables/query_log) table.
In contrast to [`initialQueryID`](#initialQueryID) function, `queryID` can return different results on different shards.
**Syntax**
```sql theme={null}
queryID()
```
**Aliases**: `query_id`
**Arguments**
* None.
**Returned value**
Returns the ID of the current query. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
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());
```
```response title=Response theme={null}
┌─count(DISTINCT t)─┐
│ 3 │
└───────────────────┘
```
revision
Introduced in: v22.7.0
Returns the current ClickHouse server revision.
**Syntax**
```sql theme={null}
revision()
```
**Arguments**
* None.
**Returned value**
Returns the current ClickHouse server revision. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT revision()
```
```response title=Response theme={null}
┌─revision()─┐
│ 54485 │
└────────────┘
```
rowNumberInAllBlocks
Introduced in: v1.1.0
Returns a unique row number for each row processed.
**Syntax**
```sql theme={null}
rowNumberInAllBlocks()
```
**Arguments**
* None.
**Returned value**
Returns the ordinal number of the row in the data block starting from `0`. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT rowNumberInAllBlocks()
FROM
(
SELECT *
FROM system.numbers_mt
LIMIT 10
)
SETTINGS max_block_size = 2
```
```response title=Response theme={null}
┌─rowNumberInAllBlocks()─┐
│ 0 │
│ 1 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│ 4 │
│ 5 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│ 2 │
│ 3 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│ 6 │
│ 7 │
└────────────────────────┘
┌─rowNumberInAllBlocks()─┐
│ 8 │
│ 9 │
└────────────────────────┘
```
rowNumberInBlock
Introduced in: v1.1.0
For each [block](/resources/develop-contribute/introduction/architecture#block) processed by `rowNumberInBlock`, returns the number of the current row.
The returned number starts from 0 for each block.
**Syntax**
```sql theme={null}
rowNumberInBlock()
```
**Arguments**
* None.
**Returned value**
Returns the ordinal number of the row in the data block starting from `0`. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT rowNumberInBlock()
FROM
(
SELECT *
FROM system.numbers_mt
LIMIT 10
) SETTINGS max_block_size = 2
```
```response title=Response theme={null}
┌─rowNumberInBlock()─┐
│ 0 │
│ 1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│ 0 │
│ 1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│ 0 │
│ 1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│ 0 │
│ 1 │
└────────────────────┘
┌─rowNumberInBlock()─┐
│ 0 │
│ 1 │
└────────────────────┘
```
runningAccumulate
Introduced in: v1.1.0
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](/reference/functions/window-functions) instead.
You can use setting [`allow_deprecated_error_prone_window_functions`](/reference/settings/session-settings#allow_deprecated_error_prone_window_functions) to allow usage of this function.
**Syntax**
```sql theme={null}
runningAccumulate(agg_state[, grouping])
```
**Arguments**
* `agg_state` — State of the aggregate function. [`AggregateFunction`](/reference/data-types/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`](/reference/data-types)
**Returned value**
Returns the accumulated result for each row. [`Any`](/reference/data-types)
**Examples**
**Usage example with initializeAggregation**
```sql title=Query theme={null}
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);
```
```response title=Response theme={null}
┌─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.0
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.
**Requirements**
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.
**Deprecated**
It is advised to use [window functions](/reference/functions/window-functions) instead.
**Syntax**
```sql theme={null}
runningConcurrency(start, end)
```
**Arguments**
* `start` — A column with the start time of events. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
* `end` — A column with the end time of events. [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`DateTime64`](/reference/data-types/datetime64)
**Returned value**
Returns the number of concurrent events at each event start time. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT start, runningConcurrency(start, end) FROM example_table;
```
```response title=Response theme={null}
┌──────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.0
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](/reference/functions/window-functions) instead.
You can use setting [`allow_deprecated_error_prone_window_functions`](/reference/settings/session-settings#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`](/reference/statements/select/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**
```sql theme={null}
runningDifference(x)
```
**Arguments**
* `x` — Column for which to calculate the running difference. [`Any`](/reference/data-types)
**Returned value**
Returns the difference between consecutive values, with 0 for the first row.
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
EventID,
EventTime,
runningDifference(EventTime) AS delta
FROM
(
SELECT
EventID,
EventTime
FROM events
WHERE EventDate = '2025-11-24'
ORDER BY EventTime ASC
LIMIT 5
);
```
```response title=Response theme={null}
┌─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**
```sql title=Query theme={null}
SELECT
number,
runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1;
```
```response title=Response theme={null}
┌─number─┬─diff─┐
│ 0 │ 0 │
└────────┴──────┘
┌─number─┬─diff─┐
│ 65536 │ 0 │
└────────┴──────┘
```
runningDifferenceStartingWithFirstValue
Introduced in: v1.1.0
Calculates the difference between consecutive row values in a data block, but unlike [`runningDifference`](#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](/reference/functions/window-functions) instead.
You can use setting `allow_deprecated_error_prone_window_functions` to allow usage of this function.
**Syntax**
```sql theme={null}
runningDifferenceStartingWithFirstValue(x)
```
**Arguments**
* `x` — Column for which to calculate the running difference. [`Any`](/reference/data-types)
**Returned value**
Returns the difference between consecutive values, with the first row's value for the first row. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT
number,
runningDifferenceStartingWithFirstValue(number) AS diff
FROM numbers(5);
```
```response title=Response theme={null}
┌─number─┬─diff─┐
│ 0 │ 0 │
│ 1 │ 1 │
│ 2 │ 1 │
│ 3 │ 1 │
│ 4 │ 1 │
└────────┴──────┘
```
serverUUID
Introduced in: v20.1.0
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**
```sql theme={null}
serverUUID()
```
**Arguments**
* None.
**Returned value**
Returns the random UUID of the server. [`UUID`](/reference/data-types/uuid)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT serverUUID();
```
```response title=Response theme={null}
┌─serverUUID()─────────────────────────────┐
│ 7ccc9260-000d-4d5c-a843-5459abaabb5f │
└──────────────────────────────────────────┘
```
shardCount
Introduced in: v21.9.0
Returns the total number of shards for a distributed query.
If a query is not distributed then constant value `0` is returned.
**Syntax**
```sql theme={null}
shardCount()
```
**Arguments**
* None.
**Returned value**
Returns the total number of shards or `0`. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
-- 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;
```
```response title=Response theme={null}
┌─shardCount()─┐
│ 2 │
│ 2 │
└──────────────┘
```
shardNum
Introduced in: v21.9.0
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**
```sql theme={null}
shardNum()
```
**Arguments**
* None.
**Returned value**
Returns the shard index or a constant `0`. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─dummy─┬─shardNum()─┬─shardCount()─┐
│ 0 │ 1 │ 2 │
│ 0 │ 2 │ 2 │
└───────┴────────────┴──────────────┘
```
showCertificate
Introduced in: v22.6.0
Shows information about the current server's Secure Sockets Layer (SSL) certificate if it has been configured.
See [Configuring TLS](/concepts/features/security/tls/configuring-tls) for more information on how to configure ClickHouse to use OpenSSL certificates to validate connections.
**Syntax**
```sql theme={null}
showCertificate()
```
**Arguments**
* None.
**Returned value**
Returns map of key-value pairs relating to the configured SSL certificate. [`Map(String, String)`](/reference/data-types/map)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT showCertificate() FORMAT LineAsString;
```
```response title=Response theme={null}
{'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.0
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:
1. **Testing**: When testing or benchmarking ClickHouse, you may want to simulate delays or introduce pauses to observe how the system behaves under certain conditions.
2. **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.
3. **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.
For security reasons, the function can only be executed in the default user profile (with `allow_sleep` enabled).
**Syntax**
```sql theme={null}
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*`](/reference/data-types/int-uint) or [`const Float*`](/reference/data-types/float)
**Returned value**
Returns `0`. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
-- 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);
```
```response title=Response theme={null}
┌─sleep(2)─┐
│ 0 │
└──────────┘
1 row in set. Elapsed: 2.012 sec.
```
sleepEachRow
Introduced in: v1.1.0
Pauses the execution of a query for a specified number of seconds for each row in the result set.
The `sleepEachRow()` function is primarily used for testing and debugging purposes, similar to the [`sleep()`](#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:
1. **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.
2. **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.
3. **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.
**Syntax**
```sql theme={null}
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*`](/reference/data-types/int-uint) or [`const Float*`](/reference/data-types/float)
**Returned value**
Returns `0` for each row. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
-- The output will be delayed, with a 0.5-second pause between each row.
SELECT number, sleepEachRow(0.5) FROM system.numbers LIMIT 5;
```
```response title=Response theme={null}
┌─number─┬─sleepEachRow(0.5)─┐
│ 0 │ 0 │
│ 1 │ 0 │
│ 2 │ 0 │
│ 3 │ 0 │
│ 4 │ 0 │
└────────┴───────────────────┘
```
structureToCapnProtoSchema
Introduced in: v23.8.0
Function that converts ClickHouse table structure to CapnProto format schema
**Syntax**
```sql theme={null}
structureToCapnProtoSchema(table_structure, message)
```
**Arguments**
* None.
**Returned value**
**Examples**
**random**
```sql title=Query theme={null}
SELECT structureToCapnProtoSchema('s String, x UInt32', 'MessageName') format TSVRaw
```
```response title=Response theme={null}
struct MessageName
{
s @0 : Data;
x @1 : UInt32;
}
```
structureToProtobufSchema
Introduced in: v23.8.0
Converts a ClickHouse table structure to Protobuf format schema.
This function takes a ClickHouse table structure definition and converts it into a Protocol Buffers (Protobuf)
schema definition in proto3 syntax. This is useful for generating Protobuf schemas that match your ClickHouse
table structures for data interchange.
**Syntax**
```sql theme={null}
structureToProtobufSchema(structure, message_name)
```
**Arguments**
* `structure` — ClickHouse table structure definition as a string (e.g., 'column1 Type1, column2 Type2'). [`String`](/reference/data-types/string)
* `message_name` — Name for the Protobuf message type in the generated schema. [`String`](/reference/data-types/string)
**Returned value**
Returns a Protobuf schema definition in proto3 syntax that corresponds to the input ClickHouse structure. [`String`](/reference/data-types/string)
**Examples**
**Converting ClickHouse structure to Protobuf schema**
```sql title=Query theme={null}
SELECT structureToProtobufSchema('s String, x UInt32', 'MessageName') FORMAT TSVRaw;
```
```response title=Response theme={null}
syntax = "proto3";
message MessageName
{
bytes s = 1;
uint32 x = 2;
}
```
tcpPort
Introduced in: v20.12.0
Returns the [native interface](/concepts/features/interfaces/tcp) 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**
```sql theme={null}
tcpPort()
```
**Arguments**
* None.
**Returned value**
Returns the TCP port number. [`UInt16`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT tcpPort()
```
```response title=Response theme={null}
┌─tcpPort()─┐
│ 9000 │
└───────────┘
```
throwIf
Introduced in: v1.1.0
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**
```sql theme={null}
throwIf(x[, message[, error_code]])
```
**Arguments**
* `x` — The condition to check. [`Any`](/reference/data-types)
* `message` — Optional. Custom error message. [`const String`](/reference/data-types/string)
* `error_code` — Optional. Custom error code. [`const Int8/16/32`](/reference/data-types/int-uint)
**Returned value**
Returns `0` if the condition is false, throws an exception if the condition is true. [`UInt8`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT throwIf(number = 3, 'Too many') FROM numbers(10);
```
```response title=Response theme={null}
↙ 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.0
Returns the internal name of the data type of the given value.
Unlike function [`toTypeName`](#toTypeName), the returned data type potentially includes internal wrapper columns like `Const` and `LowCardinality`.
**Syntax**
```sql theme={null}
toColumnTypeName(value)
```
**Arguments**
* `value` — Value for which to return the internal data type. [`Any`](/reference/data-types)
**Returned value**
Returns the internal data type used to represent the value. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toColumnTypeName(CAST('2025-01-01 01:02:03' AS DateTime));
```
```response title=Response theme={null}
┌─toColumnTypeName(CAST('2025-01-01 01:02:03', 'DateTime'))─┐
│ Const(UInt32) │
└───────────────────────────────────────────────────────────┘
```
toTypeName
Introduced in: v1.1.0
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**
```sql theme={null}
toTypeName(x)
```
**Arguments**
* `x` — A value of arbitrary type. [`Any`](/reference/data-types)
**Returned value**
Returns the data type name of the input value. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT toTypeName(123)
```
```response title=Response theme={null}
┌─toTypeName(123)─┐
│ UInt8 │
└─────────────────┘
```
tokenizeQuery
Introduced in: v26.5.0
Tokenizes a ClickHouse SQL query string and returns an array of tokens.
Each token is a named tuple with the beginning position (in bytes), the end position, and the token type.
**Syntax**
```sql theme={null}
tokenizeQuery(query)
```
**Arguments**
* `query` — A ClickHouse SQL query string. String.
**Returned value**
An array of named tuples `(begin UInt64, end UInt64, type Enum8(...))` representing the tokens of the query. [`Array(Tuple(begin UInt64, end UInt64, type Enum8(...)))`](/reference/data-types/array)
**Examples**
**simple**
```sql title=Query theme={null}
SELECT tokenizeQuery('SELECT 1')
```
```response title=Response theme={null}
[(0,6,'BareWord'),(6,7,'Whitespace'),(7,8,'Number')]
```
transactionID
Introduced in: v22.6.0
Returns the ID of a transaction.
This function is part of an experimental feature set.
Enable experimental transaction support by adding this setting to your [configuration](/concepts/features/configuration/server-config/configuration-files):
```xml theme={null}
1
```
For more information see the page [Transactional (ACID) support](/concepts/features/operations/insert/transactions#transactions-commit-and-rollback).
**Syntax**
```sql theme={null}
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)`](/reference/data-types/tuple)
**Examples**
**Usage example**
```sql title=Query theme={null}
BEGIN TRANSACTION;
SELECT transactionID();
ROLLBACK;
```
```response title=Response theme={null}
┌─transactionID()────────────────────────────────┐
│ (32,34,'0ee8b069-f2bb-4748-9eae-069c85b5252b') │
└────────────────────────────────────────────────┘
```
transactionLatestSnapshot
Introduced in: v22.6.0
Returns the newest snapshot (Commit Sequence Number) of a [transaction](/concepts/features/operations/insert/transactions#transactions-commit-and-rollback) that is available for reading.
This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:
```xml theme={null}
1
```
For more information see the page [Transactional (ACID) support](/concepts/features/operations/insert/transactions#transactions-commit-and-rollback).
**Syntax**
```sql theme={null}
transactionLatestSnapshot()
```
**Arguments**
* None.
**Returned value**
Returns the latest snapshot (CSN) of a transaction. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
BEGIN TRANSACTION;
SELECT transactionLatestSnapshot();
ROLLBACK;
```
```response title=Response theme={null}
┌─transactionLatestSnapshot()─┐
│ 32 │
└─────────────────────────────┘
```
transactionOldestSnapshot
Introduced in: v22.6.0
Returns the oldest snapshot (Commit Sequence Number) that is visible for some running [transaction](/concepts/features/operations/insert/transactions#transactions-commit-and-rollback).
This function is part of an experimental feature set. Enable experimental transaction support by adding this setting to your configuration:
```xml theme={null}
1
```
For more information see the page [Transactional (ACID) support](/concepts/features/operations/insert/transactions#transactions-commit-and-rollback).
**Syntax**
```sql theme={null}
transactionOldestSnapshot()
```
**Arguments**
* None.
**Returned value**
Returns the oldest snapshot (CSN) of a transaction. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
BEGIN TRANSACTION;
SELECT transactionOldestSnapshot();
ROLLBACK;
```
```response title=Response theme={null}
┌─transactionOldestSnapshot()─┐
│ 32 │
└─────────────────────────────┘
```
transform
Introduced in: v1.1.0
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**
```sql theme={null}
transform(x, array_from, array_to[, default])
```
**Arguments**
* `x` — Value to transform. [`(U)Int*`](/reference/data-types/int-uint) or [`Decimal`](/reference/data-types/decimal) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime)
* `array_from` — Constant array of values to search for matches. [`Array((U)Int*)`](/reference/data-types/array) or [`Array(Decimal)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array) or [`Array(String)`](/reference/data-types/array) or [`Array(Date)`](/reference/data-types/array) or [`Array(DateTime)`](/reference/data-types/array)
* `array_to` — Constant array of values to return for corresponding matches in `array_from`. [`Array((U)Int*)`](/reference/data-types/array) or [`Array(Decimal)`](/reference/data-types/array) or [`Array(Float*)`](/reference/data-types/array) or [`Array(String)`](/reference/data-types/array) or [`Array(Date)`](/reference/data-types/array) or [`Array(DateTime)`](/reference/data-types/array)
* `default` — Optional. Value to return if `x` is not found in `array_from`. If omitted, returns x unchanged. [`(U)Int*`](/reference/data-types/int-uint) or [`Decimal`](/reference/data-types/decimal) or [`Float*`](/reference/data-types/float) or [`String`](/reference/data-types/string) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/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`](/reference/data-types)
**Examples**
**transform(T, Array(T), Array(U), U) -> U**
```sql title=Query theme={null}
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
```
```response title=Response theme={null}
┌─title─────┬──────c─┐
│ Yandex │ 498635 │
│ Google │ 229872 │
│ Other │ 104472 │
└───────────┴────────┘
```
**transform(T, Array(T), Array(T)) -> T**
```sql title=Query theme={null}
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
```
```response title=Response theme={null}
┌─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.0
Two uniqThetaSketch objects to do intersect calculation(set operation ∩), the result is a new uniqThetaSketch.
**Syntax**
```sql theme={null}
uniqThetaIntersect(uniqThetaSketch,uniqThetaSketch)
```
**Arguments**
* `uniqThetaSketch` — uniqThetaSketch object. [`Tuple`](/reference/data-types/tuple) or [`Array`](/reference/data-types/array) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
A new uniqThetaSketch containing the intersect result. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
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);
```
```response title=Response theme={null}
┌─a_intersect_b─┬─a_cardinality─┬─b_cardinality─┐
│ 1 │ 2 │ 3 │
└───────────────┴───────────────┴───────────────┘
```
uniqThetaNot
Introduced in: v22.9.0
Two uniqThetaSketch objects to do a\_not\_b calculation(set operation ×), the result is a new uniqThetaSketch.
**Syntax**
```sql theme={null}
uniqThetaNot(uniqThetaSketch,uniqThetaSketch)
```
**Arguments**
* `uniqThetaSketch` — uniqThetaSketch object. [`Tuple`](/reference/data-types/tuple) or [`Array`](/reference/data-types/array) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a new uniqThetaSketch containing the a\_not\_b result. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
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);
```
```response title=Response theme={null}
┌─a_not_b─┬─a_cardinality─┬─b_cardinality─┐
│ 2 │ 3 │ 2 │
└─────────┴───────────────┴───────────────┘
```
uniqThetaUnion
Introduced in: v22.9.0
Two uniqThetaSketch objects to do union calculation(set operation ∪), the result is a new uniqThetaSketch.
**Syntax**
```sql theme={null}
uniqThetaUnion(uniqThetaSketch,uniqThetaSketch)
```
**Arguments**
* `uniqThetaSketch` — uniqThetaSketch object. [`Tuple`](/reference/data-types/tuple) or [`Array`](/reference/data-types/array) or [`Date`](/reference/data-types/date) or [`DateTime`](/reference/data-types/datetime) or [`String`](/reference/data-types/string) or [`(U)Int*`](/reference/data-types/int-uint) or [`Float*`](/reference/data-types/float) or [`Decimal`](/reference/data-types/decimal)
**Returned value**
Returns a new uniqThetaSketch containing the union result. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
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);
```
```response title=Response theme={null}
┌─a_union_b─┬─a_cardinality─┬─b_cardinality─┐
│ 4 │ 2 │ 3 │
└───────────┴───────────────┴───────────────┘
```
uptime
Introduced in: v1.1.0
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**
```sql theme={null}
uptime()
```
**Arguments**
* None.
**Returned value**
Returns the server uptime in seconds. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT uptime() AS Uptime
```
```response title=Response theme={null}
┌─Uptime─┐
│ 55867 │
└────────┘
```
variantElement
Introduced in: v25.2.0
Extracts a column with specified type from a `Variant` column.
**Syntax**
```sql theme={null}
variantElement(variant, type_name[, default_value])
```
**Arguments**
* `variant` — Variant column. [`Variant`](/reference/data-types/variant)
* `type_name` — The name of the variant type to extract. [`String`](/reference/data-types/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`](/reference/data-types)
**Returned value**
Returns a column with the specified variant type extracted from the Variant column. [`Any`](/reference/data-types)
**Examples**
**Usage example**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─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.0
Returns the variant type name for each row of `Variant` column. If row contains NULL, it returns 'None' for it.
**Syntax**
```sql theme={null}
variantType(variant)
```
**Arguments**
* `variant` — Variant column. [`Variant`](/reference/data-types/variant)
**Returned value**
Returns an Enum column with variant type name for each row. [`Enum`](/reference/data-types/enum)
**Examples**
**Usage example**
```sql title=Query theme={null}
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;
```
```response title=Response theme={null}
┌─variantType(v)─┐
│ None │
│ UInt64 │
│ String │
│ Array(UInt64) │
└────────────────┘
```
version
Introduced in: v1.1.0
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**
```sql theme={null}
version()
```
**Arguments**
* None.
**Returned value**
Returns the current version of ClickHouse. [`String`](/reference/data-types/string)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT version()
```
```response title=Response theme={null}
┌─version()─┐
│ 24.2.1.1 │
└───────────┘
```
visibleWidth
Introduced in: v1.1.0
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**
```sql theme={null}
visibleWidth(x)
```
**Arguments**
* `x` — A value of any data type. [`Any`](/reference/data-types)
**Returned value**
Returns the approximate width of the value when displayed in text format. [`UInt64`](/reference/data-types/int-uint)
**Examples**
**Calculate visible width of NULL**
```sql title=Query theme={null}
SELECT visibleWidth(NULL)
```
```response title=Response theme={null}
┌─visibleWidth(NULL)─┐
│ 4 │
└────────────────────┘
```
zookeeperSessionUptime
Introduced in: v21.11.0
Returns the uptime of the current ZooKeeper session in seconds.
**Syntax**
```sql theme={null}
zookeeperSessionUptime()
```
**Arguments**
* None.
**Returned value**
Returns the uptime of the current ZooKeeper session in seconds. [`UInt32`](/reference/data-types/int-uint)
**Examples**
**Usage example**
```sql title=Query theme={null}
SELECT zookeeperSessionUptime();
```
```response title=Response theme={null}
┌─zookeeperSessionUptime()─┐
│ 286 │
└──────────────────────────┘
```