Eseguire query su campi nidificati e ripetuti in SQL precedente
Questo documento illustra in dettaglio come eseguire query sui dati nidificati e ripetuti nella sintassi delle query SQL precedente. La sintassi di query preferita per BigQuery è GoogleSQL. Per informazioni su come gestire i dati nidificati e ripetuti in GoogleSQL, consulta la guida alla migrazione di GoogleSQL.
BigQuery supporta il caricamento
e l'esportazione di dati nidificati e ripetuti sotto forma di
file JSON e Avro. Per molte query SQL precedente, BigQuery può appianare automaticamente i dati. Ad esempio, molte istruzioni SELECT possono recuperare campi nidificati o ripetuti mantenendo la struttura dei dati, mentre le clausole WHERE possono filtrare i dati mantenendone la struttura. Al contrario, le clausole ORDER BY e GROUP BY
appiattiscono implicitamente i dati sottoposti a query. Per le circostanze in cui i dati non sono appiattiti implicitamente, come l'esecuzione di query su più campi ripetuti in SQL precedente, puoi eseguire query sui dati utilizzando le funzioni SQL FLATTEN e WITHIN.
FLATTEN
Quando esegui query sui dati nidificati, BigQuery appiattisce automaticamente i dati della tabella. Ad esempio, 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 person :
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery restituisce i dati con un output appiattito:
+---------------+-----+--------+-------------------+------------------------+ | 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 diventa citiesLived_place e
citiesLived.yearsLived diventa citiesLived_yearsLived.
Sebbene BigQuery possa appiattire automaticamente i campi nidificati, potrebbe essere necessario chiamare esplicitamente FLATTEN quando si ha a che fare con più di un campo ripetuto. 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 query su più di un campo ripetuto, devi appiattire 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 funziona specificamente con le funzioni di aggregazione per eseguire l'aggregazione tra elementi secondari e campi ripetuti all'interno di record e campi nidificati. Quando specifichi la parola chiave WITHIN, devi specificare l'ambito in base al quale vuoi eseguire l'aggregazione:
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 principale del campo nella funzione di aggregazione.
Supponiamo di voler trovare il numero di figli di ciascuna persona nell'esempio precedente. Per farlo, puoi contare il numero di children.name di ogni record:
SELECT fullName, COUNT(children.name) WITHIN RECORD AS numberOfChildren FROM [dataset.tableId];
Viene visualizzato 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 risultati della nostra query WITHIN RECORD: Giovanni Rossi ha due figli, Jane e Giovanni, Jane Austen ha due figli, Josh e Jim, Mike Jones ha tre figli, Earl, Sam e Kit, mentre Anna Karenina non ha figli.
Supponiamo ora che tu voglia trovare il numero di volte in cui una persona ha vissuto in luoghi diversi.
Puoi utilizzare la clausola WITHIN per eseguire l'aggregazione 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 esegue le seguenti operazioni:
- Esegue un
WITHIN RECORDsucitiesLived.placee conteggia il numero di luoghi in cui ha vissuto ogni persona - Esegue un
WITHINsucitiesLived.yearsLivede conteggia il numero di volte in cui ogni persona ha vissuto in ogni città (conteggio solo incitiesLived).
L'utilizzo dell'aggregazione basata sugli ambiti per i campi nidificati e ripetuti è una delle funzionalità più potenti di BigQuery, che spesso può eliminare le unioni costose nelle query.