Clickhouse 连接Mysql创建字典
-- clickhouse 使用mysql作为dict字典表
-- mysql
CREATE TABLE ch_dict.dict_demo(
id bigint(20)PRIMARY KEY,
value_1 varchar(400),
value_2 varchar(400)
);
insert into dict_demo values (1,'v1','v2');
insert into dict_demo values (2,'vv1','vv2');
-- clickhouse
CREATE DICTIONARY dict.dict_demo (
id UInt64,
value_1 String DEFAULT '',
value_2 String DEFAULT ''
)
PRIMARY KEY id
SOURCE(MYSQL(
port 3306
user 'root'
password 'xxx'
replica(host 'mysql_host_address' priority 1)
db 'ch_dict'
table 'dict_demo'
invalidate_query 'select id from ch_dict.dict_demo where 1=0'
fail_on_connection_loss 'true'
))
LAYOUT(HASHED())
LIFETIME(MIN 1 MAX 10);
SELECT * FROM dict_demo;
SELECT dictGet('dict_demo', 'value_1', 2);
[1] Configuring an External Dictionaryl
[2] ClickHouse Tutorial
[2] Ext-dict-functionsl