ClickHouse tips #3: the transform function

Clickhouse has a powerful feature, JOIN engines, that allows us to prepare a table to be joined with better performance than a regular table (MergeTree, Log…). It also allows to use joinGet to get table values using a key.

Somtimes you don’t have a JOIN table but you’d like to use something with the joinGet performance. Unfortunately, you can’t use joinGet with something created on the fly.

However there is a way to do that, using transform

The basic structure would be like this

A real-life example

Given a sales table like this with 1M rows (download the csv here):

and a exchange_rate table like this (CSV here), with daily data for 3 years:

Imagine you want to get the total amount of sales in dollars per country. You could do it with a join like this if the data is small:

But if the data is big, using transform would have a better performance. You’d do something like this:

Run it yourself

We’ve created two CSVs with fake data and those schemas. If you want to replicate the queries above in your account, create the data sources with this command: