Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

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
Sep 29, 2021
 ・ 
  min read

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: {% code-line %}APPLY{% code-line-end %}, {% code-line %}EXCEPT{% code-line-end %} AND {% code-line %}REPLACE{% code-line-end %}.

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

The first one {% code-line %}APPLY{% code-line-end %} will apply a function to all the columns we specify in the SELECT clause like:

Now, with {% code-line %}EXCEPT{% code-line-end %} we can select different columns except the ones specified, and we can combine the modifiers:

The last one is {% code-line %}REPLACE{% code-line-end %} 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 {% code-line %}APPLY{% code-line-end %}.

Tinybird
Team
Share this post

Become a better data developer

Subscribe to the tinytales newsletter for monthly tips on building better data products.