Perhitungan UV dalam periode waktu arbitrer yang panjang menggunakan Dynamic Table dan RoaringBitmap - Hologres

Untuk skenario bisnis dengan volume data besar serta persyaratan ketat terkait queries per second (QPS) dan latensi, kombinasikan Hologres RoaringBitmap dengan Dynamic Table guna melakukan komputasi bertahap dan memungkinkan perhitungan UV fleksibel dalam periode waktu apa pun. Topik ini menjelaskan pendekatan implementasi untuk dua tipe bidang: INT dan TEXT.

Ikhtisar solusi

Solusi ini menawarkan keunggulan berikut: Dynamic Table menggunakan Pembaruan bertahap untuk memproses hanya data baru setiap kali, sehingga memberikan performa tinggi dengan konsumsi sumber daya rendah untuk perhitungan UV ber-QPS tinggi dan latensi rendah. Solusi ini tidak memerlukan tugas penjadwalan tambahan karena Dynamic Table menangani penyegaran secara otomatis. Kueri bersifat fleksibel dan mendukung rentang waktu apa pun. Pendekatan ini cocok untuk perhitungan UV ber-QPS tinggi dalam periode waktu arbitrer yang panjang pada data berskala besar (ratusan juta catatan).

Bergantung pada skenario bisnis dan tipe data Anda, implementasikan RoaringBitmap dengan salah satu dari dua cara berikut:

Metode 1: Perhitungan UV periode panjang untuk bidang INT — Paling sesuai untuk deduplikasi eksak ketika UID bertipe INT. Dikombinasikan dengan Dynamic Table, metode ini mendukung operasi irisan, union, dan selisih pada kelompok pengguna berdasarkan tag atau properti apa pun.
Metode 2: Bidang TEXT dengan tabel pemetaan — Paling sesuai ketika UID bertipe TEXT. Memerlukan tabel pemetaan pengguna untuk mengonversi UID TEXT menjadi bilangan bulat.

Metode 1: Perhitungan UV periode panjang RoaringBitmap untuk bidang INT

Gunakan metode ini untuk perhitungan UV ber-QPS tinggi dalam periode waktu arbitrer yang panjang pada data berskala besar (ratusan juta catatan) ketika bidang UID bertipe int.

Alur kerja

Buat tabel detail pengguna untuk menyimpan data granular di seluruh dimensi bisnis.
Buat Dynamic Table berdasarkan logika bisnis Anda untuk memproses tabel detail secara bertahap. Kelompokkan berdasarkan dimensi dasar dan agregasikan UID ke dalam RoaringBitmap yang disimpan di Dynamic Table.
Kueri Dynamic Table berdasarkan dimensi yang diinginkan. Terapkan RB_OR_AGG pada bidang RoaringBitmap untuk menghapus duplikat, lalu hitung kardinalitas untuk UV dan jumlah untuk PV—menghasilkan respons kueri dalam waktu kurang dari satu detik.

Persiapkan data dasar

Buat ekstensi RoaringBitmap sebelum digunakan:

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

Buat tabel detail pengguna (disarankan dipartisi per hari). Contoh berikut menunjukkan pembuatan tabel dan pengaturan properti:

DROP TABLE IF EXISTS ods_app_detail;
BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
  uid int,
  country text,
  prov text,
  city text,
  ymd text NOT NULL
)
LOGICAL PARTITION BY LIST (ymd);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,ymd');
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

Buat Dynamic Table untuk memproses data

Buat Dynamic Table yang mengagregasi tabel detail berdasarkan dimensi dan menghitung RoaringBitmap menggunakan RB_BUILD_AGG pada uid. Konfigurasikan Pembaruan bertahap. Contoh berikut hanya menyegarkan partisi terbaru dengan freshness data 5 menit:

CREATE DYNAMIC TABLE dt_dws_app_rb
LOGICAL PARTITION BY LIST(ymd)
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '1 days',
  partition_key_time_format = 'YYYYMMDD'
)
AS
SELECT
  RB_BUILD_AGG(uid) AS rb_uid,
  country,
  prov,
  city,
  ymd,
  COUNT(1) AS pv
FROM ods_app_detail
GROUP BY country, prov, city, ymd;

Lakukan REFRESH manual pada partisi historis. Contohnya:

REFRESH DYNAMIC TABLE public.dt_dws_app_rb PARTITION(20251201) WITH (refresh_mode = 'full');

Kueri UV dalam periode waktu arbitrer yang panjang

-- Kueri UV dan PV untuk hari tertentu
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  sum(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;

-- Kueri UV dan PV untuk satu bulan
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  sum(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd >= '20251201' AND ymd <= '20251230'
GROUP BY country, prov, city;

Metode 2: Perhitungan UV periode panjang RoaringBitmap untuk bidang TEXT dengan tabel pemetaan

Gunakan metode ini ketika UID bertipe teks. Gabungkan dengan tabel pemetaan pengguna untuk memetakan UID teks ke bilangan bulat guna pemrosesan RoaringBitmap. Gunakan fungsi hg_id_encoding dalam Dynamic Table untuk secara otomatis menulis dan memperbarui tabel pemetaan.

Buat dan perbarui tabel pemetaan pengguna

BEGIN;
CREATE TABLE uid_mapping (
  uid text NOT NULL,
  uid_int32 serial,
  PRIMARY KEY (uid)
);
CALL set_table_property('uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('uid_mapping', 'orientation', 'row');
COMMIT;

Buat Dynamic Table untuk memproses data

Dalam Dynamic Table, gunakan hg_id_encoding_int4(uid, 'uid_mapping') untuk memetakan UID teks ke bilangan bulat dan secara otomatis mengisi tabel pemetaan. Kemudian agregasikan berdasarkan dimensi ke dalam RoaringBitmap:

CREATE DYNAMIC TABLE dt_dws_app_rb
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '1 days',
  partition_key_time_format = 'YYYYMMDD'
)
LOGICAL PARTITION BY LIST (ymd)
AS
SELECT
  country,
  prov,
  city,
  RB_BUILD_AGG(uid_int4) AS uid_rb,
  ymd
FROM (
  SELECT
    country,
    prov,
    city,
    hg_id_encoding_int4(uid, 'uid_mapping') AS uid_int4,
    ymd
  FROM ods_app_detail
) a
GROUP BY country, prov, city, ymd;

Kueri UV dalam periode waktu arbitrer yang panjang

SELECT
  country,
  prov,
  city,
  RB_CARDINALITY(RB_OR_AGG(uid_rb)) AS uv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;