Clickhouse的数据类型

1 数据类型

注意在CK中关键字严格区分大小写

ck中建表的时候 ,一定指定表引擎!

create table tb_test1(
id Int8 ,
 name String 
)engine=Memory;
┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int8   │              │                    │         │                  │                │
│ name │ String │              │                    │         │                  │                │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

1.1 数值类型

1) IntX和UIntX

以前我们常用Tinyint、Smallint、Int和Bigint指代整数的不同取值范围。而ClickHouse则直接使用Int8、Int16、Int32和Int64指代4种大小的Int类型，其末尾的数字正好表明了占用字节的大小（8位=1字节），

*ClickHouse支持无符号的整数，使用前缀U表示*

create table test_int(
    id Int8 ,
    age UInt8 ,
    cdId Int32
)engine=Memory ;

2) FloatX

注意: 和我以前的认知是一样的,这种数据类型在数据特别精准的情况下可能出现数据精度问题!

Select 8.0/0 -->inf 正无穷

Select -8.0/0 -->inf 负无穷

Select 0/0 -->nan 非数字

3) Decimal

如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提 供了Decimal32、Decimal64和Decimal128三种精度的定点数。可以通过 两种形式声明定点：简写方式有Decimal32(S)、Decimal64(S)、

Decimal128(S)三种，原生方式为Decimal(P,S)，其中：

·P代表精度，决定总位数（整数部分+小数部分），取值范围是1 ～38；·S代表规模，决定小数位数，取值范围是0～P

在使用两个不同精度的定点数进行四则运算的时候，它们的小数点 位数S会发生变化。

在进行加法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相加后S=4：

在进行减法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相减后S=4：

在进行乘法运算时，S取最和。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相乘后S=4+2：

在进行除法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相除后S=4：但是要保证被除数的S大于除数的S,否则会报错

create table test_decimal(
id Int8 ,
    sal Decimal(5,2)
)engine=Memory ;

1.2 字符串类型

符串类型可以细分为String、FixedString和UUID三类。从命名来看仿佛不像是由一款数据库提供的类型，反而更像是一门编程语言的设计,没错CK语法具备编程语言的特征(数据+运算)

1) String

字符串由String定义，长度不限。因此在使用String的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob和Blob等字符类型。String类型不限定字符集，因为它根本就没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性，在同一套程序中应该遵循使用统一的编码，例如“统一保持UTF-8编码”就是一种很好的约定。所以在对数据操作的时候我们不在需要区关注编码和乱码问题!

2) FixedString

FixedString类型和传统意义上的Char类型有些类似，对于一些字符有明确长度的场合，可以使用固定长度的字符串。定长字符串通过FixedString(N)声明，其中N表示字符串长度。但与Char不同的是，

FixedString使用null字节填充末尾字符，而Char通常使用空格填充。比如在下面的例子中，字符串‘abc’虽然只有3位，但长度却是5，因为末尾有2位空字符填充 !

create table test_str(
name String ,
    job FixedString(4)   -- 最长4个字符
)engine=Memory ;

3) UUID

UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充

CREATE TABLE test_uuid
(
    `uid` UUID,
    `name` String
)
ENGINE = Memory ;
DESCRIBE TABLE test_uuid

┌─name─┬─type───┬
│ uid  │ UUID   │
│ name │ String │ 
└──────┴────────┴
insert into test_uuid select generateUUIDv4() , 'zss' ;
select * from test_uuid ;
┌──────────────────────────────────uid─┬─name─┐
│ 47e39e22-d2d6-46fd-8014-7cd3321f4c7b │ zss  │
└──────────────────────────────────────┴──────┘

-------------------------UUID类型的字段默认补位0-----------------------------
insert into test_uuid (name) values('hangge') ;
┌──────────────────────────────────uid─┬─name─┐
│ 47e39e22-d2d6-46fd-8014-7cd3321f4c7b │ zss  │
└──────────────────────────────────────┴──────┘
┌──────────────────────────────────uid─┬─name───┐
│ 00000000-0000-0000-0000-000000000000 │ hangge │
└──────────────────────────────────────┴────────┘

1.3 时间类型

1) Date

Date类型不包含具体的时间信息，只精确到天，支持字符串形式写入：

CREATE TABLE test_date
(
    `id` int,
    `ct` Date
)
ENGINE = Memory ;
DESCRIBE TABLE test_date  ;
┌─name─┬─type──┬
│ id   │ Int32 │
│ ct   │ Date  │
└──────┴───────┴
insert into test_date vlaues(1,'2021-09-11'),(2,now()) ;
select id , ct from test_date ;

┌─id─┬─────────ct─┐
│  1 │ 2021-09-11 │
│  2 │ 2021-05-17 │
└────┴────────────┘

2) DateTime

DateTime类型包含时、分、秒信息，精确到秒，支持字符串形式写入：

create table testDataTime(ctime DateTime) engine=Memory ;
insert into testDataTime values('2021-12-27 01:11:12'),(now()) ;
select * from testDataTime ;

3)DateTime64

DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置

-- 建表 
CREATE TABLE test_date_time64
(
    `ctime` DateTime64
)
ENGINE = Memory ;
-- 建表
CREATE TABLE test_date_time64_2
(
    `ctime` DateTime64(2)
)
ENGINE = Memory ;
-- 分别插入数据
insert into test_date_time64 values('2021-11-11 11:11:11'),(now()) ;
insert into test_date_time64_2 values('2021-11-11 11:11:11'),(now()) ;
-- 查询数据
SELECT *
FROM test_date_time64;
┌───────────────────ctime─┐
│ 2021-11-11 11:11:11.000 │
│ 2021-05-17 10:40:51.000 │
└─────────────────────────┘
SELECT 
    *, toTypeName(ctime)
FROM test_date_time64

┌───────────────────ctime─┬─toTypeName(ctime)─┐
│ 2021-11-11 11:11:11.000 │ DateTime64(3)     │
│ 2021-05-17 10:40:51.000 │ DateTime64(3)     │
------------------------------------------------
SELECT 
    *, toTypeName(ctime)
FROM test_date_time64_2

┌──────────────────ctime─┬─toTypeName(ctime)─┐
│ 2021-11-11 11:11:11.00 │ DateTime64(2)     │
│ 2021-05-17 10:41:26.00 │ DateTime64(2)     │
└────────────────────────┴───────────────────┘

1.4 复杂类型

1) Enum

ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse提供了Enum8和Enum16两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)!

create table test_enum(id Int8 , color Enum('red'=1 , 'green'=2 , 'blue'=3)) engine=Memory ;
insert into  test_enum values(1,'red'),(1,'red'),(2,'green');
也可以使用这种方式进行插入数据:
insert into test_enum values(3,3) ;

在定义枚举集合的时候，有几点需要注意。首先，Key和Value是不允许重复的，要保证唯一性。其次，Key和Value的值都不能为Null，但Key允许是空字符串。在写入枚举数据的时候，只会用到Key字符串部分，

注意: 其实我们可以使用字符串来替代Enum类型来存储数据,那么为什么是要使用枚举类型呢？这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中（包括排序、分组、去重、过滤等），会使用Int类型的Value值 ,提高处理数据的效率!

• 限制枚举类型字段的值
• 底层存储的是对应的Int类型的数据
• 可以使用String

2) Array(T)

CK支持数组这种复合数据类型 , 并且数据在操作在今后的数据分析中起到非常便利的效果!数组的定义方式有两种 : array(T) [e1,e2…] , 我们在这里要求数组中的数据类型是一致的!

数组的定义 
[1,2,3,4,5]
array('a' , 'b' , 'c')
[1,2,3,'hello']   -- 错误
create table test_array(
id Int8 ,
hobby Array(String)
)engine=Memory ;
insert into test_array values(1,['eat','drink','la']),(2,array('sleep','palyg','sql'));
┌─id─┬─hobby───────────────────┐
│  1 │ ['eat','drink','la']    │
│  2 │ ['sleep','palyg','sql'] │
└────┴─────────────────────────┘
select id , hobby  , toTypeName(hobby) from test_array ;
┌─id─┬─hobby───────────────────┬─toTypeName(hobby)─┐
│  1 │ ['eat','drink','la']    │ Array(String)     │
│  2 │ ['sleep','palyg','sql'] │ Array(String)     │
└────┴─────────────────────────┴───────────────────┘
 select id , hobby[2]  , toTypeName(hobby) from test_array ; -- 数组的取值 [index]  1-based

3) Tuple

元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然以最小存储代价为原则。与数组类似，元组也可以使用两种方式定义，常规方式tuple(T)：元组中可以存储多种数据类型,但是要注意数据类型的顺序

select tuple(1,'asb',12.23) as x , toTypeName(x) ;
 ┌─x───────────────┬─toTypeName(tuple(1, 'asb', 12.23))─┐
│ (1,'asb',12.23) │ Tuple(UInt8, String, Float64)      │
└─────────────────┴────────────────────────────────────┘
---简写形式
SELECT 
    (1, 'asb', 12.23) AS x,
    toTypeName(x)

┌─x───────────────┬─toTypeName(tuple(1, 'asb', 12.23))─┐
│ (1,'asb',12.23) │ Tuple(UInt8, String, Float64)      │
└─────────────────┴────────────────────────────────────┘
注意:建表的时候使用元组的需要制定元组的数据类型
CREATE TABLE test_tuple ( 
c1 Tuple(UInt8, String, Float64) 
) ENGINE = Memory; 

• (1,2,3,‘abc’)
• tuple(1,2,3,‘abc’)
• col Tuple(Int8,Int8,String) – 定义泛型

4) Nested

Nested是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。

create table test_nested(
    uid Int8 ,
    name String ,
    props Nested(
        pid Int8,
        pnames String ,
        pvalues String
    )
)engine = Memory ;
desc test_nested ;
┌─name──────────┬─type──────────┬
│ uid           │ Int8          │
│ name          │ String        │
│ props.pid     │ Array(Int8)   │
│ props.pnames  │ Array(String) │
│ props.pvalues │ Array(String) │
└───────────────┴───────────────┴

嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐。需要注意的是，在同一行数据内每个数组字段的长度必须相等。

insert into test_nested values(1,'hadoop',[1,2,3],['p1','p2','p3'],['v1','v2','v3']);
-- 行和行之间的属性的个数可以不一致 ,但是当前行的Nested类型中的数组个数必须一致
insert into test_nested values(2,'spark',[1,2],['p1','p2'],['v1','v2']);
SELECT *
FROM test_nested

┌─uid─┬─name───┬─props.pid─┬─props.pnames─────┬─props.pvalues────┐
│   1 │ hadoop │ [1,2,3]   │ ['p1','p2','p3'] │ ['v1','v2','v3'] │
└─────┴────────┴───────────┴──────────────────┴──────────────────┘
┌─uid─┬─name──┬─props.pid─┬─props.pnames─┬─props.pvalues─┐
│   2 │ spark │ [1,2]     │ ['p1','p2']  │ ['v1','v2']   │
└─────┴───────┴───────────┴──────────────┴───────────────┘
SELECT 
    uid,
    name,
    props.pid,
    props.pnames[1]
FROM test_nested;
┌─uid─┬─name───┬─props.pid─┬─arrayElement(props.pnames, 1)─┐
│   1 │ hadoop │ [1,2,3]   │ p1                            │
└─────┴────────┴───────────┴───────────────────────────────┘
┌─uid─┬─name──┬─props.pid─┬─arrayElement(props.pnames, 1)─┐
│   2 │ spark │ [1,2]     │ p1                            │
└─────┴───────┴───────────┴───────────────────────────────┘

create table test_nested(
id Int8 ,
name String  ,
scores Nested(
       seq  UInt8 ,
	   sx Float64 ,
       yy Float64 ,  
	   yw Float64
	   )
)engine = Memory ;

insert into test_nested values (1,'wbb',[1,2,3],[11,12,13],[14,14,11],[77,79,10]);
insert into test_nested values (2,'taoge',[1,2],[99,10],[14,40],[77,11]);
-- 注意 每行中的数组的个数一致  行和行之间可以不一直被

┌─id─┬─name─┬─scores.seq─┬─scores.sx──┬─scores.yy──┬─scores.yw──┐
│  1 │ wbb  │ [1,2,3]    │ [11,12,13] │ [14,14,11] │ [77,79,10] │
└────┴──────┴────────────┴────────────┴────────────┴────────────┘
┌─id─┬─name──┬─scores.seq─┬─scores.sx─┬─scores.yy─┬─scores.yw─┐
│  2 │ taoge │ [1,2]      │ [99,10]   │ [14,40]   │ [77,11]   │
└────┴───────┴────────────┴───────────┴───────────┴───────────┘
SELECT 
    name,
    scores.sx
FROM test_nested;
┌─name─┬─scores.sx──┐
│ wbb  │ [11,12,13] │
└──────┴────────────┘
┌─name──┬─scores.sx─┐
│ taoge │ [99,10]   │
└───────┴───────────┘

5) Map (本教程版本不支持)

https://repo.yandex.ru/clickhouse/rpm/testing/x86_64/ 下载新版本的CK安装包

6) GEO

• Point

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_point (p Point) ENGINE = Memory();
INSERT INTO geo_point VALUES((10, 10));
SELECT p, toTypeName(p) FROM geo_point;
┌─p───────┬─toTypeName(p)─┐
│ (10,10) │ Point         │
└─────────┴───────────────┘

• Ring

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_ring (r Ring) ENGINE = Memory();
INSERT INTO geo_ring VALUES([(0, 0), (10, 0), (10, 10), (0, 10)]);
SELECT r, toTypeName(r) FROM geo_ring;

┌─r─────────────────────────────┬─toTypeName(r)─┐
│ [(0,0),(10,0),(10,10),(0,10)] │ Ring          │
└───────────────────────────────┴───────────────┘

• Polygon

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_polygon (pg Polygon) ENGINE = Memory();
INSERT INTO geo_polygon VALUES([[(20, 20), (50, 20), (50, 50), (20, 50)], [(30, 30), (50, 50), (50, 30)]]);
SELECT pg, toTypeName(pg) FROM geo_polygon;

• MultiPolygon

SET allow_experimental_geo_types = 1;
CREATE TABLE geo_multipolygon (mpg MultiPolygon) ENGINE = Memory();
INSERT INTO geo_multipolygon VALUES([[[(0, 0), (10, 0), (10, 10), (0, 10)]], [[(20, 20), (50, 20), (50, 50), (20, 50)],[(30, 30), (50, 50), (50, 30)]]]);
SELECT mpg, toTypeName(mpg) FROM geo_multipolygon;

7)IPV4

域名类型分为IPv4和IPv6两类，本质上它们是对整型和字符串的进一步封装。IPv4类型是基于UInt32封装的

（1）出于便捷性的考量，例如IPv4类型支持格式检查，格式错误的IP数据是无法被写入的，例如：

INSERT INTO IP4_TEST VALUES (‘www.51doit.com’,‘192.0.0’)

Code: 441. DB::Exception: Invalid IPv4 value.

（2）出于性能的考量，同样以IPv4为例，IPv4使用UInt32存储，相比String更加紧凑，占用的空间更小，查询性能更快。IPv6类型是基于FixedString(16)封装的，它的使用方法与IPv4别无二致, 在使用Domain类型的时候还有一点需要注意，虽然它从表象上看起来与String一样，但Domain类型并不是字符串，所以它不支持隐式的自动类型转换。如果需要返回IP的字符串形式，则需要显式调用 IPv4NumToString或IPv6NumToString函数进行转换。

create table test_domain(
id Int8 ,
ip IPv4
)engine=Memory ;
insert  into test_domain values(1,'192.168.133.2') ;
insert  into test_domain values(1,'192.168.133') ; 在插入数据的会进行数据的检查所以这行数据会报错
-- Exception on client:
-- Code: 441. DB::Exception: Invalid IPv4 value.
-- Connecting to database doit1 at localhost:9000 as user default.
-- Connected to ClickHouse server version 20.8.3 revision 54438.

8) Boolean和Nullable

ck中没有Boolean类型 ,使用1和0来代表true和false

Nullable 某种数据类型允许为null , 或者是没有给值的情况下模式是NULL

create table test_null(
id  Int8 ,
 age Int8
)engine = Memory ;

create table test_null2(
id  Int8 ,
 age Nullable(Int8)
)engine = Memory ;