天天看點
傳回

ClickHouse資料庫資料定義手記-不一般的DDL和DML

前提

前面一篇文章已經很詳細地介紹了

ClickHouse

中每種資料類型的定義和基本使用，這篇文章會詳細地介紹

ClickHouse

中的

DDL

和

DML

，很多操作差別于傳統的

DBMS

，特别是代價巨大的

DELETE

和

UPDATE

操作。接下來開始吧????????

❝

一般情況下，筆者建議ClickHouse的關鍵字全用大寫，這樣可以更加凸顯出自定義的駝峰命名和大寫關鍵字的不同，可讀性和可維護性更高

❞

❝

本文使用的ClickHouse服務版本為目前最新的20.10.3.30

❞

資料庫DDL

ClickHouse

服務啟動後，預設會生成一個命名為

default

的資料庫（除了系統資料庫之外，不切換資料庫建立表預設就是在

default

資料庫建立），資料庫就像命名空間，實體上實作了資料隔離，同時有效避免了表命名沖突等問題。通過

SHOW DATABASES

可以列出目前服務中的所有資料庫：

f5abc88ff7e4 :) SHOW DATABASES

SHOW DATABASES

┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ default                        │
│ system                         │
└────────────────────────────────┘

3 rows in set. Elapsed: 0.023 sec.

建立資料庫的基本文法如下：

CREATE DATABASE [IF NOT EXISTS] $db_name [ON CLUSTER $cluster] [ENGINE = $engine(...)]

其中有三段可選的子句：

  • IF NOT EXISTS

     ：代表不存在

    $db_name

     同名資料庫的時候才建立，否則忽略，不使用此子句如果資料庫中已經存在同名的資料庫定義則會抛出一個異常

  • ON CLUSTER $cluster

     ：指定叢集中的所有

    ClickHouse

     服務執行個體都執行對應的

    DDL

     ，進階的分布式

    DDL

     功能

  • [ENGINE = $engine(...)]

     ：指定資料庫引擎（挺意外的，不單表有對應的表引擎，資料庫也有相應的引擎）

常用的資料庫

DDL

：

DDL 功能 備注

SHOW DATABASES

 		列出服務中所有資料庫

SHOW CREATE DATABASE $db_name

 		傳回建庫的

DDL

 語句

CREATE DATABASE $db_name

 		建立資料庫

USE DATABASE $db_name

 		切換（目前會話中使用的）資料庫

例如：

f5abc88ff7e4 :) CREATE DATABASE db_test;

CREATE DATABASE db_test

Ok.

0 rows in set. Elapsed: 0.034 sec.

f5abc88ff7e4 :) SHOW CREATE DATABASE db_test;

SHOW CREATE DATABASE db_test

┌─statement──────────────────────────────┐
│ CREATE DATABASE db_test
ENGINE = Atomic │
└────────────────────────────────────────┘

1 rows in set. Elapsed: 0.007 sec.

資料庫引擎

這個是

ClickHouse

的一個十分先進的特性，資料庫可以定義引擎類型，不同的引擎應用于不同的場景，用得熟練就可以領略一下"萬物皆為表"的遠大宏願。官方文檔上隻提到了三種資料庫引擎：預設的資料庫引擎、

MySQL

和

Lazy

。但是從大部分參考資料來看，

ClickHouse

支援「至少五種」資料庫引擎。已知可用的資料庫引擎如下：

引擎 特點 備注

Ordinary

 		選用此資料庫引擎可以使用任意類型的表引擎 預設的資料庫引擎

Dictionary

 		字典引擎，會為所有資料字典建立對應的關聯表 此引擎使用内置的字典功能，應該是不常用的

Memory

 		記憶體引擎，表和資料都存放在記憶體，重新開機後資料會被清理 可以簡單認為這類資料庫中所有表的引擎被設定為

Memory

Lazy

 		日志引擎，此類型資料庫隻能使用

Log

 系列的表引擎 		需要配置一個

expiration_time_in_seconds

 時間值指定最後一次通路後表駐留于記憶體的過期時間

MySQL

MySQL

 引擎，使用此引擎的資料庫需要指定一個

MySQL

 執行個體的連結參數，自動同步

MySQL

 的表和資料 		格式

ENGINE = MySQL('HOST:PORT','DATABASE','USERNAME','PASSWORD')

Atomic

 		不指定任何資料庫引擎的時候自動選用的資料庫引擎

20.10.3.30

 版本不指定資料庫引擎建立的資料庫就是此類型，猜測是

Ordinary

 類型的别名

絕大多數情況下，選用

Ordinary

類型或者不需要指定資料庫引擎即可。測試一下：

f5abc88ff7e4 :) CREATE DATABASE db_default;

CREATE DATABASE db_default

Ok.

0 rows in set. Elapsed: 0.027 sec.

f5abc88ff7e4 :) SHOW CREATE DATABASE db_default;

SHOW CREATE DATABASE db_default

┌─statement─────────────────────────────────┐
│ CREATE DATABASE db_default
ENGINE = Atomic │
└───────────────────────────────────────────┘

1 rows in set. Elapsed: 0.007 sec.

f5abc88ff7e4 :) CREATE DATABASE db_ordinary ENGINE = Ordinary;

CREATE DATABASE db_ordinary
ENGINE = Ordinary

Ok.

0 rows in set. Elapsed: 0.019 sec.

f5abc88ff7e4 :) CREATE DATABASE db_memory ENGINE = Memory;

CREATE DATABASE db_memory
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.014 sec.

f5abc88ff7e4 :) CREATE DATABASE db_lazy ENGINE = Lazy(60);

CREATE DATABASE db_lazy
ENGINE = Lazy(60)

Ok.

0 rows in set. Elapsed: 0.017 sec.

資料表DDL

資料表

DDL

有很多用法類似于傳統的

DBMS

例如

MySQL

的使用方式，但是也添加了一些新的特性。

建表DDL

就建立資料庫表來說，一共有三種方式：

  • 第一種：基于嚴格的

    Schema

     文法定義建立資料庫表
  • 第二種：拷貝其他表的

    Schema

     建立新的表，同時可以選擇指定表引擎，有點像

    MySQL

     中的

    CREATE TABLE XX LIKE YY
  • 第三種：拷貝其他表的

    Schema

     建立新的表并且導入

    SELECT

     查詢的資料，同時必須指定表引擎

「嚴格的Schema文法定義」

# 文法定義
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name [ON CLUSTER $cluster_name](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1] [COMMENT comment1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2] [COMMENT comment1],
    ...
) ENGINE = $engine

舉例：

f5abc88ff7e4 :) CREATE TABLE t_test(id UInt64 COMMENT 'ID',name String COMMENT '姓名') ENGINE = Memory;

CREATE TABLE t_test
(
    `id` UInt64 COMMENT 'ID',
    `name` String COMMENT '姓名'
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.032 sec.

「拷貝表結構且可以修改表引擎」

# 文法定義
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name AS [$other_db_name.]$other_table_name [ENGINE = engine]

# 例如
CREATE TABLE default.t_new_test AS default.t_test

舉例：

f5abc88ff7e4 :) CREATE TABLE default.t_new_test AS default.t_test;

CREATE TABLE default.t_new_test AS default.t_test

Ok.

0 rows in set. Elapsed: 0.028 sec.

f5abc88ff7e4 :) DESC default.t_new_test;

DESCRIBE TABLE default.t_new_test

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ UInt64 │              │                    │ ID      │                  │                │
│ name │ String │              │                    │ 姓名    │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

2 rows in set. Elapsed: 0.004 sec.

「拷貝表結構導入資料并且指定表引擎」

# 文法定義
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name AS table_function()

# 例如
CREATE TABLE [IF NOT EXISTS] [$db_name.]$table_name ENGINE = $engine AS SELECT ...

舉例：

f5abc88ff7e4 :) CREATE TABLE default.t_test_func ENGINE = Memory AS SELECT * FROM t_test;

CREATE TABLE default.t_test_func
ENGINE = Memory AS
SELECT *
FROM t_test

Ok.

0 rows in set. Elapsed: 0.028 sec.

預設值表達式

ClickHouse

推薦所有寫入的資料列都包含值，「否則會填充對應類型的零值」，或者通過預設值表達式指定預設值。假如某個字段定義了預設值，那麼該字段就不需要強制指定資料類型，

ClickHouse

會基于預設值表達式推斷出它的（合理類型内的較窄範圍的）資料類型，沒錯，定義了預設值甚至都不需要定義類型。此外，預設值表達式可以定義為「常量或者基于其他列的計算表達式」，

ClickHouse

會檢查這些表達式是否出現循環依賴。預設值表達式包含三種關鍵字：

  • DEFAULT $expression

     ：例如

    amount Decimal(10,2) DEFAULT 0

     、

    c2 UInt32 DEFAULT c1

     （

    c2 DEFAULT 1000

     ）

  • MATERIALIZED $expression

     ：例如

    a MATERIALIZED (b+1), b UInt16

  • ALIAS $expression

     ：例如

    a ALIAS (b+1), b UInt16

三者的差別如下：

關鍵字 特點 列資料是否持久化

DEFAULT

 		對應的列可以出現在

INSERT

 語句中，不出現則通過表達式計算和填充，

SELECT *

 查詢結果會包含對應的列 		如果使用的表引擎支援實體持久化則可以持久化

MATERIALIZED

 		對應的列不可以出現在

INSERT

 語句中，

SELECT *

 查詢結果不會包含對應的列，但是

SELECT

 指定列名則會傳回，傳回值依賴于動态計算，

dump

 資料場景下通過

INSERT ... SELECT *

 可以導入對應列資料 		如果使用的表引擎支援實體持久化則可以持久化

ALIAS

 		對應的列不可以出現在

INSERT

 語句中，

SELECT *

 查詢結果不會包含對應的列，但是

SELECT

 指定列名則會傳回，傳回值依賴于動态計算 		不支援持久化，甚至列資料都不會存儲在表中，取值總是動态計算

❝

如果使用習慣了MySQL中的DEFAULT關鍵字，那麼可以認為ClickHouse的DEFAULT關鍵與之類似，隻是更加先進可以基于表達式進行計算

❞

使用預設值表達式需要注意幾點：

  • 可以使用

    ALTER

     關鍵字修改某個列的預設值：

    ALTER TABLE [$db_name.]$table_name MODIFY COLUMN $column_name [DEFAULT|MATERIALIZED|ALIAS] exp

     ，但是修改的動作不會影響資料表中之前已經存在的資料
  • 預設值的修改有比較多的限制，

    MergeTree

     系列的表引擎中主鍵字段無法修改預設值，甚至有某些表引擎類型完全不允許修改任意列的預設值（如

    TinyLog

     ）
  • 無法為

    Nested

     類型資料結構中的元素設定預設值

DEFAULT

關鍵字舉例：

f5abc88ff7e4 :) CREATE TABLE t_d(a UInt16,b DEFAULT (a + 1)) ENGINE = Memory;

CREATE TABLE t_d
(
    `a` UInt16,
    `b` DEFAULT a + 1
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.024 sec.

f5abc88ff7e4 :) INSERT INTO t_d(a,b) VALUES(1,11);

INSERT INTO t_d (a, b) VALUES

Ok.

1 rows in set. Elapsed: 0.007 sec.

f5abc88ff7e4 :) INSERT INTO t_d(a) VALUES(3);

INSERT INTO t_d (a) VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) select * from t_d;

SELECT *
FROM t_d

┌─a─┬──b─┐
│ 1 │ 11 │
└───┴────┘
┌─a─┬─b─┐
│ 3 │ 4 │
└───┴───┘

2 rows in set. Elapsed: 0.004 sec.

MATERIALIZED

關鍵字舉例：

f5abc88ff7e4 :) CREATE TABLE t_m(a UInt16,b MATERIALIZED (a + 1)) ENGINE = Memory;

CREATE TABLE t_m
(
    `a` UInt16,
    `b` MATERIALIZED a + 1
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.019 sec.

f5abc88ff7e4 :) INSERT INTO t_m(a) VALUES (2);

INSERT INTO t_m (a) VALUES

Ok.

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT * FROM t_m;

SELECT *
FROM t_m

┌─a─┐
│ 2 │
└───┘

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT b FROM t_m;

SELECT b
FROM t_m

┌─b─┐
│ 3 │
└───┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) INSERT INTO t_m(a,b) VALUES (2,3);

INSERT INTO t_m (a, b) VALUES


Received exception from server (version 20.10.3):
Code: 44. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Cannot insert column b, because it is MATERIALIZED column..

0 rows in set. Elapsed: 0.004 sec.

ALIAS

關鍵字舉例：

f5abc88ff7e4 :) CREATE TABLE t_a(a UInt16,b ALIAS (a + 1)) ENGINE = Memory;

CREATE TABLE t_a
(
    `a` UInt16,
    `b` ALIAS a + 1
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.021 sec.

f5abc88ff7e4 :) INSERT INTO TABLE t_a(a) VALUES (11);

INSERT INTO t_a (a) VALUES

Ok.

1 rows in set. Elapsed: 0.003 sec.

f5abc88ff7e4 :) SELECT *,b FROM t_a;

SELECT
    *,
    b
FROM t_a

┌──a─┬──b─┐
│ 11 │ 12 │
└────┴────┘

1 rows in set. Elapsed: 0.005 sec.

其他常用的表操作

指令 功能

SHOW CREATE TABLE $table_name

 		檢視建表語句

DESC TABLE $table_name | DESCRIBE TABLE $table_name

 		展示表的所有列定義

DROP TABLE $table_name

 		删除表，表資料會從檔案系統中實體删除

TRUNCATE TABLE $table_name

 		清空表資料

例如：

f5abc88ff7e4 :) DESCRIBE TABLE p_v1

DESCRIBE TABLE p_v1

┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ Id        │ UInt64 │              │                    │         │                  │                │
│ EventTime │ Date   │              │                    │         │                  │                │
│ name      │ String │ DEFAULT      │ 'dv'               │         │                  │                │
│ age       │ UInt16 │              │                    │         │                  │                │
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

4 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SHOW CREATE TABLE p_v1

SHOW CREATE TABLE p_v1

┌─statement─────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.p_v1
(
    `Id` UInt64,
    `EventTime` Date,
    `name` String DEFAULT 'dv',
    `age` UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY Id
SETTINGS index_granularity = 8192 │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.008 sec.

列壓縮編碼

ClickHouse

服務為了節省磁盤空間，會使用高性能壓縮算法對存儲的資料進行壓縮。預設啟用的是

lz4

（

lz4 fast compression

）壓縮算法，在

MergeTree

家族引擎下可以通過

ClickHouse

服務端配置中的

compression

節點選項配置來改變預設的壓縮算法。基本文法：

## 建表
$column_name [type] COCEC($算法類型)

## 修改
ALTER TABLE $table_name MODIFY COLUMN $column_name CODEC($算法類型);

## 建表例子
CREATE TABLE codec_example
(
    ts DateTime CODEC(LZ4),
    dt Date CODEC(DEFAULT)
)

可以選用的算法：

  • None

     ：無壓縮

  • Default

     ：預設的壓縮算法，預設值也是使用預設的壓縮算法

  • LZ4

     ：

    lz4

     壓縮算法中的快速壓縮算法版本

  • LZ4HC[(level)]

     ：

    lz4

     高壓縮率壓縮算法版本，

    level

     預設值為

    9

     ，支援

    [1,12]

     ，推薦選用

    [4,9]

  • ZSTD[(level)]

     ：

    zstd

     壓縮算法，

    level

     預設值為

    1

     ，支援

    [1,22]

采用不同的表引擎，會支援不同的壓縮算法，目前的支援清單如下：

  • MergeTree

     系列引擎：支援所有可選的壓縮算法，并且支援在服務端配置預設壓縮算法

  • Log

     系列引擎：預設使用

    lz4

     壓縮算法，支援所有可選的壓縮算法

  • Set

     系列引擎：隻支援預設的壓縮算法

  • Join

     系列引擎：隻支援預設的壓縮算法

還有幾個特殊的編碼解碼方法如

Delta(delta_bytes)

、

DoubleDelta

、

Gorilla

和

T64

，這裡不做展開。

臨時表

ClickHouse

也支援臨時表，不過有比較多的限制：

  • 生命周期綁定在會話的生命周期，會話結束（例如連接配接斷開），臨時表會消失
  • 臨時表無法指定表引擎，建立時候隐式使用

    Memory

     引擎
  • 建立臨時表的時候無法指定資料庫，臨時表總是在資料庫"外部"建立（換言之，臨時表不屬于任何資料庫）
  • 如果一個臨時表與另一個非臨時表的名稱相同，并且查詢的時候不指定資料庫，那麼将使用臨時表查詢（換言之，不指定資料庫進行查詢前提下，臨時表優先級比普通表要高）

建立臨時表的文法如下：

CREATE TEMPORARY TABLE [IF NOT EXISTS] $table_name
(
    $column_name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    $column_name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)

❝

注意：官方文檔提醒，絕大多數情況下，臨時表都不是手動建立的而是内部建立，一般用于分布式全局的外部資料查詢，例如用于叢集間資料的查詢傳遞，是以官方應該是不建議使用臨時表

❞

視圖

ClickHouse

支援視圖功能，目前一共支援兩種視圖：普通（

Normal

）視圖和物化（

Materialized

）視圖。通過

DROP TABLE [$db_name.]$view_table_name

語句可以直接删除視圖，而通過

SHOW TABLES

可以展示所有的表，視圖也會被認為是一種特殊的表一并進行展示。

普通視圖

普通視圖的建立文法如下：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [$db_name.]$view_table_name [ON CLUSTER] AS SELECT ...

普通視圖不會存儲任何資料，它隻是一個查詢映射，起到了簡化查詢語義的作用，對查詢的性能也不會有任何正負作用。假設有一個表叫

t_test

，建立一個普通視圖

view_test

：

CREATE VIEW view_test AS SELECT * FROM t_test

如果直接從視圖

view_test

做查詢

SELECT * FROM view_test

，語義完全等價于

SELECT * FROM (SELECT * FROM t_test)

。

物化視圖

物化視圖支援定義表引擎，因為其資料儲存的形式由表引擎決定。建立物化視圖的文法如下：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [$db_name.]$view_table_name [ON CLUSTER] [TO[$db_name.]$table_name] [ENGINE = $engine] [POPULATE] AS SELECT ...

物化視圖的特點如下：

  • 需要定義表引擎，決定資料存儲的形式
  • 物化視圖建立後會生成内部表，表名以

    .inner.

     開頭，可以通過

    SHOW TABLES

     驗證

  • [TO[$db_name.]$table_name]

     子句和

    [POPULATE]

     子句互斥，二者隻能選其中一者或者都不選
  • 在使用

    [TO[$db_name.]$table_name]

     子句的時候，必須顯式指定表引擎

  • POPULATE

     關鍵字決定了物化視圖的更新政策，如果使用了

    POPULATE

     則在建立視圖的過程會将源表已經存在的資料一并導入，類似于

    CREATE TABLE ... AS

     ，如果不指定

    POPULATE

     關鍵字，建立的物化視圖是全新沒有資料的，建立完成之後才會開始同步源表寫入的資料（官方不推薦使用

    POPULATE

     關鍵字，因為啟用會有可能導緻建立物化視圖過程中新寫入源表的資料無法同步到視圖中）
  • 物化視圖中的資料不支援同步删除，如果源表的資料不存在或者源表被删除了，物化視圖的資料依然存在

ClickHouse

中的物化視圖的實作更像是資料插入觸發器。如果視圖查詢中存在某些聚合，則這些聚合操作僅僅會作用于這些新寫入的資料。對源表的現有資料進行的任何更改（例如更新、删除、删除分區等）都不會更改物化視圖中的資料。

❝

筆者注：物化視圖是一把雙刃劍，用的合理會簡化大量同步和聚合的工作，濫用則會導緻維護十分困難還會影響性能

❞

基本的列操作

基本的列操作都是圍繞

ALTER

關鍵字執行。通用的基本文法是：

ALTER TABLE [$db_name.]$table_name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

下面為了簡化文法，暫時省略

[$db_name.]

和

[ON CLUSTER cluster]

等子句。

追加新的列 - ADD COLUMN

ADD COLUMN

語句用于在指定的表添加一個新的列。基本文法：

ALTER TABLE $table_name ADD COLUMN [IF EXISTS] $column_name [type] [default_expr] [codec] [AFTER $pre_column_name]

  • type

     ：可選，用于指定列類型

  • default_expr

     ：可選，用于設定預設值表達式

  • codec

     ：可選，見前面一節的「列壓縮編碼」

  • AFTER

     子句：可選，用于指定在哪個已經存在的列後面添加新的列

舉例：

ALTER TABLE default.p_v1 ADD COLUMN age UInt16 AFTER name

修改列 - MODIFY COLUMN

MODIFY COLUMN

語句可以用于修改已經存在的列的類型、預設值表達式或者

TTL

表達式。基本文法：

ALTER TABLE $table_name MODIFY COLUMN [IF EXISTS] $column_name [type] [default_expr] [TTL]

舉例：

ALTER TABLE default.p_v1 MODIFY COLUMN age UInt32

類型修改的時候，本質上會使用内置函數

toType()

進行轉換，如果目前類型與期望類型不能相容無法轉換，則列修改操作會失敗，抛出異常。

添加或者修改列備注 - COMMENT COLUMN

ClickHouse

中添加或者修改列注釋使用特殊的

COMMENT COLUMN

子句。基本文法：

ALTER TABLE $table_name COMMENT COLUMN [IF EXISTS] $column_name '備注内容'

舉例：

ALTER TABLE default.p_v1 COMMENT COLUMN age '年齡'

删除列 - DROP COLUMN

DROP COLUMN

語句用于删除列，對應的列資料會從檔案系統中「實體删除」。基本文法：

ALTER TABLE $table_name DROP COLUMN [IF EXISTS] $column_name

舉例：

ALTER TABLE default.p_v1 DROP COLUMN age

重置對應列和分區的所有值 - CLEAR COLUMN

CLEAR COLUMN

語句用于重置對應的列和指定分區的所有值為預設值，如果沒有設定預設值表達式，則對應列的所有值重置為其類型的零值。基本文法：

ALTER TABLE $table_name CLEAR COLUMN [IF EXISTS] $column_name IN PARTITION $partition_name

舉例：

f5abc88ff7e4 :) CREATE TABLE p_v1(Id UInt64,EventTime Date,name String DEFAULT 'dv')ENGINE = MergeTree() PARTITION BY toYYYYMM(EventTime) ORDER BY Id

CREATE TABLE p_v1
(
    `Id` UInt64,
    `EventTime` Date,
    `name` String DEFAULT 'dv'
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY Id

Ok.

0 rows in set. Elapsed: 0.047 sec.

f5abc88ff7e4 :) INSERT INTO p_v1 VALUES(1,'2020-11-28','doge1'),(2,'2020-10-29','doge2');

INSERT INTO p_v1 VALUES

Ok.

2 rows in set. Elapsed: 0.074 sec.

f5abc88ff7e4 :) ALTER TABLE p_v1 clear column name IN partition 202011;

ALTER TABLE p_v1
    CLEAR COLUMN name     IN PARTITION 202011


Ok.

0 rows in set. Elapsed: 0.163 sec.

f5abc88ff7e4 :) SELECT * FROM  p_v1;

SELECT *
FROM p_v1

┌─Id─┬──EventTime─┬─name──┐
│  2 │ 2020-10-29 │ doge2 │
└────┴────────────┴───────┘
┌─Id─┬──EventTime─┬─name─┐
│  1 │ 2020-11-28 │ dv   │
└────┴────────────┴──────┘

資料分區

資料分區在

ClickHouse

中就是分區表，本質是資料表

DDL

，但是考慮到資料分區的重要性，把這個特性單獨拉出來一個

h2

章節。

ClickHouse

中的資料分區是指同一個本地執行個體中的資料的縱向切分，跟橫向切分中的資料分片概念完全不同。

ClickHouse

目前隻有使用了

MergeTree

系清單引擎的表（包括

REPLICATED*

系列和使用了

MergeTree

系清單引擎的物化視圖）才支援資料分區。這裡僅僅簡單介紹一下

PARTITION

關鍵字的使用和常用的分區相關操作。

分區是資料表中記錄按指定條件的邏輯組合，可以通過任意條件（例如按月、按日或者按事件類型）設定分區，每個分區的資料分别儲存，以簡化資料操作和提高性能，是以在通路資料的時候盡可能使用最小的分區子集。

ClickHouse

在建立表的時候通過

PARTITION BY expr

子句指定分區定義，分區鍵可以是基于表中資料列組成的任意表達式。例如有個字段是

Date

類型，如果按月分區可以使用表達式

toYYYYMM(date_column)

，例如：

CREATE TABLE pv (
    visitDate Date,
    hour UInt8,
    clientID String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(visitDate)
ORDER BY hour;

建立完此表，使用

INSERT INTO pv VALUES('2020-11-01',1,'11'),('2020-10-01',2,'22');

寫入兩條資料後，通過下面的語句查詢分區資訊：

f5abc88ff7e4 :) SELECT partition,name,path,active FROM system.parts WHERE table = 'pv'

SELECT
    partition,
    name,
    path,
    active
FROM system.parts
WHERE table = 'pv'

┌─partition─┬─name─────────┬─path─────────────────────────────────────────────────────────────────────────────┬─active─┐
│ 202010    │ 202010_5_5_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_5_5_0/ │      1 │
│ 202010    │ 202010_7_7_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_7_7_0/ │      1 │
│ 202011    │ 202011_2_2_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_2_2_0/ │      1 │
│ 202011    │ 202011_4_4_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_4_4_0/ │      1 │
│ 202011    │ 202011_6_6_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_6_6_0/ │      1 │
└───────────┴──────────────┴──────────────────────────────────────────────────────────────────────────────────┴────────┘

5 rows in set. Elapsed: 0.005 sec.

可見分區一共有

202010

和

202011

兩個，而

name

屬性是分區資料部分（

parts

）的名稱，例如

202010_5_5_0

：

  • 第一部分

    202010

     是分區名稱
  • 第一個

    5

     是資料塊最小編号（

    MinBlockNum

     ）
  • 第二個

    5

     是資料塊最大編号（

    MaxBlockNum

     ）
  • 最後的 表示分區的層級，指某個分區合并過的次數

這個

name

屬性是合并樹家族表引擎特有的，後面如果有機會分析合并樹的基本原理的時候會更加深入分析其具體含義。分區完畢之後，通過分區鍵進行查詢就能采用分區最小資料集：

f5abc88ff7e4 :) SELECT * FROM pv WHERE visitDate = '2020-11-01'

SELECT *
FROM pv
WHERE visitDate = '2020-11-01'

┌──visitDate─┬─hour─┬─clientID─┐
│ 2020-11-01 │    1 │ 11       │
└────────────┴──────┴──────────┘

3 rows in set. Elapsed: 0.020 sec.

❝

如果一個分區有多部分沒合并，一般在寫入資料的15分鐘之後會對新寫入的分區部分資料進行合并，然後對應的部分就會變成非活躍狀态，可以通過OPTIMIZE TABLE table_name PARTITION partition進行執行計劃觸發合并，不過這是一個相當耗時的操作，一般不建議主動使用。

❞

資料分區的其他操作主要是圍繞

ALTER

關鍵字，文法是：

ALTER TABLE $table_name $OP PARTITION|PART $partition(_part)_expr

查詢分區資訊

查詢分區資訊主要依賴到系統表

system.parts

，可以通過

DESC system.parts

檢視列中繼資料定義（一共有

44

個列），這裡一般選用下面幾個常用的屬性：

  • partition

     ：分區名稱

  • name

     ：分區部分名稱（這個應該是目标表對于某個表分區的最小單元）

  • active

     ：分區部分是否活躍

  • path

     ：分區部分的存儲磁盤路徑

  • database

     ：分區所在資料庫

  • table

     ：分區所在的表

  • engine

     ：分區所在的表引擎

例如：

f5abc88ff7e4 :) SELECT database,table,engine,partition,name,path,active FROM system.parts WHERE table = 'pv';

SELECT
    database,
    table,
    engine,
    partition,
    name,
    path,
    active
FROM system.parts
WHERE table = 'pv'

┌─database─┬─table─┬─engine────┬─partition─┬─name─────────┬─path─────────────────────────────────────────────────────────────────────────────┬─active─┐
│ default  │ pv    │ MergeTree │ 202010    │ 202010_5_5_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_5_5_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202010    │ 202010_7_7_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202010_7_7_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202011    │ 202011_2_2_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_2_2_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202011    │ 202011_4_4_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_4_4_0/ │      1 │
│ default  │ pv    │ MergeTree │ 202011    │ 202011_6_6_0 │ /var/lib/clickhouse/store/f17/f1729bf7-7baf-40d0-bd4f-6ee13877d0db/202011_6_6_0/ │      1 │
└──────────┴───────┴───────────┴───────────┴──────────────┴──────────────────────────────────────────────────────────────────────────────────┴────────┘

5 rows in set. Elapsed: 0.005 sec.

删除分區

實體删除分區或者分區部分通過

DROP PARTITION|PART

子句完成，基本文法如下：

ALTER TABLE $table_name DROP PARTITION|PART partition(_part)_expr

此删除操作是異步的，執行語句完畢後對應的分區或者分區部分會先設定為非活躍（也就是設定

active = 0

），然後在

10

分鐘後進行實體删除。

例如：

ALTER TABLE pv DROP PARTITION 202010;

ALTER TABLE pv DROP PART 202010_5_5_0;

ALTER TABLE pv DROP PART all_5_5_0;

分區的解除安裝與裝載

ClickHouse

的分區支援熱解除安裝和熱裝載（仔細閱讀文檔發現應該是相對耗時的異步操作，操作時候需要謹慎），主要通過關鍵字

DETACH PARTITION|PART

和

ATTACH PARTITION|PART

完成，兩者剛好也是互逆操作。

「解除安裝分區 - DETACH PARTITION|PART」

基本文法如下：

ALTER TABLE $table_name DETACH PARTITION|PART $partition_expr

分區解除安裝并不會對該分區資料進行實體删除，而是把整個分區資料轉移到對應資料表目錄的

detached

子目錄下，此時直接通過

SELECT

查詢對應分區的資料集不會傳回任何資料（這個是當然的，資料目錄都被移動了......）。分區一旦被移動到了

detached

子目錄下就會一直存在，除非主動删除或者使用

ATTACH | DROP DETACHED

指令去重新裝載或者删除對應的資料目錄。

「裝載分區 - ATTACH PARTITION|PART」

基本文法如下：

ALTER TABLE $table_name ATTACH PARTITION|PART $partition_expr

分區裝載是分區解除安裝的逆操作，其實就是把

detached

子目錄下的分區資料重新轉移到資料表的分區目錄中。

「移除分區解除安裝備份 - DROP DETACHED PARTITION|PART」

基本文法如下：

ALTER TABLE $table_name DROP DETACHED PARTITION|PART $partition_expr

移除

detached

子目錄下對應的分區資料，實體删除，移除完成之後無法通過

ATTACH

關鍵字重新裝載。

分區資料的拷貝覆寫

基本文法如下：

ALTER TABLE $table_y_name REPLACE PARTITION $partition_expr FROM $table_x_name

直接拷貝資料表

table_x_name

的分區到資料表

table_y_name

的已經存在的分區，覆寫整個分區的資料，并且

table_x_name

原有的分區資料不會被删除。前提條件如下：

  • 兩個表的表結構完全一樣（列定義）
  • 兩個表的分區鍵完全一樣

分區資料的移動

基本文法如下：

ALTER TABLE $table_source MOVE PARTITION $partition_expr TO TABLE $table_dest

移動資料表

table_source

指定分區到資料表

table_dest

中，類似于剪切操作，資料表

table_source

原有的分區資料會被删除。前提條件如下：

  • 兩個表的表結構完全一樣（列定義）
  • 兩個表的分區鍵完全一樣
  • 兩個表的表引擎完全一樣
  • 兩個表的存儲政策（

    storage policy

     ）完全一樣

重置分區列資料

基本文法如下：

ALTER TABLE $table_name CLEAR COLUMN $column_name IN PARTITION $partition_expr

重置分區的列資料為預設值，如果沒有定義預設值表達式，則重置為對應類型的零值。

重置分區索引

基本文法如下：

ALTER TABLE $table_name CLEAR INDEX $index_name IN PARTITION $partition_expr

文檔中提到：有點像重置分區列資料的操作，但是隻重置分區的對應的索引，不會重置資料（具體功能未知，因為尚未深入了解索引的原理）。

其他分區操作

  • 分區備份：

    ALTER TABLE table_name FREEZE [PARTITION partition_expr]
  • 分區還原：

    ALTER TABLE table_name FETCH PARTITION partition_expr FROM 'path-in-zookeeper'
  • 移動分區到磁盤：

    ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name'

涉及到配置、磁盤路徑甚至是

Zookeeper

中的路徑，比較複雜，暫時不做展開。

TTL表達式

TTL

（

Time To Live

）表達式是

ClickHouse

一項比較創新的進階功能，用于指定資料的存活時間。

TTL

表達式有列字段級别（到期會重置對應列的過期資料），也有表級别（到期會删除整張表）。如果同時指定了列

TTL

和表

TTL

，則按照先到期先執行的原則處理。

TTL

表達式用于确定目标的生命周期，表達式計算的結果必須是

Date

或者

DateTime

資料類型，時間間隔使用關鍵字

INTERVAL

定義，而且還可以在表達式中定義資料在磁盤和資料卷之間移動的邏輯（限于表

TTL

）。基本的文法如下：

TTL time_column
TTL time_column + interval

## 需要使用INTERVAL關鍵字定義時間間隔
TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR

❝

到目前為止，ClickHouse隻提供了TTL定義和更新的文法， 沒有提供指定某個TTL表達式進行停止的文法，隻有一個全局停止所有TTL合并的指令：SYSTEM STOP/START TTL MERGES

❞

表TTL表達式

表

TTL

通過某個

Date

或者

DateTime

資料類型進行表級别設定過期時間（從目前來看，應該隻有

MergeTree

表引擎家族的表支援表級别

TTL

），當觸發

TTL

清理時，那些滿足過期時間的資料列将會被删除（或者被移動）。基本表達式如下：

TTL $expr [DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'], ...

舉個例子：

CREATE TABLE test_ttl
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],   # <--- 這裡DELETE可以不填，因為預設政策就是DELETE
    d + INTERVAL 1 WEEK TO VOLUME '資料卷',
    d + INTERVAL 2 WEEK TO DISK '磁盤目錄';

上面的例子說明：

  • 基于

    d

     過期1個星期的資料會被移動到其他資料卷
  • 基于

    d

     過期2個星期的資料會被移動到其他磁盤目錄
  • 基于

    d

     過期1個月的資料會被實體删除

可以通過

ALTER

關鍵字修改表級别的

TTL

，如：

ALTER TABLE $table_name MODIFY TTL $data(_time)_column + INTERVAL 1 DAY

列TTL表達式

列級别的

TTL

通過表達式定義列資料過期時候，過期的列資料會被

ClickHouse

重置為預設值或者對應類型的零值。

KEY

（主鍵）列不能定義

TTL

表達式，如果某個列的所有資料都已經過期了，那麼

ClickHouse

會把該列直接從檔案系統中移除。基本文法如下：

$column_name type $Date(_Time)_column + INTERVAL_EXP

# 例如
CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

DML

DML

對應于日常開發了解中的

CURD

，主要關鍵字包括

INSERT

、

SELECT

、

UPDATE

和

DELETE

。

SELECT

ClickHouse

中的

SELECT

基本用法和主流的關系型

DBMS

相似，支援指定列、

*

、内置函數和大量的聚合相關的關鍵，這裡不做深入展開，後面分析關鍵和函數的時候應該會大量用到

SELECT

操作。

INSERT

INSERT

關鍵字的主要功能就是寫入資料，此操作在

ClickHouse

中會比主流的關系型

DBMS

更加多樣靈活。基本文法如下：

INSERT INTO [$db_name.]$table_name [(c1, c2, c3) | (*)] VALUES (v11, v12, v13), (v21, v22, v23), ...
  • 指定列寫入：

    INSERT INTO table (x_col,y_col,z_col) VALUES (x1,y1,z1),(x2,y2,z2)

     或者

    INSERT INTO table COLUMNS(x_col,y_col,z_col) VALUES (x1,y1,z1),(x2,y2,z2)
  • 不指定列（按照列定義順序一一對應寫入）寫入：

    INSERT INTO table VALUES (x1,y1,z1),(x2,y2,z2)

     或者

    INSERT INTO table (*) VALUES (x1,y1,z1),(x2,y2,z2)
  • 排除指定的列寫入（剩餘的列）：

    INSERT INTO table (* EXCEPT(x_col,y_col)) VALUES (z1),(z2)

❝

寫入的時候如果有的列沒有被填充資料，會使用預設值或者對應類型的零值填充。

❞

還可以指定資料格式進行資料寫入，基本文法是：

INSERT INTO [$db_name.]$table_name [(c1, c2, c3)] FORMAT $format_name $data_set

例如：

  • 寫入基于

    Tab

     分隔的資料： 
INSERT INTO test_tab FORMAT TabSeparated
1 foo
2 bar
  • 寫入

    csv

     格式的資料： 
INSERT INTO test_csv FORMAT CSV
1,'foo'
2,'bar'

最後一種是通過

SELECT

子句寫入資料，此過程支援表達式或者函數，基本文法如下：

INSERT INTO [$db_name.]$table_name [(c1, c2, c3)] SELECT ...

## 例如
INSERT INTO test_insert SELECT 1,'doge',now()

追求性能的前提下，盡可能不要在後面的

SELECT

子句中附帶函數，因為函數最終也是需要

ClickHouse

服務端進行解析和調用，大量使用會導緻寫入性能下降。

出于寫入性能的考量，官方建議：

  • 批量寫入資料
  • 寫入資料之前通過分區鍵對資料進行預分組

ClickHouse

對于資料寫入都是面向

Block

資料結構，單個

Block

資料塊寫入是原子性的，而單個

Block

資料塊允許寫入的行數由配置項

max_insert_block_size

控制，預設值是

1048576

，注意此原子性基于

CLI

指令寫入資料是不生效的，隻有使用

JDBC

或者

HTTP

協定的時候才生效。

UPDATE和DELETE

ClickHouse

雖然提供

UPDATE

和

DELETE

關鍵字，但是這兩種操作是重量級操作，被稱為

Mutation

查詢，通過

ALTER

執行。

Mutation

查詢有幾個特點：

  • 不支援事務
  • 重量級操作，消耗嚴重，必須盡可能批量操作
  • 異步執行，送出後立即傳回，但是結果需要從系統表

    system.mutations

     中查詢

基本文法如下：

# DELETE
ALTER $table_name DELETE WHERE $filter_exp

## DELETE例子
ALTER test_delete DELETE WHERE id = 1;

# UPDATE
ALTER $table_name UPDATE columnx = exp_x,... WHERE $filter_exp

## UPDATE例子
ALTER test_update UPDATE name = 'throwable' WHERE id = 1;

查詢

system.mutations

的執行結果：

SELECT database,table,mutation_id,blick_numbers.number,is_done FROM system.mutations

❝

ClickHouse對寫入和查詢性能的傾斜，導緻他會放棄一些特性，例如事務和高效的精确更新或删除功能，這些是利弊權衡，沒有所謂正确與否。

❞

小結

這篇文章比較詳細地介紹了

ClickHouse

中常用的

DDL

和

DML

，部分進階特性如分布式

DDL

會在後面分析

ClickHouse

叢集搭建的時候再介紹。接下來會詳細學習一下

ClickHouse

目前支援的主流的表引擎和對應的使用場景。

參考資料

  • https://clickhouse.tech
  • 《ClickHouse原了解析與應用實踐》

（c-10-d e-a-20201208 封面自《鬥羅大陸》小舞）

大資料 ClickHouse 資料庫
上一篇: 經曆一些磨練造就一個不一般的自己
下一篇: “以卡養卡”是個技術活，一般人玩不了，不一般的人玩的溜

繼續閱讀