Clickhouse(4)---Clickhouse数据类型

最新推荐文章于 2024-07-30 20:09:12 发布

Abner G

最新推荐文章于 2024-07-30 20:09:12 发布

阅读量1k

点赞数

分类专栏： Clickhouse 文章标签：数据库 database 数据类型

本文链接：https://blog.csdn.net/Abner_G/article/details/120879760

版权

Clickhouse 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

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;

Abner G

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Clickhouse(4)---Clickhouse数据类型

Clickhouse数据类型数值类型IntX和UIntXFloatXDecimal字符串类型StringFixedStringUUID时间类型二级目录复杂类型二级目录函数算数函数CK中关键字严格区分大小写建表的时候一般情况下要指定引擎!!!-- 创建表create table tb_test1(id Int8 , name String )engine=Memory;┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬
复制链接

扫一扫