Search for a command to run...
Pivoting data in ClickHouse is fun and everyone should do it
I accidently became a data & analytics engineer in the early 00:s but back then we called ourselves Business Intelligence consultants. I've left the consulting industry to focus on long-term missions to reinvent our data & analytics tasks using open source technologies.
No comments yet. Be the first to comment.
It's something everyone should do
Todays lesson in working with legacy output is: data contracts, data contracts and data contracts. The great thing about shipping structured data as text files is it is so darn easy! Just store it as a CSV and boom: minimum overhead and just the righ...
Use Arrays and Tuples to keep nested data nested
A very long time ago my mentor at the time took a peek at a spreadsheet full of data I was tasked to read into QlikView. "That data is in a reporting format.", he said hinting at the fact that there was one column for each year of metrics. "You will have to transpose those columns into rows so you get the year in one column and the metric in another, like transactions."
In QlikView this was a fairly common practice at the time since many data sources came in spreadsheet format. There was a function named CrossTable that took care of that effortlessly.
In ClickHouse the same can be achieved by grouping the year columns into an array and then doing an ARRAY JOIN which will produce a new table by iterating through the array creating a separate record for each item.
Let's pretend that we have a copy of The Economist's Democracy Index list by country ingested into ClickHouse. It would look something like this when queried:
| Region | Country | Regime type | 2022 | 2021 | 2020 |
| North America | Canada | Full democracy | 8.88 | 8.87 | 9.24 |
| North America | United States | Flawed democracy | 7.85 | 7.85 | 7.92 |
| Western Europe | Austria | Full democracy | 8.2 | 8.07 | 8.16 |
To get this table into a transactional state where we can perform aggregation functions like Min(), Max() and Avg() we can create a dbt model like this:
{{
config(
materialized='table',
engine='MergeTree()',
order_by='(country, year)'
)
}}
WITH pivoted AS (
SELECT
Region AS region,
Country AS country,
`Regime type` AS regime_type,
year,
democracy_index
FROM ingest.democracy_index_by_country_2022
LEFT ARRAY JOIN
splitByString(',', '2022,2021,2020') AS year,
[`2022`,`2021`,`2020`] AS democracy_index
), final AS (
SELECT
toLowCardinality(region) AS region,
toLowCardinality(assumeNotNull(country)) AS country,
toLowCardinality(regime_type) AS regime_type,
toUInt16(year) AS year,
round(democracy_index, 2) as democracy_index
FROM pivoted
)
SELECT * FROM final
There is a lot of stuff going on in here! The double curly brackets at the top tell dbt that I want the result of this SQL query to be materialized as a new table in the database. Where and how this table is created is defined by how we've set up our dbt project.
I always recommend typing out SQL in a CTE (Common Table Expression) format. It is so much easier to read by both myself a week from now and by my colleagues.
The real magic happens in the LEFT ARRAY JOIN clause. The first statement after is a trick to create an array of year names by splitting a string into individual values by the splitByString() function. In the second statement, we treat all our year columns (escaped by backtick since column names are only numbers) as an array. When LEFT ARRAY JOIN is executed the year and democracy_index columns are created by iterating through the two arrays.
The final bit is some best practices to get the data in a healthy shape before running analytic queries. Since sorting columns shouldn't contain NULL and my table will be sorted by country and year we apply assumeNotNull() to the country column.