Search Shortcut cmd + k | ctrl + k
chsql
Downloads 404this week
GitHub stars 46
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

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 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');
system_columns table      
chsql_openssl_version scalar      
system_functions table      
uptime scalar      
url_flock table      
system_disks table      
system_tables table      
chsql scalar      
system_databases table