Consultar campos anidados y repetidos en SQL antiguo
En este documento se explica cómo consultar datos anidados y repetidos con la sintaxis de consultas de SQL antiguo. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre cómo gestionar datos anidados y repetidos en GoogleSQL, consulta la guía de migración de GoogleSQL.
BigQuery admite la carga y la exportación de datos anidados y repetidos en forma de archivos JSON y Avro. En muchas consultas de SQL antiguo, BigQuery puede acoplar los datos automáticamente. Por ejemplo, muchas instrucciones SELECT pueden recuperar campos anidados o repetidos manteniendo la estructura de los datos, y las cláusulas WHERE pueden filtrar datos manteniendo su estructura. Por el contrario, las cláusulas ORDER BY y GROUP BY
aplanan implícitamente los datos consultados. En los casos en los que los datos no se aplanan de forma implícita, como cuando se consultan varios campos repetidos en SQL antiguo, puede consultar los datos mediante las funciones de SQL FLATTEN y WITHIN.
FLATTEN
Al consultar datos anidados, BigQuery automáticamente quita el formato a los datos de la tabla. Por ejemplo, veamos un esquema de muestra para datos de personas:
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)Observa que hay varios campos repetidos y anidados. Si ejecutas una consulta de SQL antiguo como la siguiente en la tabla person :
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery devuelve los datos con una salida combinada:
+---------------+-----+--------+-------------------+------------------------+ | 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 | +---------------+-----+--------+-------------------+------------------------+
En este ejemplo, citiesLived.place ahora es citiesLived_place y citiesLived.yearsLived ahora es citiesLived_yearsLived.
Aunque BigQuery puede acoplar automáticamente los campos anidados, es posible que tengas que llamar a FLATTEN explícitamente cuando trabajes con más de un campo repetido. Por ejemplo, si intentas ejecutar una consulta con SQL antiguo como la siguiente:
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
BigQuery devuelve un error similar al siguiente:
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
Para consultar más de un campo repetido, primero tienes que quitar el formato de uno de los campos:
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
Esto devuelve:
+------------+-----+--------+-------------------+ | 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 | +------------+-----+--------+-------------------+
Cláusula WITHIN
La palabra clave WITHIN funciona específicamente con funciones de agregación para agregar datos de campos secundarios y repetidos en registros y campos anidados. Cuando especifique la palabra clave WITHIN, deberá especificar el ámbito en el que quiere agregar los datos:
WITHIN RECORD: agrega datos en los valores repetidos del registro.WITHIN node_name: agrega datos en los valores repetidos del nodo especificado, donde un nodo es un nodo principal del campo de la función de agregación.
Supongamos que quieres saber el número de hijos que tiene cada persona del ejemplo anterior. Para ello, puedes contar el número de children.name que tiene cada registro:
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
Obtienes el resultado siguiente:
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
Para comparar, enumera todos los nombres de niños:
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 | +---------------+---------------+
Esto coincide con los resultados de nuestra consulta WITHIN RECORD: Juan Pérez tiene dos hijos llamados Juan y Juana, Jane Austen tiene dos hijos llamados Juan y Jim, Mike Jones tiene tres hijos llamados Earl, Sam y Kit, y Anna Karenina no tiene hijos.
Ahora, supongamos que quieres saber cuántas veces ha vivido una persona en lugares distintos.
Puedes usar la cláusula WITHIN para agregar por un nodo en particular:
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 | +---------------+---------------------+-------------------+-------------------------+
Esta consulta hace lo siguiente:
- Realiza una
WITHIN RECORDencitiesLived.placey cuenta el número de lugares en los que ha vivido cada persona. - Realiza una
WITHINencitiesLived.yearsLivedy cuenta el número de veces que cada persona ha vivido en cada ciudad (solo encitiesLived).
Usar la agregación acotada en campos anidados y repetidos es una de las funciones más potentes de BigQuery, que a menudo puede eliminar las uniones costosas en las consultas.