Hologres:SQL ステートメントを使用したマテリアライズドビューの管理

マテリアライズドビューは、次の集計関数をサポートしています。

• SUM

• COUNT

• AVG

• MIN

• MAX

• RB_BUILD_CARDINALITY_AGG：BIGINT データ型のみがサポートされています。さらに、roaring bitmap エクステンションを作成する必要があります。

正確なユニークビジター（UV）計算は、計算の複雑さが高い演算子であり、通常はシステムパフォーマンスのボトルネックになります。Hologres は、RB_BUILD_CARDINALITY_AGG 集計関数をサポートしています。roaring bitmap データ構造を使用することにより、Hologres は BIGINT 型のデータをマテリアライズドビューに事前に集計できます。BIGINT 型は通常、ビジネス ID フィールドで使用されます。これにより、UV 統計のリアルタイム重複除外が実装されます。次のステートメントを実行して、マテリアライズドビューを作成できます。集計と重複除外には、BIGINT 型のフィールドのみがサポートされています。

-- UV 計算は roaring bitmap データ型に依存します。事前に roaring bitmap エクステンションを作成する必要があります。
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
);
-- ベーステーブルに appendonly プロパティを設定します。
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;

RB_BUILD_CARDINALITY_AGG 集計関数は、UV 値を計算するために使用されます。mv_sales_r ビューでは、user_count フィールドに userid フィールドの UV 値が格納されます。user_count フィールドをクエリして、UV 値を取得できます。

この例では、mv_sales マテリアライズドビューを使用します。次の表に、base_sales ベーステーブルに含まれるデータを示します。

sales_mv マテリアライズドビューを直接クエリすると、次の結果が返されます。

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

集計ディメンションからマテリアライズドビューをクエリすると、正しくない結果が返されます。たとえば、AVG 関数を使用して 日 ごとにマテリアライズドビューのデータを集計すると、正しくない結果が返されます。これは、平均値の平均が元の値の平均と等しくないためです。

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

この場合、日ごとに集計される別のマテリアライズドビューを作成できます。ただし、これにより、マテリアライズドビューの数が拡張されます。Hologres では、中間状態の集計結果に基づいて、1 つのマテリアライズドビューのみを使用して異なるディメンションで集計クエリを実装できます。次の例では、AVG 集計関数を使用します。次の変更された DDL ステートメントを実行して、マテリアライズドビューを作成します。

BEGIN;
CREATE TABLE base_sales(
  day text not null,
  hour int ,
  ts timestamptz,
  amount float,
  pk text not null primary key
);
-- ベーステーブルに appendonly プロパティを設定します。
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;

ステートメントに示されているように、AVG 集計関数は AVG_PARTIAL 集計関数に置き換えられます。amount_avg_partial 列には、中間状態の集計結果が格納されます。AVG_FINAL 集計関数を使用して、中間状態の集計結果の最終集計をクエリできます。

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

次の表に、サポートされている集計関数と、それらの部分集計関数と最終集計関数を示します。

base_sales_table ベーステーブルに TTL が設定されており、一部のデータの期限が切れています。ベーステーブルをクエリすると、次のクエリ結果が返されます。

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

-- クエリ結果
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     4
  20210101 |    13   |     6

ただし、期限切れのデータはマテリアライズドビューにマテリアライズされています。マテリアライズドビューをクエリすると、次のクエリ結果が返される場合があります。

postgres=> select * from mv_sales;
-- クエリ結果
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     3
  20210101 |    13   |     6

この場合、クエリ結果に不整合が生じます。次の改善を行うことをお勧めします。

• ベーステーブルに TTL を設定しないでください。

• ベーステーブルに TTL が設定されていて、マテリアライズドビューの GROUP BY キーに時間フィールドが含まれている場合は、マテリアライズドビューから期限切れ間近のデータをクエリしないでください。

• ベーステーブルをパーティションテーブルとして作成します。ベーステーブルに TTL を設定しないでください。子パーティションテーブルを削除することで、期限切れのデータを削除します。

クエリ対象のマテリアライズドビューを明示的に指定する必要はありません。以前と同様に、ベーステーブルに基づいてクエリを実行できます。一致するマテリアライズドビューが存在する場合、オプティマイザーはクエリを最適なマテリアライズドビューにインテリジェントにルーティングして、クエリを高速化します。Hologres は、次のルールに基づいてマテリアライズドビューを選択します。

• Hologres は、クエリされたすべての列、またはクエリされた列の値の計算に使用できる列を含むマテリアライズドビューを選択します。

• Hologres は、GROUP BY キーに元のクエリの GROUP BY キーで指定されたすべての列が含まれているマテリアライズドビューを選択します。

• 複数のマテリアライズドビューが要件を満たしている場合、Hologres は GROUP BY キーに含まれるフィールド数が最も少ないマテリアライズドビューを選択します。

インテリジェントルーティングをサポートする集計関数には、SUM、COUNT、MIN、MAX が含まれます。