Como consultar campos aninhados e repetidos no SQL legado

Neste documento, você verá detalhes sobre como consultar dados aninhados e repetidos na sintaxe de consulta SQL legada. A sintaxe de consulta de preferência para o BigQuery é o GoogleSQL. Para mais informações sobre gerenciamento de dados aninhados e repetidos no GoogleSQL, consulte o Guia de migração do GoogleSQL.

No BigQuery, é possível carregar e exportar dados aninhados e repetidos como arquivos nos formatos JSON e Avro. Para muitas consultas do SQL legado, o nivelamento dos dados pode ocorrer automaticamente no BigQuery. Por exemplo, muitas instruções SELECT podem recuperar campos aninhados ou repetidos e, ao mesmo tempo, manter a estrutura dos dados. Com as cláusulas WHERE, é possível filtrar os dados enquanto mantém a estrutura deles. Por outro lado, as cláusulas ORDER BY e GROUP BY nivelam implicitamente os dados consultados. Para casos em que os dados não são implicitamente nivelados, como uma consulta a vários campos repetidos no SQL legado, consulte os seus dados usando as funções FLATTEN e WITHIN do SQL.

FLATTEN

Quando você faz consultas em dados aninhados, o BigQuery automaticamente nivela os dados da tabela. Por exemplo, vamos analisar uma amostra de esquema para dados pessoais:

   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)

Observe que há vários campos aninhados e repetidos. Se você executar uma consulta de SQL legado como a seguinte na tabela de pessoas:

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

O BigQuery retorna os dados com um resultado nivelado:

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

Neste exemplo, citiesLived.place agora é citiesLived_place e citiesLived.yearsLived agora é citiesLived_yearsLived.

Ainda que o BigQuery possa nivelar automaticamente campos aninhados, você talvez tenha que chamar FLATTEN explicitamente quando estiver lidando com mais de um campo repetido. Por exemplo, se você tentar executar uma consulta de SQL legado como esta:

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

O BigQuery retornará um erro semelhante a este:

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

Para consultar mais de um campo repetido, nivele um dos 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

Nessa consulta, é retornado:

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

A palavra-chave WITHIN é usada especificamente com funções de agregação para agregar filhos e campos repetidos em registros e campos aninhados. Ao especificar a palavra-chave WITHIN, especifique o escopo da agregação:

  • WITHIN RECORD: agrega dados nos valores repetidos no registro.
  • WITHIN node_namenode_name: os dados são agregados a valores repetidos dentro do nó especificado, em que um nó é o pai do campo na função de agregação.

Suponha que você queira encontrar o número de filhos de cada pessoa no exemplo anterior. Para isso, basta contar o número de children.name de cada registro:

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

Você recebe o seguinte resultado:

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

Para comparar, liste todos os nomes de filhos:

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

Isso corresponde aos resultados da consulta WITHIN RECORD. João da Silva tem dois filhos chamados Jane e João, Jane Austen tem dois filhos chamados Josh e Jim, Mike Jones tem três filhos chamados Earl, Sam e Kit, e Anna Karenina não tem filhos.

Agora, suponha que você queira descobrir quantas vezes uma pessoa morou em outros lugares. Use a cláusula WITHIN para agregar dados em um nó específico.

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

Nesta consulta:

  • Executa WITHIN RECORD em citiesLived.place e conta o número de lugares em que cada pessoa residiu
  • Executa WITHIN em citiesLived.yearsLived e conta o número de vezes que cada pessoa residiu em cada cidade, contando apenas citiesLived.

A agregação em escopo nos campos aninhados e repetidos é um dos recursos mais avançados do BigQuery. Com ele, geralmente é possível eliminar as junções dispendiosas nas consultas.