Untuk mengoptimalkan kueri kompleks yang berulang dan memakan waktu, Anda dapat menggunakan fitur tampilan yang di-materialisasi sinkron dalam ApsaraDB for SelectDB untuk menyimpan cache data dari kueri kompleks, memungkinkan akses cepat dan pemrosesan data yang efisien.
Ikhtisar
Tampilan yang di-materialisasi sinkron adalah dataset pra-komputasi berdasarkan pernyataan SELECT yang telah ditentukan dan disimpan dalam tabel khusus di SelectDB. Selama kueri, sistem secara otomatis mencocokkan tampilan yang di-materialisasi sinkron paling optimal dan membaca data langsung darinya. Tampilan yang di-materialisasi sinkron memiliki karakteristik berikut:
Dapat mengurangi waktu respons kueri.
Tampilan yang di-materialisasi sinkron mengurangi waktu respons kueri dengan menyimpan hasil komputasi sebelumnya.
Mekanisme sinkronisasi otomatis tidak memerlukan pemeliharaan data manual.
Data tetap konsisten dengan tabel dasar secara real-time.
Dalam skenario penyisipan, pembaruan, atau penghapusan data, SelectDB memastikan konsistensi kuat antara tabel dasar dan tampilan yang di-materialisasi sinkron, menjaga validitas data tampilan yang di-materialisasi melalui mekanisme pembaruan inkremental bawaan.
Catatan
Catatan Penggunaan:
Kueri langsung tidak didukung.
Berbeda dengan tampilan yang di-materialisasi asinkron, Anda tidak dapat menanyakan langsung tampilan yang di-materialisasi sinkron. Pernyataan kueri masih menanyakan tabel dasar. Ketika SelectDB menerima pernyataan kueri, ia secara otomatis memilih tampilan yang di-materialisasi paling optimal, membaca data dari tampilan yang di-materialisasi, dan melakukan perhitungan. Untuk informasi lebih lanjut, lihat Pencocokan Otomatis Kueri.
Dalam model Unik, Anda tidak dapat melakukan operasi agregasi kasar pada data dengan membuat tampilan yang di-materialisasi sinkron.
Hal ini karena tampilan yang di-materialisasi sinkron untuk model data Unik hanya dapat mengubah urutan kolom dan tidak dapat berfungsi sebagai tujuan agregasi.
Dampak kinerja: Terlalu banyak tampilan yang di-materialisasi sinkron pada satu tabel akan mempengaruhi efisiensi impor data.
Selama impor data, tampilan yang di-materialisasi sinkron dan data tabel dasar diperbarui secara sinkron.
Sebagai contoh: Jika lebih dari 10 tabel tampilan yang di-materialisasi sinkron dibuat untuk satu tabel, kecepatan impor mungkin menjadi sangat lambat. Hal ini karena satu operasi impor perlu mengimpor data ke lebih dari 10 tabel secara bersamaan.
Kasus penggunaan
Mempercepat operasi agregasi yang memakan waktu.
Kueri perlu mencocokkan indeks awalan yang berbeda.
Mengurangi jumlah data yang harus dipindai melalui pra-penyaringan.
Mempercepat kueri dengan pra-menghitung ekspresi kompleks.
Buat tampilan yang di-materialisasi
Prinsip
Sebelum membuat tampilan yang di-materialisasi, Anda perlu menentukan jenis tampilan yang di-materialisasi yang akan dibuat berdasarkan karakteristik pernyataan kueri. Memiliki definisi tampilan yang di-materialisasi yang persis cocok dengan pernyataan kueri tidak selalu merupakan situasi ideal. Berikut adalah dua prinsip untuk membuat tampilan yang di-materialisasi.
Abstraksi metode pengelompokan dan agregasi umum dari beberapa kueri sebagai definisi tampilan yang di-materialisasi.
Tampilan yang di-materialisasi yang diabstraksi yang dapat dicocokkan oleh beberapa kueri adalah yang paling efektif. Hal ini karena pemeliharaan tampilan yang di-materialisasi juga mengonsumsi sumber daya. Jika tampilan yang di-materialisasi hanya cocok untuk kueri tertentu dan tidak dapat digunakan oleh kueri lain, efisiensi biayanya akan rendah, karena ia menempati sumber daya penyimpanan kluster sementara tidak berguna untuk lebih banyak kueri.
Anda tidak perlu membuat tampilan yang di-materialisasi untuk semua kombinasi dimensi.
Dalam kueri analitik aktual, tidak semua analisis dimensi tercakup. Oleh karena itu, membuat tampilan yang di-materialisasi hanya untuk kombinasi dimensi yang sering digunakan dapat mencapai keseimbangan antara ruang dan waktu.
Catatan
Pernyataan SELECT
Hanya mendukung pernyataan SELECT berdasarkan satu tabel.
Kolom SELECT tidak boleh termasuk kolom auto-increment, konstanta, ekspresi duplikat, atau fungsi jendela.
Jika kolom SELECT termasuk kolom kunci partisi dan kolom bucketing dari tabel yang dibuat, kolom-kolom ini harus digunakan sebagai kolom Kunci dalam tampilan yang di-materialisasi.
Klausa
Klausa WHERE, GROUP BY, dan ORDER BY diperbolehkan, tetapi JOIN, HAVING, LIMIT, dan LATERAL VIEW dilarang.
Fungsi Agregat yang Didukung
PentingFungsi agregat parameter tidak mendukung ekspresi dan hanya mendukung kolom tunggal. Sebagai contoh:
sum(a)didukung, tetapisum(a+b)tidak didukung.Fungsi agregat yang berbeda tidak diizinkan pada kolom yang sama. Sebagai contoh:
select sum(a), min(a) from tabletidak didukung.
Saat ini, fungsi agregat berikut didukung dalam pernyataan untuk membuat tampilan yang di-materialisasi sinkron:
SUM, MIN, MAX.
COUNT.
Fungsi BITMAP_UNION mendukung pembuatan tampilan yang di-materialisasi hanya dalam dua kasus berikut:
Saat menggunakan format
BITMAP_UNION(TO_BITMAP(COLUMN)), kolom (COLUMN) tipe hanya bisa bilangan bulat, tidak termasuk tipelargeint.Saat menggunakan format
BITMAP_UNION(COLUMN), tabel dasar harus berupa model Aggregate.
Fungsi HLL_UNION mendukung pembuatan tampilan yang di-materialisasi hanya dalam dua kasus berikut:
Saat menggunakan format
HLL_UNION(HLL_HASH(COLUMN)), tipe kolom (COLUMN) tidak bolehDECIMAL.Saat menggunakan format
HLL_UNION(COLUMN), tabel dasar harus berupa model Aggregate.
Sintaks
CREATE MATERIALIZED VIEW <mv_name> as <query>
[PROPERTIES ("key" = "value")]Deskripsi Parameter
Nama Parameter | Wajib | Deskripsi |
mv_name | Ya | Nama tampilan yang di-materialisasi sinkron. Nama tampilan yang di-materialisasi untuk tabel dasar yang sama tidak boleh duplikat. |
query | Ya | Pernyataan kueri yang digunakan untuk membangun tampilan yang di-materialisasi sinkron. Hasil dari pernyataan kueri adalah data dalam tampilan yang di-materialisasi. Untuk informasi lebih lanjut, lihat deskripsi parameter kueri di bawah ini. |
properties | Tidak | Mendeklarasikan konfigurasi terkait dari tampilan yang di-materialisasi. Formatnya adalah sebagai berikut. Konfigurasi berikut dapat didefinisikan dalam properti ini. |
Deskripsi parameter kueri adalah sebagai berikut.
Format kueri yang saat ini didukung adalah sebagai berikut.
SELECT select_expr[, select_expr ...]FROM <base_view_name>GROUP BY column_name[, column_name ...]ORDER BY column_name[, column_name ...]Deskripsi parameter adalah sebagai berikut.
Nama Parameter | Wajib | Deskripsi |
select_expr | Ya | Semua kolom dalam skema tampilan yang di-materialisasi sinkron. Harus mencakup setidaknya satu kolom tunggal. |
base_view_name | Ya | Tabel dasar dari tampilan yang di-materialisasi sinkron. Harus berupa tabel tunggal, bukan subkueri. |
group by | Tidak | Kolom pengelompokan dari tampilan yang di-materialisasi sinkron. Jika tidak ditentukan, data tidak dikelompokkan. |
order by | Tidak | Pengurutan dari tampilan yang di-materialisasi sinkron.
|
Contoh
Siapkan tabel dasar contoh.
Buat tabel contoh
duplicate_table, seperti yang ditunjukkan di bawah ini.CREATE TABLE duplicate_table( k1 int null, k2 int null, k3 bigint null, k4 bigint null ) DUPLICATE KEY (k1,k2,k3,k4) DISTRIBUTED BY HASH(k4) BUCKETS 3;Kueri struktur tabel dari tabel contoh
duplicate_tablesebagai berikut.DESC duplicate_table; +-------+--------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------+------+------+---------+-------+ | k1 | INT | Yes | true | N/A | | | k2 | INT | Yes | true | N/A | | | k3 | BIGINT | Yes | true | N/A | | | k4 | BIGINT | Yes | true | N/A | | +-------+--------+------+------+---------+-------+Buat tampilan yang di-materialisasi sinkron.
Buat tampilan yang di-materialisasi yang hanya berisi kolom (k1, k2) dari tabel asli, seperti yang ditunjukkan di bawah ini.
CREATE MATERIALIZED VIEW k1_k2 AS SELECT k2, k1 FROM duplicate_table;Skema dari tampilan yang di-materialisasi adalah sebagai berikut. Tampilan yang di-materialisasi hanya mencakup dua kolom k1, k2 dan tidak termasuk agregasi apa pun.
+-----------------+-------+--------+------+------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+------+---------+-------+ | k2_k1 | k2 | INT | Yes | true | N/A | | | | k1 | INT | Yes | true | N/A | | +-----------------+-------+--------+------+------+---------+-------+Buat tampilan yang di-materialisasi sinkron dengan k2 sebagai kolom pengurutan, seperti yang ditunjukkan di bawah ini.
CREATE MATERIALIZED VIEW k2_order AS SELECT k2, k1 FROM duplicate_table ORDER BY k2;Skema dari tampilan yang di-materialisasi adalah sebagai berikut. Tampilan yang di-materialisasi hanya mencakup dua kolom k2, k1, di mana k2 adalah kolom pengurutan dan tidak termasuk agregasi apa pun.
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | k2_order | k2 | INT | Yes | true | N/A | | | | k1 | INT | Yes | false | N/A | NONE | +-----------------+-------+--------+------+-------+---------+-------+Buat tampilan yang di-materialisasi dikelompokkan berdasarkan k1, k2, dengan agregasi SUM pada kolom k3, seperti yang ditunjukkan di bawah ini.
CREATE MATERIALIZED VIEW k1_k2_sumk3 AS SELECT k1, k2, sum(k3) FROM duplicate_table GROUP BY k1, k2;Skema dari tampilan yang di-materialisasi adalah sebagai berikut. Ini mencakup dua kolom k1, k2, sum(k3). Di sini, k1, k2 adalah kolom pengelompokan, dan sum(k3) adalah jumlah kolom k3 setelah dikelompokkan berdasarkan k1, k2. Karena tampilan yang di-materialisasi tidak mendeklarasikan kolom pengurutan dan mencakup data agregat, sistem secara otomatis melengkapi kolom pengelompokan k1, k2 sebagai kolom pengurutan.
+-----------------+-------+--------+------+-------+---------+-------+ | IndexName | Field | Type | Null | Key | Default | Extra | +-----------------+-------+--------+------+-------+---------+-------+ | k1_k2_sumk3 | k1 | INT | Yes | true | N/A | | | | k2 | INT | Yes | true | N/A | | | | k3 | BIGINT | Yes | false | N/A | SUM | +-----------------+-------+--------+------+-------+---------+-------+
Kueri tampilan yang di-materialisasi
Kueri semua tampilan yang di-materialisasi yang dibuat berdasarkan tabel tertentu.
Sintaks
DESC <table_name> ALL;Deskripsi Parameter
table_name adalah tabel dasar dari tampilan yang di-materialisasi.
Contoh
Kueri tampilan yang di-materialisasi dengan duplicate_table sebagai tabel dasar.
DESC duplicate_table ALL;Hasil kueri adalah sebagai berikut.
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | |
| | | k2 | INT | INT | Yes | true | NULL | | true | | |
| | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | |
| | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | |
| | | | | | | | | | | | |
| k2_order | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mv_k1 | INT | INT | Yes | false | NULL | NONE | true | `k1` | |
| | | | | | | | | | | | |
| k1_k2 | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | |
| | | | | | | | | | | | |
| k1_k2_sumk3 | AGG_KEYS | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | |
| | | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | |
| | | mva_SUM__`k3` | BIGINT | BIGINT | Yes | false | NULL | SUM | true | `k3` | |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+Berdasarkan hasil kueri, tabel contoh duplicate_table memiliki total tiga tampilan yang di-materialisasi: k1_k2, k2_order dan k1_k2_sumk3, beserta struktur tabel mereka.
Hapus tampilan yang di-materialisasi
Jika Anda tidak lagi memerlukan tampilan yang di-materialisasi sinkron, Anda dapat menghapusnya atau membatalkan pembuatannya melalui perintah.
Hapus tampilan yang di-materialisasi yang belum selesai
Jika Anda ingin membatalkan pembuatan tampilan yang di-materialisasi yang belum selesai, Anda dapat menggunakan perintah berikut.
Sintaks
CANCEL ALTER TABLE MATERIALIZED VIEW FROM <database>.<table_name>;Deskripsi Parameter
Nama Parameter
Wajib
Deskripsi
database
Ya
Nama database tempat tampilan yang di-materialisasi yang akan dihapus berada.
table_name
Ya
Nama tabel tempat tampilan yang di-materialisasi yang akan dihapus berada.
Contoh
Batalkan semua tampilan yang di-materialisasi yang belum selesai untuk tabel dasar duplicate_table.
CANCEL ALTER TABLE MATERIALIZED VIEW FROM test_db.duplicate_table;Jika tampilan yang di-materialisasi sudah dibuat, pembatalan tidak dapat dilakukan menggunakan perintah ini, tetapi dapat dihapus menggunakan perintah hapus.
Hapus tampilan yang di-materialisasi yang telah selesai
Jika tampilan yang di-materialisasi sudah dibuat, Anda dapat menghapusnya menggunakan perintah hapus.
Sintaks
DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;Deskripsi Parameter
Nama Parameter
Wajib
Deskripsi
IF EXISTS
Tidak
Jika tampilan yang di-materialisasi tidak ada, jangan lempar kesalahan. Jika kata kunci ini tidak ditentukan, kesalahan akan dilaporkan jika tampilan yang di-materialisasi tidak ada.
mv_name
Ya
Nama tampilan yang di-materialisasi yang akan dihapus.
table_name
Ya
Tabel dasar dari tampilan yang di-materialisasi yang akan dihapus.
Contoh
Lihat tampilan yang di-materialisasi dari tabel dasar
duplicate_tabledan struktur tabel mereka, seperti yang ditunjukkan di bawah ini.DESC duplicate_table ALL;Hasil kueri adalah sebagai berikut.
+-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | | | | | k2 | INT | INT | Yes | true | NULL | | true | | | | | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | | | | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | | | | | | | | | | | | | | | | k1_k2 | DUP_KEYS | mv_k2 | INT | INT | Yes | true | NULL | | true | `k2` | | | | | mv_k1 | INT | INT | Yes | true | NULL | | true | `k1` | | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+Hapus tampilan yang di-materialisasi bernama
k1_k2dari tabel dasarduplicate_table.DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;Setelah menghapus tampilan yang di-materialisasi, lihat tampilan yang di-materialisasi dari tabel dasar
duplicate_tabledan struktur tabel mereka, seperti yang ditunjukkan di bawah ini.DESC duplicate_table ALL;Hasil kueri adalah sebagai berikut.
+-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+ | duplicate_table | DUP_KEYS | k1 | INT | INT | Yes | true | NULL | | true | | | | | | k2 | INT | INT | Yes | true | NULL | | true | | | | | | k3 | BIGINT | BIGINT | Yes | true | NULL | | true | | | | | | k4 | BIGINT | BIGINT | Yes | true | NULL | | true | | | +-----------------+---------------+-------+--------+--------------+------+------+---------+-------+---------+------------+-------------+
Lihat status
Membuat tampilan yang di-materialisasi sinkron adalah operasi asinkron. Setelah Anda mengirimkan tugas pembuatan, SelectDB menghitung data historis di latar belakang hingga pembuatan berhasil. Anda dapat memeriksa apakah tampilan yang di-materialisasi telah dibangun menggunakan perintah.
Sintaks
SHOW ALTER TABLE MATERIALIZED VIEW FROM <database>Deskripsi Parameter
database adalah database tempat tabel dasar dari tampilan yang di-materialisasi sinkron berada. Hasil dari perintah ini akan menampilkan semua tugas pembuatan tampilan yang di-materialisasi untuk database tersebut.
Contoh
Lihat informasi tugas pembuatan tampilan yang di-materialisasi dalam database test_db.
SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;
-- Hasil
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId | TableName | CreateTime | FinishTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt | 494350 | 133107 | FINISHED | | NULL | 2592000 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+Deskripsi hasil adalah sebagai berikut.
Nama Bidang | Deskripsi |
| Mengacu pada tabel sumber data tampilan yang di-materialisasi. |
| Nama tabel dasar. |
| Nama tampilan yang di-materialisasi. |
| Status tugas.
Ketika State menjadi FINISHED, tampilan yang di-materialisasi telah berhasil dibuat. |
Lihat pernyataan
Lihat pernyataan yang digunakan untuk membuat tampilan yang di-materialisasi melalui perintah.
Sintaks
Sintaks ini tidak dapat menanyakan tampilan yang di-materialisasi yang sudah dihapus.
SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>Deskripsi Parameter
Nama Parameter | Wajib | Deskripsi |
mv_name | Ya | Nama tampilan yang di-materialisasi. |
table_name | Ya | Tabel dasar dari tampilan yang di-materialisasi. |
Contoh
Siapkan contoh tampilan yang di-materialisasi dengan pernyataan pembuatan berikut.
CREATE MATERIALIZED VIEW id_col1 AS SELECT id,col1 FROM table3;Lihat pernyataan pembuatan Tampilan yang di-materialisasi yang dibuat, seperti yang ditunjukkan di bawah ini.
SHOW CREATE MATERIALIZED VIEW id_col1 ON table3;Hasil kueri adalah sebagai berikut.
SHOW CREATE MATERIALIZED VIEW id_col1 on table3; +-----------+----------+----------------------------------------------------------------+ | TableName | ViewName | CreateStmt | +-----------+----------+----------------------------------------------------------------+ | table3 | id_col1 | create materialized view id_col1 as select id,col1 from table3 | +-----------+----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
Pencocokan Kueri Otomatis
Setelah Tampilan yang di-materialisasi berhasil dibuat, kueri tidak perlu diubah sama sekali dan tetap mengkueri tabel dasar. SelectDB akan secara otomatis memilih Tampilan yang di-materialisasi yang paling optimal berdasarkan Pernyataan kueri saat ini, membaca data dari Tampilan yang di-materialisasi, dan melakukan perhitungan.
Anda dapat menggunakan EXPLAIN perintah untuk memeriksa apakah kueri saat ini menggunakan tampilan yang di-materialisasi. Untuk informasi selengkapnya tentang EXPLAIN, lihat Query Explain.
Hubungan pencocokan antara agregasi Tampilan yang di-materialisasi dan agregasi kueri adalah sebagai berikut.
Agregasi Tampilan Termaterialisasi | Agregasi Kueri |
sum | sum |
min | min |
max | max |
count | count |
bitmap_union | bitmap_union, bitmap_union_count, count(distinct) |
hll_union | hll_raw_agg, hll_union_agg, ndv, approx_count_distinct |
Ketika fungsi agregat dari bitmap dan hll cocok dengan tampilan yang di-materialisasi selama kueri, operator agregasi kueri tersebut akan ditulis ulang berdasarkan skema tabel dari tampilan yang di-materialisasi.
Contoh penggunaan lengkap
Menggunakan tampilan yang di-materialisasi melibatkan tiga langkah berikut:
Buat tampilan yang di-materialisasi.
Periksa secara asinkron apakah pembuatan tampilan yang di-materialisasi telah selesai.
Kueri dan otomatis cocokkan dengan tampilan yang di-materialisasi.
Latar belakang contoh
Sebagai contoh, dalam skenario bisnis yang menghitung PV iklan dan UV, data klik iklan asli disimpan di SelectDB. Oleh karena itu, kueri PV dan UV iklan dapat dipercepat dengan membuat tampilan yang di-materialisasi menggunakan tipe agregasi bitmap_union.
Persiapan: Desain tabel dasar
Buat tabel advertiser_view_record untuk menyimpan detail data klik iklan. Bidang mencakup waktu setiap klik iklan, iklan yang diklik, saluran tempat terjadinya klik, dan pengguna yang melakukan klik. Pernyataan spesifik adalah sebagai berikut:
CREATE TABLE advertiser_view_record(
time date,
advertiser varchar(10),
channel varchar(10),
user_id int
)
DISTRIBUTED BY HASH(time);Kueri struktur tabel dari tabel data klik iklan asli ditunjukkan di bawah ini:
DESC advertiser_view_record ALL;
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
| advertiser_view_record | DUP_KEYS | time | DATE | DATEV2 | Yes | true | NULL | | true | | |
| | | advertiser | VARCHAR(10) | VARCHAR(10) | Yes | true | NULL | | true | | |
| | | channel | VARCHAR(10) | VARCHAR(10) | Yes | false | NULL | NONE | true | | |
| | | user_id | INT | INT | Yes | false | NULL | NONE | true | | |
+------------------------+---------------+------------+-------------+--------------+------+-------+---------+-------+---------+------------+-------------+
4 rows in set (0.02 sec)Langkah 1: Buat tampilan yang di-materialisasi
Analisis desain.
Untuk menanyakan nilai UV iklan, Anda perlu melakukan deduplikasi tepat pada pengguna untuk iklan yang sama. Metode kueri biasa adalah sebagai berikut:
SELECT advertiser, channel, COUNT(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;Untuk jenis skenario kueri UV ini, Anda dapat membuat tampilan yang di-materialisasi dengan
bitmap_unionuntuk mencapai pre-deduplikasi tepat. Di SelectDB, hasil daricount(distinct)agregasi danbitmap_union_countagregasi sepenuhnya konsisten. Danbitmap_union_countsama dengan menghitung hasil daribitmap_union. Jadi jika sebuah kueri melibatkancount(distinct), kecepatan kueri dapat dipercepat dengan membuat tampilan yang di-materialisasi denganbitmap_unionagregasi. Untuk kasus ini, Anda dapat membuat tampilan yang di-materialisasi yang mengelompokkan berdasarkan iklan dan saluran serta melakukan deduplikasi tepat pada kolomuser_id.Buat tampilan yang di-materialisasi.
Berdasarkan analisis di atas, buat tampilan yang di-materialisasi yang mengelompokkan berdasarkan iklan dan saluran, dan melakukan deduplikasi tepat pada
user_id. Pernyataannya adalah sebagai berikut:CREATE MATERIALIZED VIEW advertiser_uv AS SELECT advertiser, channel, bitmap_union(to_bitmap(user_id)) FROM advertiser_view_record GROUP BY advertiser, channel; Query OK, 0 rows affected (0.012 sec)CatatanKarena user_id adalah tipe INT, di SelectDB Anda perlu terlebih dahulu mentransformasi bidang tersebut menjadi tipe bitmap menggunakan fungsi
to_bitmapsebelum Anda dapat melakukanbitmap_unionagregasi.Setelah membuat tampilan yang di-materialisasi, kueri tampilan yang di-materialisasi dari tabel detail klik iklan dan struktur tabel mereka ditunjukkan di bawah ini:
DESC advertiser_view_record ALL; +------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+ | IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause | +------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+ | advertiser_view_record | DUP_KEYS | time | DATE | DATEV2 | Yes | true | NULL | | true | | | | | | advertiser | VARCHAR(10) | VARCHAR(10) | Yes | true | NULL | | true | | | | | | channel | VARCHAR(10) | VARCHAR(10) | Yes | false | NULL | NONE | true | | | | | | user_id | INT | INT | Yes | false | NULL | NONE | true | | | | | | | | | | | | | | | | | advertiser_uv | AGG_KEYS | mv_advertiser | VARCHAR(*) | VARCHAR(*) | Yes | true | NULL | | true | `advertiser` | | | | | mv_channel | VARCHAR(*) | VARCHAR(*) | Yes | true | NULL | | true | `channel` | | | | | mva_BITMAP_UNION__to_bitmap_with_check(CAST(`user_id` AS BIGINT)) | BITMAP | BITMAP | No | false | NULL | BITMAP_UNION | true | to_bitmap_with_check(CAST(`user_id` AS BIGINT)) | | +------------------------+---------------+-------------------------------------------------------------------+-------------+--------------+------+-------+---------+--------------+---------+-------------------------------------------------+-------------+ 8 rows in set (0.03 sec)
Langkah 2: Periksa apakah pembuatan tampilan sudah selesai
Gunakan perintah Lihat status. Ketika statusnya FINISHED, tampilan yang di-materialisasi telah berhasil dibuat.
Langkah 3: Kueri pencocokan otomatis
Jalankan pernyataan kueri.
Ketika tabel tampilan yang di-materialisasi dibuat, ketika menanyakan UV iklan, SelectDB akan secara otomatis menanyakan data dari tampilan yang di-materialisasi yang telah dibuat
advertiser_uv. Contohnya sebagai berikut:SELECT advertiser, channel, COUNT(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel;Setelah mencocokkan tampilan yang di-materialisasi, SelectDB secara implisit mentransformasi kueri aktual menjadi kueri berikut:
SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id)) FROM advertiser_uv GROUP BY advertiser, channel;Verifikasi apakah kueri menggunakan tampilan yang di-materialisasi.
Anda dapat menggunakan perintah EXPLAIN untuk memverifikasi apakah SelectDB telah mencocokkan tampilan yang di-materialisasi, seperti yang ditunjukkan di bawah ini:
EXPLAIN SELECT advertiser, channel, count(distinct user_id) FROM advertiser_view_record GROUP BY advertiser, channel; +-------------------------------------------------------------------------------------------------------------------------------------------------+ | Explain String | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | PLAN FRAGMENT 0 | | OUTPUT EXPRS: | | advertiser[#13] | | channel[#14] | | count(DISTINCT user_id)[#15] | | PARTITION: UNPARTITIONED | | | | VRESULT SINK | | | | 4:VEXCHANGE | | offset: 0 | | | | PLAN FRAGMENT 1 | | | | PARTITION: HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] | | | | STREAM DATA SINK | | EXCHANGE ID: 04 | | UNPARTITIONED | | | | 3:VAGGREGATE (merge finalize) | | | output: bitmap_union_count(partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#9])[#12] | | | group by: mv_advertiser[#7], mv_channel[#8] | | | cardinality=1 | | | projections: mv_advertiser[#10], mv_channel[#11], bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#12] | | | project output tuple id: 4 | | | | | 2:VEXCHANGE | | offset: 0 | | | | PLAN FRAGMENT 2 | | | | PARTITION: HASH_PARTITIONED: time[#3] | | | | STREAM DATA SINK | | EXCHANGE ID: 02 | | HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] | | | | 1:VAGGREGATE (update serialize) | | | STREAMING | | | output: partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT))[#2])[#9] | | | group by: mv_advertiser[#0], mv_channel[#1] | | | cardinality=1 | | | | | 0:VOlapScanNode | | TABLE: default_cluster:test.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON | | partitions=1/1, tablets=10/10, tabletList=13531,13533,13535 ... | | cardinality=1, avgRowSize=2745.0, numNodes=1 | | pushAggOp=NONE | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 49 rows in set (0.11 sec)Di hasil EXPLAIN, pertama-tama Anda dapat melihat bahwa nilai atribut
rollupdari OlapScanNode adalahadvertiser_uv. Ini menunjukkan bahwa kueri akan langsung memindai data dari tampilan yang di-materialisasi, mengonfirmasi kecocokan yang berhasil.Selain itu, untuk bidang
user_id,count(distinct)ditulis ulang sebagaibitmap_union_count(to_bitmap). Ini menunjukkan bahwa kueri akan menggunakan metode Bitmap untuk mencapai deduplikasi tepat.
FAQ
Q: Error: DATA_QUALITY_ERR: "Kualitas data tidak memenuhi syarat, silakan periksa data Anda."
A: Kesalahan ini terjadi karena masalah kualitas data atau perubahan skema yang menyebabkan penggunaan memori melebihi batas, sehingga pembuatan tampilan yang di-materialisasi gagal. Jika ini disebabkan oleh masalah memori, tingkatkan parameter
memory_limitation_per_thread_for_schema_change_bytes.PentingTipe Bitmap hanya mendukung bilangan bulat positif. Jika ada bilangan negatif dalam data asli, pembuatan tampilan yang di-materialisasi akan gagal.
Untuk bidang tipe String, Anda dapat menggunakan fungsi bitmap_hash atau bitmap_hash64 untuk menghitung nilai Hash dan mengembalikan bitmap dari nilai Hash tersebut.