Eseguire query su campi nidificati e ripetuti in SQL precedente
Questo documento descrive 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 sulla gestione di 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ò automaticamente
appiattire 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
appiattiscono implicitamente i dati sottoposti a query. Per le circostanze in cui i dati non vengono appiattiti in modo implicito,
ad esempio 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 ora è citiesLived_place e
citiesLived.yearsLived ora è 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 in modo specifico con le funzioni di aggregazione per aggregare i valori
dei campi secondari e ripetuti all'interno di record e campi nidificati. Quando specifichi la parola chiave WITHIN, devi specificare l'ambito su cui 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 | +---------------+---------------+
Ciò corrisponde ai risultati della query WITHIN RECORD: John Doe ha due figli
di nome Jane e John, Jane Austen ha due figli di nome Josh e Jim, Mike Jones ha tre
figli di nome Earl, Sam e Kit e Anna Karenina non ha figli.
Supponiamo ora di voler trovare il numero di volte in cui una persona ha vissuto in luoghi diversi.
Puoi utilizzare la clausola WITHIN per l'aggregazione in un nodo specifico:
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 una
WITHIN RECORDsucitiesLived.placee conta il numero di luoghi in cui ha vissuto ogni persona - Esegue un
WITHINsucitiesLived.yearsLivede conta il numero di volte in cui ogni persona ha vissuto in ogni città (conteggio solo sucitiesLived).
L'utilizzo dell'aggregazione con ambito su campi nidificati e ripetuti è una delle funzionalità più potenti di BigQuery, che spesso può eliminare join costosi nelle query.