Refresh materialized views

Enable binary logging

Before you create a materialized view that uses fast refresh, you must enable binary logging for the cluster and its base tables.

SET ADB_CONFIG BINLOG_ENABLE=true; -- For clusters with an engine version before 3.2.0.0, run this command to enable binary logging. It is enabled by default in 3.2.0.0 and later.
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;
ALTER TABLE product binlog=true;

Single-table materialized views

• Create a single-table materialized view fast_mv1 that uses fast refresh without an aggregate operation and refreshes 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;

• Create a single-table materialized view fast_mv2 that uses fast refresh with a GROUP BY aggregation and refreshes 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,          -- Aggregate output column.
     SUM(price * quantity) AS total_revenue, -- Aggregate output column.
     customer_id / 100 AS new_customer_id    -- Non-aggregate output columns can use any expression.
  FROM sales
  WHERE ifnull(price, 1) > 0                 -- Conditions can use any expression.
  GROUP BY customer_id, sale_date;

• Create a single-table materialized view fast_mv3 that uses fast refresh without a GROUP BY aggregation and refreshes every minute.

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

Multi-table materialized views

• Create a multi-table materialized view fast_mv4 that uses fast refresh without an aggregate operation and refreshes 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;

• Create a multi-table materialized view fast_mv5 that uses fast refresh with a GROUP BY aggregation and refreshes 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;

Limitations

The following limitations apply to materialized views that use fast refresh:

• For clusters with an engine version earlier than 3.2.3.0, a partitioned table cannot be a base table for a materialized view that uses fast refresh.

• For clusters with an engine version earlier than 3.2.3.1, INSERT OVERWRITE and TRUNCATE operations are not supported on the base tables of a materialized view that uses fast refresh. Running these operations causes an error.

• Fast refresh supports only scheduled refreshes with an interval between 5 seconds (s) and 5 minutes (min).

• The query_body of a materialized view that uses fast refresh has the following limitations:

  • A materialized view must guarantee that its results are identical to a direct query on its base tables and must support all DML changes. If the query_body does not support fast refresh, the CREATE MATERIALIZED VIEW statement returns an error.

  • Conditions cannot contain non-deterministic expressions, such as now() or rand().

  • Only the following aggregate functions are supported: COUNT, SUM, MAX, MIN, AVG, APPROX_DISTINCT, and COUNT(DISTINCT).

  • When the query_body uses the MAX, MIN, APPROX_DISTINCT, or COUNT(DISTINCT) aggregate function, only INSERT operations are allowed on the base tables. Data-deleting operations, such as DELETE, UPDATE, REPLACE, and INSERT ON DUPLICATE KEY UPDATE, are prohibited.

  • The DISTINCT keyword is not supported for aggregate functions other than COUNT(DISTINCT).

  • COUNT(DISTINCT) supports only the INTEGER type.

  • AVG does not support the DECIMAL type.

  • The HAVING keyword is not supported for aggregate operations.

  • Window functions are not supported.

  • Sorting operations are not supported.

  • Set operations such as UNION, EXCEPT, and INTERSECT are not supported.

• Multi-table materialized views that use fast refresh have the following additional limitations:

  • Multi-table materialized views currently support only INNER JOIN.

  • By default, a multi-table materialized view can be joined with a maximum of five tables.

  • The join columns in a multi-table materialized view must be original columns from the tables, have the same data type, and each join column must have an index.