13.Merge引擎
13.1.Merge引擎
13.2.案例
14.File引擎
14.1.File引擎
14.2.案例
15.External Data引擎
15.1.External Data引擎
16.Null Engine
16.1.Null Engine
16.2.案例
17.URL引擎
17.1.URL引擎
18.其他引擎
18.1.其他引擎:Memory、Set、Buffer
18.2.案例
Memory引擎
Set引擎
Buffer引擎
13.Merge引擎
13.1.Merge引擎
根据匹配表名的正则表达式,从任意数量的表中同时读取数据。
Merge引擎的表本身不存储数据。
读取是自动并行化的。
读取时按需使用索引。
指定表引擎:
ENGINE = Merge(dbname, table_reg_expression)
参数: 数据库名称、匹配表名的正则表达式
示例: Merge(hits,’^WatchLog’)
从虚拟列_table获取读取数据的表名称。
13.2.案例
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');
3.查看数据
xxxx2 :) select * from WatchLog_new;
SELECT *
FROM WatchLog_new
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │ 2 │ hit │ 3 │
└────────────┴────────┴───────────┴─────┘
1 rows in set. Elapsed: 0.007 sec.
xxxx2 :) select * from WatchLog_old;
SELECT *
FROM WatchLog_old
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │ 1 │ hit │ 3 │
└────────────┴────────┴───────────┴─────┘
1 rows in set. Elapsed: 0.005 sec.
xxxx2 :) select * from WatchLog;
SELECT *
FROM WatchLog
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │ 2 │ hit │ 3 │
└────────────┴────────┴───────────┴─────┘
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │ 1 │ hit │ 3 │
└────────────┴────────┴───────────┴─────┘
2 rows in set. Elapsed: 0.007 sec.
xxxx2 :)
4.虚拟列
xxxx2 :) select _table,UserId FROM WatchLog;
SELECT
_table,
UserId
FROM WatchLog
┌─_table───────┬─UserId─┐
│ WatchLog_new │ 2 │
└──────────────┴────────┘
┌─_table───────┬─UserId─┐
│ WatchLog_old │ 1 │
└──────────────┴────────┘
2 rows in set. Elapsed: 0.006 sec.
xxxx2 :)
14.File引擎
14.1.File引擎
File表引擎按照支持格式(TabSeparated、CSV等),将数据保存文件中。
使用场景:
- 数据从ClickHouse导出到文件。
- 将数据从一种格式转换为另一种格式。
- 通过编辑磁盘上的文件更新ClickHouse中的数据。
指定表引擎:
ENGINE = File(Format)
Format参数指定了文件格式。
ClickHouse不支持为File引擎指定文件系统路径。
当使用File(Format)创建表时,它会在该文件夹中创建空子目录。
当数据写入该表时,它将数据写入子目录下的文件data.Format文件中。
14.2.案例
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;
数据默认写入文件:
/var/lib/clickhouse/data/default/file_engine_table/data.CSV。
4.手动修改磁盘文件,新增记录:
[root@xxxx2 ~]# cd /var/lib/clickhouse/data/default/file_engine_table
[root@xxxx2 file_engine_table]# ls
data.CSV
[root@xxxx2 file_engine_table]# vim data.CSV,最终有如下结果:
"one",1
"two",2
"two",3
5.查询数据
xxxx2 :) select * from file_engine_table;
SELECT *
FROM file_engine_table
┌─name─┬─value─┐
│ one │ 1 │
│ two │ 2 │
│ two │ 3 │
└──────┴───────┘
3 rows in set. Elapsed: 0.005 sec.
xxxx2 :)
15.External Data引擎
15.1.External Data引擎
将外部数据发送至ClickHouse的server。数据存放在临时表中,用于查询(例如IN操作)。
外部数据可以通过非交互式的命令行客户端或HTTP接口上传。
如果需要对大量外部数据运行多个查询,最好提前将数据上传到数据库,而不要使用此功能。
命令行客户端格式:–external --file=… [–name=…] [–format=…] [–types=…|–structure=…]
–external : 标记外部数据的开始。
–file : 文件的路径或者”-”。”-”表示stdin,只能从stdin检索个表。
(以下选项可选)
–name : 表的名称,如果省略,使用默认名称_data。
–format :文件中数据的格式。默认值:TabSeparated。
–types和–structure 这两个参数只需要指定其中之一。
–types : 逗号分隔的列分类的列表。例如:UInt64,String,URL String。定义列名称和类型。
HTTP接口:外部数据以multipart/form-data格式传递。每个表作为单独的文件传输。表名取自文 件名。“query_string"传递参数"name_format”、“name_types"和"name_structure”,其中"name" 是表名,默认为文件名。
16.Null Engine
16.1.Null Engine
写入Null引擎表时,数据被忽略。从Null引擎的表读取时,响应为空。但是,可以在Null表上创建实例化视图。因此,写入表的数据将最终出现在视图中。
指定表引擎:
ENGINE = File(Format)
16.2.案例
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;
xxxx2 :) select * from m_view_sum;
SELECT *
FROM m_view_sum
┌─id────┬─value─┐
│ id001 │ 2 │
│ id002 │ 17 │
│ id003 │ 24 │
└───────┴───────┘
┌─id────┬─value─┐
│ id001 │ 1 │
│ id002 │ 33 │
│ id003 │ 36 │
└───────┴───────┘
6 rows in set. Elapsed: 0.012 sec.
xxxx2 :) select * from m_view_sum;
SELECT *
FROM m_view_sum
┌─id────┬─value─┐
│ id001 │ 2 │
│ id002 │ 17 │
│ id003 │ 24 │
└───────┴───────┘
┌─id────┬─value─┐
│ id001 │ 1 │
│ id002 │ 33 │
│ id003 │ 36 │
└───────┴───────┘
6 rows in set. Elapsed: 0.012 sec.
xxxx2 :) optimize table m_view_sum;
OPTIMIZE TABLE m_view_sum
Ok.
0 rows in set. Elapsed: 0.005 sec.
xxxx2 :) select * from m_view_sum;
SELECT *
FROM m_view_sum
┌─id────┬─value─┐
│ id001 │ 3 │
│ id002 │ 50 │
│ id003 │ 60 │
└───────┴───────┘
3 rows in set. Elapsed: 0.005 sec.
xxxx2 :)
17.URL引擎
17.1.URL引擎
管理远程HTTP/HTTPS服务器上的数据,此引擎类似于File引擎。
可以进行并行读写。
指定表引擎:
ENGINE=URL(URL, Format) URL必须符合统一资源定位符的结构。指定的URL指向使用HTTP或HTTPS的服务器。 Format为ClickHouse支持的格式。 INSERT和SELECT操作分别转换为POST和GET请求,为了处理POST请求,远程服务器必须支持分块 传输编码。 可以使用max_http_get_redirects设置限制HTTP GET重定向跃点的最大数量。
18.其他引擎
18.1.其他引擎:Memory、Set、Buffer
Memory引擎 :数据以未压缩的形式存储在RAM中。Server重启,数据将从表中消失。读和写操作不会互相阻塞。不支持索引。读取时并行化的。该引擎仅用于测试。
Set:Set引擎的数据使用位于RAM中, 它专用于IN运算符的右侧。检索数据的唯一办法是在IN运算符的右半部分使用它。启动server时,Set表引擎的数据将加载到RAM。
Buffer : 当写入Buffer引擎的表时,数据先写入内存,然后周期性地刷到另外一张表(可称为目标表)。读取操作是同时从缓冲区和另外一张表同时读取数据。Buffer引擎限制很多,在极少情况下才会使用。
18.2.案例
1.Memory引擎
drop table memory_demo;
create table memory_demo (id Int8) ENGINE=Memory;
insert into memory_demo values(1);
xxxx2 :) select * from memory_demo;
SELECT *
FROM memory_demo
┌─id─┐
│ 1 │
└────┘
1 rows in set. Elapsed: 0.007 sec.
xxxx2 :)
2.Set引擎
//创建Set引擎的表
drop table set_demo;
create table set_demo (id Int8) ENGINE=Set;
//插入数据
insert into set_demo values(1);
insert into set_demo values(2);
insert into set_demo values(1);
3.Buffer引擎
示例:
Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)
引擎参数:
database : 数据库名称。也可以使用一个返回字符串的常量表达式,例如:currentDatabase()。
table : 数据刷新的目标表。
num_layers : 并行的层数。在物理上, 该表将表示为num_layers个独立的缓冲区。
min_time, max_time, min_rows, max_rows, min_bytes, and max_bytes :从缓冲区刷新数据的条件。如果满足所有min条件或满足至少一个max条件,则从缓冲区刷新数据并将其写入目标表。
min_time,max_time : 从第一次写入缓冲区起以秒为单位的时间条件。
min_rows,max_rows : 缓冲区行数的条件。
min_bytes,max_bytes : 缓冲区字节数的条件。
A.创建目标表
drop table t_target;
create table t_target(id String, name String) ENGINE=MergeTree order by id;
B.创建buffer表
drop table t_buffer;
CREATE TABLE t_buffer AS t_target ENGINE = Buffer(currentDatabase(), t_target, 16, 10, 100, 5, 1000000, 10000000, 100000000);
说明,最少等待10秒,最多等待100秒。上面的各各参数的含义可以从上面Buffer定义中找到。
C: 往buffer表插入数据
insert into t_buffer values('a', 'aaa');
D: 查看表数据
select * from t_buffer;
select * from t_target;
结果显示:
刚插入数据后查询到的结果是:
xxxx2 :) select * from t_buffer;
SELECT *
FROM t_buffer
┌─id─┬─name─┐
│ a │ aaa │
└────┴──────┘
1 rows in set. Elapsed: 0.006 sec.
xxxx2 :) select * from t_target;
SELECT *
FROM t_target
Ok.
0 rows in set. Elapsed: 0.004 sec.
xxxx2 :)
等一段时间之后,显示的结果是:
xxxx2 :) select * from t_buffer;
SELECT *
FROM t_buffer
┌─id─┬─name─┐
│ a │ aaa │
└────┴──────┘
1 rows in set. Elapsed: 0.007 sec.
xxxx2 :) select * from t_target;
SELECT *
FROM t_target
┌─id─┬─name─┐
│ a │ aaa │
└────┴──────┘
1 rows in set. Elapsed: 0.008 sec.
xxxx2 :)