- Query formatting (e.g.,
formatQuery
,formatQuerySingleLine
) - File path manipulation (
basename
) - Version information (
version()
) - Block and row numbering (
blockNumber()
,rowNumberInBlock()
) - Size formatting (
formatReadableSize
,formatReadableDecimalSize
) - Mathematical and type-checking operations (
least
,greatest
,isFinite
,isConstant
)
ClickHouse function reference
basename
Extracts the filename from a path. Syntax:path
(String
): A string containing a file path.
- The filename extracted from the path. [
String
]
- If the path ends with a slash or backslash, it returns an empty string.
- If there are no slashes or backslashes, it returns the original string.
- Backslashes must be escaped in string literals.
When using Windows-style paths, remember to escape backslashes:
visibleWidth
Calculates the approximate width when outputting values to the console in text format (tab-separated). Syntax:x
(any type): The value to calculate the width for.
- The approximate width of the value when displayed in the console. [
UInt64
]
- This function is used by the system to implement [Pretty formats].
- For NULL values, it returns the width of the string ‘NULL’ in Pretty formats.
- ‘Crunchy Taco’ has a visible width of 12 characters.
- The number 42 has a visible width of 2 characters.
- NULL has a visible width of 4 characters (the length of ‘NULL’ in Pretty formats).
toTypeName
Returns the name of the data type of the passed argument. Syntaxx
(any type): Value of any type.
- A string containing the name of the type.
42
is identified asUInt8
- The string ‘Carne asada’ is identified as
String
- The
now()
function returns aDateTime
type
The function returns the type name exactly as it appears in the ClickHouse source code. Therefore, for some data types, it may return a name that differs from the one used in SQL syntax.For
NULL
values, the function returns the type Nullable(Nothing)
, which corresponds to ClickHouse’s internal NULL representation.blockSize
Returns the number of rows in the current data block. Syntax:UInt64
]
Example:
blockSize()
returns the size of each processed block. The last block contains only one row because there are only 10 numbers in total.
The
blockSize()
function is primarily used for debugging and testing purposes. In production queries, the block size can vary and should not be relied upon for business logic.byteSize
Returns an estimation of the uncompressed byte size of its arguments in memory. Syntax:argument
(any type): Value of any type.
- Estimation of byte size of the arguments in memory. (
UInt64
)
String
) arguments, the function returns the string length + 9 (terminating zero + length).
currentDatabase
Returns the name of the current database. Syntax:- The name of the current database. (
String
)
isConstant
Checks whether the argument is a constant expression. Syntax:x
: Expression to check.
- 1 if
x
is constant. (UInt8
) - 0 if
x
is non-constant. (UInt8
)
isConstant
returns 0 because number
is not a constant expression.
isConstant
returns 1 because 42 is a constant value.
isConstant
returns 1 because cos(pi)
is a constant expression, as pi
is defined as a constant in the WITH clause.
Note that
isConstant
evaluates the constancy of an expression during query analysis, before actual execution. This makes it useful for optimizing queries and understanding how ClickHouse processes different types of expressions.isFinite
Checks whether a floating-point value is finite. Syntax:x
(Float32
orFloat64
): The value to check.
1
if x
is finite, 0
otherwise. Note that 0
is returned for NaN values. [UInt8
]
Example:
isFinite
checks if each taco_price
is finite. The result 1
indicates a finite price, while 0
indicates an infinite price or NaN.
isInfinite
Checks if a floating-point value is infinite. Syntax:x
(Float32
orFloat64
): The value to check.
1
if x
is infinite, 0
otherwise. Note that
0
is returned for NaN values.UInt8
]
Example:
isInfinite
checks if each taco_price
is infinite. The result 1
indicates an infinite price, while 0
indicates a finite price or NaN.
ifNotFinite
Checks if a value is finite and returns an alternative value if it’s not. Syntax:x
(Float32
orFloat64
): The value to check.y
(Float32
orFloat64
): The value to return ifx
is not finite.
- If
x
is finite, returnsx
. - If
x
is infinite or NaN, returnsy
.
finite
returns 0.0 because it’s a finite number.infinite
returns 42.0 because 1/0 is infinity.nan
returns 42.0 because 0/0 is NaN (Not a Number).
isNaN
Checks whether the argument is NaN (Not a Number). Syntaxx
(Float32
orFloat64
): Value to check.
- 1 if the argument is NaN.
- 0 otherwise.
UInt8
Example
Query:
0/0
results in NaN, soisNaN(0/0)
returns 1.1
and0/1
are not NaN, soisNaN
returns 0 for both.tacos_eaten / total_tacos
(5/0) results in a division by zero, which is NaN, soisNaN
returns 1.
bar
Builds a bar chart. Syntax:x
(numeric): Size to display.min
(Int32
): Minimum value for the bar.max
(Int32
): Maximum value for the bar.width
(UInt8
): Width of the bar in characters.
(x - min)
and equal to width
characters when x = max
. The band is drawn with accuracy to one eighth of a symbol.
Example:
formatReadableDecimalSize
Formats a size in bytes into a human-readable string using decimal (base 10) units. Syntax:bytes
(UInt64
): The number of bytes to format.
String
]
Example:
This function uses decimal (base 10) units, where 1 KB = 1000 bytes. For binary (base 2) units, use the
formatReadableSize
function instead.formatReadableSize
Formats a size in bytes into a human-readable string with appropriate units (B, KiB, MiB, etc.). Syntax:- FORMAT_BYTES
x
(UInt64
): Size in bytes.
String
)
Example:
- This function uses binary prefixes (KiB, MiB, etc.) rather than decimal prefixes (KB, MB, etc.).
x
(numeric): The number to format.
String
)
Example:
The function rounds the number and chooses an appropriate suffix to make it more readable. It’s ideal for presenting statistics or large quantities in user interfaces or reports.
formatReadableTimeDelta
Formats a time interval (delta) in seconds into a human-readable string representation. Syntax:column
(numeric) — A column with a numeric time delta in seconds.maximum_unit
(String
, optional) — Maximum unit to show. Default value: years.- Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years.
minimum_unit
(String
, optional) — Minimum unit to show. All smaller units are truncated.- Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years.
- Default value: seconds if maximum_unit is seconds or bigger, nanoseconds otherwise.
parseTimeDelta
Parses a string representing a time duration and converts it to a number of seconds. Syntax:time_string
(String
) — A string containing a sequence of numbers followed by time unit indicators.
- The total number of seconds represented by the input string. (
Float64
)
- Supported time units include:
ns
(nanoseconds)us
(microseconds)ms
(milliseconds)s
orsec
(seconds)m
ormin
(minutes)h
orhour
(hours)d
orday
(days)w
orweek
(weeks)mo
ormonth
(months, assuming 30 days per month)y
oryear
(years, assuming 365 days per year)
- The function is case-insensitive for unit indicators.
- Multiple duration components can be combined, e.g., ‘1h30m15s’.
- If the input string is invalid or cannot be parsed, the function will throw an exception.
least
Returns the smaller value of a and b. Syntax:a
(any comparable type): First value to compare.b
(any comparable type): Second value to compare. Must be of the same type asa
.
a
and b
. The return type is the same as the input type.
Example:
min_value
returns 3, the smaller of 5 and 3.min_string
returns ‘guacamole’, which comes before ‘salsa’ lexicographically.min_date
returns the earlier date, ‘2023-04-15’.
The
least
function can be particularly useful in queries where you need to find the minimum value across multiple columns or when setting a lower bound for a value.greatest
Returns the larger value of two arguments. Syntax:a
(any comparable type): First value to compare.b
(any comparable type): Second value to compare.
- For numeric values,
greatest
returns 5 as it’s larger than 3. - For strings,
greatest
performs a lexicographical comparison, returning ‘salsa’ as it comes after ‘guacamole’ alphabetically.
The
greatest
function can be used with various data types, including numbers, strings, and dates. When comparing different data types, type conversion rules apply.version
Returns the current version of ClickHouse as a string. Syntax:- Debugging version-specific issues
- Ensuring compatibility with certain features
- Logging or reporting purposes
If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise, it produces a constant value.
blockNumber
Returns a monotonically increasing sequence number of the block containing the row. The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate. Syntax:- Sequence number of the data block where the row is located. [
UInt64
]
blockNumber()
returns the sequence number of each data block processed.
rowNumberInBlock
Returns the row number within the current data block being processed. Syntax:UInt64
]
Example:
rowNumberInBlock()
returns the row number within each block. The numbering restarts at 0 for each new block.
The result of this function depends on the affected data blocks and the order of data in the block. It’s primarily used for debugging and testing purposes.
rowNumberInAllBlocks
Returns a unique row number for each row processed by the function. The returned numbers start at 0. Syntax:- Ordinal number of the row in the data block starting from 0. [
UInt64
]
rowNumberInAllBlocks()
assigns a unique number to each taco order, regardless of the block size. This can be useful for generating unique identifiers or for analyzing the overall order of rows in a query result.
The order of rows during calculation of
rowNumberInAllBlocks()
can differ from the order of rows returned to the user. To ensure a specific order, use an ORDER BY
clause in your query.identity
Returns the input value unchanged. This function is primarily used for debugging and testing purposes. Syntax:x
(any type): A value of any type.
- The input value, unchanged.
- Debugging: When you want to examine the exact value being passed through a complex query without any transformations.
- Testing: To verify that data is being processed correctly without any unintended modifications.
- Query optimization: The
identity
function can be used to cancel index usage, allowing you to test query performance with a full scan.
When analyzing a query for potential index usage, the query analyzer ignores everything within
identity
functions. It also disables constant folding.countDigits
Counts the number of decimal digits needed to represent a value. Syntax:x
(numeric) — An integer or decimal value.
UInt8
.
Example:
regular_int
: 42 requires 2 digits to represent.decimal
: 1.5 is treated as 15, requiring 2 digits.large_int
: 1234567890 requires 10 digits to represent.
For decimal values, the function takes into account their scale. For example:
countDigits(42.000) = 5
countDigits(0.04200) = 4
Decimal64
value might overflow, you can use: countDigits(x) > 18
.formatQuery
Formats a SQL query string, improving its readability. Syntax:query
(String
): The SQL query to be formatted.
- The formatted query. (
String
)
If the input query is not well-formed, the function throws an exception. To return
NULL
instead of an exception for invalid queries, use the formatQueryOrNull()
function.formatQueryOrNull
Returns a formatted, possibly multi-line, version of the given SQL query. If the query is not well-formed, it returns NULL instead of throwing an exception. Syntax:query
(String
): The SQL query to be formatted.
- The formatted query, or NULL if the input query is not well-formed. [
String
]
formatQuerySingleLine
Formats a SQL query as a single line, removing line breaks and extra whitespace. Syntax:query
(String
): The SQL query to be formatted.
String
]
Example:
If the input query is not well-formed, the function will throw an exception. To return NULL instead, use the
formatQuerySingleLineOrNull()
function.formatQuerySingleLineOrNull
Formats a SQL query as a single line, removing unnecessary whitespace. If the input is not a valid query, it returns NULL instead of throwing an exception. Syntax:query
(String
): The SQL query to format.
- A formatted single-line version of the query, or NULL if the input is invalid. [
String
]