Functions

BLAKE3(message)

• message — The input string to hash. String

Returns the 32-byte BLAKE3 hash of the input string as a fixed-length string. FixedString(32)

CAST(x, T) or CAST(x AS T) or x::T

• x — A value of any type. Any
• T — The target data type. String

Returns the converted value with the target data type. Any

CRC32(s)

• s — String to calculate CRC32 for. String

Returns the CRC32 checksum of the string. UInt32

CRC32IEEE(s)

• s — String to calculate CRC32 for. String

Returns the CRC32 checksum of the string. UInt32

CRC64(s)

• s — String to calculate CRC64 for. String

Returns the CRC64 checksum of the string. UInt64

DATE(expr)

• expr — The value to convert. String or UInt32 or Date or DateTime

Returns a Date value. Date

FQDN()

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

HMAC(mode, message, key)

• mode — Hash algorithm name (case-insensitive). Supported: md5, sha1, sha224, sha256, sha384, sha512. String
• message — Message to be authenticated. String
• key — Secret key for HMAC. String

Returns a binary string containing the HMAC digest. String

IPv4CIDRToRange(ipv4, cidr)

• ipv4 — IPv4 address. IPv4 or String
• cidr — CIDR value. UInt8

Returns a tuple with two IPv4 addresses representing the subnet range. Tuple(IPv4, IPv4)

IPv4NumToString(num)

• num — IPv4 address as UInt32 number. UInt32

Returns a number representing the MAC address, or 0 if the format is invalid. String

IPv4NumToStringClassC(num)

• num — IPv4 address as UInt32 number. UInt32

Returns the IPv4 address string with xxx replacing the last octet. String

IPv4StringToNum(string)

• string — IPv4 address string. String

Returns theIPv4 address. UInt32

IPv4StringToNumOrDefault(string)

• string — IPv4 address string. String

Returns the IPv4 address, or 0 if invalid. UInt32

IPv4StringToNumOrNull(string)

• string — IPv4 address string. String

Returns the IPv4 address, or NULL if invalid. Nullable(UInt32)

IPv4ToIPv6(x)

• x — IPv4 address. UInt32

Returns an IPv6 address in binary format. FixedString(16)

IPv6CIDRToRange(ipv6, cidr)

• ipv6 — IPv6 address. IPv6 or String
• cidr — CIDR value. UInt8

Returns a tuple with two IPv6 addresses representing the subnet range. Tuple(IPv6, IPv6)

IPv6NumToString(x)

• x — IPv6 address in binary format. FixedString(16) or IPv6

Returns the IPv6 address string in text format. String

IPv6StringToNum(string)

• string — IPv6 address string. String

Returns theIPv6 address in binary format. FixedString(16)

IPv6StringToNumOrDefault(string)

• string — IPv6 address string. String

IPv6 address in binary format, or zero-filled FixedString(16) if invalid. FixedString(16)

IPv6StringToNumOrNull(string)

• string — IPv6 address string. String

Returns IPv6 address in binary format, or NULL if invalid. Nullable(FixedString(16))

JSONAllPaths(json)

• json — JSON column. JSON

Returns an array of all paths in the JSON column. Array(String)

JSONAllPathsWithTypes(json)

• json — JSON column. JSON

Returns a map of all paths and their data types in the JSON column. Map(String, String)

JSONArrayLength(json)

• json — String with valid JSON. String

Returns the number of array elements if json is a valid JSON array string, otherwise returns NULL. Nullable(UInt64)

JSONDynamicPaths(json)

• json — JSON column. JSON

Returns an array of dynamic paths in the JSON column. Array(String)

JSONDynamicPathsWithTypes(json)

• json — JSON column. JSON

Returns a map of dynamic paths and their data types in the JSON column. Map(String, String)

JSONExtract(json[, indices_or_keys, ...], return_type)

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*
• return_type — ClickHouse data type to return. String

Returns a value of specified ClickHouse data type if possible, otherwise returns the default value for that type.

JSONExtractArrayRaw(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns an array of strings with JSON array elements. If the part is not an array or does not exist, an empty array will be returned. Array(String)

JSONExtractArrayRawCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the array. Keys use case-insensitive matching String or (U)Int*

Returns an array of raw JSON strings. Array(String)

JSONExtractBool(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns a Bool value if it exists, otherwise returns 0. Bool

JSONExtractBoolCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returns the extracted boolean value (1 for true, 0 for false), 0 if not found. UInt8

JSONExtractCaseInsensitive(json [, indices_or_keys...], return_type)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*
• return_type — The ClickHouse data type to extract String

Returns the extracted value in the specified data type. Any

JSONExtractFloat(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns a Float value if it exists, otherwise returns 0. Float64

JSONExtractFloatCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returns the extracted Float value, 0 if not found or cannot be converted. Float64

JSONExtractInt(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns an Int value if it exists, otherwise returns 0. Int64

JSONExtractIntCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returns the extracted Int value, 0 if not found or cannot be converted. Int64

JSONExtractKeys(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns an array with the keys of the JSON object. Array(String)

JSONExtractKeysAndValues(json[, indices_or_keys, ...], value_type)

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*
• value_type — ClickHouse data type of the values. String

Returns an array of tuples with the parsed key-value pairs. Array(Tuple(String, value_type))

JSONExtractKeysAndValuesCaseInsensitive(json [, indices_or_keys...], value_type)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the object. Keys use case-insensitive matching String or (U)Int*
• value_type — The ClickHouse data type of the values String

Returns an array of tuples containing key-value pairs. Array(Tuple(String, T))

JSONExtractKeysAndValuesRaw(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns an array of tuples with parsed key-value pairs where values are unparsed strings. Array(Tuple(String, String))

JSONExtractKeysAndValuesRawCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the object. Keys use case-insensitive matching String or (U)Int*

Returns an array of tuples containing key-value pairs as raw strings. Array(Tuple(String, String))

JSONExtractKeysCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the object. Keys use case-insensitive matching String or (U)Int*

Returns an array of keys from the JSON object. Array(String)

JSONExtractRaw(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns the part of JSON as an unparsed string. If the part does not exist or has a wrong type, an empty string will be returned. String

JSONExtractRawCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returns the raw JSON string of the extracted element. String

JSONExtractString(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns a String value if it exists, otherwise returns an empty string. String

JSONExtractStringCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returns the extracted string value, empty string if not found. String

JSONExtractUInt(json [, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returns a UInt value if it exists, otherwise returns 0. UInt64

JSONExtractUIntCaseInsensitive(json [, indices_or_keys]...)

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returns the extracted UInt value, 0 if not found or cannot be converted. UInt64

JSONHas(json[ ,indices_or_keys, ...])

• json — JSON string to parse String
• [ ,indices_or_keys, ...] — A list of zero or more arguments. String or (U)Int*

Returns 1 if the value exists in json, otherwise 0 UInt8

JSONKey(json[, indices_or_keys, ...])

• json — JSON string to parse. String
• indices_or_keys — Optional list of indices or keys specifying a path to a nested element. Each argument can be either a string (access by key) or an integer (access by index starting from 1). String or Int*

Returns the key name at the specified position in the JSON object. String

JSONLength(json [, indices_or_keys, ...])

• json — JSON string to parse String
• [, indices_or_keys, ...] — Optional. A list of zero or more arguments. String or (U)Int8/16/32/64

Returns the length of the JSON array or JSON object, otherwise returns 0 if the value does not exist or has the wrong type. UInt64

JSONMergePatch(json1[, json2, ...])

• json1[, json2, ...] — One or more strings with valid JSON. String

Returns the merged JSON object string, if the JSON object strings are valid. String

JSONSharedDataPaths(json)

• json — JSON column. JSON

Returns an array of paths stored in shared data structure in the JSON column. Array(String)

JSONSharedDataPathsWithTypes(json)

• json — JSON column. JSON

Returns a map of paths stored in shared data structure and their data types in the JSON column. Map(String, String)

JSONType(json[, indices_or_keys, ...])

• json — JSON string to parse String
• json[, indices_or_keys, ...] — A list of zero or more arguments, each of which can be either string or integer. String or (U)Int8/16/32/64

Returns the type of a JSON value as a string, otherwise if the value doesn't exist it returns Null=0 Enum

JSON_EXISTS(json, path)

• json — A string with valid JSON. String
• path — A string representing the path. String

Returns 1 if the value exists in the JSON document, otherwise 0. UInt8

JSON_QUERY(json, path)

• json — A string with valid JSON. String
• path — A string representing the path. String

Returns the extracted JSON array or JSON object as a string, or an empty string if the value does not exist. String

JSON_VALUE(json, path)

• json — A string with valid JSON. String
• path — A string representing the path. String

Returns the extracted JSON scalar as a string, or an empty string if the value does not exist. String

L1Distance(vector1, vector2)

• vector1 — First vector. Tuple(T) or Array(T)
• vector2 — Second vector. Tuple(T) or Array(T)

Returns the 1-norm distance. UInt32 or Float64

L1Norm(vector)

• vector — Vector or tuple of numeric values. Array(T) or Tuple(T)

Returns the L1-norm or taxicab geometry distance. UInt* or Float* or Decimal

L1Normalize(tuple)

• tuple — A tuple of numeric values. Tuple(T)

Returns the unit vector. Tuple(Float64)

L2Distance(vector1, vector2)

• vector1 — First vector. Tuple(T) or Array(T)
• vector2 — Second vector. Tuple(T) or Array(T)

Returns the 2-norm distance. Float64

L2DistanceTransposed(vector1, vector2, p)

• vectors — Vectors. QBit(T, UInt64)
• reference — Reference vector. Array(T)
• p — Number of bits from each vector element to use in the distance calculation (1 to element bit-width). The quantization level controls the precision-speed trade-off. Using fewer bits results in faster I/O and calculations with reduced accuracy, while using more bits increases accuracy at the cost of performance. UInt

Returns the approximate 2-norm distance. Float64

L2Norm(vector)

• vector — Vector or tuple of numeric values. Tuple(T) or Array(T)

Returns the L2-norm or Euclidean distance. UInt* or Float*

L2Normalize(tuple)

• tuple — A tuple of numeric values. Tuple(T)

Returns the unit vector. Tuple(Float64)

L2SquaredDistance(vector1, vector2)

• vector1 — First vector. Tuple(T) or Array(T)
• vector2 — Second vector. Tuple(T) or Array(T)

Returns the sum of the squares of the difference between the corresponding elements of two vectors. Float64

L2SquaredNorm(vector)

• vector — Vector or tuple of numeric values. Array(T) or Tuple(T)

Returns the L2-norm squared. UInt* or Float* or Decimal

LinfDistance(vector1, vector2)

• vector1 — First vector. Tuple(T) or Array(T)
• vector2 — Second vector. Tuple(T) or Array(T)

Returns the Infinity-norm distance. Float64

LinfNorm(vector)

• vector — Vector or tuple of numeric values. Array(T) or Tuple(T)

Returns the Linf-norm or the maximum absolute value. Float64

LinfNormalize(tuple)

• tuple — A tuple of numeric values. Tuple(T)

Returns the unit vector. Tuple(Float64)

LpDistance(vector1, vector2, p)

• vector1 — First vector. Tuple(T) or Array(T)
• vector2 — Second vector. Tuple(T) or Array(T)
• p — The power. Possible values: real number from [1; inf). UInt* or Float*

Returns the p-norm distance. Float64

LpNorm(vector, p)

• vector — Vector or tuple of numeric values. Tuple(T) or Array(T)
• p — The power. Possible values are real numbers in the range [1; inf). UInt* or Float*

Returns the Lp-norm. Float64

LpNormalize(tuple, p)

• tuple — A tuple of numeric values. Tuple(T)
• p — The power. Possible values are any number in the range range from [1; inf). UInt* or Float*

Returns the unit vector. Tuple(Float64)

MACNumToString(num)

• num — UInt64 number. UInt64

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

MACStringToNum(s)

• s — MAC address string. String

Returns a UInt64 number. UInt64

MACStringToOUI(s)

• s — MAC address string. String

First three octets as UInt64 number. UInt64

MD4(s)

• s — The input string to hash. String

Returns the MD4 hash of the given input string as a fixed-length string. FixedString(16)

MD5(s)

• s — The input string to hash. String

Returns the MD5 hash of the given input string as a fixed-length string. FixedString(16)

RIPEMD160(s)

• s — The input string to hash. String

Returns the RIPEMD160 hash of the given input string as a fixed-length string. FixedString(20)

SHA1(s)

• s — The input string to hash String

Returns the SHA1 hash of the given input string as a fixed-length string. FixedString(20)

SHA224(s)

• s — The input value to hash. String

Returns the SHA224 hash of the given input string as a fixed-length string. FixedString(28)

SHA256(s)

• s — The input string to hash. String

Returns the SHA256 hash of the given input string as a fixed-length string. FixedString(32)

SHA384(s)

• s — The input string to hash. String

Returns the SHA384 hash of the given input string as a fixed-length string. FixedString(48)

SHA512(s)

• s — The input string to hash String

Returns the SHA512 hash of the given input string as a fixed-length string. FixedString(64)

SHA512_256(s)

• s — The input string to hash. String

Returns the SHA512_256 hash of the given input string as a fixed-length string. FixedString(32)

ULIDStringToDateTime(ulid[, timezone])

• ulid — Input ULID. String or FixedString(26)
• timezone — Optional. Timezone name for the returned value. String

Timestamp with milliseconds precision. DateTime64(3)

URLHash(url[, N])

• url — URL string to hash. String
• N — Optional. Level in the URL hierarchy. (U)Int*

Returns the computed hash value of url. UInt64

URLHierarchy(url)

• url — The URL to process. String

Returns an array of progressively longer URLs forming a hierarchy. Array(String)

URLPathHierarchy(url)

• url — The URL to process. String

Returns an array of progressively longer URL path components forming a hierarchy. Array(String)

UTCTimestamp()

Returns the current date and time at the moment of query analysis. DateTime

UUIDNumToString(binary[, variant])

• binary — Binary representation of a UUID. FixedString(16)
• variant — Variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft. (U)Int*

Returns the UUID as a string. String

UUIDStringToNum(string[, variant = 1])

• string — A string or fixed-string of 36 characters) String or FixedString(36)
• variant — Variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft. (U)Int*

Returns the binary representation of string. FixedString(16)

UUIDToNum(uuid[, variant = 1])

• uuid — UUID. String or FixedString
• variant — Variant as specified by RFC4122. 1 = Big-endian (default), 2 = Microsoft. (U)Int*

Returns a binary representation of the UUID. FixedString(16)

UUIDv7ToDateTime(uuid[, timezone])

• uuid — A UUID version 7. String
• timezone — Optional. Timezone name for the returned value. String

Returns a timestamp with milliseconds precision. If the UUID is not a valid version 7 UUID, it returns 1970-01-01 00:00:00.000. DateTime64(3)

YYYYMMDDToDate(YYYYMMDD)

• YYYYMMDD — Number containing the year, month and day. (U)Int* or Float* or Decimal

Returns a Date value from the provided arguments Date

YYYYMMDDToDate32(YYYYMMDD)

• YYYYMMDD — Number containing the year, month and day. (U)Int* or Float* or Decimal

Returns a Date32 value from the provided arguments Date32

YYYYMMDDhhmmssToDateTime(YYYYMMDDhhmmss[, timezone])

• YYYYMMDDhhmmss — Number containing the year, month, day, hour, minute, and second. (U)Int* or Float* or Decimal
• timezone — Timezone name. String

Returns a DateTime value from the provided arguments DateTime

YYYYMMDDhhmmssToDateTime64(YYYYMMDDhhmmss[, precision[, timezone]])

• YYYYMMDDhhmmss — Number containing the year, month, day, hour, minute, and second. (U)Int* or Float* or Decimal
• precision — Precision for the fractional part (0-9). UInt8
• timezone — Timezone name. String

Returns a DateTime64 value from the provided arguments DateTime64

abs(x)

• x — Value to get the absolute value of The absolute value of x

accurateCast(x, T)

• x — A value to convert. Any
• T — The target data type name. String

Returns the converted value with the target data type. Any

accurateCastOrDefault(x, T[, default_value])

• x — A value to convert. Any
• T — The target data type name. const String
• default_value — Optional. Default value to return if conversion fails. Any

Returns the converted value with the target data type, or the default value if conversion is not possible. Any

accurateCastOrNull(x, T)

• x — A value to convert. Any
• T — The target data type name. String

Returns the converted value with the target data type, or NULL if conversion is not possible. Any

acos(x)

• x — The value for which to find arc cosine of. (U)Int* or Float* or Decimal*

Returns the arc cosine of x Float*

acosh(x)

• x — Hyperbolic cosine of angle. Values from the interval: 1 ≤ x < +∞. (U)Int* or Float* or Decimal*

Returns the angle, in radians. Values from the interval: 0 ≤ acosh(x) < +∞. Float64

addDate(datetime, interval)

• datetime — The date or date with time to which interval is added. Date or Date32 or DateTime or DateTime64 or String
• interval — Interval to add. Interval

Returns date or date with time obtained by adding interval to datetime. Date or Date32 or DateTime or DateTime64

addDays(datetime, num)

• datetime — Date or date with time to add specified number of days to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of days to add. (U)Int* or Float*

Returns datetime plus num days. Date or Date32 or DateTime or DateTime64

addHours(datetime, num)

• datetime — Date or date with time to add specified number of hours to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of hours to add. (U)Int* or Float*

Returns datetime plus num hours DateTime or DateTime64(3)

addInterval(interval_1, interval_2)

• interval_1 — First interval or tuple of intervals. Interval or Tuple(Interval)
• interval_2 — Second interval to be added. Interval

Returns a tuple of intervals Tuple(Interval)

addMicroseconds(datetime, num)

• datetime — Date with time to add specified number of microseconds to. DateTime or DateTime64 or String
• num — Number of microseconds to add. (U)Int* or Float*

Returns date_time plus num microseconds DateTime64

addMilliseconds(datetime, num)

• datetime — Date with time to add specified number of milliseconds to. DateTime or DateTime64 or String
• num — Number of milliseconds to add. (U)Int* or Float*

Returns datetime plus num milliseconds DateTime64

addMinutes(datetime, num)

• datetime — Date or date with time to add specified number of minutes to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of minutes to add. (U)Int* or Float*

Returns datetime plus num minutes DateTime or DateTime64(3)

addMonths(datetime, num)

• datetime — Date or date with time to add specified number of months to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of months to add. (U)Int* or Float*

Returns datetime plus num months Date or Date32 or DateTime or DateTime64

addNanoseconds(datetime, num)

• datetime — Date with time to add specified number of nanoseconds to. DateTime or DateTime64 or String
• num — Number of nanoseconds to add. (U)Int* or Float*

Returns datetime plus num nanoseconds DateTime64

addQuarters(datetime, num)

• datetime — Date or date with time to add specified number of quarters to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of quarters to add. (U)Int* or Float*

Returns datetime plus num quarters Date or Date32 or DateTime or DateTime64

addSeconds(datetime, num)

• datetime — Date or date with time to add specified number of seconds to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of seconds to add. (U)Int* or Float*

Returns datetime plus num seconds DateTime or DateTime64(3)

addTupleOfIntervals(datetime, intervals)

• datetime — Date or date with time to add intervals to. Date or Date32 or DateTime or DateTime64
• intervals — Tuple of intervals to add to datetime. Tuple(Interval)

Returns date with added intervals Date or Date32 or DateTime or DateTime64

addWeeks(datetime, num)

• datetime — Date or date with time to add specified number of weeks to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of weeks to add. (U)Int* or Float*

Returns datetime plus num weeks Date or Date32 or DateTime or DateTime64

addYears(datetime, num)

• datetime — Date or date with time to add specified number of years to. Date or Date32 or DateTime or DateTime64 or String
• num — Number of years to add. (U)Int* or Float*

Returns datetime plus num years Date or Date32 or DateTime or DateTime64

addressToLine(address_of_binary_instruction)

• address_of_binary_instruction — Address of instruction in a running process. UInt64

Returns a source code filename and line number delimited by a colon, for example, /build/obj-x86_64-linux-gnu/../src/Common/ThreadPool.cpp:199. Returns the name of a binary, if no debug information could be found, otherwise an empty string, if the address is not valid. String

addressToLineWithInlines(address_of_binary_instruction)

• address_of_binary_instruction — The address of an instruction in a running process. UInt64

Returns an array whose first element is the source code filename and line number delimited by a colon. The second, third, etc. element list inline functions' source code filenames, line numbers and function names. If no debug information could be found, then an array with a single element equal to the name of the binary is returned, otherwise an empty array is returned if the address is not valid. Array(String)

addressToSymbol(address_of_binary_instruction)

• address_of_binary_instruction — Address of instruction in a running process. UInt64

Returns the symbol from ClickHouse object files or an empty string, if the address is not valid. String

aes_decrypt_mysql(mode, ciphertext, key[, iv])

• mode — Decryption mode. String
• ciphertext — Encrypted text that needs to be decrypted. String
• key — Decryption key. String
• iv — Optional. Initialization vector. String

Returns the decrypted String. String

aes_encrypt_mysql(mode, plaintext, key[, iv])

• mode — Encryption mode. String
• plaintext — Text that should be encrypted. String
• key — Encryption key. If the key is longer than required by mode, MySQL-specific key folding is performed. String
• iv — Optional. Initialization vector. Only the first 16 bytes are taken into account. String

Ciphertext binary string. String

age('unit', startdate, enddate[, timezone])

• unit — The type of interval for result.

Returns the difference between enddate and startdate expressed in unit. Int32

aggThrow(throw_prob)

• throw_prob — Probability to throw on creation. Float64

Returns an exception: Code: 503. DB::Exception: Aggregate function aggThrow has thrown exception successfully.

alphaTokens(s[, max_substrings])

• s — The string to split. String
• max_substrings — Optional. When max_substrings > 0, the number of returned substrings will be no more than max_substrings, otherwise the function will return as many substrings as possible. Int64

Returns an array of selected substrings of s. Array(String)

analysisOfVariance(val, group_no)

• val — Value. (U)Int* or Float* or Decimal
• group_no — Group number that val belongs to. (U)Int* or Float* or Decimal

Returns a tuple with the F-statistic and p-value. Tuple(Float64, Float64)

and(val1, val2[, ...])

• val1, val2[, ...] — List of at least two values. Nullable((U)Int*) or Nullable(Float*)

Returns: - 0, if at least one argument evaluates to false - NULL, if no argument evaluates to false and at least one argument is NULL - 1, otherwise Nullable(UInt8)

any(column)[ RESPECT NULLS]

• column — The column name. Any

Returns the first value encountered. Any

anyHeavy(column)

• column — The column name. String

Returns a frequently occurring value. The result is nondeterministic. Any

anyLast(column) [RESPECT NULLS]

• column — The column name. Any

Returns the last value encountered. Any

appendTrailingCharIfAbsent(s, c)

• s — Input string. String
• c — Character to append if absent. String

Returns string s with character c appended if s does not end with c. String

approx_top_k(N[, reserved])(column)

• column — The name of the column for which to find the most frequent values. String

Returns an array of the approximately most frequent values and their counts, sorted in descending order of approximate frequency. Array

approx_top_sum(N[, reserved])(column, weight)

• column — The name of the column for which to find the most frequent values. String
• weight — The weight. Every value is accounted weight times for frequency calculation. UInt64

Returns an array of the approximately most frequent values and their counts, sorted in descending order of approximate frequency. Array

areaCartesian(object)

• object — geometry object Geometry

Returns the area of the object. Float64

areaSpherical(object)

• object — geometry object Geometry

Returns the area of the object. Float64

argAndMax(arg, val)

• arg — Argument for which to find the maximum value. const String
• val — The maximum value. (U)Int8/16/32/64 or Float* or Date or DateTime or Tuple

Returns a tuple containing the arg value that corresponds to maximum val value and the maximum val value. Tuple

argAndMin(arg, val)

• arg — Argument for which to find the minimum value. const String
• val — The minimum value. (U)Int8/16/32/64 or Float* or Date or DateTime or Tuple

Returns a tuple containing the arg value that corresponds to minimum val value and the minimum val value. Tuple

argMax(arg, val)

• arg — Argument for which to find the maximum value. const String
• val — The maximum value. (U)Int8/16/32/64 or Float* or Date or DateTime or Tuple

Returns the arg value that corresponds to maximum val value. Type matches arg type.

argMin(arg, val)

• arg — Argument for which to find the maximum value. const String
• val — The minimum value. (U)Int8/16/32/64 or Float* or Date or DateTime or Tuple

Returns the arg value that corresponds to minimum val value. Type matches arg type.

array(x1 [, x2, ..., xN])

• x1 — Constant value of any type T. If only this argument is provided, the array will be of type T. - [, x2, ..., xN] — Additional N constant values sharing a common supertype with x1 Returns an array, where 'T' is the smallest common type out of the passed arguments. Array(T)

arrayAUCPR(scores, labels[, partial_offsets])

• cores — Scores prediction model gives. Array((U)Int*) or Array(Float*)
• labels — Labels of samples, usually 1 for positive sample and 0 for negative sample. Array((U)Int*) or Array(Enum)
• partial_offsets —
• Optional. An Array(T) of three non-negative integers for calculating a partial area under the PR curve (equivalent to a vertical band of the PR space) instead of the whole AUC. This option is useful for distributed computation of the PR AUC. The array must contain the following elements [higher_partitions_tp, higher_partitions_fp, total_positives].
  • higher_partitions_tp: The number of positive labels in the higher-scored partitions.
  • higher_partitions_fp: The number of negative labels in the higher-scored partitions.
  • total_positives: The total number of positive samples in the entire dataset.

note When arr_partial_offsets is used, the arr_scores and arr_labels should be only a partition of the entire dataset, containing an interval of scores. The dataset should be divided into contiguous partitions, where each partition contains the subset of the data whose scores fall within a specific range. For example: - One partition could contain all scores in the range [0, 0.5). - Another partition could contain scores in the range [0.5, 1.0].

Returns area under the precision-recall (PR) curve. Float64

arrayAll(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• cond1_arr, ... — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns 1 if the lambda function returns true for all elements, 0 otherwise UInt8

arrayAvg([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — Optional. A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the average of elements in the source array, or the average of elements of the lambda results if provided. Float64

arrayCompact(arr)

• arr — An array to remove duplicates from. Array(T)

Returns an array without duplicate values Array(T)

arrayConcat(arr1 [, arr2, ... , arrN])

• arr1 [, arr2, ... , arrN] — N number of arrays to concatenate. Array(T)

Returns a single combined array from the provided array arguments. Array(T)

arrayCount([func, ] arr1, ...)

• func — Optional. Function to apply to each element of the array(s). Lambda function
• arr1, ..., arrN — N arrays. Array(T)

Returns the number of elements for which func returns true. Otherwise, returns the number of non-zero elements in the array. UInt32

arrayCumSum([func,] arr1[, arr2, ... , arrN])

• func — Optional. A lambda function to apply to the array elements at each position. Lambda function
• arr1 — The source array of numeric values. Array(T)
• [arr2, ..., arrN] — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. Array(T)

Returns an array of the partial sums of the elements in the source array. The result type matches the input array's numeric type. Array(T)

arrayCumSumNonNegative([func,] arr1[, arr2, ... , arrN])

• func — Optional. A lambda function to apply to the array elements at each position. Lambda function
• arr1 — The source array of numeric values. Array(T)
• [arr2, ..., arrN] — Optional. Additional arrays of the same size, passed as arguments to the lambda function if specified. Array(T)

Returns an array of the partial sums of the elements in the source array, with any negative running sum replaced by zero. The result type matches the input array's numeric type. Array(T)

arrayDifference(arr)

• arr — Array for which to calculate differences between adjacent elements. Array(T)

Returns an array of differences between adjacent array elements UInt*

arrayDistinct(arr)

• arr — Array for which to extract distinct elements. Array(T)

Returns an array containing the distinct elements Array(T)

arrayDotProduct(v1, v2)

• v1 — First vector. Array((U)Int* | Float* | Decimal) or Tuple((U)Int* | Float* | Decimal)
• v2 — Second vector. Array((U)Int* | Float* | Decimal) or Tuple((U)Int* | Float* | Decimal)

The dot product of the two vectors.

note The return type is determined by the type of the arguments. If Arrays or Tuples contain mixed element types then the result type is the supertype.

(U)Int* or Float* or Decimal

arrayElement(arr, n)

• arr — The array to search. Array(T). - n — Position of the element to get. (U)Int*. Returns a single combined array from the provided array arguments Array(T)

arrayElementOrNull(arrays)

• arrays — Arbitrary number of array arguments. Array

Returns a single combined array from the provided array arguments. Array(T)

arrayEnumerate(arr)

• arr — The array to enumerate. Array

Returns the array [1, 2, 3, ..., length (arr)]. Array(UInt32)

arrayEnumerateDense(arr)

• arr — The array to enumerate. Array(T)

Returns an array of the same size as arr, indicating where each element first appears in the source array Array(T)

arrayEnumerateDenseRanked(clear_depth, arr, max_array_depth)

• clear_depth — Enumerate elements at the specified level separately. Must be less than or equal to max_arr_depth. UInt*
• arr — N-dimensional array to enumerate. Array(T)
• max_array_depth — The maximum effective depth. Must be less than or equal to the depth of arr. UInt*

Returns an array denoting where each element first appears in the source array Array

arrayEnumerateUniq(arr1[, arr2, ... , arrN])

• arr1 — First array to process. Array(T)
• arr2, ... — Optional. Additional arrays of the same size for tuple uniqueness. Array(UInt32)

Returns an array where each element is the position among elements with the same value or tuple. Array(T)

arrayEnumerateUniqRanked(clear_depth, arr, max_array_depth)

• clear_depth — Enumerate elements at the specified level separately. Positive integer less than or equal to max_arr_depth. UInt*
• arr — N-dimensional array to enumerate. Array(T)
• max_array_depth — The maximum effective depth. Positive integer less than or equal to the depth of arr. UInt*

Returns an N-dimensional array the same size as arr with each element showing the position of that element in relation to other elements of the same value. Array(T)

arrayExcept(source, except)

• source — The source array containing elements to filter. Array(T)
• except — The array containing elements to exclude from the result. Array(T)

Returns an array of the same type as the input array containing elements from source that weren't found in except. Array(T)

arrayExists(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns 1 if the lambda function returns true for at least one element, 0 otherwise UInt8

arrayFill(func(x [, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x [, y1, ..., yN]) — A lambda function func(x [, y1, y2, ... yN]) → F(x [, y1, y2, ... yN]) which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Lambda function
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns an array Array(T)

arrayFilter(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])]

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns a subset of the source array Array(T)

arrayFirst(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function. - source_arr — The source array to process. Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T). Returns the first element of the source array for which λ is true, otherwise returns the default value of T.

arrayFirstIndex(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function. - source_arr — The source array to process. Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T). Returns the index of the first element of the source array for which func is true, otherwise returns 0 UInt32

arrayFirstOrNull(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the first element of the source array for which func is true, otherwise returns NULL.

arrayFlatten(arr)

• arr — A multidimensional array. Array(Array(T))

Returns a flattened array from the multidimensional array Array(T)

arrayFold(λ(acc, x1 [, x2, x3, ... xN]), arr1 [, arr2, arr3, ... arrN], acc)

• λ(x, x1 [, x2, x3, ... xN]) — A lambda function λ(acc, x1 [, x2, x3, ... xN]) → F(acc, x1 [, x2, x3, ... xN]) where F is an operation applied to acc and array values from x with the result of acc re-used. Lambda function
• arr1 [, arr2, arr3, ... arrN] — N arrays over which to operate. Array(T)
• acc — Accumulator value with the same type as the return type of the Lambda function. Returns the final acc value.

arrayIntersect(arr, arr1, ..., arrN)

• arrN — N arrays from which to make the new array. Array(T). Returns an array with distinct elements that are present in all N arrays Array(T)

arrayJaccardIndex(arr_x, arr_y)

• arr_x — First array. Array(T)
• arr_y — Second array. Array(T)

Returns the Jaccard index of arr_x and arr_y Float64

arrayJoin(arr)

• arr — An array to unfold. Array(T)

Returns a set of rows unfolded from arr.

arrayLast(func(x[, y1, ..., yN]), source[, cond1, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function. - source — The source array to process. Array(T). - [, cond1, ... , condN] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T). Returns the last element of the source array for which func is true, otherwise returns the default value of T.

arrayLastIndex(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the index of the last element of the source array for which func is true, otherwise returns 0 UInt32

arrayLastOrNull(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x [, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function. - source_arr — The source array to process. Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T). Returns the last element of the source array for which λ is not true, otherwise returns NULL.

arrayLevenshteinDistance(from, to)

• from — The first array. Array(T). - to — The second array. Array(T). Levenshtein distance between the first and the second arrays. Float64

arrayLevenshteinDistanceWeighted(from, to, from_weights, to_weights)

• from — first array. Array(T). - to — second array. Array(T). - from_weights — weights for the first array. Array((U)Int*|Float*)
• to_weights — weights for the second array. Array((U)Int*|Float*)

Levenshtein distance between the first and the second arrays with custom weights for each element Float64

arrayMap(func, arr)

• func — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• arr — N arrays to process. Array(T)

Returns an array from the lambda results Array(T)

arrayMax([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — Optional. A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the maximum element in the source array, or the maximum element of the lambda results if provided.

arrayMin([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — Optional. A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• cond1_arr, ... — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the minimum element in the source array, or the minimum element of the lambda results if provided.

arrayNormalizedGini(predicted, label)

• predicted — The predicted value. Array(T)
• label — The actual value. Array(T)

A tuple containing the Gini coefficients of the predicted values, the Gini coefficient of the normalized values, and the normalized Gini coefficient (= the ratio of the former two Gini coefficients) Tuple(Float64, Float64, Float64)

arrayPartialReverseSort([f,] arr [, arr1, ... ,arrN], limit)

• f(arr[, arr1, ... ,arrN]) — The lambda function to apply to elements of array x. Lambda function
• arr — Array to be sorted. Array(T)
• arr1, ... ,arrN — N additional arrays, in the case when f accepts multiple arguments. Array(T)
• limit — Index value up until which sorting will occur. (U)Int*

Returns an array of the same size as the original array where elements in the range [1..limit] are sorted in descending order. The remaining elements (limit..N] are in an unspecified order.

arrayPartialShuffle(arr [, limit[, seed]])

• arr — The array to shuffle. Array(T)
• seed — Optional. The seed to be used with random number generation. If not provided, a random one is used. (U)Int*
• limit — Optional. The number to limit element swaps to, in the range [1..N]. (U)Int*

Array with elements partially shuffled. Array(T)

arrayPartialSort([f,] arr [, arr1, ... ,arrN], limit)

• f(arr[, arr1, ... ,arrN]) — The lambda function to apply to elements of array x. Lambda function
• arr — Array to be sorted. Array(T)
• arr1, ... ,arrN — N additional arrays, in the case when f accepts multiple arguments. Array(T)
• limit — Index value up until which sorting will occur. (U)Int*

Returns an array of the same size as the original array where elements in the range [1..limit] are sorted in ascending order. The remaining elements (limit..N] are in an unspecified order.

arrayPopBack(arr)

• arr — The array for which to remove the last element from. Array(T)

Returns an array identical to arr but without the last element of arr Array(T)

arrayPopFront(arr)

• arr — The array for which to remove the first element from. Array(T)

Returns an array identical to arr but without the first element of arr Array(T)

arrayProduct([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — Optional. A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the product of elements in the source array, or the product of elements of the lambda results if provided. Float64

arrayPushBack(arr, x)

• arr — The array for which to add value x to the end of. Array(T)
• x —
• Single value to add to the end of the array. Array(T).

note - Only numbers can be added to an array with numbers, and only strings can be added to an array of strings. - When adding numbers, ClickHouse automatically sets the type of x for the data type of the array. - Can be NULL. The function adds a NULL element to an array, and the type of array elements converts to Nullable.

For more information about the types of data in ClickHouse, see Data types.

Returns an array identical to arr but with an additional value x at the end of the array Array(T)

arrayPushFront(arr, x)

• arr — The array for which to add value x to the end of. Array(T). - x —
• Single value to add to the start of the array. Array(T).

note - Only numbers can be added to an array with numbers, and only strings can be added to an array of strings. - When adding numbers, ClickHouse automatically sets the type of x for the data type of the array. - Can be NULL. The function adds a NULL element to an array, and the type of array elements converts to Nullable.

For more information about the types of data in ClickHouse, see Data types.

Returns an array identical to arr but with an additional value x at the beginning of the array Array(T)

arrayROCAUC(scores, labels[, scale[, partial_offsets]])

• scores — Scores prediction model gives. Array((U)Int*) or Array(Float*)
• labels — Labels of samples, usually 1 for positive sample and 0 for negative sample. Array((U)Int*) or Enum
• scale — Optional. Decides whether to return the normalized area. If false, returns the area under the TP (true positives) x FP (false positives) curve instead. Default value: true. Bool
• partial_offsets —
• An array of four non-negative integers for calculating a partial area under the ROC curve (equivalent to a vertical band of the ROC space) instead of the whole AUC. This option is useful for distributed computation of the ROC AUC. The array must contain the following elements [higher_partitions_tp, higher_partitions_fp, total_positives, total_negatives]. Array of non-negative Integers. Optional.
  • higher_partitions_tp: The number of positive labels in the higher-scored partitions.
  • higher_partitions_fp: The number of negative labels in the higher-scored partitions.
  • total_positives: The total number of positive samples in the entire dataset.
  • total_negatives: The total number of negative samples in the entire dataset.

note When arr_partial_offsets is used, the arr_scores and arr_labels should be only a partition of the entire dataset, containing an interval of scores. The dataset should be divided into contiguous partitions, where each partition contains the subset of the data whose scores fall within a specific range. For example: - One partition could contain all scores in the range [0, 0.5). - Another partition could contain scores in the range [0.5, 1.0].

Returns area under the receiver operating characteristic (ROC) curve. Float64

arrayRandomSample(arr, samples)

• arr — The input array or multidimensional array from which to sample elements. Array(T)
• samples — The number of elements to include in the random sample. (U)Int*

An array containing a random sample of elements from the input array Array(T)

arrayReduce(agg_f, arr1 [, arr2, ... , arrN)])

• agg_f — The name of an aggregate function which should be a constant. String
• arr1 [, arr2, ... , arrN)] — N arrays corresponding to the arguments of agg_f. Array(T)

Returns the result of the aggregate function

arrayReduceInRanges(agg_f, ranges, arr1 [, arr2, ... ,arrN)])

• agg_f — The name of the aggregate function to use. String
• ranges — The range over which to aggregate. An array of tuples, (i, r) containing the index i from which to begin from and the range r over which to aggregate. Array(T) or Tuple(T)
• arr1 [, arr2, ... ,arrN)] — N arrays as arguments to the aggregate function. Array(T)

Returns an array containing results of the aggregate function over the specified ranges Array(T)

arrayRemove(arr, elem)

• arr — Array(T) - elem — T Returns a subset of the source array Array(T)

arrayResize(arr, size[, extender])

• arr — Array to resize. Array(T)
• size — -The new length of the array. If size is less than the original size of the array, the array is truncated from the right. If size is larger than the initial size of the array, the array is extended to the right with extender values or default values for the data type of the array items.
• extender — Value to use for extending the array. Can be NULL. An array of length size. Array(T)

arrayReverse(arr)

• arr — The array to reverse. Array(T)

Returns an array of the same size as the original array containing the elements in reverse order Array(T)

arrayReverseFill(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns an array with elements of the source array replaced by the results of the lambda. Array(T)

arrayReverseSort([f,] arr [, arr1, ... ,arrN)

• f(y1[, y2 ... yN]) — The lambda function to apply to elements of array x. - arr — An array to be sorted. Array(T) - arr1, ..., yN — Optional. N additional arrays, in the case when f accepts multiple arguments. Returns the array x sorted in descending order if no lambda function is provided, otherwise it returns an array sorted according to the logic of the provided lambda function, and then reversed. Array(T).

arrayReverseSplit(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Lambda function
• [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns an array of arrays. Array(Array(T))

arrayRotateLeft(arr, n)

• arr — The array for which to rotate the elements.Array(T). - n — Number of elements to rotate. (U)Int8/16/32/64. An array rotated to the left by the specified number of elements Array(T)

arrayRotateRight(arr, n)

• arr — The array for which to rotate the elements.Array(T). - n — Number of elements to rotate. (U)Int8/16/32/64. An array rotated to the right by the specified number of elements Array(T)

arrayShiftLeft(arr, n[, default])

• arr — The array for which to shift the elements.Array(T). - n — Number of elements to shift.(U)Int8/16/32/64. - default — Optional. Default value for new elements. An array shifted to the left by the specified number of elements Array(T)

arrayShiftRight(arr, n[, default])

• arr — The array for which to shift the elements. Array(T)
• n — Number of elements to shift. (U)Int8/16/32/64
• default — Optional. Default value for new elements. An array shifted to the right by the specified number of elements Array(T)

arrayShuffle(arr [, seed])

• arr — The array to shuffle. Array(T)
• seed (optional) — Optional. The seed to be used with random number generation. If not provided a random one is used. (U)Int*

Array with elements shuffled Array(T)

arraySimilarity(from, to, from_weights, to_weights)

• from — first array Array(T)
• to — second array Array(T)
• from_weights — weights for the first array. Array((U)Int*|Float*)
• to_weights — weights for the second array. Array((U)Int*|Float*)

Returns the similarity between 0 and 1 of the two arrays based on the weighted Levenshtein distance Float64

arraySlice(arr, offset [, length])

• arr — Array to slice. Array(T)
• offset — Indent from the edge of the array. A positive value indicates an offset on the left, and a negative value is an indent on the right. Numbering of the array items begins with 1. (U)Int*
• length — The length of the required slice. If you specify a negative value, the function returns an open slice [offset, array_length - length]. If you omit the value, the function returns the slice [offset, the_end_of_array]. (U)Int*

Returns a slice of the array with length elements from the specified offset Array(T)

arraySort([f,] arr [, arr1, ... ,arrN])

• f(y1[, y2 ... yN]) — The lambda function to apply to elements of array x. - arr — An array to be sorted. Array(T) - arr1, ..., yN — Optional. N additional arrays, in the case when f accepts multiple arguments. Returns the array arr sorted in ascending order if no lambda function is provided, otherwise it returns an array sorted according to the logic of the provided lambda function. Array(T).

arraySplit(func(x[, y1, ..., yN]), source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — A lambda function which operates on elements of the source array (x) and condition arrays (y).Lambda function. - source_arr — The source array to split Array(T). - [, cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T). Returns an array of arrays Array(Array(T))

arrayStringConcat(arr[, separator])

• arr — The array to concatenate. Array(T)
• separator — Optional. Separator string. By default an empty string. const String

Returns the concatenated string. String

arraySum([func(x[, y1, ..., yN])], source_arr[, cond1_arr, ... , condN_arr])

• func(x[, y1, ..., yN]) — Optional. A lambda function which operates on elements of the source array (x) and condition arrays (y). Lambda function
• source_arr — The source array to process. Array(T)
• , cond1_arr, ... , condN_arr] — Optional. N condition arrays providing additional arguments to the lambda function. Array(T)

Returns the sum of elements in the source array, or the sum of elements of the lambda results if provided.

arraySymmetricDifference(arr1, arr2, ... , arrN)

• arrN — N arrays from which to make the new array. Array(T). Returns an array of distinct elements not present in all source arrays Array(T)

arrayUnion(arr1, arr2, ..., arrN)

• arrN — N arrays from which to make the new array. Array(T)

Returns an array with distinct elements from the source arrays Array(T)

arrayUniq(arr1[, arr2, ..., arrN])

• arr1 — Array for which to count the number of unique elements. Array(T)
• [, arr2, ..., arrN] — Optional. Additional arrays used to count the number of unique tuples of elements at corresponding positions in multiple arrays. Array(T)

For a single argument returns the number of unique elements. For multiple arguments returns the number of unique tuples made from elements at corresponding positions across the arrays. UInt32

arrayWithConstant(N, x)

• length — Number of elements in the array. (U)Int*
• x — The value of the N elements in the array, of any type. Returns an Array with N elements of value x. Array(T)

arrayZip(arr1, arr2, ... , arrN)

• arr1, arr2, ... , arrN — N arrays to combine into a single array. Array(T)

Returns an array with elements from the source arrays grouped in tuples. Data types in the tuple are the same as types of the input arrays and in the same order as arrays are passed Array(T)

arrayZipUnaligned(arr1, arr2, ..., arrN)

• arr1, arr2, ..., arrN — N arrays to combine into a single array. Array(T)

Returns an array with elements from the source arrays grouped in tuples. Data types in the tuple are the same as types of the input arrays and in the same order as arrays are passed. Array(T) or Tuple(T1, T2, ...)

ascii(s)

• s — String input. String

Returns the ASCII code point of the first character. If s is empty, the result is 0. If the first character is not an ASCII character or not part of the Latin-1 supplement range of UTF-16, the result is undefined. Int32

asin(x)

• x — Argument for which to calculate arcsine of. (U)Int* or Float* or Decimal

Returns the arcsine value of the provided argument x Float64

asinh(x)

• x — Hyperbolic sine of angle. Values from the interval: -∞ < x < +∞. (U)Int* or Float* or Decimal*

Returns the angle, in radians. Values from the interval: -∞ < asinh(x) < +∞. Float64

assumeNotNull(x)

• x — The original value of any nullable type. Nullable(T)

Returns the non-nullable value, if the original value was not NULL, otherwise an arbitrary value, if the input value is NULL. Any

atan(x)

• x — The value for which to find arc tangent of. (U)Int* or Float* or Decimal*

Returns the arc tangent of x. Float*

atan2(y, x)

• y — y-coordinate of the point through which the ray passes. (U)Int* or Float* or Decimal*
• x — x-coordinate of the point through which the ray passes. (U)Int* or Float* or Decimal*

Returns the angle θ such that -π < θ ≤ π, in radians Float64

atanh(x)

• x — Hyperbolic tangent of angle. Values from the interval: -1 < x < 1. (U)Int*, Float* or Decimal*. (U)Int* or Float* or Decimal*

Returns the angle, in radians. Values from the interval: -∞ < atanh(x) < +∞ Float64

authenticatedUser()

The name of the authenticated user. String

avg(x)

• x — Input values. (U)Int* or Float* or Decimal

Returns the arithmetic mean, otherwise returns NaN if the input parameter x is empty. Float64

avg2(x1, x2])

• x1, x2] — Accepts two values for averaging. Returns the average value of the provided arguments, promoted to the largest compatible type.

avgWeighted(x, weight)

• x — Values. (U)Int* or Float*
• weight — Weights of the values. (U)Int* or Float*

Returns NaN if all the weights are equal to 0 or the supplied weights parameter is empty, or the weighted mean otherwise. Float64

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

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

Returns a unicode-art bar string. String

base32Decode(encoded)

• encoded — String column or constant. String

Returns a string containing the decoded value of the argument. String

base32Encode(plaintext)

• plaintext — Plaintext to encode. String

Returns a string containing the encoded value of the argument. String or FixedString

base58Decode(encoded)

• encoded — String column or constant to decode. String

Returns a string containing the decoded value of the argument. String

base58Encode(plaintext)

• plaintext — Plaintext to encode. String

Returns a string containing the encoded value of the argument. String

base64Decode(encoded)

• encoded — String column or constant to decode. If the string is not valid Base64-encoded, an exception is thrown. String

Returns the decoded string. String

base64Encode(plaintext)

• plaintext — Plaintext column or constant to decode. String

Returns a string containing the encoded value of the argument. String

base64URLDecode(encoded)

• encoded — String column or constant to encode. If the string is not valid Base64-encoded, an exception is thrown. String

Returns a string containing the decoded value of the argument. String

base64URLEncode(plaintext)

• plaintext — Plaintext column or constant to encode. String

Returns a string containing the encoded value of the argument. String

basename(expr)

• expr — A string expression. Backslashes must be escaped. String

Returns the tail of the input string after its last slash or backslash. If the input string ends with a slash or backslash, the function returns an empty string. Returns the original string if there are no slashes or backslashes. String

bech32Decode(address)

• address — A Bech32 string to decode. String or FixedString

Returns a tuple consisting of (hrp, data) that was used to encode the string. The data is in binary format. Tuple(String, String)

bech32Encode(hrp, data[, witver])

• hrp — A String of 1 - 83 lowercase characters specifying the "human-readable part" of the code. Usually 'bc' or 'tb'. String or FixedString
• data — A String of binary data to encode. String or FixedString
• witver — Optional. The witness version (default = 1). An UInt* specifying the version of the algorithm to run. 0 for Bech32 and 1 or greater for Bech32m. UInt*

Returns a Bech32 address string, consisting of the human-readable part, a separator character which is always '1', and a data part. The length of the string will never exceed 90 characters. If the algorithm cannot generate a valid address from the input, it will return an empty string. String

bin(arg)

• arg — A value to convert to binary. String or FixedString or (U)Int* or Float* or Decimal or Date or DateTime

Returns a string with the binary representation of the argument. String

bitAnd(a, b)

• a — First value. (U)Int* or Float*
• b — Second value. (U)Int* or Float*

Returns the result of bitwise operation a AND b

bitCount(x)

• x — An integer or float value. (U)Int* or Float*

Returns the number of bits set to one in x. UInt8.

note The function does not convert the input value to a larger type (sign extension). For example: bitCount(toUInt8(-1)) = 8.

bitHammingDistance(x, y)

• x — First number for Hamming distance calculation. (U)Int* or Float*
• y — Second number for Hamming distance calculation. (U)Int* or Float*

Returns the hamming distance between x and y UInt8

bitNot(a)

• a — Value for which to apply bitwise NOT operation. (U)Int* or Float* or String

Returns the result of ~a i.e a with bits flipped.

bitOr(a, b)

• a — First value. (U)Int* or Float*
• b — Second value. (U)Int* or Float*

Returns the result of bitwise operation a OR b

bitPositionsToArray(arg)

• arg — An integer value. (U)Int*

Returns an array with the ascendingly ordered positions of 1 bits in the binary representation of the input. Array(UInt64)

bitRotateLeft(a, N)

• a — A value to rotate. (U)Int8/16/32/64
• N — The number of positions to rotate left. UInt8/16/32/64

Returns the rotated value with type equal to that of a. (U)Int8/16/32/64

bitRotateRight(a, N)

• a — A value to rotate. (U)Int8/16/32/64
• N — The number of positions to rotate right. UInt8/16/32/64

Returns the rotated value with type equal to that of a. (U)Int8/16/32/64

bitShiftLeft(a, N)

• a — A value to shift. (U)Int* or String or FixedString
• N — The number of positions to shift. UInt8/16/32/64

Returns the shifted value with type equal to that of a.

bitShiftRight(a, N)

• a — A value to shift. (U)Int* or String or FixedString
• N — The number of positions to shift. UInt8/16/32/64

Returns the shifted value with type equal to that of a.

bitSlice(s, offset[, length])

• s — The String or Fixed String to slice. String or FixedString
• offset — Returns the starting bit position (1-based indexing).
• Positive values: count from the beginning of the string.
• Negative values: count from the end of the string.

   [`(U)Int8/16/32/64`](https://clickhouse.com/docs/sql-reference/data-types/int-uint) or [`Float*`](https://clickhouse.com/docs/sql-reference/data-types/float)
  

  • length — Optional. The number of bits to extract.
  • Positive values: extract length bits.
  • Negative values: extract from the offset to (string_length - |length|).
  • Omitted: extract from offset to end of string.
  • If length is not a multiple of 8, the result is padded with zeros on the right. (U)Int8/16/32/64 or Float*

Returns a string containing the extracted bits, represented as a binary sequence. The result is always padded to byte boundaries (multiples of 8 bits) String

bitTest(a, i)

• a — Number to convert. (U)Int8/16/32/64 or Float*
• i — Position of the bit to return. (U)Int8/16/32/64 or Float*

Returns the value of the bit at position i in the binary representation of a UInt8

bitTestAll(a, index1[, index2, ... , indexN])

• a — An integer value. (U)Int8/16/32/64
• index1, ... — One or multiple positions of bits. (U)Int8/16/32/64

Returns the result of the logical conjunction UInt8

bitTestAny(a, index1[, index2, ... , indexN])

• a — An integer value. (U)Int8/16/32/64
• index1, ... — One or multiple positions of bits. (U)Int8/16/32/64

Returns the result of the logical disjunction UInt8

bitXor(a, b)

• a — First value. (U)Int* or Float*
• b — Second value. (U)Int* or Float*

Returns the result of bitwise operation a XOR b

bitmapAnd(bitmap1, bitmap2)

• bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T). Returns a bitmap containing bits present in both input bitmaps AggregateFunction(groupBitmap, T)

bitmapAndCardinality(bitmap1, bitmap2)

• bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T). Returns the number of set bits in the intersection of the two bitmaps UInt64

bitmapAndnot(bitmap1, bitmap2)

• bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T). Returns a bitmap containing set bits present in the first bitmap but not in the second AggregateFunction(groupBitmap, T)

bitmapAndnotCardinality(bitmap1, bitmap2)

• bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T). Returns the number of set bits in the result of bitmap1 AND-NOT bitmap2 UInt64

bitmapBuild(array)

• array — Unsigned integer array. Array(UInt*)

Returns a bitmap from the provided array AggregateFunction(groupBitmap, T)

bitmapCardinality(bitmap)

• bitmap — Bitmap object. AggregateFunction(groupBitmap, T). Returns the number of bits set in the bitmap UInt64

bitmapContains(bitmap, value)

• bitmap — Bitmap object. AggregateFunction(groupBitmap, T). - value — Element to check for. (U)Int8/16/32/64 Returns 1 if the bitmap contains the specified value, otherwise 0 UInt8

bitmapHasAll(bitmap1, bitmap2)

• bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T). Returns 1 if all set bits of the second bitmap are present in the first bitmap, otherwise 0 UInt8

bitmapHasAny(bitmap1, bitmap2)

• bitmap1 — First bitmap object. AggregateFunction(groupBitmap, T). - bitmap2 — Second bitmap object. AggregateFunction(groupBitmap, T). Returns 1 if any bits of the second bitmap are present in the first bitmap, otherwise 0 UInt8