Clickhouse 数据字典

运行环境:
Clickhouse 20.4.5.36
CentOS 7.6

数据字典概述:

数据字典是clickhouse提供的一种简单 实用的存储媒介,以键值和属性映射的形式定义数据。字典中的数据会主动或被动加载到内存之中,并支持动态更新。由于字典数据常驻内存特特性,比较适合保存常量或者经常使用的维度表数据,以避免不必要的JOIN数据。

数据字典分为内置和扩展两种形式,内置数据字典是以clickhouse默认自带的字典;外部字典是通过用户自定义配置实现的字典。

在正常情况下字典中的数据只能通过字典函数访问,但是也有例外,即特殊的字典表引擎。在字典表引擎的帮助下 可以将数据字典挂载到一张代理的数据表下,从而实现数据表和自带数据的JOIN查询。

 

Clickhouse是外部数据字典来处理多维数据架构,早期的clickhouse版本适用XML配置,在新版本中ClickHouse有了显着的进步,字典也达到了新的实用水平。

########################### 数据字典 #######################
总览
ClickHouse外部词典是内存中的键值结构。它们可用于替换SQL中的联接。在ClickHouse中支持多个表联接之前,字典是
许多应用程序的灵丹妙药(silver bullet)。
字典已插入外部资源。源可以是另一个数据库(ClickHouse,MySQL或通用的ODBC),文件或Web服务中的表。 ClickHouse也
可以自动从外部来源刷新字典
以使其保持最新状态。
以这种方式使用字典来处理ETL流程是过时的。由于字典通常是内存中的,因此它们可用于低频率的查询,以及通常可提高查询性能。

但是数据字典存在两个可用性问题。
首先,必须在XML配置文件中定义字典。无法使用DDL定义它们。这使模式管理变得不一致和不方便。
例如,如果没有对物理服务器的访问权限,则根本无法创建字典。

第二个问题是SQL兼容性。 BI工具和分析人员必须知道如何在SQL查询中使用字典。人可以学习,但是BI工具不能。
因此,字典很难与Tableau等工具一起使用。



数据字典的DDL
在此之前需要创建大量的XML文件,在最新的ClickHouse 20.1版本中引入了CREATE DICTIONARY语句,至少需要20.1.11.73+版本:


CREATE DICTIONARY dict(
  key_column UInt64 DEFAULT 0,
  value_column String DEFAULT 'a'
)
PRIMARY KEY key_column
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 't_dict' PASSWORD '' DB 'datasets'))
LIFETIME(MIN 1 MAX 10)
LAYOUT(HASHED());

我们使用DDL语句定义数据字典,我们可以使用如下语句查询字典和查看字典的定义,当然也可以使用on cluster语法,将DDL语句传播到集群的每个节点。

Clickhouse> SHOW DICTIONARIES;

SHOW DICTIONARIES

┌─name─┐
│ dict │
└──────┘

1 rows in set. Elapsed: 0.003 sec. 

Clickhouse> SHOW CREATE DICTIONARY dict;

SHOW CREATE DICTIONARY dict

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE DICTIONARY datasets.dict
(
    `key_column` UInt64 DEFAULT 0, 
    `value_column` String DEFAULT 'a'
)
PRIMARY KEY key_column
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' TABLE 't_dict' PASSWORD '' DB 'datasets'))
LIFETIME(MIN 1 MAX 10)
LAYOUT(HASHED()) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.001 sec. 
-- 测试函数 dictGet()

Clickhouse> select dictGet('datasets.dict','value_column',toUInt64(1));

SELECT dictGet('datasets.dict', 'value_column', toUInt64(1))

┌─dictGet('datasets.dict', 'value_column', toUInt64(1))─┐
│ A1                                                    │
└───────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 2.541 sec. 

由于DDL字典是schema的一部分,需要在调用dictGet函数的时候加上数据字典的范围。


使用字典JOIN
在2018年发布了将可以将表的引擎设置为Dictionary engine的功能,所以字典表可以被用于SQL查询中,被当做表或者视图。
这样看起来更加智能,比起只存储于内存之中,也可以获得更加好的性能。

Clickhouse> select * from numbers(5) as n inner join dict on key_column=number;

SELECT *
FROM numbers(5) AS n
INNER JOIN dict ON key_column = number

┌─number─┬─key_column─┬─value_column─┐
│      0 │          0 │ A0           │
│      1 │          1 │ A1           │
│      2 │          2 │ A2           │
│      3 │          3 │ A3           │
│      4 │          4 │ A4           │
└────────┴────────────┴──────────────┘

5 rows in set. Elapsed: 0.017 sec. 
在早于20.3版本之前性能会比较差,需要耗费大量的时间。

Clickhouse> SELECT number,dictGet('datasets.dict', 'value_column', number) val FROM numbers(5);

SELECT 
    number, 
    dictGet('datasets.dict', 'value_column', number) AS val
FROM numbers(5)

┌─number─┬─val─┐
│      0 │ A0  │
│      1 │ A1  │
│      2 │ A2  │
│      3 │ A3  │
│      4 │ A4  │
└────────┴─────┘

5 rows in set. Elapsed: 0.011 sec. 

现在快了! 更改是微不足道的,但是它要求用户知道某些表实际上是字典。 当然,许多用户质疑ClickHouse为什么无法自动重写查询。 从最新的20.4版本开始,终于可以了!
在20.4版本之后:

Clickhouse> select * from numbers(5) as n inner join dict on key_column=number;

SELECT *
FROM numbers(5) AS n
INNER JOIN dict ON key_column = number

┌─number─┬─key_column─┬─value_column─┐
│      0 │          0 │ A0           │
│      1 │          1 │ A1           │
│      2 │          2 │ A2           │
│      3 │          3 │ A3           │
│      4 │          4 │ A4           │
└────────┴────────────┴──────────────┘

5 rows in set. Elapsed: 0.008 sec. 

Clickhouse> select version();

SELECT version()

┌─version()─┐
│ 20.4.5.36 │
└───────────┘

1 rows in set. Elapsed: 0.003 sec. 

SELECT 
    database, 
    name, 
    status, 
    origin, 
    type, 
    key, 
    attribute.names, 
    attribute.types, 
    query_count, 
    hit_rate, 
    element_count, 
    load_factor, 
    source, 
    loading_start_time, 
    loading_duration, 
    last_exception
FROM system.dictionaries

┌─database─┬─name─┬─status─┬─origin────────┬─type───┬─key────┬─attribute.names──┬─attribute.types─┬─query_count─┬─hit_rate─┬─element_count─┬────────load_factor─┬─source──────────────────────┬──loading_start_time─┬─loading_duration─┬─last_exception─┐
│ datasets │ dict │ LOADED │ datasets.dict │ Hashed │ UInt64 │ ['value_column'] │ ['String']      │           0 │        1 │      10000000 │ 0.2980232238769531 │ ClickHouse: datasets.t_dict │ 2020-06-27 20:33:37 │            1.845 │                │
└──────────┴──────┴────────┴───────────────┴────────┴────────┴──────────────────┴─────────────────┴─────────────┴──────────┴───────────────┴────────────────────┴─────────────────────────────┴─────────────────────┴──────────────────┴────────────────┘

1 rows in set. Elapsed: 0.012 sec.


可以看到使用JOIN 响应速度很快,实际上clickhouse不在需要JOIN 正确的表,他可以更加高效的进行字典调用。目前的版本只需要5次调用,而不需要扫描
1000万行的表数据。
另外一个优点则是字典的所有属性都可使用joinGet()函数join 到一起,这样每个需要的属性都可以自动的查询到。

结论:
ClickHouse外部词典最终成为ClickHouse中的一等公民,词典是模式的一部分,可以使用本地的DDL或者on cluster 方式创建。
从20.4版本开始,可以对用户隐藏实现细节,由于clickhouse可以自动转换join到字典的调用。
现在Clickhouse字典要比以前的版本更加用户友好了。
DDL创建数据字典从19.17.4.11版本开始支持。

参考:

https://www.altinity.com/blog/2020/5/19/clickhouse-dictionaries-reloaded

https://www.altinity.com/blog/2017/4/20/clickhouse-dictionaries-benchmarking

https://www.altinity.com/blog/2017/4/12/dictionaries-explained

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值