ClickHouse 之二 其他引擎(Null,File,Log,mergetree,TTL)

ClickHouse 其他系列引擎分析

---

TTL

（1）、创建带TTL的表：
DROP TABLE example_table;
CREATE TABLE example_table 
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MINUTE,
    b String TTL d + INTERVAL 1 MINUTE,
    c String
)
ENGINE = MergeTree
ORDER BY d;

（2）、插入数据：
insert into example_table values (now(), 1, 'value1', 'ccc1');
insert into example_table values(now(), 2, 'value2', 'ccc2');



给表的列添加TTL：

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;
修改列的TTL：
ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 MONTH;
	
	
2. 表级TTL示例

（1）、创建表

drop table example_table ;
CREATE TABLE example_table 
(
   d DateTime,
    a Int,
    b String,
    c String
)
ENGINE = MergeTree
ORDER BY d
TTL d + INTERVAL 1 MINUTE DELETE;


（2）、插入数据

insert into example_table values (now(), 1, 'value1', 'ccc1');
insert into example_table values(now(), 2, 'value2', 'ccc2');

等待1分钟后， 执行optimize操作。
optimize table example_table

Log引擎

啥都不说,直接上代码

插入三种log系列的不同引擎
（1）建表

DROP TABLE table_tinylog;
CREATE TABLE table_tinylog(
    userid UInt64,
    pageviews UInt8,
    duration UInt8
)
ENGINE = TinyLog;

DROP TABLE table_log;
CREATE TABLE table_log(
    userid UInt64,
    pageviews UInt8,
    duration UInt8
)
ENGINE = Log;

DROP TABLE table_stripelog;
CREATE TABLE table_stripelog(
    userid UInt64,
    pageviews UInt8,
    duration UInt8
)
ENGINE = StripeLog;

（2）插入数据

三张表插入同样的数据：
INSERT INTO table_tinylog VALUES (4324182021466249494, 1, 146),(4324182021466249414, 9, 156);
INSERT INTO table_tinylog VALUES (4324182021466249495, 2, 147),(4324182021466249424, 8, 157);
INSERT INTO table_tinylog VALUES (4324182021466249496, 3, 148),(4324182021466249434, 7, 158);
INSERT INTO table_tinylog VALUES (4324182021466249497, 4, 141),(4324182021466249444, 6, 151);
INSERT INTO table_tinylog VALUES (4324182021466249498, 5, 142),(4324182021466249454, 5, 152);
INSERT INTO table_tinylog VALUES (4324182021466249499, 6, 143),(4324182021466249464, 4, 153);
​
INSERT INTO table_log VALUES (4324182021466249494, 1, 146),(4324182021466249414, 9, 156);
INSERT INTO table_log VALUES (4324182021466249495, 2, 147),(4324182021466249424, 8, 157);
INSERT INTO table_log VALUES (4324182021466249496, 3, 148),(4324182021466249434, 7, 158);
INSERT INTO table_log VALUES (4324182021466249497, 4, 141),(4324182021466249444, 6, 151);
INSERT INTO table_log VALUES (4324182021466249498, 5, 142),(4324182021466249454, 5, 152);
INSERT INTO table_log VALUES (4324182021466249499, 6, 143),(4324182021466249464, 4, 153);
​
INSERT INTO table_stripelog VALUES (4324182021466249494, 1, 146),(4324182021466249414, 9, 156);
INSERT INTO table_stripelog VALUES (4324182021466249495, 2, 147),(4324182021466249424, 8, 157);
INSERT INTO table_stripelog VALUES (4324182021466249496, 3, 148),(4324182021466249434, 7, 158);
INSERT INTO table_stripelog VALUES (4324182021466249497, 4, 141),(4324182021466249444, 6, 151);
INSERT INTO table_stripelog VALUES (4324182021466249498, 5, 142),(4324182021466249454, 5, 152);
INSERT INTO table_stripelog VALUES (4324182021466249499, 6, 143),(4324182021466249464, 4, 153);

（3）区别请详查看文件系统的数据目录

table_log表（基于Log引擎）

ll /var/lib/clickhouse/data/default/table_log/

总用量 20
-rw-r----- 1 clickhouse clickhouse 168 3月 14 01:38 duration.bin
-rw-r----- 1 clickhouse clickhouse 288 3月 14 01:38 __marks.mrk
-rw-r----- 1 clickhouse clickhouse 168 3月 14 01:38 pageviews.bin
-rw-r----- 1 clickhouse clickhouse 138 3月 14 01:38 sizes.json
-rw-r----- 1 clickhouse clickhouse 258 3月 14 01:38 userid.bin

table_stripelog表（基于StripeLog引擎）

ll /var/lib/clickhouse/data/default/table_stripelog/

总用量 12
-rw-r----- 1 clickhouse clickhouse 1044 3月 14 01:38 data.bin
-rw-r----- 1 clickhouse clickhouse 536 3月 14 01:38 index.mrk
-rw-r----- 1 clickhouse clickhouse 70 3月 14 01:38 sizes.json
​

table_tinylog表（基于TinyLog引擎）

 ll /var/lib/clickhouse/data/default/table_tinylog/

总用量 16
-rw-r----- 1 clickhouse clickhouse 168 3月 14 01:37 duration.bin
-rw-r----- 1 clickhouse clickhouse 168 3月 14 01:37 pageviews.bin
-rw-r----- 1 clickhouse clickhouse 107 3月 14 01:37 sizes.json
-rw-r----- 1 clickhouse clickhouse 258 3月 14 01:37 userid.bin

Null 引擎

请注意,null引擎直接插无效,构建视图有效

create database test;
use test;
DROP TABLE IF EXISTS src_null;
DROP TABLE IF EXISTS m_view_sum;

// 创建Null引擎的表
CREATE TABLE src (id String, value UInt32) ENGINE = Null;
// 创建物化视图
CREATE MATERIALIZED VIEW m_view_sum ENGINE = SummingMergeTree() order by id AS SELECT id, sum(value) as value FROM src group by id;

// 插入数据
insert into src values('id001', 1),('id002', 33),('id003', 36);
insert into src values('id001', 2),('id002', 17),('id003', 24);

// 查看数据
select * from m_view_sum;
optimize table m_view_sum;
select * from m_view_sum;

MergeTree

这个案例演示了merge 引擎表相当于对于当前数据库中匹配了一个类似于^WatchLog的正则表达式.

1. 创建表
DROP TABLE WatchLog_old;
CREATE TABLE WatchLog_old(date Date, UserId Int64, EventType String, Cnt UInt64)
ENGINE=MergeTree PARTITION BY date ORDER BY (UserId, EventType);

INSERT INTO WatchLog_old VALUES ('2018-01-01', 1, 'hit', 3);

DROP TABLE WatchLog_new;
CREATE TABLE WatchLog_new(date Date, UserId Int64, EventType String, Cnt UInt64)
ENGINE=MergeTree PARTITION BY date ORDER BY (UserId, EventType);

INSERT INTO WatchLog_new VALUES ('2018-01-02', 2, 'hit', 3);


2.创建Merge引擎表：
DROP TABLE WatchLog;
CREATE TABLE WatchLog as WatchLog_old ENGINE=Merge(currentDatabase(), '^WatchLog');

查看数据：
SELECT * FROM WatchLog;
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │      2 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │      1 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘


虚拟列：
SELECT _table,UserId FROM WatchLog;

File

案例：
1. 创建File引擎的表
CREATE TABLE file_engine_table (name String, value UInt32) ENGINE=File(CSV)

默认情况下， ClickHouse将创建文件夹：/var/lib/clickhouse/data/default/file_engine_table。
2. 插入数据
insert into file_engine_table values('one', 1);
insert into file_engine_table values('two', 2);


3. 查询数据
SELECT * FROM file_engine_table
┌─name─┬─value─┐
│ one  │     1 │
│ two  │     2 │
└──────┴───────┘

数据默认写入文件：/var/lib/clickhouse/data/default/file_engine_table/data.TabSeparated。

4. 手工修改磁盘文件，新增记录：
"three",3

5. 查询数据
SELECT * FROM file_engine_table