עבודה עם 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 רקורסיבי זמין במאמר בנושא WITHסעיף במאמרי העזרה של GoogleSQL.

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

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