Consulta campos anidados y repetidos en SQL heredado
En este documento, se detalla cómo consultar datos anidados y repetidos en la sintaxis de consulta de SQL heredado. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre el manejo de datos anidados y repetidos en GoogleSQL, consulta la guía de migración de GoogleSQL.
BigQuery es compatible con la carga y exportación de los datos anidados y repetidos en el formato de archivos JSON y Avro. Para muchas consultas de SQL heredado, BigQuery puede compactar datos de forma automática. Por ejemplo, muchas declaraciones SELECT pueden recuperar campos repetidos o anidados mientras mantienen la estructura de los datos. A su vez, las cláusulas WHERE pueden filtrar los datos mientras se mantiene su estructura. Por el contrario, las cláusulas ORDER BY y GROUP BY compactan los datos consultados de forma implícita. En los casos en que los datos no se compactan de forma implícita, como cuando se consultan varios campos repetidos en SQL heredado, puedes consultar tus datos con las funciones FLATTEN y WITHIN de SQL.
FLATTEN
Cuando consultas datos anidados, BigQuery compacta los datos de la tabla de forma automática por ti. Por ejemplo, observemos un esquema de muestra sobre 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)
Ten en cuenta que hay varios campos anidados y repetidos. Si ejecutas una consulta de SQL heredado como la siguiente en la tabla sobre personas:
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery muestra los datos con una salida compactada:
+---------------+-----+--------+-------------------+------------------------+ | 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.
A pesar de que BigQuery puede compactar los campos anidados automáticamente, tal vez necesites llamar de forma explícita a FLATTEN cuando se trata de más de un campo repetido. Por ejemplo, si intentas ejecutar una consulta de SQL heredado como la que se muestra a continuación:
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
BigQuery muestra un error similar al siguiente:
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
Para consultar en más de un campo repetido, debes compactar 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
Que muestra lo siguiente:
+------------+-----+--------+-------------------+ | 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 trabaja en forma específica con funciones agregadas para agregar datos en campos de niños y repetidos dentro de los registros y campos anidados. Cuando especificas la palabra clave WITHIN, debes especificar el permiso según el cual deseas agregar, como se indica a continuación:
WITHIN RECORD: Agrega datos en los valores repetidos en el registro.WITHIN node_name: Agrega datos en los valores repetidos dentro del nodo especificado, en el que un nodo es un nodo superior del campo en la función de agregación.
Supongamos que quieres encontrar la cantidad de hijos que tiene cada persona en nuestro ejemplo anterior. Para hacerlo, puedes contar la cantidad de nombres de niños 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 | +---------------+------------------+
Con el fin de comparar, intenta enumerar todos los nombres de los 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 nuestros resultados de la consulta WITHIN RECORD. John Doe tiene dos hijos llamados Jane y John; Jane Finten tiene dos hijos llamados Josh y Jim; Mike Jones tiene tres hijos llamados Earl, Sam y Kit, y Anna Karenina no tiene hijos.
Ahora, supongamos que quieres encontrar la cantidad de veces que una persona vivió en distintos lugares.
Puedes usar la cláusula WITHIN para agregar en 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 un
WITHIN RECORDencitiesLived.placey cuenta la cantidad de lugares en los que vivió cada persona. - Realiza un
WITHINencitiesLived.yearsLivedy cuenta la cantidad de veces que cada persona vivió en cada ciudad (solo cuenta en función decitiesLived).
Una de las características más poderosas de BigQuery es el uso de una agregación con un alcance delimitado sobre los campos anidados y repetidos, que muchas veces puede quitar uniones costosas en las consultas.