Utilizza campi nidificati e ripetuti

BigQuery può essere utilizzato con molti metodi di modellazione dei dati diversi e in genere offre prestazioni elevate con molte metodologie di modello dei dati. Per andare oltre ottimizzare un modello dei dati per migliorare le prestazioni. Un metodo che puoi prendere in considerazione è la denormalizzazione dei dati, il che significa aggiungere colonne di dati a una singola tabella per ridurre o rimuovere i join di tabelle.

Best practice: utilizza campi nidificati e ripetuti per denormalizzare i dati spazio di archiviazione e aumentare le prestazioni delle query.

La denormalizzazione è una strategia comune per aumentare le prestazioni di lettura per relazionali precedentemente normalizzati. Il metodo consigliato per per denormalizzare i dati in BigQuery è usare campi. È preferibile utilizzare questa strategia quando le relazioni sono gerarchiche ed è spesso oggetto di query insieme, ad esempio nelle relazioni padre-figlio.

I risparmi sullo spazio di archiviazione derivanti dall'uso di dati normalizzati hanno un impatto minore nella realtà sistemi diversi. Gli aumenti dei costi di archiviazione valgono per le prestazioni migliorate dell'utilizzo di dati denormalizzati. I join richiedono il coordinamento dei dati (comunicazione larghezza di banda larga). La denormalizzazione localizza i dati su singole slot, in modo che l'esecuzione possa essere eseguita in parallelo.

Per mantenere le relazioni durante la denormalizzazione dei dati, puoi utilizzare e campi ripetuti, invece di "appiattire" completamente i dati. Quando relazionale i dati sono completamente appiattiti, la comunicazione di rete (shuffling) può incidere influire sulle prestazioni delle query.

Ad esempio, denormalizzando uno schema degli ordini senza utilizzare elementi nidificati e ripetuti potrebbe richiedere di raggruppare i dati in base a un campo come order_id (in presenza di una relazione di tipo one-to-many). A causa dello shuffling richiesto, il raggruppamento dei dati è meno efficace della denormalizzazione dei dati utilizzando e campi nidificati e ripetuti.

In alcuni casi, denormalizzando i dati e usando asset nidificati e ripetuti campi non aumentano le prestazioni. Ad esempio, gli schemi a stella di solito sono ottimizzati per l'analisi e, di conseguenza, il rendimento potrebbe non sarà significativamente differente se si tenta di denormalizzare ulteriormente.

Utilizzare campi nidificati e ripetuti

BigQuery non richiede una denormalizzazione completamente piatta. Tu puoi utilizzare campi nidificati e ripetuti per mantenere le relazioni.

• Dati nidificati (STRUCT)

  • La nidificazione dei dati ti consente di rappresentare entità esterne in linea.
  • L'esecuzione di query sui dati nidificati utilizza "punto" per fare riferimento ai campi foglia, ovvero in modo simile alla sintassi con un join.
  • I dati nidificati sono rappresentati come Tipo di 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 (purché la relazione non è una cardinalità elevata).
  • In caso di dati ripetuti, lo shuffling non è necessario.
  • I dati ripetuti sono rappresentati come ARRAY. Puoi utilizzare un Funzione ARRAY in GoogleSQL quando esegui una query sui dati ripetuti.

• Dati nidificati e ripetuti (ARRAY di STRUCT s)

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

Per ulteriori informazioni, vedi Specifica 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

Per analizzare i dati di questa tabella, è necessario utilizzare un Clausola GROUP BY, simile alla seguente:

SELECT COUNT (Item_Name)
FROM Orders
GROUP BY Order_Id;

La clausola GROUP BY comporta un ulteriore overhead di calcolo, ma può essere evitato mediante la nidificazione di 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 Operatore UNNEST per unire i dati nidificati, come mostrato nella query seguente:

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, uno per ogni articolo venduto in quell'ordine, ottenendo così un una tabella grande e un'operazione GROUP BY costosa.

Esercizio

Puoi vedere la differenza di prestazioni delle query che utilizzano campi nidificati come rispetto a quelli che non lo fanno seguendo i passaggi descritti in questa sezione.

1. Crea una tabella basata su bigquery-public-data.stackoverflow.comments set di dati pubblico:

   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 questa query per vedere primo commento per ciascun 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 crea un campo comments utilizzando invece un tipo STRUCT per archiviare i dati post_id e creation_date di due campi individuali:

   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. Usando la tabella stackoverflow_nested, esegui questa query per visualizzare il primo commento per ciascun 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. Elimina stackoverflow e stackoverflow_nested quando hai finito.