MaxCompute:Common table expression (CTE)

Sintaksis

WITH
  <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query>
  )
  [, <cte_name> [(col_name [, col_name] ...)] AS (
    <cte_query2>
  )
  , ...]

Parameter

Contoh

Kueri berikut menggunakan UNION ALL untuk menggabungkan dua operasi JOIN. Kedua JOIN tersebut menggunakan subkueri sebelah kiri yang sama, sehingga harus diduplikasi tanpa CTE:

INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    JOIN (
        SELECT * FROM src2 WHERE value > 0) b
    ON a.key = b.key
) c
UNION ALL
SELECT * FROM (
    SELECT a.key, b.value
    FROM (
        SELECT * FROM src WHERE key IS NOT NULL) a
    LEFT OUTER JOIN (
        SELECT * FROM src3 WHERE value > 0) b
    ON a.key = b.key AND b.key IS NOT NULL
) d;

Dengan menulis ulang menggunakan CTE, duplikasi dihilangkan. Subkueri a didefinisikan sekali dan digunakan kembali oleh kedua JOIN:

WITH
  a AS (SELECT * FROM src WHERE key IS NOT NULL),
  b AS (SELECT * FROM src2 WHERE value > 0),
  c AS (SELECT * FROM src3 WHERE value > 0),
  d AS (SELECT a.key, b.value FROM a JOIN b ON a.key = b.key),
  e AS (SELECT a.key, c.value FROM a LEFT OUTER JOIN c ON a.key = c.key AND c.key IS NOT NULL)
INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
SELECT * FROM d UNION ALL SELECT * FROM e;

Sintaksis

WITH RECURSIVE <cte_name> [(col_name [, col_name] ...)] AS (
  <initial_part> UNION ALL <recursive_part>
)
SELECT ... FROM ...;

Parameter

Batasan

• CTE rekursif tidak dapat muncul dalam subkueri IN, EXISTS, atau subkueri skalar.

• Jumlah maksimum iterasi default adalah 10. Tingkatkan batas ini dengan mengatur odps.sql.rcte.max.iterate.num (nilai maksimum: 100).

• Hasil antara tidak disimpan antar iterasi. Jika task gagal, eksekusi dimulai ulang dari awal. Untuk komputasi rekursif berdurasi panjang, batasi jumlah iterasi atau simpan hasil antara dalam tabel temporary.

• CTE rekursif tidak didukung dalam mode Query Acceleration (MaxQA/MCQA).

  • Dalam mode MCQA, jika interactive_auto_rerun=true diatur, task akan fallback ke mode normal. Jika tidak, task gagal.

  • Dalam mode MaxQA, auto-fallback tidak didukung. Pekerjaan gagal langsung dan harus dikirimkan secara manual ke kelompok kuota Pemrosesan batch untuk dicoba ulang.

Contoh

• Contoh 1: Mendefinisikan CTE rekursif bernama cte_name.

  -- Metode 1: Menentukan eksplisit nama kolom output
  WITH RECURSIVE cte_name(a, b) AS (
    SELECT 1L, 1L                                   -- initial_part: iterasi 0
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5    -- recursive_part: mereferensi iterasi sebelumnya
  )
  SELECT * FROM cte_name ORDER BY a LIMIT 100;
  
  -- Metode 2: Menginferensi nama kolom dari initial_part
  WITH RECURSIVE cte_name AS (
    SELECT 1L AS a, 1L AS b
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5
  )
  SELECT * FROM cte_name ORDER BY a LIMIT 100;

  Catatan

  • Dalam recursive_part, tetapkan kondisi terminasi untuk menghindari loop tak terbatas. Dalam contoh ini, WHERE a + 1 <= 5 berfungsi sebagai kriteria terminasi. Jika kondisi WHERE tidak terpenuhi, dataset yang dihasilkan pada iterasi saat ini kosong, dan iterasi berhenti.

  • Jika nama kolom output tidak ditentukan secara eksplisit, sistem mendukung inferensi otomatis. Misalnya, pada Metode 2, nama kolom output dari initial_part digunakan sebagai nama kolom output CTE rekursif.

  Hasil:

  +------------+------------+
  | a          | b          |
  +------------+------------+
  | 1          | 1          |
  | 2          | 2          |
  | 3          | 3          |
  | 4          | 4          |
  | 5          | 5          |
  +------------+------------+

• Contoh 2: CTE rekursif dalam subkueri (error kompilasi)

  CTE rekursif tidak diperbolehkan di dalam subkueri IN, EXISTS, atau subkueri skalar. Kueri berikut gagal dikompilasi:

  WITH RECURSIVE cte_name(a, b) AS (
    SELECT 1L, 1L
    UNION ALL
    SELECT a+1, b+1 FROM cte_name WHERE a+1 <= 5)
  SELECT x, x IN (SELECT a FROM cte_name) FROM VALUES (1L), (2L) AS t(x);

  Error:

  FAILED: ODPS-0130071:[5,31] Semantic analysis exception - using Recursive-CTE cte_name in scalar/in/exists sub-query is not allowed, please check your query, the query text location is from [line 5, column 13] to [line 5, column 40]

• Contoh 3: Menelusuri hierarki organisasi

  Buat tabel employees dan masukkan data:

  CREATE TABLE employees(name STRING, boss_name STRING);
  INSERT INTO TABLE employees VALUES
    ('zhang_3', null),
    ('li_4',    'zhang_3'),
    ('wang_5',  'zhang_3'),
    ('zhao_6',  'li_4'),
    ('qian_7',  'wang_5');

  Definisikan CTE rekursif bernama company_hierarchy dengan tiga kolom output: nama karyawan, nama manajernya, dan levelnya dalam hierarki:

  WITH RECURSIVE company_hierarchy(name, boss_name, level) AS (
      SELECT name, boss_name, 0L FROM employees WHERE boss_name IS NULL
      UNION ALL
      SELECT e.name, e.boss_name, h.level + 1
        FROM employees e
        JOIN company_hierarchy h ON e.boss_name = h.name
  )
  SELECT * FROM company_hierarchy ORDER BY level, boss_name, name LIMIT 1000;

  Eksekusi berlangsung sebagai berikut:

  • Iterasi 0 (initial_part): Memilih karyawan yang boss_name IS NULL, memberikan level = 0. Hasil: ('zhang_3', NULL, 0).

  • Iterasi 1 (recursive_part): Melakukan JOIN antara employees dengan tabel kerja (iterasi 0). Kondisi e.boss_name = h.name menemukan karyawan yang dikelola oleh zhang_3. Hasil: li_4 dan wang_5 pada level = 1.

  • Iterasi 2: Menemukan karyawan yang dikelola oleh li_4 atau wang_5. Hasil: zhao_6 dan qian_7 pada level = 2.

  • Iterasi 3: Tidak ada karyawan yang memiliki zhao_6 atau qian_7 sebagai manajer. Tabel kerja kosong, dan iterasi berhenti.

  Hasil:

  +---------+-----------+------------+
  | name    | boss_name | level      |
  +---------+-----------+------------+
  | zhang_3 | NULL      | 0          |
  | li_4    | zhang_3   | 1          |
  | wang_5  | zhang_3   | 1          |
  | zhao_6  | li_4      | 2          |
  | qian_7  | wang_5    | 2          |
  +---------+-----------+------------+

• Contoh 4: Data siklik menyebabkan loop tak terbatas

  Masukkan catatan ke dalam tabel employees dari Contoh 3 di mana seorang karyawan menjadi manajer dirinya sendiri.

  INSERT INTO TABLE employees VALUES('qian_7', 'qian_7');

  Catatan ini menyatakan bahwa manajer dari qian_7 adalah qian_7 sendiri. Menjalankan CTE rekursif yang didefinisikan sebelumnya menghasilkan loop tak terbatas. Sistem membatasi jumlah maksimum iterasi. Kueri akhirnya gagal.

  Error:

  FAILED: ODPS-0010000:System internal error - recursive-cte: company_hierarchy exceed max iterate number 10

Contoh penggunaan

Tambahkan hint /*+ MATERIALIZE */ ke pernyataan SELECT tingkat atas dari CTE non-rekursif untuk menyimpan cache hasilnya ke dalam tabel temporary. Referensi berikutnya membaca dari cache alih-alih menjalankan kueri ulang:

WITH v1 AS (SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
SELECT a FROM v1 UNION ALL SELECT a FROM v1;

-- Hasil:
+------------+
| a          |
+------------+
| 0.8414709848078965 |
| 0.8414709848078965 |
+------------+

Saat hint berlaku, tab Job Details di LogView menampilkan beberapa Fuxi Jobs, yang mengonfirmasi bahwa hasil antara telah disimpan.

Batasan

• HINT MATERIALIZE harus diterapkan pada pernyataan SELECT tingkat atas dari CTE non-rekursif. CTE rekursif tidak memerlukan hint MATERIALIZE.

  • Contoh salah: Pada CTE berikut, pernyataan tingkat atas adalah UNION, bukan SELECT, sehingga hint MATERIALIZE tidak berlaku.

    WITH v1 AS (
      SELECT /*+ MATERIALIZE */ SIN(1.0) AS a
      UNION ALL
      SELECT /*+ MATERIALIZE */ SIN(1.0) AS a)
    SELECT a FROM v1 UNION ALL SELECT a FROM v1;

  • Contoh benar: Tulis ulang contoh salah di atas dengan membungkusnya dalam subkueri.

    WITH 
      v1 AS (SELECT /*+ MATERIALIZE */ * FROM 
               (SELECT SIN(1.0) AS a
                UNION ALL 
                SELECT SIN(1.0) AS a)
            ) 
    SELECT a FROM v1 UNION ALL SELECT a FROM v1;

• Jika CTE menggunakan fungsi non-deterministik seperti RAND atau UDF Java/Python non-deterministik, materialisasi CTE menyimpan cache satu evaluasi fungsi tersebut. Referensi berikutnya mengembalikan nilai cache, yang mengubah perilaku semantik kueri yang mengharapkan nilai acak independen per pemanggilan.

• CTE termaterialisasi tidak didukung di MCQA(Query Acceleration 1.0) -deprecated. Jika task dijalankan dalam mode MCQA dengan interactive_auto_rerun=true, task akan fallback ke mode normal. Jika tidak, task gagal.