---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: chsql
excerpt: |
DuckDB Community Extensions
ClickHouse SQL Macros for DuckDB
extension:
name: chsql
description: ClickHouse SQL Macros for DuckDB
version: 1.4.0
language: SQL & C++
build: cmake
license: MIT
maintainers:
- lmangani
- akvlad
- rustyconover
excluded_platforms: "windows_amd64_rtools;windows_amd64_mingw;windows_amd64;"
repo:
github: Query-farm/clickhouse-sql
ref: 7c871adf7990631ecdca77fb2cd0fc7e5c5b5665
docs:
hello_world: |
-- Use 100+ boring ClickHouse SQL function macros in DuckDB SQL queries.
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 │ │
└──────────┴──────────────┴────────────┴─────────────┴──────────────────┴─────────────────┴─────────┴─────────────────────┴───────────────┴──────────────┴──────────────┴───────────────┴───────────┴───────────┴────────────┘
extended_description: |
## DuckDB ClickHouse SQL extension
The DuckDB chsql community extension implements 100+ popular [ClickHouse SQL Macros](https://duckdb.org/community_extensions/extensions/chsql#added-functions), 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.
✔ ClickHouse SQL is awesome and full of great functions.
✔ The DuckDB library is ~51M and modular. Can LOAD extensions.
❌ The ClickHouse monolith is ~551M and growing. No extensions.
✔ DuckDB is open source and protected by a no-profit foundation.
❌ ClickHouse is open core and controlled by for-profit corporation.
✔ DuckDB embedded is fast, mature and elegantly integrated in many languages.
❌ chdb is still experimental, unstable and currently only supports Python.
```
### Extensions
- [chsql_native](https://duckdb.org/community_extensions/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.
extension_star_count: 94
extension_star_count_pretty: 94
extension_download_count: 6617
extension_download_count_pretty: 6.6k
image: '/images/community_extensions/social_preview/preview_community_extension_chsql.png'
layout: community_extension_doc
---
### Compatibility Matrix
This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ✅ | ✅ | ❌ | ❌ | ❌ |
| Linux ARM64 | ✅ | ✅ | ❌ | ❌ | ❌ |
| macOS AMD64 | ✅ | ✅ | ❌ | ❌ | ❌ |
| macOS ARM64 | ✅ | ✅ | ❌ | ❌ | ❌ |
| Windows AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ✅ | ✅ | ❌ | ❌ | ❌ |
### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```
{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### 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]);] |
| arrayJoin | macro | Unroll an array into multiple rows | NULL | [SELECT arrayJoin([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);] |
| 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');] |
| domain | macro | Extracts the domain from a URL | NULL | [SELECT domain('https://clickhouse.com/docs');] |
| empty | macro | Check if a string is empty | NULL | [SELECT empty('');] |
| 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);] |
| toFloat | macro | Converts a value to a float | NULL | [SELECT toFloat('123.45');] |
| 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');] |
| 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');] |
| 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');] |
| toUInt16OrNull | macro | Converts to an unsigned 16-bit integer or returns NULL on failure | NULL | [SELECT toUInt16OrNull('abc');] |
| 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');] |
| toUInt8 | macro | Converts a value to an unsigned 8-bit integer | NULL | [SELECT toUInt8('123');] |
| 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');] |
| JSONExtractString | macro | Extracts JSON data as a VARCHAR from a JSON object | NULL | [SELECT JSONExtractString(json_column, 'user.email');] |
| 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');] |
| system_databases | table | NULL | NULL | NULL |
| uptime | scalar | NULL | NULL | NULL |
| url_flock | table | NULL | NULL | NULL |
| system_functions | table | NULL | NULL | NULL |
| system_columns | table | NULL | NULL | NULL |
| system_disks | table | NULL | NULL | NULL |
| system_tables | table | NULL | NULL | NULL |
### Overloaded Functions
This extension does not add any function overloads.
### Added Types
This extension does not add any types.
### Added Settings
This extension does not add any settings.