clickhouse创建外部字典表

本次使用mysql的数据，利用ck的Dictionary 引擎创建外部字典表

1.创建配置XML

​ 将外部字典配置文件拷贝到每台集群服务器clickhouse的配置目录下：

/etc/clickhouse-server/下，和config.xml同级，配置如下：

以设备字典为例device_dictionary.xml

<dictionaries><dictionary><name>device</name><source><mysql><port>3306</port><user>root</user><password>12345</password><replica><host>193.123.33.10</host><priority>1</priority></replica><db>map</db><table>device</table></mysql></source><lifetime># 字典数据存活时间<min>300</min><max>360</max></lifetime><layout><hashed /></layout><structure><id><name>id</name></id><attribute><name>typeid</name><type>UInt8</type><null_value></null_value></attribute><attribute><name>arid</name><type>UInt64</type><null_value></null_value></attribute><attribute><name>remark</name><type>String</type><null_value></null_value></attribute></structure></dictionary>
</dictionaries>

2.建表语句

CREATE TABLE db.device_all on cluster comm_cluster (
id UInt64 COMMENT '设备id',
typeid UInt8,
areaid UInt64 COMMENT '设备地址id',
remark String)ENGINE = Dictionary(device);