Utilizzare campi nidificati e ripetuti
BigQuery può essere utilizzato con molti metodi di modellazione dei dati diversi e generalmente offre prestazioni elevate su numerose metodologie di modello dei dati. Per ottimizzare ulteriormente un modello dei dati per le prestazioni, puoi prendere in considerazione la denormalizzazione dei dati, che prevede l'aggiunta di colonne di dati a una singola tabella per ridurre o rimuovere i join delle tabelle.
Best practice: utilizza campi nidificati e ripetuti per denormalizzare l'archiviazione dei dati e aumentare le prestazioni delle query.
La denormalizzazione è una strategia comune per aumentare le prestazioni di lettura per i set di dati relazionali precedentemente normalizzati. Il modo consigliato per denormalizzare i dati in BigQuery è utilizzare campi nidificati e ripetuti. È preferibile utilizzare questa strategia quando le relazioni sono gerarchiche e spesso oggetto di query insieme, ad esempio nelle relazioni padre-figlio.
Il risparmio di spazio di archiviazione derivante dall'uso di dati normalizzati ha un impatto minore nei sistemi moderni. Gli aumenti dei costi di archiviazione valgono i miglioramenti delle prestazioni derivanti dall'utilizzo dei dati denormalizzati. I join richiedono il coordinamento dei dati (larghezza di banda della comunicazione). La denormalizzazione localizza i dati in singoli slot, in modo che l'esecuzione possa essere eseguita in parallelo.
Per mantenere le relazioni e allo stesso tempo denormalizzare i dati, puoi utilizzare campi nidificati e ripetuti invece di dividere completamente i dati. Quando i dati relazionali sono completamente suddivisi, la comunicazione di rete (shuffling) può influire negativamente sulle prestazioni delle query.
Ad esempio, se la denormalizzazione di uno schema degli ordini senza utilizzare campi nidificati e ripetuti potrebbe essere necessario raggruppare i dati in base a un campo come order_id
(in presenza di una relazione one-to-many). A causa dello shuffling coinvolto, il raggruppamento dei dati è meno efficace rispetto alla denormalizzazione dei dati tramite campi nidificati e ripetuti.
In alcuni casi, la denormalizzazione dei dati e l'utilizzo di campi nidificati e ripetuti non comporta un aumento delle prestazioni. Ad esempio, gli schemi a stella sono generalmente schemi ottimizzati per l'analisi e, di conseguenza, le prestazioni potrebbero non variare significativamente se si cerca di denormalizzare ulteriormente.
Utilizzare campi nidificati e ripetuti
BigQuery non richiede una denormalizzazione completamente piatta. Puoi utilizzare campi nidificati e ripetuti per mantenere le relazioni.
Dati nidificati (
STRUCT
)- La nidificazione dei dati ti consente di rappresentare entità straniere in linea.
- L'esecuzione di query sui dati nidificati utilizza la sintassi "punto" per fare riferimento ai campi foglia, che è simile alla sintassi che utilizza un join.
- I dati nidificati sono rappresentati come un tipo
STRUCT
in GoogleSQL.
Dati ripetuti (
ARRAY
)- La creazione di un campo di tipo
RECORD
con la modalità impostata suREPEATED
ti consente di preservare una relazione one-to-many in linea (a condizione che la relazione non sia ad alta cardinalità). - Con dati ripetuti, l'ordinamento casuale non è necessario.
- I dati ripetuti sono rappresentati come
ARRAY
. Puoi utilizzare una funzioneARRAY
in GoogleSQL per eseguire query sui dati ripetuti.
- La creazione di un campo di tipo
Dati nidificati e ripetuti (
ARRAY
diSTRUCT
)- Nidificazione e ripetizione si completano a vicenda.
- Ad esempio, in una tabella di record delle transazioni, potresti includere un array di
STRUCT
di elementi pubblicitari.
Per saperne di più, consulta Specificare le colonne nidificate e ripetute negli schemi delle tabelle.
Per ulteriori informazioni sulla denormalizzazione dei dati, consulta Denormalizzazione.
Esempio
Considera una tabella Orders
con una riga per ogni elemento pubblicitario venduto:
Order_Id | Item_Name |
---|---|
001 | A1 |
001 | B1 |
002 | A1 |
002 | C1 |
Se vuoi analizzare i dati di questa tabella, devi utilizzare una clausola GROUP BY
, simile alla seguente:
SELECT COUNT (Item_Name) FROM Orders GROUP BY Order_Id;
La clausola GROUP BY
comporta un overhead di calcolo aggiuntivo, che può essere evitato nidificando i dati ripetuti. Puoi evitare di utilizzare una clausola GROUP BY
creando una tabella con un ordine per riga, in cui gli elementi pubblicitari dell'ordine si trovano in un campo nidificato:
Order_Id | Item_Name |
---|---|
001 |
A1 B1 |
002 |
A1 C1 |
In BigQuery, in genere, specifichi uno schema nidificato come ARRAY
di STRUCT
oggetti. Puoi utilizzare l'operatore UNNEST
per suddividere i dati nidificati, come mostrato nella seguente query:
SELECT * FROM UNNEST( [ STRUCT('001' AS Order_Id, ['A1', 'B1'] AS Item_Name), STRUCT('002' AS Order_Id, ['A1', 'C1'] AS Item_Name) ] );
Questa query restituisce risultati simili ai seguenti:
Se questi dati non fossero nidificati, potresti avere più righe per ogni ordine, una per ogni articolo venduto in quell'ordine, il che si tradurrebbe in una tabella di grandi dimensioni e in un'operazione GROUP BY
costosa.
Attività fisica
Puoi vedere la differenza di prestazioni nelle query che utilizzano campi nidificati rispetto a quelle che non utilizzano campi nidificati seguendo la procedura descritta in questa sezione.
Crea una tabella basata sul set di dati pubblico
bigquery-public-data.stackoverflow.comments
:CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow` AS ( SELECT user_id, post_id, creation_date FROM `bigquery-public-data.stackoverflow.comments` );
Utilizzando la tabella
stackoverflow
, esegui la query seguente per visualizzare il commento più recente per ogni utente:SELECT user_id, ARRAY_AGG(STRUCT(post_id, creation_date AS earliest_comment) ORDER BY creation_date ASC LIMIT 1)[OFFSET(0)].* FROM `PROJECT.DATASET.stackoverflow` GROUP BY user_id ORDER BY user_id ASC;
L'esecuzione di questa query richiede circa 25 secondi ed elabora 1,88 GB di dati.
Crea una seconda tabella con dati identici che crei un campo
comments
utilizzando un tipoSTRUCT
per archiviare i datipost_id
ecreation_date
, anziché due campi singoli:CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow_nested` AS ( SELECT user_id, ARRAY_AGG(STRUCT(post_id, creation_date) ORDER BY creation_date ASC) AS comments FROM `bigquery-public-data.stackoverflow.comments` GROUP BY user_id );
Utilizzando la tabella
stackoverflow_nested
, esegui la query seguente per visualizzare il primo commento per ogni utente:SELECT user_id, (SELECT AS STRUCT post_id, creation_date as earliest_comment FROM UNNEST(comments) ORDER BY creation_date ASC LIMIT 1).* FROM `PROJECT.DATASET.stackoverflow_nested` ORDER BY user_id ASC;
L'esecuzione di questa query richiede circa 10 secondi ed elabora 1,28 GB di dati.
Elimina le tabelle
stackoverflow
estackoverflow_nested
quando hai finito di utilizzarle.