# ClickHouse 🤝 DuckDB = OLAP²

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1688299920521/0c93f5ee-3183-429b-89e8-f0166ad46bf7.png align="left")

# ClickHouse 🤝 DuckDB

If you follow this blog you're familiar with the **OLAP** ecosystem, our passion for **ClickHouse** and our involvement in developing the [chDB in-memory database](https://chdb.io).

*The ClickHouse community is strong and proud but there's a small taboo* 🤫

🦆 <mark>The bipedal in the room is </mark> **<mark>DuckDB </mark>** <mark>and its exciting features and small size!</mark>

😂 Jokes aside, there are already [lots of comparisons around](https://www.vantage.sh/blog/clickhouse-local-vs-duckdb) making a case for either or both platforms being the best or the fastest at something. We leave that to the benchmark experts, while we focus on what we love: `integrations without borders!`

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1688299739987/f0120f58-a2ac-4666-a59b-644a9a39b990.png align="left")

### Enemies with Benefits

*<mark>Can ClickHouse and DuckDB become </mark>* ***<mark>enemies with benefits</mark>*** *<mark>(for users)?</mark>*

To make this little *bromance* happen we'll use a *magical combo*:

* [**ClickHouse** UDF Functions](https://clickhouse.com/docs/en/engines/table-functions/executable)
    
* [**QuackPipe**: DuckDB-powered API with extra ClickHouse friendliness](https://github.com/metrico/quackpipe)
    

💡 [**Quackpipe**](https://github.com/metrico/quackpipe) runs a **DuckDB** core and supports the **Clickhouse query API** and several native data **formats -** essentially impersonating a basic **ClickHouse** and including a variety of [ClickHouse Macro Aliases](https://github.com/metrico/quackpipe/blob/main/aliases.sql) to make familiar commands and functions available out of the box for ClickHouse clients and users.

It can run standalone, embedded or accept input and commands via `-stdin` from scripts. It even [includes a fully working clickhouse play user interface](https://quackpipe.fly.dev/)! 😎

<iframe src="https://quackpipe.fly.dev" width="100%" height="500"></iframe>

<center>☝️☝️☝️ This is a Live demo, not just an image. Pick a Preset and click RUN ☝️☝️☝️</center>

That's the definition of a ClickHouse **UDF-ready** toolset! Let's go <mark>OLAP²</mark>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1688299955214/64768690-3d59-4c91-a069-95f3ed60067a.png align="left")

# OLAP² Experiment

### Install Quackpipe UDF

Download the [latest quackpipe binary](https://github.com/metrico/quackpipe) to your **clickhouse-server** `user_scripts` directory to allow [safe execution](https://clickhouse.com/docs/en/sql-reference/functions/udf) from within your ClickHouse queries:

```bash
curl -fsSL github.com/metrico/quackpipe/releases/latest/download/quackpipe-amd64 --output quackpipe \
&& chmod +x quackpipe

mv quackpipe /var/lib/clickhouse/user_scripts/quackpipe
```

⚡ Once ready, we can leverage our **Quackpipe** integration with two methods:

### Table UDF:

This method can be used to return multiple **columns** and **rows** from DuckDB into ClickHouse using any of the supported formats. Table schema MUST be known.

```sql
:) SELECT * FROM executable('quackpipe -stdin -format TSV', TSV, 'id UInt32, num UInt32', (SELECT 'SELECT 1, 2'))
```

```sql
:) SELECT *
FROM executable('quackpipe -stdin -format TSV', TSV, 'id UInt32, num UInt32', (
    SELECT 'SELECT 1, 2'
))
Query id: dd878948-bec8-4abe-9e06-2f5813653c3a
┌─id─┬─num─┐
│  1 │   2 │
└────┴─────┘
1 rows in set. Elapsed: 0.268 sec.
```

<mark>What is this? Think of it as a </mark> **<mark>SELECT within a SELECT</mark>** <mark>with a </mark> *<mark>different syntax</mark>*<mark>.</mark>

The data comes from DuckDB right into ClickHouse columns. You can [read and pass data to UDF functions](https://clickhouse.com/docs/en/engines/table-engines/special/executable#passing-query-results-to-a-script) ad-hoc per query, or by using a dedicated engine table.

```sql
:) CREATE TABLE onlyone
(
    `id` UInt64
    `num` UInt64
)
ENGINE = Executable('quackpipe -stdin -format TSV', TSV, (
    SELECT 'SELECT 1, 2'
));

:) SELECT * FROM onlyone

Query id: 45aa7267-e0f4-4fdc-a52e-5a7a4bf2c0a8
┌─id─┬─num─┐
│  1 │   2 │
└────┴─────┘
1 rows in set. Elapsed: 0.272 sec.
```

### Function UDF:

<mark>NOTE: ONLY WORKS AS FUNCTION, cannot return multiple rows or columns!</mark>

1. Configure the UDF function in `/etc/clickhouse-server/duckdb_function.xml`
    

* the `return_type` is `String` with the full results
    
* the actual content can be JSON, CSV, TSV with or without Column Names
    

```xml
<functions>
    <function>
        <type>executable</type>
        <name>duckdb</name>
        <return_type>String</return_type>
        <argument>
            <type>String</type>
        </argument>
        <format>TSV</format>
        <command>quackpipe -stdin -format TSV</command>
    </function>
</functions>
```

1. Restart Clickhouse and make sure the function is loaded
    

```bash
clickhouse-udf | Processing configuration file '/etc/clickhouse-server/duckdb_function.xml'.
clickhouse-udf | Saved preprocessed configuration to '/var/lib/clickhouse/preprocessed_configs/duckdb_function.xml'.
```

1. Test with Clickhouse client.
    

```sql
b5c4f004e862 :) SELECT duckdb('SELECT 1') as t1

SELECT duckdb('SELECT 1') AS t1

Query id: 68c4fbd0-75a5-466d-9720-3937bcd96898

┌─t1─┐
│ 1  │
└────┘

1 row in set. Elapsed: 0.047 sec.
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1688299955214/64768690-3d59-4c91-a069-95f3ed60067a.png align="left")

### BONUS CONTENT

Upon request of **Alexey Milovidov**, here's DuckDB querying data out of ClickHouse and [**chdb**](https://chdb.dev) using nothing more than the HTTP API and URL query parameters 🔥🔥🔥

```sql
SELECT * FROM 
read_json_auto("https://chdb.fly.dev/?default_format=JSONEachRow&query=SELECT number as once, number *2 as twice FROM numbers(10)")
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1688675218864/07c187da-6446-45c9-8763-98634d74acf9.png align="center")

The string format is not the prettiest *(we'll work on that!)* but the query returns steaming hot columns and column formats just fine! It even works on **MotherDuck**:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1688674470128/8dc47e98-99ab-4cde-b66a-fb98ef6f5359.png align="center")

### Conclusion

Where do we go from here? Can this combination be used to build something amazing and revolutionary, or is it just a useless hack/flex for both platforms?

*That's up to the users and community - and hopefully, You too!*

Want more? <mark>Discover all of our projects on the </mark> [<mark>Metrico github repository</mark>](https://metrico.in)

[![](https://user-images.githubusercontent.com/1423657/231310060-aae46ee6-c748-44c9-905e-20a4eba0a814.png align="center")](https://github.com/metrico/quackpipe)