שאילתות של שדות בתוך שדות ושדות חוזרים ב-SQL מדור קודם

במאמר הזה מוסבר איך להריץ שאילתות על נתונים מקוננים וחוזרים בתחביר של שאילתות SQL מדור קודם. התחביר המועדף לשאילתות ב-BigQuery הוא GoogleSQL. מידע על טיפול בנתונים מקוננים וחוזרים ב-GoogleSQL זמין במדריך להעברת נתונים מ-GoogleSQL.

‫BigQuery תומך בטעינה ובייצוא של נתונים מקוננים וחוזרים בצורה של קובצי JSON ו-Avro. במקרים רבים, BigQuery יכול לשטח את הנתונים באופן אוטומטי בשאילתות SQL מדור קודם. לדוגמה, הרבה הצהרות SELECT יכולות לאחזר שדות מוטמעים או חוזרים תוך שמירה על מבנה הנתונים, וסעיפים WHERE יכולים לסנן נתונים תוך שמירה על המבנה שלהם. לעומת זאת, פסקיות ORDER BY ו-GROUP BY משטחות באופן מרומז את הנתונים שנשאלו. במקרים שבהם הנתונים לא משוטחים באופן מרומז, כמו כששולחים שאילתות לגבי כמה שדות חוזרים ב-SQL מדור קודם, אפשר לשלוח שאילתות לגבי הנתונים באמצעות פונקציות ה-SQL‏ FLATTEN ו-WITHIN.

FLATTEN

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

   Last modified                 Schema                 Total Rows   Total Bytes   Expiration
 ----------------- ----------------------------------- ------------ ------------- ------------
  27 Sep 10:01:06   |- kind: string                     4            794
                    |- fullName: string (required)
                    |- age: integer
                    |- gender: string
                    +- phoneNumber: record
                    |  |- areaCode: integer
                    |  |- number: integer
                    +- children: record (repeated)
                    |  |- name: string
                    |  |- gender: string
                    |  |- age: integer
                    +- citiesLived: record (repeated)
                    |  |- place: string
                    |  +- yearsLived: integer (repeated)

שימו לב שיש כמה שדות חוזרים ושדות בתוך שדות. אם מריצים שאילתת SQL מדור קודם כמו השאילתה הבאה על הטבלה person :

SELECT
  fullName AS name,
  age,
  gender,
  citiesLived.place,
  citiesLived.yearsLived
FROM [dataset.tableId]

הנתונים שמוחזרים מ-BigQuery הם פלט שטוח:

+---------------+-----+--------+-------------------+------------------------+
|     name      | age | gender | citiesLived_place | citiesLived_yearsLived |
+---------------+-----+--------+-------------------+------------------------+
| John Doe      |  22 | Male   | Seattle           |                   1995 |
| John Doe      |  22 | Male   | Stockholm         |                   2005 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1989 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1993 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1998 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   2002 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1990 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1993 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1998 |
| Mike Jones    |  35 | Male   | Washington DC     |                   2008 |
| Mike Jones    |  35 | Male   | Portland          |                   1993 |
| Mike Jones    |  35 | Male   | Portland          |                   1998 |
| Mike Jones    |  35 | Male   | Portland          |                   2003 |
| Mike Jones    |  35 | Male   | Portland          |                   2005 |
| Mike Jones    |  35 | Male   | Austin            |                   1973 |
| Mike Jones    |  35 | Male   | Austin            |                   1998 |
| Mike Jones    |  35 | Male   | Austin            |                   2001 |
| Mike Jones    |  35 | Male   | Austin            |                   2005 |
| Anna Karenina |  45 | Female | Stockholm         |                   1992 |
| Anna Karenina |  45 | Female | Stockholm         |                   1998 |
| Anna Karenina |  45 | Female | Stockholm         |                   2000 |
| Anna Karenina |  45 | Female | Stockholm         |                   2010 |
| Anna Karenina |  45 | Female | Moscow            |                   1998 |
| Anna Karenina |  45 | Female | Moscow            |                   2001 |
| Anna Karenina |  45 | Female | Moscow            |                   2005 |
| Anna Karenina |  45 | Female | Austin            |                   1995 |
| Anna Karenina |  45 | Female | Austin            |                   1999 |
+---------------+-----+--------+-------------------+------------------------+

בדוגמה הזו, citiesLived.place הוא עכשיו citiesLived_place ו-citiesLived.yearsLived הוא עכשיו citiesLived_yearsLived.

למרות ש-BigQuery יכול לשטח באופן אוטומטי שדות מקוננים, יכול להיות שתצטרכו לקרוא באופן מפורש לפונקציה FLATTEN כשעובדים עם יותר משדה חוזר אחד. לדוגמה, אם מנסים להריץ שאילתת SQL מדור קודם כמו הבאה:

SELECT fullName, age
FROM [dataset.tableId]
WHERE
  (citiesLived.yearsLived > 1995 ) AND
  (children.age > 3)

‫BigQuery מחזיר שגיאה דומה ל:

Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived

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

SELECT
  fullName,
  age,
  gender,
  citiesLived.place
FROM (FLATTEN([dataset.tableId], children))
WHERE
  (citiesLived.yearsLived > 1995) AND
  (children.age > 3)
GROUP BY fullName, age, gender, citiesLived.place

החזרות:

+------------+-----+--------+-------------------+
|  fullName  | age | gender | citiesLived_place |
+------------+-----+--------+-------------------+
| John Doe   |  22 | Male   | Stockholm         |
| Mike Jones |  35 | Male   | Los Angeles       |
| Mike Jones |  35 | Male   | Washington DC     |
| Mike Jones |  35 | Male   | Portland          |
| Mike Jones |  35 | Male   | Austin            |
+------------+-----+--------+-------------------+

הפסוקית WITHIN

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

  • ‫WITHIN RECORD: צובר נתונים בערכים החוזרים ברשומה.
  • ‫WITHIN node_name: צבירת נתונים בערכים חוזרים בתוך הצומת שצוין, כאשר צומת הוא צומת אב של השדה בפונקציית הצבירה.

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

SELECT
  fullName,
  COUNT(children.name) WITHIN RECORD AS numberOfChildren
FROM [dataset.tableId];

התוצאה הבאה מוצגת:

+---------------+------------------+
|   fullName    | numberOfChildren |
+---------------+------------------+
| John Doe      |                2 |
| Jane Austen   |                2 |
| Mike Jones    |                3 |
| Anna Karenina |                0 |
+---------------+------------------+

כדי להשוות, כדאי לנסות לרשום את כל השמות של הילדים:

SELECT fullName, children.name
FROM [dataset.tableId]
 
+---------------+---------------+
|   fullName    | children_name |
+---------------+---------------+
| John Doe      | Jane          |
| John Doe      | John          |
| Jane Austen   | Josh          |
| Jane Austen   | Jim           |
| Mike Jones    | Earl          |
| Mike Jones    | Sam           |
| Mike Jones    | Kit           |
| Anna Karenina | None          |
+---------------+---------------+

התוצאות האלה תואמות לתוצאות השאילתה WITHIN RECORD. לג'ון דאו יש שני ילדים בשם ג'יין וג'ון, לג'יין אוסטן יש שני ילדים בשם ג'וש וג'ים, למייק ג'ונס יש שלושה ילדים בשם ארל, סם וקיט, ולאנה קארנינה אין ילדים.

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

SELECT
  fullName,
  COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived,
  citiesLived.place,
  COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity,
FROM [dataset.tableId];
 
+---------------+---------------------+-------------------+-------------------------+
|   fullName    | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity |
+---------------+---------------------+-------------------+-------------------------+
| John Doe      |                   2 | Seattle           |                       1 |
| John Doe      |                   2 | Stockholm         |                       1 |
| Mike Jones    |                   4 | Los Angeles       |                       4 |
| Mike Jones    |                   4 | Washington DC     |                       4 |
| Mike Jones    |                   4 | Portland          |                       4 |
| Mike Jones    |                   4 | Austin            |                       4 |
| Anna Karenina |                   3 | Stockholm         |                       4 |
| Anna Karenina |                   3 | Moscow            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
+---------------+---------------------+-------------------+-------------------------+

השאילתה הזו מבצעת את הפעולות הבאות:

  • מבצעת WITHIN RECORD ב-citiesLived.place וסופרת את מספר המקומות שבהם כל אדם גר
  • מבצעת WITHIN ב-citiesLived.yearsLived וסופרת את מספר הפעמים שכל אדם גר בכל עיר (הספירה מתבצעת רק ב-citiesLived).

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