Halaman ini diterjemahkan oleh Cloud Translation API.

Menggunakan CTE rekursif

Di GoogleSQL untuk BigQuery, klausa WITH berisi satu atau beberapa ekspresi tabel umum (CTE) dengan tabel sementara yang dapat Anda referensikan dalam ekspresi kueri. CTE dapat bersifat non-rekursif, rekursif, atau keduanya. Kata kunci RECURSIVE memungkinkan rekursi dalam klausa WITH (WITH RECURSIVE).

CTE rekursif dapat mereferensikan dirinya sendiri, CTE sebelumnya, atau CTE berikutnya. CTE non-rekursif hanya dapat mereferensikan CTE sebelumnya dan tidak dapat mereferensikan dirinya sendiri. CTE rekursif berjalan terus-menerus hingga tidak ada hasil baru yang ditemukan, sementara CTE non-rekursif berjalan sekali. Oleh karena itu, CTE rekursif biasanya digunakan untuk membuat kueri data hierarkis dan data grafik.

Misalnya, bayangkan sebuah grafik dengan setiap baris mewakili satu node yang dapat terhubung ke node lainnya. Untuk menemukan penutupan transitif semua node yang dapat dijangkau dari node awal tertentu tanpa mengetahui jumlah hop maksimum, Anda memerlukan CTE rekursif dalam kueri (WITH RECURSIVE). Kueri rekursif akan dimulai dengan kasus dasar node awal, dan setiap langkah akan menghitung node baru yang tidak terlihat yang dapat dijangkau dari semua node yang terlihat sejauh ini hingga langkah sebelumnya. Kueri berakhir saat tidak ada node baru yang dapat ditemukan.

Namun, CTE rekursif mungkin mahal secara komputasi, jadi sebelum Anda menggunakannya, tinjau panduan ini dan bagian klausa WITH dari dokumentasi referensi GoogleSQL.

Membuat CTE rekursif

Untuk membuat CTE rekursif di GoogleSQL, gunakan klausa WITH RECURSIVE seperti ditunjukkan dalam contoh berikut:

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC

Contoh sebelumnya memberikan hasil berikut:

/*-----------*
 | iteration |
 +-----------+
 | 1         |
 | 1         |
 | 2         |
 | 2         |
 | 3         |
 | 3         |
 *-----------*/

CTE rekursif mencakup istilah dasar, operator union, dan istilah rekursif. Istilah dasar menjalankan iterasi pertama dari operasi union rekursif. Istilah rekursif menjalankan iterasi yang tersisa dan harus menyertakan satu referensi mandiri ke CTE rekursif. Hanya istilah rekursif yang dapat menyertakan referensi mandiri.

Dalam contoh sebelumnya, CTE rekursif berisi komponen berikut:

Nama CTE rekursif: CTE_1
Istilah dasar: SELECT 1 AS iteration
Operator union: UNION ALL
Istilah rekursif: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3

Untuk mempelajari sintaksis, aturan, dan contoh CTE rekursif lebih lanjut, lihat klausa WITH di dokumentasi referensi GoogleSQL.

Mempelajari keterjangkauan dalam directed acyclic graph (DAG)

Anda dapat menggunakan kueri rekursif untuk menjelajahi keterjangkauan dalam directed acyclic graph (DAG). Kueri berikut menemukan semua node yang dapat dijangkau dari node 5 dalam grafik yang disebut GraphData:

WITH RECURSIVE
  GraphData AS (
    --    1          5
    --   / \        / \
    --  2 - 3      6   7
    --      |       \ /
    --      4        8
    SELECT 1 AS from_node, 2 AS to_node UNION ALL
    SELECT 1, 3 UNION ALL
    SELECT 2, 3 UNION ALL
    SELECT 3, 4 UNION ALL
    SELECT 5, 6 UNION ALL
    SELECT 5, 7 UNION ALL
    SELECT 6, 8 UNION ALL
    SELECT 7, 8
  ),
  R AS (
    (SELECT 5 AS node)
    UNION ALL
    (
      SELECT GraphData.to_node AS node
      FROM R
      INNER JOIN GraphData
        ON (R.node = GraphData.from_node)
    )
  )
SELECT DISTINCT node FROM R ORDER BY node;

Contoh sebelumnya memberikan hasil berikut:

/*------*
 | node |
 +------+
 | 5    |
 | 6    |
 | 7    |
 | 8    |
 *------*/

Memecahkan masalah error batas iterasi

CTE rekursif dapat menghasilkan rekursi tanpa batas, yang terjadi ketika istilah rekursif dijalankan terus-menerus tanpa memenuhi kondisi penghentian. Untuk menghentikan rekursi tanpa batas, batas iterasi untuk setiap CTE rekursif diterapkan. Untuk BigQuery, batas iterasinya adalah 500 iterasi. Setelah CTE rekursif mencapai jumlah iterasi maksimum, eksekusi CTE akan dibatalkan dengan error.

Batasan ini ada karena penghitungan CTE rekursif bisa jadi mahal, dan menjalankan CTE dengan jumlah iterasi besar akan menghabiskan banyak resource sistem serta memerlukan waktu yang lebih lama untuk diselesaikan.

Kueri yang mencapai batas iterasi biasanya tidak memiliki kondisi penghentian yang tepat, sehingga menciptakan loop tanpa henti, atau menggunakan CTE rekursif dalam skenario yang tidak sesuai.

Jika Anda mengalami error batas iterasi rekursi, tinjau saran di bagian ini.

Memeriksa rekursi tanpa batas

Untuk mencegah rekursi tak terbatas, pastikan istilah rekursif tersebut dapat menghasilkan hasil kosong setelah mengeksekusi sejumlah iterasi tertentu.

Salah satu cara untuk memeriksa rekursi tak terbatas adalah dengan mengonversi CTE rekursif menjadi TEMP TABLE dengan loop REPEAT untuk iterasi 100 pertama, seperti berikut:

DECLARE current_iteration INT64 DEFAULT 0;

CREATE TEMP TABLE recursive_cte_name AS
SELECT base_expression, current_iteration AS iteration;

REPEAT
  SET current_iteration = current_iteration + 1;
  INSERT INTO recursive_cte_name
    SELECT recursive_expression, current_iteration
    FROM recursive_cte_name
    WHERE termination_condition_expression
      AND iteration = current_iteration - 1
      AND current_iteration < 100;
  UNTIL NOT EXISTS(SELECT * FROM recursive_cte_name WHERE iteration = current_iteration)
END REPEAT;

Ganti nilai berikut:

recursive_cte_name: CTE rekursif untuk di-debug.
base_expression: Istilah dasar CTE rekursif.
recursive_expression: Istilah rekursif dari CTE rekursif.
termination_condition_expression: Ekspresi penghentian CTE rekursif.

Misalnya, pertimbangkan CTE rekursif berikut yang disebut TestCTE:

WITH RECURSIVE
  TestCTE AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 3 FROM TestCTE WHERE MOD(n, 6) != 0
  )

Contoh ini menggunakan nilai berikut:

recursive_cte_name: TestCTE
base_expression: SELECT 1
recursive_expression: n + 3
termination_condition_expression: MOD(n, 6) != 0

Oleh karena itu, kode berikut akan menguji TestCTE untuk rekursi tak terbatas:

DECLARE current_iteration INT64 DEFAULT 0;

CREATE TEMP TABLE TestCTE AS
SELECT 1 AS n, current_iteration AS iteration;

REPEAT
SET current_iteration = current_iteration + 1;

INSERT INTO TestCTE
SELECT n + 3, current_iteration
FROM TestCTE
WHERE
  MOD(n, 6) != 0
  AND iteration = current_iteration - 1
  AND current_iteration < 10;

UNTIL
  NOT EXISTS(SELECT * FROM TestCTE WHERE iteration = current_iteration)
    END REPEAT;

-- Print the number of rows produced by each iteration

SELECT iteration, COUNT(1) AS num_rows
FROM TestCTE
GROUP BY iteration
ORDER BY iteration;

-- Examine the actual result produced for a specific iteration

SELECT * FROM TestCTE WHERE iteration = 2;

Contoh sebelumnya memberikan hasil berikut yang menyertakan ID iterasi dan jumlah baris yang dihasilkan selama iterasi tersebut:

/*-----------+----------*
 | iteration | num_rows |
 +-----------+----------+
 | 0         | 1        |
 | 1         | 1        |
 | 2         | 1        |
 | 3         | 1        |
 | 4         | 1        |
 | 5         | 1        |
 | 6         | 1        |
 | 7         | 1        |
 | 8         | 1        |
 | 9         | 1        |
 | 10        | 1        |
 *-----------+----------*/

Ini adalah hasil sebenarnya yang dihasilkan selama iterasi 2:

/*----------+-----------*
 | n        | iteration |
 +----------+-----------+
 | 7        | 2         |
 *----------+-----------*/

Jika jumlah baris selalu lebih besar dari nol, yang benar dalam contoh ini, contoh tersebut kemungkinan memiliki rekursi tak terbatas.

Memverifikasi penggunaan CTE rekursif yang sesuai

Pastikan Anda menggunakan CTE rekursif dalam skenario yang sesuai. CTE rekursif mungkin mahal untuk dihitung karena dirancang untuk mengkueri data hierarkis dan data grafik. Jika Anda tidak membuat kueri kedua jenis data ini, pertimbangkan alternatifnya, seperti menggunakan pernyataan LOOP dengan CTE non-rekursif.

Memisahkan CTE rekursif menjadi beberapa CTE rekursif

Jika menurut Anda CTE rekursif memerlukan lebih dari jumlah maksimum iterasi yang diizinkan, Anda mungkin dapat memecah CTE rekursif menjadi beberapa CTE rekursif.

Anda dapat memisahkan CTE rekursif dengan struktur kueri yang mirip dengan berikut ini:

WITH RECURSIVE
  CTE_1 AS (
    SELECT base_expression
    UNION ALL
    SELECT recursive_expression FROM CTE_1 WHERE iteration < 500
  ),
  CTE_2 AS (
    SELECT * FROM CTE_1 WHERE iteration = 500
    UNION ALL
    SELECT recursive_expression FROM CTE_2 WHERE iteration < 500 * 2
  ),
  CTE_3 AS (
    SELECT * FROM CTE_2 WHERE iteration = 500 * 2
    UNION ALL
    SELECT recursive_expression FROM CTE_3 WHERE iteration < 500 * 3
  ),
  [, ...]
SELECT * FROM CTE_1
UNION ALL SELECT * FROM CTE_2 WHERE iteration > 500
UNION ALL SELECT * FROM CTE_3 WHERE iteration > 500 * 2
[...]

Ganti nilai berikut:

base_expression: Ekspresi istilah dasar untuk CTE saat ini.
recursive_expression: Ekspresi istilah rekursif untuk CTE saat ini.

Misalnya, kode berikut membagi CTE menjadi tiga CTE yang berbeda:

WITH RECURSIVE
  CTE_1 AS (
    SELECT 1 AS iteration
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 10
  ),
  CTE_2 AS (
    SELECT * FROM CTE_1 WHERE iteration = 10
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_2 WHERE iteration < 10 * 2
  ),
  CTE_3 AS (
    SELECT * FROM CTE_2 WHERE iteration = 10 * 2
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_3 WHERE iteration < 10 * 3
  )
SELECT iteration FROM CTE_1
UNION ALL
SELECT iteration FROM CTE_2 WHERE iteration > 10
UNION ALL
SELECT iteration FROM CTE_3 WHERE iteration > 20
ORDER BY 1 ASC

Dalam contoh sebelumnya, 500 iterasi diganti dengan 10 iterasi sehingga lebih cepat untuk melihat hasil kueri. Kueri menghasilkan 30 baris, tetapi setiap CTE rekursif hanya melakukan iterasi 10 kali. Outputnya akan terlihat seperti berikut:

/*-----------*
 | iteration |
 +-----------+
 | 2         |
 | ...       |
 | 30        |
 *-----------*/

Anda dapat menguji kueri sebelumnya pada iterasi yang jauh lebih besar.

Menggunakan loop, bukan CTE rekursif

Untuk menghindari batas iterasi, pertimbangkan untuk menggunakan loop, bukan CTE rekursif. Anda dapat membuat loop dengan salah satu dari beberapa pernyataan loop, seperti LOOP, REPEAT, atau WHILE. Untuk informasi selengkapnya, lihat Loop.

Mengubah batas rekursif

Jika menurut Anda faktor-faktor berikut berlaku, hubungi Layanan Pelanggan untuk meningkatkan batas rekursif:

Anda memiliki alasan yang valid agar CTE rekursif dapat menjalankan lebih dari 500 iterasi.
Anda tidak masalah dengan eksekusi yang lebih lama.

Perlu diingat bahwa meningkatkan batas rekursif memiliki potensi risiko:

CTE Anda mungkin gagal dengan pesan error yang berbeda, seperti memori terlampaui atau waktu tunggu habis.
Jika project Anda menggunakan model penetapan harga on demand, CTE Anda mungkin masih akan gagal dengan error tingkat penagihan hingga Anda beralih ke model penetapan harga berbasis kapasitas.
CTE rekursif dengan jumlah iterasi besar menghabiskan banyak resource. Hal ini dapat memengaruhi kueri lain yang berjalan dalam reservasi yang sama, karena kueri tersebut bersaing untuk resource bersama.

Harga

Jika Anda menggunakan penagihan on-demand, BigQuery mengenakan biaya berdasarkan jumlah byte yang diproses selama eksekusi kueri dengan CTE rekursif.

Untuk mengetahui informasi selengkapnya, lihat Penghitungan ukuran kueri.

Kuota

Untuk mengetahui informasi tentang kuota dan batas CTE rekursif, lihat Kuota dan batas.