MaxCompute:CREATE MATERIALIZED VIEW

Materyalleştirilmiş görünümler aşağıdaki senaryolar için uygundur:

• Sıkça yürütülen ve sabit bir desene sahip olan sorgular.

• Toplama ve birleştirme gibi zaman alan işlemler içeren sorgular.

• Bir tablodaki verilerin yalnızca küçük bir alt kümesine erişen sorgular.

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [project_name.]<mv_name>
[LIFECYCLE <days>]    --Specifies the lifecycle.
[BUILD DEFERRED]    --Creates the schema without populating data.
[(<col_name> [COMMENT <col_comment>], ...)]    --Column comments.
[DISABLE REWRITE]    --Specifies whether the materialized view can be used for query rewrite.
[COMMENT 'table comment']    --Table comment.
[PARTITIONED BY (<col_name> [, <col_name>, ...])]    --Creates the materialized view as a partitioned table.
[CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...])
  [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])]
    INTO <number_of_buckets> BUCKETS]    --Sets the shuffle and sort properties for a clustered table.
[REFRESH EVERY <num> {MINUTES | HOURS | DAYS}] 
[TBLPROPERTIES("compressionstrategy"="{normal|high|extreme}",    --Specifies the data storage compression strategy for the table.
                "enable_auto_substitute"="true",    --Specifies whether to enable query passthrough to the source table when a partition does not exist.
                "enable_auto_refresh"="true",    --Specifies whether to enable automatic refresh.
                "refresh_interval_minutes"="120",    --Specifies the refresh interval.
                "only_refresh_max_pt"="true"    --For partitioned materialized views, automatically refreshes only the latest partition from the source table.
                )]
AS <select_statement>;

Bir materyalleştirilmiş görünüm oluşturma

1. mf_t ve mf_t1 adlı tablolar oluşturun ve tablolara veri ekleyin.

   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'; 
   -- The following result is returned.
   +------------+------------+------------+------------+
   | 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';
   -- The following result is returned.
   +------------+------------+------------+------------+
   | id         | value      | name       | ds         |
   +------------+------------+------------+------------+
   | 1          | 10         | kyle       | 1          |
   | 3          | 20         | john       | 1          |
   +------------+------------+------------+------------+

2. Bir materyalleştirilmiş görünüm oluşturun.

   • Örnek 1: ds adlı bir bölüm anahtar sütunu içeren bir materyalleştirilmiş görünüm oluşturun.

     CREATE MATERIALIZED VIEW mf_mv LIFECYCLE 7 
     (
       key comment 'unique id',
       value comment 'input value',
       ds comment 'partition'
       )
     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';
     --Query the materialized view.
     SELECT * FROM mf_mv WHERE ds =1;
     +------------+------------+------------+
     | key        | value      | ds         |
     +------------+------------+------------+
     | 1          | 10         | 1          |
     +------------+------------+------------+

   • Örnek 2: Küme lenmiş bölümlendirilmemiş bir materyalleştirilmiş görünüm oluşturun.

     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';

   • Örnek 3: Küme lenmiş bölümlendirilmiş bir materyalleştirilmiş görünüm oluşturun.

     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';

Materyalleştirilmiş görünüme dayalı sorgu yeniden yazma uygulama

1. Senaryo

   Her ziyaret için sayfa kimliğini, kullanıcı kimliğini ve ziyaret saatini kaydeden visit_records adlı bir sayfa ziyaret tablosu düşünün. Sık yapılan bir analiz görevi farklı sayfalar için ziyaret sayısını hesaplamaktır.

   Bu durumda sayfa kimliğine göre gruplandıran ve her sayfa için ziyaretleri sayan visit_records üzerinde bir materyalleştirilmiş görünüm oluşturabilirsiniz. Ardından sonraki sorguları bu materyalleştirilmiş görünüm üzerinde çalıştırabilirsiniz.

   visit_records yapısı aşağıdaki gibidir:

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

2. Bir materyalleştirilmiş görünüm oluşturun.

   -- Create a materialized view for the visit_records table that groups by page ID and counts the visits for each page.
   
   CREATE MATERIALIZED VIEW count_mv AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

3. Aşağıdaki sorguyu çalıştırın:

   SET odps.sql.materialized.view.enable.auto.rewriting=true; 
   SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   Bu sorgu deyimi yürütüldüğünde MaxCompute count_mv materyalleştirilmiş görünümünü otomatik olarak eşleştirir ve önceden toplanmış verileri count_mv üzerinden okur.

4. Sorgunun materyalleştirilmiş görünüm kullanılarak yeniden yazıldığını doğrulamak için aşağıdaki EXPLAIN komutunu çalıştırın:

   EXPLAIN SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   Aşağıdaki sonuç döndürülür:

   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

   Döndürülen sonuçtaki Data source değeri sorgu tarafından okunan tablonun doc_test_dev projesinin count_mv tablosu olduğunu gösterir. Bu durum materyalleştirilmiş görünümün etkili olduğunu ve sorgu yeniden yazmanın başarılı olduğunu gösterir.

Örnek 1: Filtre koşullarıyla yeniden yazma

1. Bir materyalleştirilmiş görünüm oluşturun.

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

2. Aşağıdaki tabloda materyalleştirilmiş görünüm için yeniden yazma örnekleri sunulmaktadır.

   Orijinal sorgu	Yeniden yazılmış sorgu
   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;	Materyalleştirilmiş görünüm d sütununu içermediğinden yeniden yazma başarısız olur.
   SELECT d, e FROM src WHERE a=10;	Materyalleştirilmiş görünüm d ve e sütunlarını içermediğinden yeniden yazma başarısız olur.
   SELECT a, b FROM src WHERE a=1;	Materyalleştirilmiş görünüm a=1 koşulundaki verileri içermediğinden yeniden yazma başarısız olur.

Örnek 2: Toplama işlevleriyle yeniden yazma

Örnek 3: JOIN yan tümcesiyle yeniden yazma

Bu üç JOIN yeniden yazma senaryosu birleştirilebilir.

Materyalleştirilmiş görünüm sorgu yeniden yazmanın amacı sorguları hızlandırmak olduğundan MaxCompute en iyi performansı sunan yeniden yazma kurallarına öncelik verir. Kötü hızlandırmaya neden olacak işlemler getiren bir kural uygulanmaz.

Örnek 4: LEFT JOIN yan tümcesiyle yeniden yazma

1. Bir materyalleştirilmiş görünüm oluşturun.

   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;

2. Aşağıdaki tabloda bir sorgunun materyalleştirilmiş görünüme dayalı olarak nasıl yeniden yazıldığı gösterilmektedir.

   Orijinal sorgu	Yeniden yazılmış sorgu
   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;

Örnek 5: UNION ALL yan tümcesiyle yeniden yazma

1. Bir materyalleştirilmiş görünüm oluşturun.

   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;

2. Aşağıdaki tabloda bir sorgunun materyalleştirilmiş görünüme dayalı olarak nasıl yeniden yazıldığı gösterilmektedir.

   Orijinal sorgu	Yeniden yazılmış sorgu
   SELECT user_id, tran_amount FROM alipay_tran UNION ALL SELECT user_id, tran_amount FROM unionpay_tran;	SELECT user_id, tran_amount FROM mv;

Materyalleştirilmiş görünüm için geçiş sorgusu özelliğini etkinleştirmek üzere aşağıdaki parametreyi ayarlayın:

Materyalleştirilmiş görünümü oluştururken tblproperties öğesine "enable_auto_substitute"="true" yapılandırmasını ekleyin.