MaxCompute:PETUNJUK SUBQUERY_MAPJOIN

Batasan

• Petunjuk ini hanya berlaku untuk subkueri skalar, IN, NOT IN, EXISTS, dan NOT EXISTS. Petunjuk ini tidak mendukung subkueri dasar. Untuk informasi lebih lanjut tentang subkueri, lihat subkueri.

• Saat Anda menggunakan PETUNJUK SUBQUERY_MAPJOIN, sistem secara otomatis memperlakukan set hasil subkueri sebagai tabel kecil untuk operasi MAPJOIN, sehingga Anda tidak perlu menentukannya secara manual.

  Penting

  Jika set hasil subkueri terlalu besar, dapat terjadi error out of memory (OOM). Gunakan petunjuk ini hanya jika Anda yakin bahwa hasil subkueri memenuhi syarat sebagai tabel kecil. Untuk informasi lebih lanjut tentang batasan MAPJOIN, lihat Batasan.

• Dalam beberapa skenario, MaxCompute tidak mengonversi subkueri menjadi operasi JOIN. Jika Anda menggunakan PETUNJUK SUBQUERY_MAPJOIN dalam kasus seperti ini, sistem akan mengeluarkan pesan peringatan. Untuk contohnya, lihat Contoh 5.

Penggunaan

Dalam subkueri, gunakan petunjuk /*+ subquery_mapjoin */ untuk menentukan algoritma MAPJOIN. Petunjuk harus ditempatkan tepat setelah tanda kurung buka subkueri.

Asumsikan tabel t1 dan t2 didefinisikan sebagai berikut:

CREATE TABLE t1(a BIGINT, b BIGINT);
CREATE TABLE t2(a BIGINT, b BIGINT);

• Subkueri skalar

  SELECT a,
         (/*+ subquery_mapjoin */ SELECT b FROM t2 WHERE a = t1.a)
  FROM t1;

• Subkueri IN dan NOT IN

  SELECT * FROM t1 WHERE a IN (/*+ subquery_mapjoin */ SELECT a FROM t2 WHERE b = t1.b);
  SELECT * FROM t1 WHERE a NOT IN (/*+ subquery_mapjoin */ SELECT a FROM t2 WHERE b = t1.b);

• Subkueri EXISTS dan NOT EXISTS

  SELECT * FROM t1 WHERE EXISTS (/*+ subquery_mapjoin */ SELECT * FROM t2 WHERE b = t1.b);
  SELECT * FROM t1 WHERE NOT EXISTS (/*+ subquery_mapjoin */ SELECT * FROM t2 WHERE b = t1.b);

• Sintaksis salah

  -- Ini salah. PETUNJUK SUBQUERY_MAPJOIN tidak ditempatkan tepat setelah tanda kurung buka subkueri.
  SELECT * FROM t1 WHERE a IN (SELECT /*+ subquery_mapjoin */ a FROM t2 WHERE b = t1.b);

Data sampel

Contoh dalam topik ini menggunakan data sampel berikut.

1. Buat tabel sale_detail dan shop_detail, lalu isi dengan data.

   -- Buat tabel partisi bernama sale_detail.
   CREATE TABLE if NOT EXISTS sale_detail
   (
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE
   )
   PARTITIONED BY (sale_date STRING, region STRING);
   
   -- Tambahkan partisi ke tabel sale_detail.
   ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');
   
   -- Masukkan data ke tabel sale_detail.
   INSERT INTO sale_detail PARTITION (sale_date='2013', region='china') VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
   INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai') VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
   
   -- Buat shop_detail dari partisi 2013 tabel sale_detail.
   SET odps.sql.allow.fullscan=true;
   CREATE TABLE shop_detail AS SELECT shop_name,customer_id,total_price FROM sale_detail WHERE sale_date='2013'AND region='china'; 

2. Kueri data dalam tabel sale_detail.

   SET odps.sql.allow.fullscan=true;
   SELECT * FROM sale_detail; 

   Hasil berikut dikembalikan:

   +------------+-------------+-------------+------------+------------+
   | shop_name  | customer_id | total_price | sale_date  | region     |
   +------------+-------------+-------------+------------+------------+
   | s1         | c1          | 100.1       | 2013       | china      |
   | s2         | c2          | 100.2       | 2013       | china      |
   | s3         | c3          | 100.3       | 2013       | china      |
   | null       | c5          | NULL        | 2014       | shanghai   |
   | s6         | c6          | 100.4       | 2014       | shanghai   |
   | s7         | c7          | 100.5       | 2014       | shanghai   |
   +------------+-------------+-------------+------------+------------+

3. Kueri data dalam tabel shop_detail.

   SELECT * FROM shop_detail; 

   Hasil berikut dikembalikan:

   +------------+-------------+-------------+
   | shop_name  | customer_id | total_price |
   +------------+-------------+-------------+
   | s1         | c1          | 100.1       |
   | s2         | c2          | 100.2       |
   | s3         | c3          | 100.3       |
   +------------+-------------+-------------+

Contoh

Contoh berikut menggunakan data sampel untuk menunjukkan cara menggunakan PETUNJUK SUBQUERY_MAPJOIN.

SET odps.sql.allow.fullscan=true;
SELECT * FROM shop_detail WHERE (/*+ subquery_mapjoin */ SELECT COUNT(*) FROM sale_detail WHERE sale_detail.shop_name = shop_detail.shop_name) >= 1;

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail WHERE total_price IN (/*+ subquery_mapjoin */ SELECT total_price FROM shop_detail WHERE customer_id = sale_detail.customer_id);

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail WHERE EXISTS (/*+ subquery_mapjoin */ SELECT * FROM shop_detail WHERE customer_id = sale_detail.customer_id);

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

SET odps.sql.allow.fullscan=true;
SELECT * FROM (/*+ subquery_mapjoin */ SELECT shop_name FROM sale_detail) a;

-- pesan error
FAILED: ODPS-0130161:[1,16] Parse exception - invalid subquery_mapjoin hint, should only be used for scalar/in/exists subquery

-- Tambahkan kolom sale_date ke shop_detail dan masukkan data.
ALTER TABLE shop_detail ADD columns if not exists(sale_date STRING);
INSERT OVERWRITE TABLE shop_detail VALUES ('s1','c1',100.1,'2013'),('s2','c2',100.2,'2013'),('s3','c3',100.3,'2013');

-- Aktifkan peringatan. Anda dapat melewati langkah ini jika Proyek Anda sudah dikonfigurasi untuk menampilkan peringatan.
SET odps.compiler.warning.disable=false;

/** Pada kueri ini, subkueri menargetkan tabel partisi. Untuk mengaktifkan pemangkasan partisi, 
sistem tidak mengonversi subkueri menjadi JOIN. 
Akibatnya, sistem mengabaikan PETUNJUK SUBQUERY_MAPJOIN dan mengeluarkan peringatan. **/
SELECT * FROM sale_detail WHERE sale_date IN (/*+ subquery_mapjoin */ SELECT sale_date FROM shop_detail);

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

WARNING:[1,47]  subquery_mapjoin hint does not work because the subquery is not converted to join