AnalyticDB:Refresh materialized views

Enable binary logging

Before you create a fast-refresh materialized view, enable binary logging for both the cluster and the base tables.

SET ADB_CONFIG BINLOG_ENABLE=true; --For clusters earlier than V3.2.0.0, run this statement manually. For V3.2.0.0 or later, binary logging is automatically enabled.
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;
ALTER TABLE product binlog=true;

Single-table views

No aggregate function -- refresh every 10 seconds:

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

Aggregate with GROUP BY -- refresh every 5 seconds:

CREATE MATERIALIZED VIEW fast_mv2
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   customer_id, sale_date,                 -- The system uses the GROUP BY columns as the primary key of the materialized view.
   COUNT(sale_id) AS cnt_sale_id,          -- Perform an aggregate operation on a column.
   SUM(price * quantity) AS total_revenue, -- Perform an aggregate operation on a column.
   customer_id / 100 AS new_customer_id    -- You can use an expression to define columns that are not involved in aggregate operations.
FROM sales
WHERE ifnull(price, 1) > 0                 -- You can use an expression to define WHERE conditions.
GROUP BY customer_id, sale_date;

Aggregate without GROUP BY -- refresh every 1 minute:

CREATE MATERIALIZED VIEW fast_mv3
REFRESH FAST NEXT now() + INTERVAL 1 minute
AS
SELECT count(*) AS cnt   -- The system generates a constant as the primary key to ensure that only one record is contained in the materialized view.
FROM sales;

Multi-table views

No aggregate function -- refresh every 5 seconds:

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

Aggregate with GROUP BY -- refresh every 10 seconds:

CREATE MATERIALIZED VIEW fast_mv5
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT
    s.sale_id,
    c.customer_name,
    p.product_name,
    COUNT(*) AS cnt,
    SUM(s.price * s.quantity) AS revenue,
    SUM(p.unit_price) AS sum_p
FROM
    sales s
JOIN
    (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
JOIN
    (SELECT * FROM product WHERE stock_quantity > 0) p ON p.product_id = s.product_id
GROUP BY
    s.sale_id, c.customer_name, p.product_name;