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 de BigQuery es SQL estándar. Para obtener más información sobre cómo controlar los datos anidados y repetidos en SQL estándar, consulta la Guía de migración de SQL estándar.

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 mantiene 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.

COMPACTA

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 un resultado acoplado:

+---------------+-----+--------+-------------------+------------------------+
|     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 | Russia            |                   1998 |
| Anna Karenina |  45 | Female | Russia            |                   2001 |
| Anna Karenina |  45 | Female | Russia            |                   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 de forma automática, 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 siguiente:

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

DENTRO de la cláusula

La palabra clave WITHIN trabaja en forma específica con funciones agregadas para agregar a través de los de niños y repetidos dentro de los registros y campos anidados. Cuando especificas la palabra clave WITHIN, tienes que especificar el alcance sobre el que quieres agregar:

  • WITHIN RECORD: agrega datos en los valores repetidos dentro del 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 niños 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 consultas WITHIN RECORD; John Doe tiene dos niños llamados Jane y John, Jane Austen tiene dos niños llamados Josh y Jim, Mike Jones tiene tres niños; Earl, Sam y Kit, mientras que Anna Karenina no tiene niños.

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 | Russia            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
+---------------+---------------------+-------------------+-------------------------+

Esta consulta lleva a cabo estos pasos:

  • Realiza un WITHIN RECORD en citiesLived.place y cuenta el número de lugares en los que vivió una persona.
  • Realiza un WITHIN en citiesLived.yearsLived y cuenta el número de veces que cada persona vivió en cada ciudad (cuenta solo en citiesLived).

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.

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.