Clickhouse中支持哪些具体的数据类型？
- 内容介绍
- 文章标签
- 相关推荐
本文共计5701个文字，预计阅读时间需要23分钟。
当前版本 SELECT version() 为 22.3.3.44，整理日期 2022-04。最新信息请查看官网，新版本可能增加新的数据类型。基础类型 1.1，数值类型 1.1.1，整数类型。
1 基础类型 1.1 数值类型 1.1.1 整数 有符号数整形当前版本
SELECT version()为 22.3.3.44
整理日期 2022-04
最新信息请查看官网，新版本可能会添加新的数据类型
clickhouse.com/docs/en/sql-reference/data-types/
Int8, Int16, Int32, Int64, Int128, Int256
UInt8, UInt16, UInt32, UInt64, UInt128, UInt256
没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。
1.1.2 浮点数建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示.
浮点型进行计算时可能引起四舍五入的误差。
ac27f97f6a77 :) SELECT 1 - 0.9
SELECT 1 - 0.9
Query id: 7cc88fd3-1e0b-4ec1-aa23-57fd333ec1f7
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
1 rows in set. Elapsed: 0.035 sec.
ClickHouse直接使用Float32和Float64代表单精度浮点数以及双精度浮点数，Float32从小数点后第8位起及Float64从小数点后第17位起，会产生数据溢出
ac27f97f6a77 :) SELECT toFloat32('0.12345678901234567890') as a , toTypeName(a)
SELECT
toFloat32('0.12345678901234567890') AS a,
toTypeName(a)
Query id: c5d87476-acfb-4704-bb6f-c92483761808
┌──────────a─┬─toTypeName(toFloat32('0.12345678901234567890'))─┐
│ 0.12345679 │ Float32 │
└────────────┴─────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.032 sec.
ac27f97f6a77 :) SELECT toFloat64('0.12345678901234567890') as a , toTypeName(a)
SELECT
toFloat64('0.12345678901234567890') AS a,
toTypeName(a)
Query id: a3839d5e-7252-4118-b1f2-85de2f910cc2
┌───────────────────a─┬─toTypeName(toFloat64('0.12345678901234567890'))─┐
│ 0.12345678901234568 │ Float64 │
└─────────────────────┴─────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.026 sec.
ClickHouse的浮点数支持正无穷、负无穷以及非数字的表达方式.
-- 正无穷 inf
ac27f97f6a77 :) SELECT 0.1/0
SELECT 0.1 / 0
Query id: aa43028a-8778-49dc-8f73-7b2a7c8d37bd
┌─divide(0.1, 0)─┐
│ inf │
└────────────────┘
1 rows in set. Elapsed: 0.033 sec.
-- 负无穷 -inf
ac27f97f6a77 :) SELECT -0.1/0
SELECT -0.1 / 0
Query id: 84a1070b-09c7-4153-a743-c69a0dc67c19
┌─divide(-0.1, 0)─┐
│ -inf │
└─────────────────┘
1 rows in set. Elapsed: 0.032 sec.
-- 非数字 nan
ac27f97f6a77 :) SELECT 0/0
SELECT 0 / 0
Query id: 925823e7-f76c-4a73-af16-9ced6ec5769e
┌─divide(0, 0)─┐
│ nan │
└──────────────┘
1 rows in set. Elapsed: 0.032 sec.
1.1.3 定点数
定点数表示形式
有符号的浮点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃(不舍入)。
如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提供了Decimal32、Decimal64、Decimal128和Decimal256四种精度的定点数。
可以通过两种形式声明定点：
- 简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)、Decimal256(S)四种
- 原生方式为Decimal(P,S)，其中
- P代表精度，决定总位数（整数部分+小数部分），取值范围是[1:76]
- S代表规模，决定小数位数，取值范围是[0:P]。
例如：Decimal32(4) 可以表示数字（-99999.9999, 99999.9999) , 步长为0.0001
使用场景: 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用 Decimal进行存储
在使用定点数时还有一点值得注意：由于现代计算器系统只支持32位和64位CPU，所以Decimal128是在软件层面模拟实现的，它的速度会明显慢于Decimal32与Decimal64。
定点数运算及结果类型在使用两个不同精度的定点数进行四则运算的时:
- 会得到更宽的结果类型(无关顺序)
- Decimal64(S1)
Decimal32(S2) -> Decimal64(S) - Decimal128(S1)
Decimal32(S2) -> Decimal128(S) - Decimal128(S1)
Decimal64(S2) -> Decimal128(S)
- Decimal64(S1)
- 小数点位数S会发生变化
加法示例
Decemal32 op Decemal64 = Decemal64
S=max(S1, S2)=max(4, 2) = 4
-- 加法
840013eee323 :) SELECT toDecimal64(2,4) + toDecimal32(2,2) as a, toTypeName(a)
SELECT
toDecimal64(2, 4) + toDecimal32(2, 2) AS a,
toTypeName(a)
Query id: 31c174e5-e50f-4ac4-8642-e263854470c9
┌─a─┬─toTypeName(plus(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 4 │ Decimal(18, 4) │
└───┴────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.012 sec.
减法示例
Decemal32 op Decemal64 = Decemal64
S=max(S1, S2)=max(2, 4) = 4
-- 减法
840013eee323 :) SELECT toDecimal64(4,2) - toDecimal32(2,4) as a, toTypeName(a)
SELECT
toDecimal64(4, 2) - toDecimal32(2, 4) AS a,
toTypeName(a)
Query id: 7188e58d-ee0b-4893-a48e-1717f90cd14e
┌─a─┬─toTypeName(minus(toDecimal64(4, 2), toDecimal32(2, 4)))─┐
│ 2 │ Decimal(18, 4) │
└───┴─────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.031 sec.
乘法示例
Decemal32 op Decemal64 = Decemal64
S=S1+S2=4+2 = 6
840013eee323 :) SELECT toDecimal64(2,4) * toDecimal32(2,2) as a, toTypeName(a)
SELECT
toDecimal64(2, 4) * toDecimal32(2, 2) AS a,
toTypeName(a)
Query id: 0c99d332-233c-4d14-964e-c9ba7a012029
┌─a─┬─toTypeName(multiply(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 4 │ Decimal(18, 6) │
└───┴────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.015 sec.
除法示例
Decemal32 op Decemal64 = Decemal64
S=S1 = 4
840013eee323 :) SELECT toDecimal64(2,4) / toDecimal32(2,2) as a, toTypeName(a)
SELECT
toDecimal64(2, 4) / toDecimal32(2, 2) AS a,
toTypeName(a)
Query id: 48020990-1e82-4682-837b-50b81cb054f5
┌─a─┬─toTypeName(divide(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 1 │ Decimal(18, 4) │
└───┴──────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.013 sec.
溢出检查
对 Decimal 类型执行操作时，数值可能会发生溢出。分数中的过多数字被丢弃（不是舍入的）。整数中的过多数字将导致异常
-- 默认溢出检查是打开的
840013eee323 :) SELECT toDecimal32(2, 4) AS x, x / 3
SELECT
toDecimal32(2, 4) AS x,
x / 3
Query id: 44ec8d15-7db1-43bd-88c1-1d50a7afecf3
┌─x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2 │ 0.6666 │
└───┴──────────────────────────────┘
1 rows in set. Elapsed: 0.029 sec.
-- 检查溢出会导致计算变慢。如果已知溢出不可能，则可以通过设置decimal_check_overflow来禁用溢出检查，在这种情况下，溢出将导致结果不正确：
840013eee323 :) SET decimal_check_overflow = 0;
SET decimal_check_overflow = 0
Query id: 74b7b860-250f-42fe-9421-dba591f2a913
Ok.
0 rows in set. Elapsed: 0.019 sec.
840013eee323 :) SELECT toDecimal32(4.2, 8) AS x, 6 * x
SELECT
toDecimal32(4.2, 8) AS x,
6 * x
Query id: c195a43d-3ba7-4f19-b161-e965c18c51e1
┌───x─┬─multiply(6, toDecimal32(4.2, 8))─┐
│ 4.2 │ -17.74967296 │
└─────┴──────────────────────────────────┘
1 rows in set. Elapsed: 0.015 sec.
--溢出检查不仅发生在算术运算上，还发生在比较运算上：
SET decimal_check_overflow = 1; --恢复默认溢出检查
840013eee323 :) SELECT toDecimal32(1, 8) < 100
SELECT toDecimal32(1, 8) < 100
Query id: 0e3b112f-1603-4cc9-b264-c8be6d419e55
0 rows in set. Elapsed: 0.037 sec.
Received exception from server (version 22.3.3):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Can't compare decimal number due to overflow: While processing toDecimal32(1, 8) < 100. (DECIMAL_OVERFLOW)
1.2 字符串类型
1.2.1 String
String是不限长的，它可以包含任意的字节集，包含空字节。
String — LONGTEXT, MEDIUMTEXT, TINYTEXT, TEXT, LONGBLOB, MEDIUMBLOB, TINYBLOB, BLOB, VARCHAR, CHAR
固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的 字符串时候，将返回错误消息。
与 String 相比，极少会使用 FixedString，因为使用起来不是很方便。
选择数据时，ClickHouse 不会删除字符串末尾的空字节。如果使用该WHERE子句，则应手动添加空字节以匹配该FixedString值。以下示例说明了如何将WHERE子句与FixedString.
CREATE TABLE FixedStringTable
(
`s` FixedString(2)
)
ENGINE = Memory
1.2.3 UUID
UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充
-- 创建一个测试表
CREATE TABLE t_uuid (
x UUID,
y String
) ENGINE = Memory;
-- 插入一条数据，使用generateUUIDv4（）生成uuid
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
-- select查看帮我们生成了UUID
840013eee323 :) SELECT * FROM t_uuid
SELECT *
FROM t_uuid
Query id: 5435e8e0-592f-4c8d-b09f-a027dcc9a688
┌─x────────────────────────────────────┬─y─────────┐
│ f349e2b9-5caf-4e16-a192-1573b19b19c3 │ Example 1 │
└──────────────────────────────────────┴───────────┘
1 rows in set. Elapsed: 0.015 sec.
-- 再插入一条数据，UUID不输入值
INSERT INTO t_uuid (y) VALUES ('Example 2')
840013eee323 :) SELECT * FROM t_uuid
-- 再次查询发现，不插入UUID，会按UUID格式全部赋值为0
SELECT *
FROM t_uuid
Query id: c8751de4-0646-4707-9923-e219f8709853
┌─x────────────────────────────────────┬─y─────────┐
│ f349e2b9-5caf-4e16-a192-1573b19b19c3 │ Example 1 │
└──────────────────────────────────────┴───────────┘
┌─x────────────────────────────────────┬─y─────────┐
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘
2 rows in set. Elapsed: 0.012 sec.
1.3 时间类型
时间类型分为DateTime、DateTime64、Date、Date32四类。ClickHouse目前没有时间戳类型。时间类型最高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助UInt类型实现。
1.3.1 DateTime时间戳类型。用四个字节（无符号的）存储 Unix 时间戳。精确到秒。
值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]
Tips：
- 将 datetime 作为整数插入时，它被视为 Unix Timestamp (UTC)。1546300800代表'2019-01-01 00:00:00'UTC。但是，由于timestamp列已Asia/Istanbul指定 (UTC+3) 时区，当输出为字符串时，值将显示为'2019-01-01 03:00:00'
- 将字符串值作为日期时间插入时，它被视为在列时区中。'2019-01-01 00:00:00'将被视为处于Asia/Istanbul时区并保存为1546290000.
-- 支持的时区可通过SELECT * FROM system.time_zones查看，默认使用服务端时区，clickhouse-client可通过--use_client_time_zone使用
-- 这里指定时区Asia/Istanbul
CREATE TABLE dt_time
(
`timestamp` DateTime('Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt_time Values (1546300800, 1), ('2019-01-01 00:00:00', 2);
-- 查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt_time;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt_time
Query id: 94617fa9-ab85-4933-ac23-e89584059e3b
┌───────────timestamp─┬─event_id─┬─toTypeName(timestamp)─────┐
│ 2019-01-01 03:00:00 │ 1 │ DateTime('Asia/Istanbul') │
│ 2019-01-01 00:00:00 │ 2 │ DateTime('Asia/Istanbul') │
└─────────────────────┴──────────┴───────────────────────────┘
2 rows in set. Elapsed: 0.033 sec.
-- 日期过滤查询
SELECT * FROM dt_time WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')
SELECT * FROM dt_time WHERE timestamp = '2019-01-01 00:00:00'
-- 获取时区时间
SELECT toDateTime(now(), 'Asia/Istanbul') AS column, toTypeName(column) AS x
SELECT toDateTime(now()) AS column, toTypeName(column) AS x
-- 时区转化
SELECT
toDateTime(timestamp, 'Europe/London') as lon_time,
toDateTime(timestamp, 'Asia/Istanbul') as mos_time
FROM dt_time
1.3.2 DateTime64
DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置。
精度：10-precision，有效范围：[ 0 : 9 ]
常用precision - 3（毫秒）、6（微秒）、9（纳秒）
语法：DateTime64(precision, [timezone])
CREATE TABLE dt_time64
(
`timestamp` DateTime64(3, 'Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt_time64 Values (1546300800000, 1), ('2019-01-01 00:00:00', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt_time64;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt_time64
Query id: 58bb31be-7bb8-465b-bc9f-6afc18c72374
┌───────────────timestamp─┬─event_id─┬─toTypeName(timestamp)──────────┐
│ 2019-01-01 03:00:00.000 │ 1 │ DateTime64(3, 'Asia/Istanbul') │
│ 2019-01-01 00:00:00.000 │ 2 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴──────────┴────────────────────────────────┘
2 rows in set. Elapsed: 0.038 sec.
1.3.3 Date
日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。
取值范围: [1970-01-01, 2149-06-06]。
注意，其实写入一条超过时间范围的数值也是能写进去，并能查出来的，如：INSERT INTO dt VALUES ('2050-01-01', 3);
CREATE TABLE dt
(
`timestamp` Date,
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt VALUES (1546300800, 1), ('2019-01-01', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt
Query id: b42b081d-de80-4a85-b735-b6f1d3d91ef1
┌──timestamp─┬─event_id─┬─toTypeName(timestamp)─┐
│ 2019-01-01 │ 1 │ Date │
│ 2019-01-01 │ 2 │ Date │
└────────────┴──────────┴───────────────────────┘
2 rows in set. Elapsed: 0.029 sec.
1.3.4 Date32
日期类型，用四个字节存储，表示从从 1925-01-01 (无符号) 到当前的日期值。
取值范围同DateTime64的日期范围 [1925-01-01, 2283-11-11]
CREATE TABLE dt32
(
`timestamp` Date32,
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt32 VALUES (7258118400, 1), ('2200-01-01', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt32;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt32
Query id: 39b2b956-6c28-4036-a5c3-d8cd2fe00006
┌──timestamp─┬─event_id─┬─toTypeName(timestamp)─┐
│ 2106-02-07 │ 1 │ Date32 │
│ 2200-01-01 │ 2 │ Date32 │
└────────────┴──────────┴───────────────────────┘
2 rows in set. Elapsed: 0.028 sec.
2 复合类型
2.1 Array(T)
clickhouse起始数组索引为1，类型T可以是任何数据类型。
创建数组的两种方式：
- 通过函数创建：array(T)
- 使用方扩号：[]
-- 在动态创建数组时，ClickHouse 会自动将参数类型定义为可以存储所有列出的参数的最窄数据类型，即以最小存储代价为原则
-- array(T)
840013eee323 :) SELECT array(1, 2) AS x, toTypeName(x)
SELECT
[1, 2] AS x,
toTypeName(x)
Query id: aaaf4542-b5bd-4c54-b7e4-42a95b65baa7
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.041 sec.
-- 方扩号 []
-- 同一数组可以有不同类型，但是类型必须兼容，如果[1, 'a']就会报错
840013eee323 :) SELECT [1, 2.0] AS x, toTypeName(x)
SELECT
[1, 2.] AS x,
toTypeName(x)
Query id: fcb358f3-89de-4907-9a2d-2c581bdf98ab
┌─x─────┬─toTypeName([1, 2.])─┐
│ [1,2] │ Array(Float64) │
└───────┴─────────────────────┘
1 rows in set. Elapsed: 0.017 sec.
-- 如果有任何Nullable或文字NULL值，则数组元素的类型会统一变为Nullable
-- 如果 ClickHouse 无法确定数据类型，则会抛出异常
840013eee323 :) SELECT array(1, 2, NULL) AS x, toTypeName(x)
SELECT
[1, 2, NULL] AS x,
toTypeName(x)
Query id: 535c4f47-c756-4903-846f-efd019a90b4d
┌─x──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8)) │
└────────────┴──────────────────────────┘
1 rows in set. Elapsed: 0.024 sec.
-- 对于多维数组，您可以使用sizeN-1指定维度，获取该维度的大小，而不需要读取整列
CREATE TABLE t_arr (`arr` Array(Array(Array(UInt32)))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_arr VALUES ([[[12, 13, 0, 1],[12]]]);
840013eee323 :) SELECT arr.size0, arr.size1, arr.size2 FROM t_arr;
SELECT
arr.size0,
arr.size1,
arr.size2
FROM t_arr
Query id: ecb76832-6005-403a-8b9e-6ffb4ead225b
┌─arr.size0─┬─arr.size1─┬─arr.size2─┐
│ 1 │ [2] │ [[4,1]] │
└───────────┴───────────┴───────────┘
1 rows in set. Elapsed: 0.051 sec.
2.2 Tuple
元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然以最小存储代价为原则。
元组定义方式：
- 函数方式：tuple(T1, T2, ...)
- 圆扩号：（T1, T2, ...）
840013eee323 :) SELECT tuple(1,'a') AS x, toTypeName(x)
SELECT
(1, 'a') AS x,
toTypeName(x)
Query id: 1b470c56-d8d0-47dc-8ac5-c7cd853b41f1
┌─x───────┬─toTypeName((1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String) │
└─────────┴──────────────────────┘
1 rows in set. Elapsed: 0.039 sec.
-- 查询
CREATE TABLE named_tuples (`a` Tuple(s String, i Int64)) ENGINE = Memory;
INSERT INTO named_tuples VALUES (('y', 10)), (('x',-10));
840013eee323 :) SELECT a.s FROM named_tuples;
SELECT a.s
FROM named_tuples
Query id: b964f84a-b6de-47b2-a587-4ba790623d78
┌─a.s─┐
│ y │
│ x │
└─────┘
2 rows in set. Elapsed: 0.018 sec.
840013eee323 :) SELECT a.2 FROM named_tuples;
SELECT a.2
FROM named_tuples
Query id: a2f16f29-ea87-4bb5-9f24-d3e1bf845a5a
┌─tupleElement(a, 2)─┐
│ 10 │
│ -10 │
└────────────────────┘
2 rows in set. Elapsed: 0.035 sec.
2.3 Enum
ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。
ClickHouse支持两种枚举类型，它们除了取值范围不同之外，别无二致：
- 8 位Enum. 它最多可以包含在该[-128, 127]范围内枚举的 256 个值
- 16 位Enum。它最多可以包含在该[-32768, 32767]范围内枚举的 65536 个值
枚举固定使用'string' = integer键值对的形式定义数据。
可能有人会觉得，完全可以使用String代替枚举，为什么还需要专门的枚举类型呢？这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中（包括排序、分组、去重、过滤等），会使用Int类型的Value值。
CREATE TABLE t_enum
(
x Enum('hello' = 1, 'world' = 2)
)
ENGINE = Memory
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
840013eee323 :) INSERT INTO t_enum values('a')
-- 插入不存在的值会报错
840013eee323 :) INSERT INTO t_enum values('a')
INSERT INTO t_enum FORMAT Values
Query id: c23a0ac1-75c5-4e5b-a9f5-c359ab77f62d
Exception on client:
Code: 36. DB::Exception: Unknown element 'a' for enum: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (BAD_ARGUMENTS)
-- 查询元素得到的是字符串name
840013eee323 :) SELECT * FROM t_enum
SELECT *
FROM t_enum
Query id: c4dc6929-5b77-4571-9fd1-5e4f0f9c0f26
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.3 revision 54455.
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
3 rows in set. Elapsed: 0.042 sec.
-- 如果您需要查等效数字，则必须将Enum值转换为整数类型
840013eee323 :) SELECT CAST(x, 'Int8') FROM t_enum
SELECT CAST(x, 'Int8')
FROM t_enum
Query id: 865f450b-b725-48e2-860d-b4a17c165b9b
┌─CAST(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘
3 rows in set. Elapsed: 0.041 sec.
-- 在查询中创建Enum，需要CAST
840013eee323 :) SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))
SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))
Query id: 9b91895a-2200-436b-96fb-c314b3af40a1
┌─toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))─┐
│ Enum8('a' = 1, 'b' = 2) │
└─────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.029 sec.
2.4 Nested
嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。
CREATE TABLE nested_test
(
name String,
age UInt8,
dept Nested
(
id UInt8,
name String
)
) ENGINE = Memory
-- 注意：nested_test与dept并不是一对一的包含关系，这样写入会报错的
840013eee323 :) INSERT INTO nested_test VALUES ('nauu',18, 10000, '研发部');
INSERT INTO nested_test FORMAT Values
Query id: 7e766cc1-f146-4542-80c2-4e104e51b9bb
Exception on client:
Code: 53. DB::Exception: Type mismatch in IN or VALUES section. Expected: Array(UInt8). Got: UInt64: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (TYPE_MISMATCH)
-- 嵌套类型本质是一种多维数组的结构，嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐，但是行内数组字段的长度没有对齐
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心']);
840013eee323 :) INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心','测试部']);
INSERT INTO nested_test FORMAT Values
Query id: e9de8279-7b4d-4ff3-8082-f22f7b0bc135
1 rows in set. Elapsed: 0.021 sec.
Received exception from server (version 22.3.3):
Code: 190. DB::Exception: Received from localhost:9000. DB::Exception: Elements 'dept.id' and 'dept.name' of Nested data structure 'dept' (Array columns) have different array sizes.. (SIZES_OF_ARRAYS_DOESNT_MATCH)
-- 查询
840013eee323 :) SELECT name, dept.id, dept.name FROM nested_test
SELECT
name,
dept.id,
dept.name
FROM nested_test
Query id: b00fbc7a-bef1-4b8b-b3f6-a33bb3f76a06
┌─name──┬─dept.id────┬─dept.name──────────────────────────┐
│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部'] │
└───────┴────────────┴────────────────────────────────────┘
┌─name──┬─dept.id─┬─dept.name─────────────────┐
│ bruce │ [16,17] │ ['研发部','技术支持中心'] │
└───────┴─────────┴───────────────────────────┘
2 rows in set. Elapsed: 0.033 sec.
默认 flatten_nested = 1
-- flatten_nested = 1
SET flatten_nested = 1;
CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
840013eee323 :) SHOW CREATE TABLE t_nest;
SHOW CREATE TABLE t_nest
Query id: a9438b0c-65b0-471d-9ad0-67e0b628ae6e
┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
`n.a` Array(UInt32),
`n.b` Array(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.013 sec.
INSERT INTO t_nest VALUES ([1,2],[100,101]);
-- flatten_nested = 0
SET flatten_nested = 0;
CREATE TABLE t_nest2 (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
840013eee323 :) SHOW CREATE TABLE t_nest2;
SHOW CREATE TABLE t_nest2
Query id: 70b7cd13-ba42-40b5-933d-4035f433da1c
┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest2
(
`n` Nested(a UInt32, b UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.030 sec.
INSERT INTO t_nest2 VALUES ([(1, 100)]);
2.5 Map
Map(key, value)数据类型存储key:value对
- key— 键。类型可以为：String、Integer、LowCardinality或FixedString。
- value—值。类型可以为：String、Integer、Array、LowCardinality或FixedString
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});
-- 查询
840013eee323 :) SELECT a['key2'] FROM table_map;
SELECT a['key2']
FROM table_map
Query id: 781bacf5-b11e-4b4a-9924-28e5e007f80a
┌─arrayElement(a, 'key2')─┐
│ 10 │
│ 20 │
│ 30 │
└─────────────────────────┘
3 rows in set. Elapsed: 0.020 sec.
-- 如果列中没有此类key，则为数值类型返回0，空字符串或者空数组。
INSERT INTO table_map VALUES ({'key3':100}), ({});
840013eee323 :) SELECT a['key3'] FROM table_map;
SELECT a['key3']
FROM table_map
Query id: 605a8e55-182b-489d-b566-a14e0a0408cc
┌─arrayElement(a, 'key3')─┐
│ 0 │
│ 0 │
│ 0 │
└─────────────────────────┘
┌─arrayElement(a, 'key3')─┐
│ 100 │
│ 0 │
└─────────────────────────┘
5 rows in set. Elapsed: 0.032 sec.
3 特殊类型
3.1 Nullable
通过存储特殊值NULL来表示缺失值，NULL是任何Nullable类型的默认值，除非在 ClickHouse 服务器配置中另有指定。需要与数据类型一起搭配使用。
- Nullable(TypeName),这里TypeName只能是基础类型而不能是复合数据类型Array和Tuple，如：Nullable(Int8)类型列可以存储Int8类型值，而没有值的行将存储NULL
- 但复合数据类型可以包含Nullable类型值，例如Array(Nullable(Int8))
注意：使用Nullable几乎总是会对性能产生负面影响，在设计数据库时请记住这一点。
- 它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段
- 应该慎用Nullable类型，包括Nullable的数据表，不然会使查询和写入性能变慢。
- 因为在正常情况下，每个列字段的数据会被存储在对应的[Column].bin文件中。如果一个列字段被Nullable类型修饰后，会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时，需要一倍的额外文件操作
CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO nullable VALUES (1) (NULL) (2) (NULL);
-- 可以通过使用null子列而不读取整列来查找列中的NULL值。如果对应的值是NULL，则返回1，否则返回0
840013eee323 :) SELECT n.null FROM nullable;
SELECT n.`null`
FROM nullable
Query id: daea8aa1-82ed-41ae-90a9-8b89186970d5
┌─n.null─┐
│ 0 │
│ 1 │
│ 0 │
│ 1 │
└────────┘
4 rows in set. Elapsed: 0.031 sec.
-- 使用示例
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE Memory;
INSERT INTO t_null VALUES (1, NULL), (2, 3)
840013eee323 :) SELECT x + y FROM t_null;
SELECT x + y
FROM t_null
Query id: 041a6863-e49e-41c5-993c-9f53a304b37a
┌─plus(x, y)─┐
│ ᴺᵁᴸᴸ │
│ 5 │
└────────────┘
2 rows in set. Elapsed: 0.031 sec.
3.2 Domain
- IPv4是基于UInt32类型的域
- IPv6是基于FixedString(16)类型的域
为什么不用字符串类型代替Domain?
- 出于便捷的考量，Domain类型支持格式校验，人性化的输入输出格式
- 出于性能的考量，Pv4使用UInt32存储，IPv6类型是基于FixedString(16)，相比String更紧凑，占用空间少，查询性能快。
CREATE TABLE hits (url String, from4 IPv4, from6 IPv6) ENGINE = MergeTree() ORDER BY url;
840013eee323 :) DESCRIBE TABLE hits;
DESCRIBE TABLE hits
Query id: 4bb5b4df-31b0-4fe1-8723-b54dbfaf1d1a
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ url │ String │ │ │ │ │ │
│ from4 │ IPv4 │ │ │ │ │ │
│ from6 │ IPv6 │ │ │ │ │ │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
3 rows in set. Elapsed: 0.023 sec.
INSERT INTO hits (url, from4,from6) VALUES ('wikipedia.org', '116.253.40.133', '2a02:aa08:e000:3100::2')('clickhouse.com', '183.247.232.58', '2001:44c8:129:2632:33:0:252:2');
840013eee323 :) SELECT url, from4, toTypeName(from4), from6, toTypeName(from6) FROM hits;
SELECT
url,
from4,
toTypeName(from4),
from6,
toTypeName(from6)
FROM hits
Query id: e9c2c4d2-62af-4282-91d8-0071c3d54f22
┌─url────────────────────┬─from4──────────┬─toTypeName(from4)─┬─from6─────────────────────────┬─toTypeName(from6)─┐
│ clickhouse.com │ 183.247.232.58 │ IPv4 │ 2001:44c8:129:2632:33:0:252:2 │ IPv6 │
│ wikipedia.org │ 116.253.40.133 │ IPv4 │ 2a02:aa08:e000:3100::2 │ IPv6 │
└────────────────────────┴────────────────┴───────────────────┴───────────────────────────────┴───────────────────┘
2 rows in set. Elapsed: 0.048 sec.
Snow nothing, reap nothing.
本文共计5701个文字，预计阅读时间需要23分钟。
当前版本 SELECT version() 为 22.3.3.44，整理日期 2022-04。最新信息请查看官网，新版本可能增加新的数据类型。基础类型 1.1，数值类型 1.1.1，整数类型。
1 基础类型 1.1 数值类型 1.1.1 整数 有符号数整形当前版本
SELECT version()为 22.3.3.44
整理日期 2022-04
最新信息请查看官网，新版本可能会添加新的数据类型
clickhouse.com/docs/en/sql-reference/data-types/
Int8, Int16, Int32, Int64, Int128, Int256
UInt8, UInt16, UInt32, UInt64, UInt128, UInt256
没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。
1.1.2 浮点数建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示.
浮点型进行计算时可能引起四舍五入的误差。
ac27f97f6a77 :) SELECT 1 - 0.9
SELECT 1 - 0.9
Query id: 7cc88fd3-1e0b-4ec1-aa23-57fd333ec1f7
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
1 rows in set. Elapsed: 0.035 sec.
ClickHouse直接使用Float32和Float64代表单精度浮点数以及双精度浮点数，Float32从小数点后第8位起及Float64从小数点后第17位起，会产生数据溢出
ac27f97f6a77 :) SELECT toFloat32('0.12345678901234567890') as a , toTypeName(a)
SELECT
toFloat32('0.12345678901234567890') AS a,
toTypeName(a)
Query id: c5d87476-acfb-4704-bb6f-c92483761808
┌──────────a─┬─toTypeName(toFloat32('0.12345678901234567890'))─┐
│ 0.12345679 │ Float32 │
└────────────┴─────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.032 sec.
ac27f97f6a77 :) SELECT toFloat64('0.12345678901234567890') as a , toTypeName(a)
SELECT
toFloat64('0.12345678901234567890') AS a,
toTypeName(a)
Query id: a3839d5e-7252-4118-b1f2-85de2f910cc2
┌───────────────────a─┬─toTypeName(toFloat64('0.12345678901234567890'))─┐
│ 0.12345678901234568 │ Float64 │
└─────────────────────┴─────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.026 sec.
ClickHouse的浮点数支持正无穷、负无穷以及非数字的表达方式.
-- 正无穷 inf
ac27f97f6a77 :) SELECT 0.1/0
SELECT 0.1 / 0
Query id: aa43028a-8778-49dc-8f73-7b2a7c8d37bd
┌─divide(0.1, 0)─┐
│ inf │
└────────────────┘
1 rows in set. Elapsed: 0.033 sec.
-- 负无穷 -inf
ac27f97f6a77 :) SELECT -0.1/0
SELECT -0.1 / 0
Query id: 84a1070b-09c7-4153-a743-c69a0dc67c19
┌─divide(-0.1, 0)─┐
│ -inf │
└─────────────────┘
1 rows in set. Elapsed: 0.032 sec.
-- 非数字 nan
ac27f97f6a77 :) SELECT 0/0
SELECT 0 / 0
Query id: 925823e7-f76c-4a73-af16-9ced6ec5769e
┌─divide(0, 0)─┐
│ nan │
└──────────────┘
1 rows in set. Elapsed: 0.032 sec.
1.1.3 定点数
定点数表示形式
有符号的浮点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃(不舍入)。
如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提供了Decimal32、Decimal64、Decimal128和Decimal256四种精度的定点数。
可以通过两种形式声明定点：
- 简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)、Decimal256(S)四种
- 原生方式为Decimal(P,S)，其中
- P代表精度，决定总位数（整数部分+小数部分），取值范围是[1:76]
- S代表规模，决定小数位数，取值范围是[0:P]。
例如：Decimal32(4) 可以表示数字（-99999.9999, 99999.9999) , 步长为0.0001
使用场景: 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用 Decimal进行存储
在使用定点数时还有一点值得注意：由于现代计算器系统只支持32位和64位CPU，所以Decimal128是在软件层面模拟实现的，它的速度会明显慢于Decimal32与Decimal64。
定点数运算及结果类型在使用两个不同精度的定点数进行四则运算的时:
- 会得到更宽的结果类型(无关顺序)
- Decimal64(S1)
Decimal32(S2) -> Decimal64(S) - Decimal128(S1)
Decimal32(S2) -> Decimal128(S) - Decimal128(S1)
Decimal64(S2) -> Decimal128(S)
- Decimal64(S1)
- 小数点位数S会发生变化
加法示例
Decemal32 op Decemal64 = Decemal64
S=max(S1, S2)=max(4, 2) = 4
-- 加法
840013eee323 :) SELECT toDecimal64(2,4) + toDecimal32(2,2) as a, toTypeName(a)
SELECT
toDecimal64(2, 4) + toDecimal32(2, 2) AS a,
toTypeName(a)
Query id: 31c174e5-e50f-4ac4-8642-e263854470c9
┌─a─┬─toTypeName(plus(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 4 │ Decimal(18, 4) │
└───┴────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.012 sec.
减法示例
Decemal32 op Decemal64 = Decemal64
S=max(S1, S2)=max(2, 4) = 4
-- 减法
840013eee323 :) SELECT toDecimal64(4,2) - toDecimal32(2,4) as a, toTypeName(a)
SELECT
toDecimal64(4, 2) - toDecimal32(2, 4) AS a,
toTypeName(a)
Query id: 7188e58d-ee0b-4893-a48e-1717f90cd14e
┌─a─┬─toTypeName(minus(toDecimal64(4, 2), toDecimal32(2, 4)))─┐
│ 2 │ Decimal(18, 4) │
└───┴─────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.031 sec.
乘法示例
Decemal32 op Decemal64 = Decemal64
S=S1+S2=4+2 = 6
840013eee323 :) SELECT toDecimal64(2,4) * toDecimal32(2,2) as a, toTypeName(a)
SELECT
toDecimal64(2, 4) * toDecimal32(2, 2) AS a,
toTypeName(a)
Query id: 0c99d332-233c-4d14-964e-c9ba7a012029
┌─a─┬─toTypeName(multiply(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 4 │ Decimal(18, 6) │
└───┴────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.015 sec.
除法示例
Decemal32 op Decemal64 = Decemal64
S=S1 = 4
840013eee323 :) SELECT toDecimal64(2,4) / toDecimal32(2,2) as a, toTypeName(a)
SELECT
toDecimal64(2, 4) / toDecimal32(2, 2) AS a,
toTypeName(a)
Query id: 48020990-1e82-4682-837b-50b81cb054f5
┌─a─┬─toTypeName(divide(toDecimal64(2, 4), toDecimal32(2, 2)))─┐
│ 1 │ Decimal(18, 4) │
└───┴──────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.013 sec.
溢出检查
对 Decimal 类型执行操作时，数值可能会发生溢出。分数中的过多数字被丢弃（不是舍入的）。整数中的过多数字将导致异常
-- 默认溢出检查是打开的
840013eee323 :) SELECT toDecimal32(2, 4) AS x, x / 3
SELECT
toDecimal32(2, 4) AS x,
x / 3
Query id: 44ec8d15-7db1-43bd-88c1-1d50a7afecf3
┌─x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2 │ 0.6666 │
└───┴──────────────────────────────┘
1 rows in set. Elapsed: 0.029 sec.
-- 检查溢出会导致计算变慢。如果已知溢出不可能，则可以通过设置decimal_check_overflow来禁用溢出检查，在这种情况下，溢出将导致结果不正确：
840013eee323 :) SET decimal_check_overflow = 0;
SET decimal_check_overflow = 0
Query id: 74b7b860-250f-42fe-9421-dba591f2a913
Ok.
0 rows in set. Elapsed: 0.019 sec.
840013eee323 :) SELECT toDecimal32(4.2, 8) AS x, 6 * x
SELECT
toDecimal32(4.2, 8) AS x,
6 * x
Query id: c195a43d-3ba7-4f19-b161-e965c18c51e1
┌───x─┬─multiply(6, toDecimal32(4.2, 8))─┐
│ 4.2 │ -17.74967296 │
└─────┴──────────────────────────────────┘
1 rows in set. Elapsed: 0.015 sec.
--溢出检查不仅发生在算术运算上，还发生在比较运算上：
SET decimal_check_overflow = 1; --恢复默认溢出检查
840013eee323 :) SELECT toDecimal32(1, 8) < 100
SELECT toDecimal32(1, 8) < 100
Query id: 0e3b112f-1603-4cc9-b264-c8be6d419e55
0 rows in set. Elapsed: 0.037 sec.
Received exception from server (version 22.3.3):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Can't compare decimal number due to overflow: While processing toDecimal32(1, 8) < 100. (DECIMAL_OVERFLOW)
1.2 字符串类型
1.2.1 String
String是不限长的，它可以包含任意的字节集，包含空字节。
String — LONGTEXT, MEDIUMTEXT, TINYTEXT, TEXT, LONGBLOB, MEDIUMBLOB, TINYBLOB, BLOB, VARCHAR, CHAR
固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的 字符串时候，将返回错误消息。
与 String 相比，极少会使用 FixedString，因为使用起来不是很方便。
选择数据时，ClickHouse 不会删除字符串末尾的空字节。如果使用该WHERE子句，则应手动添加空字节以匹配该FixedString值。以下示例说明了如何将WHERE子句与FixedString.
CREATE TABLE FixedStringTable
(
`s` FixedString(2)
)
ENGINE = Memory
1.2.3 UUID
UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充
-- 创建一个测试表
CREATE TABLE t_uuid (
x UUID,
y String
) ENGINE = Memory;
-- 插入一条数据，使用generateUUIDv4（）生成uuid
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
-- select查看帮我们生成了UUID
840013eee323 :) SELECT * FROM t_uuid
SELECT *
FROM t_uuid
Query id: 5435e8e0-592f-4c8d-b09f-a027dcc9a688
┌─x────────────────────────────────────┬─y─────────┐
│ f349e2b9-5caf-4e16-a192-1573b19b19c3 │ Example 1 │
└──────────────────────────────────────┴───────────┘
1 rows in set. Elapsed: 0.015 sec.
-- 再插入一条数据，UUID不输入值
INSERT INTO t_uuid (y) VALUES ('Example 2')
840013eee323 :) SELECT * FROM t_uuid
-- 再次查询发现，不插入UUID，会按UUID格式全部赋值为0
SELECT *
FROM t_uuid
Query id: c8751de4-0646-4707-9923-e219f8709853
┌─x────────────────────────────────────┬─y─────────┐
│ f349e2b9-5caf-4e16-a192-1573b19b19c3 │ Example 1 │
└──────────────────────────────────────┴───────────┘
┌─x────────────────────────────────────┬─y─────────┐
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘
2 rows in set. Elapsed: 0.012 sec.
1.3 时间类型
时间类型分为DateTime、DateTime64、Date、Date32四类。ClickHouse目前没有时间戳类型。时间类型最高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助UInt类型实现。
1.3.1 DateTime时间戳类型。用四个字节（无符号的）存储 Unix 时间戳。精确到秒。
值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]
Tips：
- 将 datetime 作为整数插入时，它被视为 Unix Timestamp (UTC)。1546300800代表'2019-01-01 00:00:00'UTC。但是，由于timestamp列已Asia/Istanbul指定 (UTC+3) 时区，当输出为字符串时，值将显示为'2019-01-01 03:00:00'
- 将字符串值作为日期时间插入时，它被视为在列时区中。'2019-01-01 00:00:00'将被视为处于Asia/Istanbul时区并保存为1546290000.
-- 支持的时区可通过SELECT * FROM system.time_zones查看，默认使用服务端时区，clickhouse-client可通过--use_client_time_zone使用
-- 这里指定时区Asia/Istanbul
CREATE TABLE dt_time
(
`timestamp` DateTime('Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt_time Values (1546300800, 1), ('2019-01-01 00:00:00', 2);
-- 查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt_time;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt_time
Query id: 94617fa9-ab85-4933-ac23-e89584059e3b
┌───────────timestamp─┬─event_id─┬─toTypeName(timestamp)─────┐
│ 2019-01-01 03:00:00 │ 1 │ DateTime('Asia/Istanbul') │
│ 2019-01-01 00:00:00 │ 2 │ DateTime('Asia/Istanbul') │
└─────────────────────┴──────────┴───────────────────────────┘
2 rows in set. Elapsed: 0.033 sec.
-- 日期过滤查询
SELECT * FROM dt_time WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul')
SELECT * FROM dt_time WHERE timestamp = '2019-01-01 00:00:00'
-- 获取时区时间
SELECT toDateTime(now(), 'Asia/Istanbul') AS column, toTypeName(column) AS x
SELECT toDateTime(now()) AS column, toTypeName(column) AS x
-- 时区转化
SELECT
toDateTime(timestamp, 'Europe/London') as lon_time,
toDateTime(timestamp, 'Asia/Istanbul') as mos_time
FROM dt_time
1.3.2 DateTime64
DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置。
精度：10-precision，有效范围：[ 0 : 9 ]
常用precision - 3（毫秒）、6（微秒）、9（纳秒）
语法：DateTime64(precision, [timezone])
CREATE TABLE dt_time64
(
`timestamp` DateTime64(3, 'Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt_time64 Values (1546300800000, 1), ('2019-01-01 00:00:00', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt_time64;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt_time64
Query id: 58bb31be-7bb8-465b-bc9f-6afc18c72374
┌───────────────timestamp─┬─event_id─┬─toTypeName(timestamp)──────────┐
│ 2019-01-01 03:00:00.000 │ 1 │ DateTime64(3, 'Asia/Istanbul') │
│ 2019-01-01 00:00:00.000 │ 2 │ DateTime64(3, 'Asia/Istanbul') │
└─────────────────────────┴──────────┴────────────────────────────────┘
2 rows in set. Elapsed: 0.038 sec.
1.3.3 Date
日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。
取值范围: [1970-01-01, 2149-06-06]。
注意，其实写入一条超过时间范围的数值也是能写进去，并能查出来的，如：INSERT INTO dt VALUES ('2050-01-01', 3);
CREATE TABLE dt
(
`timestamp` Date,
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt VALUES (1546300800, 1), ('2019-01-01', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt
Query id: b42b081d-de80-4a85-b735-b6f1d3d91ef1
┌──timestamp─┬─event_id─┬─toTypeName(timestamp)─┐
│ 2019-01-01 │ 1 │ Date │
│ 2019-01-01 │ 2 │ Date │
└────────────┴──────────┴───────────────────────┘
2 rows in set. Elapsed: 0.029 sec.
1.3.4 Date32
日期类型，用四个字节存储，表示从从 1925-01-01 (无符号) 到当前的日期值。
取值范围同DateTime64的日期范围 [1925-01-01, 2283-11-11]
CREATE TABLE dt32
(
`timestamp` Date32,
`event_id` UInt8
)
ENGINE = Memory;
INSERT INTO dt32 VALUES (7258118400, 1), ('2200-01-01', 2);
--查看
840013eee323 :) SELECT timestamp, event_id, toTypeName(timestamp) FROM dt32;
SELECT
timestamp,
event_id,
toTypeName(timestamp)
FROM dt32
Query id: 39b2b956-6c28-4036-a5c3-d8cd2fe00006
┌──timestamp─┬─event_id─┬─toTypeName(timestamp)─┐
│ 2106-02-07 │ 1 │ Date32 │
│ 2200-01-01 │ 2 │ Date32 │
└────────────┴──────────┴───────────────────────┘
2 rows in set. Elapsed: 0.028 sec.
2 复合类型
2.1 Array(T)
clickhouse起始数组索引为1，类型T可以是任何数据类型。
创建数组的两种方式：
- 通过函数创建：array(T)
- 使用方扩号：[]
-- 在动态创建数组时，ClickHouse 会自动将参数类型定义为可以存储所有列出的参数的最窄数据类型，即以最小存储代价为原则
-- array(T)
840013eee323 :) SELECT array(1, 2) AS x, toTypeName(x)
SELECT
[1, 2] AS x,
toTypeName(x)
Query id: aaaf4542-b5bd-4c54-b7e4-42a95b65baa7
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.041 sec.
-- 方扩号 []
-- 同一数组可以有不同类型，但是类型必须兼容，如果[1, 'a']就会报错
840013eee323 :) SELECT [1, 2.0] AS x, toTypeName(x)
SELECT
[1, 2.] AS x,
toTypeName(x)
Query id: fcb358f3-89de-4907-9a2d-2c581bdf98ab
┌─x─────┬─toTypeName([1, 2.])─┐
│ [1,2] │ Array(Float64) │
└───────┴─────────────────────┘
1 rows in set. Elapsed: 0.017 sec.
-- 如果有任何Nullable或文字NULL值，则数组元素的类型会统一变为Nullable
-- 如果 ClickHouse 无法确定数据类型，则会抛出异常
840013eee323 :) SELECT array(1, 2, NULL) AS x, toTypeName(x)
SELECT
[1, 2, NULL] AS x,
toTypeName(x)
Query id: 535c4f47-c756-4903-846f-efd019a90b4d
┌─x──────────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8)) │
└────────────┴──────────────────────────┘
1 rows in set. Elapsed: 0.024 sec.
-- 对于多维数组，您可以使用sizeN-1指定维度，获取该维度的大小，而不需要读取整列
CREATE TABLE t_arr (`arr` Array(Array(Array(UInt32)))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_arr VALUES ([[[12, 13, 0, 1],[12]]]);
840013eee323 :) SELECT arr.size0, arr.size1, arr.size2 FROM t_arr;
SELECT
arr.size0,
arr.size1,
arr.size2
FROM t_arr
Query id: ecb76832-6005-403a-8b9e-6ffb4ead225b
┌─arr.size0─┬─arr.size1─┬─arr.size2─┐
│ 1 │ [2] │ [[4,1]] │
└───────────┴───────────┴───────────┘
1 rows in set. Elapsed: 0.051 sec.
2.2 Tuple
元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然以最小存储代价为原则。
元组定义方式：
- 函数方式：tuple(T1, T2, ...)
- 圆扩号：（T1, T2, ...）
840013eee323 :) SELECT tuple(1,'a') AS x, toTypeName(x)
SELECT
(1, 'a') AS x,
toTypeName(x)
Query id: 1b470c56-d8d0-47dc-8ac5-c7cd853b41f1
┌─x───────┬─toTypeName((1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String) │
└─────────┴──────────────────────┘
1 rows in set. Elapsed: 0.039 sec.
-- 查询
CREATE TABLE named_tuples (`a` Tuple(s String, i Int64)) ENGINE = Memory;
INSERT INTO named_tuples VALUES (('y', 10)), (('x',-10));
840013eee323 :) SELECT a.s FROM named_tuples;
SELECT a.s
FROM named_tuples
Query id: b964f84a-b6de-47b2-a587-4ba790623d78
┌─a.s─┐
│ y │
│ x │
└─────┘
2 rows in set. Elapsed: 0.018 sec.
840013eee323 :) SELECT a.2 FROM named_tuples;
SELECT a.2
FROM named_tuples
Query id: a2f16f29-ea87-4bb5-9f24-d3e1bf845a5a
┌─tupleElement(a, 2)─┐
│ 10 │
│ -10 │
└────────────────────┘
2 rows in set. Elapsed: 0.035 sec.
2.3 Enum
ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。
ClickHouse支持两种枚举类型，它们除了取值范围不同之外，别无二致：
- 8 位Enum. 它最多可以包含在该[-128, 127]范围内枚举的 256 个值
- 16 位Enum。它最多可以包含在该[-32768, 32767]范围内枚举的 65536 个值
枚举固定使用'string' = integer键值对的形式定义数据。
可能有人会觉得，完全可以使用String代替枚举，为什么还需要专门的枚举类型呢？这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中（包括排序、分组、去重、过滤等），会使用Int类型的Value值。
CREATE TABLE t_enum
(
x Enum('hello' = 1, 'world' = 2)
)
ENGINE = Memory
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
840013eee323 :) INSERT INTO t_enum values('a')
-- 插入不存在的值会报错
840013eee323 :) INSERT INTO t_enum values('a')
INSERT INTO t_enum FORMAT Values
Query id: c23a0ac1-75c5-4e5b-a9f5-c359ab77f62d
Exception on client:
Code: 36. DB::Exception: Unknown element 'a' for enum: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (BAD_ARGUMENTS)
-- 查询元素得到的是字符串name
840013eee323 :) SELECT * FROM t_enum
SELECT *
FROM t_enum
Query id: c4dc6929-5b77-4571-9fd1-5e4f0f9c0f26
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.3 revision 54455.
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
3 rows in set. Elapsed: 0.042 sec.
-- 如果您需要查等效数字，则必须将Enum值转换为整数类型
840013eee323 :) SELECT CAST(x, 'Int8') FROM t_enum
SELECT CAST(x, 'Int8')
FROM t_enum
Query id: 865f450b-b725-48e2-860d-b4a17c165b9b
┌─CAST(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘
3 rows in set. Elapsed: 0.041 sec.
-- 在查询中创建Enum，需要CAST
840013eee323 :) SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))
SELECT toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))
Query id: 9b91895a-2200-436b-96fb-c314b3af40a1
┌─toTypeName(CAST('a', 'Enum(\'a\' = 1, \'b\' = 2)'))─┐
│ Enum8('a' = 1, 'b' = 2) │
└─────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.029 sec.
2.4 Nested
嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。
CREATE TABLE nested_test
(
name String,
age UInt8,
dept Nested
(
id UInt8,
name String
)
) ENGINE = Memory
-- 注意：nested_test与dept并不是一对一的包含关系，这样写入会报错的
840013eee323 :) INSERT INTO nested_test VALUES ('nauu',18, 10000, '研发部');
INSERT INTO nested_test FORMAT Values
Query id: 7e766cc1-f146-4542-80c2-4e104e51b9bb
Exception on client:
Code: 53. DB::Exception: Type mismatch in IN or VALUES section. Expected: Array(UInt8). Got: UInt64: While executing ValuesBlockInputFormat: data for INSERT was parsed from query. (TYPE_MISMATCH)
-- 嵌套类型本质是一种多维数组的结构，嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐，但是行内数组字段的长度没有对齐
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心']);
840013eee323 :) INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心','测试部']);
INSERT INTO nested_test FORMAT Values
Query id: e9de8279-7b4d-4ff3-8082-f22f7b0bc135
1 rows in set. Elapsed: 0.021 sec.
Received exception from server (version 22.3.3):
Code: 190. DB::Exception: Received from localhost:9000. DB::Exception: Elements 'dept.id' and 'dept.name' of Nested data structure 'dept' (Array columns) have different array sizes.. (SIZES_OF_ARRAYS_DOESNT_MATCH)
-- 查询
840013eee323 :) SELECT name, dept.id, dept.name FROM nested_test
SELECT
name,
dept.id,
dept.name
FROM nested_test
Query id: b00fbc7a-bef1-4b8b-b3f6-a33bb3f76a06
┌─name──┬─dept.id────┬─dept.name──────────────────────────┐
│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部'] │
└───────┴────────────┴────────────────────────────────────┘
┌─name──┬─dept.id─┬─dept.name─────────────────┐
│ bruce │ [16,17] │ ['研发部','技术支持中心'] │
└───────┴─────────┴───────────────────────────┘
2 rows in set. Elapsed: 0.033 sec.
默认 flatten_nested = 1
-- flatten_nested = 1
SET flatten_nested = 1;
CREATE TABLE t_nest (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
840013eee323 :) SHOW CREATE TABLE t_nest;
SHOW CREATE TABLE t_nest
Query id: a9438b0c-65b0-471d-9ad0-67e0b628ae6e
┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest
(
`n.a` Array(UInt32),
`n.b` Array(UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.013 sec.
INSERT INTO t_nest VALUES ([1,2],[100,101]);
-- flatten_nested = 0
SET flatten_nested = 0;
CREATE TABLE t_nest2 (`n` Nested(a UInt32, b UInt32)) ENGINE = MergeTree ORDER BY tuple();
840013eee323 :) SHOW CREATE TABLE t_nest2;
SHOW CREATE TABLE t_nest2
Query id: 70b7cd13-ba42-40b5-933d-4035f433da1c
┌─statement───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.t_nest2
(
`n` Nested(a UInt32, b UInt32)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set. Elapsed: 0.030 sec.
INSERT INTO t_nest2 VALUES ([(1, 100)]);
2.5 Map
Map(key, value)数据类型存储key:value对
- key— 键。类型可以为：String、Integer、LowCardinality或FixedString。
- value—值。类型可以为：String、Integer、Array、LowCardinality或FixedString
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});
-- 查询
840013eee323 :) SELECT a['key2'] FROM table_map;
SELECT a['key2']
FROM table_map
Query id: 781bacf5-b11e-4b4a-9924-28e5e007f80a
┌─arrayElement(a, 'key2')─┐
│ 10 │
│ 20 │
│ 30 │
└─────────────────────────┘
3 rows in set. Elapsed: 0.020 sec.
-- 如果列中没有此类key，则为数值类型返回0，空字符串或者空数组。
INSERT INTO table_map VALUES ({'key3':100}), ({});
840013eee323 :) SELECT a['key3'] FROM table_map;
SELECT a['key3']
FROM table_map
Query id: 605a8e55-182b-489d-b566-a14e0a0408cc
┌─arrayElement(a, 'key3')─┐
│ 0 │
│ 0 │
│ 0 │
└─────────────────────────┘
┌─arrayElement(a, 'key3')─┐
│ 100 │
│ 0 │
└─────────────────────────┘
5 rows in set. Elapsed: 0.032 sec.
3 特殊类型
3.1 Nullable
通过存储特殊值NULL来表示缺失值，NULL是任何Nullable类型的默认值，除非在 ClickHouse 服务器配置中另有指定。需要与数据类型一起搭配使用。
- Nullable(TypeName),这里TypeName只能是基础类型而不能是复合数据类型Array和Tuple，如：Nullable(Int8)类型列可以存储Int8类型值，而没有值的行将存储NULL
- 但复合数据类型可以包含Nullable类型值，例如Array(Nullable(Int8))
注意：使用Nullable几乎总是会对性能产生负面影响，在设计数据库时请记住这一点。
- 它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段
- 应该慎用Nullable类型，包括Nullable的数据表，不然会使查询和写入性能变慢。
- 因为在正常情况下，每个列字段的数据会被存储在对应的[Column].bin文件中。如果一个列字段被Nullable类型修饰后，会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时，需要一倍的额外文件操作
CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO nullable VALUES (1) (NULL) (2) (NULL);
-- 可以通过使用null子列而不读取整列来查找列中的NULL值。如果对应的值是NULL，则返回1，否则返回0
840013eee323 :) SELECT n.null FROM nullable;
SELECT n.`null`
FROM nullable
Query id: daea8aa1-82ed-41ae-90a9-8b89186970d5
┌─n.null─┐
│ 0 │
│ 1 │
│ 0 │
│ 1 │
└────────┘
4 rows in set. Elapsed: 0.031 sec.
-- 使用示例
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE Memory;
INSERT INTO t_null VALUES (1, NULL), (2, 3)
840013eee323 :) SELECT x + y FROM t_null;
SELECT x + y
FROM t_null
Query id: 041a6863-e49e-41c5-993c-9f53a304b37a
┌─plus(x, y)─┐
│ ᴺᵁᴸᴸ │
│ 5 │
└────────────┘
2 rows in set. Elapsed: 0.031 sec.
3.2 Domain
- IPv4是基于UInt32类型的域
- IPv6是基于FixedString(16)类型的域
为什么不用字符串类型代替Domain?
- 出于便捷的考量，Domain类型支持格式校验，人性化的输入输出格式
- 出于性能的考量，Pv4使用UInt32存储，IPv6类型是基于FixedString(16)，相比String更紧凑，占用空间少，查询性能快。
CREATE TABLE hits (url String, from4 IPv4, from6 IPv6) ENGINE = MergeTree() ORDER BY url;
840013eee323 :) DESCRIBE TABLE hits;
DESCRIBE TABLE hits
Query id: 4bb5b4df-31b0-4fe1-8723-b54dbfaf1d1a
┌─name──┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ url │ String │ │ │ │ │ │
│ from4 │ IPv4 │ │ │ │ │ │
│ from6 │ IPv6 │ │ │ │ │ │
└───────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
3 rows in set. Elapsed: 0.023 sec.
INSERT INTO hits (url, from4,from6) VALUES ('wikipedia.org', '116.253.40.133', '2a02:aa08:e000:3100::2')('clickhouse.com', '183.247.232.58', '2001:44c8:129:2632:33:0:252:2');
840013eee323 :) SELECT url, from4, toTypeName(from4), from6, toTypeName(from6) FROM hits;
SELECT
url,
from4,
toTypeName(from4),
from6,
toTypeName(from6)
FROM hits
Query id: e9c2c4d2-62af-4282-91d8-0071c3d54f22
┌─url────────────────────┬─from4──────────┬─toTypeName(from4)─┬─from6─────────────────────────┬─toTypeName(from6)─┐
│ clickhouse.com │ 183.247.232.58 │ IPv4 │ 2001:44c8:129:2632:33:0:252:2 │ IPv6 │
│ wikipedia.org │ 116.253.40.133 │ IPv4 │ 2a02:aa08:e000:3100::2 │ IPv6 │
└────────────────────────┴────────────────┴───────────────────┴───────────────────────────────┴───────────────────┘
2 rows in set. Elapsed: 0.048 sec.
Snow nothing, reap nothing.