clickhouse数据字典
前言：
clickhouse的安装见
https://blog.csdn.net/weixin_41463944/article/details/142534145?spm=1001.2014.3001.5501
1、需要在路径/etc/clickhouse-serve下定义*_dictionary.xml结尾的配置文件
2、需要准备organization.csv和sales.csv本地测试文件（注意不是windows下的xlsx表格，是linux下的文件）
2.1 organization.csv文件
1,"a0001","研发部"
2,"a0002","产品部"
3,"a0003","数据部"
4,"a0004","测试部"
5,"a0005","运维部"
6,"a0006","规划部",
7,"a0007","市场部"
2.2 sales.csv文件
1,2016-01-01,2017-01-10,100
2,2016-05-01,2017-07-01,200
3,2014-03-05,2018-01-20,300
4,2018-08-01,2019-10-01,400
5,2017-03-01,2017-06-01,500
6,2017-04-09,2018-05-30,600
7,2018-06-01,2019-01-25,700
8,2019-08-01,2019-12-12,800
3、关于每个xml字典文件的格式
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>字典名称（全局唯一）</name>
<source>
数据源配置
</source>
<layout>
字典数据在内存中的格式
</layout>
<structure>
字典的数据结构
</structure>
<lifetime>
字典的自动更新频率
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
4、关于扩展字典的几种类型配置
一共7种类型，其中flat、hashed、sparse_hashed、range_hashed、cache的主键为数值型UInt64类型的，其他类型如complex_key_hashed、complex_key_cache为复合类型的key
4.1）flat类型的字典，在/etc/clickhouse-serve目录下新建test_flat_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_flat_dict</name>
<source>
<file>
<path>/home/clickhouse-server/etc/clickhouse-server/dictionary/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<flat/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
4.2）hashed类型的字典，在/etc/clickhouse-serve目录下新建test_hashed_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_hashed_dict</name>
<source>
<file>
<path>/home/clickhouse-server/etc/clickhouse-server/dictionary/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<hashed/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<attribute>
<name>code</name>
<type>String</type>
<null_value></null_value>
</attribute>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
4.3）complex_key_hashed类型的字典，在/etc/clickhouse-serve目录下新建complex_key_hashed_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_complex_key_hashed_dict</name>
<source>
<file>
<path>/home/clickhouse-server/etc/clickhouse-server/dictionary/organization.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<complex_key_hashed/>
</layout>
<structure>
<key>
<attribute>
<name>id</name>
<type>UInt64</type>
</attribute>
<attribute>
<name>code</name>
<type>String</type>
</attribute>
</key>
<attribute>
<name>name</name>
<type>String</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
4.4）range_hashed类型的字典，在/etc/clickhouse-serve目录下新建test_range_hashed_dictionary.xml
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>test_range_hashed_dict</name>
<source>
<file>
<path>/home/clickhouse-server/etc/clickhouse-server/dictionary/sales.csv</path>
<format>CSV</format>
</file>
</source>
<layout>
<range_hashed/>
</layout>
<structure>
<id>
<name>id</name>
</id>
<range_min>
<name>start</name>
</range_min>
<range_max>
<name>end</name>
</range_max>
<attribute>
<name>price</name>
<type>Float32</type>
<null_value></null_value>
</attribute>
</structure>
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
</dictionary>
</dictionaries>
5、关于字典的数据源
5.1 文件类型，格式通常为CSV或者TabSeparated
1）本地文件
<source>
<file>
<path>/home/clickhouse-server/etc/clickhouse-server/dictionary/organization.csv</path>
<format>CSV</format>
</file>
</source>
2）可执行文件
<source>
<executable>
<command>/home/clickhouse-server/etc/clickhouse-server/dictionary/organization.csv</command>
<format>CSV</format>
</executable>
</source>
3）远程文件
<source>
<http>
<url>xxxxx.csv</url>
<format>CSV</format>
</http>
</source>
5.2 数据库类型的数据源
1）mysql
<source>
<mysql>
<port>3306</port>
<user>xxxx</user>
<password>xxxx</password>
<replica>
<host>localhost</host>
<priority>1</priority>
</replica>
<db>db_mybatis</db>
<table>userList</table>
</mysql>
</source>
其中<mysql> 标签的下一级可添加 <where>is_deleted=0</where>做为筛选条件
2）clickhouse
<source>
<clickhouse>
<host>localhost</host>
<port>9000</port>
<user>xxxx</user>
<password>xxxxx</password>
<db>my_database</db>
<table>test_table</table>
</clickhouse>
</source>
6、关于DDL语句
dictGet(‘字典名称’,‘字典的具体字段’,‘key键值’)，如果字典为复合key的话，则在字典函数中的key用元组tuple(‘a’,‘b’,‘c’)表示
7、关于手动刷新数据字典
7.1 ）全局字典刷新
system reload dictionaries
7.2）刷新单个字典（字典名称不加单引号）
system reload dictionary test_mysql_dict
原文地址：https://blog.csdn.net/weixin_41463944/article/details/142719985
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！