Hologres：Perhitungan UV mendekati waktu nyata berdasarkan pre-aggregasi

Langkah 1: Persiapkan data dasar

1. Buat ekstensi roaring bitmap.

   Sebelum menggunakan roaring bitmaps, Anda harus membuat ekstensi roaring bitmap. Ekstensi dibuat pada level database. Untuk setiap database, Anda hanya perlu membuat ekstensi sekali. Sintaks:

   Catatan

   Anda harus memuat ekstensi roaring bitmap dalam skema public.

   CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2. Buat tabel fakta pengguna.

   Buat tabel fakta pengguna untuk menyimpan data pengguna lengkap dan detail. Biasanya, tabel fakta adalah tabel berukuran besar yang menyimpan data pengguna lengkap dan detail. Kami sarankan Anda membuat tabel fakta yang dipartisi per hari untuk memfasilitasi penulisan dan pembaruan data.

   BEGIN;
   CREATE TABLE IF NOT EXISTS ods_app_detail (
        uid int,
        country text,
        prov text,
        city text,
        channel text,
        operator text,
        brand text,
        ip text,
        click_time text,
        year text,
        month text,
        day text,
        ymd text NOT NULL
   )
   PARTITION BY LIST (ymd) ;-- Konfigurasikan partisi per hari karena tabel fakta berisi sejumlah besar data.
   CALL set_table_property('ods_app_detail', 'orientation', 'column');
   CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
   -- Tentukan kunci distribusi untuk tabel berdasarkan persyaratan query real-time. Data didistribusikan ke shard berdasarkan kunci distribusi.
   CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
   -- Tentukan field yang dapat digunakan dalam klausa WHERE. Kami sarankan Anda mengonfigurasi field yang berisi informasi tahun, bulan, dan tanggal sebagai kunci kluster dan kolom waktu acara.
   CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
   CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
   COMMIT;

3. Buat tabel hasil agregasi untuk roaring bitmaps.

   Buat tabel hasil agregasi untuk menyimpan roaring bitmaps yang telah diagregasi.

   • Hanya jutaan catatan data yang disimpan dalam tabel hasil agregasi untuk setiap hari setelah agregasi. Kami sarankan Anda membuat tabel hasil agregasi tanpa partisi untuk memfasilitasi penulisan balik data. Anda juga dapat membuat tabel hasil agregasi yang dipartisi per bulan atau kuartal. Jika Anda mengonfigurasi partisi per hari, jumlah data dalam setiap partisi kecil, dan sejumlah besar file kecil dihasilkan karena partisi. Ini meningkatkan penggunaan memori.

   • Buat tabel hasil agregasi berdasarkan dimensi seperti negara, provinsi, dan kota, dan konfigurasikan field dimensi sebagai field kunci distribusi. Ini memfasilitasi query berbasis dimensi. Jika lebih dari tiga field ditentukan dalam klausa GROUP BY, kami sarankan Anda mengonfigurasi field yang paling sering digunakan sebagai field kunci distribusi.

   • Konfigurasikan field dimensi query dan field tanggal sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.

   • Konfigurasikan field tanggal sebagai kunci kluster dan kolom waktu acara untuk memfasilitasi penyaringan data.

   Contoh kode:

   BEGIN;
   CREATE TABLE dws_app_rb(
     rb_uid roaringbitmap, -- Perhitungan UV.
     country text,
     prov text,
     city text,
     ymd text NOT NULL, -- Field tanggal.
     pv integer, -- Perhitungan PV.
     PRIMARY key(country, prov, city, ymd)-- Konfigurasikan field dimensi query dan field tanggal sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.
   );
   CALL set_table_property('dws_app_rb', 'orientation', 'column');
   -- Konfigurasikan field tanggal sebagai kunci kluster dan kolom waktu acara untuk memfasilitasi penyaringan data.
   CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
   CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
   -- Konfigurasikan field yang ditentukan dalam klausa GROUP BY sebagai field kunci distribusi.
   CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
   END;

Langkah 2: Bangun tabel hasil agregasi dengan roaring bitmaps.

Setelah Anda membuat tabel fakta, Anda dapat membangun tabel hasil agregasi dengan menulis data hasil ke tabel hasil agregasi. Contoh kode:

-- Contoh: Query data yang dihasilkan dalam enam bulan tertentu dari tabel fakta, ubah data yang diquery menjadi roaring bitmaps, dan tulis roaring bitmaps ke tabel hasil agregasi.
INSERT INTO dws_app_rb
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd >= '20231201' AND ymd <='20240502'
GROUP BY country,prov,city,ymd;

Jika tabel fakta diperbarui, Anda dapat memperbarui data inkremental atau data penuh dalam tabel hasil agregasi berdasarkan granularitas pembaruan tabel fakta. Tabel berikut menjelaskan perbedaan antara pembaruan inkremental dan pembaruan penuh.

INSERT INTO dws_app_rb
SELECT  
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1)
FROM    ods_app_detail
WHERE ymd = '20240503'
GROUP BY country,prov,city,ymd;

-- Eksekusi pernyataan INSERT OVERWRITE untuk memperbarui semua data dalam tabel hasil agregasi.
CALL hg_insert_overwrite('public.dws_app_rb' , $$SELECT
 RB_BUILD_AGG(uid),
 country,
 prov,
 city,
 ymd,
COUNT(1) FROM ods_app_detail 
 WHERE ymd >= '20231201' AND ymd <='20240503'
 GROUP BY country,prov,city,ymd$$);

Langkah 3: Hitung jumlah UV dalam periode waktu kustom dan panjang

Anda dapat menghitung jumlah UV dan jumlah PV dalam periode waktu kustom berdasarkan kombinasi dimensi dasar kustom. Dalam banyak kasus, hasil query dikembalikan dalam milidetik. Metode ini lebih efisien daripada mengeksekusi pernyataan SQL tradisional dan juga mendukung QPS tinggi. Contoh:

• Hitung jumlah UV dan jumlah PV pada hari tertentu.

  SELECT  
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
    country,
    prov,
    city,
    SUM(pv) AS pv
  FROM    dws_app_rb
  WHERE   ymd = '20240329'
  GROUP BY country,prov,city;

• Hitung jumlah UV dan jumlah PV dalam bulan tertentu.

  SELECT  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
    country,
    prov,
    city,
    SUM(pv) AS pv
  FROM dws_app_rb
  WHERE   ymd >= '20240301' AND ymd <= '20240331'
  GROUP BY country,prov,city;

Langkah 1: Persiapkan data dasar

1. Buat ekstensi roaring bitmap.

   Sebelum menggunakan roaring bitmaps, Anda harus membuat ekstensi roaring bitmap. Ekstensi dibuat pada level database. Untuk setiap database, Anda hanya perlu membuat ekstensi sekali. Sintaks:

   Catatan

   Anda harus memuat ekstensi roaring bitmap dalam skema public.

   CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2. Buat tabel fakta pengguna.

   Buat tabel fakta pengguna untuk menyimpan data pengguna lengkap dan detail. Biasanya, tabel fakta adalah tabel berukuran besar yang menyimpan data pengguna lengkap dan detail. Kami sarankan Anda membuat tabel fakta yang dipartisi per hari untuk memfasilitasi penulisan dan pembaruan data.

   BEGIN;
   CREATE TABLE IF NOT EXISTS ods_app_detail (
        uid text,
        country text,
        prov text,
        city text,
        channel text,
        operator text,
        brand text,
        ip text,
        click_time text,
        year text,
        month text,
        day text,
        ymd text NOT NULL
   )
   PARTITION BY LIST (ymd) ;-- Konfigurasikan partisi per hari karena tabel fakta berisi sejumlah besar data.
   CALL set_table_property('ods_app_detail', 'orientation', 'column');
   CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
   -- Tentukan kunci distribusi untuk tabel berdasarkan persyaratan query real-time. Data didistribusikan ke shard berdasarkan kunci distribusi.
   CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
   -- Tentukan field yang dapat digunakan dalam klausa WHERE. Kami sarankan Anda mengonfigurasi field yang berisi informasi tahun, bulan, dan tanggal sebagai kunci kluster dan kolom waktu acara.
   CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
   CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
   COMMIT;

3. Buat tabel hasil agregasi untuk roaring bitmaps.

   Buat tabel hasil agregasi untuk menyimpan roaring bitmaps yang telah diagregasi.

   • Hanya jutaan catatan data yang disimpan dalam tabel hasil agregasi untuk setiap hari setelah agregasi. Kami sarankan Anda membuat tabel hasil agregasi tanpa partisi untuk memfasilitasi penulisan balik data. Anda juga dapat membuat tabel hasil agregasi yang dipartisi per bulan atau kuartal. Jika Anda mengonfigurasi partisi per hari, jumlah data dalam setiap partisi kecil, dan sejumlah besar file kecil dihasilkan karena partisi. Ini meningkatkan penggunaan memori.

   • Buat tabel hasil agregasi berdasarkan dimensi seperti negara, provinsi, dan kota, dan konfigurasikan field dimensi sebagai field kunci distribusi. Ini memfasilitasi query berbasis dimensi. Jika lebih dari tiga field ditentukan dalam klausa GROUP BY, kami sarankan Anda mengonfigurasi field yang paling sering digunakan sebagai field kunci distribusi.

   • Konfigurasikan field dimensi query dan field tanggal sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.

   • Konfigurasikan field tanggal sebagai kunci kluster dan kolom waktu acara untuk memfasilitasi penyaringan data.

   Contoh kode:

   BEGIN;
   CREATE TABLE dws_app_rb(
     rb_uid roaringbitmap, -- Perhitungan UV.
     country text,
     prov text,
     city text,
     ymd text NOT NULL, -- Field tanggal.
     pv integer, -- Perhitungan PV.
     PRIMARY key(country, prov, city, ymd)-- Konfigurasikan field dimensi query dan field tanggal sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.
   );
   CALL set_table_property('dws_app_rb', 'orientation', 'column');
   -- Konfigurasikan field tanggal sebagai kunci kluster dan kolom waktu acara untuk memfasilitasi penyaringan data.
   CALL set_table_property('dws_app_rb', 'clustering_key', 'ymd');
   CALL set_table_property('dws_app_rb', 'event_time_column', 'ymd');
   -- Konfigurasikan field yang ditentukan dalam klausa GROUP BY sebagai field kunci distribusi.
   CALL set_table_property('dws_app_rb', 'distribution_key', 'country,prov,city');
   END;

4. Buat tabel pemetaan UID.

   UID yang disimpan dalam roaring bitmaps harus berupa bilangan bulat 32-bit dan UID berurutan lebih disukai. Namun, UID yang dikumpulkan dalam sistem bisnis atau titik pelacakan biasanya bertipe TEXT. Oleh karena itu, Anda perlu membuat tabel pemetaan UID yang berisi kolom bertipe SERIAL. Kolom tersebut terdiri dari bilangan bulat 32-bit auto-increment. Dengan cara ini, pemetaan UID dikelola secara otomatis dan tetap stabil.

   BEGIN;
    CREATE TABLE uid_mapping (
        uid text NOT NULL,
        uid_int32 serial,
       PRIMARY KEY (uid)
    );
    -- Konfigurasikan kolom UID sebagai kunci kluster dan kunci distribusi untuk dengan cepat menemukan bilangan bulat 32-bit yang sesuai dengan 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;

Langkah 2: Bangun roaring bitmaps dan impor ke tabel hasil agregasi

1. Impor data ke dan perbarui tabel pemetaan UID.

   1. Inisialisasi penuh tabel pemetaan UID.

      Impor semua data UID ke tabel pemetaan UID dan inisialisasi data. Anda juga dapat memfilter data berdasarkan field tanggal dan mengimpor data yang diperoleh ke tabel pemetaan UID. Dalam contoh ini, data UID dalam periode enam bulan tertentu diimpor.

      -- Impor data dalam periode enam bulan tertentu ke tabel pemetaan UID.
      INSERT INTO uid_mapping (uid) B
      SELECT distinct (uid) FROM ods_app_detail 
      WHERE B.ymd >= '20231201' AND B.ymd <='20240502';

   2. Verifikasi data dalam tabel pemetaan UID.

      Setelah Anda mengimpor data ke tabel pemetaan UID, periksa apakah data dalam tabel pemetaan UID konsisten dengan data dalam tabel fakta.

      -- Verifikasi data.
      SELECT COUNT(*) FROM uid_mapping;

      Verifikasi kontinuitas nilai dalam kolom bertipe SERIAL. Dalam skenario tertentu, operasi TRUNCATE dan INSERT dilakukan untuk menginisialisasi tabel pemetaan UID beberapa kali. Namun, ketika operasi TRUNCATE dilakukan, urutan nilai dalam kolom bertipe SERIAL tidak diatur ulang. Akibatnya, nilai bertipe SERIAL terbuang. Setelah data diimpor beberapa kali, nilai dalam kolom bertipe SERIAL melebihi 32-bit.

      -- Verifikasi kontinuitas nilai dalam kolom bertipe SERIAL.
      SELECT MAX(uid_int32),MIN(uid_int32) FROM uid_mapping;

   3. Perbarui tabel pemetaan UID.

      Jika UID dalam tabel fakta diperbarui, Anda harus memperbarui data dalam tabel pemetaan UID sesegera mungkin. Jika hanya UID pada hari tertentu yang diperbarui dalam tabel fakta, Anda dapat mengeksekusi pernyataan INSERT ON CONFLICT untuk memperbarui tabel pemetaan UID. Jika UID yang diperbarui tidak dapat diidentifikasi, Anda dapat melakukan penulisan balik data penuh, yang memakan waktu jika melibatkan sejumlah besar data.

      Dalam contoh ini, pernyataan INSERT ON CONFLICT DO NOTHING dieksekusi untuk memperbarui data hari sebelumnya dalam tabel pemetaan UID. DO NOTHING memastikan bahwa data diperbarui tetapi tidak ditulis berulang kali.

      -- Perbarui data hari sebelumnya dalam tabel pemetaan UID.
      INSERT INTO uid_mapping (uid)
      SELECT distinct (uid) FROM ods_app_detail 
      WHERE ymd = '20240503' 
      ON conflict do nothing;

2. Impor data ke dan perbarui tabel hasil agregasi.

   Setelah Anda memperbarui tabel pemetaan UID, lakukan langkah-langkah berikut untuk mengagregasi data dan menyisipkan hasil agregasi ke tabel hasil agregasi:

   1. Gabungkan tabel fakta dan tabel pemetaan UID untuk mendapatkan kondisi agregasi dan UID 32-bit dalam kolom uid_int32.

   2. Agregasikan data berdasarkan kondisi agregasi, dan sisipkan data yang telah diagregasi sebagai roaring bitmaps ke tabel hasil agregasi. Anda dapat mengagregasi data per hari atau per bulan berdasarkan kebutuhan bisnis Anda.

   3. Anda hanya perlu mengagregasi data sekali dan menyimpan hasil agregasi dalam tabel hasil agregasi. Jumlah catatan data dalam tabel hasil agregasi sama dengan jumlah UV.

   4. Eksekusi pernyataan berikut untuk menyisipkan data ke tabel hasil agregasi: Dalam contoh ini, data dalam periode enam bulan tertentu diagregasi ke tabel hasil agregasi.

      WITH aggregation_src AS (
          SELECT
             B.uid_int32,
              A.country,
              A.prov,
              A.city,
              A.ymd
          FROM
              ods_app_detail A
              INNER JOIN uid_mapping B ON A.uid = B.uid
          WHERE
              A.ymd >= '20231201' AND A.ymd <='20240502')
      INSERT INTO dws_app_rb
      SELECT
          RB_BUILD_AGG (uid_int32),
          country,
          prov,
          city,
          ymd,
          COUNT(1)
      FROM
          aggregation_src
      GROUP BY
          country,
          prov,
          city    ,ymd;

   5. Perbarui tabel hasil agregasi.

      Jika tabel fakta dan tabel pemetaan UID diperbarui, Anda juga harus memperbarui tabel hasil agregasi. Anda dapat menggunakan mode pembaruan inkremental atau pembaruan penuh untuk memperbarui tabel hasil agregasi berdasarkan aturan berikut:

      • Pembaruan inkremental: Jika data diperbarui dalam tabel fakta secara berkala, seperti setiap hari, Anda dapat mengeksekusi pernyataan INSERT untuk menulis data inkremental di partisi terbaru ke tabel hasil agregasi.

      • Pembaruan penuh: Jika data tidak diperbarui secara berkala dalam tabel fakta, dan Anda tidak dapat mengidentifikasi data inkremental, Anda dapat mengeksekusi pernyataan INSERT OVERWRITE untuk memperbarui tabel hasil agregasi dengan menggunakan metode penulisan balik data penuh.

Langkah 3: Hitung jumlah UV dalam periode waktu kustom dan panjang

Anda dapat menghitung jumlah UV dan jumlah PV dalam periode waktu kustom berdasarkan kombinasi dimensi dasar kustom. Dalam banyak kasus, hasil query dikembalikan dalam milidetik. Metode ini lebih efisien daripada mengeksekusi pernyataan SQL tradisional dan juga mendukung QPS tinggi. Contoh:

• Hitung jumlah UV dan jumlah PV pada hari tertentu.

  SELECT  country,
          prov,
          city,
          RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
          SUM(pv) AS pv
  FROM    dws_app_rb
  WHERE   ymd = '20240329'
  GROUP BY country,prov,city;

• Hitung jumlah UV dan jumlah PV dalam bulan tertentu.

  -- Hitung jumlah UV dan jumlah PV dalam bulan tertentu.
  SELECT  country
          ,prov
          ,RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv
          ,SUM(pv) AS pv
  FROM    public.dws_app_rb
  WHERE   ymd >= '20240301' AND ymd <= '20240331'
  GROUP BY country,prov,city;

Langkah 1: Persiapkan data dasar

1. Buat ekstensi roaring bitmap.

   Sebelum menggunakan roaring bitmaps, Anda harus membuat ekstensi roaring bitmap. Ekstensi dibuat pada level database. Untuk setiap database, Anda hanya perlu membuat ekstensi sekali. Sintaks:

   Catatan

   Anda harus memuat ekstensi roaring bitmap dalam skema public.

   CREATE EXTENSION IF NOT EXISTS roaringbitmap;

2. Buat tabel perilaku pengguna dan tabel properti.

   Bucket biasanya digunakan untuk analisis profil dengan menggabungkan beberapa tabel. Dalam contoh ini, dua tabel diperlukan. Satu tabel perilaku pengguna digunakan untuk menyimpan perilaku pengguna lengkap dan detail, dan satu tabel properti digunakan untuk menyimpan properti pengguna, seperti gender dan usia. Anda dapat menggabungkan kedua tabel untuk menghitung kardinalitas pengguna berdasarkan dimensi kustom.

   • Buat tabel perilaku pengguna bernama ods_user_behaviour_detail.

     -- Buat tabel perilaku pengguna untuk menyimpan data perilaku detail.
     BEGIN;
     CREATE TABLE IF NOT EXISTS ods_user_behaviour_detail (
          uid int,
          operator text,
          channel text,
          shop_id text,
          time text,
          ymd text NOT NULL
     );
     CALL set_table_property('ods_user_behaviour_detail', 'orientation', 'column');
     -- Tentukan kunci distribusi untuk tabel berdasarkan persyaratan query real-time. Data didistribusikan ke shard berdasarkan kunci distribusi.
     CALL set_table_property('ods_user_behaviour_detail', 'distribution_key', 'uid');
     -- Tentukan field yang dapat digunakan dalam klausa WHERE. Kami sarankan Anda mengonfigurasi field yang berisi informasi tahun, bulan, dan tanggal sebagai kunci kluster dan kolom waktu acara.
     CALL set_table_property('ods_user_behaviour_detail', 'clustering_key', 'ymd');
     CALL set_table_property('ods_user_behaviour_detail', 'event_time_column', 'ymd');
     COMMIT;

   • Buat tabel properti pengguna bernama dim_userbase.

     -- Buat tabel properti pengguna.
     BEGIN;
     CREATE TABLE IF NOT EXISTS dim_userbase (
          uid int, 
          age text,
          gender text,
          country text,
          prov text,
          city text
          );
     CALL set_table_property('dim_userbase', 'orientation', 'column');
     CALL set_table_property('dim_userbase', 'distribution_key', 'uid');
     COMMIT;
     

3. Buat tabel hasil agregasi dengan roaring bitmaps.

   Buat tabel hasil agregasi untuk menyimpan roaring bitmaps yang telah diagregasi.

   • Hanya jutaan catatan data yang disimpan dalam tabel hasil agregasi untuk setiap hari setelah agregasi. Kami sarankan Anda membuat tabel hasil agregasi tanpa partisi untuk memfasilitasi penulisan balik data. Anda juga dapat membuat tabel hasil agregasi yang dipartisi per bulan atau kuartal. Jika Anda mengonfigurasi partisi per hari, jumlah data dalam setiap partisi kecil, dan sejumlah besar file kecil dihasilkan karena partisi. Ini meningkatkan penggunaan memori.

   • Buat tabel hasil agregasi berdasarkan dimensi seperti negara, provinsi, dan kota, dan konfigurasikan field dimensi sebagai field kunci distribusi. Ini memfasilitasi query berbasis dimensi. Jika lebih dari tiga field ditentukan dalam klausa GROUP BY, kami sarankan Anda mengonfigurasi field yang paling sering digunakan sebagai field kunci distribusi.

   • Konfigurasikan field dimensi query dan field tanggal sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.

   • Konfigurasikan field tanggal sebagai kunci kluster dan kolom waktu acara untuk memfasilitasi penyaringan data.

   • Buat tabel hasil agregasi bernama dws_user_behaviour_rb.

     -- Buat tabel hasil agregasi untuk tabel perilaku pengguna.
     BEGIN;
     CREATE TABLE dws_user_behaviour_rb(
       rb_uid roaringbitmap, -- Perhitungan UV.
       bucket int NOT NULL, -- Field bucketing.
       operator text,
       channel text,
       shop_id text,
       time text,
       ymd text NOT NULL,
       PRIMARY key(operator,channel,shop_id,time, ymd,bucket)-- Tentukan field dimensi query dan field tanggal sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.
     );
     CALL set_table_property('dws_user_behaviour_rb', 'orientation', 'column');
     -- Konfigurasikan field tanggal sebagai kunci kluster dan kolom waktu acara untuk memfasilitasi penyaringan data.
     CALL set_table_property('dws_user_behaviour_rb', 'clustering_key', 'ymd');
     CALL set_table_property('dws_user_behaviour_rb', 'event_time_column', 'ymd');
     -- Konfigurasikan field bucketing sebagai kunci distribusi.
     CALL set_table_property('dws_user_behaviour_rb', 'distribution_key', 'bucket');
     END;

   • Buat tabel hasil agregasi bernama dim_userbase_rb.

     -- Buat tabel hasil agregasi untuk tabel properti pengguna.
     BEGIN;
     CREATE TABLE IF NOT EXISTS dim_userbase_rb (
          rb_uid roaringbitmap, -- Perhitungan UV.
          bucket int NOT NULL, -- Field bucketing.
          age text,
          gender text,
          country text,
          prov text,
          city text,
          PRIMARY key(age,gender,country, prov,city,bucket)-- Tentukan field dimensi query sebagai field kunci utama untuk mencegah data dimasukkan berulang kali.
          );
     CALL set_table_property('dim_userbase_rb', 'orientation', 'column');
     CALL set_table_property('dim_userbase_rb', 'distribution_key', 'bucket');-- Tentukan field bucketing sebagai kunci distribusi untuk memungkinkan join lokal.
     COMMIT;
     

Langkah 2: Bangun roaring bitmaps dan impor ke tabel hasil agregasi

Setelah Anda membuat tabel fakta, Anda harus menulis data dari tabel fakta ke tabel hasil agregasi dan menyimpan UID dalam field bucketing. Konfigurasikan bucket berdasarkan jumlah data dan karakteristik distribusi data bisnis Anda. Dalam contoh ini, data UID didistribusikan secara merata ke 256 bucket. Dengan cara ini, data UID dalam bucket yang sama didistribusikan pada shard yang sama. Ini memungkinkan komputasi paralel dan mencapai query berperforma tinggi. Contoh kode:

• Impor data dari tabel perilaku pengguna ke tabel hasil agregasi.

  -- Tulis data dari tabel fakta ke tabel hasil agregasi.
  INSERT INTO dws_user_behaviour_rb
  SELECT  
  RB_BUILD_AGG(uid),
  uid >> 24 AS bucket,-- Geser 24 bit ke kanan. Dengan cara ini, 8 bit paling signifikan digunakan sebagai ID bucket dan 24 bit paling tidak signifikan disimpan sebagai bitmap.
  operator,
  channel,
  shop_id,
  time
  ymd
  FROM ods_user_behaviour_detail
  WHERE 
   ymd >= '20231201' AND ymd <='20240503'

• Tulis data dari tabel properti ke tabel hasil agregasi.

  -- Tulis data dari tabel properti ke tabel hasil agregasi.
  INSERT INTO dim_userbase_rb
  SELECT 
  RB_BUILD_AGG(uid),
  uid >> 24 AS bucket,-- Geser 24 bit ke kanan. Dengan cara ini, 8 bit paling signifikan digunakan sebagai ID bucket dan 24 bit paling tidak signifikan disimpan sebagai bitmap.
  age,
  gender,
  country,
  prov,
  city
  FROM dim_userbase
  GROUP BY age,gender,country,prov,city,bucket;

Jika tabel fakta diperbarui, Anda dapat memperbarui data inkremental atau data penuh dalam tabel hasil agregasi berdasarkan granularitas pembaruan tabel fakta.

• Pembaruan inkremental: Jika data diperbarui dalam tabel fakta secara berkala, seperti ketika hanya partisi yang menyimpan data hari sebelumnya yang diperbarui, Anda dapat mengeksekusi pernyataan INSERT untuk menulis data inkremental ke tabel hasil agregasi.

• Pembaruan penuh: Jika data tidak diperbarui secara berkala dalam tabel fakta dan Anda tidak dapat dengan cepat mengidentifikasi data inkremental, Anda dapat memperbarui tabel hasil agregasi dengan menggunakan metode penulisan balik data penuh.

Langkah 3: Hitung jumlah UV dalam periode waktu kustom dan panjang

Anda dapat menghitung jumlah UV dan jumlah PV dalam periode waktu kustom berdasarkan kombinasi dimensi dasar kustom. Metode ini lebih efisien daripada mengeksekusi pernyataan SQL tradisional. Contoh kode:

-- Query jumlah pengguna yang memenuhi kondisi berikut: gender = 'man'&country = 'Beijing'&operator = 'Purchase'&shop_id = '1'.
SELECT
    SUM(RB_CARDINALITY (rb_and (t1.rb_uid, t2.rb_uid)))
FROM (
    SELECT
        rb_or_agg (rb_uid) AS rb_uid,
        bucket
    FROM
        dws_user_behaviour_rb
    WHERE
        OPERATOR = 'Purchase'
        AND shop_id = '1'
        AND ymd = '20240501'
    GROUP BY
        bucket) t1
    JOIN (
        SELECT
            rb_or_agg (rb_uid) AS rb_uid,
            bucket
        FROM
            dim_userbase_rb
        WHERE
            gender = 'man'
            AND country = 'Beijing'
        GROUP BY
            bucket) t2 ON t1.bucket = t2.bucket;