HELP WANTED! HOW TO EXTEND CHSQL

[lmangani]

chsql for DuckDB

Hello Stranger!

About

This DuckDB extension implements various macros using ClickHouse SQL syntax, making it easier to transition knowledge, users and scripts between the two database systems.

Since ClickHouse has hundreds of commands, this extension is a perpetual WIP.

Contributing

If you're a ClickHouse SQL wizard (or just have lots of patience) you can join the fun by contributing to this project..

Here's how you can help this extension by adding, fixing or extending its scope of SQL macros:

1. Find a ClickHouse function you are interested into the in functions list
2. Find if DuckDB functions offer a viable method to alias the target function
3. Create the macro and extend to neighboring class functions with similar scope.
4. Test and submit your contribution. We'll do the coding if needed for those only familiar with SQL syntax.

Macro Examples

Here's a couple random examples:

Simple

ClickHouse toInt(8|16|32|64|128|256)

Converts an input value to a value the Int data type. This function family includes:

toInt8(expr) — Converts to a value of data type Int8.
toInt16(expr) — Converts to a value of data type Int16.
toInt32(expr) — Converts to a value of data type Int32.
toInt64(expr) — Converts to a value of data type Int64.
toInt128(expr) — Converts to a value of data type Int128.
toInt256(expr) — Converts to a value of data type Int256.

Arguments

• expr — Expression returning a number or a string with the decimal representation of a number. Binary, octal, and hexadecimal representations of numbers are not supported. Leading zeroes are stripped.

Returned value

Integer value in the Int8, Int16, Int32, Int64, Int128 or Int256 data type.

DuckDB Macro

Let's convert our function to a DuckDB equivalent macros using native datatypes:

-- 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);

Once ready, implement the new functions by extending the the chsql_macros array:

// -- Type conversion macros
{DEFAULT_SCHEMA, "toString", {"x", nullptr}, R"(CAST(x AS VARCHAR))"},
{DEFAULT_SCHEMA, "toInt8", {"x", nullptr}, R"(CAST(x AS INT8))"},
{DEFAULT_SCHEMA, "toInt16", {"x", nullptr}, R"(CAST(x AS INT16))"},
{DEFAULT_SCHEMA, "toInt32", {"x", nullptr}, R"(CAST(x AS INT32))"},
{DEFAULT_SCHEMA, "toInt64", {"x", nullptr}, R"(CAST(x AS INT64))"},
{DEFAULT_SCHEMA, "toInt128", {"x", nullptr}, R"(CAST(x AS INT128))"},
{DEFAULT_SCHEMA, "toInt256", {"x", nullptr}, R"(CAST(x AS HUGEINT))"},
{DEFAULT_SCHEMA, "toInt8OrZero", {"x", nullptr}, R"(CASE WHEN TRY_CAST(x AS INT8) IS NOT NULL THEN CAST(x AS INT8) ELSE 0 END)"},
{DEFAULT_SCHEMA, "toInt16OrZero", {"x", nullptr}, R"(CASE WHEN TRY_CAST(x AS INT16) IS NOT NULL THEN CAST(x AS INT16) ELSE 0 END)"},
{DEFAULT_SCHEMA, "toInt32OrZero", {"x", nullptr}, R"(CASE WHEN TRY_CAST(x AS INT32) IS NOT NULL THEN CAST(x AS INT32) ELSE 0 END)"},
{DEFAULT_SCHEMA, "toInt64OrZero", {"x", nullptr}, R"(CASE WHEN TRY_CAST(x AS INT64) IS NOT NULL THEN CAST(x AS INT64) ELSE 0 END)"},
{DEFAULT_SCHEMA, "toInt128OrZero", {"x", nullptr}, R"(CASE WHEN TRY_CAST(x AS INT128) IS NOT NULL THEN CAST(x AS INT128) ELSE 0 END)"},
{DEFAULT_SCHEMA, "toInt256OrZero", {"x", nullptr}, R"(CASE WHEN TRY_CAST(x AS HUGEINT) IS NOT NULL THEN CAST(x AS HUGEINT) ELSE 0 END)"},
{DEFAULT_SCHEMA, "toInt8OrNull", {"x", nullptr}, R"(TRY_CAST(x AS INT8))"},
{DEFAULT_SCHEMA, "toInt16OrNull", {"x", nullptr}, R"(TRY_CAST(x AS INT16))"},
{DEFAULT_SCHEMA, "toInt32OrNull", {"x", nullptr}, R"(TRY_CAST(x AS INT32))"},
{DEFAULT_SCHEMA, "toInt64OrNull", {"x", nullptr}, R"(TRY_CAST(x AS INT64))"},
{DEFAULT_SCHEMA, "toInt128OrNull", {"x", nullptr}, R"(TRY_CAST(x AS INT128))"},
{DEFAULT_SCHEMA, "toInt256OrNull", {"x", nullptr}, R"(TRY_CAST(x AS HUGEINT))"},

---

Medium

ClickHouse tuplePlus

Calculates the sum of corresponding values of two tuples of the same size.

Syntax

tuplePlus(tuple1, tuple2)

Arguments

• tuple1 — First tuple. Tuple.
• tuple2 — Second tuple. Tuple.

Returned value

Tuple with the sum. Tuple.

Example

Query:

SELECT tuplePlus((1, 2), (2, 3));

Result:

(3,5)

DuckDB Macro

Let's convert our function to a DuckDB equivalent macro using a lambda function or LISTs:

CREATE OR REPLACE MACRO tuplePlus(a, b) AS (apply(a, (x,i) -> apply(b, x -> CAST(x AS BIGINT))[i] + CAST(x AS BIGINT)));

Example

Query:

SELECT tuplePlus([1, 2], [2, 3]);

Result:

tupleplus(main.list_value(1, 2), main.list_value(2, 3))
--
[3,5]

Add to Extension

Once ready, implement the new functions by extending the the chsql_macros array with the newly created macro:

    {DEFAULT_SCHEMA, "tuplePlus", {"a", "b"}, {{nullptr, nullptr}}, R"(apply(a, (x,i) -> apply(b, x -> CAST(x AS BIGINT))[i] + CAST(x AS BIGINT)))"},

---

Advanced (table macro)

ClickHouse url

The url table function creates a table from a provided URL with given format.

Syntax

SELECT * FROM url(url, format);

Arguments

• URL — HTTP or HTTPS server address, which can accept GET or POST requests
• format — Format of the data. Type: JSON,PARQUET,CSV,TEXT,BLOB

Returned value

A table with the specified format and structure and with data from the defined URL.

Table Macro

        {DEFAULT_SCHEMA, "url", {"url", "format"}, {{nullptr, nullptr}}, R"(WITH "JSON" as (SELECT * FROM read_json_auto(url)), "PARQUET" as (SELECT * FROM read_parquet(url)), "CSV" as (SELECT * FROM read_csv_auto(url)), "BLOB" as (SELECT * FROM read_blob(url)), "TEXT" as (SELECT * FROM read_text(url)) FROM query_table(format))"},

---

Ready? Submit a PR

It doesn't matter if you're adding a new command, fixing an old one or just correct a spelling issue. Everything helps!

• If you're an SQL wizard, just open an issue with your proposal for testing, validation and implementation.
• If you're a Developer, implement the new function(s) directly in the source code and submit a full PR.
• If you're a Saint, you can also implement a test case for your new function(s) in the tests/sql directory.

👍 That's it! Simpler functions are trivial while others are puzzles. Have fun!