| warning | title | excerpt | extension | repo | docs | extension_star_count | extension_star_count_pretty | extension_download_count | extension_download_count_pretty | image | layout | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github/duckdb/community-extensions repository, check README there |
chsql |
DuckDB Community Extensions
ClickHouse SQL Macros for DuckDB
|
|
|
|
57 |
57 |
8795 |
8.8k |
/images/community_extensions/social_preview/preview_community_extension_chsql.png |
community_extension_doc |
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};{% if page.docs.hello_world %}
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions
<div class="extension_functions_table"></div>
| function_name | function_type | description | comment | examples |
|------------------------|---------------|----------------------------------------------------------------------------------------------|-----------------------------------------------|--------------------------------------------------------------------------------------------------------|
| IPv4NumToString | macro | Cast IPv4 address from numeric to string format | NULL | [SELECT IPv4NumToString(2130706433);] |
| IPv4StringToNum | macro | Cast IPv4 address from string to numeric format | NULL | [SELECT IPv4StringToNum('127.0.0.1');] |
| arrayExists | macro | Check if any element of the array satisfies the condition | NULL | [SELECT arrayExists(x -> x = 1, [1, 2, 3]);] |
| arrayMap | macro | Applies a function to each element of an array | NULL | [SELECT arrayMap(x -> x + 1, [1, 2, 3]);] |
| bitCount | macro | Counts the number of set bits in an integer | NULL | [SELECT bitCount(15);] |
| domain | macro | Extracts the domain from a URL | NULL | [SELECT domain('https://clickhouse.com/docs');] |
| extractAllGroups | macro | Extracts all matching groups from a string using a regular expression | NULL | [SELECT extractAllGroups('(\d+)', 'abc123');] |
| formatDateTime | macro | Formats a DateTime value into a string | NULL | [SELECT formatDateTime(now(), '%Y-%m-%d');] |
| generateUUIDv4 | macro | Generate a UUID v4 value | NULL | [SELECT generateUUIDv4();] |
| ifNull | macro | Returns the first argument if not NULL, otherwise the second | NULL | [SELECT ifNull(NULL, 'default');] |
| intDiv | macro | Performs integer division | NULL | [SELECT intDiv(10, 3);] |
| intDivOZero | macro | Performs integer division but returns zero instead of throwing an error for division by zero | NULL | [SELECT intDivOZero(10, 0);] |
| intDivOrNull | macro | Performs integer division but returns NULL instead of throwing an error for division by zero | NULL | [SELECT intDivOrNull(10, 0);] |
| leftPad | macro | Pads a string on the left to a specified length | NULL | [SELECT leftPad('abc', 5, '*');] |
| lengthUTF8 | macro | Returns the length of a string in UTF-8 characters | NULL | [SELECT lengthUTF8('Привет');] |
| match | macro | Performs a regular expression match on a string | NULL | [SELECT match('abc123', '\d+');] |
| minus | macro | Performs subtraction of two numbers | NULL | [SELECT minus(5, 3);] |
| modulo | macro | Calculates the remainder of division (modulus) | NULL | [SELECT modulo(10, 3);] |
| moduloOrZero | macro | Calculates modulus but returns zero instead of error on division by zero | NULL | [SELECT moduloOrZero(10, 0);] |
| notEmpty | macro | Check if a string is not empty | NULL | [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 | NULL | [SELECT parseURL('https://clickhouse.com', 'host');] |
| path | macro | Extracts the path from a URL | NULL | [SELECT path('https://clickhouse.com/docs');] |
| plus | macro | Performs addition of two numbers | NULL | [SELECT plus(5, 3);] |
| protocol | macro | Extracts the protocol from a URL | NULL | [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 | NULL | [SELECT rightPad('abc', 5, '*');] |
| splitByChar | macro | Splits a string by a given character | NULL | [SELECT splitByChar(',', 'a,b,c');] |
| toDayOfMonth | macro | Extracts the day of the month from a date | NULL | [SELECT toDayOfMonth('2023-09-10');] |
| toFixedString | macro | Converts a value to a fixed-length string | NULL | [SELECT toFixedString('abc', 5);] |
| toFloatOrNull | macro | Converts a value to float or returns NULL if the conversion fails | NULL | [SELECT toFloatOrNull('abc');] |
| toFloatOrZero | macro | Converts a value to float or returns zero if the conversion fails | NULL | [SELECT toFloatOrZero('abc');] |
| toHour | macro | Extracts the hour from a DateTime value | NULL | [SELECT toHour(now());] |
| toInt128 | macro | Converts a value to a 128-bit integer | NULL | [SELECT toInt128('123456789012345678901234567890');] |
| toInt16 | macro | Converts a value to a 16-bit integer | NULL | [SELECT toInt16('123');] |
| toInt16OrNull | macro | Converts to a 16-bit integer or returns NULL on failure | NULL | [SELECT toInt16OrNull('abc');] |
| toInt16OrZero | macro | Converts to a 16-bit integer or returns zero on failure | NULL | [SELECT toInt16OrZero('abc');] |
| toInt256 | macro | Converts a value to a 256-bit integer | NULL | [SELECT toInt256('12345678901234567890123456789012345678901234567890123456789012345678901234567890');] |
| toInt256OrNull | macro | Converts to a 256-bit integer or returns NULL on failure | NULL | [SELECT toInt256OrNull('abc');] |
| toInt256OrZero | macro | Converts to a 256-bit integer or returns zero on failure | NULL | [SELECT toInt256OrZero('abc');] |
| toInt32 | macro | Converts a value to a 32-bit integer | NULL | [SELECT toInt32('123');] |
| toInt32OrNull | macro | Converts to a 32-bit integer or returns NULL on failure | NULL | [SELECT toInt32OrNull('abc');] |
| toInt32OrZero | macro | Converts to a 32-bit integer or returns zero on failure | NULL | [SELECT toInt32OrZero('abc');] |
| toInt64 | macro | Converts a value to a 64-bit integer | NULL | [SELECT toInt64('123');] |
| toInt64OrNull | macro | Converts to a 64-bit integer or returns NULL on failure | NULL | [SELECT toInt64OrNull('abc');] |
| toInt64OrZero | macro | Converts to a 64-bit integer or returns zero on failure | NULL | [SELECT toInt64OrZero('abc');] |
| toInt8 | macro | Converts a value to an 8-bit integer | NULL | [SELECT toInt8('123');] |
| toInt8OrNull | macro | Converts to an 8-bit integer or returns NULL on failure | NULL | [SELECT toInt8OrNull('abc');] |
| toInt8OrZero | macro | Converts to an 8-bit integer or returns zero on failure | NULL | [SELECT toInt8OrZero('abc');] |
| toMinute | macro | Extracts the minute from a DateTime value | NULL | [SELECT toMinute(now());] |
| toMonth | macro | Extracts the month from a Date value | NULL | [SELECT toMonth('2023-09-10');] |
| toSecond | macro | Extracts the second from a DateTime value | NULL | [SELECT toSecond(now());] |
| toString | macro | Converts a value to a string | NULL | [SELECT toString(123);] |
| toUInt16 | macro | Converts a value to an unsigned 16-bit integer | NULL | [SELECT toUInt16('123');] |
| toUInt16OrZero | macro | Converts to an unsigned 16-bit integer or returns zero on failure | NULL | [SELECT toUInt16OrZero('abc');] |
| toUInt32 | macro | Converts a value to an unsigned 32-bit integer | NULL | [SELECT toUInt32('123');] |
| toUInt32OrNull | macro | Converts to an unsigned 32-bit integer or returns NULL on failure | NULL | [SELECT toUInt32OrNull('abc');] |
| toUInt32OrZero | macro | Converts to an unsigned 32-bit integer or returns zero on failure | NULL | [SELECT toUInt32OrZero('abc');] |
| toUInt64 | macro | Converts a value to an unsigned 64-bit integer | NULL | [SELECT toUInt64('123');] |
| toUInt64OrNull | macro | Converts to an unsigned 64-bit integer or returns NULL on failure | NULL | [SELECT toUInt64OrNull('abc');] |
| toUInt64OrZero | macro | Converts to an unsigned 64-bit integer or returns zero on failure | NULL | [SELECT toUInt64OrZero('abc');] |
| toUInt8OrNull | macro | Converts to an unsigned 8-bit integer or returns NULL on failure | NULL | [SELECT toUInt8OrNull('abc');] |
| toUInt8OrZero | macro | Converts to an unsigned 8-bit integer or returns zero on failure | NULL | [SELECT toUInt8OrZero('abc');] |
| toYYYYMM | macro | Formats a Date to 'YYYYMM' string format | NULL | [SELECT toYYYYMM('2023-09-10');] |
| toYYYYMMDD | macro | Formats a Date to 'YYYYMMDD' string format | NULL | [SELECT toYYYYMMDD('2023-09-10');] |
| toYYYYMMDDhhmmss | macro | Formats a DateTime to 'YYYYMMDDhhmmss' string format | NULL | [SELECT toYYYYMMDDhhmmss(now());] |
| toYear | macro | Extracts the year from a Date or DateTime value | NULL | [SELECT toYear('2023-09-10');] |
| topLevelDomain | macro | Extracts the top-level domain (TLD) from a URL | NULL | [SELECT topLevelDomain('https://example.com');] |
| tupleConcat | macro | Concatenates two tuples into one tuple | NULL | [SELECT tupleConcat((1, 'a'), (2, 'b'));] |
| tupleDivide | macro | Performs element-wise division between two tuples | NULL | [SELECT tupleDivide((10, 20), (2, 5));] |
| tupleDivideByNumber | macro | Divides each element of a tuple by a number | NULL | [SELECT tupleDivideByNumber((10, 20), 2);] |
| tupleIntDiv | macro | Performs element-wise integer division between two tuples | NULL | [SELECT tupleIntDiv((10, 20), (3, 4));] |
| tupleIntDivByNumber | macro | Performs integer division of each element of a tuple by a number | NULL | [SELECT tupleIntDivByNumber((10, 20), 3);] |
| tupleMinus | macro | Performs element-wise subtraction between two tuples | NULL | [SELECT tupleMinus((10, 20), (5, 3));] |
| tupleModulo | macro | Performs element-wise modulus between two tuples | NULL | [SELECT tupleModulo((10, 20), (3, 6));] |
| tupleModuloByNumber | macro | Calculates the modulus of each element of a tuple by a number | NULL | [SELECT tupleModuloByNumber((10, 20), 3);] |
| tupleMultiply | macro | Performs element-wise multiplication between two tuples | NULL | [SELECT tupleMultiply((10, 20), (2, 5));] |
| tupleMultiplyByNumber | macro | Multiplies each element of a tuple by a number | NULL | [SELECT tupleMultiplyByNumber((10, 20), 3);] |
| tuplePlus | macro | Performs element-wise addition between two tuples | NULL | [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 | NULL | [SELECT JSONExtract(json_column, 'user.name');] |
| JSONExtractUInt | macro | Extracts JSON data as an unsigned integer from a JSON object | NULL | [SELECT JSONExtractUInt(json_column, 'user.age');] |
| JSONExtractInt | macro | Extracts JSON data as a 32-bit integer from a JSON object | NULL | [SELECT JSONExtractInt(json_column, 'user.balance');] |
| JSONExtractFloat | macro | Extracts JSON data as a double from a JSON object | NULL | [SELECT JSONExtractFloat(json_column, 'user.score');] |
| JSONExtractRaw | macro | Extracts raw JSON data based on key from a JSON object | NULL | [SELECT JSONExtractRaw(json_column, 'user.address');] |
| JSONHas | macro | Checks if a JSON key exists and is not null | NULL | [SELECT JSONHas(json_column, 'user.active');] |
| JSONLength | macro | Returns the length of a JSON array | NULL | [SELECT JSONLength(json_column, 'items');] |
| JSONType | macro | Determines the type of JSON element at the given path | NULL | [SELECT JSONType(json_column, 'user.data');] |
| JSONExtractKeys | macro | Extracts keys from a JSON object | NULL | [SELECT JSONExtractKeys(json_column);] |
| JSONExtractValues | macro | Extracts all values as text from a JSON object | NULL | [SELECT JSONExtractValues(json_column);] |
| equals | macro | Checks if two values are equal | NULL | [SELECT equals(column_a, column_b);] |
| notEquals | macro | Checks if two values are not equal | NULL | [SELECT notEquals(column_a, column_b);] |
| less | macro | Checks if one value is less than another | NULL | [SELECT less(column_a, column_b);] |
| greater | macro | Checks if one value is greater than another | NULL | [SELECT greater(column_a, column_b);] |
| lessOrEquals | macro | Checks if one value is less than or equal to another | NULL | [SELECT lessOrEquals(column_a, column_b);] |
| greaterOrEquals | macro | Checks if one value is greater than or equal to another | NULL | [SELECT greaterOrEquals(column_a, column_b);] |
| dictGet | macro | Retrieves an attribute from a VARIABLE string or MAP | NULL | [SELECT dictGet('dictionary_name', 'attribute');] |
| arrayJoin | macro | Unroll an array into multiple rows | NULL | [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 | NULL | [SELECT empty('');] |
| toFloat | macro | Converts a value to a float | NULL | [SELECT toFloat('123.45');] |
| toInt128OrNull | macro | Converts to a 128-bit integer or returns NULL on failure | NULL | [SELECT toInt128OrNull('abc');] |
| toInt128OrZero | macro | Converts to a 128-bit integer or returns zero on failure | NULL | [SELECT toInt128OrZero('abc');] |
| toUInt16OrNull | macro | Converts to an unsigned 16-bit integer or returns NULL on failure | NULL | [SELECT toUInt16OrNull('abc');] |
| toUInt8 | macro | Converts a value to an unsigned 8-bit integer | NULL | [SELECT toUInt8('123');] |
| JSONExtractString | macro | Extracts JSON data as a VARCHAR from a JSON object | NULL | [SELECT JSONExtractString(json_column, 'user.email');] |
| system_columns | table | NULL | NULL | [NULL] |
| chsql_openssl_version | scalar | NULL | NULL | [NULL] |
| system_functions | table | NULL | NULL | [NULL] |
| uptime | scalar | NULL | NULL | [NULL] |
| url_flock | table | NULL | NULL | [NULL] |
| system_disks | table | NULL | NULL | [NULL] |
| system_tables | table | NULL | NULL | [NULL] |
| chsql | scalar | NULL | NULL | [NULL] |
| system_databases | table | NULL | NULL | [NULL] |