You need to enable JavaScript to run this app.
导航

物化视图 SQL 语法

最近更新时间2023.03.03 15:40:44

首次发布时间2022.12.07 20:22:18

1. 物化视图介绍

物化视图主要通过预计算的方式,解决高频子查询重复计算的问题。

2. 语法说明

2.1 CREATE

  • 新增语法
CREATE [OR REPLACE] MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]mview_name
  [(column_name [COMMENT column_comment], ...) ]
  [PARTITIONED BY (col_name, col_name, ...)]
  [CLUSTERED BY (col_name, col_name, ...)
    [SORTED BY (col_name [ASC|DESC], ...)]
    INTO num_buckets BUCKETS
  ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name=property_value, ...)]
[AS] select_statement;
  • 参数说明
参数是否可选说明
[db_name.]mview_name物化视图名称,如果不设置 db_name,则默认为当前数据库。

select_statement

  • 一个 SELECT 语句,用来创建物化视图;

  • 此 SQL 会作为当前物化视图的定义;

  • 此 SQL 仅支持 Aggregate,具体约束可见后文的其他说明

(column_name [COMMENT column_comment], ...)

指定物化视图包含的 column 信息,会与 select_statement 的 select list 依次对应:

  • 相同 index 上的 name 可以不一致,如果不一致,相当于进行 alias 操作;

  • column_list 数量 = select list 数量

column_list 必须包含如下所有列:

  • 所有 group-by columns (对于 Aggregate)

  • 所有 clustered-by columns(对于 Bucket)

  • 不能与 partitioned-by columns 同名(如果指定分区)

PARTITIONED BY (col_name, col_name, ...)设置分区列,关于分区列的进一步说明可见 其他说明。

OR REPLACE

替换同名物化视图,继承其所有 schema 信息,但不会继承原本数据。
不能与 IF NOT EXISTS 参数同时设置。

IF NOT EXISTS

当 mview_name 已经存在对应实体表,创建同名视图会报错,如果设置此参数,则不会报错。
不能与 OR REPLACE 参数同时设置。

CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC
INTO num_buckets BUCKETS


DESC], ...)]

设置 Bucket 信息,用于创建 Bucket 物化视图。

COMMENT mview_comment设置 comment。
TBLPROPERTIES (property_name=property_value, ...)设置 Table Properties。

2.2 ALTER

  • 新增语法
ALTER MATERIALIZED VIEW [db_name.]mview_name
  (
      SET COMMENT mview_comment                             |
      SET TBLPROPERTIES (property_name=property_value, ...) |
  )
  • 参数说明
参数是否可选说明
[db_name.]mview_name物化视图名称,如果不设置 db_name,则默认为当前数据库。
COMMENT mview_comment修改 comment。
TBLPROPERTIES (property_name=property_value, ...)修改 Table Properties。

2.3 DROP

  • 新增语法
DROP MATERIALIZED VIEW [IF EXISTS] [db_name.]mview_name
  • 参数说明
参数是否可选说明

[db_name.]mview_name

物化视图名称,如果不设置 db_name,则默认为当前数据库。

IF EXISTS

当删除的物化视图不存在的时,设置此参数可以不报错。

2.4 SHOW

  • 新增语法
SHOW MATERIALIZED VIEWS [(IN db_name) | (ON [db_name.]table_name)]
   [[LIKE] 'identifier_with_wildcards']
  • 参数说明
参数是否可选说明
IN db_name展示 db_name 中的所有物化视图;
ON [db_name.]table_name展示基表为 table_name 的物化视图。

[LIKE] 'identifier_with_wildcards'

使用正则对物化视图名称进行过滤,例如 like '%mv1%',则会显示 name 为 amv1, amv2 的物化视图。

  • 显示字段
Key说明
database物化视图/实体表所在数据库名称
materializedViewName视图名称
type视图类型:Aggregate
valid是否有效:True / False
definition视图定义

2.5 SHOW CREATE

  • 新增语法 (类似 Show create table)
SHOW CREATE MATERIALIZED VIEW [db_name.]mview_name

2.6 DESCRIBE

  • 新增语法
DESC[RIBE] MATERIALIZED VIEW [EXTENDED | FORMATTED] [db_name.]mview_name
  • 语法说明
参数是否可选说明
[db_name.]mview_name物化视图名称,如果不设置 db_name,则默认为当前数据库。
EXTENDED展示更加全面的信息。
  • 显示字段
Key说明

#Column Information

col_name

column 名称

data_type数据类型
commentComment
materialized_view_origin_exprcolumn 原始表达式

#Partition Information

col_name

column 名称

data_type数据类型
commentComment
materialized_view_origin_exprcolumn 原始表达式

#Detailed MView Information
(EXTENDED)

Database

物化视图/实体表所在数据库名称

MView Name物化视图的名称(用户指定的名称)
MView Definition物化视图定义,创建时指定的 select_statement,如果用户指定 column_list,则此处会存储:select column_list from select_statement。
MView TypeAggregate
Base Tables基表名称列表: db_name.table1
Rewrite Eanbletrue / false
Valid当前视图是否有效,True / False,如果设置为 False,则可以 Drop。
Owner创建者名称
Created TimesMView 的创建时间
Last RefreshedMView 的最后修改时间
Provider例如:Parquet/ORC
Num BucketsBucket 相关
Bucket ColumnsBucket 相关
Sort ColumnsBucket 相关
CommentComment
3. 参数说明

离线 SQL 查询需要开启如下参数:

set bytequery.sql.materialized.view.query.enabled=true;
4. 操作示例

4.1 如何建表

点击表管理页面 - DDL 建表/视图

4.2 分区表

4.2.1 Base table create

CREATE TABLE test_olap.emps_part(
  empid INT,
  deptno INT,
  locationid INT,
  empname STRING,
  salary Int,
  ts STRING)
PARTITIONED BY (date string COMMENT 'date partition')
COMMENT 'emps partition table'
STORED AS bytelake_mor
TBLPROPERTIES ( 
'bytelake.recordkey.fields' = 'empid', 'bytelake.precombine.fields' = 'ts' );

4.2.2 Materialized view create

CREATE MATERIALIZED VIEW test_olap.mv_emps_part
PARTITIONED BY (date)
select
    empid,
    deptno,
    sum(salary) as total_salary,
    date
from
    test_olap.emps_part
group by
    empid,
    deptno,
    date

4.2.3 分区数据写入

insert into test_olap.emps_part partition (date='20221011')
values 
    (1, 1, 1, 'a', 10, '111'),
    (1, 1, 1, 'a', 99, '111'), 
    (2, 1, 1, 'b', 5, '111'), 
    (3, 2, 1, 'c', 11, '111');

4.2.4 物化视图数据同步

REFRESH MATERIALIZED VIEW test_olap.mv_emps_part partition (date='20221011');

4.2.5 数据验证

select * from test_olap.emps_part where date = '20221011';
select * from test_olap.mv_emps_part where date = '20221011';

4.2.6 物化视图查询

-- set tqs.targeting.worker=10.250.20.131:9305;
set tqs.query.engine.type=sparkcli;
set bytequery.sql.materialized.view.query.enabled=true;
select
    empid,
    deptno,
    sum(salary) as total_salary
from
    test_olap.emps_part
where date = '20221011'
group by
    empid,
    deptno;

4.3 非分区表

4.3.1 Base table create

CREATE TABLE test_olap.xy_emps(
  empid INT,
  deptno INT,
  locationid INT,
  empname STRING,
  salary Int,
  ts STRING)
COMMENT 'emps table'
STORED AS bytelake_mor
TBLPROPERTIES (
  'bytelake.recordkey.fields' = 'empid',
  'bytelake.precombine.fields' = 'ts'
);

4.3.2 Materialized view create

CREATE MATERIALIZED VIEW test_olap.xy_mv_emps
as 
select
    empid,
    deptno,
    sum(salary) as total_salary
from
    test_olap.xy_emps
group by
    empid,
    deptno

4.3.3 数据写入

insert into test_olap.xy_emps
values 
    (1, 1, 1, 'a', 10, '111'),
    (1, 1, 1, 'a', 99, '111'), 
    (2, 1, 1, 'b', 5, '111'), 
    (3, 2, 1, 'c', 11, '111');

4.3.4 MV数据同步

REFRESH MATERIALIZED VIEW test_olap.xy_mv_emps;

4.3.5 数据验证

select * from test_olap.xy_emps;
select * from test_olap.xy_mv_emps;

4.3.6 物化视图查询

set tqs.query.engine.type=sparkcli;
set bytequery.sql.materialized.view.query.enabled=true;
select
    empid,
    deptno,
    sum(salary) as total_salary
from
    test_olap.xy_emps
group by
    empid,
    deptno;
5. 相关限制
  • select_statement 中的基表仅支持单个 LAS 内表;

  • select_statement 的 output 不能包含任何基表的分区列;

  • select_statement 中的函数必须都是确定性(deterministic)的,例如不能存在:CURRENT_TIME、CURRENT_TIMESTAMP、RAND 等函数;

  • 聚合函数仅支持:SUM、COUNT、MIN、MAX;

  • 不支持算子:Window function、ORDER BY clause、LIMIT clause、嵌套子查询、UDTF (user defined table function);

  • 不支持 distinct :count(distinct col1);