Sep 29, 2021

ClickHouse Tips #12: Apply Functions to Columns with a Single Call

Clickhouse 21 allows some fancy operations packed into multiple columns with SELECT modifiers.
Tinybird
Team

Sometimes we have to apply simple operations in multiple columns and modify the stored/original value of those columns. ClickHouse has SELECT modifiers that can help us: APPLY, EXCEPT AND REPLACE.

Let’s see some easy examples using this simple set:

The first one APPLY will apply a function to all the columns we specify in the SELECT clause like:

Now, with EXCEPT we can select different columns except the ones specified, and we can combine the modifiers:

The last one is REPLACE that you can use again apply simple visual functions and replace columns with new values, using alias expressions:

Sweet right? well as we said you can mix them all like this:

or this,

And stay tuned for the next ClickHouse release 21.10 in which we can use a lambda function inside an APPLY.

Do you like this post?

Related posts

ClickHouse tips #11: Best way to get query types
Tinybird
Team
Sep 22, 2021
A big performance boost, adding columns and more
Tinybird
Team
May 31, 2021
We launched an open source ClickHouse Knowledge Base
Tinybird
Team
Oct 11, 2022
Changelog: Snowflake connector, cancellable jobs and more
Tinybird
Team
Mar 23, 2021
ClickHouse tips #7: Forward and backfilling null values
You can now create Materialized Views in the Tinybird UI
Changelog #18: High-frequency ingestion, handling NDJSON files and more product enhancements
Tinybird
Team
Feb 01, 2022
ClickHouse tips #3: the transform function
ClickHouse tips #6: Filtering data in subqueries to avoid joins
Tinybird vs. ClickHouse®️: What's the difference?

Build fast data products, faster.

Try Tinybird and bring your data sources together and enable engineers to build with data in minutes. No credit card required, free to get started.
Need more? Contact sales for Enterprise support.