clickhouse学习笔记(二)MergeTree家族引擎集成引擎函数

最新推荐文章于 2022-12-28 11:40:31 发布

z小丑八怪r

最新推荐文章于 2022-12-28 11:40:31 发布

阅读量999

点赞数

分类专栏： clickhouse

本文链接：https://blog.csdn.net/weixin_46959672/article/details/110674278

版权

clickhouse 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1 MergeTree家族引擎

1.1 MergeTree

create  table  tb_tree1(
id String ,
name String ,
city String
)
engine = MergeTree 
primary key id 
order by id;
插入数据
insert  into  tb_tree1 values('id004','wb','HB'),('id005','DHT','HN'),('id006','ZXX','HLJ');
insert  into  tb_tree1 values('id004','wb','HB'),('id005','DHT','HN'),('id006','ZXX','HLJ');
select * from tb_tree1
┌─id────┬─name─┬─city─┐
│ id004 │ wb   │ HB   │
│ id005 │ DHT  │ HN   │
│ id006 │ ZXX  │ HLJ  │
└───────┴──────┴──────┘
┌─id────┬─name─┬─city─┐
│ id004 │ wb   │ HB   │
│ id005 │ DHT  │ HN   │
│ id006 │ ZXX  │ HLJ  │
└───────┴──────┴──────┘
合并
optimize table tb_tree1 ;
┌─id────┬─name─┬─city─┐
│ id004 │ wb   │ HB   │
│ id004 │ wb   │ HB   │
│ id005 │ DHT  │ HN   │
│ id005 │ DHT  │ HN   │
│ id006 │ ZXX  │ HLJ  │
│ id006 │ ZXX  │ HLJ  │
└───────┴──────┴──────┘
insert  into  tb_tree1 values('id011','wb2','HB'),('id007','DHT2','HN'),('id016','ZXX2','HLJ');
再次合并
optimize table tb_tree1 ;
┌─id────┬─name─┬─city─┐
│ id004 │ wb   │ HB   │
│ id004 │ wb   │ HB   │
│ id005 │ DHT  │ HN   │
│ id005 │ DHT  │ HN   │
│ id006 │ ZXX  │ HLJ  │
│ id006 │ ZXX  │ HLJ  │
│ id007 │ DHT2 │ HN   │
│ id011 │ wb2  │ HB   │
│ id016 │ ZXX2 │ HLJ  │
└───────┴──────┴──────┘
合并完以后数据在磁盘上的存储是
drwxr-x---. 2 clickhouse clickhouse 177 Dec  4 09:25 all_1_1_0
drwxr-x---. 2 clickhouse clickhouse 177 Dec  4 09:28 all_1_2_1   第一次合并生成的文件
drwxr-x---. 2 clickhouse clickhouse 177 Dec  4 09:31 all_1_3_2   第二次合并生成的文件
drwxr-x---. 2 clickhouse clickhouse 177 Dec  4 09:26 all_2_2_0
drwxr-x---. 2 clickhouse clickhouse 177 Dec  4 09:30 all_3_3_0
drwxr-x---. 2 clickhouse clickhouse   6 Dec  4 09:24 detached
-rw-r-----. 1 clickhouse clickhouse   1 Dec  4 09:24 format_version.txt
过段时间以后CK内部自动的会删除合并前的多余的文件夹
进入到某个分区目录下
[root@linux01 all_1_1_0]# ll
total 40
-rw-r-----. 1 clickhouse clickhouse 320 Dec  4 02:26 checksums.txt
-rw-r-----. 1 clickhouse clickhouse  35 Dec  4 02:26 city.bin
-rw-r-----. 1 clickhouse clickhouse  48 Dec  4 02:26 city.mrk2
-rw-r-----. 1 clickhouse clickhouse  77 Dec  4 02:26 columns.txt  --所有的列
-rw-r-----. 1 clickhouse clickhouse   1 Dec  4 02:26 count.txt  --记录数据的条数
-rw-r-----. 1 clickhouse clickhouse  38 Dec  4 02:26 id.bin
-rw-r-----. 1 clickhouse clickhouse  48 Dec  4 02:26 id.mrk2
-rw-r-----. 1 clickhouse clickhouse  37 Dec  4 02:26 name.bin
-rw-r-----. 1 clickhouse clickhouse  48 Dec  4 02:26 name.mrk2
-rw-r-----. 1 clickhouse clickhouse   8 Dec  4 02:26 primary.idx  --保存主键索引
 - *.bin是按列保存数据的文件
 - *.mrk保存块偏移量

1.2 ReplacingMergeTree

create  table  replacing_tree(
id String ,
name String ,
v UInt8
)
engine =  ReplacingMergeTree(v) 
order by id;
insert  into  replacing_tree  values ('a001','zss',10),('a002','lss',10) ;
insert  into  replacing_tree  values ('a001','zss2',30),('a002','lss2',5) ;
┌─id───┬─name─┬──v─┐
│ a001 │ zss2 │ 30 │
│ a002 │ lss2 │  5 │
└──────┴──────┴────┘
┌─id───┬─name─┬──v─┐
│ a001 │ zss  │ 10 │
│ a002 │ lss  │ 10 │
└──────┴──────┴────┘

optimize  table replacing_tree ;
┌─id───┬─name─┬──v─┐
│ a001 │ zss2 │ 30 │
│ a002 │ lss  │ 10 │
└──────┴──────┴────┘

去重主键相同的数据保留v版本大的数据删除操作发生在合并时

create  table  replacing_tree2(
id String ,
name String ,
accTime DateTime
)
engine =  ReplacingMergeTree(accTime)  
order by  id 
;

insert into replacing_tree2 values('a001','zss',now()) ;
insert into replacing_tree2 values('a001','zss2',now()) ;

┌─id───┬─name─┬─────────────accTime─┐
│ a001 │ zss2 │ 2020-12-04 02:37:22 │
└──────┴──────┴─────────────────────┘

1.3 VersionedCollapsingMergeTree 指定删除数据的版本

create table  versioned_collapsing_mergetree(
id UInt8 ,
name String ,
version  UInt8 ,
sign Int8
) 
engine = VersionedCollapsingMergeTree(sign, version)
order  by id ;
insert  into   versioned_collapsing_mergetree2 values(1,'a',1,1),(1,'b',2,1) ;
insert  into   versioned_collapsing_mergetree2 values(1,'a2',3,1) ;
┌─id─┬─name─┬─version─┬─sign─┐
│  1 │ a    │       1 │    1 │
│  1 │ b    │       2 │    1 │
└────┴──────┴─────────┴──────┘
┌─id─┬─name─┬─version─┬─sign─┐
│  1 │ a2   │       3 │    1 │
└────┴──────┴─────────┴──────┘
合并
optimize table versioned_collapsing_mergetree2;
┌─id─┬─name─┬─version─┬─sign─┐
│  1 │ a    │       1 │    1 │
│  1 │ b    │       2 │    1 │
│  1 │ a2   │       3 │    1 │
└────┴──────┴─────────┴──────┘
再次插入数据，删除主键为1版本为2的数据
insert  into   versioned_collapsing_mergetree2 values(1,'a',2,-1) 
再次合并
optimize table versioned_collapsing_mergetree2;
┌─id─┬─name─┬─version─┬─sign─┐
│  1 │ a    │       1 │    1 │
│  1 │ a2   │       3 │    1 │
└────┴──────┴─────────┴──────┘

2 集成引擎

2.1 MySQL 引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

2.1.1 MySQL数据库引擎

create  database  db_ch_mysql engine=MySQL('linux01:3306' , 'hive','root','root');  
注意:  数据库引擎  MySQL中不支持DDL（创建表，删除表等）

2.1.2 MySQL表引擎

MySql中
mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | zss  |
|    2 | lss  |
|    3 | www  |
+------+------+

clickhouse中
linux01 :) create  table  tb_user(
id  Int32 ,
name  String
)
engine=MySQL('linux01:3306','db_doit19','tb_user','root','root') ;

linux01 :) select * from user
┌─id─┬─name─┐
│  1 │ zss  │
│  2 │ lss  │
│  3 │ www  │
└────┴──────┘
可以看大 在MySQL中的user表直接映射到CH中的表user

mysql引擎表 和 ch原生表的关联查询join：
在CH中创建一个原生表
create table user2(
id Int8,
deptno String
)
engine=Log;
insert into user2 values(1,'A'),(2,'B'),(3,'C');

select * 
from
user 
join user2 
on user.id=user2.id

SELECT *
FROM tb_user
INNER JOIN tb_user2 ON tb_user.id = tb_user2.id
┌─id─┬─name─┬─user2.id─┬─deptno─┐
│  1 │ zss  │        1 │ A      │
│  2 │ lss  │        2 │ B      │
│  3 │ www  │        3 │ C      │
└────┴──────┴──────────┴────────┘

2.2 HDFS引擎

在hdfs中的/ch/data/user.tsv文件

1	zss	北京	24
2	lss	北京	21
3	www	上海	27

创建hdfs表引擎
create  table  ch_hdfs(
uid  String ,
name String ,
city  String ,
age UInt8
)
engine = HDFS("hdfs://linux01:8020/ch/data/user.tsv" , "TSV") ;
select * from tb_ch_hdfs;
┌─uid─┬─name─┬─city─┬─age─┐
│ 1   │ zss  │ 北京 │  24 │
│ 2   │ lss  │ 北京 │  21 │
│ 3   │ www  │ 上海 │  27 │
└─────┴──────┴──────┴─────┘

2.3 引擎表的关联查询

mysql引擎表 和  hdfs引擎表 和  ch原生表 关联查询  join  join  

select * 
from 
	(select user.id,user.name,user2.deptno 
	FROM user 
	join user2 
	on user.id=user2.id) t 
join tb_ch_hdfs 
on t.id=cast(tb_ch_hdfs.uid as Int8); --cast()函数 转换数据类型 将uid转换为int类型
┌─id─┬─name─┬─deptno─┬─uid─┬─tb_ch_hdfs.name─┬─city─┬─age─┐
│  1 │ zss  │ A      │ 1   │ zss             │ 北京 │  24 │
│  2 │ lss  │ B      │ 2   │ lss             │ 北京 │  21 │
│  3 │ www  │ C      │ 3   │ www             │ 上海 │  27 │
└────┴──────┴────────┴─────┴─────────────────┴──────┴─────┘

3 函数

now()  当前时间
currentDatabase()  当前使用的数据库
cast('12'  as  UInt8)  
/cast(12, 'String')    转换数据类型

z小丑八怪r

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
clickhouse学习笔记(二)MergeTree家族引擎集成引擎函数

MergeTreecreate table tb_tree1(id String ,name String ,city String)engine = MergeTree primary key id order by id;插入数据insert into tb_tree1 values('id004','wb','HB'),('id005','DHT','HN'),('id006','ZXX','HLJ');insert into tb_tree1 values('id0
复制链接

扫一扫