数据字典是clickhouse提供的一种简单 实用的存储媒介,以键值和属性映射的形式定义数据。字典中的数据会主动或被动加载到内存之中,并支持动态更新。由于字典数据常驻内存特特性,比较适合保存常量或者经常使用的维度表数据,以避免不必要的JOIN数据。
数据字典分为内置和扩展两种形式,内置数据字典是以clickhouse默认自带的字典;外部字典是通过用户自定义配置实现的字典。
数据字典的DDL:
在此之前需要创建大量的XML文件,在最新的ClickHouse 20.1版本中引入了CREATE DICTIONARY语句,至少需要20.1.11.73+版本,下面是数据字典的用法:
1. 创建数据库
> CREATE DATABASE IF NOT EXISTS database_dict;
2.字典表.
> CREATE TABLE database_dict.c_dict
(
id UInt64,
dict_key UInt64,
dict_value String,
remark String
)
ENGINE = MergeTree ()
PRIMARY KEY id -- PRIMARY KEY 决定了一级索引(primary.idx)
ORDER BY id -- ORDER BY 决定了每个分区中数据的排序规则
SETTINGS index_granularity = 8192; -- 其实不写也可以,在每个data part中,索引粒度参数的含义有二:1.每隔index_granularity行对主键组的数据进行采样,形成稀疏索引,并存储在primary.idx文件中;
2.每隔index_granularity行对每一列的压缩数据([column].bin)进行采样,形成数据标记,并存储在[column].mrk文件中。
-- -------------------------------------------------------------------------------
3.clickhouse数据字典DDL
> CREATE DICTIONARY ch_dict
(
dict_key UInt64,
dict_value String
)
PRIMARY KEY dict_key
SOURCE(CLICKHOUSE(HOST '8.141.49.51' PORT 9000 USER 'default' TABLE 'c_dict' PASSWORD '1qaz@wsx' DB 'database_dict'))
LIFETIME(MIN 1 MAX 10)
LAYOUT(HASHED());
4.新建一张测试表.
> CREATE TABLE c_user
(
id UInt64,
sex UInt64,
user_name String,
idcard String,
create_time DateTime
)
ENGINE=MergeTree()
PARTITION BY toYYYYMM ( create_time )
ORDER BY id
SETTINGS index_granularity = 8192;
5. c_dict插入数据
>INSERT INTO database_dict.c_dict (id, dict_key, dict_value, remark) VALUES(0, 0, '女', '0:女');
>INSERT INTO database_dict.c_dict (id, dict_key, dict_value, remark) VALUES(1, 1, '男', '1:男');
6. c_user插入数据
>INSERT INTO database_dict.c_user (id, sex, user_name, idcard, create_time) VALUES(0, 0, '张晓梅', '13129312983891', '2021-06-01 10:20:30');
>INSERT INTO database_dict.c_user (id, sex, user_name, idcard, create_time) VALUES(0, 1, '张三', '43172682772627272', '2021-06-01 10:21:30');
7. 使用dictString查询:
SELECT
id,
dictGetString ( 'database_dict.ch_dict', 'dict_value', sex ) AS sexValue ,
user_name,
idcard,
create_time
FROM
database_dict.c_user cu
WHERE
id = 0;
步骤7执行结果:
dictGetString函数
网友评论