www.colben.cn/ch-datadict.md at master

colben/www.colben.cn

Fork 0

colben 1e344dc204 update

2021-11-14 15:52:46 +08:00

7.8 KiB

Raw Permalink Blame History

title, date, lastmod, tags, categories

title

date

lastmod

简介

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

内置字典

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

外部扩展字典

配置文件

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

配置结构

<?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>

操作

手动更新全部数据字典
```
SYSTEM RELOAD DICTIONARIES;
```
手动更新指定字典
```
SYSTEM RELOAD DICTIONARY dict_name
```

查看所有字典信息

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

字典函数查询

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

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

ddl 创建字典

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

Dictionary 表引擎

创建字典表

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

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

7.8 KiB Raw Permalink Blame History Unescape Escape

简介

内置字典

外部扩展字典

配置文件

操作

Dictionary 表引擎

7.8 KiB

Raw Permalink Blame History