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