Create dictionaries in Managed ClickHouse®

Create dictionaries in Managed ClickHouse® to accelerate queries for better efficiency and performance.

Dictionaries in Managed ClickHouse

A dictionary is a key-attribute mapping useful for low latency lookup queries, when often looking up attributes for a particular key. Dictionary data resides fully in memory, which is why using a dictionary in JOINs is often much faster than using a MergeTree table. Dictionaries can be an efficient replacement for regular tables in your JOIN clauses.

Managed ClickHouse supports backup and restore for dictionaries. Also, dictionaries in Managed ClickHouse are automatically replicated to all service nodes.

Read more on dictionaries in the upstream ClickHouse documentation.

Prerequisites

• Managed ClickHouse service created
• SQL client installed
• Dictionary source available, with its hostname, port and credentials if the source is an external database

Limitations

• Only TLS connections supported

• If no host is specified in a dictionary with a ClickHouse source, the local host is assumed, and the dictionary is filled with data from a query against the local ClickHouse, for example:

  -- users table
  CREATE TABLE IF NOT EXISTS default.users
  (
      id UInt64,
      username String,
      email String,
      country String
  )
  ENGINE = MergeTree()
  ORDER BY id;
  CREATE OR REPLACE DICTIONARY default.users_dictionary
  (
      id UInt64,
      username String,
      email String,
      country String
  )
  PRIMARY KEY id
  SOURCE(CLICKHOUSE(DB 'default' TABLE 'users'))
  LAYOUT(FLAT())
  LIFETIME(100);

  In Managed ClickHouse, to fill the dictionary the table users are queried with the permissions of the avnadmin user even if another user creates the dictionary. In upstream ClickHouse, the same is true except the default user is used.

• In Managed ClickHouse, the dictionaries_lazy_load setting is set to true, which means that errors with dictionary source parameters may only become apparent when the dictionary is loaded on the first use, rather than when it is created.

Supported layouts

Managed ClickHouse supports the same layouts that the upstream ClickHouse supports with two exceptions,ssd_cache and complex_key_ssd_cache, which are not supported.

Supported sources

• HTTP(s)
• Remote ClickHouse
• Managed ClickHouse
• Remote MySQL®
• Managed MySQL
• Remote PostgreSQL®
• Managed PostgreSQL

Create a dictionary

To create a dictionary with specified structure (attributes), source, layout, and lifetime, use the following syntax:

CREATE [OR REPLACE] DICTIONARY [IF NOT EXISTS] [db.]dictionary_name
(
    key1 type1  [DEFAULT|EXPRESSION expr1] [IS_OBJECT_ID],
    key2 type2  [DEFAULT|EXPRESSION expr2],
    attr1 type2 [DEFAULT|EXPRESSION expr3] [HIERARCHICAL|INJECTIVE],
    attr2 type2 [DEFAULT|EXPRESSION expr4] [HIERARCHICAL|INJECTIVE]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME([param1 value1 ... paramN valueN]))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME({MIN min_val MAX max_val | max_val})
SETTINGS(setting_name = setting_value, setting_name = setting_value, ...)
COMMENT 'Comment'

Examples

Speeding up JOINs

1. Create tables in your ClickHouse database:

   CREATE TABLE users
   (
       id UInt64,
       username String,
       email String,
       country String
   )
   ENGINE = MergeTree()
   ORDER BY id;

   CREATE TABLE transactions
   (
       id UInt64,
       user_id UInt64,
       product_id UInt64,
       quantity Float64,
       price Float64
   )
   ENGINE = MergeTree()
   ORDER BY id;

2. Create a dictionary for the users table:

   CREATE DICTIONARY users_dictionary
   (
       id UInt64,
       username String,
       email String,
       country String
   )
   PRIMARY KEY id
   SOURCE(CLICKHOUSE(DB 'default' TABLE 'users'))
   LAYOUT(FLAT())
   LIFETIME(100);

   You can do the same using the QUERY parameter:

   CREATE OR REPLACE DICTIONARY users_dictionary
   (
       id UInt64,
       username String,
       email String,
       country String
   )
   PRIMARY KEY id
   SOURCE(CLICKHOUSE(QUERY 'SELECT id, username, email, country FROM default.users'))
   LAYOUT(FLAT())
   LIFETIME(100);

JOINs are much faster as the data is pre-indexed in memory.

SELECT
    t.id,
    u.username,
    t.product_id,
    t.quantity,
    t.price
FROM transactions AS t
ANY LEFT JOIN users_dictionary AS u
ON t.user_id = u.id;

Mapping the taxi zones of the quick start

The quick start leaves off with neighborhood identifiers such as dropoff_nyct2010_gid in the trips table. A dictionary turns them into names without a JOIN in every query.

1. Create the mapping table and load a few zones:

   CREATE TABLE default.taxi_zones (
       gid UInt64,
       zone_name String
   )
   ENGINE = MergeTree
   ORDER BY gid;
   
   INSERT INTO default.taxi_zones VALUES
       (132, 'JFK Airport'),
       (138, 'LaGuardia Airport'),
       (161, 'Midtown Center'),
       (237, 'Upper East Side South');

2. Create the dictionary on top of it:

   CREATE DICTIONARY default.taxi_zones_dict (
       gid UInt64,
       zone_name String
   )
   PRIMARY KEY gid
   SOURCE(CLICKHOUSE(DB 'default' TABLE 'taxi_zones'))
   LAYOUT(FLAT())
   LIFETIME(600);

3. Use dictGet directly in queries over trips:

   SELECT
       dictGet('default.taxi_zones_dict', 'zone_name', toUInt64(dropoff_nyct2010_gid)) AS zone,
       count() AS trips
   FROM default.trips
   WHERE dropoff_nyct2010_gid IN (132, 138)
   GROUP BY zone

   On the quick-start dataset, this returns 27490 trips ending at JFK Airport and 17809 at LaGuardia Airport.

Caching data from an external database or URL

• Create a dictionary for the pricing table in your MySQL database using a composite key:

  CREATE DICTIONARY product_pricing
  (
      product_id UInt64,
      region String,
      price Float64 DEFAULT 0.0
  )
  PRIMARY KEY product_id, region
  SOURCE(MYSQL(HOST 'mysql.example.com' PORT 3306 USER 'app' PASSWORD 'PASSWORD' DB 'product_db' TABLE 'pricing'))
  LAYOUT(COMPLEX_KEY_HASHED())
  LIFETIME(MIN 600 MAX 900);

  This will periodically query MySQL and store the data in memory.

• Create a dictionary for the pricing table in your PostgreSQL database using the FLAT layout:

  CREATE DICTIONARY product_pricing
  (
      product_id UInt64,
      price Float64 DEFAULT 0.0
  )
  PRIMARY KEY product_id
  SOURCE(POSTGRESQL(HOST 'pg.example.com' PORT 5432 USER 'app' PASSWORD 'PASSWORD' DB 'product_db' SCHEMA 'public' TABLE 'pricing'))
  LAYOUT(FLAT())
  LIFETIME(0);

  Because LIFETIME is 0, it has to be manually refreshed as follows:

  SYSTEM RELOAD DICTIONARY product_pricing;

• Create a dictionary with HTTP as a source. The FORMAT value matches the layout of the served file, for example CSVWithNames for a CSV file whose first line holds the column names:

  CREATE DICTIONARY currency_rates
  (
      currency_code String,
      rate Float64 DEFAULT 1.0
  )
  PRIMARY KEY currency_code
  SOURCE(HTTP(URL 'https://example.com/currency_rates.csv' FORMAT 'CSVWithNames'))
  LAYOUT(COMPLEX_KEY_HASHED())
  LIFETIME(100);

  Look values up with dictGet, test whether a key is present with dictHas, and supply the value to return for a missing key with dictGetOrDefault. Because the layout is a complex-key one, the key is passed as a tuple:

  SELECT
      dictGet('currency_rates', 'rate', tuple('USD')) AS usd_rate,
      dictHas('currency_rates', tuple('XYZ')) AS xyz_known,
      dictGetOrDefault('currency_rates', 'rate', tuple('XYZ'), 0.0) AS xyz_rate

  To load the file once and never refresh it automatically, use LIFETIME(MIN 0 MAX 0) and reload the dictionary manually when the file changes.

  Note

  Because dictionaries_lazy_load is enabled, CREATE DICTIONARY succeeds even when the URL is unreachable or the format is wrong: the source is only contacted when the dictionary is first loaded, so the first dictGet is what fails. To surface a broken source right away, force the load with SYSTEM RELOAD DICTIONARY currency_rates.

• Create a dictionary for the users table in a remote ClickHouse database using the FLAT layout:

  CREATE DICTIONARY users_dictionary_remote
  (
      id UInt64,
      username String,
      email String,
      country String
  )
  PRIMARY KEY id
  SOURCE(CLICKHOUSE(HOST 'remote.example.com' PORT 21699 SECURE 1 USER 'avnadmin' PASSWORD 'PASSWORD' DB 'default' TABLE 'users'))
  LAYOUT(FLAT())
  LIFETIME(100);