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

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

‫BigQuery תומך בטעינה ובייצוא של נתונים מקוננים וחוזרים בצורה של קובצי JSON ו-Avro. במקרה של הרבה שאילתות SQL מדור קודם, BigQuery יכול לשטח את הנתונים באופן אוטומטי. לדוגמה, הרבה הצהרות 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 Clause

מילת המפתח 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, שיכולה לעיתים קרובות לבטל הצטרפויות יקרות בשאילתות.