Consultar campos aninhados e repetidos no SQL antigo
Este documento detalha como consultar dados aninhados e repetidos na sintaxe de consulta SQL antiga. A sintaxe de consulta preferida para o BigQuery é o GoogleSQL. Para obter informações sobre como processar dados aninhados e repetidos no GoogleSQL, consulte o guia de migração do GoogleSQL.
O BigQuery suporta o carregamento
e a exportação de dados aninhados e repetidos no formato de ficheiros JSON e Avro. Para muitas consultas SQL antigas, o BigQuery pode automaticamente
reduzir os dados. Por exemplo, muitas declarações SELECT podem obter campos aninhados ou repetidos
mantendo a estrutura dos dados, e as cláusulas WHERE podem filtrar dados
mantendo a respetiva estrutura. Por outro lado, as cláusulas ORDER BY e GROUP BY
reduzem implicitamente os dados consultados. Em circunstâncias em que os dados não são implicitamente reduzidos,
como a consulta de vários campos repetidos no SQL antigo, pode consultar os seus dados através das funções SQL FLATTEN e WITHIN.
FLATTEN
Quando consulta dados aninhados, o BigQuery simplifica automaticamente os dados da tabela. Por exemplo, vamos analisar um esquema de amostra para dados de pessoas:
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)Repare que existem vários campos repetidos e aninhados. Se executar uma consulta SQL antiga como a seguinte na tabela person :
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
O BigQuery devolve os seus dados com um resultado reduzido:
+---------------+-----+--------+-------------------+------------------------+ | 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.
Embora o BigQuery possa nivelar automaticamente os campos aninhados, pode ter de chamar explicitamente FLATTEN quando lida com mais do que um campo repetido. Por exemplo, se tentar executar uma consulta SQL antiga como a seguinte:
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
O BigQuery devolve um erro semelhante ao seguinte:
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
Para consultar mais do que um campo repetido, tem de nivelar 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
Que devolve:
+------------+-----+--------+-------------------+ | 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 funciona especificamente com funções de agregação para agregar dados em
campos secundários e repetidos em registos e campos aninhados. Quando especifica a palavra-chave WITHIN, tem de especificar o âmbito no qual quer agregar:
WITHIN RECORD: agrega dados nos valores repetidos no registo.WITHIN node_name: agrega dados nos valores repetidos no nó especificado, em que um nó é um nó principal do campo na função de agregação.
Suponhamos que quer saber o número de filhos que cada pessoa no exemplo anterior tem. Para tal, pode contar o número de children.name que cada registo tem:
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
Recebe o seguinte resultado:
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
Para comparar, experimente listar os nomes de todas as crianças:
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 | +---------------+---------------+
Isto corresponde aos resultados da nossa consulta WITHIN RECORD; o João Silva tem dois filhos chamados Joana e João, a Joana Silva tem dois filhos chamados João e João, o Miguel Silva tem três filhos chamados João, João e João, e a Ana Silva não tem filhos.
Agora, suponhamos que quer saber o número de vezes que uma pessoa viveu em locais diferentes.
Pode usar a cláusula WITHIN para agregar num 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 | +---------------+---------------------+-------------------+-------------------------+
Esta consulta faz o seguinte:
- Executa uma
WITHIN RECORDemcitiesLived.placee conta o número de locais onde cada pessoa viveu - Executa uma
WITHINemcitiesLived.yearsLivede conta o número de vezes que cada pessoa viveu em cada cidade (contando apenas emcitiesLived).
A utilização da agregação com âmbito sobre campos aninhados e repetidos é uma das funcionalidades mais poderosas do BigQuery, que pode eliminar frequentemente junções dispendiosas em consultas.