Clickhouse第六讲-视图

ClickHouse 中视图分为普通视图和物化视图，两者区别如图所示

普通视图

普通视图不存储数据，它只是一层 select 查询映射，类似于表的别名或者同义词，能简化查询，对原有表的查询性能没有增强的作用，具体性能依赖视图定义的语句，当从视 图中查询时，视图只是替换了映射的查询语句。普通视图当基表删除后不可用。

创建普通视图语法：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER]

AS SELECT ...

例子：

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

#向表 personinfo 中插入如下数据：

node1 :) insert into personinfo values (1,'张三',18,'2021-06-01');

node1 :) insert into personinfo values (2,'李四',19,'2021-06-02');

node1 :) insert into personinfo values (3,'王五',20,'2021-06-03');

node1 :) insert into personinfo values (4,'马六',21,'2021-06-04');

node1 :) insert into personinfo values (5,'田七',22,'2021-06-05');

#创建视图 person_view 映射查询子句

 node1 :) create view person_view as select name,birthday from personinfo;  

物化视图

 物化视图是查询结果集的一份持久化存储，所以它与普通视图完全不同，而非常趋近于表。”查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。

物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新,POPULATE 关键 字决定了物化视图的更新策略,若有 POPULATE 则在创建视图的过程会将源表已经存在的 数据一并导入，类似于 create table ... as,若无 POPULATE 则物化视图在创建之后 没有数据，只会在创建只有同步之后写入源表的数据，clickhouse 官方并不推荐使用 populated，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。 物化视图是种特殊的数据表，创建时需要指定引擎，可以用 show tables 查看。另外，物化视图不支持 alter 操作。

产生物化视图的过程就叫做“物化”（materialization），广义地讲，物化视图是 数据库中的预计算逻辑+显式缓存，典型的空间换时间思路，所以用得好的话，它可以避免对基础表的频繁查询并复用结果，从而显著提升查询的性能。

物化视图创建语法

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

#在库newdb中创建物化视图

create materialized view t_view engine=Log as select * from personinfo;

show tables;

insert into personinfo values(1,'张三',18,'2021-06-01');

insert into personinfo values(2,'李四,18,'2021-06-01');

insert into personinfo values(3,'王五',18,'2021-06-01');

insert into personinfo values(4,'马六',18,'2021-06-01');

创建无话视图

create materialized view t_view2 engine=Log as  select count(name) as cnt from personinfo

当 创 建 好 物 化 视 图 t_view1 时 ， 可 以 进 入 到/var/lib/clickhouse/data/newdb 目录下看到%2Einner%2Et_view1 目录，当物化视图中同步基表数据时，目录中有对应的列文件和元数据记录文件，与普通创建表一样，
有目录结构。物化视图可能存在的 bug(待验证)：clickhouse 物化视图 根据 tab1 表的 a，b 分
组 对 c 列 sum 操作， 得到的物化视图数据可能不是期望的结果，物化视图中 a，b 两列
的记录可能 a1，b1/a1，b2 两条数据。可能只显示 a1,b1。

update更新

由于clickhouse针对的是OLAP业务分析，update操作在clickhouse中不会经常使用，这种更新效率低：

alter table dbname table update column1 = expr1 [,....] where filter_expr

#创建表 t_update,使用 MergeTree 引擎

create table t_update (id UInt8,name String,age UInt8) engine = MergeTree() order by id ;

insert into t_update values (1,'张三',18),(2,'李四',19)

alter table t_update update age = 22 where name = '张三'

delete

ClickHouse 针对的是 OLAP 业务分析，Delete 操作与 Update 操作一样在 ClickHouse 中不会经常使用。这种删除效率低下

alter table db. table on cluster cluster delete where filter_expr

#创建表 t_delete,使用 MergeTree 引擎

create table t_delete (id UInt8,name String,age UInt8) engine = MergeTree() order by id ;

insert into t_update values (1,'张三',18),(2,'李四',19)

alter table t_delete delete where name = '张三';

向表中导入到处数据

ClickHouse 中 支 持 多 种 数 据 格 式 数 据 导 入 和 导 出 ， 支 持 格 式 有 ORC,Parquet,Avro,Protobuf,xml,json,csv 等

#创建表 t_csv ，执行引擎为 MergeTree

create table t_csv (id UInt8,name String,age UInt8) engine = MergeTree order by id;

#导入数据，在 ClickHouse-client 中执行导入数据命令

clickhouse-client --format_csv_delimiter="," --query="INSERT INTO newdb.t_csv FORMAT CSV" < /root/csvdata

#进入 ClickHouse 客户端查看表 t_csv 中的数据

导出数据

#导出数据，在 ClickHouse-client 中执行命令，将数据导入到 result 文件中

clickhouse-client --format_csv_delimiter="|" --query="select * from newdb.t_csv FORMAT CSV" > /root/result

CK的连接工具

DBeaver和tabix