Question
How to create a ClickHouse dictionary using string keys and string values from a MergeTree table source
Answer
- Create the source table for the dictionary
CREATE TABLE db1.table1_dict_source
(
id UInt32,
email String,
name String
)
ENGINE = MergeTree()
ORDER BY id;
- Insert rows
INSERT INTO db1.table1_dict_source
(id, email, name)
VALUES
(1, 'me@domain.com', 'me'),
(2, 'you@domain.com', 'you');
- Create dictionary with key/value both as String
CREATE DICTIONARY db1.table1_dict
(
email String,
name String
)
PRIMARY KEY email
SOURCE(
CLICKHOUSE(
TABLE 'table1_dict_source'
USER 'default'
PASSWORD 'ClickHouse123!'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 0 MAX 1000);
- Test the dictionary
clickhouse-cloud :) SELECT * from db1.table1_dict;
SELECT *
FROM db1.table1_dict
Query id: 098396ce-11dd-4c71-a0e1-40723dd67ddc
┌─email──────────┬─name─┐
│ me@domain.com │ me │
│ you@domain.com │ you │
└────────────────┴──────┘
2 rows in set. Elapsed: 0.001 sec.
You can also use dictGet function to retrieve values from it such as:
SELECT dictGet('db1.table1_dict', 'name', 'me@domain.com');
Response:
┌─dictGet('db1.table1_dict', 'name', 'me@domain.com')─┐
│ me │
└─────────────────────────────────────────────────────┘
More details - https://clickhouse.com/docs/en/sql-reference/functions/ext-dict-functions