ClickHouse: Redis as source for dictionaries

If two technologies make my heart bit louder, those are ClickHouse and Redis. Two very different databases, born to solve very different problems that, since ClickHouse 20.8, can work together.

Today we’ll talk about ClickHouse’s new external dictionary source for Redis. Although documented piece by piece, I haven’t found a concrete example that uses it, so I have created one little demo to play with it.

TL;DR

Clone the demo in https://github.com/pachico/demo-clickhouse-redis-dictionaries and follow instruction or keep reading for some notes.

Hypothetical use case: currency exchange rate

So, in which cases could we need these two databases connected together?

We don’t use ClickHouse for transactional operations but analytical queries. This is huge amounts of immutable data (facts) being aggregated for all sorts of insights. This brings two issues:

• data in ClickHouse is immutable: we deal with facts, with things that already happened, that do not require to be edited (although you could, but that’s an entirely different topic)
• many times, you don’t have the dynamic data required to complete your reports

Let’s dig into these two issues by imagining we have an application that stores a constant stream of price quotes (or biddings, or anything else that stores prices) from all over the world, although our company is in Europe and all our insights must be calculated in euros.

ClickHouse receives this data and stores it in the quotes_entry table that has this layout:

I can’t do aggregations since quotes are in different currencies.

Luckily, we have currency exchange rates stored in a Redis instance, updated every 6 hours. I guess that by now you know what I want to do.

What we can do to solve our problem is to create a materialized view that, once each row is inserted, will fetch from a dictionary connected to Redis the latest exchange rates, apply the conversion and store the results in a new table quote where all quotes are also converted to Euro.

How we can do it

This is our tables and dictionary definitions:

CREATE DATABASE IF NOT EXISTS quote;

use quote;

-- This is the DDL definition for the dictionary
-- that will fetch exchange rates from Redis

CREATE DICTIONARY IF NOT EXISTS exchange_rate
(
    currency String,
    rate Float32
)
PRIMARY KEY currency
SOURCE(REDIS(
    host 'redis'
    port 6379
    storage_type 'simple'
    db_index 0
))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600);

-- This table stores the data as is when reaches our system

CREATE TABLE IF NOT EXISTS quote_entry
(
    product_id UInt32,
    timestamp DateTime,
    quote Float32,
    currency LowCardinality(String)
)
ENGINE = Null;

-- This is the table where all quotes are converted to Euro

CREATE TABLE IF NOT EXISTS quote
(
    product_id UInt32,
    timestamp DateTime,
    quote Float32,
    currency LowCardinality(String),
    quote_eur Float32
)
ENGINE = Memory;

-- This is the materialized view that does the magic

CREATE MATERIALIZED VIEW IF NOT EXISTS quote_view
TO quote AS
SELECT
    quote_entry.product_id AS product_id,
    quote_entry.timestamp AS timestamp,
    quote_entry.quote AS quote,
    quote_entry.currency AS currency,
    round((quote_entry.quote * exchange_rate.rate), 2) AS quote_eur
FROM quote_entry LEFT JOIN exchange_rate
    ON quote_entry.currency = exchange_rate.currency;

Once the data is inserted into the table quote_entry the materialized view will join the inserted row with the dictionary, which has in memory the latest exchange rates, applies the conversion and saves the converted quote into the table quote.

Have a look at the dictionary DDL:

...
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600);

Make sure you read the documentation about the LAYOUT that best suits your case. If our primary key was an integer, most likely we would have chosen another one.

And the LIFETIME setting means that ClickHouse will keep the values of the dictionary in memory during that amount of seconds. Make sure you choose the one that matches your business requirements.

Also note that the table quote_entry has ENGINE = Null; which means that it really doesn’t store any data. It’s just there to trigger the materialized view.

Let’s now add some exchange rates to Redis so that the dictionary can fetch them. In Redis, do

mset USD 0.82 GBP 1.11

and now let’s see if ClickHouse can fetch this data

SELECT *
FROM quote.exchange_rate

┌─currency─┬─rate─┐
│ GBP      │ 1.11 │
│ USD      │ 0.82 │
└──────────┴──────┘

Now let’s insert a few thousand random entries with in quote_entry

INSERT INTO quote.quote_entry SELECT
    transform(number % 2, [0, 1], [1001, 1002], 0) AS product_id,
    now() AS timestamp,
    round(cbrt(rand()), 2) AS quote,
    transform(number % 2, [0, 1], ['USD', 'GBP'], '') AS currency
FROM numbers(1, 10000);

and check that the materialized view did its job:

SELECT *
FROM quote.quote
LIMIT 10

┌─product_id─┬───────────timestamp─┬───quote─┬─currency─┬─quote_eur─┐
│       1002 │ 2020-12-06 19:37:46 │ 1027.06 │ GBP      │   1140.04 │
│       1001 │ 2020-12-06 19:37:46 │ 1401.09 │ USD      │   1148.89 │
│       1002 │ 2020-12-06 19:37:46 │  723.13 │ GBP      │    802.67 │
│       1001 │ 2020-12-06 19:37:46 │  681.06 │ USD      │    558.47 │
│       1002 │ 2020-12-06 19:37:46 │  888.61 │ GBP      │    986.36 │
│       1001 │ 2020-12-06 19:37:46 │ 1478.06 │ USD      │   1212.01 │
│       1002 │ 2020-12-06 19:37:46 │ 1210.04 │ GBP      │   1343.14 │
│       1001 │ 2020-12-06 19:37:46 │ 1171.39 │ USD      │    960.54 │
│       1002 │ 2020-12-06 19:37:46 │   851.8 │ GBP      │     945.5 │
│       1001 │ 2020-12-06 19:37:46 │ 1185.65 │ USD      │    972.23 │
└────────────┴─────────────────────┴─────────┴──────────┴───────────┘

Everything seems to work as expected and now we can do all our aggregations entirely in euros.

Closing notes

I created a scripted demo that will spawn this scenario using docker-compose so you can play with it.

Find it at https://github.com/pachico/demo-clickhouse-redis-dictionaries

Hope you find it useful.