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.
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 %}.

Share this post

Become a better data developer

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