关于视图

主要内容:

  • 介绍视图,普通视图和物化视图
  • 介绍Hive中的物化视图

视图,指的是数据库中的视图。其存在的主要目的是为了提高查询数据的效率,当然还有其他用途。视图分为普通视图和物化视图。首先明白基本概念:

1、普通视图

普通视图是基于对基表的查询结果,而建立的一张虚拟表,是纯粹的逻辑对象。其本身是不存储任何数据的(不占用存储空间),它只定义了要查询的基本的SQL逻辑(提前编译好),可以自由定义要查询的表和字段,并且在引用视图时动态计算出结果。视图支持嵌套,在已有视图之上还可以嵌套视图。从数据库系统内部来看,一个视图就是一条select语句;从数据库系统外部来看,视图就如同一张表一样。也可以理解为视图就是一个函数,函数里封装了复杂的逻辑,使用者不用关心具体逻辑,也可供多人调用,具有隐藏查询复杂性的作用。但是,若是定义了复杂的查询逻辑,则视图查询会存在效率问题。

使用(普通)视图的优点:

  • 提高了重用性。比如一个比较复杂的查询不想每次都写很多语句,就可以写个视图。
  • 提高了安全性。根据权限和身份,可以对不同的用户,开放不同的表和字段(敏感数据不可见),设定不同的视图。用户对视图,不可以随意的更改和删除,保证了数据的安全性。
  • 简化了逻辑。可以在视图内部封装复杂的业务逻辑,使用时只需要调用视图即可。业务上可以查的一张表,但是底层可能映射的是三张或多张表的数据。

2、物化视图

物化视图是一种特殊的物理表,物化视图是相对普通视图而言的。在数据库中使用物化视图,通常是出于效率原因,是一种优化。视图是一种虚拟表,而物化视图是将视图的查询结果缓存到了具体表中。这种方式也是有代价的,因为物化视图的数据可能会过期。创建物化视图的过程叫做物化,这是缓存查询结果的一种形式,类似于缓存或者记忆化,或者提前计算。物化视图常用于数据仓库,在数仓中查询基表可能很耗时。在物化视图中,可以在任意列上建立索引。相反,在普通的视图中是无法做到的,只能依赖于基表。物化视图需要进行刷新才会更新数据,使用时要根据业务应用场景考虑数据的时效性,设置合理的更新频率,在时效性和查询效率之间达到平衡。

3、Hive中的视图和物化视图

Hive 3.0.0 中引入了对物化视图的支持。Hive物化视图是基于Hive内部表的查询结果得到的特殊表,存储实际的数据,占用物理空间。物化视图主要用于预先计算并保存指定逻辑的结果,在执行查询时,就可以将输入的查询语句重写或者部分重新成基于物化视图的查询,这样就可以避免进行表连接、聚合等耗时的操作,从而提高查询效率

3.1 创建物化视图

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)]
  [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
  [
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS
<query>;

说明:

  • 物化视图创建语句是原子的,这意味着在填充所有查询结果之前,其他用户看不到物化视图。
  • 不能基于物化视图的查询结果建立物化视图。
  • 不能对物化视图做增删改操作。
  • 当基表数据更新,需要手动对物化视图进行更新,否则物化视图将保留旧数据,即过期。
  • 删除基表之前必须先删除基于该基表所建立的物化视图。
  • 物化视图文件格式支持:PARQUET、TextFile、SequenceFile、RCfile、ORC。默认文件格式是ORC。化视图可以使用自定义存储处理程序存储在外部系统中,例如Druid。

3.2 物化视图的更新

当物化视图中使用的源表数据发生变化时,例如,插入新数据或修改现有数据时,需要更新物化视图,以使其与这些更改保持最新。当前,物化视图的重建操作需要由用户触发。默认情况下,Hive 将尝试以增量方式重建物化视图,如果不可能的话,将退回到完全重建。当前实现仅在源表上执行INSERT操作时支持增量重建,而UPDATEDELETE操作将强制对物化视图进行完全重建。

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

3.3 基于物化视图的其他操作

-- Drops a materialized view
DROP MATERIALIZED VIEW [db_name.]materialized_view_name;
-- Shows materialized views (with optional filters)
SHOW MATERIALIZED VIEWS [IN database_name] ['identifier_with_wildcards'];
-- Shows information about a specific materialized view
DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;
-- Setting the materialized view lifecycle
SET hive.materializedview.rewriting.time.window=10min;

3.4 基于物化视图的查询优化

物化视图创建以后,可用于相关查询的加速。即:用户提交查询query,若该query经过重写后可以命中已经存在的物化视图,则直接通过物化视图查询数据返回结果,以实现查询加速。可以在hive中进行全局配置:hive.materializedview.rewriting。默认情况下,物化视图可用于优化程序重写查询,而DISABLE REWRITE选项可用于在实例化视图创建时更改此行为。

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

RewritingApache Calcite 的一部分,它支持包含 TableScan、Project、Filter、Join、Aggregate 运算符的查询。这里提供一些示例来简要说明不同的重写:

示例基于以下表信息:

CREATE TABLE depts(
  deptno INT NOT NULL,
  deptname VARCHAR(20),
  PRIMARY KEY (deptno)
);
CREATE TABLE locations(
  locationid INT NOT NULL,
  state CHAR(2),
  PRIMARY KEY (locationid)
);
CREATE TABLE emps(
  empid INT NOT NULL,
  deptno INT NOT NULL,
  locationid INT NOT NULL,
  empname VARCHAR(20) NOT NULL,
  salary DECIMAL (18, 2),
  PRIMARY KEY (empid),
  FOREIGN KEY (deptno) REFERENCES depts(deptno),
  FOREIGN KEY (locationid) REFERENCES locations(locationid)
);

3.3.1 Join rewriting:

案例1:查询出员工编号等于1的员工的员工id和部门名称。则原查询如下:

SELECT empid, deptname
FROM depts d
JOIN (
  SELECT empid, deptno
  FROM emps
  WHERE empid = 1) AS e
ON d.deptno = e.deptno;

如果创建了如下物化视图:

CREATE MATERIALIZED VIEW mv AS
SELECT empid, deptname
FROM emps e
JOIN depts d on e.deptno = d.deptno;

则会将原查询Rewriting为:

SELECT empid, deptname
FROM mv
WHERE empid = 1;

3.3.2 Aggregate rewriting

案例2:查询出部门编号大于10的部门编号。则原查询如下:

SELECT deptno
FROM emps
WHERE deptno > 10
GROUP BY deptno;

如果创建了如下物化视图:

CREATE MATERIALIZED VIEW mv AS
SELECT empid, deptno
FROM emps
WHERE deptno > 5
GROUP BY empid, deptno;

则会将原查询Rewriting为:

SELECT deptno
FROM mv
WHERE deptno > 10
GROUP BY deptno;

3.3.3 Union rewriting

案例2:查询出工资大于10000的员工id和部门名称。则原查询如下:

SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000;

如果创建了如下物化视图:

SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 12000;

则会将原查询Rewriting为:

SELECT empid, deptname
FROM mv
UNION ALL
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000 AND salary <= 12000;