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