简介

  • 常驻内存,支持动态更新
  • 适合保存常量和经常使用的维度表数据
  • 可通过字典函数访问,也可通过袋里表与其他数据表实现 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;