ClickHouse SQL Macros for DuckDB
Installing and Loading
INSTALL chsql FROM community;
LOAD chsql;
Example
-- Use boring ClickHouse SQL function macros in DuckDB SQL queries. Examples:
SELECT toString('world') AS hello, toInt8OrZero('world') AS zero;
βββββββββββ¬ββββββββ
β hello β zero β
β varchar β int64 β
βββββββββββΌββββββββ€
β world β 0 β
βββββββββββ΄ββββββββ
SELECT IPv4NumToString(167772161), IPv4StringToNum('10.0.0.1');
ββββββββββββββββββββββββββββββ¬ββββββββββββββββββββββββββββββ
β ipv4numtostring(167772161) β ipv4stringtonum('10.0.0.1') β
β varchar β int32 β
ββββββββββββββββββββββββββββββΌββββββββββββββββββββββββββββββ€
β 10.0.0.1 β 167772161 β
ββββββββββββββββββββββββββββββ΄ββββββββββββββββββββββββββββββ
-- Query a remote ClickHouse instance via HTTP/S API using multiple formats
SELECT * FROM ch_scan("SELECT number * 100 FROM numbers(3)","https://play.clickhouse.com", format := 'Parquet');
βββββββββββββββββββββββββ
β multiply(number, 100) β
β varchar β
βββββββββββββββββββββββββ€
β 0 β
β 100 β
β 200 β
βββββββββββββββββββββββββ
About chsql
This extension implements a growing number of ClickHouse SQL Macros and functions for DuckDB.
Added Functions
| function_name | function_type | description | comment | example |
|---|---|---|---|---|
| IPv4NumToString | macro | Cast IPv4 address from numeric to string format | Β | SELECT IPv4NumToString(2130706433); |
| IPv4StringToNum | macro | Cast IPv4 address from string to numeric format | Β | SELECT IPv4StringToNum('127.0.0.1'); |
| arrayExists | macro | Check if any element of the array satisfies the condition | Β | SELECT arrayExists(x -> x = 1, [1, 2, 3]); |
| arrayMap | macro | Applies a function to each element of an array | Β | SELECT arrayMap(x -> x + 1, [1, 2, 3]); |
| bitCount | macro | Counts the number of set bits in an integer | Β | SELECT bitCount(15); |
| domain | macro | Extracts the domain from a URL | Β | SELECT domain('https://clickhouse.com/docs'); |
| extractAllGroups | macro | Extracts all matching groups from a string using a regular expression | Β | SELECT extractAllGroups('(\d+)', 'abc123'); |
| formatDateTime | macro | Formats a DateTime value into a string | Β | SELECT formatDateTime(now(), '%Y-%m-%d'); |
| generateUUIDv4 | macro | Generate a UUID v4 value | Β | SELECT generateUUIDv4(); |
| ifNull | macro | Returns the first argument if not NULL, otherwise the second | Β | SELECT ifNull(NULL, 'default'); |
| intDiv | macro | Performs integer division | Β | SELECT intDiv(10, 3); |
| intDivOZero | macro | Performs integer division but returns zero instead of throwing an error for division by zero | Β | SELECT intDivOZero(10, 0); |
| intDivOrNull | macro | Performs integer division but returns NULL instead of throwing an error for division by zero | Β | SELECT intDivOrNull(10, 0); |
| leftPad | macro | Pads a string on the left to a specified length | Β | SELECT leftPad('abc', 5, '*'); |
| lengthUTF8 | macro | Returns the length of a string in UTF-8 characters | Β | SELECT lengthUTF8('ΠΡΠΈΠ²Π΅Ρ'); |
| match | macro | Performs a regular expression match on a string | Β | SELECT match('abc123', '\d+'); |
| minus | macro | Performs subtraction of two numbers | Β | SELECT minus(5, 3); |
| modulo | macro | Calculates the remainder of division (modulus) | Β | SELECT modulo(10, 3); |
| moduloOrZero | macro | Calculates modulus but returns zero instead of error on division by zero | Β | SELECT moduloOrZero(10, 0); |
| notEmpty | macro | Check if a string is not empty | Β | SELECT notEmpty('abc'); |
| numbers | table_macro | Generates a sequence of numbers starting from 0 | Returns a table with a single column (UInt64) | SELECT * FROM numbers(10); |
| parseURL | macro | Extracts parts of a URL | Β | SELECT parseURL('https://clickhouse.com', 'host'); |
| path | macro | Extracts the path from a URL | Β | SELECT path('https://clickhouse.com/docs'); |
| plus | macro | Performs addition of two numbers | Β | SELECT plus(5, 3); |
| protocol | macro | Extracts the protocol from a URL | Β | SELECT protocol('https://clickhouse.com'); |
| read_parquet_mergetree | table | Merge parquet files using a primary sorting key for fast range queries | experimental | COPY (SELECT * FROM read_parquet_mergetree(['/folder/*.parquet'], 'sortkey') TO 'sorted.parquet'; |
| rightPad | macro | Pads a string on the right to a specified length | Β | SELECT rightPad('abc', 5, '*'); |
| splitByChar | macro | Splits a string by a given character | Β | SELECT splitByChar(',', 'a,b,c'); |
| toDayOfMonth | macro | Extracts the day of the month from a date | Β | SELECT toDayOfMonth('2023-09-10'); |
| toFixedString | macro | Converts a value to a fixed-length string | Β | SELECT toFixedString('abc', 5); |
| toFloatOrNull | macro | Converts a value to float or returns NULL if the conversion fails | Β | SELECT toFloatOrNull('abc'); |
| toFloatOrZero | macro | Converts a value to float or returns zero if the conversion fails | Β | SELECT toFloatOrZero('abc'); |
| toHour | macro | Extracts the hour from a DateTime value | Β | SELECT toHour(now()); |
| toInt128 | macro | Converts a value to a 128-bit integer | Β | SELECT toInt128('123456789012345678901234567890'); |
| toInt16 | macro | Converts a value to a 16-bit integer | Β | SELECT toInt16('123'); |
| toInt16OrNull | macro | Converts to a 16-bit integer or returns NULL on failure | Β | SELECT toInt16OrNull('abc'); |
| toInt16OrZero | macro | Converts to a 16-bit integer or returns zero on failure | Β | SELECT toInt16OrZero('abc'); |
| toInt256 | macro | Converts a value to a 256-bit integer | Β | SELECT toInt256('12345678901234567890123456789012345678901234567890123456789012345678901234567890'); |
| toInt256OrNull | macro | Converts to a 256-bit integer or returns NULL on failure | Β | SELECT toInt256OrNull('abc'); |
| toInt256OrZero | macro | Converts to a 256-bit integer or returns zero on failure | Β | SELECT toInt256OrZero('abc'); |
| toInt32 | macro | Converts a value to a 32-bit integer | Β | SELECT toInt32('123'); |
| toInt32OrNull | macro | Converts to a 32-bit integer or returns NULL on failure | Β | SELECT toInt32OrNull('abc'); |
| toInt32OrZero | macro | Converts to a 32-bit integer or returns zero on failure | Β | SELECT toInt32OrZero('abc'); |
| toInt64 | macro | Converts a value to a 64-bit integer | Β | SELECT toInt64('123'); |
| toInt64OrNull | macro | Converts to a 64-bit integer or returns NULL on failure | Β | SELECT toInt64OrNull('abc'); |
| toInt64OrZero | macro | Converts to a 64-bit integer or returns zero on failure | Β | SELECT toInt64OrZero('abc'); |
| toInt8 | macro | Converts a value to an 8-bit integer | Β | SELECT toInt8('123'); |
| toInt8OrNull | macro | Converts to an 8-bit integer or returns NULL on failure | Β | SELECT toInt8OrNull('abc'); |
| toInt8OrZero | macro | Converts to an 8-bit integer or returns zero on failure | Β | SELECT toInt8OrZero('abc'); |
| toMinute | macro | Extracts the minute from a DateTime value | Β | SELECT toMinute(now()); |
| toMonth | macro | Extracts the month from a Date value | Β | SELECT toMonth('2023-09-10'); |
| toSecond | macro | Extracts the second from a DateTime value | Β | SELECT toSecond(now()); |
| toString | macro | Converts a value to a string | Β | SELECT toString(123); |
| toUInt16 | macro | Converts a value to an unsigned 16-bit integer | Β | SELECT toUInt16('123'); |
| toUInt16OrZero | macro | Converts to an unsigned 16-bit integer or returns zero on failure | Β | SELECT toUInt16OrZero('abc'); |
| toUInt32 | macro | Converts a value to an unsigned 32-bit integer | Β | SELECT toUInt32('123'); |
| toUInt32OrNull | macro | Converts to an unsigned 32-bit integer or returns NULL on failure | Β | SELECT toUInt32OrNull('abc'); |
| toUInt32OrZero | macro | Converts to an unsigned 32-bit integer or returns zero on failure | Β | SELECT toUInt32OrZero('abc'); |
| toUInt64 | macro | Converts a value to an unsigned 64-bit integer | Β | SELECT toUInt64('123'); |
| toUInt64OrNull | macro | Converts to an unsigned 64-bit integer or returns NULL on failure | Β | SELECT toUInt64OrNull('abc'); |
| toUInt64OrZero | macro | Converts to an unsigned 64-bit integer or returns zero on failure | Β | SELECT toUInt64OrZero('abc'); |
| toUInt8OrNull | macro | Converts to an unsigned 8-bit integer or returns NULL on failure | Β | SELECT toUInt8OrNull('abc'); |
| toUInt8OrZero | macro | Converts to an unsigned 8-bit integer or returns zero on failure | Β | SELECT toUInt8OrZero('abc'); |
| toYYYYMM | macro | Formats a Date to 'YYYYMM' string format | Β | SELECT toYYYYMM('2023-09-10'); |
| toYYYYMMDD | macro | Formats a Date to 'YYYYMMDD' string format | Β | SELECT toYYYYMMDD('2023-09-10'); |
| toYYYYMMDDhhmmss | macro | Formats a DateTime to 'YYYYMMDDhhmmss' string format | Β | SELECT toYYYYMMDDhhmmss(now()); |
| toYear | macro | Extracts the year from a Date or DateTime value | Β | SELECT toYear('2023-09-10'); |
| topLevelDomain | macro | Extracts the top-level domain (TLD) from a URL | Β | SELECT topLevelDomain('https://example.com'); |
| tupleConcat | macro | Concatenates two tuples into one tuple | Β | SELECT tupleConcat((1, 'a'), (2, 'b')); |
| tupleDivide | macro | Performs element-wise division between two tuples | Β | SELECT tupleDivide((10, 20), (2, 5)); |
| tupleDivideByNumber | macro | Divides each element of a tuple by a number | Β | SELECT tupleDivideByNumber((10, 20), 2); |
| tupleIntDiv | macro | Performs element-wise integer division between two tuples | Β | SELECT tupleIntDiv((10, 20), (3, 4)); |
| tupleIntDivByNumber | macro | Performs integer division of each element of a tuple by a number | Β | SELECT tupleIntDivByNumber((10, 20), 3); |
| tupleMinus | macro | Performs element-wise subtraction between two tuples | Β | SELECT tupleMinus((10, 20), (5, 3)); |
| tupleModulo | macro | Performs element-wise modulus between two tuples | Β | SELECT tupleModulo((10, 20), (3, 6)); |
| tupleModuloByNumber | macro | Calculates the modulus of each element of a tuple by a number | Β | SELECT tupleModuloByNumber((10, 20), 3); |
| tupleMultiply | macro | Performs element-wise multiplication between two tuples | Β | SELECT tupleMultiply((10, 20), (2, 5)); |
| tupleMultiplyByNumber | macro | Multiplies each element of a tuple by a number | Β | SELECT tupleMultiplyByNumber((10, 20), 3); |
| tuplePlus | macro | Performs element-wise addition between two tuples | Β | SELECT tuplePlus((1, 2), (3, 4)); |
| url | table_macro | Performs queries against remote URLs using the specified format | Supports JSON, CSV, PARQUET, TEXT, BLOB | SELECT * FROM url('https://urleng.com/test','JSON'); |
| JSONExtract | macro | Extracts JSON data based on key from a JSON object | Β | SELECT JSONExtract(json_column, 'user.name'); |
| JSONExtractUInt | macro | Extracts JSON data as an unsigned integer from a JSON object | Β | SELECT JSONExtractUInt(json_column, 'user.age'); |
| JSONExtractInt | macro | Extracts JSON data as a 32-bit integer from a JSON object | Β | SELECT JSONExtractInt(json_column, 'user.balance'); |
| JSONExtractFloat | macro | Extracts JSON data as a double from a JSON object | Β | SELECT JSONExtractFloat(json_column, 'user.score'); |
| JSONExtractRaw | macro | Extracts raw JSON data based on key from a JSON object | Β | SELECT JSONExtractRaw(json_column, 'user.address'); |
| JSONHas | macro | Checks if a JSON key exists and is not null | Β | SELECT JSONHas(json_column, 'user.active'); |
| JSONLength | macro | Returns the length of a JSON array | Β | SELECT JSONLength(json_column, 'items'); |
| JSONType | macro | Determines the type of JSON element at the given path | Β | SELECT JSONType(json_column, 'user.data'); |
| JSONExtractKeys | macro | Extracts keys from a JSON object | Β | SELECT JSONExtractKeys(json_column); |
| JSONExtractValues | macro | Extracts all values as text from a JSON object | Β | SELECT JSONExtractValues(json_column); |
| equals | macro | Checks if two values are equal | Β | SELECT equals(column_a, column_b); |
| notEquals | macro | Checks if two values are not equal | Β | SELECT notEquals(column_a, column_b); |
| less | macro | Checks if one value is less than another | Β | SELECT less(column_a, column_b); |
| greater | macro | Checks if one value is greater than another | Β | SELECT greater(column_a, column_b); |
| lessOrEquals | macro | Checks if one value is less than or equal to another | Β | SELECT lessOrEquals(column_a, column_b); |
| greaterOrEquals | macro | Checks if one value is greater than or equal to another | Β | SELECT greaterOrEquals(column_a, column_b); |
| dictGet | macro | Retrieves an attribute from a VARIABLE string or MAP | Β | SELECT dictGet('dictionary_name', 'attribute'); |
| arrayJoin | macro | Unroll an array into multiple rows | Β | SELECT arrayJoin([1, 2, 3]); |
| ch_scan | table_macro | Query a remote ClickHouse server using HTTP/s API | Returns the query results | SELECT * FROM ch_scan('SELECT version()','https://play.clickhouse.com', format := 'parquet'); |
| empty | macro | Check if a string is empty | Β | SELECT empty(''); |
| toFloat | macro | Converts a value to a float | Β | SELECT toFloat('123.45'); |
| toInt128OrNull | macro | Converts to a 128-bit integer or returns NULL on failure | Β | SELECT toInt128OrNull('abc'); |
| toInt128OrZero | macro | Converts to a 128-bit integer or returns zero on failure | Β | SELECT toInt128OrZero('abc'); |
| toUInt16OrNull | macro | Converts to an unsigned 16-bit integer or returns NULL on failure | Β | SELECT toUInt16OrNull('abc'); |
| toUInt8 | macro | Converts a value to an unsigned 8-bit integer | Β | SELECT toUInt8('123'); |
| JSONExtractString | macro | Extracts JSON data as a VARCHAR from a JSON object | Β | SELECT JSONExtractString(json_column, 'user.email'); |
| chsql_openssl_version | scalar | Β | Β | Β |
| chsql | scalar | Β | Β | Β |