Hologres:Panduan lengkap mengenai materialized view

Saat ini, materialized view mendukung fungsi agregat berikut.

• SUM

• COUNT

• AVG

• MIN

• MAX

• RB_BUILD_CARDINALITY_AGG (Hanya mendukung tipe data BIGINT. Ekstensi roaringbitmap harus dibuat terlebih dahulu.)

Perhitungan unique visitor (UV) presisi merupakan operator dengan kompleksitas komputasi tinggi dan sering menjadi bottleneck performa sistem. Hologres mendukung fungsi agregat RB_BUILD_CARDINALITY_AGG. Dengan menggunakan struktur data RoaringBitmap, Anda dapat melakukan pra-agregasi data BIGINT—yang biasanya merepresentasikan bidang ID bisnis—ke dalam materialized view. Proses ini mencapai deduplikasi real-time untuk statistik UV. Anda dapat membuat materialized view sebagai berikut. Saat ini, hanya agregasi dan deduplikasi untuk bidang BIGINT yang didukung.

-- Perhitungan UV bergantung pada tipe data RoaringBitmap. Ekstensi RoaringBitmap harus dibuat terlebih dahulu.
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;

Fungsi rb_build_cardinality_agg menghitung jumlah nilai unik. Dalam view mv_sales_r, user_count merepresentasikan jumlah nilai unik dari userid. Anda dapat mengkueri bidang user_count untuk memperoleh jumlah nilai unik tersebut.

Asumsikan Anda telah mendefinisikan materialized view mv_sales dan tabel dasar base_sales berisi data berikut.

Kueri langsung pada sales_mv menghasilkan hasil berikut.

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

Jika Anda kemudian mencoba mengubah dimensi agregasi materialized view, misalnya dengan melakukan agregasi avg berdasarkan dimensi day, Anda akan memperoleh hasil yang salah. Hal ini karena rata-rata dari rata-rata tidak sama dengan rata-rata keseluruhan.

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

Salah satu solusinya adalah membuat materialized view lain yang diagregasi berdasarkan day. Namun, hal ini meningkatkan jumlah materialized view. Hologres menyediakan metode berbasis status agregasi antara yang memungkinkan Anda menggunakan satu materialized view untuk melakukan kueri agregat lintas dimensi berbeda. Contoh berikut menggunakan fungsi AVG. Anda dapat memodifikasi definisi view sebagai berikut.

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;

Fungsi agregat avg asli didefinisikan ulang sebagai fungsi agregat avg_partial. Kolom amount_avg_partial menyimpan status antara dari hasil agregasi. Saat menjalankan kueri, modifikasi kueri agar menggunakan fungsi avg_final. Hal ini menunjukkan bahwa kueri sedang melakukan agregasi akhir pada status antara tersebut.

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

Fungsi agregat yang didukung beserta fungsi agregat parsialnya adalah sebagai berikut.

TTL diatur untuk tabel base_sales_table. Jika data direklaim karena TTL-nya kedaluwarsa, kueri pada tabel dasar menghasilkan hasil berikut.

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

--Hasil kueri
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     4
  20210101 |    13   |     6

Namun, karena data yang direklaim telah dimaterialisasi dalam view, kueri pada materialized view mungkin menghasilkan hasil berikut.

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

Hasil kueri tidak konsisten. Solusi berikut direkomendasikan:

• Jangan mengatur TTL untuk tabel detail.

• Atur TTL untuk tabel dasar, tetapi pastikan kunci GROUP BY materialized view mencakup bidang berbasis waktu. Saat mengkueri materialized view, hindari mengkueri data yang mendekati ambang batas kedaluwarsa TTL.

• Buat tabel dasar sebagai tabel partisi dan jangan atur TTL. Reklaim data dengan menghapus partisi anak.

Anda tidak perlu secara eksplisit menentukan nama materialized view dalam kueri Anda. Sebaliknya, Anda dapat langsung mengkueri tabel dasar. Jika terdapat materialized view yang sesuai, pengoptimal secara cerdas mengarahkan kueri ke materialized view yang paling tepat untuk mempercepat kueri. Pengoptimal memilih materialized view berdasarkan kriteria berikut:

• Materialized view berisi semua kolom yang dikueri atau kolom yang nilainya dapat dihitung secara tidak langsung dari kolom yang dikueri.

• Kolom GROUP BY materialized view mencakup semua kolom dari klausa GROUP BY kueri asli.

• Jika beberapa materialized view memenuhi kriteria di atas, pengoptimal memilih yang memiliki jumlah kolom paling sedikit dalam kunci GROUP BY-nya.

Fungsi agregat yang mendukung routing cerdas adalah SUM, COUNT, MIN, dan MAX.