大数据ClickHouse（四）：数据类型详细介绍

举报
Lansonli 发表于 2022/08/11 09:03:12 2022/08/11
【摘要】 ​数据类型详细介绍ClickHouse提供了许多数据类型，它们可以划分为基础类型、复合类型和特殊类型。我们可以在system.data_type_families表中检查数据类型名称以及是否区分大小写。这个表中存储了ClickHouse支持的所有数据类型。select * from system.data_type_families limit 10;SELECT *FROM system....

​数据类型详细介绍

ClickHouse提供了许多数据类型，它们可以划分为基础类型、复合类型和特殊类型。我们可以在system.data_type_families表中检查数据类型名称以及是否区分大小写。这个表中存储了ClickHouse支持的所有数据类型。


select * from system.data_type_families limit 10;

SELECT *

FROM system.data_type_families

LIMIT 10



┌─name────────────┬─case_insensitive─┬─alias_to─┐

│ Polygon         │                    0 │            │

│ Ring            │                    0 │            │

│ MultiPolygon    │                    0 │            │

│ IPv6            │                    0 │            │

│ IntervalSecond  │                    0 │            │

│ IPv4            │                    0 │            │

│ UInt32          │                    0 │            │

│ IntervalYear    │                    0 │            │

│ IntervalQuarter │                    0 │            │

│ IntervalMonth    │                   0 │            │

└─────────────────┴──────────────────┴──────────┘



10 rows in set. Elapsed: 0.004 sec.

下面介绍下常用的数据类型，ClickHouse与Mysql、Hive中常用数据类型的对比图如下：

MySQL

Hive

ClickHouse(区分大小写)

byte

TINYINT

Int8

short

SMALLINT

Int16

int

INT

Int32

long

BIGINT

Int64

varchar

STRING

String

timestamp

TIMESTAMP

DateTime

float

FLOAT

Float32

double

DOUBLE

Float64

boolean

BOOLEAN

无


一、​​​​​​​Int

ClickHouse中整形分为Int8、Int16、Int32、Int64来表示整数不同的取值范围，其末尾数字正好代表占用字节的大小（8位=1字节），整形又包含有符号整形和无符号整形，他们写法上的区别为无符号整形前面加“U”表示。

  • 有符号整型范围

类型

字节

范围

Int8

1

[-128:127]

Int16

2

[-32768:32767]

Int32

4

[-2147483648:2147483647]

Int64

8

[-9223372036854775808:9223372036854775807]


  • 无符号整形范围

类型

字节

范围

UInt8

1

[0:255]

UInt16

2

[0:65535]

UInt32

4

[0:4294967295]

UInt64

8

[0:18446744073709551615]



二、​​​​​​​​​​​​​​Float

我们建议使用整数方式来存储数据，因为浮点类型数据计算可能导致四舍五入的误差。浮点类型包含单精度浮点数和双精度浮点数。

  • 单精度浮点数

类型

字节

有效精度位数

Float32

4

7

Float32从小数点后第8位起会发生数据溢出。


  • 双精度浮点数

类型

字节

有效精度位数

Float64

8

16

Float64从小数点后第17位起会发生数据溢出。

  • 示例
    • toFloat32(...) 用来将字符串转换成Float32类型的函数
    • toFloat64(...) 用来将字符串转换成Float64类型的函数


#浮点数有可能导致数据误差
node1 :) select 1-0.9

SELECT 1 - 0.9
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
1 rows in set. Elapsed: 0.021 sec.

#Float32类型，从第8位开始产生溢出，会四舍五入。
node1 :) select toFloat32(0.123456789);

SELECT toFloat32(0.123456789)

┌─toFloat32(0.123456789)─┐
│             0.12345679 │
└────────────────────────┘

1 rows in set. Elapsed: 0.004 sec. 

# Float64类型，从第17为开始产生数据溢出，会四舍五入
node1 :) select toFloat64(0.12345678901234567890);

SELECT toFloat64(0.12345678901234568)

┌─toFloat64(0.12345678901234568)─┐
│            0.12345678901234568 │
└────────────────────────────────┘

1 rows in set. Elapsed: 0.006 sec.


三、​​​​​​​​​​​​​​Decimal

有符号的定点数，可在加、减和乘法运算过程中保持精度。ClickHouse提供了Decimal32、Decimal64、Decimal128、Decimal256几种精度的定点数，支持几种写法：

  • Decimal(P,S)
  • Decimal32(S),数据范围：(-1*10^(9-S),1*10^(9-S))
  • Decimal64(S),数据范围：(-1*10^(18-S),1*10^(18-S))
  • Decimal128(S),数据范围：(-1*10^(38-S),1*10^(38-S))
  • Decimal256(S),数据范围：(-1*10^(76-S),1*10^(76-S))

其中，P代表精度，决定总位数（整数部分+小数部分），取值范围是1~76。S代表规模，决定小数位数，取值范围是0~P。

根据P值的范围可以有如下对等写法，这里以小数点后2位举例：

P取值

原生写法示例

等同于

[1:9]

Decimal(9,2)

Decimal32(2)

[10:18]

Decimal(18,2)

Decimal64(2)

[19:38]

Decimal(38,2)

Decimal128(2)

[39:76]

Decimal(76,2)

Decimal256(2)

另外，Decimal数据在进行四则运算时，精度（总位数）和规模（小数点位数）会发生变化，具体规则如下：

精度（总位数）对应规则：

  • Decimal64(S1) 运算符 Decimal32(S2) -> Decimal64(S)
  • Decimal128(S1) 运算符 Decimal32(S2) -> Decimal128(S）
  • Decimal128(S1) 运算符 Decimal64(S2) -> Decimal128(S)
  • Decimal256(S1) 运算符Decimal<32|64|128>(S2) -> Decimal256(S)

两个不同精度的数据进行四则运算时，结果数据的精度以最大精度为准。


规模（小数点位数）对应规则：

  • 加法|减法：S=max(S1,S2)，即以两个数据中小数点位数最多的为准。
  • 乘法：S=S1+S2(注意：S1精度>=S2精度)，即以两个数据的小数位相加为准。
  • 除法：规模以被除数的小数位为准。两数相除，被除数的小数位数不能小于除数的小数位数，也就是触发的规模可以理解为与两个数据中小数点位数大的为准。举例：a/b ，a是被除数，与a的规模保持一致。

示例：

  • toDecimal32(value,S):将字符串value转换为Decimal32类型,小数点后有S位。
  • toTypeName(字段)：获取字段的数据类型函数。
#测试加法,S取两者最大的，P取两者最大的
node1 :) select
	toDecimal64(2,3) as x,
	toTypeName(x) as xtype,
	toDecimal32(2,2) as y,
	toTypeName(y) as ytype,
	x+y as z,
	toTypeName(z) as ztype;

结果如下：


#测试减法，S取两者最大的，P取两者最大的。
node1 :) select
	toDecimal64(2,3) as x,
	toTypeName(x) as xtype,
	toDecimal32(2,2) as y,
	toTypeName(y) as ytype,
	x-y as z,
	toTypeName(z) as ztype;

 结果如下：


#测试乘法，S取两者最大的，P取两者小数位之和。
node1 :) select
	toDecimal64(2,3) as x,
	toTypeName(x) as xtype,
	toDecimal32(2,2) as y,
	toTypeName(y) as ytype,
	x*y as z,
	toTypeName(z) as ztype;

 结果如下：


#测试除法，S取两者最大的，P取被除数的小数位数。
node1 :) select
	toDecimal64(2,3) as x,
	toTypeName(x) as xtype,
	toDecimal32(2,2) as y,
	toTypeName(y) as ytype,
	x/y as z,
	toTypeName(z) as ztype;


node1 :) select 1-toDecimal64(0.9,1);
SELECT 1 - toDecimal64(0.9, 1)

┌─minus(1, toDecimal64(0.9, 1))─┐
│                           0.1 │
└───────────────────────────────┘


四、​​​​​​​​​​​​​​String

字符串可以是任意长度的。它可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他 DBMSs 中的VARCHAR、BLOB、CLOB 等类型。

这个类型比较简单，这里就不举例了


五、​​​​​​​FixedString

固定长度N的字符串（N必须是严格的正自然数）,一般在明确字符串长度的场景下使用，可以使用下面的语法对列声明为FixedString类型：

# N表示字符串的长度。
<column_name>  FixedString(N)

当向ClickHouse中插入数据时,如果字符串包含的字节数少于 N ,将对字符串末尾进行空字节填充。如果字符串包含的字节数大于N,将抛出Too large value for FixedString(N)异常。

当做数据查询时，ClickHouse不会删除字符串末尾的空字节。 如果使用WHERE子句，则须要手动添加空字节以匹配FixedString的值，新版本后期不需要手动添加。

  • 示例：
    • toFixedString(value,N)：将字符串转换为N位长度，N不能小于value字符串实际长度。
#查看字符号串长度
node1 :) select toFixedString('hello',6) as a,length(a) as alength;
SELECT 
    toFixedString('hello', 6) AS a,
    length(a) AS alength

┌─a─────┬─alength─┐
│ hello │       6 │
└───────┴─────────┘

node1 :) select toFixedString('hello world',6) as a,length(a) as alength;

SELECT 
    toFixedString('hello world', 6) AS a,
length(a) AS alength

Received exception from server (version 20.8.3):
Code: 131. DB::Exception: Received from localhost:9000. DB::Exception: String too long for type FixedString(6).


六、UUID

UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12，如果在插入新记录时未指定UUID列值，则UUID值将用0来填充（00000000-0000-0000-0000-000000000000）。

UUID类型不支持算术运算、聚合函数sum和avg。

  • 示例：
    • generateUUIDv4()随机生成一个32位的UUID。
# 使用mydb库
node1 :) use mydb;

#创建表t_uuid,指定x列为UUID类型，表引擎为TinyLog
node1 :) CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog

#向表 t_uuid中插入一条数据
node1 :) INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1';

#向表t_uuid中插入一条数据，这里不指定UUID的值，默认会生成0来填充
node1 :) INSERT INTO t_uuid (y) VALUES ('Example 2')

#查询结果
node1 :) select * from t_uuid;
SELECT *
FROM t_uuid
┌────────────────────────────────────x─┬─y─────────┐
│ 9c9f82dc-48a0-4749-b46a-cf6a1159c1fe │ Example 1 │
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘

2 rows in set. Elapsed: 0.003 sec.


七、Date

Date只能精确到天，用两个字节存储，表示从1970-01-01（无符号）到当前的日期值。日期中没有存储时区信息，不能指定时区。

  • 示例：
    • now() : 获取当前天日期，返回格式：yyyy-MM-dd HH:mm:ss
    • toDate(value) : 将字符串转成Date，只支持yyyy-MM-dd格式。
# 创建表t_date
node1 :) CREATE TABLE t_date (x date) ENGINE=TinyLog;

# 向表中插入两条数据
node1 :) INSERT INTO t_date VALUES('2022-06-01'),('2022-07-01');

# 查询结果
node1 :) SELECT x,toTypeName(x) FROM t_date;
SELECT 
    x,
    toTypeName(x)
FROM t_date
┌──────────x─┬─toTypeName(x)─┐
│ 2022-06-01 │ Date            │
│ 2022-07-01 │ Date            │
└────────────┴───────────────┘
2 rows in set. Elapsed: 0.003 sec.

# 获取当前天日期时间及日期转换
node1 :) select now(),toDate(now()) as d,toTypeName(d) ;
SELECT 
    now(),
    toDate(now()) AS d,
    toTypeName(d)
┌───────────────now()─┬──────────d─┬─toTypeName(toDate(now()))─┐
│ 2022-06-25 18:03:00 │ 2022-06-25 │ Date                          │
└─────────────────────┴────────────┴───────────────────────────┘
1 rows in set. Elapsed: 0.004 sec.


八、​​​​​​​​​​​​​​DateTime

DateTime精确到秒，可以指定时区。用四个字节（无符号的）存储Unix时间戳。允许存储与日期类型相同的范围内的值。最小值为0000-00-00 00:00:00，时间戳类型值精确到秒。

时区使用启动客户端或服务器时的系统时区。默认情况下，客户端连接到服务的时候会使用服务端时区。您可以通过启用客户端命令行选项 --use_client_time_zone 来设置使用客户端时区。

  • 示例：
    • toDateTime(DateTimeValue) ：将字符串转成DateTime，只支持yyyy-MM-dd HH:MI:SS。
    • toDateTime(DateTimeValue,时区) ：同上，支持将数据转换为对应时区时间。


# 创建表 t_datetime
node1 :) CREATE TABLE t_datetime(`timestamp` DateTime) ENGINE = TinyLog;

# 向表中插入一条数据
node1 :) INSERT INTO t_datetime Values('2022-06-01 08:00:00');

# 查询数据
node1 :) SELECT timestamp,toTypeName(timestamp) as t FROM t_datetime;
SELECT 
    timestamp,
    toTypeName(timestamp) AS t
FROM t_datetime
┌───────────timestamp─┬─t────────┐
│ 2022-06-01 08:00:00 │ DateTime │
└─────────────────────┴──────────┘
1 rows in set. Elapsed: 0.003 sec

# 转换时区查询
node1 :) SELECT  toDateTime(timestamp, 'Asia/Shanghai') AS column, toTypeName(column) AS x  FROM t_datetime;
SELECT 
    toDateTime(timestamp, 'Asia/Shanghai') AS column,
    toTypeName(column) AS x
FROM t_datetime

┌──────────────column─┬─x─────────────────────────┐
│ 2022-06-01 08:00:00 │ DateTime('Asia/Shanghai') │
└─────────────────────┴───────────────────────────┘

1 rows in set. Elapsed: 0.003 sec.


九、​​​​​​​​​​​​​​DateTime64

DateTime64精确到毫秒和微秒，可以指定时区。在内部，此类型以Int64类型将数据存储。时间刻度的分辨率由precision参数确定。此外，DateTime64 类型可以像存储其他数据列一样存储时区信息，时区会影响 DateTime64 类型的值如何以文本格式显示，以及如何解析以字符串形式指定的时间数据 (‘2020-01-01 05:00:01.000’)。时区信息不存储在表的行中，而是存储在列的元数据中。

语法如下：

# precision 精度，timezone：时区
DateTime64(precision, [timezone])
  • 示例：
    • toDateTime64(timeStr,precision):将字符串转成DateTime64，精度为precision。支持yyyy-MM-dd HH:MI:SS.SSS时间格式。
    • toDateTime64(timeStr,precision，timezone):同上，只是可以将时间转换为对应时区时间。
#创建表
node1 :) CREATE TABLE dt(`timestamp` DateTime64(3, 'Europe/Moscow'),`event_id` UInt8) ENGINE = TinyLog

#插入数据
node1 :) INSERT INTO dt Values (1546300800000, 1), ('2019-01-01 00:00:00', 2)，(1546300812345, 3)

#查询数据
node1 :) select * from dt;
SELECT *
FROM dt
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.000 │        1 │
│ 2019-01-01 00:00:00.000 │        2 │
│ 2019-01-01 03:00:12.345 │        3 │
└─────────────────────────┴──────────┘
3 rows in set. Elapsed: 0.003 sec. 

#使用toDateTime64转换时间
node1 :) select toDateTime64(timestamp,4) as t1,toDateTime64(timestamp,4,'Europe/London') as t2,event_id from dt;
SELECT 
    toDateTime64(timestamp, 4) AS t1,
    toDateTime64(timestamp, 4, 'Europe/London') AS t2,
    event_id
FROM dt
┌───────────────────────t1─┬───────────────────────t2─┬─event_id─┐
│ 2019-01-01 03:00:00.0000 │ 2019-01-01 00:00:00.0000 │        1 │
│ 2019-01-01 00:00:00.0000 │ 2018-12-31 21:00:00.0000 │        2 │
│ 2019-01-01 03:00:12.3450 │ 2019-01-01 00:00:12.3450 │        3 │
└──────────────────────────┴──────────────────────────┴──────────┘
3 rows in set. Elapsed: 0.003 sec.


十、布尔类型

ClickHouse中没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。具体参照枚举类型。


十一、​​​​​​​​​​​​​​枚举类型 Enum

枚举类型通常在定义常量时使用，ClickHouse提供了Enum8和Enum16两种枚举类型。Enum保存'string'=integer的对应关系。在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。

Enum8和Enum16分别对应'String'=Int8和'String'=Int16，Enum8类型的每个值范围是-128 ... 127，Enum16类型的每个值范围是-32768 ... 32767，所有的字符串或者数字都必须是不一样的，允许存在空字符串，Enum类型中数字可以是任意顺序，顺序并不重要。

向Enum字段中插入值时，可以插入枚举的字符串值也可以插入枚举对应的Int值，建议插入对应的字符串值，这样避免插入对应的Int值不在Enum枚举集合中再次查询表时报错。定义了枚举类型值之后，不能写入其他值的数据，写入的值不在枚举集合中就会抛出异常。

  • 示例：
#创建一个表，带有Enum类型的列
node1 :) CREATE TABLE t_enum(x Enum8('hello' = 1, 'world' = 2)) ENGINE = TinyLog

#向表中插入数据
node1 :) insert into t_enum values('hello'),(2);

#查询结果
node1 :) select * from t_enum;

SELECT *
FROM t_enum
┌─x─────┐
│ hello │
│ world │
└───────┘
2 rows in set. Elapsed: 0.003 sec. 

#插入不在枚举集合中的值时，抛出异常
node1 :) insert into t_enum values ('aa')

INSERT INTO t_enum VALUES
Exception on client:
Code: 36. DB::Exception: Unknown element 'aa' for type Enum8('hello' = 1, 'world' = 2)
Connecting to database mydb at localhost:9000 as user default.
Connected to ClickHouse server version 20.8.3 revision 54438.

#使用枚举类型代替boolean类型。建表，并插入数据
node1 :) CREATE TABLE t_enum2(bl Enum8('true' = 1, 'false' = 0)) ENGINE = TinyLog;
node1 :) insert into t_enum2 values(0),(1);
node1 :) select * from t_enum2;

SELECT *
FROM t_enum2
┌─bl────┐
│ false │
│ true  │
└───────┘
2 rows in set. Elapsed: 0.004 sec.

#查询时可以通过函数将对应的Enum底层数值获取出来
node1 :) select toInt8(bl) from t_enum2;

SELECT toInt8(bl)
FROM t_enum2
┌─toInt8(bl)─┐
│          0 │
│          1 │
└────────────┘
2 rows in set. Elapsed: 0.004 sec.


十二、Nullable

Nullable类型只能与基础数据类型搭配使用，表示某个类型的值可以为NULL,Nullable(Int8)表示可以存储Int8类型的值，没有值时存NULL。使用Nullable需要注意：Nullable类型的字段不能作为索引字段，尽量避免使用Nullable类型，因为字段被定义为Nullable类型后会额外生成[Column].null.bin文件保存Null值，增加开销，比普通列消耗更多的存储空间。

  • 示例：
#创建表，含有Nullable类型的列
node1 :) CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog;

#向表 t_null中插入数据
node1 :) INSERT INTO t_null VALUES (1, NULL), (2, 3);

#查询表t_null中的数据
node1 :) select * from t_null;

SELECT *
FROM t_null
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘
2 rows in set. Elapsed: 0.007 sec

#查询表t_null做简单运算
node1 :) SELECT x + y FROM t_null;

SELECT x + y
FROM t_null
┌─plus(x, y)─┐
│       ᴺᵁᴸᴸ │
│          5 │
└────────────┘
2 rows in set. Elapsed: 0.004 sec.


十三、​​​​​​​​​​​​​​数组类型 Array(T)

Array(T)，由 T 类型元素组成的数组。T 可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse对多维数组的支持有限。例如，不能在MergeTree表中存储多维数组。

数组的定义方式有两种：Array(T),[1,2,3... ...],数组类型里面的元素必须具有相同的数据类型，否则会报异常。另外，需要注意的是，数组元素中如果存在Null值，则元素类型将变为Nullable。

从数组中查询获取值使用 xx[1|2.. ...]，直接使用中括号获取值，下标从1开始。

  • 示例：
#两种方式定义数组
node1 :) SELECT array(1, 2) AS x, toTypeName(x),['zs','ls','ww'] as y ,toTypeName(y)
SELECT 
    [1, 2] AS x,
    toTypeName(x),
    ['zs', 'ls', 'ww'] AS y,
    toTypeName(y)

┌─x─────┬─toTypeName(array(1, 2))─┬─y────────────────┬─toTypeName(['zs', 'ls', 'ww'])─┐
│ [1,2] │ Array(UInt8)            │ ['zs','ls','ww'] │ Array(String)                  │
└───────┴─────────────────────────┴──────────────────┴────────────────────────────────┘
1 rows in set. Elapsed: 0.004 sec. 

# 数组中有Null，则数组类型为Nullable
node1 :) SELECT array(1, 2, NULL) AS x, toTypeName(x);
SELECT 
    [1, 2, NULL] AS x,
    toTypeName(x)
┌─x──────────┬─toTypeName(array(1, 2, NULL))─┐
│ [1,2,NULL] │ Array(Nullable(UInt8))        │
└────────────┴───────────────────────────────┘
1 rows in set. Elapsed: 0.006 sec.

#数组类型里面的元素必须具有相同的数据类型，否则会报异常
node1 :) SELECT array(1, 'a')
SELECT [1, 'a']
Received exception from server (version 20.8.3):
Code: 386. DB::Exception: Received from localhost: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.005 sec.

#创建表，含有array(T)类型字段，并插入数据
node1 :) create table t_array(id UInt32,name String,score Array(UInt32)) ENGINE = TinyLog;

#插入数据，注意：字符串在clickhouse中只能是单引号
node1 :) insert into t_array values (1,'zs',array(10,20,30)),(2,'ls',[100,200,300])

#数组内获取值
node1 :) select id,name,score[1] from t_array;
SELECT 
    id,
    name,
    score[1]
FROM t_array
┌─id─┬─name─┬─arrayElement(score, 1)─┐
│  1 │ zs   │  10                    │
│  2 │ ls   │  100                   │
└────┴──────┴────────────────────────┘
5 rows in set. Elapsed: 0.004 sec.


十四、Tuple类型

元组类型有1~n个元素组成，每个元素允许设置不同的数据类型，且彼此之间不要求兼容。与数组类似，元组也可以使用两种方式定义：tuple(1,'hello',12.34)或者直接写(1,'hello',45.67),元组中可以存储多种数据类型，但是要注意数据类型的顺序。

  • 示例：
#创建元组
node1 :) SELECT tuple(1,'a') AS x, toTypeName(x),(1,'b','hello') AS y ,toTypeName(y)

SELECT 
    (1, 'a') AS x,
    toTypeName(x),
    (1, 'b', 'hello') AS y,
    toTypeName(y)
┌─x───────┬─toTypeName(tuple(1, 'a'))─┬─y───────────────┬─toTypeName(tuple(1, 'b', 'hello'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │ (1,'b','hello') │ Tuple(UInt8, String, String)       │
└─────────┴───────────────────────────┴─────────────────┴────────────────────────────────────┘
1 rows in set. Elapsed: 0.004 sec. 

#建表，含有元组类型
node1 :) create table t_tuple(id UInt8,name String,info Tuple(String,UInt8)) engine = TinyLog

#插入数据
node1 :) insert into t_tuple values (1,'zs',tuple('cls1',100)),(2,'ls',('cls2',200))

#查询数据
node1 :) select * from t_tuple;

SELECT *
FROM t_tuple
┌─id─┬─name─┬─info─────────┐
│  1 │ zs   │ ('cls1',100) │
│  2 │ ls   │ ('cls2',200) │
└────┴──────┴──────────────┘
2 rows in set. Elapsed: 0.003 sec.


十五、​​​​​​​​​​​​​​嵌套类型Nested

ClickHouse支持嵌套数据类型（Nested）,可以为一个表定义一个或者多个嵌套数据类型字段，但是每个嵌套字段只支持一级嵌套，即嵌套字段内不能继续使用嵌套类型。嵌套一般用来表示简单的级联关系，嵌套本质上是一个多维数组，嵌套类型中的每个数组的长度必须相同。目前，Nested类型支持很局限，MergeTree引擎中不支持Nested类型。

  • 示例：
#创建一个表，每个人可以属于多个部门，在不同部门有不同的编号id
node1 :) create table t_nested(
:-]            id UInt8,
:-]            name String,
:-]            dept Nested(
:-]               id UInt8,
:-]               name String
:-]       )) engine = TinyLog;

#查看表t_nested的表结构
node1 :) desc t_nested;

DESCRIBE TABLE t_nested
┌─name──────┬─type──────────┬
│ id        │ UInt8         │
│ name      │ String        │
│ dept.id   │ Array(UInt8)  │
│ dept.name │ Array(String) │
└───────────┴───────────────┴
4 rows in set. Elapsed: 0.003 sec.

#向表t_nested中插入数据
node1 :) insert into t_nested values (1,'zs',[10,11,12],['dp1','dp2','dp3']),(2,'ls',[100,101],['dp4','dp5'])

#查询表 t_nested数据，可以获取嵌套类型中部分字段
node1 :) select *,dept.name[1] as first_dpt from t_nested;
SELECT 
    *,
    dept.name[1] AS first_dpt
FROM t_nested
┌─id─┬─name─┬─dept.id────┬─dept.name───────────┬─first_dpt─┐
│  1 │ zs   │ [10,11,12] │ ['dp1','dp2','dp3'] │ dp1       │
│  2 │ ls   │ [100,101]  │ ['dp4','dp5']       │ dp4       │
└────┴──────┴────────────┴─────────────────────┴───────────┘
2 rows in set. Elapsed: 0.004 sec.


十六、​​​​​​​​​​​​​​Domain

Domain类型是特定实现的类型，目前支持IPv4和IPv6两类，本质上他们是对整形和字符串的进一步封装，IPv4类型基于UInt32封装，IPv6基于FixedString(16)封装。

出于便捷性的考量，例如：IPv4类型支持格式检查，格式错误的IP无法被写入。出于性能的考量，IPv4和IPv6相对于String更加紧凑，占用的空间更小，查询性能更快。

在使用Domain时需要注意，虽然表面看起来与String一样，但是Domain类型并不是字符串，也不支持隐式自动转换成字符串，如果需要返回IP的字符串形式，需要调用函数IPv4NumToString()和IPv6NumToString()显式实现。

  • 示例：（示例以IPv4为例，IPv6同理）
#创建表 t_domain 含有IPv4字段
node1 :) CREATE TABLE t_domain(url String, from IPv4) ENGINE = TinyLog;

#插入数据
node1 :) INSERT INTO t_domain(url, from) VALUES ('https://wikipedia.org', '116.253.40.133')('https://clickhouse.tech', '183.247.232.58')('https:/
/clickhouse.tech/docs/en/', '116.106.34.242');

#查看表 t_domain结果数据
node1 :) select * from t_domain;

SELECT *
FROM t_domain
┌─url──────────────────────────────┬───────────from─┐
│ https://wikipedia.org            │ 116.253.40.133 │
│ https://clickhouse.tech          │ 183.247.232.58 │
│ https://clickhouse.tech/docs/en/ │ 116.106.34.242 │
└──────────────────────────────────┴────────────────┘
3 rows in set. Elapsed: 0.004 sec. 

#插入数据不符合IP格式会报错
node1 :) INSERT INTO t_domain(url, from) VALUES ('https://www.baidu.com', '116.253.40')

INSERT INTO t_domain (url, from) VALUES
Exception on client:
Code: 441. DB::Exception: Invalid IPv4 value.

Connecting to database mydb at localhost:9000 as user default.
Connected to ClickHouse server version 20.8.3 revision 54438.

#将表 t_domain 中from IPv4类型转换成String类型
node1 :) SELECT from,toTypeName(from) as tp1,toTypeName(s) as tp2, IPv4NumToString(from) as s FROM t_domain;

SELECT 
    from,
    toTypeName(from) AS tp1,
    toTypeName(s) AS tp2,
    IPv4NumToString(from) AS s
FROM t_domain
┌───────────from─┬─tp1──┬─tp2────┬─s──────────────┐
│ 116.253.40.133 │ IPv4 │ String │ 116.253.40.133 │
│ 183.247.232.58 │ IPv4 │ String │ 183.247.232.58 │
│ 116.106.34.242 │ IPv4 │ String │ 116.106.34.242 │
└────────────────┴──────┴────────┴────────────────┘
3 rows in set. Elapsed: 0.003 sec.
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
大数据 数据结构
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入