Home

Awesome

<a href="https://community-extensions.duckdb.org/extensions/chsql.html" target="_blank"> <img src="https://github.com/user-attachments/assets/9003897d-db6f-4a79-9443-9b72766b511b" width=200> </a>

DuckDB ClickHouse SQL extension

The DuckDB chsql community extension implements popular ClickHouse SQL syntax macros and functions,<br> making it easier for users to transition between the two database systems ⭐ designed for Quackpipe

<br>

Installation

chsql is distributed as a DuckDB Community Extension and can be installed using SQL:

INSTALL chsql FROM community;
LOAD chsql;

If you previously installed the chsql extension, upgrade using the FORCE command

FORCE INSTALL chsql FROM community;
LOAD chsql;

Usage Examples

Once installed, the macro functions provided by the extension can be used just like built-in functions.

Here's a random example out of 100s using the IPv4StringToNum and IPv4NumToString functions:

D INSTALL chsql FROM community;
D LOAD chsql;
D SELECT IPv4StringToNum('127.0.0.1'), IPv4NumToString(2130706433);
┌──────────────────────────────┬─────────────────────────────┐
│ ipv4stringtonum('127.0.0.1') │ ipv4numtostring(2130706433) │
│            int32             │           varchar           │
├──────────────────────────────┼─────────────────────────────┤
│                   2130706433 │ 127.0.0.1                   │
└──────────────────────────────┴─────────────────────────────┘

Remote Queries

The built-in ch_scan function can be used to query remote ClickHouse servers using the HTTP/s API

D SELECT * FROM ch_scan("SELECT number * 2 FROM numbers(10)", "https://play.clickhouse.com");

Supported Functions

👉 The list of supported aliases is available on the dedicated extension page<br> 👉 The combined list of supported functions can be obtained using an SQL Join

<br>

Motivation

Why is the DuckDB + chsql combo fun and useful

✔ 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.

<img src="https://github.com/user-attachments/assets/a17efd68-d2e1-42a7-8ab9-1ea4c2ff11e3" width=700 /> <br> <br>

Development

The extension is automatically build and distributed. This section is only required for development.

Managing dependencies

DuckDB extensions uses VCPKG for dependency management. Enabling VCPKG is very simple: follow the installation instructions or just run the following:

git clone https://github.com/Microsoft/vcpkg.git
./vcpkg/bootstrap-vcpkg.sh
export VCPKG_TOOLCHAIN_PATH=`pwd`/vcpkg/scripts/buildsystems/vcpkg.cmake

Note: VCPKG is only required for extensions that want to rely on it for dependency management. If you want to develop an extension without dependencies, or want to do your own dependency management, just skip this step. Note that the example extension uses VCPKG to build with a dependency for instructive purposes, so when skipping this step the build may not work without removing the dependency.

Build steps

Clone the repository and fetch all required submodules:

git submodule update --init

Build the extension:

GEN=ninja make

The main binaries that will be built are:

./build/release/duckdb
./build/release/test/unittest
./build/release/extension/dynamic_sql_clickhouse/dynamic_sql_clickhouse.duckdb_extension

Running the extension

To run the extension code, simply start the shell with ./build/release/duckdb.

Now we can use the features from the extension directly in DuckDB. See /tests/sql for a list of supported functions.

Running the tests

Different tests can be created for DuckDB extensions. The primary way of testing DuckDB extensions should be the SQL tests in ./test/sql. These SQL tests can be run using:

make test
<!-- ### Installing the deployed binaries To install your extension binaries from S3, you will need to do two things. Firstly, DuckDB should be launched with the `allow_unsigned_extensions` option set to true. How to set this will depend on the client you're using. Some examples: CLI: ```shell duckdb -unsigned ``` Python: ```python con = duckdb.connect(':memory:', config={'allow_unsigned_extensions' : 'true'}) ``` NodeJS: ```js db = new duckdb.Database(':memory:', {"allow_unsigned_extensions": "true"}); ``` Secondly, you will need to set the repository endpoint in DuckDB to the HTTP url of your bucket + version of the extension you want to install. To do this run the following SQL query in DuckDB: ```sql SET custom_extension_repository='bucket.s3.eu-west-1.amazonaws.com/<your_extension_name>/latest'; ``` Note that the `/latest` path will allow you to install the latest extension version available for your current version of DuckDB. To specify a specific version, you can pass the version instead. After running these steps, you can install and load your extension using the regular INSTALL/LOAD commands in DuckDB: ```sql INSTALL dynamic_sql_clickhouse LOAD dynamic_sql_clickhouse ``` -->
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.