Hologres：Kelola Tampilan Materialisasi Menggunakan Pernyataan SQL

Tampilan materialisasi mendukung fungsi agregat berikut:

• SUM

• COUNT

• AVG

• MIN

• MAX

• RB_BUILD_CARDINALITY_AGG: Hanya mendukung tipe data BIGINT. Selain itu, Anda harus membuat ekstensi bitmap roaring.

Komputasi pengunjung unik (UV) presisi adalah operator dengan kompleksitas komputasi tinggi dan sering kali menjadi hambatan kinerja sistem. Hologres mendukung fungsi agregat RB_BUILD_CARDINALITY_AGG. Dengan menggunakan struktur data bitmap roaring, Hologres dapat mengumpulkan data tipe BIGINT ke dalam tampilan materialisasi. Tipe BIGINT biasanya digunakan oleh bidang ID bisnis. Ini mengimplementasikan deduplikasi real-time statistik UV. Anda dapat membuat tampilan materialisasi dengan mengeksekusi pernyataan berikut. Hanya bidang tipe BIGINT yang didukung untuk agregasi dan deduplikasi.

-- Komputasi UV bergantung pada tipe data bitmap roaring. Anda harus membuat ekstensi bitmap roaring 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 agregat RB_BUILD_CARDINALITY_AGG digunakan untuk menghitung nilai UV. Di dalam tampilan mv_sales_r, bidang user_count menyimpan nilai UV untuk bidang userid. Anda dapat menanyakan bidang user_count untuk mendapatkan nilai UV.

Dalam contoh ini, tampilan materialisasi mv_sales digunakan. Tabel berikut menggambarkan data yang terkandung dalam tabel dasar base_sales.

Hasil berikut dikembalikan jika Anda langsung menanyakan tampilan materialisasi sales_mv:

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

Jika Anda menanyakan tampilan materialisasi dari dimensi agregasi, hasil yang salah akan dikembalikan. Sebagai contoh, jika Anda menggunakan fungsi AVG untuk mengumpulkan data dalam tampilan materialisasi berdasarkan hari, hasil yang salah akan dikembalikan. Hal ini karena rata-rata dari nilai rata-rata tidak sama dengan rata-rata dari nilai asli.

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

Dalam kasus ini, Anda dapat membuat tampilan materialisasi lain yang di-agregasi berdasarkan hari. Namun, ini akan meningkatkan jumlah tampilan materialisasi. Hologres memungkinkan Anda menggunakan satu tampilan materialisasi untuk mengimplementasikan query agregat dalam dimensi berbeda berdasarkan hasil agregasi dalam keadaan antara. Dalam contoh berikut, fungsi agregat AVG digunakan. Eksekusi pernyataan DDL yang dimodifikasi berikut untuk membuat tampilan materialisasi:

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;

Seperti yang ditunjukkan dalam pernyataan, fungsi agregat AVG diganti dengan fungsi agregat AVG_PARTIAL. Kolom amount_avg_partial menyimpan hasil agregasi dalam keadaan antara. Anda dapat menggunakan fungsi agregat AVG_FINAL untuk menanyakan agregasi akhir dari hasil agregasi dalam keadaan antara.

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

Tabel berikut menggambarkan fungsi agregat dan fungsi agregat parsial serta akhirnya yang didukung.

TTL diatur untuk tabel dasar base_sales_table, dan beberapa data telah kedaluwarsa. Jika Anda menanyakan tabel dasar, hasil query berikut dikembalikan:

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

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

Namun, data yang kedaluwarsa telah dimaterialisasi ke dalam tampilan materialisasi. Hasil query berikut mungkin dikembalikan jika Anda menanyakan tampilan materialisasi:

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

Dalam kasus ini, hasil query tidak konsisten. Kami merekomendasikan langkah-langkah berikut:

• Jangan atur TTL untuk tabel dasar.

• Jika TTL diatur untuk tabel dasar dan bidang waktu termasuk dalam kunci GROUP BY dari tampilan materialisasi, jangan menanyakan tampilan materialisasi untuk data yang akan kedaluwarsa.

• Buat tabel dasar sebagai tabel partisi. Jangan atur TTL untuk tabel dasar. Hapus data yang kedaluwarsa dengan menghapus tabel partisi anak.

Anda tidak perlu secara eksplisit menentukan tampilan materialisasi untuk di-query. Anda dapat melakukan query berdasarkan tabel dasar seperti biasa. Jika tampilan materialisasi yang cocok tersedia, pengoptimal secara cerdas merutekan query ke tampilan materialisasi yang paling sesuai untuk mempercepat query. Hologres memilih tampilan materialisasi berdasarkan aturan berikut:

• Hologres memilih tampilan materialisasi yang berisi semua kolom yang di-query atau kolom yang dapat digunakan untuk menghitung nilai kolom yang di-query.

• Hologres memilih tampilan materialisasi yang kunci GROUP BY-nya berisi semua kolom yang ditentukan dalam kunci GROUP BY dari query asli.

• Jika beberapa tampilan materialisasi memenuhi persyaratan, Hologres memilih tampilan materialisasi yang berisi bidang paling sedikit dalam kunci GROUP BY.

Fungsi agregat yang mendukung perutean cerdas termasuk SUM, COUNT, MIN, dan MAX.