Feb 25, 2021

ClickHouse tips #3: the transform function

Using the transform function to join two tables when joinGet is not available.
Javier Santana
Co-founder

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:

Do you like this post?

Related posts

ClickHouse tips #6: Filtering data in subqueries to avoid joins
A big performance boost, adding columns and more
Tinybird
Team
May 31, 2021
We launched an open source ClickHouse Knowledge Base
Tinybird
Team
Oct 11, 2022
ClickHouse tips #9: Filling gaps in time-series on ClickHouse
Want a managed ClickHouse®️? Here are some options
Want a managed ClickHouse®️? Here are some options
Tinybird vs. ClickHouse®️: What's the difference?
Tinybird at South Summit Madrid 2019
Tinybird vs. ClickHouse®️: What's the difference?
How to query Google Sheets with SQL in real time

Build fast data products, faster.

Try Tinybird and bring your data sources together and enable engineers to build with data in minutes. No credit card required, free to get started.
Need more? Contact sales for Enterprise support.