ClickHouse SQL Reference （四）數據類型

Tuple(T1, T2, …)

元素元組，每個元素都有一個單獨的類型。元組必須至少包含一個元素。

元組用于臨時列分組。在查詢中使用IN表達式時，以及指定lambda函數的某些形式參數時，可以對列進行分組。有關更多信息，請參閱IN操作符和高階函數部分。

元組可以是查詢的結果。在這種情況下，對于JSON以外的文本格式，值在括號中以逗號分隔。在JSON格式中，元組作為數組輸出(在方括號中)。

FixedString(N)

固定長度的N字節字符串(既不是字符也不是碼點)。

要聲明FixedString類型的列，請使用以下語法:

<column_name> FixedString(N)

Map(key, value)

Map(key, value) 數據類型存儲 key:value 鍵值對。

Date

自1970-01-01以來的天數存儲在兩個字節中(unsigned)。允許在Unix時代開始后將值存儲到編譯階段的常量(目前,這是在2149年之前,但最終完全支持的年份為2148)。

支持的取值范圍:[1970-01-01,2149-06-06]。

存儲日期值時不帶時區。

Date32

一個日期。支持與DateTime64相同的日期范圍。以本機字節順序存儲為有符號32位整數，其值表示自1970-01-01以來的天數(0表示1970-01-01，負值表示1970年之前的天數)。

DateTime

允許存儲瞬間的時間，可以表示為日歷日期和一天的時間。

DateTime([timezone])

支持的取值范圍:[1970-01-01 00:00:00,2106-02-07 06:28:15]。

精度:1秒。

使用摘要

時間點被保存為Unix時間戳，與時區或夏令時無關。時區影響DateTime類型的值如何以文本格式顯示，以及如何解析指定為字符串的值(’ 2020-01-01 05:00:01 ')。

與時區無關的Unix時間戳存儲在表中，時區用于在數據導入/導出期間將其轉換為文本格式或返回文本格式，或者用于對值進行日歷計算(例如:toDate, toHour函數等)。時區不存儲在表的行中(或結果集中)，而是存儲在列元數據中。

支持的時區列表可以在IANA時區數據庫中找到，也可以通過SELECT * FROM system.time_zones查詢。這份列表也可以在維基百科上找到。

在創建表時，可以顯式地為DateTime類型列設置時區。例如:DateTime('UTC')。如果沒有設置時區，ClickHouse將使用服務器設置或ClickHouse服務器啟動時操作系統設置中的時區參數值。

如果在初始化數據類型時沒有顯式設置時區，則clickhouse-client默認應用服務器時區。要使用客戶端時區，請使用--use_client_time_zone參數運行clickhouse-client。

ClickHouse根據date_time_output_format設置的值輸出值。默認為YYYY-MM-DD hh:mm:ss文本格式。此外，還可以使用formatDateTime函數更改輸出。

在向ClickHouse插入數據時，可以根據date_time_input_format設置的值，使用不同格式的日期和時間字符串。

DateTime64

允許存儲時間上的瞬間，可以表示為日歷日期和一天的時間，具有定義的亞秒精度

精度大小(precision):10^-precision精度秒。取值范圍:[0,9]。通常使用- 3(毫秒)，6(微秒)，9(納秒)。

DateTime64(precision, [timezone])

在內部，將數據存儲為自epoch start (1970-01-01 00:00:00 UTC)以來的一些’ ticks '作為Int64。刻度分辨率由precision參數決定。此外，DateTime64類型可以存儲整個列相同的時區，這會影響DateTime64類型的值如何以文本格式顯示，以及如何解析指定為字符串的值(’ 2020-01-01 05:00:01.000 ')。時區不存儲在表的行中(或結果集中)，而是存儲在列元數據中。具體請參見DateTime。

支持的取值范圍:[1900-01-01 00:00:00,2299-12-31 23:59:59.99999999]

注:最大值精度為8。如果使用9位(納秒)的最大精度，則支持的最大值為UTC時間的222-04-11 23:47:16。

Enum

由命名值(named values)組成的枚舉類型。

命名值可以聲明為'string' = integer 對或'string'名稱。ClickHouse僅存儲數字，但支持通過名稱對值進行操作。

ClickHouse支持:

8 bit Enum。最多可以包含256個[- 128,127]范圍內的枚舉值。
16-bit Enum。它最多可以包含65536個在[-32768,32767]范圍內枚舉的值。

ClickHouse在插入數據時自動選擇Enum類型。您還可以使用Enum8或Enum16類型來確定存儲的大小。

用例

下面我們創建一個列類型為Enum8('hello' = 1， 'world' = 2)的表:

CREATE TABLE t_enum
(x Enum('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog

類似地，您可以省略數字。ClickHouse將自動分配連續的數字。默認從1開始分配號碼。

CREATE TABLE t_enum
(x Enum('hello', 'world')
)
ENGINE = TinyLog

您還可以為名字指定合法的起始編號。

CREATE TABLE t_enum
(x Enum('hello' = 1, 'world')
)
ENGINE = TinyLog


CREATE TABLE t_enum
(`x` Enum8('hello' = -129, 'world')
)
ENGINE = TinyLogQuery id: d68d56f5-1aa9-4d5b-8b34-a9dfef6b84a6Elapsed: 0.287 sec. Received exception from server (version 24.2.1):
Code: 69. DB::Exception: Received from localhost:9000. DB::Exception: Value -129 for element 'hello' exceeds range of Enum8. (ARGUMENT_OUT_OF_BOUND)

列x只能存儲類型定義中列出的值:'hello'或'world'。如果您嘗試保存任何其他值，ClickHouse將引發異常。自動選擇此Enum的8位大小。

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')

INSERT INTO t_enum FORMAT ValuesQuery id: b73e8f46-02e5-4196-832f-17f5353eecfeOk.
Exception on client:
Code: 691. DB::Exception: Unknown element 'a' for enum: while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('Enum8(\'hello\' = 1, \'world\' = 2)') :: 2, _CAST(_dummy_0, 'Enum8(\'hello\' = 1, \'world\' = 2)') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('Enum8(\'hello\' = 1, \'world\' = 2)'), _CAST(_dummy_0, 'Enum8(\'hello\' = 1, \'world\' = 2)')) Enum8('hello' = 1, 'world' = 2) : 1': While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (UNKNOWN_ELEMENT_OF_ENUM)

當從表中查詢數據時，ClickHouse從Enum中輸出字符串值。

SELECT * FROM t_enum

在這里插入圖片描述
如果需要看到行的對應數字，則必須將Enum值強制轉換為整數類型。

SELECT CAST(x, 'Int8') FROM t_enum

在這里插入圖片描述
要在查詢中創建Enum值，還需要使用CAST。

SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))

一般規則及用法

每個值被分配一個范圍在-128…127用于Enum8 或在-32768…32767用于Enum16。所有的字符串和數字必須是不同的。允許使用空字符串。如果指定了這種類型(在表定義中)，則數字可以按任意順序排列。然而，順序并不重要。

Enum中的字符串和數字值都不能為NULL。

Enum可以包含為Nullable類型。因此，如果使用查詢創建一個表

CREATE TABLE t_enum_nullable
(x Nullable( Enum8('hello' = 1, 'world' = 2) )
)
ENGINE = TinyLog

不僅可以存儲'hello'和'world'，還可以存儲NULL。

INSERT INTO t_enum_nullable Values('hello'),('world'),(NULL)

在RAM中，Enum列的存儲方式與對應數值的Int8或Int16相同。

當以文本形式讀取時，ClickHouse將值解析為字符串，并從Enum值集合中搜索相應的字符串。如果沒有找到，則拋出異常。當以文本格式讀取時，將讀取字符串并查找相應的數值。如果沒有找到，將拋出異常。當以文本形式寫入時，它將值寫入相應的字符串。如果列數據包含垃圾(不是來自有效集合的數字)，則拋出異常。當以二進制形式讀寫時，它的工作方式與Int8和Int16數據類型相同。隱式默認值是數字最小的值。

在ORDER BY、GROUP BY、IN、DISTINCT等操作期間，Enum 的行為與相應的數字相同。例如，ORDER BY按數字排序。相等和比較操作符在enum上的工作方式與在底層數值上的工作方式相同。

枚舉值不能與數字進行比較。枚舉可以與常量字符串進行比較。如果比較的字符串不是Enum的有效值，則會引發異常。IN操作符支持左邊的Enum和右邊的一組字符串。字符串是對應Enum的值。

大多數數值和字符串操作不是為Enum值定義的，例如向Enum中添加數字或將字符串連接到Enum中。然而，Enum有一個自然的toString函數來返回它的字符串值。

枚舉值也可以使用toT函數轉換為數字類型，其中T是數字類型。當T對應于枚舉的底層數字類型時，這種轉換是零代價的。如果只更改了一組值，則可以使用ALTER零代價更改Enum類型。可以使用ALTER添加和刪除Enum的成員(只有當被刪除的值從未在表中使用過時，刪除是安全的)。作為保護措施，更改先前定義的Enum成員的數值將引發異常。

使用ALTER，可以將Enum8更改為Enum16，反之亦然，就像將Int8更改為Int16一樣。

Nullable(T)

允許存儲特殊標記(NULL)，表示“缺失值”，與T允許的正常值一起。例如，Nullable(Int8)類型列可以存儲Int8類型的值，而沒有值的行將存儲NULL。

T不能是任何復合數據類型Array, Map和Tuple，但復合數據類型可以包含Nullable類型值，例如Array(Nullable(Int8))。

Nullable類型字段不能包含在表索引中。

NULL是任何Nullable類型的默認值，除非在ClickHouse服務器配置中另有指定。

存儲特點

為了在表列中存儲Nullable類型的值，ClickHouse除了使用帶值的普通文件外，還使用帶NULL掩碼的單獨文件。掩碼文件(masks file)中的條目允許ClickHouse區分每個表行對應數據類型的NULL和默認值。由于有一個額外的文件，Nullable列比類似的普通列消耗更多的存儲空間。

使用Nullable幾乎總是會對性能產生負面影響，在設計數據庫時請記住這一點。

Finding NULL

可以通過使用null子列來查找列中的NULL值，而無需讀取整個列。如果對應的值為NULL，則返回1，否則返回0。

例子：

CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple();INSERT INTO nullable VALUES (1) (NULL) (2) (NULL);SELECT n.null FROM nullable;

在這里插入圖片描述

Array(T)

T類型項的數組，起始數組索引為1。T可以是任何數據類型，包括數組。

Creating an Array

你可以使用一個函數來創建一個數組:

array(T)

也可以使用方括號。

[]

例子：

SELECT array(1, 2) AS x, toTypeName(x)

在這里插入圖片描述

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

在這里插入圖片描述

使用

在動態創建數組時，ClickHouse自動將參數類型定義為可以存儲所有列出的參數的最窄的數據類型。如果存在任何Nullable或文字NULL值，則數組元素的類型也變為Nullable。

如果ClickHouse不能確定數據類型，它會生成一個異常。例如，當嘗試同時創建字符串和數字的數組時(SELECT array(1， 'a'))就會發生這種情況。
在這里插入圖片描述

自動數據類型檢測的示例:

SELECT array(1, 2, NULL) AS x, toTypeName(x)

在這里插入圖片描述

數組大小

可以通過使用size0子列來查找數組的大小，而無需讀取整個列。對于多維數組，您可以使用sizeN -1，其中N是所需的維度。

例子：

CREATE TABLE t_arr (`arr` Array(Array(Array(UInt32)))) ENGINE = MergeTree ORDER BY tuple();INSERT INTO t_arr VALUES ([[[12, 13, 0, 1],[12]]]);SELECT arr.size0, arr.size1, arr.size2 FROM t_arr;

在這里插入圖片描述

UUID

UUID (Universally Unique Identifier)是一個16字節的值，用于標識記錄。有關uuid的詳細信息，請參見Wikipedia。

雖然存在不同的UUID變體(請參閱此處)，但ClickHouse并不驗證插入的UUID是否符合特定的變體。UUIDs 在內部被視為16個隨機字節的序列，在SQL級別具有8-4-4-4-12表示。

UUID值示例:

61f0c404-5cb3-11e7-907b-a6006ad3dba0

默認UUID為全零。例如，當插入一條新記錄但沒有指定UUID列的值時，使用它:

00000000-0000-0000-0000-000000000000

生成UUID

ClickHouse提供generateUUIDv4函數來生成隨機的UUID版本4值。

使用示例

Example 1：
這個示例演示了如何創建一個包含UUID列的表，以及如何向表中插入一個值。

CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLogINSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'SELECT * FROM t_uuid

在這里插入圖片描述
Example 2：
在本例中，插入記錄時沒有指定UUID列值，即插入默認的UUID值:

INSERT INTO t_uuid (y) VALUES ('Example 2')SELECT * FROM t_uuid

在這里插入圖片描述

限制

UUID數據類型只支持String數據類型也支持的函數(例如min、max和count)。

算術運算(例如abs)或聚合函數(例如sum和avg)不支持UUID數據類型。

Float32, Float64

如果您需要精確的計算，特別是如果您處理需要高精度的財務或業務數據，則應該考慮使用Decimal。float可能導致不準確的結果，如下所示:
CREATE TABLE IF NOT EXISTS float_vs_decimal
(
my_float Float64,
my_decimal Decimal64(3)
)Engine=MergeTree ORDER BY tuple()

INSERT INTO float_vs_decimal SELECT round(randCanonical(), 3) AS res, res FROM system.numbers LIMIT 1000000; # Generate 1 000 000 random number with 2 decimal places and store them as a float and as a decimal

SELECT sum(my_float), sum(my_decimal) FROM float_vs_decimal;
500279.56300000014 500279.563

SELECT sumKahan(my_float), sumKahan(my_decimal) FROM float_vs_decimal;
500279.563 500279.563

別名：

Float32 — FLOAT, REAL, SINGLE.
Float64 — DOUBLE, DOUBLE PRECISION.

在創建表時，可以設置浮點數的數值參數(例如FLOAT(12)， FLOAT(15,22)， DOUBLE(12)， DOUBLE(4,18))，但ClickHouse會忽略它們。

使用浮點數

使用浮點數進行計算可能會產生舍入誤差。

SELECT 1 - 0.9

在這里插入圖片描述

計算結果取決于計算方法(計算機系統的處理器類型和體系結構)。
浮點計算可能會產生諸如無窮大(Inf)和“非數字”(NaN)之類的數字。在處理計算結果時應考慮到這一點。
從文本解析浮點數時，結果可能不是最接近的機器可表示的數字。

`NaN` and `Inf`

與標準SQL相比，ClickHouse支持以下類型的浮點數:

Inf – Infinity.

SELECT 0.5 / 0

在這里插入圖片描述

-Inf — Negative infinity.

SELECT -0.5 / 0

在這里插入圖片描述

NaN — Not a number.

SELECT 0 / 0

在這里插入圖片描述

Bool

bool類型在內部存儲為UInt8。可能的值是true (1)， false(0)。

select true as col, toTypeName(col);

在這里插入圖片描述

select true == 1 as col, toTypeName(col);

在這里插入圖片描述

CREATE TABLE test_bool
(`A` Int64,`B` Bool
)
ENGINE = Memory;INSERT INTO test_bool VALUES (1, true),(2,0);

在這里插入圖片描述

JSON

這個特性是實驗性的，還不能用于生產。如果您需要使用JSON文檔，請考慮使用本指南。

在單列中存儲JavaScript對象表示法(JSON)文檔。

JSON is an alias for Object('json').

JSON數據類型是一個過時的特性。不要使用它。如果您想使用它，請設置allow_experimental_object_type = 1。

Example 1：

創建一個帶有JSON列的表，并將數據插入其中:

CREATE TABLE json
(o JSON
)
ENGINE = MemoryINSERT INTO json VALUES ('{"a": 1, "b": { "c": 2, "d": [1, 2, 3] }}')SELECT o.a, o.b.c, o.b.d[3] FROM json

在這里插入圖片描述
Example 2：
為了能夠創建有序的MergeTree族表，必須將排序鍵提取到其列中。例如，插入一個JSON格式的HTTP訪問日志壓縮文件:

CREATE TABLE logs
(timestamp DateTime,message JSON
)
ENGINE = MergeTree
ORDER BY timestampINSERT INTO logs
SELECT parseDateTimeBestEffort(JSONExtractString(json, 'timestamp')), json
FROM file('access.json.gz', JSONAsString)

顯示JSON列

在顯示JSON列時，ClickHouse默認只顯示字段值(因為在內部，它被表示為一個元組)。你也可以通過設置output_format_json_named_tuples_as_objects = 1來顯示字段名:

SET output_format_json_named_tuples_as_objects = 1SELECT * FROM json FORMAT JSONEachRow

在這里插入圖片描述

Map(key, value)

Map(key, value) 數據類型存儲 key:value 鍵值對.

參數

key — The key part of the pair. String, Integer, LowCardinality, FixedString, UUID, Date, DateTime, Date32, Enum.
value — The value part of the pair. Arbitrary type, including Map and Array.

要從a Map('key'， 'value')列中獲取值，請使用a['key']語法。這種查找現在以線性復雜度工作。

例子

考慮表:

CREATE TABLE table_map (a Map(String, UInt64)) ENGINE=Memory;
INSERT INTO table_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});

選擇所有的key2值:

SELECT a['key2'] FROM table_map;

在這里插入圖片描述
如果Map()列中沒有這樣的``，查詢將為數值返回零、空字符串或空數組。

insert into table_map values ({'key1':4, 'key2': 40})
insert into table_map values ({'key1':4, 'key3': 40})select a['key2'] from table_map

在這里插入圖片描述

將元組轉換為映射類型

你可以使用CAST函數將Tuple()轉換為Map():

SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS map;

在這里插入圖片描述

`Map.keys` 和`Map.values` 子列

為了優化Map列處理，在某些情況下可以使用keys 和values 子列，而不是讀取整個列。

Example：

CREATE TABLE t_map (`a` Map(String, UInt64)) ENGINE = Memory;INSERT INTO t_map VALUES (map('key1', 1, 'key2', 2, 'key3', 3));SELECT a.keys FROM t_map;SELECT a.values FROM t_map;

在這里插入圖片描述

LowCardinality

將其他數據類型的內部表示更改為字典編碼。

LowCardinality(data_type)

data_type - String、FixedString、Date、DateTime和數字(Decimal除外)。LowCardinality對于某些數據類型不是有效的，請參見 allow_suspicious_low_cardinality_types 設置說明。

LowCardinality是改變數據存儲方法和數據處理規則的上層結構。ClickHouse對LowCardinality-columns應用字典編碼。對許多應用程序來說，使用字典編碼的數據可以顯著提高SELECT查詢的性能。

使用LowCardinality數據類型的效率取決于數據多樣性。如果字典包含少于10,000個不同的值，那么ClickHouse通常顯示出更高的數據讀取和存儲效率。如果字典包含超過100,000個不同的值，那么與使用普通數據類型相比，ClickHouse的性能可能會更差。

在處理字符串時，考慮使用LowCardinality而不是Enum。LowCardinality在使用中提供了更大的靈活性，并且通常顯示相同或更高的效率。

例子

創建一個LowCardinality列的表:

CREATE TABLE lc_t
(`id` UInt16,`strings` LowCardinality(String)
)
ENGINE = MergeTree()
ORDER BY id