Search Shortcut cmd + k | ctrl + k
chsql
Downloads 360this week

ClickHouse SQL Macros for DuckDB

Extension repository on GitHub
Extension descriptor (YAML)

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