---
title: "ClickHouse 数据字典"
date: 2020-09-23T13:30:00+08:00
lastmod: 2020-10-08T18:50:00+08:00
tags: []
categories: ["clickhouse"]
---

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

## 内置字典
- 默认禁用
- 不想写了，没啥意思

## 外部扩展字典
### 配置文件
- 位置: /etc/clickhouse-server/\*\_dictionary.xml
- 自动感知变更，不停机在线更新
- 系统表: system.dictionaries
- 配置结构
    ```xml
    <?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>
    ```

### 操作
- 手动更新全部数据字典
    ```sql
    SYSTEM RELOAD DICTIONARIES;
    ```

- 手动更新指定字典
    ```sql
    SYSTEM RELOAD DICTIONARY dict_name
    ```

- 查看所有字典信息
    ```sql
    SELECT name, type, key, attribute.names, attribute.types source FROM system.dictionaries;
    ```

- 字典函数查询
    ```sql
    SELECT dictGet('dict_name', 'attr_name', key)
    ```

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

- ddl 创建字典
    ```sql
    CREATE DICTIONARY dict_name(
        ...
    ) PRIMARY KEY id
    LAYOUT(FLAT())
    SOURCE(FILE(PATH '/path/to/data.csv' FORMAT CSV))
    LIFETIME(1);
    ```

## Dictionary 表引擎
- 创建字典表
    ```sql
    CREATE TABLE table_name(
        ...
    ) ENGINE = Dictionary(dict_name);
    ```

- dict_name: 已加载的字典名称
- 创建字典数据库，自动为每个字典创建对应的字典表
    ```sql
    CREATE DATABASE dict_db ENGINE = Dictionary;
    ```