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
surcitiesLived.place
et compte le nombre de lieux où chaque personne a vécu - Exécute une opération
WITHIN
surcitiesLived.yearsLived
et compte le nombre de fois où chaque personne a vécu dans chaque ville (en ne comptant que sur le champcitiesLived
)
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.