operasi penulisan ulang kueri tampilan materialisasi - MaxCompute

MaxCompute mendukung penulisan ulang pernyataan SQL query asli, termasuk kondisi filter dan jenis operator tertentu, menjadi tampilan materialisasi guna meningkatkan efisiensi pengambilan data. Topik ini memberikan panduan komprehensif mengenai proses penulisan ulang SQL.

Batasan

Untuk memanfaatkan tampilan materialisasi dalam penulisan ulang kueri, Anda harus mengaktifkan fitur tersebut dengan menambahkan konfigurasi set odps.sql.materialized.view.enable.auto.rewriting=true; sebelum pernyataan kueri. Penulisan ulang tidak didukung jika tampilan materialisasi berada dalam keadaan tidak valid, dan dalam kasus seperti itu, kueri akan menggunakan tabel sumber secara default, sehingga melewati potensi percepatan apa pun.
Catatan
Secara default, proyek MaxCompute hanya dapat menulis ulang kueri menggunakan tampilan materialisasinya sendiri. Jika Anda perlu menggunakan tampilan materialisasi dari proyek lain, tambahkan konfigurasi set odps.sql.materialized.view.source.project.white.list=<project_name1>,<project_name2>,<project_name3>; sebelum pernyataan kueri. Untuk tampilan materialisasi yang mengandung left/right join atau union all, aktifkan konfigurasi: set odps.sql.materialized.view.enable.substitute.rewriting=true;.
Agar penulisan ulang kueri tampilan materialisasi dapat bekerja, data yang diperlukan, termasuk kolom output, kolom kondisi filter, kolom fungsi agregat, dan kolom kondisi JOIN, harus tersedia di tampilan materialisasi. Jika kolom atau fungsi agregat yang diperlukan tidak didukung oleh tampilan materialisasi, penulisan ulang tidak dapat dilakukan.

Contoh penulisan ulang

Tabel berikut menunjukkan jenis operator yang didukung untuk penulisan ulang kueri tampilan materialisasi di MaxCompute, bersama dengan hubungan korespondensinya dengan produk lain:

Jenis Operator	Klasifikasi	MaxCompute	BigQuery	Amazon RedShift	Hive
FILTER	Pencocokan penuh ekspresi	Didukung	Didukung	Didukung	Didukung
FILTER	Pencocokan sebagian ekspresi	Didukung	Didukung	Didukung	Didukung
AGGREGATE	AGGREGATE Tunggal	Didukung	Didukung	Didukung	Didukung
AGGREGATE	AGGREGATE Ganda	Tidak didukung	Tidak didukung	Tidak didukung	Tidak didukung
JOIN	Jenis JOIN	INNER JOIN	Tidak didukung	INNER JOIN	INNER JOIN
	JOIN Tunggal	Didukung	Tidak didukung	Didukung	Didukung
	JOIN Ganda	Didukung	Tidak didukung	Didukung	Didukung
AGGREGATE+JOIN	-	Didukung	Tidak didukung	Didukung	Didukung

Tulis ulang pernyataan kueri dengan kondisi filter

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv AS SELECT a,b,c FROM src WHERE a>5;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan kueri asli	Pernyataan kueri yang ditulis ulang
`SELECT a,b FROM src WHERE a>5;`	`SELECT a,b FROM mv;`
`SELECT a, b FROM src WHERE a=10;`	`SELECT a,b FROM mv WHERE a=10;`
`SELECT a, b FROM src WHERE a=10 AND b=3;`	`SELECT a,b FROM mv WHERE a=10 AND b=3;`
`SELECT a, b FROM src WHERE a>3;`	`(SELECT a,b FROM src WHERE a>3 AND a<=5) UNION (SELECT a,b FROM mv);`
`SELECT a, b FROM src WHERE a=10 AND d=4;`	Penulisan ulang gagal karena tampilan materialisasi tidak memiliki kolom d.
`SELECT d, e FROM src WHERE a=10;`	Penulisan ulang gagal karena tampilan materialisasi tidak memiliki kolom d dan e.
`SELECT a, b FROM src WHERE a=1;`	Penulisan ulang gagal karena tampilan materialisasi tidak memiliki data dimana a=1.

Tulis ulang pernyataan kueri dengan fungsi agregat

Penulisan ulang didukung jika pernyataan SQL tampilan materialisasi dan pernyataan kueri memiliki kunci agregasi yang sama. Jika kunci agregasi berbeda, hanya SUM, MIN, dan MAX yang didukung.

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv AS 
SELECT a, b, sum(c) AS sum, count(d) AS cnt FROM src GROUP BY a, b;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan kueri asli	Pernyataan kueri yang ditulis ulang
`SELECT a, sum(c) FROM src GROUP BY a;`	`SELECT a, sum(sum) FROM mv GROUP BY a;`
`SELECT a, count(d) FROM src GROUP BY a, b;`	`SELECT a, cnt FROM mv;`
`SELECT a, count(b) FROM (SELECT a, b FROM src GROUP BY a, b) GROUP BY a;`	`SELECT a,count(b) FROM mv GROUP BY a;`
`SELECT a,count(b) FROM mv GROUP BY a;`	Penulisan ulang gagal karena tampilan telah melakukan agregasi pada kolom a dan b, sehingga tidak dapat mengagregasi b lagi.
`SELECT a, count(c) FROM src GROUP BY a;`	Penulisan ulang gagal karena re-agregasi tidak didukung untuk fungsi COUNT.

Ketika fungsi agregat mencakup DISTINCT, penulisan ulang didukung jika pernyataan SQL tampilan materialisasi dan pernyataan kueri memiliki kunci agregasi yang sama. Jika tidak, penulisan ulang tidak didukung.

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv AS 
SELECT a, b, sum(DISTINCT c) AS sum, count(DISTINCT d) AS cnt FROM src GROUP BY a, b;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan Kueri Asli	Pernyataan Kueri yang Direvisi
`SELECT a, count(DISTINCT d) FROM src GROUP BY a, b;`	`SELECT a, cnt FROM mv;`
`SELECT a, count(c) FROM src GROUP BY a, b;`	Penulisan ulang gagal karena re-agregasi tidak didukung untuk fungsi COUNT.
`SELECT a, count(DISTINCT c) FROM src GROUP BY a;`	Penulisan ulang gagal karena diperlukan agregasi tambahan pada a.

Tulis ulang pernyataan kueri dengan JOIN

Tulis Ulang Input JOIN

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv1 AS SELECT a, b FROM j1 WHERE b > 10;
CREATE MATERIALIZED VIEW mv2 AS SELECT a, b FROM j2 WHERE b > 10;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan kueri asli

Pernyataan kueri yang ditulis ulang

SELECT j1.a,j1.b,j2.a FROM (SELECT a,b FROM j1 WHERE b > 10) j1 JOIN j2 ON j1.a=j2.a;

SELECT mv1.a, mv1.b, j2.a FROM mv1 JOIN j2 ON mv1.a=j2.a;

SELECT j1.a,j1.b,j2.a FROM 
(SELECT a,b FROM j1 WHERE b > 10) j1 
JOIN (SELECT a,b FROM j2 WHERE b > 10) j2 ON j1.a=j2.a;

SELECT mv1.a,mv1.b,mv2.a FROM mv1 JOIN mv2 ON mv1.a=mv2.a;

JOIN dengan Kondisi Filter

Buat tampilan materialisasi dengan pernyataan berikut:

--Buat tampilan materialisasi tanpa partisi.
CREATE MATERIALIZED VIEW mv1 AS SELECT j1.a, j1.b FROM j1 JOIN j2 ON j1.a=j2.a;
CREATE MATERIALIZED VIEW mv2 AS SELECT j1.a, j1.b FROM j1 JOIN j2 ON j1.a=j2.a WHERE j1.a > 10;
--Buat tampilan materialisasi dengan partisi.
CREATE MATERIALIZED VIEW mv LIFECYCLE 7 PARTITIONED BY (ds) AS SELECT t1.id, t1.ds AS ds FROM t1 JOIN t2 ON t1.id = t2.id;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan kueri asli	Pernyataan kueri yang ditulis ulang
`SELECT j1.a,j1.b FROM j1 JOIN j2 ON j1.a=j2.a WHERE j1.a=4;`	`SELECT a, b FROM mv1 WHERE a=4;`
`SELECT j1.a,j1.b FROM j1 JOIN j2 ON j1.a=j2.a WHERE j1.a > 20;`	`SELECT a,b FROM mv2 WHERE a>20;`
`SELECT j1.a,j1.b FROM j1 JOIN j2 ON j1.a=j2.a WHERE j1.a > 5;`	`(SELECT j1.a,j1.b FROM j1 JOIN j2 ON j1.a=j2.a WHERE j1.a > 5 AND j1.a <= 10) UNION SELECT * FROM mv2;`
`SELECT key FROM t1 JOIN t2 ON t1.id= t2.id WHERE t1.ds='20210306';`	`SELECT key FROM mv WHERE ds='20210306';`
`SELECT key FROM t1 JOIN t2 ON t1.id= t2.id WHERE t1.ds>='20210306';`	`SELECT key FROM mv WHERE ds>='20210306';`
`SELECT j1.a,j1.b FROM j1 JOIN j2 ON j1.a=j2.a WHERE j2.a=4;`	Penulisan ulang gagal karena tampilan materialisasi tidak memiliki kolom j2.a.

JOIN dengan Tabel Tambahan

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv AS SELECT j1.a, j1.b FROM j1 JOIN j2 ON j1.a=j2.a;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan kueri asli

Pernyataan kueri yang ditulis ulang

SELECT j1.a, j1.b FROM j1 JOIN j2 JOIN j3 ON j1.a=j2.a AND j1.a=j3.a;

SELECT mv.a, mv.b FROM mv JOIN j3 ON mv.a=j3.a;

SELECT j1.a, j1.b FROM j1 JOIN j2 JOIN j3 ON j1.a=j2.a AND j2.a=j3.a;

SELECT mv.a, mv.b FROM mv JOIN j3 ON mv.a=j3.a;

Catatan

Ketiga jenis pernyataan di atas dapat digabungkan. Jika pernyataan kueri memenuhi kondisi untuk penulisan ulang, maka dapat ditulis ulang sesuai dengan itu.
MaxCompute memilih aturan penulisan ulang yang paling efisien untuk eksekusi. Jika operasi pasca-penulisan ulang tidak menghasilkan rencana eksekusi optimal, aturan penulisan ulang tersebut tidak akan dipilih.

Tulis ulang pernyataan kueri dengan LEFT JOIN

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv LIFECYCLE 7(
        user_id,
        job,
        total_amount
) AS SELECT t1.user_id, t1.job, sum(t2.order_amount) AS total_amount 
      FROM user_info AS t1 LEFT JOIN sale_order AS t2 ON t1.user_id=t2.user_id GROUP BY t1.user_id;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:

Pernyataan kueri asli	Pernyataan kueri yang ditulis ulang
`SELECT t1.user_id, sum(t2.order_amount) AS total_amount FROM user_info AS t1 LEFT JOIN sale_order AS t2 ON t1.user_id=t2.user_id GROUP BY t1.user_id;`	`SELECT user_id, total_amount FROM mv;`

Tulis ulang pernyataan kueri dengan UNION ALL

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW mv LIFECYCLE 7( 
        user_id, 
        tran_amount, 
        tran_date 
) AS SELECT user_id, tran_amount, tran_date FROM alipay_tran UNION ALL 
SELECT user_id, tran_amount, tran_date FROM unionpay_tran;

Tabel berikut menunjukkan perbandingan antara pernyataan kueri asli dan pernyataan kueri yang ditulis ulang untuk menjalankan kueri berdasarkan tampilan materialisasi yang dibuat:
Pernyataan kueri asli
Pernyataan kueri yang ditulis ulang
SELECT user_id, tran_amount FROM alipay_tran UNION ALL SELECT user_id, tran_amount FROM unionpay_tran;
SELECT user_id, total_amount FROM mv;

Skenario penggunaan

Anda memiliki tabel akses halaman bernama visit_records, yang mencatat ID halaman, ID pengguna, dan waktu akses setiap pengguna. Pengguna sering menganalisis lalu lintas halaman yang berbeda. Struktur visit_records adalah sebagai berikut:

+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| page_id         | string     |       |                                             |
| user_id         | string     |       |                                             |
| visit_time      | string     |       |                                             |
+------------------------------------------------------------------------------------+

Untuk memudahkan analisis, buat tampilan materialisasi untuk tabel visit_records yang dikelompokkan berdasarkan ID halaman dan menghitung jumlah kunjungan per halaman. Kemudian, lakukan operasi kueri berdasarkan tampilan materialisasi.

Buat tampilan materialisasi dengan pernyataan berikut:

CREATE MATERIALIZED VIEW count_mv AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

Jalankan pernyataan kueri sebagai berikut:
```
SET odps.sql.materialized.view.enable.auto.rewriting=true; 
SELECT page_id, count(*) FROM visit_records GROUP BY page_id;
```
Saat kueri ini dijalankan, MaxCompute secara otomatis mencocokkan tampilan materialisasi count_mv dan mengambil data agregat darinya.
Verifikasi apakah pernyataan kueri cocok dengan tampilan materialisasi menggunakan perintah berikut:
```
EXPLAIN SELECT page_id, count(*) FROM visit_records GROUP BY page_id;
```
Hasilnya adalah sebagai berikut:
```
job0 is root job

In Job job0:
root Tasks: M1

In Task M1:
    Data source: doc_test_dev.count_mv
    TS: doc_test_dev.count_mv
        FS: output: Screen
            schema:
              page_id (string)
              _c1 (bigint)


OK
```
Data source dalam hasil menunjukkan bahwa kueri membaca dari tabel count_mv proyek doc_test_dev, yang menegaskan validitas tampilan materialisasi dan keberhasilan penulisan ulang kueri.

Pernyataan kueri asli	Pernyataan kueri yang ditulis ulang
`SELECT user_id, tran_amount FROM alipay_tran UNION ALL SELECT user_id, tran_amount FROM unionpay_tran;`	`SELECT user_id, total_amount FROM mv;`