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

喜欢ヅ旅行 2022-09-01 09:11 269阅读 0赞

### ClickHouse之试图及表引擎学习 ###

*  ck视图
 *   *  普通视图
     *  物化视图
 *  表引擎
 *   *  LOG引擎家族
     *   *  TinyLog引擎
         *  Log引擎
         *  StripeLog引擎
     *  MergeTree Family引擎家族
     *   *  MergeTree引擎

# 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');

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