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 do SQL legado. A sintaxe de consulta preferida para o BigQuery é o SQL padrão. Para informações sobre como processar dados aninhados e repetidos no SQL padrão, consulte Guia de migração para o SQL padrão.

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. Já com as cláusulas ORDER BY e GROUP BY, os dados consultados são implicitamente nivelados. 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 retornará 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 | 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 |
+---------------+-----+--------+-------------------+------------------------+

Neste exemplo, citiesLived.place vira citiesLived_place e citiesLived.yearsLived vira 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: os dados são agregados a valores repetidos no registro.
  • WITHIN node_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 de consulta WITHIN RECORD. John Doe tem dois filhos: Jane e John. Jane Austen também tem dois filhos: Josh e Jim. Mike Jones tem três filhos: 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 | Russia            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
+---------------+---------------------+-------------------+-------------------------+

Nesta consulta:

  • é executada uma cláusula WITHIN RECORD em citiesLived.place, e o número de lugares em que cada pessoa morou é contado;
  • é executada uma cláusula WITHIN em citiesLived.yearsLived, e conta-se quantas vezes cada pessoa morou em cada cidade, considerando apenas os valores de 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.

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.