:Delta Live Materialized View (Delta Live MV) (Invitational Preview)

Scenario 1: Use a partitioned Delta Live MV to represent incremental data for the current day and full historical data

SET odps.task.major.version=sql_flighting_dlmv;

CREATE MATERIALIZED VIEW IF NOT EXISTS part_dlmv_department
PRIMARY KEY(dept_id) -- The primary key `dept_id` can be inferred from the SQL logic based on the base table's primary key.
-- Explicit declaration is typically not required. However, because partitioned MVs currently only support BUILD DEFERRED mode, 
-- which does not support automatic key inference, you must declare the primary key explicitly.
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 is a near-real-time ingestion table that describes the incremental data for the current day.
SELECT *, get_setting('odps.custom.setting.department.pt') AS pt FROM t_department; 
UNION ALL
-- history_t_department is a historical partitioned table that contains all historical data.
SELECT * FROM history_t_department;

Scenario 2: Infer the Delta Live MV's primary key by usinggroup by key

-- 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) -- The primary key `value` can be inferred from the SQL logic (GROUP BY value).
-- Explicit declaration is typically not required. However, because partitioned DLMVs currently support only the BUILD DEFERRED mode,
-- which does not support automatic key inference, you must declare the primary key explicitly.
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;

Scenario 3: Infer the Delta Live MV's primary key from the base table's primary key

-- 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) -- The primary key `key` can be inferred from the SQL logic (derived from the base table's PK).
-- Explicit declaration is typically not required. However, because partitioned DLMVs currently support only the BUILD DEFERRED mode, 
-- which does not support automatic key inference, you must declare the primary key explicitly.
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;

Scenario 4: The Delta Live MV's primary key cannot be inferred

-- 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. Explicitly declare the PK column.
CREATE MATERIALIZED VIEW dlmv_pt
PRIMARY KEY(value) -- The PK cannot be inferred from the SQL logic, but you know the data satisfies PK uniqueness. The PK must be declared explicitly.
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. Modify the SQL logic to allow PK inference.
-- If you cannot guarantee the uniqueness of the 'value' column, modify the logic with a GROUP BY clause, as shown below:
CREATE MATERIALIZED VIEW dlmv_pt
PRIMARY KEY(value) -- The PK `value` can now be inferred from the SQL logic (GROUP BY value).
-- Explicit declaration is typically not required. However, because partitioned DLMVs currently support only the BUILD DEFERRED mode, 
-- which does not support automatic key inference, you must declare the primary key explicitly.
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;

Scenario 1: Infer the Delta Live MV's primary key by usinggroup by key

-- 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) -- The PK `value` can be inferred from the SQL logic (GROUP BY value), so no explicit declaration is needed.
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT value, MAX(value2) FROM dlmv_base_table GROUP BY value;

Scenario 2: Infer the Delta Live MV's primary key from the base table's primary key

-- 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) -- The PK `key` can be inferred from the SQL logic (derived from the base table's PK), so no explicit declaration is needed.
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT key, value, value2 FROM dlmv_base_table;

Scenario 3: The Delta Live MV's primary key cannot be inferred

-- 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. Explicitly declare the PK column.
CREATE MATERIALIZED VIEW dlmv
PRIMARY KEY(value) -- The PK cannot be inferred from the SQL logic, but you know the data satisfies PK uniqueness. The PK must be declared explicitly.
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT value, value2 FROM dlmv_base_table;

# 2. Modify the SQL logic to allow PK inference.
-- If you cannot guarantee the uniqueness of the 'value' column, modify the logic with a GROUP BY clause, as shown below:
CREATE MATERIALIZED VIEW dlmv
-- PRIMARY KEY(value) -- The PK `value` can now be inferred from the SQL logic (GROUP BY value), so no explicit declaration is needed.
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT value, MAX(value2) FROM dlmv_base_table GROUP BY value;