# 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](/product/dbaas/service-specific/clickhouse/overview/disaster-recovery/#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](https://clickhouse.com/docs/reference/statements/create/dictionary).

## Prerequisites

- Managed ClickHouse service created
- SQL client installed
- [Dictionary source](/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/#supported-sources)
  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:

  ```sql
  -- 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`](https://clickhouse.com/docs/reference/settings/server-settings/settings/dictionaries#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](https://clickhouse.com/docs/reference/statements/create/dictionary/layouts/overview)
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:

```sql
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 `JOIN`s

1. Create tables in your ClickHouse database:

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

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

1. Create a dictionary for the `users` table:

    ```sql
    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:

    ```sql
    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);
    ```

`JOIN`s are much faster as the data is pre-indexed in memory.

```sql
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](/product/dbaas/service-specific/clickhouse/quick-start/#query-data) 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:

   ```sql
   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');
   ```

1. Create the dictionary on top of it:

   ```sql
   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);
   ```

1. Use `dictGet` directly in queries over `trips`:

   ```sql
   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:

  ```sql
  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:

  ```sql
  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:

  ```sql
  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:

  ```sql
  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:

  ```sql
  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](/product/dbaas/service-specific/clickhouse/how-to/create-dictionary/#limitations),
  > `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:

  ```sql
  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);
  ```