clickhouse timestamp类型 clickhouse optimize

转载

数码墨鱼 2024-06-12 13:06:47

文章标签 分布式数据字段字符串 文章分类 架构后端开发

文章目录

1、ClickHouse的特点
2、数据类型

2.1 整型
2.2 浮点型
2.3 布尔型
2.4 Decimal型
2.5 字符串
2.6 枚举类型
2.7 时间类型
2.8 数组

3、表引擎

3.1 TinyLog
3.2 Memory
3.3 MergeTree
3.4 ReplacingMergeTree
3.5 SummingMergeTree

4、SQL操作

4.1 Insert
4.2 Update和Delete
4.3 查询
4.4 alter
4.5 导出数据

1、ClickHouse的特点

列式存储：

聚合：对于列的聚合，计数，求和等统计操作优于行式存储；
压缩：由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重；由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。

多样化引擎

TinyLog
MergeTree
ReplacingMergeTree
SummingMergeTree

高吞吐写入能力
ClickHouse采用类LSM Tree的结构，数据写入后定期在后台Compaction(类似于HBase)。
通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在HDD上也有着优异的写入性能。
数据分区与线程级并行
优点：ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity(索引粒度)，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条Query就能利用整机所有CPU。极致的并行处理能力，极大的降低了查询延时。
缺点：对于单条查询使用多cpu，就不利于同时并发多条查询。

2、数据类型

2.1 整型

有符号整型[ $clickhouse timestamp类型 clickhouse optimize_数据$ ~ $clickhouse timestamp类型 clickhouse optimize_分布式_02$ ]

Int8：[-128,127]
Int16：[-32768,32767]
Int32：[-2147483648,2147483647]
Int64：[-9223372036854775808,9223372036854775807]

无符号整型[ $clickhouse timestamp类型 clickhouse optimize_数据_03$ ~ $clickhouse timestamp类型 clickhouse optimize_字段_04$ ]

UInt8：[0,255]
UInt16：[0,65535]
UInt32：[0,4294967295]
UInt64：[0,18446744073709551615]

2.2 浮点型

可能造成精度丢失

select 1.0-0.9;

2.3 布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

CREATE TABLE t_boolean
(
    x Enum8('false' = 0, 'true' = 1)
)
ENGINE = TinyLog;

2.4 Decimal型

有符号的浮点数，可在加、减和乘法运算过程中保持精度。

Decimal32(s)，相当于Decimal(9-s,s)，有效位数为1~9
Decimal64(s)，相当于Decimal(18-s,s)，有效位数为1~18
Decimal128(s)，相当于Decimal(38-s,s)，有效位数为1~38

2.5 字符串

String
字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
FixedString(N)
固定长度N的字符串。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。

2.6 枚举类型

Enum8 和 Enum16 类型。Enum 保存 ‘string’= integer 的对应关系。

Enum8 用 ‘String’= Int8 对描述。

Enum16 用 ‘String’= Int16 对描述。

CREATE TABLE t_enum
(
    x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello');

insert into t_enum values('a');--报错

SELECT x,CAST(x, 'Int8') FROM t_enum;

2.7 时间类型

Date接受年-月-日的字符串 2019-12-16

Datetime接受年-月-日时:分:秒的字符串 2019-12-16 20:50:10

Datetime64接受年-月-日时:分:秒.亚秒的字符串 2019-12-16 20:50:10.66

2.8 数组

SELECT array(1, 2) AS x, toTypeName(x) ;
SELECT [1,2] AS x, toTypeName(x) ;

3、表引擎

如何选择表引擎？从以下方面考虑：

数据的存储方式和位置，写到哪里以及从哪里读取数据
支持哪些查询以及如何支持
并发数据访问
索引的使用（如果存在）
是否可以执行多线程请求
数据复制参数

表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。

特别注意：引擎的名称大小写敏感

3.1 TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。

create table t_tinylog ( id String, name String) engine=TinyLog;

3.2 Memory

数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现。

3.3 MergeTree

MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎，支持索引和分区。

create table t_order_mt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
 ) engine =MergeTree
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id,sku_id);
   
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

参数详解：

partition by(可选)：

分区。
如果不填，默认一个分区。
分区目录：列文件+索引文件+表定义文件
并行：分区后，面对涉及跨分区的查询统计，ClickHouse会以分区为单位并行处理。
数据写入和分区合并：写入后的某个时刻（大概10-15分钟后），ClickHouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中

select * from t_order_mt;

insert into  t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

select * from t_order_mt;

optimize table t_order_mt final;
select * from t_order_mt;

primary key主键(可选)：

只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。
主键的设定主要依据是查询语句中的where 条件。根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全表扫描。index granularity：直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse中的MergeTree默认是8192。
稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

order by(必选)

分区内的数据按照哪些字段顺序进行有序保存
主键必须是order by字段的前缀字段（比如order by 字段是 (id,sku_id) 那么主键必须是id 或者(id,sku_id)）。因为需要先保证主键有序，这样稀疏索引才有效。

数据TTL

列级别TTL

可以管理数据或者列的生命周期的功能。

create table t_order_mt2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2)  TTL create_time+interval 10 SECOND,
    create_time  Datetime 
 ) engine =MergeTree
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id, sku_id);
   
   
   
insert into  t_order_mt2 values
(106,'sku_001',1000.00,'2020-06-12 22:52:30'),
(107,'sku_002',2000.00,'2020-06-12 22:52:30'),
(110,'sku_003',600.00,'2020-06-13 12:00:00');

指定时间过后通过optimize刷新，发现指定字段的值变为对应类型的默认值。

表级别TTL
数据会在create_time 之后10秒丢失

alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。

-SECOND

- MINUTE

- HOUR

- DAY

- WEEK

- MONTH

- QUARTER

- YEAR

3.4 ReplacingMergeTree

基于MergeTree，新增去重的功能。

去重时机
数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。
去重范围
如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。

-- 建表
create table t_order_rmt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time  Datetime
 ) engine = ReplacingMergeTree(create_time)
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id, sku_id);
   
-- 插入数据  
insert into  t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

--执行第一次查询
select * from t_order_rmt;

--手动合并
OPTIMIZE TABLE t_order_rmt FINAL;

--执行第二次查询
select * from t_order_rmt;

结论：

ReplacingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。
实际上是使用order by 字段作为唯一键
去重不能跨分区
只有合并才会进行去重
认定重复的数据保留，版本字段值最大的
如果版本字段相同则按插入顺序保留最后一笔

3.5 SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

--建表
create table t_order_smt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time Datetime
 ) engine =SummingMergeTree(total_amount)
   partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id,sku_id );
--插入数据
insert into t_order_smt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');


--执行第一次查询
select * from t_order_rmt;

--手动合并
OPTIMIZE TABLE t_order_rmt FINAL;

--执行第二次查询
select * from t_order_rmt;

结论：

以SummingMergeTree（）中指定的列作为汇总数据列
可以填写多列数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
以order by 的列为准，作为维度列
其他的列按插入顺序保留第一行
不在一个分区的数据不会被聚合

问题：

能不能直接执行以下SQL得到汇总值

select total_amount from  XXX where province_name=’’ and create_date=’xxx’

不能，可能会包含一些还没来得及聚合的临时明细

如果要是获取汇总值，还是需要使用sum进行聚合，这样效率会有一定的提高，但本身ClickHouse是列式存储的，效率提升有限，不会特别明显。

select sum(total_amount) from province_name=’’ and create_date=‘xxx’

4、SQL操作

4.1 Insert

-- 一般
insert into [table_name] values(…),(….)
-- 表到表
insert into [table_name] select a,b,c from [table_name_2]

4.2 Update和Delete

ClickHouse提供了Delete和Update的能力，这类操作被称为Mutation查询，它可以看做Alter 的一种。虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。

--删除操作
alter table t_order_smt delete where sku_id ='sku_001';
--修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;

4.3 查询

支持子查询
支持CTE（Common Table Expression 公用表表达式with子句）
支持部分窗口函数
不支持自定义函数
GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计

rollup：
select a,b,sum(c) from t group by a,b with rollup;
===>
select a,b,sum(c) from t group by a,b
union all
select a,默认值,sum(c) from t group by a
union all
select 默认值,默认值,sum(c) from t;

-----------
group by a,b,c with rollup 
=> 
group a,b,c
union all 
group a,b
union all 
group a
union all 
sum(c)

cube:
sum(c) group by a,b with cube
==>
sum(c) group by a,b
union all
sum(c) group by a
union all
sum(c) group by b
union all
sum(c)

------------
total:
group by a,b,c with total
=> 
group a,b,c
union all 
sum(c)
-------------
Hive:with rollup,with cube,grouping sets()

group by a,b with cube
==>
group by a,b grouping sets((a,b),(a),(b),())

4.4 alter

新增字段

alter table tableName add column newcolname String after col1;

修改字段类型

alter table tableName modify column newcolname String;

删除字段

alter table tableName drop column newcolname;

4.5 导出数据

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv1

类似于hive中的

hive -e "查询语句"
hive -f ***.hql文件

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：altium designer 复制元件到另一个原理图 altium designer如何复制元器件

下一篇：kafka3 延时消息 kafka发送延迟消息

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯