עבודה עם CTE רקורסיביים

ב-GoogleSQL ל-BigQuery, משפט WITH מכיל ביטוי טבלה נפוץ (CTE) אחד או יותר, שאפשר להפנות אליו בביטוי שאילתה. ביטויי CTE יכולים להיות לא רקורסיביים, רקורסיביים או שניהם. מילת המפתח RECURSIVE מאפשרת רקורסיה בסעיף WITH (WITH RECURSIVE).

‫CTE רקורסיבי יכול להפנות לעצמו, ל-CTE קודם או ל-CTE עוקב. ‫CTE לא רקורסיבי יכול להפנות רק ל-CTEs קודמים, ולא יכול להפנות לעצמו. ביטויי CTE רקורסיביים פועלים ברציפות עד שלא נמצאות תוצאות חדשות, ואילו ביטויי CTE לא רקורסיביים פועלים פעם אחת. לכן, בדרך כלל משתמשים ב-CTE רקורסיביים כדי לשלוח שאילתות לנתונים היררכיים ולנתוני תרשימים.

לדוגמה, נניח שיש תרשים שבו כל שורה מייצגת צומת שיכול לקשר לצמתים אחרים. כדי למצוא את הסגור הטרנזיטיבי של כל הצמתים הנגישים מצומת התחלה מסוים בלי לדעת את המספר המקסימלי של הדילוגים, צריך CTE רקורסיבי בשאילתה (WITH RECURSIVE). השאילתה הרקורסיבית תתחיל במקרה הבסיסי של צומת ההתחלה, ובכל שלב יחושבו הצמתים החדשים שלא נראו, שאפשר להגיע אליהם מכל הצמתים שנראו עד כה עד לשלב הקודם. השאילתה מסתיימת כשלא נמצאים צמתים חדשים.

עם זאת, ביטויי CTE רקורסיביים יכולים להיות יקרים מבחינה חישובית, לכן לפני שאתם משתמשים בהם, כדאי לעיין במדריך הזה ובקטע הסעיף WITH במסמכי העזרה של GoogleSQL.

יצירת CTE רקורסיבי

כדי ליצור CTE רקורסיבי ב-GoogleSQL, משתמשים בסעיף WITH RECURSIVE, כמו בדוגמה הבאה:

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

הדוגמה הקודמת מניבה את התוצאות הבאות:

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

CTE רקורסיבי כולל מונח בסיס, אופרטור איחוד ומונח רקורסיבי. המונח הבסיסי מריץ את האיטרציה הראשונה של פעולת האיחוד הרקורסיבית. המונח הרקורסיבי מריץ את שאר החזרות, והוא חייב לכלול הפניה עצמית אחת ל-CTE הרקורסיבי. רק המונח הרקורסיבי יכול לכלול הפניה עצמית.

בדוגמה הקודמת, ה-CTE הרקורסיבי מכיל את הרכיבים הבאים:

• שם ה-CTE הרקורסיבי: CTE_1
• מונח בסיסי: SELECT 1 AS iteration
• אופרטור איחוד: UNION ALL
• מונח רקורסיבי: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3

מידע נוסף על התחביר, הכללים והדוגמאות של CTE רקורסיבי זמין בקטע WITH במאמרי העזרה של GoogleSQL.

בדיקת הנגישות בגרף אציקלי מכוון (DAG)

אפשר להשתמש בשאילתה רקורסיבית כדי לבדוק את יכולת ההגעה בגרף אציקלי מכוון (DAG). השאילתה הבאה מוצאת את כל הצמתים שאפשר להגיע אליהם מהצומת 5 בתרשים שנקרא 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;

הדוגמה הקודמת מניבה את התוצאות הבאות:

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

פתרון שגיאות שקשורות למגבלת האיטרציות

ביטויי CTE רקורסיביים יכולים לגרום לרקורסיה אינסופית, שמתרחשת כשהמונח הרקורסיבי פועל באופן רציף בלי לעמוד בתנאי סיום. כדי לסיים רקורסיות אינסופיות, נאכפת מגבלה של איטרציות לכל CTE רקורסיבי. ב-BigQuery, מגבלת האיטרציות היא 500 איטרציות. ברגע ש-CTE רקורסיבי מגיע למספר המקסימלי של איטרציות, הביצוע של ה-CTE מבוטל ומוצגת שגיאה.

המגבלה הזו קיימת כי החישוב של CTE רקורסיבי יכול להיות יקר, והרצת CTE עם מספר גדול של איטרציות צורכת הרבה משאבי מערכת ולוקח הרבה יותר זמן לסיים אותה.

בדרך כלל, שאילתות שמגיעות למגבלת האיטרציות לא כוללות תנאי סיום מתאים, ולכן נוצרת לולאה אינסופית, או שהן משתמשות בביטויי CTE רקורסיביים בתרחישים לא מתאימים.

אם נתקלתם בשגיאה שקשורה למגבלת החזרות של הרקורסיה, כדאי לעיין בהצעות שבקטע הזה.

בדיקה אם יש רקורסיה אינסופית

כדי למנוע רקורסיה אינסופית, חשוב לוודא שהמונח הרקורסיבי יכול להפיק תוצאה ריקה אחרי ביצוע מספר מסוים של איטרציות.

אחת הדרכים לבדוק אם יש רקורסיה אינסופית היא להמיר את ה-CTE הרקורסיבי ל-TEMP TABLE עם לולאת REPEAT ל-100 האיטרציות הראשונות, באופן הבא:

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;

מחליפים את הערכים הבאים:

• ‫recursive_cte_name: ה-CTE הרקורסיבי לניפוי באגים.
• ‫base_expression: המונח הבסיסי של ה-CTE הרקורסיבי.
• ‫recursive_expression: המונח הרקורסיבי של ה-CTE הרקורסיבי.
• ‫termination_condition_expression: ביטוי הסיום של ה-CTE הרקורסיבי.

לדוגמה, נניח שיש לנו את ה-CTE הרקורסיבי הבא שנקרא TestCTE:

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

בדוגמה הזו נעשה שימוש בערכים הבאים:

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

לכן, הקוד הבא יבדוק את TestCTE לרקורסיה אינסופית:

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;

הדוגמה הקודמת מפיקה את התוצאות הבאות, שכוללות את מזהה האיטרציה ואת מספר השורות שהופקו במהלך האיטרציה הזו:

/*-----------+----------+
 | 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        |
 +-----------+----------*/

אלה התוצאות בפועל שהתקבלו במהלך איטרציה 2:

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

אם מספר השורות תמיד גדול מאפס, כמו בדוגמה הזו, סביר להניח שיש בדוגמה רקורסיה אינסופית.

מוודאים שהשימוש ב-CTE הרקורסיבי מתאים

מוודאים שמשתמשים ב-CTE הרקורסיבי בתרחיש מתאים. החישוב של ביטויי CTE רקורסיביים יכול להיות יקר, כי הם נועדו לשאילתות של נתונים היררכיים ונתוני תרשימים. אם אתם לא שולחים שאילתות לשני סוגי הנתונים האלה, כדאי לשקול חלופות, כמו שימוש בהצהרת LOOP עם CTE לא רקורסיבי.

פיצול של CTE רקורסיבי לכמה CTE רקורסיביים

אם לדעתכם ה-CTE הרקורסיבי שלכם צריך יותר מהמספר המקסימלי המותר של חזרות, יכול להיות שתוכלו לפצל אותו לכמה CTE רקורסיביים.

אפשר לפצל CTE רקורסיבי באמצעות מבנה שאילתה שדומה לזה:

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
[...]

מחליפים את הערכים הבאים:

• ‫base_expression: ביטוי המונח הבסיסי של ה-CTE הנוכחי.
• recursive_expression: ביטוי המונח הרקורסיבי של ה-CTE הנוכחי.

לדוגמה, הקוד הבא מפצל CTE לשלושה CTE נפרדים:

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

בדוגמה הקודמת, 500 חזרות הוחלפו ב-10 חזרות כדי שתוכלו לראות את תוצאות השאילתה מהר יותר. השאילתה מפיקה 30 שורות, אבל כל CTE רקורסיבי חוזר רק 10 פעמים. הפלט אמור להיראות כך:

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

אפשר לבדוק את השאילתה הקודמת על איטרציות גדולות בהרבה.

שימוש בלולאה במקום ב-CTE רקורסיבי

כדי להימנע ממגבלות על איטרציות, כדאי להשתמש בלולאה במקום ב-CTE רקורסיבי. אפשר ליצור לולאה באמצעות אחת מכמה הצהרות לולאה, כמו LOOP,‏ REPEAT או WHILE. מידע נוסף זמין במאמר לולאות.

שינוי מגבלת הרקורסיה

אם לדעתכם הגורמים הבאים רלוונטיים, פנו לשירות הלקוחות כדי להגדיל את המגבלה הרקורסיבית:

• יש לכם סיבה מוצדקת להריץ את ה-CTE הרקורסיבי יותר מ-500 איטרציות.
• אתם מוכנים להמתין הרבה יותר זמן עד שהפעולה תסתיים.

חשוב לזכור שהגדלת מגבלת הרקורסיה כרוכה בסיכונים פוטנציאליים:

• יכול להיות שה-CTE ייכשל עם הודעת שגיאה אחרת, כמו חריגה מהזיכרון או זמן קצוב לתפוגה.
• אם הפרויקט שלכם משתמש במודל התמחור על פי דרישה, יכול להיות שעדיין תופיע שגיאה ב-CTE בגלל רמת החיוב, עד שתעברו למודל התמחור שמבוסס על קיבולת.
• CTE רקורסיבי עם מספר גדול של איטרציות צורך הרבה משאבים. יכול להיות שהדבר ישפיע על שאילתות אחרות שפועלות באותו מינוי, כי הן מתחרות על משאבים משותפים.

תמחור

אם אתם משתמשים בחיוב על פי דרישה, החיוב ב-BigQuery מבוסס על מספר הבייטים שעובדו במהלך הרצת שאילתה עם CTE רקורסיבי.

למידע נוסף, קראו את המאמר חישוב גודל השאילתה.

מכסות

מידע נוסף על מכסות ומגבלות של CTE רקורסיבי זמין במאמר מכסות ומגבלות.