Esecuzione di query su campi nidificati e ripetuti in 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 su per la gestione di dati nidificati e ripetuti in GoogleSQL, consulta Guida alla migrazione di GoogleSQL.
BigQuery supporta il caricamento
e l'esportazione di dati nidificati e ripetuti
di file JSON e Avro. Per molte query SQL precedente, BigQuery può automaticamente
appiattire i dati. Ad esempio, molte istruzioni SELECT possono recuperare nidificate o ripetute
mantenendo la struttura dei dati e le clausole WHERE possono filtrare i dati
mantenendone comunque la struttura. Al contrario, ORDER BY e GROUP BY
di Google Cloud consente di
appiattire implicitamente i dati sottoposti a query. Nei casi in cui i dati non sono implicitamente appiattiti,
ad esempio eseguire query su più campi ripetuti in SQL precedente, puoi eseguire query sui dati utilizzando
Funzioni SQL di FLATTEN e WITHIN.
PIATTAFORMA
Quando esegui query su dati nidificati, BigQuery appiattisce automaticamente i dati della tabella per te. Ad esempio, diamo un'occhiata a uno schema di esempio per i dati personali:
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 sono presenti diversi campi ripetuti e nidificati. Se esegui una query SQL precedente come seguenti rispetto alla tabella delle persone :
SELECT fullName AS name, age, gender, citiesLived.place, citiesLived.yearsLived FROM [dataset.tableId]
BigQuery restituisce i dati con un output bidimensionale:
+---------------+-----+--------+-------------------+------------------------+ | 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 adesso è citiesLived_yearsLived.
Sebbene BigQuery possa appiattire automaticamente i campi nidificati, potresti dover
chiamare esplicitamente FLATTEN quando si gestiscono 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 a questo:
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 utilizza nello specifico funzioni di aggregazione per l'aggregazione
secondari e ripetuti all'interno di record e campi nidificati. Se specifichi il valore WITHIN
una parola chiave, devi specificare l'ambito per il quale desideri aggregare:
WITHIN RECORD: aggrega i dati nei valori ripetuti all'interno di il record.WITHIN node_name: aggrega i dati nei valori ripetuti all'interno del nodo specificato, dove un nodo è un nodo padre del campo di aggregazione.
Supponiamo di voler trovare il numero di figli di ogni persona nell'esempio precedente. A In questo caso, puoi contare il numero di bambini.nome di ciascun record:
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 un confronto, prova a elencare tutti i nomi di 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. Mario Rossi ha due figli
di nome Jane e John, Jane Austen ha due figli di nome Josh e Jim, Mike Jones ha tre
bambini di nome Earl, Sam e Kit, e Anna Karenina non ha figli.
Supponiamo ora di voler trovare quante volte una persona ha vissuto in luoghi diversi.
Puoi utilizzare la clausola WITHIN per eseguire l'aggregazione su 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à (contando fino acitiesLived).
L'utilizzo dell'aggregazione con ambito in campi nidificati e ripetuti è una delle opzioni molto potenti, che spesso possono eliminare i join costosi nelle query.