עבודה עם תחביר שאילתות של צינורות

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

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

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

יש בעיות בתחביר הרגיל שמקשות על הקריאה, הכתיבה והתחזוקה שלו. בטבלה הבאה אפשר לראות איך תחביר של צינורות פותר את הבעיות האלה:

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

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

תחביר בסיסי

בתחביר של צינורות, שאילתות מתחילות בשאילתת SQL רגילה או בסעיף FROM. לדוגמה, פסוקית FROM עצמאית, כמו FROM MyTable, היא תחביר תקין של מקף אנכי. אפשר להעביר את התוצאה של שאילתת SQL סטנדרטי או את הטבלה מהפסוקית FROM כקלט לתו המקף האנכי, |>, ואחריו שם של אופרטור צינור וארגומנטים לאופרטור הזה. האופרטור | מבצע טרנספורמציה כלשהי בטבלה, ואת התוצאה של הטרנספורמציה הזו אפשר להעביר לאופרטור | אחר.

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

המאפיינים העיקריים של תחביר הצינור הם:

• כל אופרטור צינור בתחביר צינור מורכב מסמל הצינור, |>, משם האופרטור ומארגומנטים כלשהם:
  |> operator_name argument_list
• אפשר להוסיף אופרטורים של צינורות לסוף של כל שאילתה תקינה.
• אפשר להשתמש באופרטורים של צינורות בכל סדר ובכל מספר פעמים.
• תחביר הצינור פועל בכל מקום שבו יש תמיכה בתחביר רגיל: בשאילתות, בתצוגות, בפונקציות שמחזירות ערכי טבלה ובהקשרים אחרים.
• אפשר לשלב תחביר של צינורות עם תחביר רגיל באותה שאילתה. לדוגמה, שאילתות משנה יכולות להשתמש בתחביר שונה משל שאילתת האב.
• מפעיל צינור יכול לראות כל כינוי שקיים בטבלה שלפני הצינור.
• שאילתה יכולה להתחיל עם פסקה של FROM, ואפשר להוסיף אליה אופרטורים של צינורות אחרי הפסקה של FROM.

לדוגמה, נבחן את הטבלה הבאה:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

כל אחת מהשאילתות הבאות מכילה תחביר תקין של צינור, שמראה איך אפשר ליצור שאילתה באופן רציף.

שאילתות יכולות להתחיל עם פסקה FROM ולא צריכות להכיל את הסימן |:

-- View the table.
FROM mydataset.Produce;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

אפשר לסנן באמצעות WHERE אופרטור צינור:

-- Filter items with no sales.
FROM mydataset.Produce
|> WHERE sales > 0;

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

כדי לבצע צבירה, משתמשים באופרטור הצינור AGGREGATE, ואחריו במספר כלשהו של פונקציות צבירה, ואז בסעיף GROUP BY. הפסקה GROUP BY היא חלק מאופרטור הצינור AGGREGATE, והיא לא מופרדת על ידי סמל הצינור (|>).

-- Compute total sales by item.
FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item;

/*---------+-------------+-----------+
 | item    | total_sales | num_sales |
 +---------+-------------+-----------+
 | apples  | 9           | 2         |
 | bananas | 15          | 1         |
 +---------+-------------+-----------*/

נניח שיש לכם את הטבלה הבאה שמכילה מזהה לכל פריט:

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

אפשר להשתמש באופרטור הצינור JOIN כדי לאחד את התוצאות של השאילתה הקודמת עם הטבלה הזו, וכך לכלול את המזהה של כל פריט:

FROM mydataset.Produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.ItemData USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

ההבדלים העיקריים מהתחביר הרגיל

ההבדלים בין תחביר עם קו אנכי לתחביר רגיל:

• שאילתות יכולות להתחיל עם פסקה של FROM.
• האופרטור SELECT לא מבצע צבירה. במקום זאת, צריך להשתמש בAGGREGATE אופרטור הצינור.
• הסינון מתבצע תמיד באמצעות WHERE האופרטור של הקו האנכי, שאפשר להשתמש בו בכל מקום. האופרטור WHERE pipe, שמחליף את HAVING ואת QUALIFY, יכול לסנן את התוצאות של פונקציות צבירה או פונקציות חלון.

פרטים נוספים מופיעים ברשימה המלאה של אופרטורים של צינורות.

תרחישים לדוגמה

תרחישים נפוצים לשימוש בתחביר של קו אנכי:

• ניתוח אד-הוק ויצירה מצטברת של שאילתות: הסדר הלוגי של הפעולות מקל על כתיבת שאילתות ועל איתור באגים בהן. כל שאילתה שמופיע לפניה התו |> היא שאילתה תקינה, כך שאפשר לראות את התוצאות הזמניות בשאילתה ארוכה. השיפורים בפרודוקטיביות יכולים להאיץ את תהליך הפיתוח בארגון.
• ניתוח יומנים: יש סוגים אחרים של תחביר דמוי צינור שפופולריים בקרב משתמשים בניתוח יומנים. תחביר הצינור מספק מבנה מוכר שמפשט את תהליך ההצטרפות של המשתמשים ל-Observability Analytics ול-BigQuery.

תכונות נוספות בתחביר של צינורות

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

EXTEND אופרטור קו אנכי

EXTEND האופרטור pipe מאפשר לכם לצרף עמודות מחושבות לטבלה הנוכחית. האופרטור EXTEND pipe דומה להצהרה SELECT *, new_column, אבל הוא מאפשר יותר גמישות בהפניה לכינויי עמודות.

בטבלה הבאה מופיעים שני ציונים של כל אדם במבחן:

CREATE OR REPLACE TABLE mydataset.Scores AS (
  SELECT 'Alex' AS student, 9 AS score1, 10 AS score2, 10 AS points_possible
  UNION ALL
  SELECT 'Dana' AS student, 5 AS score1, 7 AS score2, 10 AS points_possible);

/*---------+--------+--------+-----------------+
 | student | score1 | score2 | points_possible |
 +---------+--------+--------+-----------------+
 | Alex    | 9      | 10     | 10              |
 | Dana    | 5      | 7      | 10              |
 +---------+--------+--------+-----------------*/

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

SELECT student,
  (score1 + score2) / 2 AS average_score,
  (score1 + score2) / 2 / points_possible AS average_percent
FROM mydataset.Scores;

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

FROM mydataset.Scores
|> EXTEND (score1 + score2) / 2 AS average_score
|> EXTEND average_score / points_possible AS average_percent
|> SELECT student, average_score, average_percent;

/*---------+---------------+-----------------+
 | student | average_score | average_percent |
 +---------+---------------+-----------------+
 | Alex    | 9.5           | .95             |
 | Dana    | 6.0           | 0.6             |
 +---------+---------------+-----------------*/

SET אופרטור קו אנכי

SET האופרטור pipe מאפשר להחליף את הערך של עמודות בטבלה הנוכחית. האופרטור SET pipe דומה להצהרה SELECT * REPLACE (expression AS column). כדי להפנות לערך המקורי, צריך לציין את שם העמודה עם כינוי לטבלה.

FROM (SELECT 3 AS x, 5 AS y)
|> SET x = 2 * x;

/*---+---+
 | x | y |
 +---+---+
 | 6 | 5 |
 +---+---*/

DROP אופרטור קו אנכי

DROP האופרטור pipe מאפשר להסיר עמודות מהטבלה הנוכחית. האופרטור DROP pipe דומה להצהרה SELECT * EXCEPT(column). אחרי שמשמיטים עמודה, עדיין אפשר להפנות לערך המקורי על ידי ציון כינוי לטבלה בשם העמודה.

FROM (SELECT 1 AS x, 2 AS y) AS t
|> DROP x;

/*---+
 | y |
 +---+
 | 2 |
 +---*/

RENAME אופרטור קו אנכי

RENAME האופרטור pipe מאפשר לשנות את השם של עמודות מהטבלה הנוכחית. האופרטור RENAME pipe דומה להצהרה SELECT * EXCEPT(old_column), old_column AS new_column.

FROM (SELECT 1 AS x, 2 AS y, 3 AS z) AS t
|> RENAME y AS w;

/*---+---+---+
 | x | w | z |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---*/

AGGREGATE אופרטור קו אנכי

כדי לבצע צבירה בתחביר של צינורות, משתמשים AGGREGATE באופרטור צינור, ואחריו בכל מספר של פונקציות צבירה, ואז בסעיף GROUP BY. אין צורך לחזור על עמודות בסעיף SELECT.

בדוגמאות שבקטע הזה נעשה שימוש בטבלה Produce:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

/*---------+-------+-----------+
 | item    | sales | category  |
 +---------+-------+-----------+
 | apples  | 7     | fruit     |
 | apples  | 2     | fruit     |
 | carrots | 0     | vegetable |
 | bananas | 15    | fruit     |
 +---------+-------+-----------*/

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY item, category;

/*---------+-----------+-------+-------------+
 | item    | category  | total | num_records |
 +---------+-----------+-------+-------------+
 | apples  | fruit     | 9     | 2           |
 | carrots | vegetable | 0     | 1           |
 | bananas | fruit     | 15    | 1           |
 +---------+-----------+-------+-------------*/

אם אתם רוצים למיין את התוצאות מיד אחרי הצבירה, אתם יכולים לסמן את העמודות בפסוקית GROUP BY שאתם רוצים למיין באמצעות ASC או DESC. העמודות שלא סומנו לא מסודרות.

אם רוצים להזמין את כל העמודות, אפשר להחליף את פסוקית GROUP BY בפסוקית GROUP AND ORDER BY, שמזמינה כל עמודה בסדר עולה כברירת מחדל. אפשר לציין DESC אחרי העמודות שרוצים למיין בסדר יורד. לדוגמה, שלוש השאילתות הבאות שקולות:

-- Use a separate ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category, item
|> ORDER BY category DESC, item;

-- Explicitly mark how to order columns in the GROUP BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP BY category DESC, item ASC;

-- Only mark descending columns in the GROUP AND ORDER BY clause.
FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records
   GROUP AND ORDER BY category DESC, item;

היתרון בשימוש בסעיף GROUP AND ORDER BY הוא שלא צריך לחזור על שמות העמודות בשני מקומות.

כדי לבצע צבירה של כל הטבלה, משתמשים ב-GROUP BY() או משמיטים את פסוקית GROUP BY לגמרי:

FROM mydataset.Produce
|> AGGREGATE SUM(sales) AS total, COUNT(*) AS num_records;

/*-------+-------------+
 | total | num_records |
 +-------+-------------+
 | 24    | 4           |
 +-------+-------------*/

JOIN אופרטור קו אנכי

האופרטור JOIN של צינור מאפשר לכם לצרף את הטבלה הנוכחית לטבלה אחרת, והוא תומך בפעולות הצירוף הרגילות, כולל CROSS, ‏ INNER, ‏ LEFT, ‏ RIGHT ו-FULL.

בדוגמאות הבאות יש הפניה לטבלאות Produce ו-ItemData:

CREATE OR REPLACE TABLE mydataset.Produce AS (
  SELECT 'apples' AS item, 2 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'apples' AS item, 7 AS sales, 'fruit' AS category
  UNION ALL
  SELECT 'carrots' AS item, 0 AS sales, 'vegetable' AS category
  UNION ALL
  SELECT 'bananas' AS item, 15 AS sales, 'fruit' AS category);

CREATE OR REPLACE TABLE mydataset.ItemData AS (
  SELECT 'apples' AS item, '123' AS id
  UNION ALL
  SELECT 'bananas' AS item, '456' AS id
  UNION ALL
  SELECT 'carrots' AS item, '789' AS id
);

בדוגמה הבאה נעשה שימוש בסעיף USING כדי למנוע דו-משמעות לגבי העמודה:

FROM mydataset.Produce
|> JOIN mydataset.ItemData USING(item)
|> WHERE item = 'apples';

/*--------+-------+----------+-----+
 | item   | sales | category | id  |
 +--------+-------+----------+-----+
 | apples | 2     | fruit    | 123 |
 | apples | 7     | fruit    | 123 |
 +--------+-------+----------+-----*/

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

FROM mydataset.Produce
|> AS produce_table
|> JOIN mydataset.ItemData AS item_table
   ON produce_table.item = item_table.item
|> WHERE produce_table.item = 'bananas'
|> SELECT item_table.item, sales, id;

/*---------+-------+-----+
 | item    | sales | id  |
 +---------+-------+-----+
 | bananas | 15    | 123 |
 +---------+-------+-----*/

לצד הימני של הצירוף אין גישה לצד השמאלי של הצירוף, ולכן אי אפשר לצרף את הטבלה הנוכחית לעצמה. לדוגמה, השאילתה הבאה נכשלת:

-- This query doesn't work.
FROM mydataset.Produce
|> AS produce_table
|> JOIN produce_table AS produce_table_2 USING(item);

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

WITH cte_table AS (
  FROM mydataset.Produce
  |> WHERE item = 'carrots'
)
FROM cte_table
|> JOIN cte_table AS cte_table_2 USING(item);

דוגמה

בטבלה הבאה מפורט מידע על הזמנות של לקוחות:

CREATE OR REPLACE TABLE mydataset.CustomerOrders AS (
  SELECT 1 AS customer_id, 100 AS order_id, 'WA' AS state, 5 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 101 AS order_id, 'WA' AS state, 20 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 1 AS customer_id, 102 AS order_id, 'WA' AS state, 3 AS cost, 'food' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 103 AS order_id, 'NY' AS state, 16 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'NY' AS state, 22 AS cost, 'housewares' AS item_type
  UNION ALL
  SELECT 2 AS customer_id, 104 AS order_id, 'WA' AS state, 45 AS cost, 'clothing' AS item_type
  UNION ALL
  SELECT 3 AS customer_id, 105 AS order_id, 'MI' AS state, 29 AS cost, 'clothing' AS item_type);

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

SELECT state, item_type, AVG(total_cost) AS average
FROM
  (
    SELECT
      SUM(cost) AS total_cost,
      customer_id,
      state,
      item_type,
      COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
    FROM mydataset.CustomerOrders
    GROUP BY customer_id, state, item_type
    QUALIFY num_orders > 1
  )
GROUP BY state, item_type
ORDER BY state DESC, item_type ASC;

אם קוראים את השאילתה מלמעלה למטה, נתקלים בעמודה total_cost לפני שהיא הוגדרה. גם בתוך שאילתת המשנה, קוראים את שמות העמודות לפני שרואים מאיזו טבלה הן מגיעות.

כדי להבין את השאילתה הזו, צריך לקרוא אותה מבפנים החוצה. העמודות state ו-item_type חוזרות על עצמן פעמים רבות בסעיפים SELECT ו-GROUP BY, ואז שוב בסעיף ORDER BY.

השאילתה הבאה שוות ערך ונכתבה באמצעות תחביר של קו אנכי:

FROM mydataset.CustomerOrders
|> AGGREGATE SUM(cost) AS total_cost, GROUP BY customer_id, state, item_type
|> EXTEND COUNT(*) OVER (PARTITION BY customer_id) AS num_orders
|> WHERE num_orders > 1
|> AGGREGATE AVG(total_cost) AS average GROUP BY state DESC, item_type ASC;

/*-------+------------+---------+
 | state | item_type  | average |
 +-------+------------+---------+
 | WA    | clothing   | 35.0    |
 | WA    | food       | 3.0     |
 | NY    | clothing   | 16.0    |
 | NY    | housewares | 22.0    |
 +-------+------------+---------*/

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

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

מגבלות

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

המאמרים הבאים

• ניתוח נתונים באמצעות תחביר של צינורות
• הסבר על תחביר של שאילתות עם צינורות
• הפניה לתחביר של שאילתות רגילות
• מאמר בכנס VLDB 2024 בנושא תחביר של צינורות