MaxCompute:CREATE MATERIALIZED VIEW

Informasi latar belakang

Tampilan adalah kueri tersimpan yang dapat diakses sebagai tabel virtual. Setiap kali Anda mengkueri tampilan, pernyataan kueri dikonversi menjadi Pernyataan SQL yang digunakan untuk mendefinisikan tampilan. Tampilan materialized adalah tabel fisik khusus yang menggunakan sumber daya penyimpanan untuk menyimpan data nyata. Untuk informasi lebih lanjut tentang aturan penagihan tampilan materialized, lihat bagian Aturan Penagihan dalam topik ini.

Tabel berikut membandingkan kueri tradisional dan kueri berbasis tampilan materialized.

SELECT empid, deptname  
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2018-01-01';

CREATE MATERIALIZED VIEW mv 
AS SELECT empid, deptname, hire_date  
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2016-01-01';

SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

SELECT empid, deptname 
FROM emps JOIN depts 
ON emps.deptno=depts.deptno 
WHERE hire_date >= '2018-01-01';
-- Pernyataan di atas setara dengan pernyataan berikut: 
SELECT empid, deptname FROM mv 
WHERE hire_date >= '2018-01-01';

Aturan Penagihan

Saat menggunakan tampilan materialized, Anda akan dikenakan biaya untuk item berikut:

• Penyimpanan

  Tampilan materialized menggunakan penyimpanan fisik. Anda akan dikenakan biaya untuk penyimpanan fisik yang digunakan oleh tampilan materialized. Untuk informasi lebih lanjut tentang harga penyimpanan, lihat Harga Penyimpanan (bayar sesuai pemakaian).

• Komputasi

  Data dikueri saat Anda membuat, memperbarui, dan mengkueri tampilan materialized, serta menulis ulang kueri (jika tampilan materialized valid). Operasi ini mengonsumsi sumber daya komputasi dan menghasilkan biaya komputasi.

  • Jika proyek MaxCompute Anda menggunakan metode penagihan langganan, tidak ada biaya komputasi tambahan yang dihasilkan.

  • Jika proyek MaxCompute Anda menggunakan metode penagihan bayar sesuai pemakaian, biaya dihitung berdasarkan kompleksitas SQL dan jumlah data masukan. Untuk informasi lebih lanjut tentang penagihan, lihat bagian "Penagihan untuk pekerjaan SQL standar" dalam Harga Komputasi. Perhatikan poin-poin berikut:

    • Pernyataan SQL yang digunakan untuk memperbarui tampilan materialized sama dengan pernyataan SQL yang digunakan untuk membuat tampilan materialized. Jika proyek tempat tampilan materialized milik terikat pada grup sumber daya langganan, sumber daya langganan yang dibeli digunakan, dan tidak ada biaya tambahan yang dihasilkan. Jika proyek terikat pada grup sumber daya bayar sesuai pemakaian, biaya bervariasi berdasarkan jumlah data masukan dan kompleksitas pernyataan SQL. Setelah Anda memperbarui tampilan materialized, Anda akan dikenakan biaya penyimpanan berdasarkan ruang penyimpanan yang digunakan.

    • Jika tampilan materialized valid, data dibaca dari tampilan materialized saat operasi penulisan ulang kueri dilakukan. Jumlah data masukan yang dibaca dari tampilan materialized pernyataan kueri terkait dengan tampilan materialized dan tidak terkait dengan tabel sumber tampilan materialized. Jika tampilan materialized tidak valid, operasi penulisan ulang kueri tidak dapat dilakukan, dan data dikueri dari tabel sumber. Jumlah data masukan untuk pernyataan kueri terkait dengan tabel sumber. Untuk informasi lebih lanjut tentang cara mengkueri status tampilan materialized, lihat Kueri Status Tampilan Materialized dalam topik ini.

    • Pembengkakan data mungkin terjadi jika tampilan materialized dihasilkan berdasarkan asosiasi beberapa tabel. Oleh karena itu, jumlah data yang dibaca oleh tampilan materialized mungkin tidak selalu lebih kecil dari jumlah data dalam tabel sumber. MaxCompute tidak dapat menjamin bahwa membaca data dari tampilan materialized lebih murah daripada membaca data dari tabel sumber.

Batasan

Fungsi jendela, Fungsi tabel bernilai pengguna (UDTF), dan fungsi non-deterministik, seperti fungsi skalar yang ditentukan pengguna (UDF) dan fungsi agregat yang ditentukan pengguna (UDAF) tidak didukung.

Perhatian

• Jika pernyataan kueri berdasarkan mana Anda membuat tampilan materialized gagal dieksekusi, Anda tidak dapat membuat tampilan materialized.

• Kolom kunci partisi dalam tampilan materialized harus diturunkan dari tabel sumber. Urutan dan jumlah kolom dalam tampilan materialized harus sama dengan urutan dan jumlah kolom dalam tabel sumber. Nama kolom bisa berbeda.

• Anda harus menentukan komentar untuk semua kolom, termasuk kolom kunci partisi. Jika Anda hanya menentukan komentar untuk beberapa kolom, kesalahan akan dikembalikan.

• Anda dapat menentukan atribut partisi dan pengelompokan untuk tampilan materialized. Dalam hal ini, data di setiap partisi memiliki atribut pengelompokan yang ditentukan.

• Jika pernyataan kueri berdasarkan mana Anda membuat tampilan materialized berisi operator yang tidak didukung oleh tampilan materialized, kesalahan akan dikembalikan. Untuk informasi lebih lanjut tentang operator yang didukung oleh tampilan materialized, lihat Lakukan operasi penulisan ulang kueri berdasarkan tampilan materialized.

• Secara default, MaxCompute tidak mengizinkan Anda membuat tampilan materialized menggunakan fungsi non-deterministik, seperti UDF atau UDAF. Jika Anda harus menggunakan fungsi non-deterministik berdasarkan persyaratan bisnis Anda, jalankan perintah set odps.sql.materialized.view.support.nondeterministic.function=true; pada tingkat sesi.

• Jika tabel sumber tampilan materialized berisi partisi kosong, Anda dapat menyegarkan tampilan materialized untuk menghasilkan partisi kosong dalam tampilan materialized.

Sintaksis

CREATE MATERIALIZED VIEW [IF NOT EXISTS][project_name.]<mv_name>
[LIFECYCLE <days>]    -- Siklus hidup tampilan materialized.
[BUILD DEFERRED]    -- Menentukan bahwa hanya skema yang dibuat dan data tidak diperbarui saat Anda membuat tampilan materialized.
[(<col_name> [COMMENT <col_comment>],...)]    -- Komentar kolom.
[DISABLE REWRITE] -- Menentukan apakah akan menonaktifkan operasi penulisan ulang kueri yang dilakukan berdasarkan tampilan materialized.
[COMMENT 'komentar tabel']    -- Komentar tampilan materialized.
[PARTITIONED BY (<col_name> [, <col_name>, ...])    -- Partisi dalam tampilan materialized. Parameter ini diperlukan saat Anda membuat tampilan materialized yang dipartisi.
[CLUSTERED BY|RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
     [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
                 INTO <number_of_buckets> BUCKETS]    -- Atribut shuffle dan sort tampilan materialized. Parameter ini diperlukan saat Anda membuat tampilan materialized yang dikelompokkan.
[REFRESH EVERY <num> MINUTES/HOURS/DAYS]
[TBLPROPERTIES("compressionstrategy"="normal/high/extreme",    -- Kebijakan kompresi untuk penyimpanan data tampilan materialized.
                "enable_auto_substitute"="true",    -- Menentukan apakah akan mengkueri data dari tabel partisi sumber jika data partisi yang ingin Anda kueri tidak terkandung dalam tampilan materialized.
                "enable_auto_refresh"="true",    -- Menentukan apakah akan mengaktifkan fitur pembaruan terjadwal.
                "refresh_interval_minutes"="120",    -- Interval pembaruan.
                "only_refresh_max_pt"="true"    -- Menentukan apakah akan memperbarui data partisi terbaru di tabel sumber ke tampilan materialized secara otomatis.
                )]
AS <select_statement>;

Parameter

Contoh

Membuat tampilan materialized

1. Buat tabel bernama mf_t dan mf_t1 dan sisipkan data ke dalam tabel.

   CREATE TABLE IF NOT EXISTS mf_t( 
        id     bigint, 
        value   bigint, 
        name   string) 
   PARTITIONED BY (ds STRING); 
   
   ALTER TABLE mf_t ADD PARTITION (ds='1'); 
   INSERT INTO mf_t PARTITION (ds='1') VALUES (1,10,'kyle'),(2,20,'xia'); 
   SELECT * FROM mf_t WHERE ds ='1'; 
   -- Hasil berikut dikembalikan: 
   +------------+------------+------------+------------+
   | id         | value      | name       | ds         |
   +------------+------------+------------+------------+
   | 1          | 10         | kyle       | 1          |
   | 2          | 20         | xia        | 1          |
   +------------+------------+------------+------------+
   
   CREATE TABLE IF NOT EXISTS mf_t1( 
        id     bigint, 
        value   bigint, 
        name   string) 
   PARTITIONED BY (ds STRING); 
   
   ALTER TABLE mf_t1 ADD PARTITION (ds='1'); 
   INSERT INTO mf_t1 PARTITION (ds='1') VALUES (1,10,'kyle'),(3,20,'john'); 
   SELECT * FROM mf_t1 WHERE ds ='1';
   -- Hasil berikut dikembalikan: 
   +------------+------------+------------+------------+
   | id         | value      | name       | ds         |
   +------------+------------+------------+------------+
   | 1          | 10         | kyle       | 1          |
   | 3          | 20         | john       | 1          |
   +------------+------------+------------+------------+

2. Buat tampilan materialized.

   • Contoh 1: Buat tampilan materialized yang berisi kolom kunci partisi bernama ds.

     CREATE MATERIALIZED VIEW mf_mv LIFECYCLE 7
     (
       key comment 'unique id',
       value comment 'input value',
       ds comment 'partisi'
       )
     PARTITIONED BY (ds)
     AS SELECT t1.id AS key, t1.value AS value, t1.ds AS ds
          FROM mf_t AS t1 JOIN mf_t1 AS t2
            ON t1.id = t2.id AND t1.ds=t2.ds AND t1.ds='1';
     -- Kueri data dari tampilan materialized yang dibuat.
     SELECT * FROM mf_mv WHERE ds =1;
     +------------+------------+------------+
     | key        | value      | ds         |
     +------------+------------+------------+
     | 1          | 10         | 1          |
     +------------+------------+------------+

   • Contoh 2: Buat tampilan materialized non-partisi yang dikelompokkan.

     CREATE MATERIALIZED VIEW mf_mv2 LIFECYCLE 7 
     CLUSTERED BY (key) SORTED BY (value) INTO 1024 buckets 
     AS  SELECT t1.id AS key, t1.value AS value, t1.ds AS ds 
           FROM mf_t AS t1 JOIN mf_t1 AS t2 
             ON t1.id = t2.id AND t1.ds=t2.ds AND t1.ds='1';

   • Contoh 3: Buat tampilan materialized berpartisi yang dikelompokkan.

     CREATE MATERIALIZED VIEW mf_mv3 LIFECYCLE 7 
     PARTITIONED BY (ds) 
     CLUSTERED BY (key) SORTED BY (value) INTO 1024 buckets 
     AS  SELECT t1.id AS key, t1.value AS value, t1.ds AS ds 
           FROM mf_t AS t1 JOIN mf_t1 AS t2 
             ON t1.id = t2.id AND t1.ds=t2.ds AND t1.ds='1';

Melakukan penulisan ulang kueri berdasarkan tampilan materialized

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

Melakukan operasi penulisan ulang kueri berdasarkan tampilan materialized

Fitur terpenting dari tampilan materialized adalah melakukan operasi penulisan ulang kueri pada pernyataan kueri. Untuk melakukan operasi penulisan ulang kueri pada pernyataan kueri berdasarkan tampilan materialized, Anda harus menambahkan set odps.sql.materialized.view.enable.auto.rewriting=true; sebelum pernyataan kueri. Jika tampilan materialized tidak valid, tampilan materialized tidak dapat digunakan untuk operasi penulisan ulang kueri. Dalam hal ini, data dikueri dari tabel sumber, dan kecepatan kueri tidak dipercepat.

Operasi penulisan ulang kueri berdasarkan tampilan materialized memerlukan agar data dalam pernyataan kueri diperoleh dari tampilan materialized. Data tersebut mencakup kolom keluaran, kolom yang diperlukan oleh operasi filter, kolom yang diperlukan oleh fungsi agregat, dan kolom yang diperlukan oleh operasi JOIN. Jika kolom yang diperlukan dalam pernyataan kueri tidak termasuk dalam tampilan materialized atau tidak didukung oleh fungsi agregat, Anda tidak dapat melakukan operasi penulisan ulang kueri berdasarkan tampilan materialized.

Melakukan kueri penetrasi pada tampilan materialized

Tampilan materialized partisi tidak mencakup semua data partisi dalam tabel partisi sumber jika hanya data partisi terbaru yang diperbarui ke tampilan materialized partisi. Jika data partisi yang ingin Anda kueri tidak ada dalam tampilan materialized partisi, sistem melakukan kueri penetrasi untuk mengkueri data dari tabel partisi sumber. Gambar berikut menunjukkan cara melakukan kueri penetrasi.

Contoh berikut menunjukkan cara melakukan kueri penetrasi berdasarkan tampilan materialized.

1. Buat tampilan materialized partisi yang mendukung kueri penetrasi.

   -- Buat tabel bernama src. 
   CREATE TABLE src(id bigint,name string) PARTITIONED BY (dt string);
   -- Sisipkan data ke dalam tabel. 
   INSERT INTO src PARTITION(dt='20210101') VALUES(1,'Alex');
   INSERT INTO src PARTITION(dt='20210102') VALUES(2,'Flink');
   
   -- Buat tampilan materialized partisi yang mendukung kueri penetrasi. 
   CREATE MATERIALIZED VIEW IF NOT EXISTS mv LIFECYCLE 7 
   PARTITIONED BY (dt) 
   tblproperties("enable_auto_substitute"="true") 
   AS SELECT id, name, dt FROM src;

2. Kueri data partisi 20210101 dalam tabel src dari tampilan materialized mv.

   SELECT * FROM mv WHERE dt='20210101';

3. Kueri data partisi 20210102 dalam tabel src dari tampilan materialized mv. Tampilan materialized mv tidak mengandung data partisi tersebut. Oleh karena itu, kueri penetrasi dilakukan untuk mengkueri data partisi dari tabel src.

   SELECT * FROM mv WHERE dt = '20210102';
   -- Pernyataan di atas setara dengan pernyataan berikut karena tampilan materialized mv tidak mengandung data partisi 20210102 dan data partisi perlu dikueri dari tabel src.
   SELECT * FROM (SELECT id, name, dt FROM src WHERE dt='20210102') t;

4. Kueri data partisi 20201230 hingga 20210102 dari tampilan materialized mv. Tampilan materialized mv tidak mengandung semua data partisi. Oleh karena itu, kueri penetrasi dilakukan untuk mendapatkan data yang tidak terkandung dalam tampilan materialized mv, dan operasi UNION dilakukan pada data yang diperoleh dan data yang dikueri dari tampilan materialized mv untuk mengembalikan hasil akhir.

   SELECT * FROM mv WHERE dt >= '20201230' AND dt<='20210102' AND id='5'; 
   -- Tampilan materialized mv tidak mengandung data partisi 20210102 dan kueri penetrasi dilakukan untuk mengkueri data partisi 20210102 dari tabel sumber. Pernyataan di atas setara dengan pernyataan berikut:
   SELECT * FROM
   (SELECT id, name, dt FROM src WHERE dt='20211231' OR dt='20210102'
    UNION ALL  
    SELECT * FROM mv WHERE dt='20210101'
   ) t WHERE id = '5';

Pernyataan Terkait

• ALTER MATERIALIZED VIEW: Memperbarui tampilan materialized, mengubah siklus hidup tampilan materialized, mengaktifkan atau menonaktifkan fitur siklus hidup untuk tampilan materialized, atau menghapus partisi dari tampilan materialized.

• DESC TABLE/VIEW: Melihat informasi tentang tampilan materialized dalam proyek MaxCompute.

• SELECT MATERIALIZED VIEW: Mengkueri status tampilan materialized.

• DROP MATERIALIZED VIEW: Menghapus tampilan materialized yang ada.