Feb 25, 2021

ClickHouse tips #3: the transform function

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

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

Real-time Data Visualization: How to build faster dashboards
A new way to create intermediate Data Sources in Tinybird
Jun 15, 2023
Export data from Tinybird to Amazon S3 with the S3 Sink
Mar 21, 2024
Tinybird: A ksqlDB alternative when stateful stream processing isn't enough
To the limits of SQL... and beyond
Automating data workflows with plaintext files and Git
Chatting GraphQL with Jamie Barton of Grafbase
Apr 24, 2023
What it takes to build a real-time recommendation system
We launched an open source ClickHouse Knowledge Base
Oct 11, 2022
The definition of real-time data

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.