AnalyticDB:Refresh materialized views

Enable the binary logging feature

Before you create a materialized view that supports fast refresh in an AnalyticDB for MySQL cluster, you must enable the binary logging feature for the cluster and base tables.

SET ADB_CONFIG BINLOG_ENABLE=true; --For AnalyticDB for MySQL clusters earlier than V3.2.0.0, you must execute the statement to enable the binary logging feature. For AnalyticDB for MySQL clusters of V3.2.0.0 or later, the binary logging feature is automatically enabled. 
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 named fast_mv1 that supports fast refresh at 10-second intervals. The materialized view query does not involve an aggregate function.

  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 named fast_mv2 that supports fast refresh at 5-second intervals. The materialized view query involves an aggregate function with GROUP BY.

  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;

• Create a single-table materialized view named fast_mv3 that supports fast refresh at 1-minute intervals. The materialized view query involves an aggregate function without GROUP BY.

  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 materialized views

• Create a multi-table materialized view named fast_mv4 that supports fast refresh at 5-second intervals. The materialized view query does not involve an aggregate function.

  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 named fast_mv5 that supports fast refresh at 10-second intervals. The materialized view query involves an aggregate function with GROUP BY.

  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;

Limits

When you adjust the number of reserved resource nodes in an AnalyticDB for MySQL cluster, you cannot perform complete refresh operations on materialized views because complete refresh runs in an asynchronous manner. However, fast refresh operations are supported for materialized views.

• You cannot use partitioned tables as base tables to create a materialized view that supports fast refresh in AnalyticDB for MySQL clusters earlier than V3.2.3.0.

• You cannot execute the INSERT OVERWRITE or TRUNCATE statement on base tables of a materialized view that supports fast refresh in AnalyticDB for MySQL clusters earlier than V3.2.3.1. Otherwise, an error occurs.

• Materialized views that support fast refresh can only be automatically refreshed at scheduled intervals, and cannot be manually refreshed. The scheduled interval of fast refresh ranges from 5 seconds to 5 minutes.

• The following limits are imposed on materialized view queries:

  • Materialized views must provide the same data as the query results on base tables and must support all DML operations. In this case, not all materialized view queries allow materialized views to support fast refresh. If the materialized view that you want to create does not support fast refresh, an error occurs.

  • You cannot specify expressions that may produce indefinite values as conditions in materialized view queries. Examples: now() and rand().

  • Materialized view queries support only the following aggregate functions: COUNT(), SUM(), MAX(), MIN(), AVG(), APPROX_DISTINCT(), and COUNT(DISTINCT).

  • If you use the MAX(), MIN(), APPROX_DISTINCT(), or COUNT(DISTINCT) function in materialized view queries, you can perform only INSERT operations on base tables. You cannot perform operations that may result in data deletion on base tables, such as DELETE, UPDATE, REPLACE, and INSERT ON DUPLICATE KEY UPDATE.

  • Aggregate functions except COUNT(DISTINCT) do not support the DISTINCT keyword in materialized view queries.

  • The COUNT(DISTINCT) function supports only the INTEGER type in materialized view queries.

  • The AVG() function does not support the DECIMAL type in materialized view queries.

  • Aggregate functions do not support the HAVING keyword in materialized view queries.

  • Materialized view queries do not support window functions.

  • Materialized view queries do not support sorting operations.

  • Materialized view queries do not support set operations, such as UNION, EXCEPT, and INTERSECT.

• The following limits are imposed on multi-table materialized views that support fast refresh:

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

  • By default, you can join up to five base tables to create a materialized view. To create a materialized view that references more than five base tables, contact technical support to increase the maximum number of base tables based on your cluster specifications.

  • The fields that are used to join base tables must be the original fields of the tables and must work as indexes. The fields must be of the same data type.