【Clickhouse】Clickhouse 普通视图

2023-12-09 20:39:39

1.概述

转载：https://vkingnew.blog.csdn.net/article/details/107350687

clickhouse拥有普通视图和物化视图，其中物化视图需要独立的存储，而普通视图只是一层简单的查询同义词。

普通视图的创建语法：

语法：

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

普通视图(normal view) 不存储数据，它只是一层select 查询映射，类似于表的别名或者同义词，能简化查询，对原有表的查询性能没有增强的作用，具体性能依赖视图定义的语句。

表的定义以及数据：

CREATE TABLE emp
(
    `empno` Int32,
    `ename` String,
    `job` String,
    `mgr` Int32,
    `hiredate` Date,
    `sal` Decimal(7, 2),
    `comm` Nullable(Decimal(7, 2)),
    `deptno` Int32
)
ENGINE = MergeTree()
ORDER BY empno
 
 
 CREATE TABLE dept
(
    `deptno` Int32,
    `dname` String,
    `loc` String
)
ENGINE = Log
 
 CREATE TABLE salgrade
(
    `grade` Int32,
    `losal` Int32,
    `hisal` Int32
)
ENGINE = Log
 
 
 
insert into `dept` (`deptno`, `dname`, `loc`) values('10','ACCOUNTING','NEW YORK'),('20','RESEARCH','DALLAS'),('30','SALES','CHICAGO'),('40','OPERATIONS','BOSTON');
 
insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);
 
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7369','SMITH','CLERK','7902','1980-12-17','800.00',NULL,'20');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7499','ALLEN','SALESMAN','7698','1981-02-20','1600.00','300.00','30');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7521','WARD','SALESMAN','7698','1981-02-22','1250.00','500.00','30');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7566','JONES','MANAGER','7839','1981-04-02','2975.00',NULL,'20');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7654','MARTIN','SALESMAN','7698','1981-09-28','1250.00','1400.00','30');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7698','BLAKE','MANAGER','7839','1981-05-01','2850.00',NULL,'30');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7782','CLARK','MANAGER','7839','1981-06-09','2450.00',NULL,'10');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7788','SCOTT','ANALYST','7566','1987-04-19','3000.00',NULL,'20');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7839','KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,'10');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7844','TURNER','SALESMAN','7698','1981-09-08','1500.00','0.00','30');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7876','ADAMS','CLERK','7788','1987-05-23','1100.00',NULL,'20');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7900','JAMES','CLERK','7698','1981-12-03','950.00',NULL,'30');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7902','FORD','ANALYST','7566','1981-12-03','3000.00',NULL,'20');
insert into `emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values('7934','MILLER','CLERK','7782','1982-01-23','1300.00',NULL,'10');

视图操作：

1.基于单表的视图：

Clickhouse> CREATE OR REPLACE VIEW emp_sales AS
:-]     SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN';
 
CREATE OR REPLACE VIEW emp_sales AS
SELECT 
    empno,
    ename,
    hiredate,
    sal,
    comm
FROM emp
WHERE job = 'SALESMAN'
 
Ok.
 
0 rows in set. Elapsed: 0.011 sec.

直接查询表：

Clickhouse> SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN';
 
SELECT 
    empno,
    ename,
    hiredate,
    sal,
    comm
FROM emp
WHERE job = 'SALESMAN'
 
┌─empno─┬─ename──┬───hiredate─┬─────sal─┬────comm─┐
│  7499 │ ALLEN  │ 1981-02-20 │ 1600.00 │  300.00 │
│  7521 │ WARD   │ 1981-02-22 │ 1250.00 │  500.00 │
│  7654 │ MARTIN │ 1981-09-28 │ 1250.00 │ 1400.00 │
│  7844 │ TURNER │ 1981-09-08 │ 1500.00 │    0.00 │
└───────┴────────┴────────────┴─────────┴─────────┘
 
4 rows in set. Elapsed: 0.003 sec. 
 
Clickhouse> select * from emp_sales;
 
SELECT *
FROM emp_sales
 
┌─empno─┬─ename──┬───hiredate─┬─────sal─┬────comm─┐
│  7499 │ ALLEN  │ 1981-02-20 │ 1600.00 │  300.00 │
│  7521 │ WARD   │ 1981-02-22 │ 1250.00 │  500.00 │
│  7654 │ MARTIN │ 1981-09-28 │ 1250.00 │ 1400.00 │
│  7844 │ TURNER │ 1981-09-08 │ 1500.00 │    0.00 │
└───────┴────────┴────────────┴─────────┴─────────┘
 
4 rows in set. Elapsed: 0.003 sec.

可以看到查询视图和查询表获取的数据是一样的，查询时间也是一样的。

2.基于多表的视图：截止到20.5.27 版本支持：

多表join：

Clickhouse> SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc, d.deptno FROM emp AS e ALL INNER JOIN dept AS d ON e.deptno = d.deptno WHERE deptno = 30;
 
SELECT 
    e.empno,
    e.ename,
    e.job,
    e.sal,
    d.dname,
    d.loc,
    d.deptno
FROM emp AS e
ALL INNER JOIN dept AS d ON e.deptno = d.deptno
WHERE deptno = 30
 
┌─empno─┬─ename──┬─job──────┬─────sal─┬─dname─┬─loc─────┬─d.deptno─┐
│  7499 │ ALLEN  │ SALESMAN │ 1600.00 │ SALES │ CHICAGO │       30 │
│  7521 │ WARD   │ SALESMAN │ 1250.00 │ SALES │ CHICAGO │       30 │
│  7654 │ MARTIN │ SALESMAN │ 1250.00 │ SALES │ CHICAGO │       30 │
│  7698 │ BLAKE  │ MANAGER  │ 2850.00 │ SALES │ CHICAGO │       30 │
│  7844 │ TURNER │ SALESMAN │ 1500.00 │ SALES │ CHICAGO │       30 │
│  7900 │ JAMES  │ CLERK    │  950.00 │ SALES │ CHICAGO │       30 │
└───────┴────────┴──────────┴─────────┴───────┴─────────┴──────────┘
 
6 rows in set. Elapsed: 0.004 sec.

创建视图报错：

Clickhouse> create or replace view vu_dept30 as SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc, d.deptno FROM emp AS e ALL INNER JOIN dept AS d ON e.deptno = d.deptno WHERE deptno = 30;
 
CREATE OR REPLACE VIEW vu_dept30 AS
SELECT 
    e.empno,
    e.ename,
    e.job,
    e.sal,
    d.dname,
    d.loc,
    d.deptno
FROM emp AS e
ALL INNER JOIN dept AS d ON e.deptno = d.deptno
WHERE deptno = 30
 
 
Received exception from server (version 20.5.2):
Code: 352. DB::Exception: Received from localhost:9000. DB::Exception: Cannot detect left and right JOIN keys. JOIN ON section is ambiguous.. 
 
0 rows in set. Elapsed: 0.002 sec. 
 
create or replace view vu_dept30 as SELECT
emp.empno,
emp.ename,
emp.job,
emp.sal,
dept.dname,
dept.loc,
dept.deptno
FROM emp 
ALL INNER JOIN dept  ON emp.deptno = dept.deptno
WHERE deptno = 30

现在的版本不支持表的表名来创建视图，只只支持表的全名。在实际使用中是会有些局限，特别是当表名比较长的时候。

视图操作：

1.查看视图的定义：

Clickhouse> show create table emp_sales\G
 
SHOW CREATE TABLE emp_sales
 
Row 1:
──────
statement: CREATE VIEW default.emp_sales
(
    `empno` Int32,
    `ename` String,
    `hiredate` Date,
    `sal` Decimal(7, 2),
    `comm` Nullable(Decimal(7, 2))
) AS
SELECT 
    empno,
    ename,
    hiredate,
    sal,
    comm
FROM default.emp
WHERE job = 'SALESMAN'
 
1 rows in set. Elapsed: 0.002 sec.

2.视图的删除：

Clickhouse> drop view emp_sales;
 
DROP VIEW emp_sales
 
Ok.
 
0 rows in set. Elapsed: 0.001 sec.

普通视图不支持alter操作。

限制：

参考：

https://clickhouse.tech/docs/en/sql-reference/statements/create/view/

https://github.com/ClickHouse/ClickHouse/issues/12532

码农公寓

1.概述

相关文章