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

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 

建议需要从大量内容列中抽取小结果时，使用物化列提升性能。

三种表达式差异

（1）数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据，将会得到如下的错误。

（2）数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT 返回。而MATERIALIZED和ALIAS类型的字段不会出现在SELECT查询的返回结果集中。

（3）数据存储：在数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。