MaxCompute：動態物化視圖（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;