256 lines
7.8 KiB
Markdown
256 lines
7.8 KiB
Markdown
---
|
||
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;
|
||
```
|
||
|