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 RECORDemcitiesLived.placee conta o número de lugares em que cada pessoa residiu - Executa
WITHINemcitiesLived.yearsLivede conta o número de vezes que cada pessoa residiu em cada cidade, contando apenascitiesLived.
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.