Hologres:Manage materialized views by using SQL statements

Materialized views support the following aggregate functions:

• SUM

• COUNT

• AVG

• MIN

• MAX

• RB_BUILD_CARDINALITY_AGG: Only the BIGINT data type is supported. In addition, you must create the roaring bitmap extension.

Precise unique visitor (UV) computing is an operator with high computational complexity and is usually a system performance bottleneck. Hologres supports the RB_BUILD_CARDINALITY_AGG aggregate function. By using the roaring bitmap data structure, Hologres can pre-aggregate data of the BIGINT type into materialized views. The BIGINT type is usually used by business ID fields. This implements real-time deduplication of UV statistics. You can create materialized views by executing the following statements. Only fields of the BIGINT type are supported for aggregation and deduplication.

-- UV computing depends on the roaring bitmap data type. You must create the roaring bitmap extension in advance.
CREATE EXTENSION if not exists roaringbitmap;

BEGIN;
CREATE TABLE base_sales_r(
  day text not null,
  hour int ,
  ts timestamptz,
  amount float,
  userid bigint,
  pk text not null primary key
);
CALL SET_TABLE_PROPERTY('base_sales_r', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_r AS
  SELECT
    day,
    hour,
    avg(amount) AS amount_avg,
    rb_build_cardinality_agg(userid) as user_count
  FROM base_sales_r
  GROUP BY day, hour;

COMMIT;

insert into base_sales_r values(to_char(now(),'YYYYMMDD'),'12',now(),100,1,'pk1');
insert into base_sales_r values(to_char(now(),'YYYYMMDD'),'12',now(),200,2,'pk2');
insert into base_sales_r values(to_char(now(),'YYYYMMDD'),'12',now(),300,3,'pk3');
select user_count as UV from mv_sales_r where day = to_char(now(),'YYYYMMDD') AND hour = 12;

The RB_BUILD_CARDINALITY_AGG aggregate function is used to compute the UV value. In the mv_sales_r view, the user_count field stores the UV value for the userid field. You can query the user_count field to obtain the UV value.

In this example, the mv_sales materialized view is used. The following table describes the data that the base_sales base table contains.

The following result is returned if you directly query the sales_mv materialized view:

postgres=> select * from mv_sales;
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     3
  20210101 |    13   |     6

If you query the materialized view from an aggregation dimension, incorrect results are returned. For example, if you use the AVG function to aggregate data in the materialized view by day, an incorrect result is returned. This is because the average of average values does not equal the average of original values.

postgres=> select day, avg(amount_avg) from mv_sales group by day;
    day    |   avg
-----------+--------
  20210101 |   4.5

In this case, you can create another materialized view that is aggregated by day. However, this will cause the number of materialized views to expand. Hologres allows you to use only one materialized view to implement aggregate queries in different dimensions based on the aggregation result in the intermediate state. In the following example, the AVG aggregate function is used. Execute the following modified DDL statements to create the materialized view:

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');

CREATE MATERIALIZED VIEW mv_sales_partial AS
  SELECT
    day,
    hour,
    avg(amount) as avg,
    avg_partial(amount) AS amt_avg_partial
  FROM base_sales
  GROUP BY day, hour;

COMMIT;

As shown in the statements, the AVG aggregate function is replaced with the AVG_PARTIAL aggregate function. The amount_avg_partial column stores the aggregation result in the intermediate state. You can use the AVG_FINAL aggregate function to query the final aggregation of the aggregate result in the intermediate state.

postgres=> select day, avg(avg) as avg_avg, avg_final(amt_avg_partial) as real_avg from mv_sales_partial group by day;
    day    |   avg_avg |  real_avg
-----------+-----------+----------
  20210101 |    4.5    |     4

The following table describes the aggregate functions and their partial and final aggregate functions that are supported.

A TTL is configured for the base_sales_table base table, and some data has expired. If you query the base table, the following query result is returned:

postgres=> SELECT
    day,
    hour,
    avg(amount) AS amount_avg
  FROM base_sales
  GROUP BY day, hour;

-- Query result
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     4
  20210101 |    13   |     6

However, the expired data has been materialized into the materialized view. The following query result may be returned if you query the materialized view:

postgres=> select * from mv_sales;
-- Query result
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     3
  20210101 |    13   |     6

In this case, the query results are inconsistent. We recommend that you make the following improvements:

• Do not set a TTL for the base table.

• If a TTL is set for the base table and a time field is contained in the GROUP BY key of a materialized view, do not query the materialized view for data that is about to expire.

• Create the base table as a partitioned table. Do not set a TTL for the base table. Delete expired data by deleting child partitioned tables.

You do not need to explicitly specify the materialized view to query. You can perform queries based on the base table as before. If matching materialized views exist, the optimizer intelligently routes queries to the most appropriate materialized view to accelerate the queries. Hologres selects a materialized view based on the following rules:

• Hologres selects the materialized view that contains all queried columns or the columns that can be used to calculate the values of the queried columns.

• Hologres selects the materialized view whose GROUP BY key contains all the columns specified in the GROUP BY key of the original query.

• If multiple materialized views meet the requirements, Hologres selects the materialized view that contains the fewest fields in the GROUP BY key.

The aggregate functions that support intelligent routing include SUM, COUNT, MIN, and MAX.