Cara menggunakan fungsi Roaring Bitmap untuk segmentasi pengguna - AnalyticDB

Roaring Bitmap adalah algoritma kompresi bitmap yang efisien dan banyak digunakan di berbagai bahasa pemrograman serta platform data besar. Penggunaannya umumnya mencakup penghapusan duplikat, penyaringan tag, dan perhitungan deret waktu. Topik ini menjelaskan cara menggunakan fungsi Roaring Bitmap.

Persyaratan versi

Versi 3.1.6.4 dan yang lebih baru mendukung tipe ROARING BITMAP dan fungsi terkait pada tabel eksternal OSS.
Versi 3.2.1.0 dan yang lebih baru mendukung tipe ROARING BITMAP dan fungsi terkait pada tabel internal.

Catatan

Untuk melihat dan memperbarui versi minor, buka bagian Configuration Information pada halaman Cluster Information di AnalyticDB for MySQL console.

Batasan

Batasan berikut berlaku untuk fungsi Roaring Bitmap di AnalyticDB for MySQL:

Anda tidak dapat langsung memilih kolom bertipe ROARING BITMAP. Untuk melihat elemen dalam bidang ROARING BITMAP, gunakan operasi Unnest. Contohnya:
```
SELECT * FROM unnest(RB_BUILD(ARRAY[1,2,3]));
```
Pada versi sebelum 3.2.1.0, tipe ROARING BITMAP hanya didukung secara native pada tabel eksternal OSS. Anda tidak dapat langsung membuat tabel yang berisi kolom ROARING BITMAP. Untuk menggunakan fungsi Roaring Bitmap pada tabel internal AnalyticDB for MySQL, Anda harus menggunakan fungsi rb_build_varbinary untuk mengonversi data dari tipe VARBINARY ke tipe ROARING BITMAP. Contohnya:
```
// Definisikan tabel internal.
CREATE TABLE test_rb_cstore (id INT, rb VARBINARY);

// Lakukan perhitungan menggunakan fungsi Roaring Bitmap.
SELECT RB_CARDINALITY(RB_BUILD_VARBINARY(rb)) FROM test_rb_cstore;
```

Daftar fungsi

Fungsi Roaring Bitmap mencakup fungsi skalar dan fungsi agregat.

Fungsi skalar

Nama fungsi	Tipe data input	Tipe data output	Deskripsi	Contoh
RB_BUILD	INT	ROARING BITMAP	Membuat Roaring Bitmap dari array bilangan bulat.	`RB_BUILD(ARRAY[1,2,3])`
RB_BUILD_RANGE	INT,INT	ROARING BITMAP	Membuat Roaring Bitmap dari rentang bilangan bulat. Nilai awal inklusif, sedangkan nilai akhir eksklusif.	`RB_BUILD_RANGE(0, 10000)`
RB_BUILD_VARBINARY	VARBINARY	ROARING BITMAP	Membuat Roaring Bitmap dari tipe biner.	`RB_BUILD_VARBINARY(RB_TO_VARBINARY (RB_BUILD(ARRAY[1,2,3])))`
RB_CARDINALITY	ROARING BITMAP	BIGINT	Menghitung kardinalitas Roaring Bitmap.	`RB_CARDINALITY(RB_BUILD(ARRAY[1,2,3]))`
RB_CONTAINS	ROARING BITMAP, INT	BOOLEAN	Memeriksa apakah Roaring Bitmap berisi bilangan bulat tertentu.	`RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]), 3)`
RB_AND	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menghitung irisan dua Roaring Bitmap.	`RB_AND(RB_BUILD(ARRAY[1,2,3]), RB_BUILD(ARRAY[2,3,4]))`
RB_OR	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menghitung gabungan dua Roaring Bitmap.	`RB_OR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))`
RB_XOR	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menghitung operasi XOR (exclusive OR) dua Roaring Bitmap.	`RB_XOR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))`
RB_AND_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menjalankan operasi AND. Jika salah satu parameter input bernilai null, parameter lainnya dikembalikan. Jika salah satu parameter input adalah `{}`, output-nya adalah `{}`.	`RB_AND_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menjalankan operasi OR. Jika input bernilai null, Roaring Bitmap dianggap kosong ({}).	`RB_OR_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	Menjalankan operasi AND NOT. Jika input bernilai null, Roaring Bitmap dianggap kosong ({}).	`RB_ANDNOT_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_AND_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi AND dan mengembalikan kardinalitasnya.	`RB_AND_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_AND_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi AND dan mengembalikan kardinalitasnya. Jika input bernilai null, Roaring Bitmap dianggap kosong ({}).	`RB_AND_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi OR dan mengembalikan kardinalitasnya.	`RB_OR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi OR dan mengembalikan kardinalitasnya. Jika input bernilai null, Roaring Bitmap dianggap kosong ({}).	`RB_OR_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_XOR_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi XOR dan mengembalikan kardinalitasnya.	`RB_XOR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi AND NOT dan mengembalikan kardinalitasnya.	`RB_ANDNOT_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	Menjalankan operasi AND NOT dan mengembalikan kardinalitasnya. Jika input bernilai null, Roaring Bitmap dianggap kosong ({}).	`RB_ANDNOT_NULL2EMPTY_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_IS_EMPTY	ROARING BITMAP	BOOLEAN	Memeriksa apakah Roaring Bitmap kosong.	`RB_IS_EMPTY(RB_BUILD(ARRAY[]))`
RB_CLEAR	ROARING BITMAP,BIGINT,BIGINT	ROARING BITMAP	Menghapus rentang tertentu. Nilai range_end tidak termasuk.	`RB_CLEAR(RB_BUILD('{1,2,3}'), 2, 3)`
RB_CONTAINS	ROARING BITMAP, ROARING BITMAP	BOOLEAN	Memeriksa apakah Roaring Bitmap pertama berisi Roaring Bitmap kedua.	`RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3]))`
RB_FLIP	ROARING BITMAP, INTEGER, INTEGER	ROARING BITMAP	Membalik segmen offset tertentu dalam Roaring Bitmap.	`RB_FLIP(RB_BUILD(ARRAY[1,2,3,4,5]), 2, 5)`
RB_MINIMUM	ROARING BITMAP	INTEGER	Mengembalikan offset minimum dalam Roaring Bitmap. Jika Roaring Bitmap kosong, kesalahan akan dikembalikan.	`RB_MINIMUM(RB_BUILD(ARRAY[1,2,3]))`
RB_MAXIMUM	ROARING BITMAP	INTEGER	Mengembalikan offset maksimum dalam Roaring Bitmap. Jika Roaring Bitmap kosong, kesalahan akan dikembalikan.	`RB_MAXIMUM(RB_BUILD(ARRAY[1,2,3]))`
RB_RANK	ROARING BITMAP,INTEGER	INTEGER	Mengembalikan kardinalitas elemen yang kurang dari atau sama dengan offset tertentu dalam Roaring Bitmap.	`RB_RANK(RB_BUILD(ARRAY[1,2,3]),2)`
RB_TO_ARRAY	ROARING BITMAP	INTEGER	Mengembalikan array bilangan bulat yang sesuai dengan Roaring Bitmap.	`RB_TO_ARRAY(RB_BUILD(ARRAY[1,2,3]))`
RB_TO_VARBINAR	ROARING BITMAP	VARBINARY	Mengembalikan tipe VARBINARY yang sesuai dengan Roaring Bitmap.	`RB_TO_VARBINARY(RB_BUILD(ARRAY[1,2,3]))`
RB_RANGE_CARDINALITY	ROARING BITMAP, INTEGER, INTEGER	INTEGER	Mengembalikan kardinalitas rentang dari posisi awal (inklusif) hingga posisi akhir (eksklusif). Posisi dimulai dari 1. Penting Fungsi ini hanya didukung pada versi 3.1.10.0 dan yang lebih baru.	`RB_RANGE_CARDINALITY(RB_BUILD(ARRAY [1,2,3]),2,3)`
RB_SELECT	ROARING BITMAP, INTEGER, INTEGER	ROARING BITMAP	Mengembalikan offset bitmap dalam rentang dari posisi awal (inklusif) hingga posisi akhir (eksklusif). Penting Fungsi ini hanya didukung pada versi 3.1.10.0 dan yang lebih baru.	`RB_SELECT(RB_BUILD(ARRAY [1,3,4,5,7,9]),2, 3)`

Fungsi agregat

Nama fungsi	Tipe data input	Tipe data output	Deskripsi	Contoh
RB_BUILD_AGG	INTEGER	ROARING BITMAP	Mengagregasi offset menjadi bitmap.	`RB_CARDINALITY(RB_BUILD_AGG(1))`
RB_OR_AGG	ROARING BITMAP	ROARING BITMAP	Menjalankan agregasi OR.	`RB_CARDINALITY(RB_OR_AGG(RB_BUILD(array[1,2,3])))`
RB_AND_AGG	ROARING BITMAP	ROARING BITMAP	Menjalankan agregasi AND.	`RB_CARDINALITY(RB_AND_AGG(RB_BUILD(ARRAY[1,2,3])))`
RB_XOR_AGG	ROARING BITMAP	ROARING BITMAP	Menjalankan agregasi XOR.	`RB_CARDINALITY(RB_XOR_AGG(RB_BUILD(ARRAY[1,2,3])))`
RB_OR_CARDINALITY_AGG	ROARING BITMAP	INTEGER	Menjalankan agregasi OR dan mengembalikan kardinalitasnya.	`RB_OR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`
RB_AND_CARDINALITY_AGG	ROARING BITMAP	INTEGER	Menjalankan agregasi AND dan mengembalikan kardinalitasnya.	`RB_AND_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`
RB_XOR_CARDINALITY_AGG	ROARING BITMAP	INTEGER	Menjalankan agregasi XOR dan mengembalikan kardinalitasnya.	`RB_XOR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`

Contoh

Bagian berikut menyediakan contoh lengkap tentang cara menggunakan fungsi Roaring Bitmap.

Tabel internal

Buat tabel internal yang berisi kolom ROARINGBITMAP.

CREATE TABLE `test_rb` (
  `id` INT,
  `rb` ROARINGBITMAP
);

Masukkan data ke tabel.

INSERT INTO test_rb VALUES (1, '[1, 2, 3]');
INSERT INTO test_rb VALUES (2, '[2, 3, 4, 5, 6]');

Gunakan fungsi skalar Roaring Bitmap untuk menghitung kardinalitas.

SELECT id, RB_CARDINALITY(rb) FROM test_rb;

Hasil berikut dikembalikan:

+------+--------------------+
| id   | rb_cardinality(rb) |
+------+--------------------+
|    2 |                  5 |
|    1 |                  3 |
+------+--------------------+

Gunakan fungsi agregat Roaring Bitmap untuk melakukan agregasi.

SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

Hasil berikut dikembalikan:

+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
|                         6 |
+---------------------------+

Tampilan

Buat tabel eksternal yang berisi kolom ROARINGBITMAP.

CREATE TABLE `test_rb` (
  `id` INT,
  `rb` ROARINGBITMAP
  ) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/test_for_user/",
"format":"parquet"
}';

Catatan

Untuk informasi selengkapnya tentang parameter tabel eksternal, lihat Tabel eksternal OSS non-partisi.

Masukkan data ke tabel.

Penting
Memasukkan data menggunakan `INSERT INTO` tidak efisien. Untuk dataset besar, gunakan tool extract, transform, and load (ETL) untuk menghasilkan file data Parquet. Kemudian, unggah file tersebut ke path OSS yang sesuai sebelum membuat tabel eksternal.
```
INSERT INTO test_rb SELECT 1, rb_build(ARRAY[1,2,3]);
INSERT INTO test_rb SELECT 2, rb_build(ARRAY[2,3,4,5]);
```

Gunakan fungsi skalar Roaring Bitmap untuk menghitung kardinalitas.

SELECT id, RB_CARDINALITY(rb) FROM test_rb;

Hasil berikut dikembalikan:

+------+--------------------+
| id   | rb_cardinality(rb) |
+------+--------------------+
|    2 |                  4 |
|    1 |                  3 |
+------+--------------------+

Gunakan fungsi agregat Roaring Bitmap untuk melakukan agregasi.

SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

Hasil berikut dikembalikan:

+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
|                         5 |
+---------------------------+

Praktik skenario segmentasi pengguna

Pada skenario ini, Anda mengubah tabel tag asli menjadi tabel tag Roaring Bitmap, lalu melakukan perhitungan Roaring Bitmap. Prosesnya ditunjukkan pada gambar berikut:

Langkah 1: Siapkan tabel tag asli

Buat tabel tag asli uji coba bernama `users_base`.

CREATE TABLE users_base(
   uid INT,
   tag1 STRING, // Rentang nilai tag1 adalah x, y, dan z.
   tag2 STRING, // Rentang nilai tag2 adalah a dan b.
   tag3 INT // Rentang nilai tag3 adalah 1 hingga 10.
);

Hasilkan 100 juta baris data acak. Asumsikan data yang dihasilkan merepresentasikan data tag pengguna.

SUBMIT JOB
INSERT OVERWRITE users_base
SELECT CAST(ROW_NUMBER() OVER (ORDER BY c1) AS INT) AS uid, SUBSTRING('xyz', FLOOR(RAND() * 3) + 1, 1) AS tag1, SUBSTRING('ab', FLOOR(RAND() * 2) + 1, 1) AS tag2, CAST(FLOOR(RAND() * 10) + 1 AS INT) as tag3 FROM  
(
SELECT A.c1 FROM
UNNEST(RB_BUILD_RANGE(0, 10000)) AS A(c1)
  JOIN
  (SELECT c1 FROM
UNNEST(RB_BUILD_RANGE(0, 10000)) AS B(c1)
));

Kueri 10 baris data dari tabel tag asli `users_base`.

SELECT * FROM users_base LIMIT 10;

Hasil berikut dikembalikan:

+--------+------+------+------+
| uid    | tag1 | tag2 | tag3 |
+--------+------+------+------+
|  74526 | y    | b    |    3 |
|  75611 | z    | b    |   10 |
|  80850 | x    | b    |    5 |
|  81656 | z    | b    |    7 |
| 163845 | x    | b    |    2 |
| 167007 | y    | b    |    4 |
| 170541 | y    | b    |    9 |
| 213108 | x    | a    |   10 |
|  66056 | y    | b    |    4 |
|  67761 | z    | a    |    2 |
+--------+------+------+------+

Langkah 2: Kelompokkan tabel tag asli

Saat merancang tabel tag Roaring Bitmap, Anda dapat memanfaatkan kemampuan konkuren mesin komputasi terdistribusi. Kami menyarankan Anda menambahkan field pengelompokan, seperti `user_group` pada contoh ini, untuk mengelompokkan UID demi komputasi paralel. Anda dapat menentukan ukuran kelompok berdasarkan jumlah total ACU di kluster Anda dan kebutuhan bisnis. Ikuti prinsip-prinsip berikut:

Secara umum, semakin banyak kelompok, semakin besar kemampuan komputasi. Namun, terlalu banyak kelompok dapat menyebabkan terlalu sedikit elemen di setiap bidang Roaring Bitmap, sehingga menghambat pemanfaatan atribut komputasi bitmap secara optimal.
Sebagai praktik terbaik, kami menyarankan agar ruang Roaring Bitmap untuk setiap kelompok berisi kurang dari 100 juta baris. Misalnya, jika ruang UID asli memiliki 10 miliar catatan, Anda dapat membuat 100 kelompok, di mana setiap kelompok berisi 100 juta catatan.

Contoh ini menggunakan 16 kelompok. Catatan dikelompokkan berdasarkan uid % 16 dan hasilnya disimpan di field `user_group`. Offset dihitung menggunakan uid / 16 dan disimpan di field `offset`. Rumusnya adalah uid = 16 * offset + user_group. Offset ini kemudian digunakan untuk menghitung Roaring Bitmap.

Metode pengelompokan ini hanya sebagai contoh. Anda harus merancang fungsi pengelompokan yang sesuai dengan kebutuhan bisnis Anda.

Buat tabel tag `users` dengan field pengelompokan tambahan.

CREATE TABLE users(
   uid INT,
   tag1 STRING,
   tag2 STRING,
   tag3 INT,
   user_group INT, // Field pengelompokan
   offset INT // Field offset
);

Masukkan data dari tabel `users_base` ke tabel `users`.

SUBMIT JOB INSERT OVERWRITE users SELECT uid, tag1, tag2, tag3, CAST(uid%16 AS INT), CAST(FLOOR(uid/16) AS INT) FROM users_base;

Kueri 10 baris data dari tabel `users`.

SELECT * FROM users LIMIT 10;

Hasil berikut dikembalikan:

+---------+------+------+------+------------+--------+
| uid     | tag1 | tag2 | tag3 | user_group | offset |
+---------+------+------+------+------------+--------+
|  377194 | z    | b    |   10 |         10 |  23574 |
|  309440 | x    | a    |    1 |          0 |  19340 |
|  601745 | z    | a    |    7 |          1 |  37609 |
|  753751 | z    | b    |    3 |          7 |  47109 |
|  988186 | y    | a    |   10 |         10 |  61761 |
|  883822 | x    | a    |    9 |         14 |  55238 |
|  325065 | x    | b    |    6 |          9 |  20316 |
| 1042875 | z    | a    |   10 |         11 |  65179 |
|  928606 | y    | b    |    5 |         14 |  58037 |
|  990858 | z    | a    |    8 |         10 |  61928 |
+---------+------+------+------+------------+--------+

Langkah 3: Bangun tabel tag Roaring Bitmap

Tabel internal

Buat tabel tag Roaring Bitmap `tag_tbl_1` untuk `tag1`.

CREATE TABLE `tag_tbl_1` (
  `tag1` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
);

Masukkan data dari tabel `users` ke tabel `tag_tbl_1`.

INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;

Kueri data dari tabel tag `tag_tbl_1`.

SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

Hasil berikut dikembalikan:

+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| y    |         13 |             563654 |
| x    |         11 |             565013 |
| z    |          2 |             564428 |
| x    |          4 |             564377 |
...                                 
| z    |          5 |             564333 |
| x    |          8 |             564808 |
| x    |          0 |             564228 |
| y    |          3 |             563325 |
+------+------------+--------------------+

Buat tabel tag Roaring Bitmap `tag_tbl_2` untuk `tag2`.

CREATE TABLE `tag_tbl_2` (
  `tag2` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
);

Masukkan data dari tabel `users` ke tabel `tag_tbl_2`.

INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;

Kueri data dari tabel tag `tag_tbl_2`.

SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

Hasil berikut dikembalikan:

+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| a    |          9 |            3123039 |
| a    |          5 |            3123973 |
| a    |         12 |            3122414 |
| a    |          7 |            3127218 |
| a    |         15 |            3125403 |
...                                  
| a    |         10 |            3122698 |
| b    |          4 |            3126091 |
| b    |          3 |            3124626 |
| b    |          9 |            3126961 |
| b    |         14 |            3125351 |
+------+------------+--------------------+

Tampilan

Buat tabel tag Roaring Bitmap `tag_tbl_1` untuk `tag1`.

CREATE TABLE `tag_tbl_1` (
  `tag1` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
 ) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/tag_tbl_1/",
"format":"parquet"
}';

Masukkan data dari tabel `users` ke tabel `tag_tbl_1`.

INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;

Kueri data dari tabel tag `tag_tbl_1`.

SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

Hasil berikut dikembalikan:

+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| z    |          7 |            2082608 |
| x    |         10 |            2082953 |
| y    |          7 |            2084730 |
| x    |         14 |            2084856 |
...                                  
| z    |         15 |            2084535 |
| z    |          5 |            2083204 |
| x    |         11 |            2085239 |
| z    |          1 |            2084879 |
+------+------------+--------------------+

Buat tabel tag Roaring Bitmap `tag_tbl_2` untuk `tag2`.

CREATE TABLE `tag_tbl_2` (
  `tag2` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
 ) engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/tag_tbl_2/",
"format":"parquet"
}';

Masukkan data dari tabel `users` ke tabel `tag_tbl_2`.

INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;

Kueri data dari tabel tag `tag_tbl_2`.

SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

Hasil berikut dikembalikan:

+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| b    |         11 |            3121361 |
| a    |          6 |            3124750 |
| a    |          1 |            3125433 |
...                                    
| b    |          2 |            3126523 |
| b    |         12 |            3123452 |
| a    |          4 |            3126111 |
| a    |         13 |            3123316 |
| a    |          2 |            3123477 |
+------+------------+--------------------+

Langkah 4: Analisis data menggunakan tabel tag Roaring Bitmap

Skenario 1: Penyaringan dan analisis

Skenario ini menganalisis distribusi pengguna yang memenuhi kondisi tag1 IN ('x', 'y') berdasarkan dimensi `tag2` dan menyajikan hasilnya dalam bentuk grafik kolom.

Untuk mempermudah pemahaman, pertama-tama kueri pengguna yang memenuhi kondisi tag1 IN ('x', 'y').

SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS rb FROM tag_tbl_2 AS t2
JOIN (
SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y'))
AS t1
ON t1.user_group = t2.user_group;

Hasil berikut dikembalikan:

+------+------------+---------+
| tag2 | user_group | rb      |
+------+------------+---------+
| b    |          3 | 1041828 |
| a    |          15| 1039859 |
| a    |          9 | 1039140 |
| b    |          1 | 1041524 |
| a    |          4 | 1041599 |
| b    |          1 | 1041381 |
| b    |          10| 1041026 |
| b    |          6 | 1042289 |
+------+------------+---------+

Kueri data untuk grafik kolom yang menunjukkan pengguna yang memenuhi kondisi tag1 IN ('x', 'y') dalam dimensi `tag2`.

SELECT tag2, SUM(cnt) FROM ( 
SELECT tag2, t1.user_group AS user_group, RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS cnt FROM tag_tbl_2 AS t2
JOIN (
SELECT user_group, rb AS rb1 FROM tag_tbl_1 WHERE tag1 IN ('x', 'y'))
AS t1
ON t1.user_group = t2.user_group
) GROUP BY tag2;

Hasil berikut dikembalikan:

+------+----------+
| tag2 | sum(cnt) |
+------+----------+
| a    | 33327868 |
| b    | 33335220 |
+------+----------+

Skenario 2: Perhitungan irisan, gabungan, dan selisih untuk tabel tag Roaring Bitmap

Ambil data yang memenuhi kondisi tag1 = 'x' OR tag1 = 'y' dari tabel tag Roaring Bitmap `tag_tbl_1`. Ambil data yang memenuhi kondisi tag2 = 'b' dari tabel tag Roaring Bitmap `tag_tbl_2`. Lakukan perhitungan irisan pada data dari kedua tabel tag untuk mengidentifikasi pengguna yang memenuhi kedua kondisi: tag1 = 'x' OR tag1 = 'y' dan tag2 = 'b'.

SELECT user_group, RB_CARDINALITY(rb) FROM (
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb2
        FROM
          tag_tbl_2
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group);

Hasil berikut dikembalikan:

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|         10 |            2083679 |
|          3 |            2082370 |
|          9 |            2082847 |
|          2 |            2086511 |
...                              
|          1 |            2082291 |
|          4 |            2083290 |
|         14 |            2083581 |
|         15 |            2084110 |
+------------+--------------------+

Skenario 3: Perhitungan irisan, gabungan, dan selisih antara tabel tag asli dan tabel tag Roaring Bitmap

Ambil data yang memenuhi kondisi tag1 = 'x' OR tag1 = 'y' dari tabel tag Roaring Bitmap `tag_tbl_1`. Ambil data yang memenuhi kondisi tag2 = 'b' dari tabel tag asli `users`. Lakukan perhitungan irisan pada data dari kedua tabel untuk mendapatkan data yang memenuhi kedua kondisi: tag1 = 'x' OR tag1 = 'y' dan tag2 = 'b'.

SELECT user_group, RB_CARDINALITY(rb) FROM (
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_BUILD_AGG(offset) AS rb2
        FROM
          users
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group);

Hasil berikut dikembalikan:

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|          3 |            2082370 |
|          1 |            2082291 |
|          0 |            2082383 |
|          4 |            2083290 |
|         11 |            2081662 |
|         13 |            2085280 |
...                              
|         14 |            2083581 |
|         15 |            2084110 |
|          9 |            2082847 |
|          8 |            2084860 |
|          5 |            2083056 |
|          7 |            2083275 |
+------------+--------------------+

Skenario 4: Ekspor Roaring Bitmap dari Skenario 2 ke OSS (untuk tabel eksternal)

Buat tabel tag `tag_tbl_3` untuk mengekspor hasil perhitungan.

CREATE TABLE `tag_tbl_3` (
  `user_group` INT,
  `rb` ROARINGBITMAP
  )engine = 'oss'
TABLE_PROPERTIES = '{
"endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
"accessid":"************",
"accesskey":"************",
"url":"oss://testBucketName/roaringbitmap/tag_tbl_3/",
"format":"parquet"
}';

Ekspor hasil perhitungan dari Skenario 2 ke tabel tag `tag_tbl_3`.

INSERT OVERWRITE tag_tbl_3
    SELECT
      t1.user_group AS user_group,
      RB_AND(rb1, rb2) AS rb
    FROM
      (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb1
        FROM
          tag_tbl_1
        WHERE
          tag1 = 'x'
          OR tag1 = 'y'
        GROUP BY
          user_group
      ) AS t1
      JOIN (
        SELECT
          user_group,
          RB_OR_AGG(rb) AS rb2
        FROM
          tag_tbl_2
        WHERE
          tag2 = 'b'
        GROUP BY
          user_group
      ) AS t2 ON t1.user_group = t2.user_group;

Catatan

Setelah pernyataan SQL dieksekusi, file disimpan dalam format Parquet di path oss://testBucketName/roaringbitmap/tag_tbl_3/.

Skenario 5: Mempercepat kueri (untuk tabel eksternal)

Impor data dari tabel tag `tag1` ke tabel internal untuk mempercepat kueri.

Buat tabel internal bernama `tag_tbl_1_cstore` dan definisikan field `rb` sebagai tipe VARBINARY.
```
CREATE TABLE `tag_tbl_1_cstore` (
 `tag1` VARCHAR,
 `rb` VARBINARY,
 `user_group` INT
);
```

Impor data dari tabel tag `tag1` di OSS ke tabel internal `tag_tbl_1_cstore`.

INSERT INTO tag_tbl_1_cstore SELECT tag1, RB_TO_VARBINARY(rb), user_group FROM tag_tbl_1;

Kueri data dari tabel `tag_tbl_1_cstore`.

SELECT tag1, user_group, RB_CARDINALITY(RB_OR_AGG(RB_BUILD_VARBINARY(rb))) FROM tag_tbl_1_cstore GROUP BY tag1, user_group;

Hasil berikut dikembalikan:

+------+------------+---------------------------------------------------+
| tag1 | user_group | rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) |
+------+------------+---------------------------------------------------+
| y    |          3 |                                           2082919 |
| x    |          9 |                                           2083085 |
| x    |          3 |                                           2082140 |
| y    |         11 |                                           2082268 |
| z    |          4 |                                           2082451 |
...                                                                    
| z    |          2 |                                           2081560 |
| y    |          6 |                                           2082194 |
| z    |          7 |                                           2082608 |
+------+------------+---------------------------------------------------+