物化视图 SQL 语法

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

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;

• 参数说明

2.2 ALTER

• 新增语法

ALTER MATERIALIZED VIEW [db_name.]mview_name
  (
      SET COMMENT mview_comment                             |
      SET TBLPROPERTIES (property_name=property_value, ...) |
  )

• 参数说明

2.3 DROP

• 新增语法

DROP MATERIALIZED VIEW [IF EXISTS] [db_name.]mview_name

• 参数说明

2.4 SHOW

• 新增语法

SHOW MATERIALIZED VIEWS [(IN db_name) | (ON [db_name.]table_name)]
   [[LIKE] 'identifier_with_wildcards']

• 参数说明

• 显示字段

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

• 语法说明

• 显示字段

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

set bytequery.sql.materialized.view.query.enabled=true;

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;

• 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)；