Esecuzione di query su campi nidificati e ripetuti nell'SQL precedente
Questo documento descrive come eseguire query su dati nidificati e ripetuti nella sintassi delle query SQL precedente. La sintassi delle query preferita per BigQuery è GoogleSQL. Per informazioni sulla gestione dei dati nidificati e ripetuti in GoogleSQL, consulta la guida alla migrazione per GoogleSQL.
BigQuery supporta il caricamento e l'exporting di dati nidificati e ripetuti sotto forma di file JSON e Avro. Per molte query SQL precedente, BigQuery è in grado di suddividere automaticamente i dati. Ad esempio, molte istruzioni SELECT possono recuperare campi nidificati o ripetuti mantenendo la struttura dei dati e le clausole WHERE possono filtrare i dati mantenendone la struttura. Al contrario, le clausole ORDER BY e GROUP BY
separano implicitamente i dati sottoposti a query. Nei casi in cui i dati non siano implicitamente suddivisi, ad esempio l'esecuzione di query su più campi ripetuti nell'SQL precedente, puoi eseguire query sui dati utilizzando le funzioni SQL FLATTEN e WITHIN.
FLATTEN
Quando esegui query su dati nidificati, BigQuery appiattisce automaticamente i dati della tabella. Diamo un'occhiata a uno schema di esempio per i dati delle persone:
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)
Nota che ci sono diversi campi ripetuti e nidificati. Se esegui una query SQL precedente come la seguente sulla tabella delle persone :
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery restituisce i dati con un output suddiviso:
+---------------+-----+--------+-------------------+------------------------+ | 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 | +---------------+-----+--------+-------------------+------------------------+
In questo esempio, citiesLived.place ora è citiesLived_place e
citiesLived.yearsLived è ora citiesLived_yearsLived.
Anche se BigQuery può suddividere automaticamente i campi nidificati, potrebbe essere necessario chiamare esplicitamente FLATTEN quando hai a che fare con più campi ripetuti. Ad esempio, se provi a eseguire una query SQL precedente come la seguente:
SELECT fullName, age FROM [dataset.tableId] WHERE (citiesLived.yearsLived > 1995 ) AND (children.age > 3)
BigQuery restituisce un errore simile al seguente:
Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived
Per eseguire una query su più campi ripetuti, devi uniformare uno dei campi:
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
che restituisce:
+------------+-----+--------+-------------------+ | 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 | +------------+-----+--------+-------------------+
Clausola WITHIN
La parola chiave WITHIN utilizza in particolare le funzioni di aggregazione per aggregare i dati nei campi secondari e ripetuti all'interno di record e campi nidificati. Quando specifichi la parola chiave WITHIN, devi specificare l'ambito in base al quale vuoi aggregare:
WITHIN RECORD: aggrega i dati nei valori ripetuti all'interno del record.WITHIN node_name: aggrega i dati nei valori ripetuti all'interno del nodo specificato, dove un nodo è un nodo padre del campo nella funzione di aggregazione.
Supponi di voler trovare il numero di figli di ogni persona nell'esempio precedente. A tale scopo, puoi contare il numero di children.name che ogni record ha:
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
Ottieni il seguente risultato:
+---------------+------------------+ | fullName | numberOfChildren | +---------------+------------------+ | John Doe | 2 | | Jane Austen | 2 | | Mike Jones | 3 | | Anna Karenina | 0 | +---------------+------------------+
Per fare un confronto, prova a elencare tutti i nomi dei bambini:
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 | +---------------+---------------+
Questo corrisponde ai nostri risultati della query WITHIN RECORD. John Doe ha due figli che si chiamano Jane e John, Jane Austen ha due figli che si chiamano Josh e Jim, Mike Jones ha tre figli che si chiamano Conte, Sam e Kit e Anna Karenina non ha figli.
Ora, supponi di voler conoscere il numero di volte in cui una persona ha vissuto in luoghi diversi.
Puoi utilizzare la clausola WITHIN per aggregare i dati in un determinato nodo:
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 | +---------------+---------------------+-------------------+-------------------------+
Questa query svolge le seguenti operazioni:
- Esegue una
WITHIN RECORDsucitiesLived.placee conta il numero di luoghi in cui ha vissuto ogni persona - Esegue
WITHINil giornocitiesLived.yearsLivede conteggia il numero di volte in cui ogni persona ha vissuto in ciascuna città (contando solocitiesLived).
L'utilizzo dell'aggregazione mirata su campi nidificati e ripetuti è una delle funzionalità più potenti di BigQuery, che spesso consente di eliminare costosi join nelle query.