ClickHouse Tips #12: Apply Functions to Columns with a Single Call
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 %}.
%20(1).jpg)
