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

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

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

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

עם זאת, CTEs רקורסיביים יכולים להיות יקרים מבחינת חישובים, ולכן לפני שמשתמשים בהם, כדאי לעיין במדריך הזה ובקטע WITH clause במסמכי העזר של 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 רקורסיבי זמין בקטע WITHclause במאמרי העזרה של 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 רקורסיביים.

אפשר לפצל 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. מידע נוסף זמין במאמר בנושא לולאות.

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

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

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

חשוב לזכור שהגדלת המגבלה הרקורסיבית עלולה להיות מסוכנת:

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

תמחור

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

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

מכסות

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