ClickHouse – dictionary with string keys

i wanted to create a clickhouse dictionary that used String as a key, not an int. docs mention here: “A composite key can consist of a single element. This makes it possible to use a string as the key, for instance”. I’ve been trying this and failed few times. creation worked but i could not query the dictionary:

CREATE DICTIONARY tmp ( `id` UInt64, `short_name` String, `long_name` String) PRIMARY KEY short_name SOURCE(MYSQL(PORT 3306 HOST 'some.sql.server' DB 'db' TABLE 'some_table' USER 'login' PASSWORD 'pass')) LIFETIME(MIN 1800 MAX 3600) LAYOUT(HASHED());

select * from tmp;

0 rows in set. Elapsed: 0.036 sec.

Received exception from server (version 21.8.4):
Code: 1000. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Exception: Cannot parse unsigned integer: text, query: SELECT `short_name`, `id`, `long_name` FROM `db`.`some_table`;

it turned out that i using wrong layout – instead of “LAYOUT(HASHED());” i should have used “LAYOUT(COMPLEX_KEY_HASHED());”.

CREATE DICTIONARY tmp ( `id` UInt64, `short_name` String, `long_name` String) PRIMARY KEY short_name SOURCE(MYSQL(PORT 3306 HOST 'some.sql.server' DB 'db' TABLE 'some_table' USER 'login' PASSWORD 'pass')) LIFETIME(MIN 1800 MAX 3600) LAYOUT(HASHED());
SELECT * FROM tmp;
# works fine
SELECT dictGet('mydb.tmp', 'id', 'text')
# also works

Leave a Reply

Your email address will not be published. Required fields are marked *

 

(Spamcheck Enabled)