ClickHOuse笔记

bin/ ===> /usr/bin/

conf/ ===> /etc/clickhouse-server/

lib/ ===> /var/lib/clickhouse

log/ ====> /var/log/clickhouse

二、建表语句

create table t_order_mt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
)engine=MergeTree
partition by to YYYYMMDD(create_time) --分区
primary key(id) -- 主键
order by(id,sku_id); --排序

insert into t_order_mt values
(102,'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-02 12:00:00'),
(102,'sku_002',600.00,'2020-06-01 12:00:00');

三、表引擎

4.4 MergeTree

4.4.1 partition by 分区(可选)

》作用

分区的目的主要是降低扫描的范围，优化查询速度

》如果不填

只会使用一个分区。

》分区目录

MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那些这些就会保存到不同的分区目录中。

0. 并行
1. 分区后，面对涉及跨分区的查询统计，ClickHouse会以分区为单位并行处理。
2. 2.数据写入与分区合并
3. 
   
4. 4、文件的作用
5. bin文件:数据文件
6. mrk文件：标记文件
7. 标记文件在idx索引文件和bin数据文件之间起到了桥梁作用。
8. 以mrk2结尾的文件，表示该表启用了自适应索引间隔。
9. primary.idx文件:主键索引文件，用于加快查询效率。
10. minmax_create_time.idx:分区键的最大小值。
11. checksums.txt:校验文件,用于校验各个文件的正确性。存放各个文件的size以及hash值。
12. 5、分区 /var/lib/clickhouse/data/default/t_order_mt
13. 日期类分区键
14. 分区键为日期类型，或者可以转化成日期类型。
15. 其他类型分区键
16. String、Float类型等，通过128位的Hash算法取其Hash值作为分区ID。

6、手动合并分区命令：optimize table t_order_mt final；

按照分区合并表:optimize table t_order_mt partition '20200621' final;

二级索引

create table t_order_mt2(
id UInt32,
sku_id String,
total_amount decimal(16,2),
create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5
)engine = MergeTree
partition by toYYYYMMDD(create_time)
primary key(id)
order by (id,sku_id)
其中GRANULARITY N是设定二级索引对于一级索引粒度的粒度。

1、学习clickhouse数据类型

2、了解clickhouse表引擎

3、MergeTree引擎的了解学习主键和order by 还有分区的学习

列级别TTL

创建测试表

create table t_order_mt3(
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_mt3 values
(106,'sku_001',1000.00,'2022-10-12 11:10:00'),
(107,'sku_002',2000.00,'2022-10-12 11:20:00'),
(108,'sku_003',3000.00,'2022-10-12 11:30:00'),
(110,'sku_005',600.00,'2022-10-12 11:53:00');

alter table t_order_mt3 MODIFY TTL create_time+INTERVAL 10 SECOND;

create table t_order_rmt(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
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,'2022-10-12 11:10:00'),
(102,'sku_002',2000.00,'2022-10-12 11:20:00'),
(104,'sku_004',3000.00,'2022-10-12 11:30:00'),
(107,'sku_005',600.00,'2022-10-12 11:53:00'),
(108,'sku_005',600.00,'2022-10-12 11:53:00'),
(103,'sku_005',600.00,'2022-10-12 11:53:00'),
(1010,'sku_005',600.00,'2022-10-12 11:53:00'),
(106,'sku_005',600.00,'2022-10-12 11:53:00'),
(109,'sku_005',600.00,'2022-10-12 11:53:00'),
(108,'sku_005',600.00,'2022-10-12 11:53:00');

--- SummingMergeTree引擎
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,'2022-10-12 12:00:00'),
(101,'sku_002',2000.00,'2022-10-12 11:00:00'),
(101,'sku_004',2500.00,'2022-10-12 12:00:00'),
(101,'sku_002',2000.00,'2022-10-12 13:00:00'),
(101,'sku_002',12000.00,'2022-10-12 13:00:00'),
(101,'sku_002',600.00,'2022-10-12 12:00:00');

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

insert into t_order_mt values
(101,'sku_001',1000.00,'2022-10-12 15:00:00'),
(101,'sku_002',1000.00,'2022-10-12 15:00:00'),
(103,'sku_004',1000.00,'2022-10-12 15:00:00'),
(104,'sku_002',1000.00,'2022-10-12 15:00:00'),
(105,'sku_003',1000.00,'2022-10-12 15:00:00'),
(106,'sku_001',1000.00,'2022-10-12 15:00:00'),
(107,'sku_002',1000.00,'2022-10-12 15:00:00'),
(108,'sku_004',1000.00,'2022-10-12 15:00:00'),
(109,'sku_002',1000.00,'2022-10-12 15:00:00'),
(110,'sku_003',1000.00,'2022-10-12 15:00:00'),
(111,'sku_005',1000.00,'2022-10-12 15:00:00');

---新增字段
alter table tableName add column newcolname String after col1;
-- 修改字段类型
alter table tableName modify column newcolname String;
-- 删除字段
alter table tableName drop column newcolname;
-- 导出数据
clickhouse-client  -- query "select * from t_order_mt where create_time='2022-10-12 12:00:00'" --fomrmat C5WrthNames>/opt/module/data/rs1.csv