Hologres:MATERIALIZED VIEW

Limits

Only Hologres V1.3 and later support materialized views. If the version of your instance is earlier than V1.3, manually upgrade your Hologres instance in the Hologres console or join a DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

Create a materialized view

• Syntax

  You can execute the following statement to create a materialized view:

  CREATE  MATERIALIZED VIEW <mv_name>
  AS
  <QUERY BODY>;

  The mv_name parameter specifies the name of the custom materialized view, and the QUERY BODY parameter specifies a query statement. For more information about the query statement, see SELECT.

• Examples

  • Create a materialized view for a non-partitioned table.

    BEGIN;
    CREATE TABLE base_sales(
      day text not null,
      hour int ,
      ts timestamptz,
      amount float,
      pk text not null primary key
    );
    CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'appendonly');
    
    -- If the materialized view is deleted, you can execute the following statement to cancel the appendonly property for the table:
    --CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'none');
    
    CREATE MATERIALIZED VIEW mv_sales AS
      SELECT
        day,
        hour,
        avg(amount) AS amount_avg
      FROM base_sales
      GROUP BY day, hour;
    
    COMMIT;
    
    insert into base_sales values(to_char(now(),'YYYYMMDD'),'12',now(),100,'pk1');
    insert into base_sales values(to_char(now(),'YYYYMMDD'),'12',now(),200,'pk2');
    insert into base_sales values(to_char(now(),'YYYYMMDD'),'12',now(),300,'pk3');

  • Create a materialized view for a partitioned table.

    BEGIN;
    CREATE TABLE base_sales_p(
      day text not null,
      hour int,
      ts timestamptz,
      amount float,
      pk text not null,
      primary key (day, pk)
    ) partition by list(day);
    CALL SET_TABLE_PROPERTY('base_sales_p', 'mutate_type', 'appendonly');
    
    -- day is a partition key column. It must be included in the condition of the GROUP BY clause of the materialized view.
    CREATE MATERIALIZED VIEW mv_sales_p AS
      SELECT
        day,
        hour,
        avg(amount) AS amount_avg
      FROM base_sales_p
      GROUP BY day, hour;
    COMMIT;
    
    create table base_sales_20220101 partition of base_sales_p for values in('20220101');

Query the materialized view

SELECT * FROM mv_sales WHERE day = to_char(now(),'YYYYMMDD') AND hour = 12;

Delete the materialized view

• Syntax

  You can execute the following SQL statement to delete the materialized view:

  DROP MATERIALIZED VIEW <mv_name>;

  The mv_name parameter specifies the name of the materialized view.

• Example

  DROP MATERIALIZED VIEW mv_sales;