Specifica le colonne nidificate e ripetute negli schemi delle tabelle
Questa pagina descrive come definire uno schema di tabella con elementi nidificati e ripetuti in BigQuery. Per una panoramica degli schemi delle tabelle, consulta Specificare uno schema.
Definire colonne nidificate e ripetute
Per creare una colonna con dati nidificati, imposta il tipo di dati della colonna su
RECORD
nello schema. È possibile accedere a RECORD
come
Tipo di STRUCT
in GoogleSQL. Un STRUCT
è un contenitore di campi ordinati.
Per creare una colonna con dati ripetuti, imposta il valore
mode della colonna su REPEATED
nello schema.
È possibile accedere a un campo ripetuto come
ARRAY
digita
GoogleSQL.
Una colonna RECORD
può avere la modalità REPEATED
, rappresentata come un array di
Tipi di STRUCT
. Inoltre, un campo all'interno di un record può essere ripetuto,
rappresentato come un STRUCT
che contiene un valore ARRAY
. Un array non può contenere
direttamente da un altro array. Per ulteriori informazioni, consulta
Dichiarare un tipo ARRAY
.
Limitazioni
Gli schemi nidificati e ripetuti sono soggetti alle seguenti limitazioni:
- Uno schema non può contenere più di 15 livelli di tipi
RECORD
nidificati. - Le colonne di tipo
RECORD
possono contenere tipiRECORD
nidificati, detti anche record child. Il limite massimo di profondità nidificata è 15 livelli. Questo limite è indipendentemente dal fatto che i valoriRECORD
siano scalari o basati su array (ripetuto).
Il tipo di RECORD
non è compatibile con UNION
, INTERSECT
, EXCEPT DISTINCT
e SELECT DISTINCT
.
Schema di esempio
L'esempio seguente mostra dati nidificati e ripetuti di esempio. Questa tabella contiene informazioni sulle persone. È costituito dai seguenti campi:
id
first_name
last_name
dob
(data di nascita)addresses
(un campo nidificato e ripetuto)addresses.status
(attuale o precedente)addresses.address
addresses.city
addresses.state
addresses.zip
addresses.numberOfYears
(anni all'indirizzo)
Il file di dati JSON avrà il seguente aspetto. Nota che gli indirizzi
contiene un array di valori (indicato da [ ]
). I vari indirizzi
nell'array sono i dati ripetuti. I vari campi di ogni indirizzo
i dati nidificati.
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}
Lo schema per questa tabella è il seguente:
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
Specifica delle colonne nidificate e ripetute nell'esempio
Per creare una nuova tabella con le colonne nidificate e ripetute precedenti, selezionane una una delle seguenti opzioni:
Console
Specifica la colonna addresses
nidificata e ripetuta:
Nella console Google Cloud, apri la pagina BigQuery.
Nel riquadro Spazio di esplorazione, espandi il progetto e seleziona un set di dati.
Nel riquadro dei dettagli, fai clic su
Crea tabella.Nella pagina Crea tabella, specifica i seguenti dettagli:
- Per Origine, nel campo Crea tabella da, seleziona Vuota dalla tabella.
Nella sezione Destination (Destinazione), specifica i seguenti campi:
- In Set di dati, seleziona il set di dati in cui creare il tabella.
- In Tabella, inserisci il nome della tabella da creare.
Per Schema, fai clic su
Aggiungi campo e inserisci il seguente schema della tabella:- In Nome campo, inserisci
addresses
. - In Tipo, seleziona REGISTRA.
- In corrispondenza di Modalità, scegli RIPETUTA.
Specifica i seguenti campi per un campo nidificato:
- Nel campo Nome campo, inserisci
status
. - In Tipo, scegli STRING.
- In Modalità, lascia il valore impostato su NULLABLE.
Fai clic su
Aggiungi campo per aggiungere i seguenti campi:Nome campo Tipo Modalità address
STRING
NULLABLE
city
STRING
NULLABLE
state
STRING
NULLABLE
zip
STRING
NULLABLE
numberOfYears
STRING
NULLABLE
In alternativa, fai clic su Modifica come testo e specifica lo schema come un array JSON.
- Nel campo Nome campo, inserisci
- In Nome campo, inserisci
SQL
Utilizza la
CREATE TABLE
.
Specifica lo schema utilizzando il comando
colonna
:
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'editor query, inserisci la seguente istruzione:
CREATE TABLE IF NOT EXISTS mydataset.mytable ( id STRING, first_name STRING, last_name STRING, dob DATE, addresses ARRAY< STRUCT< status STRING, address STRING, city STRING, state STRING, zip STRING, numberOfYears STRING>> ) OPTIONS ( description = 'Example name and addresses table');
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
bq
Per specificare la colonna addresses
nidificata e ripetuta in un file di schema JSON:
usa un editor di testo per creare un nuovo file. Incolla lo schema di esempio
di cui sopra.
Dopo aver creato il file di schema JSON, puoi fornirlo tramite il a riga di comando bq. Per ulteriori informazioni, vedi Utilizzo di un file di schema JSON.
Vai
Prima di provare questo esempio, segui le istruzioni per la configurazione di Go nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Go BigQuery documentazione di riferimento.
Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.
Java
Prima di provare questo esempio, segui le istruzioni per la configurazione di Java nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Java BigQuery documentazione di riferimento.
Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.
Node.js
Prima di provare questo esempio, segui le istruzioni per la configurazione di Node.js nel Guida rapida di BigQuery con librerie client. Per ulteriori informazioni, consulta API Node.js BigQuery documentazione di riferimento.
Per autenticarti a BigQuery, configura le credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.
Python
Prima di provare questo esempio, segui le istruzioni di configurazione Python riportate nella guida rapida all'utilizzo delle librerie client di BigQuery. Per ulteriori informazioni, consulta API Python BigQuery documentazione di riferimento.
Per eseguire l'autenticazione su BigQuery, configura Credenziali predefinite dell'applicazione. Per ulteriori informazioni, vedi Configura l'autenticazione per le librerie client.
Inserisci i dati nelle colonne nidificate nell'esempio
Utilizza le seguenti query per inserire record di dati nidificati in tabelle con colonne di tipo di dati RECORD
.
Esempio 1
INSERT INTO mydataset.mytable (id, first_name, last_name, dob, addresses) values ("1","Johnny","Dawn","1969-01-22", ARRAY< STRUCT< status STRING, address STRING, city STRING, state STRING, zip STRING, numberOfYears STRING>> [("current","123 First Avenue","Seattle","WA","11111","1")])
Esempio 2
INSERT INTO mydataset.mytable (id, first_name, last_name, dob, addresses) values ("1","Johnny","Dawn","1969-01-22",[("current","123 First Avenue","Seattle","WA","11111","1")])
Eseguire query su colonne nidificate e ripetute
Per selezionare il valore di un ARRAY
in una posizione specifica, utilizza un operatore di indice آرایه.
Per accedere agli elementi in un STRUCT
, utilizza il
operatore punto.
Il seguente esempio seleziona il nome, il cognome e il primo indirizzo elencati nel campo addresses
:
SELECT first_name, last_name, addresses[offset(0)].address FROM mydataset.mytable;
Il risultato è il seguente:
+------------+-----------+------------------+ | first_name | last_name | address | +------------+-----------+------------------+ | John | Doe | 123 First Avenue | | Jane | Doe | 789 Any Avenue | +------------+-----------+------------------+
Per estrarre tutti gli elementi di un ARRAY
, utilizza la classe
Operatore UNNEST
con un CROSS JOIN
.
Il seguente esempio seleziona il nome, il cognome, l'indirizzo e lo stato per tutti gli indirizzi non situati a New York:
SELECT first_name, last_name, a.address, a.state FROM mydataset.mytable CROSS JOIN UNNEST(addresses) AS a WHERE a.state != 'NY';
Il risultato è il seguente:
+------------+-----------+------------------+-------+ | first_name | last_name | address | state | +------------+-----------+------------------+-------+ | John | Doe | 123 First Avenue | WA | | John | Doe | 456 Main Street | OR | | Jane | Doe | 321 Main Street | NJ | +------------+-----------+------------------+-------+
Modifica colonne nidificate e ripetute
Dopo aver aggiunto una colonna nidificata o una colonna nidificata e ripetuta al file definizione dello schema, puoi modificare la colonna come faresti con qualsiasi altro tipo di colonna. BigQuery supporta in modo nativo diverse modifiche allo schema, ad esempio: aggiungendo un nuovo campo nidificato a un record o riducendo la modalità di un campo nidificato. Per Per saperne di più, consulta la sezione Modifica degli schemi delle tabelle.
Quando utilizzare colonne nidificate e ripetute
BigQuery offre le prestazioni migliori quando i dati sono denormalizzati. Anziché preservarla uno schema relazionale come uno schema a stella o a fiocco di neve, denormalizza i dati e sfrutta le colonne nidificate e ripetute. Nidificati e le colonne ripetute possono mantenere relazioni senza un impatto sul rendimento di conservare uno schema relazionale (normalizzato).
Ad esempio, un database relazionale utilizzato per monitorare i libri della biblioteca probabilmente manterrebbe tutte le informazioni sull'autore in una tabella separata. Una chiave come author_id
sarebbe
utilizzato per collegare il libro agli autori.
In BigQuery, puoi preservare la relazione tra author senza creare una tabella author separata. Devi, invece, creare un autore e nidificare i campi al suo interno, come il nome, il cognome dell'autore nome, data di nascita e così via. Se un libro ha più autori, puoi ripetere la colonna degli autori nidificati.
Supponi di avere la seguente tabella mydataset.books
:
+------------------+------------+-----------+ | title | author_ids | num_pages | +------------------+------------+-----------+ | Example Book One | [123, 789] | 487 | | Example Book Two | [456] | 89 | +------------------+------------+-----------+
Hai anche la seguente tabella, mydataset.authors
, con le
informazioni per ogni ID autore:
+-----------+-------------+---------------+ | author_id | author_name | date_of_birth | +-----------+-------------+---------------+ | 123 | Alex | 01-01-1960 | | 456 | Rosario | 01-01-1970 | | 789 | Kim | 01-01-1980 | +-----------+-------------+---------------+
Se le tabelle sono di grandi dimensioni, l'unione regolare potrebbe richiedere molte risorse. In base alla tua situazione, potrebbe essere utile crea una singola tabella contenente tutte le informazioni:
CREATE TABLE mydataset.denormalized_books( title STRING, authors ARRAY<STRUCT<id INT64, name STRING, date_of_birth STRING>>, num_pages INT64) AS ( SELECT title, ARRAY_AGG(STRUCT(author_id, author_name, date_of_birth)) AS authors, ANY_VALUE(num_pages) FROM mydataset.books, UNNEST(author_ids) id JOIN mydataset.authors ON id = author_id GROUP BY title );
La tabella risultante ha il seguente aspetto:
+------------------+-------------------------------+-----------+ | title | authors | num_pages | +------------------+-------------------------------+-----------+ | Example Book One | [{123, Alex, 01-01-1960}, | 487 | | | {789, Kim, 01-01-1980}] | | | Example Book Two | [{456, Rosario, 01-01-1970}] | 89 | +------------------+-------------------------------+-----------+
BigQuery supporta il caricamento di dati nidificati e ripetuti dall'origine che supportano schemi basati su oggetti, come file JSON, file Avro File di esportazione Firestore e file di esportazione Datastore.
Deduplicare i record duplicati in una tabella
La seguente query utilizza row_number()
per identificare i record duplicati che hanno gli stessi valori per
last_name
e first_name
negli esempi utilizzati e li ordina per dob
:
CREATE OR REPLACE TABLE mydataset.mytable AS ( SELECT * except(row_num) FROM ( SELECT *, row_number() over (partition by last_name, first_name order by dob) row_num FROM mydataset.mytable) temp_table WHERE row_num=1 )
Sicurezza dei tavoli
Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alle tabelle.
Passaggi successivi
- Per inserire e aggiornare righe con colonne nidificate e ripetute, consulta Sintassi del linguaggio di manipolazione dei dati.