AnalyticDB:Create a materialized view

Enable binary logging

Incremental refresh relies on binary logging to track changes in base tables. Enable it at the cluster level and for each base table:

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

If enabling binary logging fails, see Can not create FAST materialized view.

Create a single-table fast materialized view

The following example creates a fast materialized view named sales_mv_incre on the sales table with an auto-refresh interval of 3 minutes:

CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_id;

Create a multi-table fast materialized view

On clusters running V3.2.1.0 or later, fast materialized views can join multiple tables. The following example joins customer and sales:

CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  customer.customer_id,
  SUM(sales.price) AS price
FROM customer
INNER JOIN (SELECT customer_id, price FROM sales) sales
  ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

For the complete CREATE MATERIALIZED VIEW syntax, see CREATE MATERIALIZED VIEW.

SELECT column rules

The columns in the SELECT list must follow these rules:

• With GROUP BY and aggregate functions: Include all GROUP BY columns in the SELECT list.

  View examples

  Correct example (all GROUP BY columns included):

  CREATE MATERIALIZED VIEW demo_mv1
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,    -- GROUP BY column
    sale_date,  -- GROUP BY column
    max(quantity) AS max,  -- expression columns require aliases
    sum(price) AS sum
  FROM sales
  GROUP BY sale_id, sale_date;

  Incorrect (missing GROUP BY column sale_date):

  CREATE MATERIALIZED VIEW false_mv1
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales
  GROUP BY sale_id, sale_date;  -- sale_date is in GROUP BY but not in SELECT

• With aggregate functions and no GROUP BY: The SELECT list may contain only aggregate columns, or only constant and aggregate columns.

  View examples

  -- Aggregate columns only
  CREATE MATERIALIZED VIEW demo_mv2
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales;
  
  -- Constant plus aggregate columns (the constant becomes the primary key)
  CREATE MATERIALIZED VIEW demo_mv3
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    1 AS pk,
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales;

• Without aggregation: Include all primary key columns of the base table.

  View examples

  -- Single primary key
  CREATE MATERIALIZED VIEW demo_mv4
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,   -- primary key column of base table sales
    quantity
  FROM sales;
  
  -- Composite primary key: PRIMARY KEY(sale_id, sale_date)
  CREATE MATERIALIZED VIEW demo_mv5
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,    -- first primary key column
    sale_date,  -- second primary key column
    quantity
  FROM sales1;

• UNION ALL queries: Each branch must output a column named union_all_marker with a distinct constant value per branch. Include all base table primary key columns. The materialized view primary key must include both the base table primary key columns and union_all_marker.

  CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker))
  REFRESH FAST NEXT now() + INTERVAL 5 minute
  AS
  SELECT customer_id AS id, "customer" AS union_all_marker
  FROM customer
  UNION ALL
  SELECT sale_id AS id, "sales" AS union_all_marker
  FROM sales;

• Expression columns: All expression columns in the SELECT list must have aliases, for example SUM(price) AS total_price.

General limits

These limits apply to all materialized views.

• You cannot run INSERT, DELETE, or UPDATE on a materialized view.

• You cannot drop or rename a base table or its columns while a materialized view references them. Drop the materialized view first, then modify the base table.

• Maximum materialized views per cluster:

  • V3.1.4.7 or later: 64

  • Earlier than V3.1.4.7: 8

To raise the quota, contact technical support.

Limits on complete-refresh materialized views

When you add or remove reserved nodes, asynchronous jobs are disabled. Because complete refresh is an asynchronous job, it cannot run during node scaling. Fast refresh is not affected.

Limits on fast (incremental) materialized views

See Incremental MV query constraints for the full list of SQL restrictions.

Refresh trigger: Only scheduled auto-refresh is supported. The interval must be between 5 seconds and 5 minutes.

How do I keep only the most recent year of data in a materialized view?

Use a date column as the partition key (PARTITION BY) and set a LIFECYCLE value to limit how many partitions are retained. For a view partitioned by day, set LIFECYCLE 365 to keep the most recent 365 partitions (one year).

Example: the sales table receives new records daily, partitioned by sale_date:

CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT
  sale_date,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_date;

Query execution error: Can not create FAST materialized view, because *demotable* doesn't support getting incremental data

Binary logging is not enabled for the base table demotable. Enable it with:

ALTER TABLE demotable binlog=true;

If you see XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now, the base table uses the XUANWU_V2 engine and the cluster runs a kernel version earlier than V3.2.6.0. XUANWU_V2 supports binary logging from kernel version V3.2.6.0. Upgrade the cluster to V3.2.6.0 or later, and then run ALTER TABLE demotable binlog=true; again to enable binary logging on the base table.

Query execution error: PRIMARY KEY *id* must output to MV

Your fast materialized view uses a non-aggregate query without GROUP BY. In this case, the SELECT list must include all primary key columns of the base table.

Incorrect (missing sale_id, which is the primary key of sales):

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id, price
FROM sales;

Add the primary key column:

CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Query execution error: MV PRIMARY KEY must be equal to base table PRIMARY KEY

Your fast materialized view uses a non-aggregate query without GROUP BY, and the materialized view primary key definition includes columns that are not part of the base table primary key.

Incorrect (product_id is not a primary key column of sales):

CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id, product_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Remove non-primary-key columns from the primary key definition:

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Query execution error: FAST materialized view must define PRIMARY KEY

This error has two possible causes:

• No valid primary key defined. Update the materialized view definition to meet these rules:

  • Grouped aggregate query (with GROUP BY): the primary key must be the GROUP BY columns (for example, if GROUP BY a, b, set PRIMARY KEY(a, b)).

  • Non-grouped aggregate query (without GROUP BY): the primary key must be a constant.

  • Non-aggregate query: the primary key must exactly match the base table primary key (for example, if the base table has PRIMARY KEY(sale_id, sale_date), the view must also have PRIMARY KEY(sale_id, sale_date)).

• A function is applied to a primary key column. Remove the function from the primary key column in the query.

Query execution error: The join graph is not supported

Join columns have mismatched data types. For example, if customer.id and sales.id have different types, the join fails. Align the types with:

ALTER TABLE tablename MODIFY COLUMN columnname newtype;

For more information, see Change the data type of a column.

Query execution error: Unable to use index join to refresh this fast MV

Join columns lack indexes. Add an index on each join column:

ALTER TABLE tablename ADD KEY idx_name(columnname);

For more information, see Create an index.

Query execution error: Query exceeded reserved memory limit

The query exceeded the per-node memory limit. Use the SQL diagnostics feature to identify high-memory stages and operators (Aggregation, TopN, Window, and Join are the common culprits), then optimize those operators. See also Memory metrics and Use stage and task details to analyze queries.