Clickhouse数据类型
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 │ │ │ │ │ │
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 删除表
DROP table test_int;
基础语法
– show databases ; 查看数据库
– create database if not exists test1 ; 创建数据库
– use test1 ; 使用数据库
– select currentDatabase() ; 查看当前数据库
– drop database test1 ; 删除数据库
数值类型
IntX和UIntX
ClickHouse使用Int8、Int16、Int32和Int64指代4种大小的Int类型。末尾的数字正好表明了占用字节的大小(8位=1字节)
ClickHouse支持无符号的整数,使用前缀U表示
FloatX
浮点数
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,否则会报错
字符串类型
符串类型可以细分为String、FixedString和UUID三类
String
字符串由String定义,长度不限。因此在使用String的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob和Blob等字符类型。String类型不限定字符集,因为它根本就没有这个概念,所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可维护性,在同一套程序中应该遵循使用统一的编码,例如“统一保持UTF-8编码”就是一种很好的约定。所以在对数据操作的时候我们不在需要区关注编码和乱码问题!
FixedString
FixedString类型和传统意义上的Char类型有些类似,对于一些字符有明确长度的场合,可以使用固定长度的字符串。定长字符串通过FixedString(N)声明,其中N表示字符串长度。但与Char不同的是, FixedString使用null字节填充末尾字符,而Char通常使用空格填充。比如在下面的例子中,字符串‘abc’虽然只有3位,但长度却是5,因为末尾有2位空字符填充 !
UUID
UUID是一种数据库常见的主键类型,在ClickHouse中直接把它作为一种数据类型。UUID共有32位,它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值,则会依照格式使用0填充
CREATE TABLE test_uuid
(
`uid` UUID,
`name` String
)
ENGINE = Log ;
DESCRIBE TABLE test_uuid
┌─name─┬─type───┬
│ uid │ UUID │
│ name │ String │
└──────┴────────┴
insert into test_uuid select generateUUIDv4() , 'zss' ;
insert into test_uuid values (generateUUIDv4() , 'zss') ; --19版本的ck不可使用
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 │
└──────────────────────────────────────┴────────┘
时间类型
Date
Date类型不包含具体的时间信息,只精确到天,支持字符串形式写入:
CREATE TABLE test_date
(
`id` int,
`cd` Date
) engine Memory;
insert into test_date values (1,'2021-09-11'),(2,now()) ;
select id , cd from test_date ;
select * from test_date;
┌─id─┬─────────ct─┐
│ 1 │ 2021-09-11 │
│ 2 │ 2021-05-17 │
└────┴────────────┘
DateTime
DateTime类型包含时、分、秒信息,精确到秒,支持字符串形式写入
create table testDataTime
(
ctime DateTime
) engine = Memory;
insert into testDataTime values('2021-12-27 01:11:12'),(now()) ;
select * from testDataTime;
DateTime64【19版本不支持】
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) │
└────────────────────────┴───────────────────┘
复杂类型
枚举类型 Enum
ClickHouse支持枚举类型,这是一种在定义常量时经常会使用的数据类型。ClickHouse提供了Enum8和Enum16两种枚举类型,它们除了取值范围不同之外,别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据,所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16) 默认Enum8
如果插入非定义的枚举,插入不会报错,查询会报错。
create table test_enum(id Int8 , color Enum ('男'=1 , '女'=0 )) engine=Memory ;
-- 三种插入方式都可以
select * from test_enum;
insert into test_enum values(1,'男');
insert into test_enum values(2,0);
insert into test_enum values(3,0),(4,'男');
-- 插入非定义的枚举 3未定义
insert into test_enum values(5,3);
在定义枚举集合的时候,有几点需要注意。首先,Key和Value是不允许重复的,要保证唯一性。其次,Key和Value的值都不能为Null,但Key允许是空字符串。在写入枚举数据的时候,只会用到Key字符串部分,
注意: 其实我们可以使用字符串来替代Enum类型来存储数据,那么为什么是要使用枚举类型呢?这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型,但是在后续对枚举的所有操作中(包括排序、分组、去重、过滤等),会使用Int类型的Value值 ,提高处理数据的效率!
– 官方文档
- 限制枚举类型字段的值
- 底层存储的是对应的Int类型的数据 , 使用更小的存储空间
- 可以使用String来替代枚举 / 没有值的限定
- 插入数据的时候可以插入指定的字符串 也可以插入对应的int值
数组 Array(T)
CK支持数组这种复合数据类型 , 并且数据在操作在今后的数据分析中起到非常便利的效果!
数组的定义方式有两种 : array(T) [e1,e2…]
- 我们在这里要求数组中的数据类型是一致的!
- 数组下标是从1开始的
select [1,2,3,4,5];
select array(1,2,3,4,5);
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'));
select id , hobby , toTypeName(hobby) from test_array ;
-- 数组的取值 [index] 1-based
select id , hobby[2] , toTypeName(hobby) from test_array ;
-- length(arr) 数组的长度
select * , hobby[1] , length(hobby) from test_array ;
元组 Tuple()
元组类型由1~n个元素组成,每个元素之间允许设置不同的数据类型,且彼此之间不要求兼容。元组同样支持类型推断,其推断依据仍然以最小存储代价为原则。
对于后端来说,可以理解为java对象,封装一些不同数据类型数据到一个对象(元组)。
有一个区别点是支持类型推断
通过获取指定位置 _2 的值 操作数据
获取指定位置得值,使用定义得下标取(类似数组,同样从1开始)
select tuple(1,'asb',12.23) as x , toTypeName(x) ;
CREATE TABLE test_tuple (
c1 Tuple(UInt8, String, Float64)
) ENGINE = Memory;
select * from test_tuple;
insert into test_tuple values ((1,'abner',99.6)),(tuple(2,'kim',88.3));
select tupleElement(c1 , 2) from test_tuple; -- 获取元组指定位置的值 从1开始
select tupleElement(c1 , 3) from test_tuple; -- 获取元组指定位置的值
ALTER TABLE test_tuple UPDATE c1 = (2,'candu',99.6) where c1=(1,'abner',99.6);
--更新语句报错
更新语句会报错
Exception: Mutations are not supported by storage Memory (version 20.8.3.18)
这种引擎不允许突变
(Clickhouse通过alter方式实现更新、删除,它把update、delete操作叫做mutation(突变))
使用MergeTree 引擎重新来过
CREATE TABLE IF NOT EXISTS test_tuple3 (
id Int8,
c1 Tuple(UInt8, String, Float64)
) ENGINE = MergeTree()
ORDER BY id; -- 按照 ID 字段排序
select * from test_tuple3;
select tupleElement(c1 , 2) from test_tuple; -- 获取元组指定位置的值 从1开始
select tupleElement(c1 , 3) from test_tuple; -- 获取元组指定位置的值
insert into test_tuple3 values (1,(1,'abner',99.6)),(2,tuple(2,'kim',88.3));
select * from test_tuple3;
ALTER TABLE test_tuple3 UPDATE c1 = (2,'candu',99.6) where id =1;
-- 能不能单独更新元组得某个元素呢?
ALTER TABLE test_tuple3 UPDATE tupleElement(c1 , 2) = 'candy';
单独更新元组得某个元素,会报错。所以要更新元组要整体更新;
嵌套表类型 Nested
Nested是一种嵌套表结构。一张数据表,可以定义任意多个嵌套类型字段,但每个字段的嵌套层级只支持一级,即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系,使用嵌套类型也是一种不错的选择。
嵌套类型本质是一种多维数组的结构。
嵌套表中的每个字段都是一个数组,并且行与行之间数组的长度无须对齐。
需要注意的是,在同一行数据内每个数组字段的长度必须相等。
create table test_nested(
id Int8 ,
name String ,
scores Nested(
seq UInt8 ,
sx Float64 ,
yy Float64 ,
yw Float64
)
)engine = Memory ;
-- 每个字段都是一个数组
desc test_nested;
┌─name───────┬─type───────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int8 │ │ │ │ │ │
│ name │ String │ │ │ │ │ │
│ scores.seq │ Array(UInt8) │ │ │ │ │ │
│ scores.sx │ Array(Float64) │ │ │ │ │ │
│ scores.yy │ Array(Float64) │ │ │ │ │ │
│ scores.yw │ Array(Float64) │ │ │ │ │ │
└────────────┴────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
-- 行与行之间数组的长度无须对齐
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]);
-- 在同一行数据内每个数组字段的长度必须相等
insert into test_nested values (2,'taoge',[1],[99,10],[14,40],[77,11]);
-- Elements 'scores.seq' and 'scores.sx' of Nested data structure 'scores' (Array columns) have different array sizes
和单纯的多个数组类型的区别是:每行数据中的每个属性数组的长度一致
Map 类型
Map 简单来说的话就是维护键值对之间的映射关系,可以通过键迅速定位到值。
注:
- 21版本以下不支持这种数据类型
- 21 低版本Map 类型是默认禁用的,需要手动开启。本文使用 21.9.5.16 版本默认是开启的。
-- 开启map数据类型 21低版本使用
set allow_experimental_map_type = 1 ;
-- 创建
CREATE TABLE test_map (
a Map(String, UInt64)
) ENGINE=Memory;
-- 插入数据。
insert into test_map values ({'lss':21,'zss':22,'ww':23});
select * from test_map;
┌─a───────────────────────────┐
│ {'lss':21,'zss':22,'ww':23} │
└─────────────────────────────┘
┌─a──────────────────────────────┐
│ {'lss2':21,'zss2':22,'ww2':23} │
└────────────────────────────────┘
desc test_map;
SELECT
*,
mapKeys(a), -- 所有的key
mapValues(a), -- 所有的values
a['lss'], --根据key获取value
length(a) --map长度
FROM test_map
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;
WKT空间数据类型 | geojson数组维度 | ck数据类型 |
---|---|---|
polygon | 3维数组 | Polygon |
multipolygon | 4维数组 | MultiPolygon |
域名类型
域名类型分为IPv4和IPv6两类,本质上它们是对整型和字符串的进一步封装。IPv4类型是基于UInt32封装的.新增了对数据的检测
出于性能的考量,同样以IPv4为例,IPv4使用UInt32存储,相比String更加紧凑,占用的空间更小,查询性能更快。IPv6类型是基于FixedString(16)封装的,它的使用方法与IPv4别无二致, 在使用Domain类型的时候还有一点需要注意,虽然它从表象上看起来与String一样,但Domain类型并不是字符串,所以它不支持隐式的自动类型转换。
如果需要返回IP的字符串形式,则需要显式调用 IPv4NumToString或IPv6NumToString函数进行转换。
create table test_domain(
id Int8 ,
ip IPv4,
ip2 IPv6
)engine=Memory ;
DROP table test_domain;
select * from test_domain;
insert into test_domain values(1,'192.168.133.2','2a02:aa08:e000:3100::2') ;
-- 在插入数据的会进行数据的检查所以这行数据会报错
insert into test_domain values(2,'www.baidu,com') ;
insert into test_domain values(1,'192.168.133') ;
select ip,toTypeName(ip),ip2,toTypeName(ip2),
IPv4NumToString(ip),toTypeName(IPv4NumToString(ip)),
IPv6NumToString(ip2),toTypeName(IPv6NumToString(ip2)) from test_domain;
Boolean和Nullable
ck中没有Boolean类型 ,使用1和0来代表true和false
Nullable 某种数据类型允许为null , 或者是没有给值的情况下模式是NULL
create table test_null(
id Int8 ,
age Int8
)engine = Memory ;
insert into test_null (id) values(1) ; --age为0
select * from test_null;
create table test_null2(
id Int8 ,
isSig Boolean,
age Nullable(Int8)
)engine = Memory ;
desc test_null2;
insert into test_null2 values(1,true,18) ; --报错
insert into test_null2 (id,isSig) values(1,1) ; ----age为null
insert into test_null2 values(1,1,18) ;
insert into test_null2 values(1,0,18) ;
select * from test_null2;