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: 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.