OLAP在线联合分析数据库之ClickHouse之视图及表引擎

ck视图

clikhouse拥有普通视图和物化视图俩种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理

普通视图

普通视图： 创建语句 create view [IF NOT EXISTS] [db_name].view_name AS select …

普通视图不会存储任何数据，他只是一层单纯的select查询映射，起着简化查询、明晰语义的作用，对查询性能不会又任何增强。

show tables;
select * from  tb_test2;
-- 为select id,upper(role) upper_role from  tb_test2; 语句创建一个普通视图；
CREATE view tb_test2_view as select id,upper(role) upper_role from  tb_test2;
-- 查询这个视图其实就是代理查询它所代表的查询语句
SELECT * from tb_test2_view;
-- 删除视图
DROP view tb_test2_view ;

物化视图

物化视图：物化视图支持表引擎，数据保存形式由它的表引擎决定

创建物化视图的语句：create materialized view view_name engine= engineName populate as select * from tableName;

物化视图创建好之后，如何源表被写入数据，那么物化视图也会同步更新数据。

populate修饰符决定了物化视图的初始化策略：如果使用的策略为populate，那么在创建物化视图的过程中，会连带源表的已存在的数据一并导入，如同执行了insert select语句

如果策略不是populate，那么创建的物化视图是空的，只会同步此后被写入源表的数据，物化视图目前并不支持同步删除，如果源表删除了数据，物化视图的数据仍有保留

SELECT * FROM  test_export01 te ;
CREATE materialized view test_export_view engine=Log() populate as select * from test_export01 te ;
show CREATE table test_export_view;
select * from test_export_view;

表引擎

LOG引擎家族

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

TinyLog引擎

最简单的表引擎，用于将数据存储在磁盘上，每列都存储在单独的压缩文件中，写入时，数据将追加到文件末尾，该引擎没有并发控制。

• 最简单的引擎
• 没有索引，没有标记块（只有*.bin文件）
• 写是追加写
• 数据是以列式字段文件存储
• 不允许同时读写（即不能使用类似insert into select。。。语句）

-- 建表
CREATE table test_tinylog(
	id UUID,
	name String

)engine=TinyLog();
-- 插入数据
INSERT into test_tinylog values(generateUUIDv4(),'邓怀俊');
--再次插入数据， 验证是否是以块的方式追加还是直接文件末尾追加的形式
INSERT into test_tinylog values(generateUUIDv4(),'如来佛祖');
# 再次插入数据可以看出数据不是以块的方式追加而是使用文件追加，单纯的写数据。
SELECT *
FROM test_tinylog

┌───────────────────────────────────id─┬─name─────┐
│ f4cb79ac-5f29-48f7-ba56-2c146e822fbe │ 邓怀俊   │
│ 985adcff-1375-4b17-9255-10bc608c3815 │ 如来佛祖 │
└──────────────────────────────────────┴──────────┘


# 查看底层数据存储结构可以看出没有标记块，没有索引：
root@bb8399987902:/var/lib/clickhouse/data/test_db/test_tinylog# ls
id.bin  name.bin  sizes.json

Log引擎

Log与TinyLog引擎的不同之处在于，标记的小文件与列式数据文件存在一起，这些标记存在于每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数，这使得可以在多个线程中读取数据，对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取操作和其它写入。Log引擎也不支持索引，同样，如果写入表失败。则该表将会被破坏，并且从该表读取将返回错误，Log引擎适用于临时数据，单次写入的表及测试和演示使用。

特点：

• *.bin 存储每个字段的数据；
• mark.mrk 数据块标记
• 支持多线程处理
• 并发读写

create table test_log(
 id UInt8,
 name String,
 age UInt8
)engine=Log;

-- 插入数据
insert into test_log values(1,'aa',23),(2,'bb',32);
-- 再次插入数据，查看数据是否为单纯的文件尾追加还是以块的方式追加。
insert into test_log values(3,'cc',20);
SELECT * FROM test_log
-- 结果为以块的方式追加，进而证明有标记块支持多线程处理。
┌─id─┬─name─┬─age─┐
│  1 │ aa   │  23 │
│  2 │ bb   │  32 │
└────┴──────┴─────┘
┌─id─┬─name─┬─age─┐
│  3 │ cc   │  20 │
└────┴──────┴─────┘

-- 查看数据底层：
root@bb8399987902:/var/lib/clickhouse/data/test_db/test_log# ll
total 24
drwxr-x---  2 clickhouse clickhouse  112 Jul 28 09:55 ./
drwxr-x--- 16 clickhouse clickhouse 4096 Jul 28 09:55 ../
-rw-r-----  1 clickhouse clickhouse   55 Jul 28 09:55 age.bin
-rw-r-----  1 clickhouse clickhouse   55 Jul 28 09:55 id.bin
-rw-r-----  1 clickhouse clickhouse   96 Jul 28 09:55 __marks.mrk
-rw-r-----  1 clickhouse clickhouse   61 Jul 28 09:55 name.bin
-rw-r-----  1 clickhouse clickhouse  120 Jul 28 09:55 sizes.json

StripeLog引擎

• data.bin 存储数据
• index.mrk 对数据建立索引 （所以效率最高）
• size.json 数据的大小
• 支持并发读写

create table test_stripelog(
 id UInt8,
 name String,
 age UInt8
)engine=StripeLog();

-- 插入数据
insert into test_stripelog values(1,'aa',23),(2,'bb',32);
-- 再次插入数据，查看数据是否为单纯的文件尾追加还是以块的方式追加。
insert into test_stripelog values(3,'cc',20);
SELECT * FROM test_stripelog;
bb8399987902 :) select * from test_stripelog;

SELECT * FROM test_stripelog

┌─id─┬─name─┬─age─┐
│  3 │ cc   │  20 │
└────┴──────┴─────┘
┌─id─┬─name─┬─age─┐
│  1 │ aa   │  23 │
│  2 │ bb   │  32 │
└────┴──────┴─────┘
# 查看底层数据存储：
root@bb8399987902:/var/lib/clickhouse/data/test_db/test_tinylog# cd ../test_stripelog/
root@bb8399987902:/var/lib/clickhouse/data/test_db/test_stripelog# ll
total 16
drwxr-x---  2 clickhouse clickhouse   73 Jul 28 10:13 ./
drwxr-x--- 17 clickhouse clickhouse 4096 Jul 28 10:13 ../
-rw-r-----  1 clickhouse clickhouse  291 Jul 28 10:13 data.bin
-rw-r-----  1 clickhouse clickhouse  150 Jul 28 10:13 index.mrk
-rw-r-----  1 clickhouse clickhouse   69 Jul 28 10:13 sizes.json

Log与StripeLog对比：

1.并发访问数据的锁；
2.insert请求执行过程中，表会被锁定，并且其它的读写数据的请求都会等待直到锁被解除，如果没有写数据的请求，任意数量的读请求都可以并发执行。
3. 并行读取数据
4. 在读取数据时，ck使用多线程，你每个线程处理每个数据块。
5. Log引擎为表中的每一列使用不同的文件，但是StripeLog引擎将所有数据存储在一个文件中，因此StripeLog引擎在操作系统中使用更少的描述符，而Log引擎提供更高的可读性能。
6. TinyLog引擎是三个引擎中最简单的引擎并且提供最少的功能和最低的性能。TinyLog引擎不支持并行读写和并发数据的访问，并将每一列的数据储存在不同的文件中，它比其它俩种支持并行读写的引擎的读写速度更慢，并且使用了和Log引擎同样多的文件描述符，你可以在简单的低负载的情景下使用。

MergeTree Family引擎家族

MergeTree系列的表引擎是ClickHouse的数据存储功能的核心，他们提供了用于弹性和高性能数据检索的大多数功能：列存储，自定义分区，稀疏的主索引， 辅助数据跳过索引等。

基本MergeTree表引擎可以被认为是单节点的clickhouse实例的默认表引擎，因为它在各种实例中通用且实用。

MergeTree引擎

MergeTree在写入一批数据时，数据总以数据片段的形式写入磁盘，且数据片段不可以修改，为了避免片段过多，clickhouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合并成一个新的片段，这种数据片段往复合并的特点，也正常合并树的由来。

语法

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()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
[DELETE|TO DISK ‘xxx’|TO VOLUME ‘xxx’ [, …] ]
[WHERE conditions]
[GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) …]] ] ]
[SETTINGS name=value, …]

--MergeTree
-- 建表
create table test_merge_tree(
	uid UInt8,
	name String,
	birthday Date,
	city String,
	gender String
)engine=MergeTree() 
partition by toYYYYMM(birthday)
primary key uid
-- 一定要有主键和排序字段，主键一定要包含在排序字段中,且在排序字段前面,且主键可以不唯一
order by (uid,birthday);

-- 插入数据
INSERT into test_merge_tree values (1,'zs',toDate(now()),'SH','M'),(2,'ls',toDate(now()),'BJ','M'),(3,'ww','1991-12-16','SH','M');

持续更新，后续用到会继续记录！！！