Other functions¶

basename¶

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

basename(expr)

Arguments

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

Returned value

A string that contains:

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

Example

Query:

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

Result:

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

Query:

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

Result:

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

Query:

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

Result:

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

visibleWidth¶

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

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

Syntax

visibleWidth(x)

Example

Query:

SELECT visibleWidth(NULL)

Result:

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

toTypeName¶

Returns the type name of the passed argument.

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

Syntax

toTypeName(value)

Arguments

value: A value of arbitrary type.

Returned value

The data type name of the input value. String.

Example

Query:

SELECT toTypeName(123);

Result:

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

blockSize¶

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

Syntax

blockSize()

Example

Query:

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

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

SELECT blockSize()
FROM test;

Result:

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

byteSize¶

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

Syntax

byteSize(argument [, ...])

Arguments

argument: Value.

Returned value

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

Examples

For String arguments, the function returns the string length + 9 (terminating zero + length).

Query:

SELECT byteSize('string');

Result:

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

Query:

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

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

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

Result:

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

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

Query:

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

Result:

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

materialize¶

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

Syntax

materialize(x)

### Parameters

x: A constant. Constant.

Returned value

A column containing a single value x.

Example

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

Query:

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

Result:

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

ignore¶

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

Syntax

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

Arguments

Accepts arbitrarily many arguments of arbitrary type, including NULL.

Returned value

Returns 0.

Example

Query:

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

Result:

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

isConstant¶

Returns whether the argument is a constant expression.

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

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

Syntax

isConstant(x)

Arguments

x: Expression to check.

Returned values

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

Examples

Query:

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

Result:

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

Query:

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

Result:

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

Query:

SELECT isConstant(number) FROM numbers(1)

Result:

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

bar¶

Builds a bar chart.

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

Arguments

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

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

Example:

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

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

transform¶

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

transform(x, array_from, array_to, default)

x – What to transform.

array_from – Constant array of values to convert.

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

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

array_from and array_to must have equally many elements.

Signature:

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

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

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

Example:

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

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

transform(x, array_from, array_to)

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

Example:

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

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

formatReadableDecimalSize¶

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

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

Syntax

formatReadableDecimalSize(x)

Example

Query:

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

Result:

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

formatReadableSize¶

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

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

Syntax

formatReadableSize(x)

Alias: FORMAT_BYTES.

Example

Query:

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

Result:

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

formatReadableQuantity¶

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

Syntax

formatReadableQuantity(x)

Example

Query:

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

Result:

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

formatReadableTimeDelta¶

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

Syntax

formatReadableTimeDelta(column[, maximum_unit, minimum_unit])

Arguments

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

Example

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

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

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

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

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

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

parseReadableSize¶

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

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

formatReadableSize(x)

Arguments

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

Returned value

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

Example

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

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

parseReadableSizeOrNull¶

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSizeOrNull(x)

Arguments

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

Returned value

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

Example

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

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

parseReadableSizeOrZero¶

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

The inverse operations of this function are formatReadableSize and formatReadableDecimalSize.

Syntax

parseReadableSizeOrZero(x)

Arguments

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

Returned value

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

Example

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

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

parseTimeDelta¶

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

Syntax

parseTimeDelta(timestr)

Arguments

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

Returned value

A floating-point number with the number of seconds.

Example

SELECT parseTimeDelta('11s+22min')

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

SELECT parseTimeDelta('1yr2mo')

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

least¶

Returns the smaller value of a and b.

Syntax

least(a, b)

greatest¶

Returns the larger value of a and b.

Syntax

greatest(a, b)

blockNumber¶

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

Syntax

blockNumber()

Returned value

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

Example

Query:

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

Result:

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

rowNumberInBlock¶

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

Syntax

rowNumberInBlock()

Returned value

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

Example

Query:

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

Result:

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

rowNumberInAllBlocks¶

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

Syntax

rowNumberInAllBlocks()

Returned value

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

Example

Query:

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

Result:

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

neighbor¶

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

Syntax

neighbor(column, offset[, default_value])

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

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

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

Arguments

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

Returned values

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

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

Example

Query:

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

Result:

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

Query:

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

Result:

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

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

Query:

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

Result:

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

runningDifference¶

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

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

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

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

Syntax

runningDifference(x)

Example

Query:

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

Result:

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

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

Query:

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

Result:

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

Query:

set max_block_size=100000 -- default value is 65536!

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

Result:

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

runningDifferenceStartingWithFirstValue¶

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

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

runningConcurrency¶

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

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

Syntax

runningConcurrency(start, end)

Arguments

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

Returned values

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

Example

Consider the table:

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

Query:

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

Result:

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

MACNumToString¶

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

Syntax

MACNumToString(num)

MACStringToNum¶

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

Syntax

MACStringToNum(s)

MACStringToOUI¶

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

Syntax

MACStringToOUI(s)

getSizeOfEnumType¶

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

Syntax

getSizeOfEnumType(value)

Arguments

value: Value of type Enum.

Returned values

The number of fields with Enum input values.

Example

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

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

blockSerializedSize¶

Returns the size on disk without considering compression.

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

Arguments

value: Any value.

Returned values

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

Example

Query:

SELECT blockSerializedSize(maxState(1)) as x

Result:

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

toColumnTypeName¶

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

Syntax

toColumnTypeName(value)

Arguments

value: Any type of value.

Returned values

The internal data type name used to represent value.

Example

Difference between toTypeName and toColumnTypeName:

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

Result:

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

Query:

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

Result:

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

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

dumpColumnStructure¶

Outputs a detailed description of data structures in RAM

dumpColumnStructure(value)

Arguments

value: Any type of value.

Returned values

A description of the column structure used for representing value.

Example

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

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

defaultValueOfArgumentType¶

Returns the default value for the given data type.

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

Syntax

defaultValueOfArgumentType(expression)

Arguments

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

Returned values

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

Example

Query:

SELECT defaultValueOfArgumentType( CAST(1 AS Int8) )

Result:

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

Query:

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

Result:

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

defaultValueOfTypeName¶

Returns the default value for the given type name.

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

defaultValueOfTypeName(type)

Arguments

type: A string representing a type name.

Returned values

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

Example

Query:

SELECT defaultValueOfTypeName('Int8')

Result:

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

Query:

SELECT defaultValueOfTypeName('Nullable(Int8)')

Result:

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

replicate¶

Creates an array with a single value.

This function is used for the internal implementation of arrayJoin.

Syntax

replicate(x, arr)

Arguments

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

Returned value

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

Example

Query:

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

Result:

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

initializeAggregation¶

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

Syntax

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

Arguments

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

Returned values

Result of aggregation for every row passed to the function.

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

Example

Query:

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

Result:

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

Query:

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

Result:

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

Example with AggregatingMergeTree table engine and AggregateFunction column:

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

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

finalizeAggregation¶

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

Syntax

finalizeAggregation(state)

Arguments

state: State of aggregation. AggregateFunction.

Returned values

Value/values that was aggregated.

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

Examples

Query:

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

Result:

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

Query:

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

Result:

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

Note that NULL values are ignored.

Query:

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

Result:

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

Combined example:

Query:

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

Result:

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

runningAccumulate¶

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

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

Syntax

runningAccumulate(agg_state[, grouping]);

Arguments

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

Returned value

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

Type depends on the aggregate function used.

Examples

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

Query:

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

Result:

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

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

The whole query does the following:

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

The following example shows the groupping parameter usage:

Query:

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

Result:

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

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

joinGet¶

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

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

Syntax

joinGet(join_storage_table_name, `value_column`, join_keys)

Arguments

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

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

Returned value

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

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

Example

Input table:

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

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

Query:

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

Result:

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

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

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

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

Query:

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

Result:

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

joinGetOrNull¶

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

Syntax

joinGetOrNull(join_storage_table_name, `value_column`, join_keys)

Arguments

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

Returned value

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

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

Example

Input table:

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

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

Query:

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

Result:

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

throwIf¶

Throw an exception if argument x is true.

Syntax

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

Arguments

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

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

Example

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

Result:

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

identity¶

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

Syntax

identity(x)

Example

Query:

SELECT identity(42);

Result:

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

getSettingOrDefault¶

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

Syntax

getSettingOrDefault('custom_setting', default_value);

Parameters

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

Returned value

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

Example

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

Result:

my_value
100
NULL

isDecimalOverflow¶

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

Syntax

isDecimalOverflow(d, [p])

Arguments

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

Returned values

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

Example

Query:

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

Result:

1	1	1	1

countDigits¶

Returns number of decimal digits need to represent a value.

Syntax

countDigits(x)

Arguments

x: Int or Decimal value.

Returned value

Number of digits. UInt8.

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

Example

Query:

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

Result:

10	10	19	19	39	39

errorCodeToName¶

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

Syntax

errorCodeToName(1)

Result:

UNSUPPORTED_METHOD

enabledProfiles¶

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

Syntax

enabledProfiles()

Returned value

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

queryID¶

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

Syntax

queryID()

Returned value

The ID of the current query. String

Example

Query:

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

Result:

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

partitionID¶

Computes the partition ID.

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

Syntax

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

Arguments

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

Returned value

Partition ID that the row would belong to. String.

Example

Query:

DROP TABLE IF EXISTS tab;

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

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

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

Result:

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

structureToCapnProtoSchema¶

Converts a Tinybird table structure to CapnProto schema.

Syntax

structureToCapnProtoSchema(structure)

Arguments

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

Returned value

CapnProto schema. String.

Examples

Query:

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

Result:

@0xf96402dd754d0eb7;

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

Query:

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

Result:

@0xd1c8320fecad2b7f;

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

Query:

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

Result:

@0x96ab2d4ab133c6e1;

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

structureToProtobufSchema¶

Converts a Tinybird table structure to Protobuf schema.

Syntax

structureToProtobufSchema(structure)

Arguments

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

Returned value

Protobuf schema. String.

Examples

Query:

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

Result:

syntax = "proto3";

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

Query:

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

Result:

syntax = "proto3";

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

Query:

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

Result:

syntax = "proto3";

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

formatQuery¶

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

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

Syntax

formatQuery(query)
formatQueryOrNull(query)

Arguments

query - The SQL query to be formatted. String

Returned value

The formatted query. String.

Example

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

Result:

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

formatQuerySingleLine¶

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

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

Syntax

formatQuerySingleLine(query)
formatQuerySingleLineOrNull(query)

Arguments

query - The SQL query to be formatted. String

Returned value

The formatted query. String.

Example

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

Result:

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

variantElement¶

Extracts a column with specified type from a Variant column.

Syntax

variantElement(variant, type_name, [, default_value])

Arguments

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

Returned value

Subcolumn of a Variant column with specified type.

Example

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

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

variantType¶

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

Syntax

variantType(variant)

Arguments

variant: Variant column. Variant.

Returned value

Enum8 column with variant type name for each row.

Example

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

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

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

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

minSampleSizeConversion¶

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

Syntax

minSampleSizeConversion(baseline, mde, power, alpha)

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

Arguments

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

Returned value

A named Tuple with 3 elements:

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

Example

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

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

Result:

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

minSampleSizeContinuous¶

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

Syntax

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

Alias: minSampleSizeContinous

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

Arguments

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

Returned value

A named Tuple with 3 elements:

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

Example

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

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

Result:

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

connectionId¶

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

Syntax

connectionId()

Alias: connection_id.

### Parameters

None.

Returned value

The current connection ID. UInt64.

Implementation details

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

Example

Query:

SELECT connectionId();

lowCardinalityIndices¶

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

Syntax

lowCardinalityIndices(col)

Arguments

col: a low cardinality column. LowCardinality.

Returned value

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

Example

Query:

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

-- create two parts:

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

SELECT s, lowCardinalityIndices(s) FROM test;

Result:

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

lowCardinalityKeys¶

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

Syntax

lowCardinalityIndices(col)

Arguments

col: a low cardinality column. LowCardinality.

Returned value

The dictionary keys. UInt64.

Example

Query:

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

-- create two parts:

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

SELECT s, lowCardinalityKeys(s) FROM test;

Result:

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

transactionID¶

Returns the ID of a transaction.

Syntax

transactionID()

Returned value

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

Example

Query:

BEGIN TRANSACTION;
SELECT transactionID();
ROLLBACK;

Result:

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

transactionLatestSnapshot¶

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

Syntax

transactionLatestSnapshot()

Returned value

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

Example

Query:

BEGIN TRANSACTION;
SELECT transactionLatestSnapshot();
ROLLBACK;

Result:

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

transactionOldestSnapshot¶

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

Syntax

transactionOldestSnapshot()

Returned value

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

Example

Query:

BEGIN TRANSACTION;
SELECT transactionLatestSnapshot();
ROLLBACK;

Result:

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

getSubcolumn¶

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

Syntax

getSubcolumn(col_name, subcol_name)

Arguments

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

Returned value

Returns the extracted sub-column.

Example

Query:

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

Result:

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

getTypeSerializationStreams¶

Enumerates stream paths of a data type.

This function is intended for use by developers.

Syntax

getTypeSerializationStreams(col)

Arguments

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

Returned value

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

Examples

Query:

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

Result:

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

Query:

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

Result:

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