chsql

Search Shortcut cmd + k | ctrl + k

Documentation

chsql

Downloads 528this week

GitHub stars 47

Extension repository on GitHub

Extension descriptor (YAML)

ClickHouse SQL Macros for DuckDB

Maintainer(s): lmangani, akvlad

Installing and Loading

INSTALL chsql FROM community;
LOAD chsql;

Example

-- Use boring ClickHouse SQL function macros in DuckDB SQL queries. Examples:
D SELECT toString('world') AS hello, toInt8OrZero('world') AS zero;
┌─────────┬───────┐
│  hello  │ zero  │
│ varchar │ int64 │
├─────────┼───────┤
│ world   │     0 │
└─────────┴───────┘

D 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
D SELECT * FROM ch_scan("SELECT number * 100 FROM numbers(3)","https://play.clickhouse.com", format := 'Parquet');
┌───────────────────────┐
│ multiply(number, 100) │
│        varchar        │
├───────────────────────┤
│ 0                     │
│ 100                   │
│ 200                   │
└───────────────────────┘

-- Query the emulated system tables to explore columns, rows, types, storage, etc
D SELECT * FROM system.tables;
D SELECT * FROM system.columns;
D SELECT * FROM system.functions;
D SELECT * FROM system.uptime;
D SELECT * FROM system.disks;
┌──────────┬──────────────┬────────────┬─────────────┬──────────────────┬─────────────────┬─────────┬─────────────────────┬───────────────┬──────────────┬──────────────┬───────────────┬───────────┬───────────┬────────────┐
│   name   │     path     │ free_space │ total_space │ unreserved_space │ keep_free_space │  type   │ object_storage_type │ metadata_type │ is_encrypted │ is_read_only │ is_write_once │ is_remote │ is_broken │ cache_path │
│ varchar  │   varchar    │   int64    │    int64    │      int64       │      int64      │ varchar │       varchar       │    varchar    │   boolean    │   boolean    │    boolean    │  boolean  │  boolean  │  varchar   │
├──────────┼──────────────┼────────────┼─────────────┼──────────────────┼─────────────────┼─────────┼─────────────────────┼───────────────┼──────────────┼──────────────┼───────────────┼───────────┼───────────┼────────────┤
│ localdb  │ test.db      │          0 │      262144 │                0 │               0 │ Local   │ None                │ None          │ false        │ false        │ false         │ false     │ false     │            │
│ memory   │ NULL         │          0 │           0 │                0 │               0 │ Local   │ None                │ None          │ false        │ false        │ false         │ false     │ false     │            │
│ testduck │ /tmp/duck.db │     262144 │      786432 │           262144 │               0 │ Local   │ None                │ None          │ false        │ false        │ false         │ false     │ false     │            │
└──────────┴──────────────┴────────────┴─────────────┴──────────────────┴─────────────────┴─────────┴─────────────────────┴───────────────┴──────────────┴──────────────┴───────────────┴───────────┴───────────┴────────────┘

About chsql

DuckDB ClickHouse SQL extension

The DuckDB chsql community extension implements 100+ popular ClickHouse SQL Macros, functions and helpers making it easier for users to transition between OLAP systems ⭐

Motivation

DuckDB is our favourite OLAP engine but ClickHouse has lots of integrations and users. This extension is dedicated to ClickHouse refugeess.

✔ DuckDB SQL is awesome and full of great functions.<br>
✔ ClickHouse SQL is awesome and full of great functions.

✔ The DuckDB library is ~51M and modular. Can LOAD extensions.<br>
❌ The ClickHouse monolith is ~551M and growing. No extensions.

✔ DuckDB is open source and protected by a no-profit foundation.<br>
❌ ClickHouse is open core and controlled by for-profit corporation.

✔ DuckDB embedded is fast, mature and elegantly integrated in many languages.<br>
❌ chdb is still experimental, unstable and currently only supports Python.

Extensions

chsql_native provides a native clickhouse client (binary) and a reader for ClickHouse Native format files

Legal Disclaimer

DuckDB ® is a trademark of DuckDB Foundation. ClickHouse® is a trademark of ClickHouse Inc. All trademarks, service marks, and logos mentioned or depicted are the property of their respective owners. The use of any third-party trademarks, brand names, product names, and company names is purely informative or intended as parody and does not imply endorsement, affiliation, or association with the respective owners.

Added Functions

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]

In this article