Interroger des champs imbriqués et répétés en ancien SQL

Ce document explique en détail comment interroger des données imbriquées et répétées dans une syntaxe de requête en ancien SQL. La syntaxe de requête privilégiée pour BigQuery est GoogleSQL. Pour plus d'informations sur le traitement des données imbriquées et répétées dans GoogleSQL, consultez le guide de migration GoogleSQL.

BigQuery accepte le chargement et l'exportation de données imbriquées et répétées sous la forme de fichiers JSON et Avro. BigQuery peut aplatir les données automatiquement pour de nombreuses requêtes en ancien SQL. Par exemple, de nombreuses instructions SELECT peuvent récupérer des champs imbriqués ou répétés tout en conservant la structure des données, et les clauses WHERE peuvent filtrer les données tout en conservant leur structure. Inversement, les clauses ORDER BY et GROUP BY aplatissent implicitement les données interrogées. Dans les cas où les données ne sont pas implicitement aplaties, tels que l'interrogation de plusieurs champs répétés en ancien SQL, vous pouvez interroger vos données à l'aide des fonctions SQL FLATTEN et WITHIN.

FLATTEN

Lorsque vous interrogez des données imbriquées, BigQuery aplatit automatiquement les données de la table. Examinons cet exemple de schéma de données personnelles :

   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)

Notez qu'il comporte plusieurs champs répétés et imbriqués. Si vous exécutez une requête en ancien SQL telle que la suivante dans la table des personnes :

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

BigQuery renvoie les données de sortie sous forme aplatie :

+---------------+-----+--------+-------------------+------------------------+
|     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 |
+---------------+-----+--------+-------------------+------------------------+

Dans cet exemple, citiesLived.place est maintenant citiesLived_place et citiesLived.yearsLived est maintenant citiesLived_yearsLived.

Bien que BigQuery puisse automatiquement aplatir les champs imbriqués, vous devrez peut-être appeler explicitement la fonction FLATTEN pour traiter plusieurs champs répétés. Par exemple, si vous tentez d'exécuter une requête en ancien SQL telle que celle-ci :

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

BigQuery renvoie une erreur semblable à :

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

Pour interroger plusieurs champs répétés, vous devez aplatir l'un des champs :

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

Lequel renvoie :

+------------+-----+--------+-------------------+
|  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

Le mot clé WITHIN fonctionne spécifiquement avec les fonctions d'agrégation pour agréger des enfants et des champs répétés dans les enregistrements et les champs imbriqués. Lorsque vous spécifiez le mot clé WITHIN, vous devez spécifier le champ d'application de l'agrégation que vous souhaitez effectuer :

  • WITHIN RECORD : agrège les données des valeurs répétées dans l'enregistrement.
  • WITHIN node_name : agrège les données des valeurs répétées dans le nœud spécifié, où un nœud est un nœud parent du champ dans la fonction d'agrégation.

Supposons que vous souhaitiez connaître le nombre d'enfants pour chacune des personnes, dans notre exemple précédent. Pour ce faire, vous pouvez compter le nombre d'occurrences de "children.name" pour chaque enregistrement :

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

Vous obtenez le résultat suivant :

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

Pour comparer, essayez de répertorier tous les noms d'enfants :

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          |
+---------------+---------------+

Cela correspond à nos résultats de requête WITHIN RECORD. John Doe a deux enfants prénommés Jane et John, Jane Austen a deux enfants prénommés Josh et Jim, Mike Jones a trois enfants prénommés Earl, Sam et Kit, et Anna Karenina n'a pas d'enfants.

Supposons maintenant que vous souhaitiez savoir le nombre de fois où une personne a vécu dans des endroits différents. Vous pouvez utiliser la clause WITHIN pour agréger sur un nœud particulier :

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 |
+---------------+---------------------+-------------------+-------------------------+

Cette requête effectue les opérations suivantes :

  • Exécute une opération WITHIN RECORD sur citiesLived.place et compte le nombre de lieux où chaque personne a vécu
  • Exécute une opération WITHIN sur citiesLived.yearsLived et compte le nombre de fois où chaque personne a vécu dans chaque ville (en ne comptant que sur le champ citiesLived)

L'utilisation d'une agrégation ciblée sur des champs imbriqués et répétés est l'une des fonctionnalités les plus puissantes de BigQuery, et permet souvent d'éliminer les jointures coûteuses dans les requêtes.