Other Functions
hostName()β
Returns a string with the name of the host that this function was performed on. For distributed processing, this is the name of the remote server host, if the function is performed on a remote server. If it is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise it produces a constant value.
getMacroβ
Gets a named value from the macros section of the server configuration.
Syntax
getMacro(name);
Arguments
name
β Name to retrieve from themacros
section. String.
Returned value
- Value of the specified macro.
Type: String.
Example
The example macros
section in the server configuration file:
<macros>
<test>Value</test>
</macros>
Query:
SELECT getMacro('test');
Result:
ββgetMacro('test')ββ
β Value β
ββββββββββββββββββββ
An alternative way to get the same value:
SELECT * FROM system.macros
WHERE macro = 'test';
ββmacroββ¬βsubstitutionββ
β test β Value β
βββββββββ΄βββββββββββββββ
FQDNβ
Returns the fully qualified domain name.
Syntax
fqdn();
This function is case-insensitive.
Returned value
- String with the fully qualified domain name.
Type: String
.
Example
Query:
SELECT FQDN();
Result:
ββFQDN()βββββββββββββββββββββββββββ
β clickhouse.ru-central1.internal β
βββββββββββββββββββββββββββββββββββ
basenameβ
Extracts the trailing part of a string after the last slash or backslash. This function if often used to extract the filename from a path.
basename( expr )
Arguments
expr
β Expression resulting in a String type value. All the backslashes must be escaped in the resulting value.
Returned Value
A string that contains:
The trailing part of a string after the last slash or backslash.
If the input string contains a path ending with slash or backslash, for example, `/` or `c:\`, the function returns an empty string.
The original string if there are no slashes or backslashes.
Example
SELECT 'some/long/path/to/file' AS a, basename(a)
ββaβββββββββββββββββββββββ¬βbasename('some\\long\\path\\to\\file')ββ
β some\long\path\to\file β file β
ββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββ
SELECT 'some\\long\\path\\to\\file' AS a, basename(a)
ββaβββββββββββββββββββββββ¬βbasename('some\\long\\path\\to\\file')ββ
β some\long\path\to\file β file β
ββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββββββββ
SELECT 'some-file-name' AS a, basename(a)
ββaβββββββββββββββ¬βbasename('some-file-name')ββ
β some-file-name β some-file-name β
ββββββββββββββββββ΄βββββββββββββββββββββββββββββ
visibleWidth(x)β
Calculates the approximate width when outputting values to the console in text format (tab-separated). This function is used by the system for implementing Pretty formats.
NULL
is represented as a string corresponding to NULL
in Pretty
formats.
SELECT visibleWidth(NULL)
ββvisibleWidth(NULL)ββ
β 4 β
ββββββββββββββββββββββ
toTypeName(x)β
Returns a string containing the type name of the passed argument.
If NULL
is passed to the function as input, then it returns the Nullable(Nothing)
type, which corresponds to an internal NULL
representation in ClickHouse.
blockSize()β
Gets the size of the block. In ClickHouse, queries are always run on blocks (sets of column parts). This function allows getting the size of the block that you called it for.
byteSizeβ
Returns 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.
Type: UInt64.
Examples
For String arguments the funtion 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 takes multiple arguments, it returns their combined byte size.
Query:
SELECT byteSize(NULL, 1, 0.3, '');
Result:
ββbyteSize(NULL, 1, 0.3, '')ββ
β 19 β
ββββββββββββββββββββββββββββββ
materialize(x)β
Turns a constant into a full column containing just one value. In ClickHouse, full columns and constants are represented differently in memory. Functions work differently for constant arguments and normal arguments (different code is executed), although the result is almost always the same. This function is for debugging this behavior.
ignore(β¦)β
Accepts any arguments, including NULL
. Always returns 0.
However, the argument is still evaluated. This can be used for benchmarks.
sleep(seconds)β
Sleeps βsecondsβ seconds on each data block. You can specify an integer or a floating-point number.
sleepEachRow(seconds)β
Sleeps βsecondsβ seconds on each row. You can specify an integer or a floating-point number.
currentDatabase()β
Returns the name of the current database. You can use this function in table engine parameters in a CREATE TABLE query where you need to specify the database.
currentUser()β
Returns the login of current user. Login of user, that initiated query, will be returned in case distibuted query.
SELECT currentUser();
Alias: user()
, USER()
.
Returned values
- Login of current user.
- Login of user that initiated query in case of disributed query.
Type: String
.
Example
Query:
SELECT currentUser();
Result:
ββcurrentUser()ββ
β default β
βββββββββββββββββ
isConstantβ
Checks whether the argument is a constant expression.
A constant expression means an expression whose resulting value is known at the query analysis (i.e.Β before execution). For example, expressions over literals are constant expressions.
The function is intended for development, debugging and demonstration.
Syntax
isConstant(x)
Arguments
x
β Expression to check.
Returned values
1
βx
is constant.0
βx
is non-constant.
Type: 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 β
ββββββββββββββββββββββ
isFinite(x)β
Accepts Float32 and Float64 and returns UInt8 equal to 1 if the argument is not infinite and not a NaN, otherwise 0.
isInfinite(x)β
Accepts Float32 and Float64 and returns UInt8 equal to 1 if the argument is infinite, otherwise 0. Note that 0 is returned for a NaN.
ifNotFiniteβ
Checks whether floating point value is finite.
Syntax
ifNotFinite(x,y)
Arguments
Returned value
x
ifx
is finite.y
ifx
is not finite.
Example
Query:
SELECT 1/0 as infimum, ifNotFinite(infimum,42)
Result:
ββinfimumββ¬βifNotFinite(divide(1, 0), 42)ββ
β inf β 42 β
βββββββββββ΄ββββββββββββββββββββββββββββββββ
You can get similar result by using ternary operator: isFinite(x) ? x : y
.
isNaN(x)β
Accepts Float32 and Float64 and returns UInt8 equal to 1 if the argument is a NaN, otherwise 0.
hasColumnInTable([βhostnameβ[, βusernameβ[, βpasswordβ]],] βdatabaseβ, βtableβ, βcolumnβ)β
Accepts constant strings: database name, table name, and column name. Returns a UInt8 constant expression equal to 1 if there is a column, otherwise 0. If the hostname parameter is set, the test will run on a remote server. The function throws an exception if the table does not exist. 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.
barβ
Allows building a unicode-art diagram.
bar(x, min, max, width)
draws a band with a 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 inInt64
.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 for converting.
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
β Arrays of the same size.
Types:
transform(T, Array(T), Array(U), U) -> U
T
and U
can be numeric, string, or Date or DateTime types.
Where the same letter is indicated (T or U), for numeric types these might not be matching types, but types that have a common type.
For example, the first argument can have the Int64 type, while the second has the Array(UInt16) type.
If the βxβ value is equal to one of the elements in the βarray_fromβ array, it returns the existing element (that is numbered the same) from the βarray_toβ array. Otherwise, it returns βdefaultβ. If there are multiple matching elements in βarray_fromβ, it returns one of the matches.
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)β
Differs from the first variation in that the βdefaultβ argument is omitted. If the βxβ value is equal to one of the elements in the βarray_fromβ array, it returns the matching element (that is numbered the same) from the βarray_toβ array. Otherwise, it returns βxβ.
Types:
transform(T, Array(T), Array(T)) -> T
Example:
SELECT
transform(domain(Referer), ['yandex.ru', 'google.ru', 'vk.com'], ['www.yandex', 'example.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 β
ββββββββββββββββββ΄ββββββββββ
formatReadableSize(x)β
Accepts the size (number of bytes). Returns a rounded size with a suffix (KiB, MiB, etc.) as a string.
Example:
SELECT
arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
formatReadableSize(filesize_bytes) AS filesize
ββfilesize_bytesββ¬βfilesizeββββ
β 1 β 1.00 B β
β 1024 β 1.00 KiB β
β 1048576 β 1.00 MiB β
β 192851925 β 183.92 MiB β
ββββββββββββββββββ΄βββββββββββββ
formatReadableQuantity(x)β
Accepts the number. Returns a rounded number with a suffix (thousand, million, billion, etc.) as a string.
It is useful for reading big numbers by human.
Example:
SELECT
arrayJoin([1024, 1234 * 1000, (4567 * 1000) * 1000, 98765432101234]) AS number,
formatReadableQuantity(number) AS number_for_humans
ββββββββββnumberββ¬βnumber_for_humansββ
β 1024 β 1.02 thousand β
β 1234000 β 1.23 million β
β 4567000000 β 4.57 billion β
β 98765432101234 β 98.77 trillion β
ββββββββββββββββββ΄ββββββββββββββββββββ
formatReadableTimeDeltaβ
Accepts the time delta in seconds. Returns a time delta with (year, month, day, hour, minute, second) as a string.
Syntax
formatReadableTimeDelta(column[, maximum_unit])
Arguments
column
β A column with numeric time delta.maximum_unit
β Optional. Maximum unit to show. Acceptable values seconds, minutes, hours, days, months, years.
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 β
ββββββββββββββ΄ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
least(a, b)β
Returns the smallest value from a and b.
greatest(a, b)β
Returns the largest value of a and b.
uptime()β
Returns the serverβs uptime in seconds. If it is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise it produces a constant value.
version()β
Returns the version of the server as a string. If it is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise it produces a constant value.
buildId()β
Returns the build ID generated by a compiler for the running ClickHouse server binary. If it is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard. Otherwise it produces a constant value.
blockNumberβ
Returns the sequence number of the data block where the row is located.
rowNumberInBlockβ
Returns the ordinal number of the row in the data block. Different data blocks are always recalculated.
rowNumberInAllBlocks()β
Returns the ordinal number of the row in the data block. This function only considers the affected data blocks.
neighborβ
The window function that provides access to a row at a specified offset which comes 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.
warning
It can reach the neighbor rows only inside the currently processed data block.
The rows order used during the calculation of neighbor
can differ from the order of rows returned to the user.
To prevent that you can make 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 forwards or backwards from the current row ofcolumn
. Int64.default_value
β Optional. The value to be returned if offset goes beyond the scope of the block. Type of data blocks affected.
Returned values
- Value for
column
inoffset
distance from current row ifoffset
value is not outside block bounds. - Default value for
column
ifoffset
value is outside block bounds. Ifdefault_value
is given, then it will be used.
Type: type of data blocks affected or 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(x)β
Calculates the difference between successive row values ββin the data block. Returns 0 for the first row and the difference from the previous row for each subsequent row.
warning
It can reach the previous row only inside the currently processed data block.
The result of the function depends on the affected data blocks and the order of data in the block.
The rows order used during the calculation of runningDifference
can differ from the order of rows returned to the user.
To prevent that you can make a subquery with ORDER BY and call the function from outside the subquery.
Example:
SELECT
EventID,
EventTime,
runningDifference(EventTime) AS delta
FROM
(
SELECT
EventID,
EventTime
FROM events
WHERE EventDate = '2016-11-24'
ORDER BY EventTime ASC
LIMIT 5
)
ββ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 - block size affects the result. With each new block, the runningDifference
state is reset.
SELECT
number,
runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1
ββnumberββ¬βdiffββ
β 0 β 0 β
ββββββββββ΄βββββββ
ββnumberββ¬βdiffββ
β 65536 β 0 β
ββββββββββ΄βββββββ
set max_block_size=100000 -- default value is 65536!
SELECT
number,
runningDifference(number + 1) AS diff
FROM numbers(100000)
WHERE diff != 1
ββnumberββ¬βdiffββ
β 0 β 0 β
ββββββββββ΄βββββββ
runningDifferenceStartingWithFirstValueβ
Same as for runningDifference, the difference is the value of the first row, returned the value of the first row, and each subsequent row returns the difference from the previous 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.
warning
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.
Type: 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(num)β
Accepts a UInt64 number. Interprets it as a MAC address in big endian. Returns a string containing the corresponding MAC address in the format AA:BB:CC:DD:EE:FF (colon-separated numbers in hexadecimal form).
MACStringToNum(s)β
The inverse function of MACNumToString. If the MAC address has an invalid format, it returns 0.
MACStringToOUI(s)β
Accepts a MAC address in the 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.
getSizeOfEnumTypeβ
Returns the number of fields in Enum.
getSizeOfEnumType(value)
Arguments:
value
β Value of typeEnum
.
Returned values
- The number of fields with
Enum
input values. - An exception is thrown if the type is not
Enum
.
Example
SELECT getSizeOfEnumType( CAST('a' AS Enum8('a' = 1, 'b' = 2) ) ) AS x
ββxββ
β 2 β
βββββ
blockSerializedSizeβ
Returns size on disk (without taking into account 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 name of the class that represents the data type of the column in RAM.
toColumnTypeName(value)
Arguments:
value
β Any type of value.
Returned values
- A string with the name of the class that is used for representing the
value
data type in RAM.
Example of the difference betweentoTypeName ' and ' toColumnTypeName
SELECT toTypeName(CAST('2018-01-01 01:02:03' AS DateTime))
ββtoTypeName(CAST('2018-01-01 01:02:03', 'DateTime'))ββ
β DateTime β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββ
SELECT toColumnTypeName(CAST('2018-01-01 01:02:03' AS DateTime))
ββtoColumnTypeName(CAST('2018-01-01 01:02:03', 'DateTime'))ββ
β Const(UInt32) β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
The example shows that the DateTime
data type is stored in memory as Const(UInt32)
.
dumpColumnStructureβ
Outputs a detailed description of data structures in RAM
dumpColumnStructure(value)
Arguments:
value
β Any type of value.
Returned values
- A string describing the structure that is used for representing the
value
data type in RAM.
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β
Outputs the default value for the data type.
Does not include default values for custom columns set by the user.
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
SELECT defaultValueOfArgumentType( CAST(1 AS Int8) )
ββdefaultValueOfArgumentType(CAST(1, 'Int8'))ββ
β 0 β
βββββββββββββββββββββββββββββββββββββββββββββββ
SELECT defaultValueOfArgumentType( CAST(1 AS Nullable(Int8) ) )
ββdefaultValueOfArgumentType(CAST(1, 'Nullable(Int8)'))ββ
β α΄Ία΅α΄Έα΄Έ β
βββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
defaultValueOfTypeNameβ
Outputs the default value for 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
SELECT defaultValueOfTypeName('Int8')
ββdefaultValueOfTypeName('Int8')ββ
β 0 β
ββββββββββββββββββββββββββββββββββ
SELECT defaultValueOfTypeName('Nullable(Int8)')
ββdefaultValueOfTypeName('Nullable(Int8)')ββ
β α΄Ία΅α΄Έα΄Έ β
ββββββββββββββββββββββββββββββββββββββββββββ
indexHintβ
The function is intended for debugging and introspection purposes. The function ignores it's argument and always returns 1. Arguments are not even evaluated.
But for the purpose of index analysis, the argument of this function is analyzed as if it was present directly without being wrapped inside indexHint
function. This allows to select data in index ranges by the corresponding condition but without further filtering by this condition. The index in ClickHouse is sparse and using indexHint
will yield more data than specifying the same condition directly.
Syntax
SELECT * FROM table WHERE indexHint(<expression>)
Returned value
- Type: Uint8.
Example
Here is the example of test data from the table ontime.
Input table:
SELECT count() FROM ontime
ββcount()ββ
β 4276457 β
βββββββββββ
The table has indexes on the fields (FlightDate, (Year, FlightDate))
.
Create a query, where the index is not used.
Query:
SELECT FlightDate AS k, count() FROM ontime GROUP BY k ORDER BY k
ClickHouse processed the entire table (Processed 4.28 million rows
).
Result:
βββββββββββkββ¬βcount()ββ
β 2017-01-01 β 13970 β
β 2017-01-02 β 15882 β
........................
β 2017-09-28 β 16411 β
β 2017-09-29 β 16384 β
β 2017-09-30 β 12520 β
ββββββββββββββ΄ββββββββββ
To apply the index, select a specific date.
Query:
SELECT FlightDate AS k, count() FROM ontime WHERE k = '2017-09-15' GROUP BY k ORDER BY k
By using the index, ClickHouse processed a significantly smaller number of rows (Processed 32.74 thousand rows
).
Result:
βββββββββββkββ¬βcount()ββ
β 2017-09-15 β 16428 β
ββββββββββββββ΄ββββββββββ
Now wrap the expression k = '2017-09-15'
into indexHint
function.
Query:
SELECT
FlightDate AS k,
count()
FROM ontime
WHERE indexHint(k = '2017-09-15')
GROUP BY k
ORDER BY k ASC
ClickHouse used the index in the same way as the previous time (Processed 32.74 thousand rows
).
The expression k = '2017-09-15'
was not used when generating the result.
In examle the indexHint
function allows to see adjacent dates.
Result:
βββββββββββkββ¬βcount()ββ
β 2017-09-14 β 7071 β
β 2017-09-15 β 16428 β
β 2017-09-16 β 1077 β
β 2017-09-30 β 8167 β
ββββββββββββββ΄ββββββββββ
replicateβ
Creates an array with a single value.
Used for internal implementation of arrayJoin.
SELECT replicate(x, arr);
Arguments:
arr
β Original array. ClickHouse creates a new array of the same length as the original and fills it with the valuex
.x
β The value that the resulting array will be filled with.
Returned value
An array filled with the value x
.
Type: Array
.
Example
Query:
SELECT replicate(1, ['a', 'b', 'c'])
Result:
ββreplicate(1, ['a', 'b', 'c'])ββ
β [1,1,1] β
βββββββββββββββββββββββββββββββββ
filesystemAvailableβ
Returns amount of remaining space on the filesystem where the files of the databases located. It is always smaller than total free space (filesystemFree) because some space is reserved for OS.
Syntax
filesystemAvailable()
Returned value
- The amount of remaining space available in bytes.
Type: UInt64.
Example
Query:
SELECT formatReadableSize(filesystemAvailable()) AS "Available space", toTypeName(filesystemAvailable()) AS "Type";
Result:
ββAvailable spaceββ¬βTypeββββ
β 30.75 GiB β UInt64 β
βββββββββββββββββββ΄βββββββββ
filesystemFreeβ
Returns total amount of the free space on the filesystem where the files of the databases located. See also filesystemAvailable
Syntax
filesystemFree()
Returned value
- Amount of free space in bytes.
Type: UInt64.
Example
Query:
SELECT formatReadableSize(filesystemFree()) AS "Free space", toTypeName(filesystemFree()) AS "Type";
Result:
ββFree spaceββ¬βTypeββββ
β 32.39 GiB β UInt64 β
ββββββββββββββ΄βββββββββ
filesystemCapacityβ
Returns the capacity of the filesystem in bytes. For evaluation, the path to the data directory must be configured.
Syntax
filesystemCapacity()
Returned value
- Capacity information of the filesystem in bytes.
Type: UInt64.
Example
Query:
SELECT formatReadableSize(filesystemCapacity()) AS "Capacity", toTypeName(filesystemCapacity()) AS "Type"
Result:
ββCapacityβββ¬βTypeββββ
β 39.32 GiB β UInt64 β
βββββββββββββ΄βββββββββ
initializeAggregationβ
Calculates result of aggregate function based on single value. It is intended to use this function to initialize aggregate functions with combinator -State. You can create states of aggregate functions and insert them to columns of type AggregateFunction or use initialized aggregates as default values.
Syntax
initializeAggregation (aggregate_function, arg1, arg2, ..., argN)
Arguments
aggregate_function
β Name of the aggregation function to initialize. String.arg
β Arguments of aggregate function.
Returned value(s)
- Result of aggregation for every row passed to the function.
The return type is the same as the return type of function, that initializeAgregation
takes as first argument.
Example
Query:
SELECT uniqMerge(state) FROM (SELECT initializeAggregation('uniqState', number % 3) AS state FROM numbers(10000));
Result:
ββuniqMerge(state)ββ
β 3 β
ββββββββββββββββββββ
Query:
SELECT finalizeAggregation(state), toTypeName(state) FROM (SELECT initializeAggregation('sumState', number % 3) AS state FROM numbers(5));
Result:
ββfinalizeAggregation(state)ββ¬βtoTypeName(state)ββββββββββββββ
β 0 β AggregateFunction(sum, UInt8) β
β 1 β AggregateFunction(sum, UInt8) β
β 2 β AggregateFunction(sum, UInt8) β
β 0 β AggregateFunction(sum, UInt8) β
β 1 β AggregateFunction(sum, UInt8) β
ββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββββ
Example with AggregatingMergeTree
table engine and AggregateFunction
column:
CREATE TABLE metrics
(
key UInt64,
value AggregateFunction(sum, UInt64) DEFAULT initializeAggregation('sumState', toUInt64(0))
)
ENGINE = AggregatingMergeTree
ORDER BY key
INSERT INTO metrics VALUES (0, initializeAggregation('sumState', toUInt64(42)))
See Also
finalizeAggregationβ
Takes state of aggregate function. Returns result of aggregation (or finalized state when using-State combinator).
Syntax
finalizeAggregation(state)
Arguments
state
β State of aggregation. AggregateFunction.
Returned value(s)
- Value/values that was aggregated.
Type: Value of any types that was aggregated.
Examples
Query:
SELECT finalizeAggregation(( SELECT countState(number) FROM numbers(10)));
Result:
ββfinalizeAggregation(_subquery16)ββ
β 10 β
ββββββββββββββββββββββββββββββββββββ
Query:
SELECT finalizeAggregation(( SELECT sumState(number) FROM numbers(10)));
Result:
ββfinalizeAggregation(_subquery20)ββ
β 45 β
ββββββββββββββββββββββββββββββββββββ
Note that NULL
values are ignored.
Query:
SELECT finalizeAggregation(arrayReduce('anyState', [NULL, 2, 3]));
Result:
ββfinalizeAggregation(arrayReduce('anyState', [NULL, 2, 3]))ββ
β 2 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ
Combined example:
Query:
WITH initializeAggregation('sumState', number) AS one_row_sum_state
SELECT
number,
finalizeAggregation(one_row_sum_state) AS one_row_sum,
runningAccumulate(one_row_sum_state) AS cumulative_sum
FROM numbers(10);
Result:
ββnumberββ¬βone_row_sumββ¬βcumulative_sumββ
β 0 β 0 β 0 β
β 1 β 1 β 1 β
β 2 β 2 β 3 β
β 3 β 3 β 6 β
β 4 β 4 β 10 β
β 5 β 5 β 15 β
β 6 β 6 β 21 β
β 7 β 7 β 28 β
β 8 β 8 β 36 β
β 9 β 9 β 45 β
ββββββββββ΄ββββββββββββββ΄βββββββββββββββββ
See Also
runningAccumulateβ
Accumulates states of an aggregate function for each row of a data block.
warning
The state is reset for each new data block.
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 thegrouping
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 thegrouping
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
takessumState(0)
and returns0
. - For the second row, the function merges
sumState(0)
andsumState(1)
resulting insumState(0 + 1)
, and returns1
as a result. - For the third row, the function merges
sumState(0 + 1)
andsumState(2)
resulting insumState(0 + 1 + 2)
, and returns3
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 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 indicates where search is performed. The identifier is searched in the default database (see parameterdefault_database
in the config file). To override the default database, use theUSE db_name
or specify the database and the table through the separatordb_name.db_table
, see the example.value_column
β name of the column of the table that contains required data.join_keys
β list of keys.
Returned value
Returns list of values corresponded to list of keys.
If certain does not exist in source table then 0
or null
will be returned based on join_use_nulls setting.
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) SETTINGS join_use_nulls = 1
INSERT INTO db_test.id_val VALUES (1,11)(2,12)(4,13)
ββidββ¬βvalββ
β 4 β 13 β
β 2 β 12 β
β 1 β 11 β
ββββββ΄ββββββ
Query:
SELECT joinGet(db_test.id_val,'val',toUInt32(number)) from numbers(4) SETTINGS join_use_nulls = 1
Result:
ββjoinGet(db_test.id_val, 'val', toUInt32(number))ββ
β 0 β
β 11 β
β 12 β
β 0 β
ββββββββββββββββββββββββββββββββββββββββββββββββββββ
modelEvaluate(model_name, β¦)β
Evaluate external model. Accepts a model name and model arguments. Returns Float64.
throwIf(x[, custom_message])β
Throw an exception if the argument is non zero. custom_message - is an optional parameter: a constant string, provides an error message
SELECT throwIf(number = 3, 'Too many') FROM numbers(10);
β Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Received exception from server (version 19.14.1):
Code: 395. DB::Exception: Received from localhost:9000. DB::Exception: Too many.
identityβ
Returns the same value that was used as its argument. Used for debugging and testing, allows to cancel using index, and get the query performance of a full scan. When query is analyzed for possible use of index, the analyzer does not look inside identity
functions. Also constant folding is not applied too.
Syntax
identity(x)
Example
Query:
SELECT identity(42)
Result:
ββidentity(42)ββ
β 42 β
ββββββββββββββββ
randomPrintableASCIIβ
Generates a string with a random set of ASCII printable characters.
Syntax
randomPrintableASCII(length)
Arguments
length
β Resulting string length. Positive integer.If you pass `length < 0`, behavior of the function is undefined.
Returned value
- String with a random set of ASCII printable characters.
Type: String
Example
SELECT number, randomPrintableASCII(30) as str, length(str) FROM system.numbers LIMIT 3
ββnumberββ¬βstrβββββββββββββββββββββββββββββ¬βlength(randomPrintableASCII(30))ββ
β 0 β SuiCOSTvC0csfABSw=UcSzp2.`rv8x β 30 β
β 1 β 1Ag NlJ &RCN:*>HVPG;PE-nO"SUFD β 30 β
β 2 β /"+<"wUTh:=LjJ Vm!c&hI*m#XTfzz β 30 β
ββββββββββ΄βββββββββββββββββββββββββββββββββ΄βββββββββββββββββββββββββββββββββββ
randomStringβ
Generates a binary string of the specified length filled with random bytes (including zero bytes).
Syntax
randomString(length)
Arguments
length
β String length. Positive integer.
Returned value
- String filled with random bytes.
Type: String.
Example
Query:
SELECT randomString(30) AS str, length(str) AS len FROM numbers(2) FORMAT Vertical;
Result:
Row 1:
ββββββ
str: 3 G : pT ?w Ρi k aV f6
len: 30
Row 2:
ββββββ
str: 9 ,] ^ ) ]?? 8
len: 30
See Also
randomFixedStringβ
Generates a binary string of the specified length filled with random bytes (including zero bytes).
Syntax
randomFixedString(length);
Arguments
length
β String length in bytes. UInt64.
Returned value(s)
- String filled with random bytes.
Type: FixedString.
Example
Query:
SELECT randomFixedString(13) as rnd, toTypeName(rnd)
Result:
ββrndβββββββ¬βtoTypeName(randomFixedString(13))ββ
β jβhγHΙ¨Z'β β FixedString(13) β
ββββββββββββ΄ββββββββββββββββββββββββββββββββββββ
randomStringUTF8β
Generates a random string of a specified length. Result string contains valid UTF-8 code points. The value of code points may be outside of the range of assigned Unicode.
Syntax
randomStringUTF8(length);
Arguments
length
β Required length of the resulting string in code points. UInt64.
Returned value(s)
- UTF-8 random string.
Type: String.
Example
Query:
SELECT randomStringUTF8(13)
Result:
ββrandomStringUTF8(13)ββ
β π€πΠ΄ε
εΊσ‘
΄σ±±σ¦ͺξ₯τξπΉπ° β
ββββββββββββββββββββββββ
getSettingβ
Returns the current value of a custom setting.
Syntax
getSetting('custom_setting');
Parameter
custom_setting
β The setting name. String.
Returned value
- The setting current value.
Example
SET custom_a = 123;
SELECT getSetting('custom_a');
Result
123
See Also
isDecimalOverflowβ
Checks whether the Decimal value is out of its (or specified) precision.
Syntax
isDecimalOverflow(d, [p])
Arguments
d
β value. Decimal.p
β precision. Optional. If omitted, the initial precision of the first argument is used. Using of this paratemer could be helpful for data extraction to another DBMS or file. UInt8.
Returned values
1
β Decimal value has more digits then it's precision allow,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 you need to represent the value.
Syntax
countDigits(x)
Arguments
Returned value
Number of digits.
Type: UInt8.
note
For Decimal
values takes into account their scales: calculates result over underlying integer type which is (value * scale)
. For example: countDigits(42) = 2
, countDigits(42.000) = 5
, countDigits(0.04200) = 4
. I.e. you may check decimal overflow for Decimal64
with countDecimal(x) > 18
. It's a slow variant of isDecimalOverflow.
Example
Query:
SELECT countDigits(toDecimal32(1, 9)), countDigits(toDecimal32(-1, 9)),
countDigits(toDecimal64(1, 18)), countDigits(toDecimal64(-1, 18)),
countDigits(toDecimal128(1, 38)), countDigits(toDecimal128(-1, 38));
Result:
10 10 19 19 39 39
errorCodeToNameβ
Returned value
- Variable name for the error code.
Type: LowCardinality(String).
Syntax
errorCodeToName(1)
Result:
UNSUPPORTED_METHOD
tcpPortβ
Returns native interface TCP port number listened by this server. If it is executed in the context of a distributed table, then it generates a normal column, otherwise it produces a constant value.
Syntax
tcpPort()
Arguments
- None.
Returned value
- The TCP port number.
Type: UInt16.
Example
Query:
SELECT tcpPort();
Result:
ββtcpPort()ββ
β 9000 β
βββββββββββββ
See Also
currentProfilesβ
Returns a list of the current settings profiles for the current user.
The command SET PROFILE could be used to change the current setting profile. If the command SET PROFILE
was not used the function returns the profiles specified at the current user's definition (see CREATE USER).
Syntax
currentProfiles()
Returned value
- List of the current user settings profiles.
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.
defaultProfilesβ
Returns all the profiles specified at the current user's definition (see CREATE USER statement).
Syntax
defaultProfiles()
Returned value
- List of the default settings profiles.
currentRolesβ
Returns the names of the roles which are current for the current user. The current roles can be changed by the SET ROLE statement. If the SET ROLE
statement was not used, the function currentRoles
returns the same as defaultRoles
.
Syntax
currentRoles()
Returned value
- List of the current roles for the current user.
enabledRolesβ
Returns the names of the current roles and the roles, granted to some of the current roles.
Syntax
enabledRoles()
Returned value
- List of the enabled roles for the current user.
defaultRolesβ
Returns the names of the roles which are enabled by default for the current user when he logins. Initially these are all roles granted to the current user (see GRANT), but that can be changed with the SET DEFAULT ROLE statement.
Syntax
defaultRoles()
Returned value
- List of the default roles for the current user.
getServerPortβ
Returns the number of the server port. When the port is not used by the server, throws an exception.
Syntax
getServerPort(port_name)
Arguments
port_name
β The name of the server port. String. Possible values:- 'tcp_port'
- 'tcp_port_secure'
- 'http_port'
- 'https_port'
- 'interserver_http_port'
- 'interserver_https_port'
- 'mysql_port'
- 'postgresql_port'
- 'grpc_port'
- 'prometheus.port'
Returned value
- The number of the server port.
Type: UInt16.
Example
Query:
SELECT getServerPort('tcp_port');
Result:
ββgetServerPort('tcp_port')ββ
β 9000 β
βββββββββββββββββββββββββββββ
queryIDβ
Returns the ID of the current query. Other parameters of a query can be extracted from the system.query_log table via query_id
.
In contrast to initialQueryID function, queryID
can return different results on different shards (see example).
Syntax
queryID()
Returned value
- The ID of the current query.
Type: String
Example
Query:
CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT queryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());
Result:
ββcount()ββ
β 3 β
βββββββββββ
initialQueryIDβ
Returns the ID of the initial current query. Other parameters of a query can be extracted from the system.query_log table via initial_query_id
.
In contrast to queryID function, initialQueryID
returns the same results on different shards (see example).
Syntax
initialQueryID()
Returned value
- The ID of the initial current query.
Type: String
Example
Query:
CREATE TABLE tmp (str String) ENGINE = Log;
INSERT INTO tmp (*) VALUES ('a');
SELECT count(DISTINCT t) FROM (SELECT initialQueryID() AS t FROM remote('127.0.0.{1..3}', currentDatabase(), 'tmp') GROUP BY queryID());
Result:
ββcount()ββ
β 1 β
βββββββββββ
shardNumβ
Returns the index of a shard which processes a part of data for a distributed query. Indices are started from 1
.
If a query is not distributed then constant value 0
is returned.
Syntax
shardNum()
Returned value
- Shard index or constant
0
.
Type: UInt32.
Example
In the following example a configuration with two shards is used. The query is executed on the system.one table on every shard.
Query:
CREATE TABLE shard_num_example (dummy UInt8)
ENGINE=Distributed(test_cluster_two_shards_localhost, system, one, dummy);
SELECT dummy, shardNum(), shardCount() FROM shard_num_example;
Result:
ββdummyββ¬βshardNum()ββ¬βshardCount()ββ
β 0 β 2 β 2 β
β 0 β 1 β 2 β
βββββββββ΄βββββββββββββ΄βββββββββββββββ
See Also
shardCountβ
Returns the total number of shards for a distributed query.
If a query is not distributed then constant value 0
is returned.
Syntax
shardCount()
Returned value
- Total number of shards or
0
.
Type: UInt32.
See Also
- shardNum() function example also contains
shardCount()
function call.
getOSKernelVersionβ
Returns a string with the current OS kernel version.
Syntax
getOSKernelVersion()
Arguments
- None.
Returned value
- The current OS kernel version.
Type: String.
Example
Query:
SELECT getOSKernelVersion();
Result:
ββgetOSKernelVersion()βββββ
β Linux 4.15.0-55-generic β
βββββββββββββββββββββββββββ
zookeeperSessionUptimeβ
Returns the uptime of the current ZooKeeper session in seconds.
Syntax
zookeeperSessionUptime()
Arguments
- None.
Returned value
- Uptime of the current ZooKeeper session in seconds.
Type: UInt32.
Example
Query:
SELECT zookeeperSessionUptime();
Result:
ββzookeeperSessionUptime()ββ
β 286 β
ββββββββββββββββββββββββββββ