ClickHouse資料庫數據定義手記之數據類型

• 2020 年 11 月 22 日
• 筆記
• 小數據

前提

前邊一篇文章詳細分析了如何在Windows10系統下搭建ClickHouse的開發環境，接著需要詳細學習一下此資料庫的數據定義，包括數據類型、DDL和DML。ClickHouse作為一款完備的DBMS，提供了類似於MySQL（其實有部分語法差別還是比較大的）的DDL與DML功能，並且實現了大部分標準SQL規範中的內容。系統學習ClickHouse的數據定義能夠幫助開發者更深刻地理解和使用ClickHouse。本文大綱（右側分支）👇👇

本文會詳細分析ClickHouse目前最新版本（20.10.3.30）支援的所有數據類型。

數據類型

ClickHouse的數據類型從大體的來看主要包括：

• 數值類型
• 字元串類型
• 日期時間類型
• 複合類型
• 特殊類型

這裡做一份匯總的表格👇

ClickHouse中類型嚴格區分大小寫，一般為駝峰表示，例如DateTime不能寫成DATETIME或者DATE_TIME，同理，UUID不能寫成uuid

下面就每種類型再詳細分析其用法。

數值類型

數值類型主要包括整型數值、浮點數值、高精度數值和特殊的布爾值。

整型

整型數值指固定長度（bit數）的整數，可以使用帶符號和無符號的表示方式。先看整型數值的表示範圍👇👇

帶符號整型數值：

Int128和Int256能表示的整數範圍十分巨大，佔用的位元組大小也隨之增大，一般很少使用。

無符號整型數值：

值得注意的是，UInt128類型並不支援，因此不存在UInt128。UInt256能表示的整數範圍十分巨大，佔用的位元組大小也隨之增大，一般很少使用。

一般在使用MySQL的時候會定義一個BIGINT UNSIGNED類型的自增趨勢的主鍵，在ClickHouse中對標UInt64類型。做一個小測試，在ClickHouse命令行客戶端中執行：

SELECT \
toInt8(127) AS a,toTypeName(a) AS aType, \
toInt16(32767) AS b,toTypeName(b) AS bType, \
toInt32(2147483647) AS c,toTypeName(c) AS cType, \
toInt64(9223372036854775807) AS d,toTypeName(d) AS dType, \
toInt128(170141183460469231731687303715884105727) AS e,toTypeName(e) AS eType, \
toInt256(57896044618658097711785492504343953926634992332820282019728792003956564819967) AS f,toTypeName(f) AS fType, \
toUInt8(255) AS g,toTypeName(g) AS gType, \
toUInt16(65535) AS h,toTypeName(h) AS hType, \
toUInt32(4294967295) AS i,toTypeName(i) AS iType, \
toUInt64(18446744073709551615) AS j,toTypeName(j) AS jType, \
toUInt256(115792089237316195423570985008687907853269984665640564039457584007913129639935) AS k,toTypeName(k) AS kType; 

輸出結果：

SELECT
    toInt8(127) AS a,
    toTypeName(a) AS aType,
    toInt16(32767) AS b,
    toTypeName(b) AS bType,
    toInt32(2147483647) AS c,
    toTypeName(c) AS cType,
    toInt64(9223372036854775807) AS d,
    toTypeName(d) AS dType,
    toInt128(1.7014118346046923e38) AS e,
    toTypeName(e) AS eType,
    toInt256(5.78960446186581e76) AS f,
    toTypeName(f) AS fType,
    toUInt8(255) AS g,
    toTypeName(g) AS gType,
    toUInt16(65535) AS h,
    toTypeName(h) AS hType,
    toUInt32(4294967295) AS i,
    toTypeName(i) AS iType,
    toUInt64(18446744073709551615) AS j,
    toTypeName(j) AS jType,
    toUInt256(1.157920892373162e77) AS k,
    toTypeName(k) AS kType

┌───a─┬─aType─┬─────b─┬─bType─┬──────────c─┬─cType─┬───────────────────d─┬─dType─┬────────────────────────────────────────e─┬─eType──┬────────────────────f─┬─fType──┬───g─┬─gType─┬─────h─┬─hType──┬──────────i─┬─iType──┬────────────────────j─┬─jType──┬──────────────────────────────────────────────────────────────────────────────k─┬─kType───┐
│ 127 │ Int8  │ 32767 │ Int16 │ 2147483647 │ Int32 │ 9223372036854775807 │ Int64 │ -170141183460469231731687303715884105728 │ Int128 │ -9223372036854775808 │ Int256 │ 255 │ UInt8 │ 65535 │ UInt16 │ 4294967295 │ UInt32 │ 18446744073709551615 │ UInt64 │ 115792089237316195423570985008687907853269984665640564039448360635876274864128 │ UInt256 │
└─────┴───────┴───────┴───────┴────────────┴───────┴─────────────────────┴───────┴──────────────────────────────────────────┴────────┴──────────────────────┴────────┴─────┴───────┴───────┴────────┴────────────┴────────┴──────────────────────┴────────┴────────────────────────────────────────────────────────────────────────────────┴─────────┘

1 rows in set. Elapsed: 0.009 sec.

尷尬，上面的shell執行結果有點長，變形了。

浮點數

浮點數包括單精度浮點數Float32和雙精度浮點數Float64👇👇

可以做一個小測試：

f5abc88ff7e4 :) SELECT toFloat32('0.1234567890') AS a,toTypeName(a);

SELECT
    toFloat32('0.1234567890') AS a,
    toTypeName(a)

┌──────────a─┬─toTypeName(toFloat32('0.1234567890'))─┐
│ 0.12345679 │ Float32                               │
└────────────┴───────────────────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT toFloat32('0.0123456789') AS a,toTypeName(a);

SELECT
    toFloat32('0.0123456789') AS a,
    toTypeName(a)

┌───────────a─┬─toTypeName(toFloat32('0.0123456789'))─┐
│ 0.012345679 │ Float32                               │
└─────────────┴───────────────────────────────────────┘

1 rows in set. Elapsed: 0.036 sec.

f5abc88ff7e4 :) SELECT toFloat64('0.12345678901234567890') AS a,toTypeName(a);

SELECT
    toFloat64('0.12345678901234567890') AS a,
    toTypeName(a)

┌───────────────────a─┬─toTypeName(toFloat64('0.12345678901234567890'))─┐
│ 0.12345678901234568 │ Float64                                         │
└─────────────────────┴─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT toFloat64('0.01234567890123456789') AS a,toTypeName(a);

SELECT
    toFloat64('0.01234567890123456789') AS a,
    toTypeName(a)

┌────────────────────a─┬─toTypeName(toFloat64('0.01234567890123456789'))─┐
│ 0.012345678901234568 │ Float64                                         │
└──────────────────────┴─────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

特別地，與標準的SQL相比，ClickHouse支援如下特殊的浮點數類別：

• Inf – 表示正無窮
• -Inf – 表示負無窮
• NaN – 表示不是數字

驗證一下：

f5abc88ff7e4 :) SELECT divide(0.5,0);

SELECT 0.5 / 0

┌─divide(0.5, 0)─┐
│            inf │
└────────────────┘

1 rows in set. Elapsed: 0.007 sec.

f5abc88ff7e4 :) SELECT divide(-0.5,0);

SELECT -0.5 / 0

┌─divide(-0.5, 0)─┐
│            -inf │
└─────────────────┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT divide(0.0,0.0);

SELECT 0. / 0.

┌─divide(0., 0.)─┐
│            nan │
└────────────────┘

1 rows in set. Elapsed: 0.005 sec.

高精度數值

高精度數值類型Decimal一般又稱為為定點數，可以指定總位數和固定位數小數點，表示一定範圍內的精確數值。Decimal的原生表示形式為Decimal(P,S)，兩個參數的意義是：

• P：代表精度，決定總位數（也就是決定整數部分加上小數部分一共有多少位數字），取值範圍是[1,76]
• S：代表規模（scale），決定小數位數，取值範圍是[0,P]

Decimal(P,S)衍生出的簡單表示形式有：Decimal32(S)、Decimal64(S)、Decimal128(S)和Decimal256(S)。見下表：

如果覺得衍生類型不好理解，還是直接使用Decimal(P,S)就行。它的定義格式如下：

column_name Decimal(P,S)

# 如
amount Decimal(10,2)

對於四則運算，使用兩個不同精度的Decimal數值進行（內置函數）運算，運算結果小數位的規則如下（假設S1為左值的小數位，S2為右值的小數位，S為結果小數位）：

• 對於加法和減法，S = max(S1,S2)
• 對於乘法，S = S1 + S2
• 對於除法，S = S1（結果小數位和被除數小數位一致）

f5abc88ff7e4 :) SELECT toDecimal32(2,4) AS x, toDecimal32(2,2) AS y,x+y;

SELECT
    toDecimal32(2, 4) AS x,
    toDecimal32(2, 2) AS y,
    x + y

┌──────x─┬────y─┬─plus(toDecimal32(2, 4), toDecimal32(2, 2))─┐
│ 2.0000 │ 2.00 │                                     4.0000 │
└────────┴──────┴────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.019 sec.

f5abc88ff7e4 :) SELECT toDecimal32(2,4) AS x, toDecimal32(2,5) AS y,y/x

SELECT
    toDecimal32(2, 4) AS x,
    toDecimal32(2, 5) AS y,
    y / x

┌──────x─┬───────y─┬─divide(toDecimal32(2, 5), toDecimal32(2, 4))─┐
│ 2.0000 │ 2.00000 │                                      1.00000 │
└────────┴─────────┴──────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT toDecimal32(2,4) AS x, toDecimal32(2,4) AS y,y*x

SELECT
    toDecimal32(2, 4) AS x,
    toDecimal32(2, 4) AS y,
    y * x

┌──────x─┬──────y─┬─multiply(toDecimal32(2, 4), toDecimal32(2, 4))─┐
│ 2.0000 │ 2.0000 │                                     4.00000000 │
└────────┴────────┴────────────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

重點注意：如果從事的是金融領域等追求準確精度的數值存儲，不能使用浮點數，而應該考慮使用整型或者定點數，舍入儘可能交由程式規則處理，畢竟資料庫是存儲數據的工具，不應該承擔太多處理數據計算的職能。

布爾值

ClickHouse中不存在布爾值類型，官方建議使用UInt8類型，通過值0或1表示false或true。

字元串類型

字元串類型主要包括：

• 不定長（動態長度）字元串String
• 固定長度字元串FixedString(N)，這裡的N是最大位元組數，而不是長度，例如UTF-8字元佔用3個位元組，GBK字元佔用2個位元組
• 特殊字元串UUID（存儲的是數值，只是形式是字元串）

ClickHouse中沒有編碼的概念，字元串可以包含一組任意位元組，這些位元組按原樣存儲和輸出。這個編碼和解碼操作推測完全移交給客戶端完成。一般情況下，推薦使用UTF-8編碼存儲文本類型內容，這樣就能在不進行轉換的前提下讀取和寫入數據。

String

String類型不限制字元串的長度，可以直接替代其他DBMS的VARCHAR、BLOB、CLOB等字元串類型，相比VARCHAR這類要考慮預測數據最大長度，顯然String無比便捷。使用Java語言開發，直接使用String類型承接即可。String類型的數據列的定義如下：

column_name String

FixedString

FixedString類型的數據列的定義如下：

column_name FixedString(N)

FixedString表示固定長度N的字元串，這裡的N代表N個位元組（N bytes），而不是N個字元或者N個碼點（code point）。一些使用FixedString類型的典型場景：

• 二進位表示存儲IP地址，如使用FixedString(16)存儲IPV6地址
• 哈希值的二進位表示形式，如FixedString(16)存儲MD5的二進位值，FixedString(32)存儲SHA256的二進位值

當寫入FixedString類型數據的時候：

• 如果數據位元組數大於N，則會返回一個Too large value for FixedString(N)的異常
• 如果數據位元組數小於N，則會使用null位元組填補剩下的部分

官方文檔提示查詢條件WHERE中如果需要匹配FixedString類型的列，傳入的查詢參數要自行補尾部的\0，否則有可能導致查詢條件失效。也就是更加建議寫入數據和查詢條件都是固定位元組數的參數。

內置函數length()會直接返回N，而內置函數empty()在全為null位元組的前提下會返回1，其他情況返回0。

UUID

UUID這個概念很常見，Java中也有靜態方法java.util.UUID#randomUUID()直接生成UUID，因為其獨特的唯一性有時候可以選擇生成UUID作為資料庫的主鍵類型。ClickHouse直接定義了一種UUID類型，嚴格來說這種類型不是字元串，但是因為在文檔上它的位置順序排在字元串類型之下，日期時間類型之上，形式上看起來也像字元串，並且它僅僅支援字元串類型的內置函數，所以筆者也把它歸類為字元串類型。ClickHouse中的UUID實際上是一個16位元組的數字，它的通用格式如下：

8-4-4-4-4-12

## 例子
61f0c404-5cb3-11e7-907b-a6006ad3dba0

## 零值
00000000-0000-0000-0000-000000000000

UUID類型列定義格式如下：

column_name UUID

可以通過內置函數generateUUIDv4()直接生成UUID數據，測試一下：

f5abc88ff7e4 :) CREATE TABLE test_u(id UInt64,u UUID) ENGINE = Memory;

CREATE TABLE test_u
(
    `id` UInt64,
    `u` UUID
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.018 sec.

f5abc88ff7e4 :) INSERT INTO test_u VALUES (1,generateUUIDv4());

INSERT INTO test_u VALUES

Ok.

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT * FROM test_u;

SELECT *
FROM test_u

┌─id─┬────────────────────────────────────u─┐
│  1 │ fc379d2c-0753-45a3-8589-1ef95ee0d8c9 │
└────┴──────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

日期時間類型

日期時間類型包括Date（表示年月日）、DateTime（表示年月日時分秒）和DateTime64（表示年月日時分秒亞秒）。

Date

Date表示年月日，但是這種類型在ClickHouse中使用2位元組（2 byte -> 16 bit）無符號整數去存儲距離Unix紀元（1970-01-01）的天數，不支援時區，能夠表示的最大年份為2105年。基於這個特性，在插入Date類型數據的時候可以採用yyyy-MM-dd格式或者無符號整數。見下面的測試：

f5abc88ff7e4 :) CREATE TABLE test_dt(date Date) ENGINE = Memory;

CREATE TABLE test_dt
(
    `date` Date
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.025 sec.

f5abc88ff7e4 :) INSERT INTO dt VALUES(1),(2),('0000-00-00'),('2020-11-11');

INSERT INTO dt VALUES


Received exception from server (version 20.10.3):
Code: 60. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Table default.dt doesn't exist..

0 rows in set. Elapsed: 0.007 sec.

f5abc88ff7e4 :) INSERT INTO test_dt VALUES(1),(2),('0000-00-00'),('2020-11-11');

INSERT INTO test_dt VALUES

Ok.

4 rows in set. Elapsed: 0.025 sec.

f5abc88ff7e4 :) SELECT * FROM test_dt;

SELECT *
FROM test_dt

┌───────date─┐
│ 1970-01-02 │
│ 1970-01-03 │
│ 1970-01-01 │
│ 2020-11-11 │
└────────────┘

4 rows in set. Elapsed: 0.005 sec.

Date類型中的0或者’0000-00-00’代表1970-01-01

DateTime

DateTime是通常概念中的年月日時分秒，支援時區，但是不支援毫秒表示，也就是此類型精確到秒。它的定義格式為：

column_name DateTime[(time_zone)]

可以表示的範圍：[1970-01-01 00:00:00, 2105-12-31 23:59:59]。使用DateTime的時候需要注意幾點：

• DateTime時間點實際上保存為Unix時間戳（筆者探究過這裡的單位應該是秒），與時區或者夏時制無關
• DateTime的時區並不存儲在列數據或者結果集中，而是存儲在列元數據中
• 創建表定義DateTime類型的列的時候如果不指定時區，則使用伺服器或者作業系統中設置的默認時區
• 創建表定義DateTime類型的列的時候如果不指定時區，ClickHouse客戶端會使用ClickHouse服務端的時區，也可以通過參數--use_client_time_zone指定
• 可以通過配置值date_time_input_format或date_time_output_format分別指定DateTime類型數據的輸入和輸出格式
• DateTime類型數據插入的時候，整數會被視為Unix時間戳，並且會使用UTC作為時區（零時區），字元串會被視為使用了時區的日期時間（取決於服務或者系統），再基於時區轉化為對應的Unix時間戳進行存儲

可以測試一下：

f5abc88ff7e4 :) CREATE TABLE test_dt(t DateTime,tz DateTime('Asia/Shanghai')) ENGINE = Memory;

CREATE TABLE test_dt
(
    `t` DateTime,
    `tz` DateTime('Asia/Shanghai')
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.029 sec.

f5abc88ff7e4 :) INSERT INTO test_dt VALUES(1605194721,'2020-11-01 00:00:00');   # <-------------- 這裡的1605194721是北京時間2020-11-12 23:25:21的Unix時間戳

INSERT INTO test_dt VALUES

Ok.

1 rows in set. Elapsed: 0.006 sec.

f5abc88ff7e4 :) SELECT * FROM test_dt;

SELECT *
FROM test_dt

┌───────────────────t─┬──────────────────tz─┐
│ 2020-11-12 15:25:21 │ 2020-11-01 00:00:00 │
└─────────────────────┴─────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT toDateTime(t, 'Asia/Shanghai') AS sh_time,toDateTime(tz, 'Europe/London') AS lon_time FROM test_dt;

SELECT
    toDateTime(t, 'Asia/Shanghai') AS sh_time,
    toDateTime(tz, 'Europe/London') AS lon_time
FROM test_dt

┌─────────────sh_time─┬────────────lon_time─┐
│ 2020-11-12 23:25:21 │ 2020-10-31 16:00:00 │
└─────────────────────┴─────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

DateTime64

DateTime64其實和DateTime類型差不多，不過可以額外表示亞秒，所謂亞秒，精度就是10 ^ (-n)（10的負n次方）秒，例如0.1秒、0.01秒等等。它的定義格式為：

column_name DateTime64(precision [, time_zone])

測試一下：

f5abc88ff7e4 :) SELECT toDateTime64(now(), 5, 'Asia/Shanghai') AS column, toTypeName(column) AS x;

SELECT
    toDateTime64(now(), 5, 'Asia/Shanghai') AS column,
    toTypeName(column) AS x

┌────────────────────column─┬─x──────────────────────────────┐
│ 2020-11-12 23:45:56.00000 │ DateTime64(5, 'Asia/Shanghai') │
└───────────────────────────┴────────────────────────────────┘

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) CREATE TABLE test_dt64(t DateTime64(2),tz DateTime64(3,'Asia/Shanghai')) ENGINE = Memory;

CREATE TABLE test_dt64
(
    `t` DateTime64(2),
    `tz` DateTime64(3, 'Asia/Shanghai')
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.017 sec.

f5abc88ff7e4 :) INSERT INTO test_dt64 VALUES(1605194721,'2020-11-01 00:00:00');

INSERT INTO test_dt64 VALUES

Ok.

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT * FROM test_dt64;

SELECT *
FROM test_dt64

┌──────────────────────t─┬──────────────────────tz─┐
│ 1970-07-05 18:52:27.21 │ 2020-11-01 00:00:00.000 │
└────────────────────────┴─────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

複合類型

複合類型主要包括數組Array(T)、元組Tuple(T,S....R)、枚舉Enum和嵌套Nested，這裡的複合指的是同類型多元素複合或者多類型多元素複合。

Array

數組類型Array(T)中的T可以是任意的數據類型（但是同一個數組的元素類型必須唯一），類似於泛型數組T[]。它的定義如下：

column_name Array(T)

## 定義
major Array(String)

## 寫入
VALUES (['a','b','c']), (['A','B','C'])

編寫測試例子：

f5abc88ff7e4 :) CREATE TABLE test_arr(a Array(UInt8),b Array(String)) ENGINE = Memory;

CREATE TABLE test_arr
(
    `a` Array(UInt8),
    `b` Array(String)
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.017 sec.

f5abc88ff7e4 :) INSERT INTO test_arr VALUES([1,2,3],['throwable','doge']);

INSERT INTO test_arr VALUES

Ok.

1 rows in set. Elapsed: 0.005 sec.

f5abc88ff7e4 :) SELECT * FROM test_arr;

SELECT *
FROM test_arr

┌─a───────┬─b────────────────────┐
│ [1,2,3] │ ['throwable','doge'] │
└─────────┴──────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :)

需要注意的是：

• 可以使用array()函數或者[]快速創建數組
• 快速創建數組時，ClickHouse會自動將參數類型定義為可以存儲所有列出的參數的”最窄”的數據類型，可以理解為最小代價原則
• ClickHouse無法確定數組的數據類型（常見的是快速創建數組使用了多類型元素），將會返回一個異常（例如SELECT array(1, 'a')是非法的）
• 如果數組中的元素存在NULL，元素類型將會變為Nullable(T)

f5abc88ff7e4 :) SELECT array(1, 2) AS x, toTypeName(x);

SELECT
    [1, 2] AS x,
    toTypeName(x)

┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

f5abc88ff7e4 :) SELECT [1, 2, NULL] AS x, toTypeName(x);

SELECT
    [1, 2, NULL] AS x,
    toTypeName(x)

┌─x──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))   │
└────────────┴──────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT array(1, 'a')

SELECT [1, 'a']


Received exception from server (version 20.10.3):
Code: 386. DB::Exception: Received from clickhouse-server:9000. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not.

0 rows in set. Elapsed: 0.015 sec.

Tuple

元組（Tuple(S,T...R)）類型的數據由1-n個元素組成，每個元素都可以使用單獨（可以不相同）的數據類型。它的定義如下：

column_name Tuple(S,T...R)

## 定義
x_col Tuple(UInt64, String, DateTime)

## 寫入
VALUES((1,'throwables','2020-11-14 00:00:00')),((2,'throwables','2020-11-13 00:00:00'))

需要注意的是：

• 類似於數組類型Array，元組Tuple對於每個元素的類型推斷也是基於最小代價原則
• 創建表的時候明確元組Tuple中元素的類型定義後，數據寫入的時候元素的類型會進行檢查，必須一一對應，否則會拋出異常（如x_col Tuple(UInt64, String)只能寫入(1,'a')而不能寫入('a','b')）

f5abc88ff7e4 :) SELECT tuple(1,'1',NULL) AS x, toTypeName(x);

SELECT
    (1, '1', NULL) AS x,
    toTypeName(x)

┌─x────────────┬─toTypeName(tuple(1, '1', NULL))─────────┐
│ (1,'1',NULL) │ Tuple(UInt8, String, Nullable(Nothing)) │
└──────────────┴─────────────────────────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) CREATE TABLE test_tp(id UInt64, a Tuple(UInt64,String)) ENGINE = Memory;

CREATE TABLE test_tp
(
    `id` UInt64,
    `a` Tuple(UInt64, String)
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.018 sec.

f5abc88ff7e4 :) INSERT INTO test_tp VALUES(1,(999,'throwable')),(2,(996,'doge'));

INSERT INTO test_tp VALUES

Ok.

2 rows in set. Elapsed: 0.003 sec.

f5abc88ff7e4 :) INSERT INTO test_tp VALUES(1,('doge','throwable'));

INSERT INTO test_tp VALUES


Exception on client:
Code: 6. DB::Exception: Cannot parse string 'doge' as UInt64: syntax error at begin of string. Note: there are toUInt64OrZero and toUInt64OrNull functions, which returns zero/NULL instead of throwing exception.: while executing 'FUNCTION CAST(_dummy_0, 'Tuple(UInt64, String)') Tuple(UInt64, String) = CAST(_dummy_0, 'Tuple(UInt64, String)')': data for INSERT was parsed from query

這裡可以看出ClickHouse在處理Tuple類型數據寫入發現類型不匹配的時候，會嘗試進行類型轉換，也就是按照寫入的數據對應位置的元素類型和列定義Tuple中對應位置的類型做轉換（如果類型一致則不需要轉換），類型轉換異常就會拋出異常。類型為Tuple(UInt64,String)實際上可以寫入('111','222')或者(111,'222')，但是不能寫入('a','b')。轉換過程會調用內置函數，如無意外會消耗額外的性能和時間，因此更推薦在寫入數據的時候確保每個位置元素和列定義時候的元素類型一致。

Enum

枚舉類型Enum算是ClickHouse中獨創的複合類型，它使用有限鍵值對K-V(String:Int)的形式定義數據，有點像Java中的HashMap結構，而KEY和VALUE都不允許NULL值，但是KEY允許設置為空字元串。Enum的數據查詢一般返回是KEY的集合，寫入可以是KEY也可以是VALUE。它的定義如下：

column_name Enum('str1' = num1, 'str2' = num2 ...)

# 例如
sex Enum('male' = 1,'female' = 2,'other' = 3)

Enum可以表示的值範圍是16位，也就是VALUE只能從[-32768,32767]中取值。它衍生出兩種簡便的類型Enum8（本質是(String:Int18)，代表值範圍是8位，也就是[-128,127]）和Enum16（本質是(String:Int16)，代表值範圍是16位，也就是[-32768,32767]），如果直接使用原生類型Enum則會根據實際定義的K-V對數量最終決定具體選用Enum8或是Enum16存儲數據。測試一下：

f5abc88ff7e4 :) CREATE TABLE test_e(sex Enum('male' = 1,'female' = 2,'other' = 3)) ENGINE = Memory;

CREATE TABLE test_e
(
    `sex` Enum('male' = 1, 'female' = 2, 'other' = 3)
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.021 sec.

f5abc88ff7e4 :) INSERT INTO test_e VALUES(1),(2),('other');

INSERT INTO test_e VALUES

Ok.

3 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT sex,CAST(sex,'Int8') FROM test_e

SELECT
    sex,
    CAST(sex, 'Int8')
FROM test_e

┌─sex────┬─CAST(sex, 'Int8')─┐
│ male   │                 1 │
│ female │                 2 │
│ other  │                 3 │
└────────┴───────────────────┘

3 rows in set. Elapsed: 0.005 sec.

ClickHouse中的Enum本質就是String:Int，特化一個這樣的類型，方便定義有限集合的鍵值對，枚舉的VALUE是整型數值，會直接參与ORDER BY、GROUP BY、IN、DISTINCT等操作。按照常規思維來說，排序、聚合、去重等操作使用整型對比使用字元串在性能上應該有不錯的提升，所以在使用有限狀態集合的場景使用Enum類型比使用String定義枚舉集合理論上有天然優勢。

Nested

嵌套類型Nested算是一種比較奇特的類型。如果使用過GO語言，Nested類型數據列定義的時候有點像GO語言的結構體：

column_name Nested(
    field_name_1 Type1,
    field_name_2 Type2
)

## 定義
major Nested(
    id UInt64,
    name String
)

## 寫入
VALUES ([1,2],['Math','English'])

## 查詢
SELECT major.id,major.name FROM

ClickHouse的嵌套類型和固有思維中傳統的嵌套類型大有不同，它的本質是一種多維數組結構，可以這樣理解：

major Nested(
    id UInt64,
    name String
)

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

major.id Array(UInt64)
major.name Array(String)

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ Java中的實體類
class Entity {

    Long id;
    List<Major> majors;
}

class Major {
    
    Long id;
    String name;
}

嵌套類型行與行之間的數組長度無須固定，但是同一行中嵌套表內每個數組的長度必須對齊，例如：

測試一下：

f5abc88ff7e4 :) CREATE TABLE test_nt(id UInt64,n Nested(id UInt64,name String)) ENGINE Memory;

CREATE TABLE test_nt
(
    `id` UInt64,
    `n` Nested(    id UInt64,     name String)
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.020 sec.

f5abc88ff7e4 :) INSERT INTO test_nt VALUES (1,[1,2,3],['a','b','c']),(2,[999],['throwable']);

INSERT INTO test_nt VALUES

Ok.

2 rows in set. Elapsed: 0.003 sec.

f5abc88ff7e4 :) SELECT * FROM test_nt;

SELECT *
FROM test_nt

┌─id─┬─n.id────┬─n.name────────┐
│  1 │ [1,2,3] │ ['a','b','c'] │
│  2 │ [999]   │ ['throwable'] │
└────┴─────────┴───────────────┘

2 rows in set. Elapsed: 0.005 sec.

可以通過ARRAY JOIN子句實現嵌套類型的子表數據平鋪，類似於MySQL中的行轉列：

f5abc88ff7e4 :) SELECT n.id,n.name FROM test_nt ARRAY JOIN n;

SELECT
    n.id,
    n.name
FROM test_nt
ARRAY JOIN n

┌─n.id─┬─n.name────┐
│    1 │ a         │
│    2 │ b         │
│    3 │ c         │
│  999 │ throwable │
└──────┴───────────┘

特殊類型

特殊類型主要包括Nullable、域名Domain和Nothing。

Nullable

Nullable不算一種獨立的類型，它是一種其他類型的類似輔助修飾符的修飾類型，與其他基本類型搭配使用。如果熟悉Java中的java.lang.Optional，Nullable的功能就是與Optional相似，表示某個基本數據類型可以為Null值（寫入時候不傳值）。它的定義如下：

column_name Nullable(TypeName)

# 如
amount Nullable(Decimal(10,2))
age Nullable(UInt16)
createTime Nullable(DateTime)

需要注意幾點：

• NULL是Nullable的默認值，也就是INSERT時候可以使用NULL指定空值或者不傳值
• 不能使用Nullable修飾複合數據類型，但是複合數據類型中的元素可以使用Nullable修飾
• Nullable修飾的列不能添加索引
• 官網文檔有一段提醒：Nullable幾乎總是造成負面的性能影響，在設計資料庫的時候必須牢記這一點，這是因為Nullable中的列的NULL值和列的非NULL值會存放在兩個不同的文件，所以不能添加索引，查詢和寫入還會涉及到非單個文件的操作

測試一下：

f5abc88ff7e4 :) CREATE TABLE test_null(id UInt64,name Nullable(String)) ENGINE = Memory;

CREATE TABLE test_null
(
    `id` UInt64,
    `name` Nullable(String)
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.022 sec.

f5abc88ff7e4 :) INSERT INTO test_null VALUES(1,'throwable'),(2,NULL);

INSERT INTO test_null VALUES

Ok.

2 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :) SELECT * FROM test_null;

SELECT *
FROM test_null

┌─id─┬─name──────┐
│  1 │ throwable │
│  2 │ NULL      │
└────┴───────────┘ 

2 rows in set. Elapsed: 0.004 sec.

f5abc88ff7e4 :)

Domain

Domain類型也是ClickHouse獨有的類型，是基於其他類型進行封裝的一種特殊類型，包括IPv4（本質上是基於UInt32封裝，以緊湊的二進位形式存儲）和IPv6（本質上是基於FixedString(16)封裝）兩種類型。它們的定義如下：

column_name IPv4
column_name IPv6

Domain類型的局限性：

• 不能通過ALTER TABLE改變當前Domain類型列的類型
• 不能通過字元串隱式轉換從其他列或者其他表插入Domain類型的列數據，例如A表有String類型存儲的IP地址格式的列，無法導入B表中Domain類型的列
• Domain類型對存儲的值不做限制，但是寫入數據的時候會校驗是否滿足IPv4或者IPv6的格式

此外，Domain類型數據的INSERT或者SELECT都做了人性化格式化操作，所以在使用INSERT語句的時候可以直接使用字元串形式寫入，查詢的結果雖然在客戶端命令行展示的是可讀的”字元串”，但是如果想查詢到字元串格式的結果需要使用內置函數IPv4NumToString()和IPv6NumToString()（這裡也就說明了不支援隱式類型轉換，文檔中也提到CAST()內置函數可以把IPv4轉化為UInt32，把IPv6轉化為FixedString(16)）。測試一下：

f5abc88ff7e4 :) CREATE TABLE test_d(id UInt64,ip IPv4) ENGINE = Memory;

CREATE TABLE test_d
(
    `id` UInt64,
    `ip` IPv4
)
ENGINE = Memory

Ok.

0 rows in set. Elapsed: 0.029 sec.

f5abc88ff7e4 :) INSERT INTO test_d VALUES(1,'192.168.1.0');

INSERT INTO test_d VALUES

Ok.

1 rows in set. Elapsed: 0.003 sec.

f5abc88ff7e4 :) SELECT ip,IPv4NumToString(ip) FROM test_d;

SELECT
    ip,
    IPv4NumToString(ip)
FROM test_d

┌──────────ip─┬─IPv4NumToString(ip)─┐
│ 192.168.1.0 │ 192.168.1.0         │
└─────────────┴─────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

Nothing

Nothing不是一種顯式的數據類型，它存在的唯一目的就是表示不希望存在值的情況，使用者也無法創建Nothing類型。例如字面量NULL其實是Nullable(Nothing)類型，空的數組array()（內置函數）是Nothing類型。

f5abc88ff7e4 :) SELECT toTypeName(array());

SELECT toTypeName([])

┌─toTypeName(array())─┐
│ Array(Nothing)      │
└─────────────────────┘

1 rows in set. Elapsed: 0.006 sec.

所有類型的零值

ClickHouse中所有列定義完畢之後如果沒有定義默認值（這個比較複雜，在以後介紹DDL相關的文章的時候再說），如果不使用Nullable，那麼寫入數據的時候空的列會被填充對應類型的零值。各類型零值歸類如下：

• 數值類型的零值為數字0
• 字元串類型的零值為空字元串''，UUID的零值為00000000-0000-0000-0000-000000000000
• 日期時間類型的零值為其存儲的時間偏移量的零值
• Enum類型是定義的VALUE值最小的為零值
• Array類型的零值為[]
• Tuple類型的零值為[類型1的零值,類型2的零值......]
• Nested類型的零值為多維數組並且每個數組都是[]
• 特殊地，可以認為Nullable修飾的類型的零值為NULL

使用JDBC驅動

這裡模擬一個場景，基本上使用所有的ClickHouse中常用的類型。定義一張訂單表：

CREATE TABLE ShoppingOrder (
  id UInt64 COMMENT '主鍵',
  orderId UUID COMMENT '訂單ID',
  amount Decimal(10,2) COMMENT '金額',
  createTime DateTime COMMENT '創建日期時間',
  customerPhone FixedString(11) COMMENT '顧客手機號',
  customerName String COMMENT '顧客姓名',
  orderStatus Enum('init' = 0,'cancel' = -1,'paid' = 1) COMMENT '訂單狀態',
  goodsIdList Array(UInt64) COMMENT '貨物ID數組',
  address Nested(province String, city String, street String, houseNumber UInt64) COMMENT '收貨地址'
) ENGINE = Memory;

// 合成一行
CREATE TABLE ShoppingOrder (id UInt64 COMMENT '主鍵',orderId UUID COMMENT '訂單ID',amount Decimal(10,2) COMMENT '金額',createTime DateTime COMMENT '創建日期時間',customerPhone FixedString(11) COMMENT '顧客手機號',customerName String COMMENT '顧客姓名', orderStatus Enum('init' = 0,'cancel' = -1,'paid' = 1) COMMENT '訂單狀態',goodsIdList Array(UInt64) COMMENT '貨物ID數組',address Nested(province String, city String, street String, houseNumber UInt64) COMMENT '收貨地址') ENGINE = Memory;

創建完成後，調用DESC ShoppingOrder：

f5abc88ff7e4 :) DESC ShoppingOrder;

DESCRIBE TABLE ShoppingOrder

┌─name────────────────┬─type─────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment──────┬─codec_expression─┬─ttl_expression─┐
│ id                  │ UInt64                                       │              │                    │ 主鍵         │                  │                │
│ orderId             │ UUID                                         │              │                    │ 訂單ID       │                  │                │
│ amount              │ Decimal(10, 2)                               │              │                    │ 金額         │                  │                │
│ createTime          │ DateTime                                     │              │                    │ 創建日期時間 │                  │                │
│ customerPhone       │ FixedString(11)                              │              │                    │ 顧客手機號   │                  │                │
│ customerName        │ String                                       │              │                    │ 顧客姓名     │                  │                │
│ orderStatus         │ Enum8('cancel' = -1, 'init' = 0, 'paid' = 1) │              │                    │ 訂單狀態     │                  │                │
│ goodsIdList         │ Array(UInt64)                                │              │                    │ 貨物ID數組   │                  │                │
│ address.province    │ Array(String)                                │              │                    │ 收貨地址     │                  │                │
│ address.city        │ Array(String)                                │              │                    │ 收貨地址     │                  │                │
│ address.street      │ Array(String)                                │              │                    │ 收貨地址     │                  │                │
│ address.houseNumber │ Array(UInt64)                                │              │                    │ 收貨地址     │                  │                │
└─────────────────────┴──────────────────────────────────────────────┴──────────────┴────────────────────┴──────────────┴──────────────────┴────────────────┘

12 rows in set. Elapsed: 0.004 sec.

引入clickhouse-jdbc依賴：

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>

編寫測試案例：

@RequiredArgsConstructor
@Getter
public enum OrderStatus {

    INIT("init", 0),

    CANCEL("cancel", -1),

    PAID("paid", 1),

    ;

    private final String type;
    private final Integer status;

    public static OrderStatus fromType(String type) {
        for (OrderStatus status : OrderStatus.values()) {
            if (Objects.equals(type, status.getType())) {
                return status;
            }
        }
        return OrderStatus.INIT;
    }
}

@Data
public class Address {

    private String province;

    private String city;

    private String street;

    private Long houseNumber;
}

@Data
public class ShoppingOrder {

    private Long id;
    private String orderId;
    private BigDecimal amount;
    private OffsetDateTime createTime;
    private String customerPhone;
    private String customerName;
    private Integer orderStatus;
    private Set<Long> goodsIdList;

    /**
     * 這裡實際上只有一個元素
     */
    private List<Address> addressList;
}

 @Test
public void testInsertAndSelectShoppingOrder() throws Exception {
    ClickHouseProperties props = new ClickHouseProperties();
    props.setUser("root");
    props.setPassword("root");
    // 不創建資料庫的時候會有有個全局default資料庫
    ClickHouseDataSource dataSource = new ClickHouseDataSource("jdbc:clickhouse://localhost:8123/default", props);
    ClickHouseConnection connection = dataSource.getConnection();
    PreparedStatement ps = connection.prepareStatement("INSERT INTO ShoppingOrder VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
    // 這裡可以考慮使用Snowflake演算法生成自增趨勢主鍵
    long id = System.currentTimeMillis();
    int idx = 1;
    ps.setLong(idx ++, id);
    ps.setString(idx ++, "00000000-0000-0000-0000-000000000000");
    ps.setBigDecimal(idx ++, BigDecimal.valueOf(100L));
    ps.setTimestamp(idx ++, new Timestamp(System.currentTimeMillis()));
    ps.setString(idx ++, "12345678901");
    ps.setString(idx ++, "throwable");
    ps.setString(idx ++, "init");
    ps.setString(idx ++, "[1,999,1234]");
    ps.setString(idx ++, "['廣東省']");
    ps.setString(idx ++, "['廣州市']");
    ps.setString(idx ++, "['X街道']");
    ps.setString(idx , "[10087]");
    ps.execute();
    ClickHouseStatement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery("SELECT * FROM ShoppingOrder");
    List<ShoppingOrder> orders = Lists.newArrayList();
    while (rs.next()) {
        ShoppingOrder order = new ShoppingOrder();
        order.setId(rs.getLong("id"));
        order.setOrderId(rs.getString("orderId"));
        order.setAmount(rs.getBigDecimal("amount"));
        order.setCreateTime(OffsetDateTime.ofInstant(rs.getTimestamp("createTime").toInstant(), ZoneId.systemDefault()));
        order.setCustomerPhone(rs.getString("customerPhone"));
        order.setCustomerName(rs.getString("customerName"));
        String orderStatus = rs.getString("orderStatus");
        order.setOrderStatus(OrderStatus.fromType(orderStatus).getStatus());
        // Array(UInt64) -> Array<BigInteger>
        Array goodsIdList = rs.getArray("goodsIdList");
        BigInteger[] goodsIdListValue = (BigInteger[]) goodsIdList.getArray();
        Set<Long> goodsIds = Sets.newHashSet();
        for (BigInteger item : goodsIdListValue) {
            goodsIds.add(item.longValue());
        }
        order.setGoodsIdList(goodsIds);
        List<Address> addressList = Lists.newArrayList();
        // Array(String) -> Array<String>
        Array province = rs.getArray("address.province");
        List<String> provinceList = arrayToList(province);
        // Array(String) -> Array<String>
        Array city = rs.getArray("address.city");
        List<String> cityList = arrayToList(city);
        // Array(String) -> Array<String>
        Array street = rs.getArray("address.street");
        List<String> streetList = arrayToList(street);
        // UInt64 -> Array<BigInteger>
        Array houseNumber = rs.getArray("address.houseNumber");
        BigInteger[] houseNumberValue = (BigInteger[]) houseNumber.getArray();
        List<Long> houseNumberList = Lists.newArrayList();
        for (BigInteger item : houseNumberValue) {
            houseNumberList.add(item.longValue());
        }
        int size = provinceList.size();
        for (int i = 0; i < size; i++) {
            Address address = new Address();
            address.setProvince(provinceList.get(i));
            address.setCity(cityList.get(i));
            address.setStreet(streetList.get(i));
            address.setHouseNumber(houseNumberList.get(i));
            addressList.add(address);
        }
        order.setAddressList(addressList);
        orders.add(order);
    }
    System.out.println("查詢結果:" + JSON.toJSONString(orders));
}

private List<String> arrayToList(Array array) throws Exception {
    String[] v = (String[]) array.getArray();
    return Lists.newArrayList(Arrays.asList(v));
}

輸出結果：

查詢結果:
[{
	"addressList": [{
		"city": "廣州市",
		"houseNumber": 10087,
		"province": "廣東省",
		"street": "X街道"
	}],
	"amount": 100.00,
	"createTime": "2020-11-17T23:53:34+08:00",
	"customerName": "throwable",
	"customerPhone": "12345678901",
	"goodsIdList": [1, 1234, 999],
	"id": 1605628412414,
	"orderId": "00000000-0000-0000-0000-000000000000",
	"orderStatus": 0
}]

客戶端查詢：

f5abc88ff7e4 :) SELECT * FROM ShoppingOrder;

SELECT *
FROM ShoppingOrder

┌────────────id─┬──────────────────────────────orderId─┬─amount─┬──────────createTime─┬─customerPhone─┬─customerName─┬─orderStatus─┬─goodsIdList──┬─address.province─┬─address.city─┬─address.street─┬─address.houseNumber─┐
│ 1605628412414 │ 00000000-0000-0000-0000-000000000000 │ 100.00 │ 2020-11-17 15:53:34 │ 12345678901   │ throwable    │ init        │ [1,999,1234] │ ['廣東省']       │ ['廣州市']   │ ['X街道']      │ [10087]             │
└───────────────┴──────────────────────────────────────┴────────┴─────────────────────┴───────────────┴──────────────┴─────────────┴──────────────┴──────────────────┴──────────────┴────────────────┴─────────────────────┘

1 rows in set. Elapsed: 0.004 sec.

實踐表明：

• ClickHouseDataType中可以查看ClickHouse各種數據類型和Java數據類型以及SQLType之間的對應關係，如UInt64 => BigInteger
• ClickHouse的Array類型寫入數據的時候可以使用[元素x,元素y]的格式，也可以使用java.sql.Array進行傳遞，具體是ClickHouseArray，讀取數據也可以類似地操作
• 枚舉Enum會直接轉換為Java中的String類型

小結

本文已經十分詳細分析了ClickHouse的各種數據類型的功能和基本使用例子，下一篇文章將會分析DDL部分。ClickHouse中的很多DDL的用法比較獨特，和傳統關係型資料庫的DDL區別比較大。

個人部落格

• Throwable’s Blog

（本文完 c-7-d e-a-20201118 最近玩《王國守衛戰-復仇》鴿了很久）