动态物化视图（Delta Live MV）（邀测）

场景一：使用分区 DLMV 表达当天入仓的增量数据以及历史的全量数据

SET odps.task.major.version=sql_flighting_dlmv;

CREATE MATERIALIZED VIEW IF NOT EXISTS part_dlmv_department
PRIMARY KEY(dept_id) -- 可以通过SQL逻辑推导出pk列为dept_id（通过base表的pk列推导出来），
-- 预期不需要显示声明，但由于分区mv目前只支持BUILD DEFERRED，
-- 该模式下还没有做pk推导的功能，所以需要显示的声明pk
LIFECYCLE 10
BUILD DEFERRED
PARTITIONED BY (pt)
TBLPROPERTIES('refresh_mode'='incremental', 
  'refresh_job_settings'='set odps.task.major.version=sql_flighting_dlmv;')
AS 
-- t_department 是近实时入仓表，主要描述用户当天的增量数据
SELECT *, get_setting('odps.custom.setting.department.pt') AS pt FROM t_department; 
UNION ALL
-- history_t_department 是历史分区表，包含了历史所有数据
SELECT * FROM history_t_department;

场景二：通过group by key推导出dlmv的pk列。

// pk delta table
CREATE TABLE dlmv_base_table(
    key    STRING NOT NULL PRIMARY KEY,
    value  BIGINT,
    value2 BIGINT
)
STORED AS ALIORC
TBLPROPERTIES (
    'transactional'                    = 'true',
    'cdc.insert.into.passthrough.enable' = 'true',
    'acid.cdc.mode.enable'             = 'true',
    'acid.cdc.build.async'             = 'false'
);

CREATE MATERIALIZED VIEW dlmv_pt
PRIMARY KEY(value) -- 可以通过SQL逻辑推导出pk列为value（gropy by value），
-- 预期不需要显示声明pk列，但分区dlmv当前只支持BUILD DEFERRED模式，
-- 该模式下还没有做pk推导的功能，所以需要显示的声明pk列
build deferred 
partitioned BY (pt)
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT *, get_setting('odps.custom.setting.dlmv_pt.pt') AS pt FROM (SELECT value, MAX(value2)  FROM dlmv_base_table GROUP BY value) t;

场景三：通过base表的pk推导出dlmv的pk列。

// pk delta table
CREATE TABLE dlmv_base_table(
    key    STRING NOT NULL PRIMARY KEY,
    value  BIGINT,
    value2 BIGINT
)
STORED AS ALIORC
TBLPROPERTIES (
    'transactional'                    = 'true',
    'cdc.insert.into.passthrough.enable' = 'true',
    'acid.cdc.mode.enable'             = 'true',
    'acid.cdc.build.async'             = 'false'
);

CREATE MATERIALIZED VIEW dlmv_pt
PRIMARY KEY(key) --可以通过SQL逻辑推导出pk列为key（通过base表的pk列推导出来），预期不需要显示声明pk列, 但分区dlmv当前只支持BUILD DEFERRED模式，该模式下还没有做pk推导的功能，所以需要显示的声明pk列
build deferred 
partitioned BY (pt)
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT key, value, value2, get_setting('odps.custom.setting.dlmv_pt.pt') as pt FROM dlmv_base_table;

场景四：无法推导出dlmv的pk列。

// pk delta table
CREATE TABLE dlmv_base_table(
    key    STRING NOT NULL PRIMARY KEY,
    value  BIGINT,
    value2 BIGINT
)
STORED AS ALIORC
TBLPROPERTIES (
    'transactional'                    = 'true',
    'cdc.insert.into.passthrough.enable' = 'true',
    'acid.cdc.mode.enable'             = 'true',
    'acid.cdc.build.async'             = 'false'
);

# 1. 直接显示声明pk列
CREATE MATERIALIZED VIEW dlmv_pt
primary key(value) -- 通过SQL逻辑推导出不出来pk列为valu，但用户侧知道数据本身是满足pk唯一性的, 需要显示声明pk列
build deferred 
partitioned BY (pt)
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT value, value2, get_setting('odps.custom.setting.dlmv_pt.pt') as pt FROM dlmv_base_table;

# 2. 改造SQL逻辑，从而能推导出pk列
-- 如果用户侧无法保证value列值的唯一性，则可以通过group by逻辑进行改造，如下所示：
CREATE MATERIALIZED VIEW dlmv_pt
primary key(value) -- 可以通过SQL逻辑推导出pk列为value（gropy by value），预期不需要显示声明pk列，但分区dlmv当前只支持BUILD DEFERRED模式，该模式下还没有做pk推导的功能，所以需要显示的声明pk列
build deferred 
partitioned BY (pt)
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT value, MAX(value2), get_setting('odps.custom.setting.dlmv_pt.pt') as pt FROM dlmv_base_table GROUP BY value;

场景一：通过group by key推导出dlmv的pk列。

// pk delta table
CREATE TABLE dlmv_base_table(
    key    STRING NOT NULL PRIMARY KEY,
    value  BIGINT,
    value2 BIGINT
)
STORED AS ALIORC
TBLPROPERTIES (
    'transactional'                    = 'true',
    'cdc.insert.into.passthrough.enable' = 'true',
    'acid.cdc.mode.enable'             = 'true',
    'acid.cdc.build.async'             = 'false'
);

CREATE MATERIALIZED VIEW dlmv
-- primary key(value) 可以通过SQL逻辑推导出pk列为value（gropy by value），不需要显示声明pk列
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT value, MAX(value2) FROM dlmv_base_table GROUP BY value;

场景二：通过base表的pk推导出dlmv的pk列。

// pk delta table
CREATE TABLE dlmv_base_table(
    key    STRING NOT NULL PRIMARY KEY,
    value  BIGINT,
    value2 BIGINT
)
STORED AS ALIORC
TBLPROPERTIES (
    'transactional'                    = 'true',
    'cdc.insert.into.passthrough.enable' = 'true',
    'acid.cdc.mode.enable'             = 'true',
    'acid.cdc.build.async'             = 'false'
);

CREATE MATERIALIZED VIEW dlmv
-- primary key(key) 可以通过SQL逻辑推导出pk列为key（通过base表的pk列推导出来），不需要显示声明pk列
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT key, value, value2 FROM dlmv_base_table;

场景三：无法推导出dlmv的pk列。

// pk delta table
CREATE TABLE dlmv_base_table(
    key    STRING NOT NULL PRIMARY KEY,
    value  BIGINT,
    value2 BIGINT
)
STORED AS ALIORC
TBLPROPERTIES (
    'transactional'                    = 'true',
    'cdc.insert.into.passthrough.enable' = 'true',
    'acid.cdc.mode.enable'             = 'true',
    'acid.cdc.build.async'             = 'false'
);

# 1. 显示声明pk列
CREATE MATERIALIZED VIEW dlmv
primary key(value) -- 通过SQL逻辑推导出不出来pk列为valu，但用户侧知道数据本身是满足pk唯一性的, 需要显示声明pk列
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT value, value2 FROM dlmv_base_table;

# 2. 改造SQL逻辑，从而能推导出pk列
-- 如果用户侧无法保证value列值的唯一性，则可以通过group by逻辑进行改造，如下所示：
CREATE MATERIALIZED VIEW dlmv
-- primary key(value) 可以通过SQL逻辑推导出pk列为value（gropy by value），不需要显示声明pk列
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT value, MAX(value2) FROM dlmv_base_table GROUP BY value;