比较ClickHouse三种表字段默认值表达式

最新推荐文章于 2024-05-21 15:42:23 发布

原创最新推荐文章于 2024-05-21 15:42:23 发布 · 4.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse #数据库 #sql

ClickHouse 专栏收录该内容

32 篇文章

订阅专栏

本文介绍了ClickHouse中定义表字段默认值的三种方式——DEFAULT、MATERIALIZED和ALIAS。MATERIALIZED和ALIAS在数据写入和查询时有特定行为：MATERIALIZED列数据会被持久化，适合复杂计算；ALIAS列不持久化，仅在查询时计算。文章通过示例展示了这两种表达式的使用，并建议在需要提高查询性能时使用物化列。

ClickHouse表字段支持三种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED和ALIAS。Default其他RDBMS也支持，比较常用，本文注意介绍后两者表达式的差异，并通过示例分别进行介绍。

数据类型

无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主，例如下面的例子：

CREATE TABLE dfv_v1 (
    id String,
    c1 DEFAULT 1000,
    c2 String DEFAULT c1
) ENGINE = TinyLog

c1字段没有定义数据类型，默认值为整型1000；c2字段定义了数据类型和默认值，且默认值等于c1。根据默认值的优先级顺序，其中c1字段根据默认值被推断为UInt16；而c2字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的String。

alias表达式

alias列没有序列化，是在查询时才计算。因此不适合复杂计算，示例如下：

CREATE TABLE test_new_col
 (
    `_csv` String,
    `csv_as_array` Array(String) ALIAS splitByChar(';',_csv),
    `csv_col1` String DEFAULT csv_as_array[1],
    `csv_col2` String DEFAULT csv_as_array[2]
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO test_new_col (_csv) values
    ('a1;b1;c1;d1'), 
    ('a2;b2;c2;d2'), 
    ('a3;b3;c3;d3');

SELECT csv_col1, csv_col2 FROM test_new_col;

查询结果：

Query id: 45e5ded7-3b93-4bc0-b825-47c4310d21cf

┌─csv_col1─┬─csv_col2─┐
│ a1       │ b1       │
│ a2       │ b2       │
│ a3       │ b3       │
└──────────┴──────────┘

我们再增加一列：

ALTER TABLE test_new_col ADD COLUMN `csv_col3` String DEFAULT csv_as_array[3];

SELECT csv_col3 FROM test_new_col

┌─csv_col3─┐
│ c1       │
│ c2       │
│ c3       │
└──────────┘

物化列

使用默认值(default或MATERIALIZED)的表达式会持久化列数据。如果需要使用复杂的表达式查询数据，会避免在SELECT执行时直接计算这样的表达式会非常昂贵。

举例，下面查询需要使用positionCaseInsensitive函数，查找字符串menu在列notes中的位置。在每个查询中执行这样操作非常耗时，因为notes内容非常多。

┌─notes─────────────────────────────────────────────────┐ 
│ wine list in Swedish; Arthur H. Rumpf Menu Collection │ 
│ wine list in Swedish; Arthur H. Rumpf Menu Collection │ 
└───────────────────────────────────────────────────────┘

直接查询SQL:

SELECT     
  dish_count,     
  status,     
  positionCaseInsensitive(notes, 'menu') AS result 
FROM menu 
GROUP BY     
  dish_count,     
  status,     
  result 
ORDER BY result DESC 
LIMIT 2

为了避免查询时临时计算，我们增加物化列存储计算结果：

ALTER TABLE menu     
ADD COLUMN `materlized_result` Int16 MATERIALIZED positionCaseInsensitive(notes, 'menu') 

-- 增加列之后，未来插入数据会物化。但之前的数据需要执行optimise语句
OPTIMIZE TABLE menu FINAL

下面可以直接查询物化列，读者可以对比两者性能：

SELECT     
  dish_count,     
  status,     
  materlized_result AS result 
FROM menu 
GROUP BY     
  dish_count,     
  status,     
  result 
ORDER BY result DESC 
LIMIT 2