ClickHouse 数据字典

简介

常驻内存，支持动态更新
适合保存常量和经常使用的维度表数据
可通过字典函数访问，也可通过袋里表与其他数据表实现 JOIN 查询

内置字典

默认禁用
不想写了，没啥意思

外部扩展字典

配置文件

位置: /etc/clickhouse-server/*_dictionary.xml
自动感知变更，不停机在线更新
系统表: system.dictionaries

配置结构

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177


<?xml version="1.0"?>
<dictionaries>
  <dictionary>
    <!-- 字典名称，全局唯一 -->
    <name>dict_name</name>

    <!-- 字典数据结构 -->
    <structure>
      <!-- 数值 key，UInt64，支持字典类型: flat、hashed、range_hashed、cache -->
      <id>
        <name>field_name</name>
      <id>

      <!-- 复合 key，Tuple，类似复合主键，支持字典类型: complex_key_hashed、complex_key_cache -->
      <key>
        <attribute>
          <name>field_name</name>
          <type>field_type</type>
        </attribute>
        <attribute>
          ...
        </attribute>
      </key>

      <!-- 字符串 key，String，支持字典类型: ip_trie -->
      <key>
        <attribute>
          <name>field_name</name>
          <type>String</type>
        </attribute>
      </key>

      <!-- 只在 range_hashed 字典类型中使用 -->
      <range_min>
        <name>field_name</name>
      </range_min>
      <range_max>
        <name>field_name</name>
      </range_max>

      <!-- 字典属性 -->
      <attribute>
        <!-- 字段名称，必填 -->
        <name>field_name</name>

        <!-- 字段类型，必填 -->
        <type>field_type</type>

        <!-- 查询时，key 无对应字段时的默认值，必填，这里指定的是空字符串 -->
        <null_value></null_value>

        <!-- 函数或运算符表达式，非必填，默认无表达式 -->
        <expression></expression>

        <!-- 是否支持层次结构，非必填，默认 false -->
        <hierarchical>false</hierarchical>

        <!-- 是否支持单射优化，非必填，默认 false -->
        <injective>false</injective>

        <!-- 是否开启 MongoDB 优化，非必填，默认 false -->
        <is_object_id>false</is_object_id>
      </attribute>
    </structure>

    <!-- 在内存中的数据格式类型 -->
    <layout>
      <!-- 性能最高，只能用数值 key，数组结构保存，初始容量 1024，上限 500000
        支持数据源: Local file、Executable file、HTTP、DBMS -->
      <flat/>

      <!-- 只能用数值 key，散列结构保存，无存储上限
        支持数据源: Local file、Executable file、HTTP、DBMS -->
      <hashed/>

      <!-- 只能用数值 key，散列结构保存并按时间排序，无存储上限
        range_min 和 range_max 指定时间区间字段，且字段必须是 Date 或 DateTime 类型
        支持数据源: Local file、Executable file、HTTP、DBMS -->
      <range_hashed/>

      <!-- 只能用数值 key，固定大小(size_in_cells)数组存储
        cells 数组未缓存的数据在查询时才会加载并缓存到 cells 中，性能不稳定
        支持数据源: Executable file、HTTP、ClickHouse、MySQL -->
      <cache>
        <!-- 缓存大小，视内存而定 -->
        <size_in_cells>16384</size_in_cells>
      </cache>

      <!-- 只能用复合 key，其他与 hashed 一样 -->
      <complex_key_hashed/>

      <!-- 只能用复合 key，其他与 cache 一样 -->
      <complex_key_cache>
        <size_in_cells>16384</size_in_cells>
      </complex_key_cache>

      <!-- 只能用单个 String 字段，trie 树结构，专用语 IP 前缀查询
        支持数据源: Local file、Executable file、HTTP、DBMS -->
      <ip_trie/>
    </layout>

    <!-- 数据源 -->
    <source>
      <!-- 本地文件，如果文件修改时间出现变动，会出发数据更新 -->
      <file>
        <path>/path/to/data.csv</path>
        <format>CSV</format>
      </file>

      <!-- 可执行文件，如果文件修改时间出现变动，会出发数据更新 -->
      <executable>
        <command>cat /path/to/data.csv</command>
        <format>CSV</format>
      </executable>

      <!-- 远程文件，支持 http 和 https 协议，post 请求，如果文件修改时间出现变动，会出发数据更新 -->
      <http>
        <url>http://192.168.1.2:9080/data.csv</url>
        <format>CSV</format>
      </http>

      <!-- mysql -->
      <mysql>
        <user>root</root>
        <password>123456<password>
        <replica>
          <host>192.168.1.3</host>
          <priority>1</priority>
        </replica>
        <port>3306</port>
        <db>db_name</db>
        <table>table_name</table>

        <!-- 查询过滤条件，非必填 -->
        <where>id=1</where>

        <!-- 指定一条 sql 语句，如果返回结果和上一次不一样，则更新，非必填 -->
        <invalidate_query>sql</invalidate_query>
      </mysql>

      <!-- clickhouse -->
      <clickhouse>
        <user>default</root>
        <password><password>
        <host>192.168.1.4</host>
        <port>9000</port>
        <db>default</db>
        <table>table_name</table>
        <where>id=1</where>
        <invalidate_query>sql</invalidate_query>
      </clickhouse>

      <!-- mongodb -->
      <mongodb>
        <user></root>
        <password><password>
        <host>192.168.1.5</host>
        <port>27017</port>
        <db>db_name</db>
        <colection>collection_name</collection>
      </mongodb>

      <!-- odbc 连接其他数据库 ... -->
    </source>

    <!-- 字典自动更新频率，单位秒，min 和 max 都是 0 表示禁用更新
      在 cache 字典中还代表缓存失效时间
      字典更新时旧版本依旧提供服务，新版本完全更新成功后才会替代就版本 -->
    <lifetime>
      <min>300</min>
      <max>360</max>
    </lifetime>
  </dictionary>
  <dictionary>
      ...
  </dictionary>
</dictionaries>

操作

手动更新全部数据字典
1

SYSTEM RELOAD DICTIONARIES;
手动更新指定字典
1

SYSTEM RELOAD DICTIONARY dict_name

查看所有字典信息

1

SELECT name, type, key, attribute.names, attribute.types source FROM system.dictionaries;

字典函数查询

1

SELECT dictGet('dict_name', 'attr_name', key)

其他字典函数
- 无符号整型: dictGetUInt8、dictGetUInt16、dictGetUInt32、dictGetUInt64
- 整型: dictGetInt8、dictGetInt16、dictGetInt32、dictGetInt64
- 浮点数: dictGetFloat32、dictGetFloat64
- 字符串: ditGetString、dictGetUUID
- 日期: dictGetDate、dictGetDateTime

ddl 创建字典

1
2
3
4
5
6


CREATE DICTIONARY dict_name(
    ...
) PRIMARY KEY id
LAYOUT(FLAT())
SOURCE(FILE(PATH '/path/to/data.csv' FORMAT CSV))
LIFETIME(1);

Dictionary 表引擎

创建字典表

1
2
3


CREATE TABLE table_name(
    ...
) ENGINE = Dictionary(dict_name);

dict_name: 已加载的字典名称
创建字典数据库，自动为每个字典创建对应的字典表
1

CREATE DATABASE dict_db ENGINE = Dictionary;

文章目录

简介

内置字典

外部扩展字典

配置文件

操作

Dictionary 表引擎