AnalyticDB:CREATE MATERIALIZED VIEW

Example preparation

All examples in this section use the following base tables. Run these statements first to create them.

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- Specify XUANWU as the table engine.
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);

/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- Specify XUANWU as the table engine.
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);

Complete refresh

Every 5 minutes

CREATE MATERIALIZED VIEW myview1
REFRESH   -- Equivalent to REFRESH COMPLETE.
 NEXT now() + INTERVAL 5 minute  -- Runs at creation time, then every 5 minutes.
AS
SELECT count(*) AS cnt FROM customer;

Daily at 02:00

CREATE MATERIALIZED VIEW myview2
REFRESH COMPLETE
 START WITH DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')  -- First run: tomorrow at 02:00.
 NEXT DATE_FORMAT(now() + INTERVAL 1 day, '%Y-%m-%d 02:00:00')        -- Then: every day at 02:00.
AS
SELECT count(*) AS cnt FROM customer;

Every Monday at 02:00

CREATE MATERIALIZED VIEW myview3
REFRESH COMPLETE ON DEMAND
 START WITH DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')  -- First run: next Monday at 02:00.
 NEXT DATE_FORMAT(now() + INTERVAL 7 - weekday(now()) day, '%Y-%m-%d 02:00:00')        -- Then: every Monday at 02:00.
AS
SELECT count(*) AS cnt FROM customer;

On the first day of each month at 02:00

CREATE MATERIALIZED VIEW myview4
REFRESH   -- Equivalent to REFRESH COMPLETE.
 NEXT DATE_FORMAT(last_day(now()) + INTERVAL 1 day, '%Y-%m-%d 02:00:00')  -- Runs at 02:00 on the 1st of the next month, then monthly.
AS
SELECT count(*) AS cnt FROM customer;

Once only

CREATE MATERIALIZED VIEW myview5
REFRESH   -- Equivalent to REFRESH COMPLETE.
 START WITH now() + INTERVAL 1 day  -- Runs once, 24 hours after creation. No further scheduled refreshes.
AS
SELECT count(*) AS cnt FROM customer;

Manual refresh only

CREATE MATERIALIZED VIEW myview6 (
  PRIMARY KEY (customer_id)
) DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id FROM customer;

To refresh manually:

REFRESH MATERIALIZED VIEW myview6;

On INSERT OVERWRITE (triggered refresh)

Refreshes automatically when the base table is overwritten by an INSERT OVERWRITE statement. No schedule is needed.

CREATE MATERIALIZED VIEW myview7
REFRESH COMPLETE ON OVERWRITE
AS
SELECT count(*) AS cnt FROM customer;

Fast refresh — single-table

Before creating a materialized view with fast refresh, enable binary logging for the cluster and base tables:

SET ADB_CONFIG BINLOG_ENABLE=true;
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;

Non-aggregate query, 10-second interval

CREATE MATERIALIZED VIEW fast_mv1
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT sale_id, sale_date, price
FROM sales
WHERE price > 10;

Grouped aggregate query, 5-second interval

The GROUP BY columns (customer_id, sale_date) become the primary key of the materialized view.

CREATE MATERIALIZED VIEW fast_mv2
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   customer_id, sale_date,                 -- GROUP BY columns; used as the primary key.
   COUNT(sale_id) AS cnt_sale_id,          -- Aggregate operation; alias required.
   SUM(price * quantity) AS total_revenue, -- Aggregate operation; alias required.
   customer_id / 100 AS new_customer_id    -- Expression column; alias required.
FROM sales
WHERE ifnull(price, 1) > 0                 -- Expressions are supported in WHERE conditions.
GROUP BY customer_id, sale_date;

Non-grouped aggregate query, 1-minute interval

The system generates a constant as the primary key because a non-grouped aggregate always produces a single row.

CREATE MATERIALIZED VIEW fast_mv3
REFRESH FAST NEXT now() + INTERVAL 1 minute
AS
SELECT count(*) AS cnt
FROM sales;

Fast refresh — multi-table

Non-aggregate JOIN, 5-second interval

CREATE MATERIALIZED VIEW fast_mv4
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
    c.customer_id,
    c.customer_name,
    s.sale_id,
    (s.price * s.quantity) AS revenue
FROM
    sales s
JOIN
    customer c ON s.customer_id = c.customer_id;

Grouped aggregate JOIN, 10-second interval

CREATE MATERIALIZED VIEW fast_mv5
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT
    s.sale_id,
    c.customer_name,
    COUNT(*) AS cnt,
    SUM(s.price * s.quantity) AS revenue
FROM
    sales s
JOIN
    (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
GROUP BY
    s.sale_id, c.customer_name;

Partitioned materialized view

Creates a materialized view with a distribution key (sale_id) and a partition key (sale_date). All columns from SELECT * are included even if not listed in mv_definition.

CREATE MATERIALIZED VIEW myview8 (
  quantity INT,
  price DECIMAL(10, 2),
  sale_date TIMESTAMP
)
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(date_format(sale_date, "%Y%m%d")) LIFECYCLE 30
AS
SELECT * FROM sales;

Custom schema definition

Index only specific columns

CREATE MATERIALIZED VIEW myview9 (
  INDEX (sale_date),
  PRIMARY KEY (sale_id)
) DISTRIBUTED BY HASH (sale_id)
REFRESH
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Primary key, distribution key, clustered index, column-based index, and comment

CREATE MATERIALIZED VIEW myview10 (
  quantity INT,
  price DECIMAL(10, 2),
  KEY INDEX_ID(customer_id) COMMENT 'customer',
  CLUSTERED KEY INDEX(sale_id),
  PRIMARY KEY(sale_id, sale_date)
)
DISTRIBUTED BY HASH(sale_id)
COMMENT 'MATERIALIZED VIEW c'
AS
SELECT * FROM sales;

Elastic materialized view

An elastic materialized view uses a job resource group powered by the XIHE engine. Resources are provisioned on demand, which reduces idle compute costs but introduces higher refresh latency.

Use the serverless resource group, refresh daily

CREATE MATERIALIZED VIEW myview11
MV_PROPERTIES='{
  "mv_resource_group": "serverless"
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;

Use the serverless resource group with a maximum of 12 ACUs

Set elastic_job_max_acu to increase the resource limit and improve refresh speed.

CREATE MATERIALIZED VIEW myview12
MV_PROPERTIES='{
  "mv_resource_group": "serverless",
  "mv_refresh_hints": {"elastic_job_max_acu": "12"}
}'
REFRESH COMPLETE ON DEMAND
 START WITH now()
 NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM sales;