Utilizzare campi nidificati e ripetuti

BigQuery può essere utilizzato con vari metodi di modellazione dei dati e in generale offre prestazioni elevate su numerose metodologie per i modello dei dati. Per ottimizzare ulteriormente un modello dei dati per le prestazioni, un metodo che potresti prendere in considerazione è la denormalizzazione dei dati, che prevede l'aggiunta di colonne di dati a una singola tabella per ridurre o rimuovere le unioni 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 dello spazio di archiviazione derivante dall'uso di dati normalizzati ha meno effetti nei sistemi moderni. Gli aumenti dei costi di archiviazione valgono i miglioramenti in termini di prestazioni derivanti dall'utilizzo dei dati denormalizzati. I join richiedono il coordinamento dei dati (larghezza di banda di comunicazione). La denormalizzazione localizza i dati per singoli slot, in modo che l'esecuzione possa essere eseguita in parallelo.

Per mantenere le relazioni e 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, la denormalizzazione di uno schema di ordini senza l'utilizzo di campi nidificati e ripetuti potrebbe richiedere di raggruppare i dati in base a un campo come order_id (in caso di relazione one-to-many). A causa del conseguente shuffling, il raggruppamento dei dati è meno efficace rispetto alla denormalizzazione dei dati utilizzando 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 in genere ottimizzati per l'analisi e, di conseguenza, le prestazioni potrebbero non essere molto diverse 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 di nidificazione (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 con l'uso di 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 su REPEATED consente di mantenere una relazione one-to-many in linea (a condizione che la relazione non sia a cardinalità elevata).
  • In caso di dati ripetuti, l'ordinamento casuale non è necessario.
  • I dati ripetuti sono rappresentati come ARRAY. Puoi utilizzare una funzione ARRAY in GoogleSQL quando esegui query sui dati ripetuti.

• Dati nidificati e ripetuti (ARRAY di STRUCT)

  • Nidificazione e ripetizione si completano a vicenda.
  • Ad esempio, in una tabella di record delle transazioni, potresti includere un array di STRUCT elementi pubblicitari.

Per maggiori informazioni, 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. Utilizza l'operatore UNNEST per unire 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 diverse 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.

Esercizio

Puoi notare la differenza di prestazioni nelle query che utilizzano campi nidificati rispetto a quelle che non lo utilizzano seguendo i passaggi descritti in questa sezione.

1. 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`
   );
   

2. Utilizzando la tabella stackoverflow, esegui la seguente query 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.

3. Crea una seconda tabella con dati identici che crei un campo comments utilizzando un tipo STRUCT per archiviare i dati post_id e creation_date, invece di 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
   );
   

4. Utilizzando la tabella stackoverflow_nested, esegui la seguente query 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.

5. Al termine, elimina le tabelle stackoverflow e stackoverflow_nested.