Skip to content

Instantly share code, notes, and snippets.

@javisantana

javisantana/til_dynamic_joinget.md

Last active November 6, 2020 06:18
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save javisantana/07c689564874cea6e4a31bd40fc770b1 to your computer and use it in GitHub Desktop.
Save javisantana/07c689564874cea6e4a31bd40fc770b1 to your computer and use it in GitHub Desktop.
Download ZIP
how to generate a map data structure dynamically in clickhouse
Raw
til_dynamic_joinget.md

Clickhouse has a powerful feature, JOIN engines, that allows to prepare a table to be joined with better performance that 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 (well, you could create a temporally join table but you need several SQL queries).

So there is a way to do that, using transform:

with (
  select (groupArray(key), groupArray(value)) from my_table
) as key_value
select transform(column, key_value.1, key_value.2) from other_table

it's not as fast as joinGet but it's pretty fast.

There is also a ongoing PR (as I type this) that adds a Map type that migth be useful as well.

Test this on tinybird

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment