Utilizzare campi nidificati e ripetuti

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. È meglio utilizzare questa strategia quando le relazioni sono gerarchiche e spesso interrogate insieme, come nelle relazioni padre-figlio.

I risparmi di spazio di archiviazione derivanti dall'utilizzo dei dati normalizzati hanno un impatto minore sui sistemi moderni. L'aumento dei costi di archiviazione vale il miglioramento delle prestazioni dell'utilizzo dei dati denormalizzati. Le join richiedono il coordinamento dei dati (larghezza di banda di comunicazione). La denormalizzazione localizza i dati in singoli slot, in modo da poterli eseguire in parallelo.

Per mantenere le relazioni con la normalizzazione dei dati, puoi utilizzare campi nidificati e ripetuti anziché suddividere completamente i dati. Quando i dati relazionali vengono suddivisi completamente, la comunicazione di rete (in ordine casuale) può influire negativamente sulle prestazioni delle query.

Ad esempio, la denormalizzazione di uno schema di ordini senza campi nidificati e ripetuti potrebbe richiedere il raggruppamento dei dati in base a un campo come order_id (in presenza di una relazione one-to-many). A causa dell'effetto casuale, il raggruppamento dei dati è meno efficace rispetto alla normalizzazione dei dati utilizzando campi nidificati e ripetuti.

In alcune circostanze, la denormalizzazione dei dati e l'utilizzo di campi nidificati e ripetuti non comportano un aumento delle prestazioni. Evita la denormalizzazione in questi casi d'uso:

  • Hai uno schema a stelle con dimensioni che cambiano di frequente.
  • BigQuery integra un sistema di elaborazione di transazioni online (OLTP) con mutazione a livello di riga, ma non può sostituirlo.

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 le entità straniere in linea.
    • Per le query sui dati nidificati è necessario utilizzare la sintassi "dot&dott"; per fare riferimento ai campi foglia, simili alla sintassi con un join.
    • I dati nidificati sono rappresentati come un tipo STRUCT in SQL standard.
  • Dati ripetuti (ARRAY)

    • La creazione di un campo di tipo RECORD con la modalità impostata su REPEATED ti consente di preservare una relazione one-to-many incorporata (a condizione che la relazione non sia intensa).
    • Con i dati ripetuti, la riproduzione casuale non è necessaria.
    • I dati ripetuti sono rappresentati come ARRAY. Puoi utilizzare una funzione ARRAY nell'SQL standard quando esegui query sui dati ripetuti.
  • Dati nidificati e ripetuti (ARRAY su STRUCT)

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

Per scoprire di più, consulta Specificare colonne nidificate e ripetute negli schemi della tabella.

Per un esempio dettagliato di come denormalizzare i dati, consulta la sezione Denormalizzazione.

Esempio

Prendi in considerazione una tabella Orders con una riga per ogni elemento pubblicitario venduto:

ID ordine Nome articolo
001 A1
001 B1
002 A1
002 C1

Per 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 prevede un overhead di calcolo aggiuntivo, ma può essere evitato nidificando i dati ripetuti. Per evitare di utilizzare una clausola GROUP BY, crea una tabella con un ordine per riga, in cui gli elementi pubblicitari sono ordinati in un campo nidificato:

ID ordine Nome_articolo
001 A1

B1
002 A1

C1

In BigQuery, di solito specifichi uno schema nidificato come ARRAY di oggetti STRUCT. 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 produce risultati simili ai seguenti:

Query output con dati non nidificati

Se questi dati non sono nidificati, potrebbero avere diverse righe per ogni ordine, una per ogni articolo venduto nell'ordine, il che si traduce in una tabella di grandi dimensioni e in una costosa operazione di GROUP BY.

Attività fisica

Puoi seguire la differenza di rendimento nelle query che utilizzano campi nidificati rispetto a quelli che non lo fanno 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ù antico 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 singoli campi:

    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 di 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. Elimina le tabelle stackoverflow e stackoverflow_nested quando hai finito.