🐣 Introducing: chsql for DuckDB

TLDR: DuckDB extension providing ClickHouse SQL Dialect Macros

Prequel

Our readers know this has been a ClickHouse centric blog for quite some time. Together we've documented our journey with qryn - the first polyglot observability stack build on top of Clickhouse - and on the side we built a few frankenstein serverless embedded Clickhouse series, later even joining efforts founding chdb - quickly killed by world class crooks.We also experimented lots with DuckDB

First Quack ⭐

Once the chdb adventure aborted we switched focus on Quackpipe, a fast and tiny serverless OLAP API powered by DuckDB and emulating the ClickHouse HTTP API with basic format compatibility and shipping with the same play interface, session persistence and authentication. Quackpipe is double-face and also works as a FIFO processor and ClickHouse UDFs to run DuckDB queries.

🔥 Curious? Try running a serverless query using our Fly.io public demo

Different SQL Strokes

Now Quackpipe speaks the DuckDB SQL language - which is amazing - but our audience are ClickHouse refugees who spent years mastering language conventions just like ourselves - and some of those functions are actually good and useful.

Our initial solution was loading a list of ClickHouse SQL aliases at startup time but this approach was slow, fragile and quite hard to maintain, update and embed.

-- Type conversion macros
CREATE OR REPLACE MACRO toString(expr) AS CAST(expr AS VARCHAR);
CREATE OR REPLACE MACRO toInt8(expr) AS CAST(expr AS INT8);
CREATE OR REPLACE MACRO toInt16(expr) AS CAST(expr AS INT16);
CREATE OR REPLACE MACRO toInt32(expr) AS CAST(expr AS INT32);
CREATE OR REPLACE MACRO toInt64(expr) AS CAST(expr AS INT64);
CREATE OR REPLACE MACRO toInt128(expr) AS CAST(expr AS INT128);
CREATE OR REPLACE MACRO toInt256(expr) AS CAST(expr AS HUGEINT);
-- Type conversion with default values
CREATE OR REPLACE MACRO toInt8OrZero(expr) AS COALESCE(TRY_CAST(expr AS INT8), 0);
CREATE OR REPLACE MACRO toInt16OrZero(expr) AS COALESCE(TRY_CAST(expr AS INT16), 0);
CREATE OR REPLACE MACRO toInt32OrZero(expr) AS COALESCE(TRY_CAST(expr AS INT32), 0);
CREATE OR REPLACE MACRO toInt64OrZero(expr) AS COALESCE(TRY_CAST(expr AS INT64), 0);
CREATE OR REPLACE MACRO toInt128OrZero(expr) AS COALESCE(TRY_CAST(expr AS INT128), 0);
CREATE OR REPLACE MACRO toInt256OrZero(expr) AS COALESCE(TRY_CAST(expr AS HUGEINT), 0);
CREATE OR REPLACE MACRO toInt8OrNull(expr) AS TRY_CAST(expr AS INT8);
CREATE OR REPLACE MACRO toInt16OrNull(expr) AS TRY_CAST(expr AS INT16);
CREATE OR REPLACE MACRO toInt32OrNull(expr) AS TRY_CAST(expr AS INT32);
CREATE OR REPLACE MACRO toInt64OrNull(expr) AS TRY_CAST(expr AS INT64);
CREATE OR REPLACE MACRO toInt128OrNull(expr) AS TRY_CAST(expr AS INT128);
CREATE OR REPLACE MACRO toInt256OrNull(expr) AS TRY_CAST(expr AS HUGEINT);
-- and so on and on....

Luckily something much better was at the horizon....

DuckDB Extensions 🦆

DuckDB has a flexible extension mechanism that allows for dynamically loading extensions on all supported architectures, extending DuckDB functionality by providing support for additional file formats, introducing new types, and domain-specific functionality. What would we do without JSON, HTTPFS, Arrow, etc?

And while DuckDB Labs could have selfishly kept extension super complex they decided to do the opposite and invited every developer to the party of the year!

DuckDB Community Extensions 🦆🦆🦆

TL;DR: DuckDB extensions can now be published via theDuckDB Community Extensions repository.The repository makes it easier for users to install extensions using theINSTALL ⟨extension name⟩ FROM community syntax. Extension developers avoid the burdens of compilation and distribution.

This it not marketing. The full ecosystem is ready to use with working examples, actions to build for all platforms and distribution for community extensions!

This is how it's done. Competition is none.

New plan: Quackpipe will use an extension to speak "ClickHouse" friendly.

👋 ClickHouse SQL Extension

Thanks to the fantastic examples a few hours later the chsql extension for DuckDB was already born implementing a small but growing number of native macros using ClickHouse SQL syntax transpiled to DuckDB SQL and Lambdas, making it easier to transition knowledge, users and scripts between the two database systems.

Publishing the extension was so easy it felt like cheating. It's quack magic.

Surprise, MotherDucker!

Community is where DuckDB really shines. After years of working with Vendors trying to kill any opensource Community effort this ecosystem feels different.

Respect to everyone at DuckDB and MotherDuck for doing such an amazing job.

UPDATE: We're excited to be part of the MotherDuck Startup Program*! 🦆*

Kudos: As soon as we started working on the extension and before we could even ask several Ducks flocked in and kindly helped us overcome a few initial issues, demonstrating how much they respect users and value developer interactions.**A++

For the first time after years of attempting to work with crippled UDFs in ClickHouse and dealing with the monster size of the project, we see some light ahead in OLAP.

So - Expect lots more DuckDB content on the blog as the adventure continues!

⭐⭐⭐ SQL Hackers, Join us! ⭐⭐⭐

This is just the beginning of a long journey. To succeed we'll need your help.

If you're a ClickHouse or DuckDB SQL wizard*(or just have lots of SQL patience)* you can join the fun and contribute by adding, fixing or extending supported macros:

1. Find a ClickHouse function you are interested into from the functions list

2. Find any DuckDB functions offering viable methods to alias the target function

3. Create your macro and extend to neighboring functions with similar scope.

4. Test and Submit your contribution. We'll do the coding if needed.