Clickhouse Dictionaries 使用样例

Clickhouse Dictionaries 使用样例

Clickhouse Dictionaries 字典使用

clickhouse支持从各种数据源添加自己的字典。字典的数据源可以是本地文本、可执行文件
、HTTP（s）资源或其他DBMS。有关更多信息，请参阅“外部词典的来源”。

• 完全或部分将字典存储在RAM中。
• 定期更新字典并动态加载缺失值。换句话说，可以动态加载字典。

外部词典的配置位于一个或多个文件中。 配置的路径在dictionaries_config参数中指定。

字典配置：

*_dictionary.xml

当前配置文件目录下，以_dictionary.xml结尾的均作为字典配置文件进行字典加载。
字典可以在服务器启动时或首次使用时加载，具体取决于dictionaries_lazy_load设置。

字典懒加载

true

如果设置为true, 每个字典只在第一次使用时创建. 如果字典创建失败,则会在调用字典函数时抛出异常.
如果设置为false,所有字典则会在服务器启动时加载，如果加载过程中有异常，则会shutdown 服务。
以mysql为例，尝试下Clickhouse的字典功能。最后的配置附录中包括：

• 以 flat形式存储的Numeric keyKey类型的字典
• 以 complex_key_hashed形式存储的Composite keyKey类型的字典

  建立外部字典

  MYSQL 中建立测试表test_dict，并添加3条记录

  ```sql SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;

— Table structure for test_dict

DROP TABLE IF EXISTS test_dict; CREATE TABLE test_dict ( id int(11) NOT NULL, code varchar(20) NOT NULL, name varchar(200) DEFAULT NULL, status tinyint(4) NOT NULL DEFAULT ‘1’, PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

— Records of test_dict

BEGIN; INSERT INTO test_dict VALUES (1, ‘f’, ‘女性’, 1); INSERT INTO test_dict VALUES (2, ‘m’, ‘男性’, 1); INSERT INTO test_dict VALUES (3, ‘cat’, ‘猫’, 1); COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

<a name="IFa9E"></a>
#### 在clickhouse 配置文件目录建立字典配置文件
> vi tmysql_dictionary.xml
`tmysql_dictionary.xml`内容如下：
```xml
<dictionaries>
    <dictionary>
        <name>dicts</name><!--字典名称-->
        <source>
            <mysql><!--dbms类型：mysql-->
                <replica><!--mysql 服务器，多台/主从可以配置多个-->
                    <host>116.255.199.xx</host>
                    <priority>1</priority><!--优先级-->
                </replica>
                <port>3306</port><!--端口-->
                <user>xxxx</user><!--mysql用户名-->
                <password>xxxxx</password><!--mysql 密码-->
                <db>db_benchmark</db><!--数据库-->
                <table>test_dict</table><!--表-->
                <where>status=1</where><!--过滤条件-->
                <invalidate_query>select count(*) from test_dict where status=1</invalidate_query><!--如果该查询结果发生变更，则更新字典-->
            </mysql>
        </source>
        <lifetime><!--clickhouse 会在这个时间间隔范围内，随机均匀时间进行字典升级（负载均衡），避免大量同时升级字典源-->
            <min>300</min>
            <max>360</max>
        </lifetime>
        <layout><!--字典在内存中存储方式；flat：二维数组(flat arrays),其他类型见文档-->
            <flat/>
        </layout>
        <structure>
            <id>
                <name>id</name><!--主键，好像必须是正整数型(UInt64)-->
            </id>
            <attribute><!--其他属性-->
                <name>code</name><!--属性名称-->
                <type>String</type><!--属性类型-->
                <null_value></null_value><!--null值替换-->
            </attribute>
            <attribute>
                <name>name</name>
                <type>String</type>
                <null_value></null_value>
            </attribute>
        </structure>
    </dictionary>
</dictionaries>

重新启动clickhouse服务，测试

clickhouse-server –config-file=/etc/clickhouse-server/config.xml clickhouse-client -m

select * from system.dictionaries where name =’dicts’;

字典的使用

字典函数

具体函数见：ext_dict_functions

当需要对字典使用原始数据或者执行join操作时，则可以使用Dictionary engine建立字典视图
创建字典视图语法：

CREATETABLE %table_name% (%fields%) engine = Dictionary(%dictionary_name%)`

create table test_dicts(
  id UInt64,
  code String
  ,name String
) 
engine=Dictionary('dicts');
select * from test_dicts;

配置文件附录

• 以 flat形式存储的Numeric key型的字典
• 以 complex_key_hashed形式存储的Composite keyKey类型的字典

vim tmysql_dictionary.xml

<dictionaries>
    <dictionary>
        <name>dicts</name>
        <source>
            <mysql>
                <replica>
                    <host>116.255.199.xx</host>
                    <priority>1</priority>
                </replica>
                <port>3306</port>
                <user>db_benchmark</user>
                <password>password</password>
                <db>db_benchmark</db>
                <table>test_dict</table>
                <where>status=1</where>
                <invalidate_query>select count(*) from test_dict where status=1</invalidate_query>
            </mysql>
        </source>
        <lifetime>
            <min>300</min>
            <max>360</max>
        </lifetime>
        <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>
    </dictionary>
    <dictionary>
        <name>mydicts</name>
        <source>
            <mysql>
                <replica>
                    <host>116.255.199.xx</host>
                    <priority>1</priority>
                </replica>
                <port>3306</port>
                <user>db_benchmark</user>
                <password>password</password>
                <db>db_benchmark</db>
                <table>test_complex_key_dict</table>
                <where></where>
                <invalidate_query>select count(*) from test_complex_key_dict</invalidate_query>
            </mysql>
        </source>
        <lifetime>
            <min>100</min>
            <max>160</max>
        </lifetime>
        <layout>
            <complex_key_hashed />
        </layout>
        <structure>
            <key>
                <attribute>
                    <name>code</name>
                    <type>String</type>
                </attribute>
            </key>
            <attribute>
                <name>value</name>
                <type>String</type>
                <null_value>??</null_value>
            </attribute>
        </structure>
    </dictionary>
</dictionaries>