LOAP引擎：clickhouse02： 引擎及sql语法

**clickhouse 的sql语句要求很严格，区分大小写

建表数据类型：

Int8 .... Int256
UInt8 UInt256
Float32   Float64 
String 
Date 
DateTime

常用引擎

1.日志引擎

具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。

1.Log引擎（数据分块）

create table  `tb_user`(
id String ,
name String ,
age UInt8 ,
birthday  Date
)  engine = Log;

insert into tb_user values('uid0002','wanggang',21,'1999-03-08');
insert into  tb_user  select * from tb_user ; -----此处会对表进行损坏

 

数据存储在本地的默认位置 
1) 数据是以列为文件存储
2) 插入数据的时候是想文件后面追加数据
3) xx.bin  列数据
4) size.json每个文件的大小
5) 不支持索引
6) 读写分离 
 

/var/lib/clickhouse/data/default/tb_user

-rw-r-----. 1 clickhouse clickhouse  54 Dec  3 21:58 age.bin
-rw-r-----. 1 clickhouse clickhouse  56 Dec  3 21:58 birthday.bin
-rw-r-----. 1 clickhouse clickhouse  68 Dec  3 21:58 id.bin
-rw-r-----. 1 clickhouse clickhouse  64 Dec  3 21:58 __marks.mrk  数据的块偏移量
-rw-r-----. 1 clickhouse clickhouse  69 Dec  3 21:58 name.bin  
-rw-r-----. 1 clickhouse clickhouse 151 Dec  3 21:58 sizes.json  -- 长度 

**合并块

optimize table tb_name;

2.TinyLog引擎（数据不分块）

每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。

该引擎没有并发控制

 - 只支持并发读

- 如果同时从表中读取和写入数据，则读取操作将抛出异常；

- 如果同时写入多个查询中的表，则数据将被破坏

2.MergeTree家族引擎

1.MergeTree引擎

基本格式

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2) 
ENGINE = MergeTree()   ----设置引擎为MergeTree
ORDER BY expr          ----必写项，不写报错，排序，没有写主键的话排序字段同时也代表主键字段
[PARTITION BY expr]    ----分区字段
[PRIMARY KEY expr]     ----主键字段
[SAMPLE BY expr]       ----取样字段
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

建表语句

create table tb_merge_user(
id Int8,
name String
)
engine=MergeTree()
order by id;

insert into tb_merge_user values(1,'zs'),(2,'ls');
insert into tb_merge_user values(3,'ww');

┌─id─┬─name─┐
│  3 │ ww   │
└────┴──────┘
┌─id─┬─name─┐
│  1 │ zs   │
│  2 │ ls   │
└────┴──────┘

合并后会多出一个合并文件

过段时间以后CK内部自动的会删除合并前的多余的文件夹

2.ReplacingMergeTree

这个引擎是在 MergeTree 的基础上，添加了“处理重复数据”的功能，该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。数据的去重只会在合并的过程中出现。

建表语句：

create table tb_replace_user(
id UInt32,
name String,
version UInt32
)
engine=ReplacingMergeTree(version) ---删除主键相同的行，相同主键下按版本删除，留下高版本，删除低版本
order by id;

insert into tb_replace_user values(1,'ll',10),(2,'vv',20);
insert into tb_replace_user values(3,'nn',20);
insert into tb_replace_user values(3,'nn',40);

合并后：

┌─id─┬─name─┬─version─┐
│  1     │         ll  │              10 │
│  2     │ vv        │              20 │
│  3     │ nn        │             40 │
└────┴──────┴─────

3.CollapsingMergeTree

CollapsingMergeTree来消除ReplacingMergeTree的限制(只删除小版本字段,只保留最大版本数据)。该引擎要求在建表语句中指定一个标记列Sign，后台Compaction时会将主键相同、Sign相反的行进行折叠，也即删除。

建表语句：

create table tb_replac_tree(
id UInt8,
name String,
version UInt8
)
engine=ReplacingMergeTree(version)
order by id;

insert into tb_replac_tree values(1,'yyy',10),(2,'jjj',50);

insert into tb_replac_tree values(1,'y',20),(2,'j',20)(3,'w',20);

┌─id─┬─name─┬─version─┐
│  1 │ y    │      20 │
│  2 │ jjj  │      50 │
│  3 │ w    │      20 │
└────┴──────┴─────────┘

注意:

CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。只有保证老的状态行在在取消行的上面, 新的状态行在取消行的下面! 但是多线程无法保证写的顺序!

4.VersionedCollapsingMergeTree

解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。

建表语句：

create table tb_versioned_collapsing_mergetree(
id UInt8,
name String,
version UInt8,
flag Int8
)
engine=VersionedCollapsingMergeTree(flag,version)
order by id;

insert into tb_versioned_collapsing_mergetree values(
1,'a1',1,1)
(1,'a4',1,1)
(1,'a5',1,1
);
insert into tb_versioned_collapsing_mergetree values(
1,'a6',1,-1
);

只删除离标记最近的版本
┌─id─┬─name─┬─version─┬─flag─┐
│  1 │ a1   │       1 │    1 │
│  1 │ a4   │       1 │    1 │
└────┴──────┴─────────┴──────┘

集成引擎

1.本地引擎

默认的引擎, 默认操作本地或者是指定集群的数据

建表语句：

create table tb_user2(
id UInt32,
name String
)
engine=Log;

insert into tb_user2 values(3,'jj'),(4,'hh');
insert into tb_user2 values(10,'jj'),(20,'hh');

2.MySQL引擎

1.MySQL数据库引擎

不允许DDL操作  

建表语句：

create database db_ch_doit19 engine=MySQL('linux01:3306','hive','root','root');

----参数一：MySQL地址 参数二：MySQL数据库名字  参数三：用户名  参数四：密码
----参数二 ： 必须保证MySQL中有的数据库

把hive集成到ch中，hive有的表在ch中都可以看得到

2.MySQL表引擎

建表语句：

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

--参数一：MySQL地址 参数二：数据库名字 参数三：表名字 参数四：用户名 参数五：密码

--得到的表里的数据是MySQL表中的数据

3.HDFS表引擎

建表语句：

create table tb_user3(
id Int32,
name String
)
engine = HDFS('hdfs://linux01:8020/ch/data/user3.tsv','TSV');

参数一：hdfs地址（具体到文件的名字）  参数二：TSV（以制表符Tab间隔）或者CSV（以逗号为间隔） 

4.三者联合查询

本地引擎、MySQL引擎及HDFS引擎可以两两联合join

建表语句：

select 
tb_user.id,
tb_user.name,
tb_user2.name
from
tb_user
join 
tb_user2
on 
tb_user.id=cast(tb_user2.id,'Int32');  

--格式转换函数：cast(x as T)将x的格式转化为T格式
eg: 1.   cast('15' as UInt8)
    2.   cast('15',UInt8)