目录
一、文档阅读
ClickHouse 是于2016年开源的列式存储数据库(DBMS)。
1、 关于 表引擎的
表引擎的介绍:表引擎是ClickHouse的一大特色。可以说, 表引擎决定了如何存储表的数据。
表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎,以及引擎使用的相关参数。
特别注意:引擎的名称大小写敏感
1.1、TinyLog
以列文件的形式保存在磁盘上,不支持索引,没有并发控制。一般保存少量数据的小表,生产环境上作用有限。可以用于平时练习测试用。
create table t_tinylog ( id String, name String) engine=TinyLog;
1.2、Memory
内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失。读写操作不会相互阻塞,不支持索引。
自我理解:是基于内存进行保存数据的,若是集群宕机断电数据将会丢失。
1.3、MergeTree
ClickHouse中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(*MergeTree)中的其他引擎,支持索引和分区,地位可以相当于innodb之于Mysql。 而且基于MergeTree,还衍生出了很多小弟,也是非常有特色的引擎。
1)建表语句
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);
2)插入数据
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');
MergeTree其实还有很多参数(绝大多数用默认值即可),但是三个参数是更加重要的,也涉及了关于 MergeTree 的很多概念。
主要有一下几个参数需要设置:
partition by、order by、primary key、TTL
1.4、ReplacingMergeTree
ReplacingMergeTree是MergeTree的一个变种,它存储特性完全继承MergeTree,只是多了一个去重的功能。 尽管MergeTree可以设置主键,但是primary key其实没有唯一约束的功能。如果你想处理掉重复的数据,可以借助这个ReplacingMergeTree。
1)去重时机
数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行,所以你无法预先作出计划。有一些数据可能仍未被处理。
2)去重范围
如果表经过了分区,去重只会在分区内部进行去重,不能执行跨分区的去重。
所以ReplacingMergeTree能力有限, ReplacingMergeTree 适用于在后台清除重复的数据以节省空间,但是它不保证没有重复的数据出现。
ReplacingMergeTree | ClickHouse Docs
感悟:
去重是按照 order by 中的字段作为唯一键进行去重的,当没有设置版本号时,保留最后一条数据。但是呢,官网上也说了,这个也不一定保证没有重复数据的出现。
1.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 );
感悟:以 order by 中的字段作为聚合的维度,SummingMergeTree () 中的列作为聚合列。
2、SQL操作
2.1 Insert
从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]
2.2 Update 和 Delete
官网上有很多关于增加列或删除列的操作
Column Manipulations | ClickHouse Docs
1) 删除操作
alter table t_order_smt delete where sku_id ='sku_001';
2)修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;
2.3 数据导入导出
1)官网
2)命令行数据导入
3)实操 数据导出
clickhouse-client --query
"select * from t_order_mt where create_time='2020-06-01 12:00:00'"
--format CSVWithNames> /opt/module/data/rs1.csv
更多支持格式参照:
ck_sql="
"
sql1="select * from dw_071000.tpl_im_mec_vehicle_base limit 1"
sql2="select * from dw_071000.tpl_im_section_flow_5min limit 1"
clickhouse-client --host 10.39.235.24 --port 9000 --user default --password root --multiquery -q"${sql1};${sql2}"
3、副本
感悟:副本的概念类似于hadoop中的副本,目的是为了防止数据丢失,一份数据几个副本就保存几份。
需要依赖 zk 进行管理,当有一个 ch 节点挂掉后可以从其他节点拿到数据。就像是 hadoop 依赖 zk 一样。
注意:
副本是表级别的,不是整个服务器级的。可以针对表进行设置副本有几个。
4、分片集群
副本虽然能够提高数据的可用性,降低丢失风险,但是每台服务器实际上必须容纳全量数据,对数据的横向扩容没有解决。
要解决数据水平切分的问题,需要引入分片的概念。
通过分片把一份完整的数据进行切分,不同的分片分布到不同的节点上,再通过Distributed表引擎把数据拼接起来一同使用。
Distributed表引擎本身不存储数据,有点类似于MyCat之于MySql,成为一种中间件,通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。
注意:ClickHouse的集群是表级别的,实际企业中,大部分做了高可用,但是没有用分片,避免降低查询性能以及操作集群的复杂性。
5、建表实操
CREATE TABLE dw_110000.tpl_im_mec_vehicle_base_local
(
`id` String COMMENT '唯一id',
`source` UInt8 DEFAULT '1' COMMENT '1-边缘计算设备',
`device_id` UInt64 COMMENT '设备id',
`device_pile_no` String COMMENT '设备桩号',
`altitude` Float64 COMMENT '高程',
`heading` Float64 COMMENT '航向偏离正北的角度,顺指针为正',
`report_time` DateTime DEFAULT now() COMMENT '上报时间',
`plate_number` String COMMENT '车牌号',
`obstacle_id` String COMMENT '交通参与者id',
`plate_color` String COMMENT '车牌颜色',
`vehicle_type` UInt8 COMMENT '车辆类型,1-小汽车,2-货车,3-大巴车,4-面包车,5-危化品车,99-其他',
`vehicle_color` String COMMENT '车辆颜色',
`vehicle_speed` UInt16 COMMENT '车辆速度 km/h',
`direction` UInt8 COMMENT '1-雄安方向,2-北京方向',
`line` UInt8 COMMENT '车道号,1-1车道,2-2车道,3-3车道,4-4车道,5-应急车道',
`road_section` UInt8 COMMENT '路段归属',
`latitude` Decimal(17, 10) DEFAULT '0.00000000000000' COMMENT '纬度',
`longitude` Decimal(17, 10) DEFAULT '0.00000000000000' COMMENT '经度',
`delete_flag` UInt8 DEFAULT '0' COMMENT '删除标识 0正常 1删除',
`ts` UInt64,
`create_time` DateTime DEFAULT now() COMMENT '创建时间',
`update_time` DateTime DEFAULT now() COMMENT '更新时间'
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY report_time
ORDER BY report_time
TTL create_time + toIntervalMonth(3)
SETTINGS index_granularity = 8192
COMMENT 'MEC过车数据';
①、PARTITION BY (分区) :
这个相当于 hive 中的分区字段 dt,按照什么什么分区。
作用 : 分区的目的主要是降低扫描的范围,优化查询速度。
不填:只会使用一个分区。
数据写入与分区合并:
任何一个批次的数据写入都会产生一个临时分区,不会纳入任何一个已有的分区。写入后的某个时刻(大概10-15分钟后),ClickHouse会自动执行合并操作(等不及也可以手动通过optimize执行),把临时分区的数据,合并到已有分区中。
optimize table 表名 final;
分区是跟存储相关的,而索引是与查询有关的。
②、primary key主键(可选)
虽然是ck中的主键,但是它只是一级索引,并没有 唯一约束的功能。
ClickHouse中的主键,和其他数据库不太一样,它只提供了数据的一级索引,但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。
根据条件通过对 主键进行某种形式的二分查找,能够定位 到对应的index granularity,避免了全表扫描。
index granularity: 直接翻译的话就是索引粒度,指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse中的MergeTree默认是8192。(感觉是一个索引下所包含的数据量大小的最大值 )。
感觉一级索引也是挺重要的。可以增加
③、order by:
要求:主键必须是order by字段的前缀字段。
比如order by 字段是 (id,sku_id) 那么主键必须是id 或者(id,sku_id)
order by 是联合主键的意思.
order by 表示 数据按照什么字段进行排序。
④、TTL: TTL 即Time To Live,MergeTree提供了可以管理数据表或者列的生命周期的功能。
分为字段级别和表级别 生存时间。
比如:表级别的 (下面是修改表级别的 TTL)
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
在 到达时间后,对于表级别的 TTL ,数据将会丢失。
注意:
涉及判断的字段必须是Date或者Datetime类型,推荐使用分区的日期字段。
能够使用的时间周期:
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
二、官网查看
官方中文文档:
这里还可以调整语言:
其他博客上看的:
含有_local 后缀的表示是一个本地表,采用读写分离模式?