ClickHouse(一)概念、基础
ClickHouse概念
ClickHouse概述
1.列式是数据库 2.在线分析处理(OLAP) 3.底层是C++编写
多样化引擎、数据分区/线程级并行(耗CPU)、顺序读写。适用于固定宽表查询
单表查询擅长,多表关联查询不擅长。
ClickHouse数据类型
Int:Int8、Int16、Int32、Int64。UInt8、UInt16、UInt32、UInt64。
Float:Float32、Float64
Boolean:Int8
Decimal:Decimal32(5)整数+小数=9位 小数5位、Decimal64(5)整数+小数=18位 小数5位
Decimal128(5)整数+小数=38位 小数5位
String:String、FixedString(N)添加空字节补全
Enum:Enum8('hello'=1,'world'=2) 'String'=Int8 描述 Enum16 'String'=Int8
插入的时候只能插入固定的字符串或者整数、select cast(x,'Int8') from table;
日期:Date(2019-12-16) Datetime(2019-12-16 10:15:10) Datetime64(2019-12-16 10:15:10.47)
数组:Array(1,2) 或者 [1,2]
空值:Nullable 使用空值会对性能产生负面影响
表引擎
TinyLog:列文件保存在磁盘上,不支持索引、没有并发控制、保存数据量小的表(小于100万行)
Memory:以未压缩形式保存在内存中、服务器重启会消失、数据量不大太(一亿以内)
MergeTree:支持索引分区、其他表子引擎
引擎
MergeTree表语句+解释
//主键索引,但是数据不强调唯一
create table data(id Uint32,name String,time Datetime)engine=MergeTree
partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)
SETTINGS index_granularity=8192 //数据间隔 大量重读的需要调整
//分区是分目录(列文件+索引文件+表定义文件) 一个分区一个线程
//order by 是分区内有序的,是必须填的。orderby 主键必须是第一个字段
//二级索引:create table data(id Uint32,INDEX a total_amount TYPE minmax GRANULARITY 5)
//二级索引作用:minmax GRANULARITY二级索引对于一级索引粒度的粒度(每次跳几个一级索引)
//二级索引场景:数据大量重复,一级索引不明显的时候
TTL
create table data(id Uint32 TTL create_time + INERVAL 10 SECONG); 字段级别
alter table data(id Uint32) MODIFY TTL create_time + INERVAL 10 SECONG; 表级别
create table data(id Uint32,name String,time Datetime)engine=MergeTree
partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)
TTL create_time + INERVAL 1 MONTH DELETE WHERE toDayOfWeek(d)=1
SECONG/MINUTE/HOUR/DAY/MONTH
create_time 不能是主键,类型必须是日期类型
储文件解释
//分区存储:分区名称+最小分区编号+最小分区编号+合并等级(被合并的次数)
//手动合并分区命令:optimize table XXXXX partition '20221010' final;
bin文件:数据文件 mrk:标记文件(idx,bin桥梁作用)
primary.idx:主键索引文件,加快查询效率
minmax_create_time.idx:分区键的最大最小值
checksums.txt:校验文件,用于校验各个文件的正确性。存放文件的size和hash值
ReplacingMergeTree
性能继承MergeTree,但是多了去重功能,order by 字段作为唯一约束
去重的时机:数据插入,合并分区去重,插入数据会默认去重一下 去重不能跨分区
建表语句及解释:
create table data(id Uint32,name String,time Datetime)engine=ReplacingMergeTree(create_time)
partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)
//ReplacingMergeTree(create_time) create_time作为版本字段。默认保留最后一条插入
SummingMergeTree
应用场景:不查询明细数据,只关心以维度聚合。非实时聚合,分区合并的时候聚合
create table data(id Uint32,name String,time Datetime)engine=SummingMergeTree(score)
partition by toYYYYMMDD(Datetime) primary key(id) order by (id,name)
//依据order by 的字段作为维度,聚合score 插入数据的时候默认会聚合一次
SQL样例
insert into table_name values
insert into table_name select * from table_name_a
alter table_name delete where column='10' //删除数据
alter table_name update name='zhangsan' where column='10' //修改数据
删除和修改操作都比较重,高性能思路:
更新的时候插入一条新数据,version+1,查询的时候version=max(version)
删除:0表示未删除 1表示删除 查询的时候加一个条件 _sign=0
子查询、with、支持join(但是没法使用缓存)、
if(1,mm,nn) 1=true nultiIf(con_1,then_1,con_2,then_2,con_3,then_3)
group by a,b with rollup=a|a,b|all group by a,b with cube=a|b|all|a,b
group by a,b with totals=a,b|all
alter table table_name add column new_column_name String after col1;
alter table table_name modify column new_column_name String ;
alter table table_name drop column;
集群表
CREATE TABLE on cluster dmp default.userpkg_all (gazj String) ENGINE = Distributed(dmp, default, userpkg, hiveHash(id))
CREATE TABLE on cluster dmp default.userpkg_all (gazj String)
ENGINE = MergeTree partition by toYYYYMMDD(Datetime) order by (id,name) //在每个节点都创建本地表
插入数据的时候:向分布式表插入数据
搬砖多年终不得要领,遂载源码看之望得真经。