Delta Live MV

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

SET odps.task.major.version=sql_flighting_dlmv;

CREATE MATERIALIZED VIEW IF NOT EXISTS part_dlmv_department
PRIMARY KEY(dept_id) -- The primary key can be inferred from the SQL logic. However, because partitioned MVs only support BUILD DEFERRED mode, which requires an explicitly declared primary key, you must declare it here.
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 mainly contains 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 containing all historical data.
SELECT * FROM history_t_department;

Scenario 2: Use group by key to derive the PK column of a dlmv.

// 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 can be inferred from the SQL logic. However, because partitioned MVs only support BUILD DEFERRED mode, which requires an explicitly declared primary key, you must declare it here.
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 primary key column from the primary key of the base table.

// 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 can be inferred from the SQL logic. However, because partitioned MVs only support BUILD DEFERRED mode, which requires an explicitly declared primary key, you must declare it here.
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 primary key column 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 primary key column
CREATE MATERIALIZED VIEW dlmv_pt
primary key(value) -- The primary key column cannot be inferred as 'value' from the SQL logic, but the user knows the data itself satisfies the primary key uniqueness. The primary key must be explicitly declared.
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 primary key inference.
-- If the uniqueness of the 'value' column cannot be guaranteed, the logic can be modified with a group by clause, as shown below:
CREATE MATERIALIZED VIEW dlmv_pt
primary key(value) -- The primary key can be inferred from the SQL logic. However, because partitioned MVs only support BUILD DEFERRED mode, which requires an explicitly declared primary key, you must declare it here.
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: By using group by key, you can derive the PK column of the DLMV.

// 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) can be inferred from the SQL logic (group by value), so explicit declaration is not 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 primary key column from the primary key of the base table.

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

Scenario 3: The Delta Live MV primary key column 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 primary key column
CREATE MATERIALIZED VIEW dlmv
primary key(value) -- The primary key column cannot be inferred as 'value' from the SQL logic, but the user knows the data itself satisfies the primary key uniqueness. The primary key must be explicitly declared.
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS
SELECT value, value2 FROM dlmv_base_table;

# 2. Modify the SQL logic to allow primary key inference
-- If the uniqueness of the 'value' column cannot be guaranteed, the logic can be modified with a group by clause, as shown below:
CREATE MATERIALIZED VIEW dlmv
-- primary key(value) can be inferred from the SQL logic (group by value), so explicit declaration is not needed.
TBLPROPERTIES (
    'refresh_mode'        = 'incremental',
    'enable_auto_refresh' = 'true'
)
AS SELECT value, MAX(value2) FROM dlmv_base_table GROUP BY value;