ClickHouse（二十一）：Clickhouse SQL DDL操作-临时表及视图

 

进入正文前，感谢宝子们订阅专题、点赞、评论、收藏！关注IT贫道，获取高质量博客内容！

🏡个人主页：含各种IT体系技术，IT贫道_Apache Doris,大数据OLAP体系技术栈,Kerberos安全认证-CSDN博客

📌订阅：拥抱独家专题，你的订阅将点燃我的创作热情！

👍点赞：赞同优秀创作，你的点赞是对我创作最大的认可！

⭐️ 收藏：收藏原创博文，让我们一起打造IT界的荣耀与辉煌！

✏️评论：留下心声墨迹，你的评论将是我努力改进的方向！

---

目录

​​​​​​​1. 临时表

1.1 创建临时表语法

1.2 示例

​​​​​​​​​​​​​​2. 视图

​​​​​​​​​​​​​​2.1 普通视图

​​​​​​​2.2 物化视图

---

​​​​​​​1. 临时表

ClickHouse支持临时表，临时表具备以下特征：

1. 当会话结束或者链接中断时，临时表将随会话一起消失。
2. 临时表仅能够使用Memory表引擎，创建临时表时不需要指定表引擎。
3. 无法为临时表指定数据库。它是在数据库之外创建的，与会话绑定。
4. 如果临时表与另一个表名称相同，那么当在查询时没有显式的指定db的情况下，将优先使用临时表。
5. 对于分布式处理，查询中使用的临时表将被传递到远程服务器。

1.1 创建临时表语法

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name [ON CLUSTER cluster](name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],...)注意：不需要指定表引擎，默认是Memory

1.2 示例

#查看库 newdb下 表node1 :) show tables;SHOW TABLES┌─name────────┐│ t1            ││ t2            ││ t_log        ││ t_stripelog ││ t_tinylog    │└─────────────┘5 rows in set. Elapsed: 0.004 sec.#查询表 t_log表数据node1 :) select * from t_log;SELECT *FROM t_log┌─id─┬─name─┬─age─┐│  1  │ 张三   │  18 ││  2  │ 李四   │  19 │└────┴─────┴─────┘┌─id─┬─name─┬─age─┐│  3  │ 王五  │  20   ││  4  │ 马六  │  21   ││  5  │ 田七  │  22   │└────┴─────┴─────┘5 rows in set. Elapsed: 0.004 sec.#创建临时表 t_log ，与当前库下的t_log同名node1 :) create temporary table t_log(id UInt8 ,name String);CREATE TEMPORARY TABLE t_log(`id` UInt8,`name` String)Ok.0 rows in set. Elapsed: 0.001 sec.#查询表 t_log的数据与结构，发现没有数据，这里查询的是临时表，结构如下：node1 :) desc t_log;DESCRIBE TABLE t_log┌─name─┬─type───┬│ id   │ UInt8  ││ name │ String │└──────┴────────┴2 rows in set. Elapsed: 0.003 sec.#如果想要查询到库newdb下的t_log需要加上数据库名node1 :) select * from newdb.t_log;#切换库为default，同样还可以查询到表t_log，说明表不属于任何库node1 :) use default;node1 :) desc t_log;DESCRIBE TABLE t_log┌─name─┬─type───┬│ id   │ UInt8  ││ name │ String │└──────┴────────┴2 rows in set. Elapsed: 0.004 sec.#退出客户端之后，重新登录，查询t_log不存在。node1 :) select * from t_log;Exception: Received from localhost:9000. DB::Exception: Table default.t_log doesn't exist..#也可以不退出客户端直接删除临时表node1 :) drop table t_log;DROP TABLE t_logOk.0 rows in set. Elapsed: 0.001 sec.

注意：在大多数情况下，临时表不是手动创建的，而是在使用外部数据进行查询或分布式时创建的,可以使用ENGINE = Memory的表代替临时表。

​​​​​​​​​​​​​​2. 视图

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

​​​​​​​​​​​​​​2.1 普通视图

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

• 创建普通视图语法：

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

• 示例：

#在库 newdb中创建表 personinfonode1 :) 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');#查询表中的数据node1 :) select * from personinfo;SELECT *FROM personinfo┌─id─┬─name─┬─age─┬───birthday─┐│  1  │ 张三  │  18 │ 2021-06-01 ││  2  │ 李四  │  19 │ 2021-06-02 │└────┴──────┴─────┴────────────┘┌─id─┬─name─┬─age─┬───birthday─┐│  3 │ 王五  │  20  │ 2021-06-03 ││  4 │ 马六  │  21  │ 2021-06-04 ││  5 │ 田七  │  22  │ 2021-06-05 │└────┴──────┴─────┴────────────┘5 rows in set. Elapsed: 0.004 sec.#创建视图 person_view 映射查询子句node1 :) create view person_view as select name,birthday from personinfo;CREATE VIEW person_view ASSELECTname,birthdayFROM personinfoOk.0 rows in set. Elapsed: 0.009 sec.#查询视图person_view中的数据结果node1 :) select * from person_view;SELECT *FROM person_view┌─name─┬───birthday─┐│ 张三  │ 2021-06-01 ││ 李四  │ 2021-06-02 │└──────┴────────────┘┌─name─┬───birthday─┐│ 王五  │ 2021-06-03 ││ 马六  │ 2021-06-04 ││ 田七  │ 2021-06-05 │└──────┴────────────┘5 rows in set. Elapsed: 0.004 sec.#删除视图 使用drop即可node1 :) drop table person_view;DROP TABLE person_viewOk.0 rows in set. Elapsed: 0.002 sec.

​​​​​​​2.2 物化视图

物化视图是查询结果集的一份持久化存储，所以它与普通视图完全不同，而非常趋近于表。”查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表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 中创建物化视图 t_view1node1 :) create materialized view  t_view1 engine = Log as select * from personinfo;#查询 所有表node1 :) show tables;SHOW TABLES┌─name───────────┐│ .inner.t_view1 ││ personinfo      └────────────────┘2 rows in set. Elapsed: 0.004 sec.#向表 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');#查看物化视图 t_view1数据node1 :) select * from t_view1;SELECT *FROM t_view1┌─id─┬─name─┬─age─┬───birthday─┐│  1 │ 张三 │  18 │ 2021-06-01 ││  2 │ 李四 │  19 │ 2021-06-02 │└────┴──────┴─────┴────────────┘┌─id─┬─name─┬─age─┬───birthday─┐│  3 │ 王五 │  20 │ 2021-06-03 ││  4 │ 马六 │  21 │ 2021-06-04 ││  5 │ 田七 │  22 │ 2021-06-05 │└────┴──────┴─────┴────────────┘5 rows in set. Elapsed: 0.004 sec.#创建物化视图 t_view2node1 :) create materialized view  t_view2 engine = Log as select count(name) as cnt from personinfo;#向表 personinfo中插入以下数据node1 :) insert into personinfo values (6,'赵八',23,'2021-06-06'),(7,'孙九',22,'2021-06-07');#查询物化视图表 t_view2数据,可以看到做了预计算，这里不能一条条插入，不然效果是每条数据都会生成一个结果。node1 :) select * from t_view2;SELECT *FROM t_view2┌─cnt─┐│   2  │└─────┘1 rows in set. Elapsed: 0.004 sec.#删除物化视图node1 :) drop table t_view2;DROP TABLE t_view2Ok.0 rows in set. Elapsed: 0.001 sec.

注意：当创建好物化视图t_view1时，可以进入到/var/lib/clickhouse/data/newdb目录下看到%2Einner%2Et_view1目录，当物化视图中同步基表数据时，目录中有对应的列文件和元数据记录文件，与普通创建表一样，有目录结构。

---

👨‍💻如需博文中的资料请私信博主。

---