Utilizzare campi nidificati e ripetuti

BigQuery può essere utilizzato con molti metodi di modellazione dei dati diversi e in genere offre prestazioni elevate in molte metodologie dimodello dei datii. Per ottimizzare ulteriormente un modello dei dati per le prestazioni, un metodo che potresti prendere in considerazione è la denormalizzazione dei dati, che consiste nell'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 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'utilizzo di dati normalizzati ha un effetto minore nei sistemi moderni. L'aumento dei costi di archiviazione vale il miglioramento delle prestazioni derivante dall'utilizzo di 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 durante la denormalizzazione dei dati, puoi utilizzare campi nidificati e ripetuti anziché appiattire completamente i dati. Quando i dati relazionali vengono completamente appiattiti, la comunicazione di rete (shuffling) può influire negativamente sul rendimento delle query.

Ad esempio, la denormalizzazione di uno schema degli ordini senza utilizzare campi nidificati e ripetuti potrebbe richiedere di raggruppare i dati in base a un campo come order_id (quando esiste una relazione uno-a-molti). A causa del data shuffling coinvolto, il raggruppamento dei dati è meno efficace della denormalizzazione dei dati mediante l'utilizzo di campi nidificati e ripetuti.

In alcune circostanze, la denormalizzazione dei dati e l'utilizzo di campi nidificati e ripetuti non comportano un aumento del rendimento. Ad esempio, gli schemi a stella sono in genere schemi ottimizzati per l'analisi e, di conseguenza, il rendimento potrebbe non essere significativamente diverso se tenti di denormalizzare ulteriormente.

Utilizzo di campi nidificati e ripetuti

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

  • Dati di nidificazione (STRUCT)

    • L'incorporamento dei dati consente di rappresentare le entità esterne in linea.
    • L'esecuzione di query sui dati nidificati utilizza la sintassi "." per fare riferimento ai campi foglia, che è simile alla sintassi che utilizza un'unione.
    • 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 conservare una relazione one-to-many in linea (a condizione che la relazione non abbia una cardinalità elevata).
    • Con i dati ripetuti, non è necessario mescolarli.
    • I dati ripetuti sono rappresentati come un ARRAY. Puoi utilizzare una funzione ARRAY in GoogleSQL quando esegui query sui dati ripetuti.

  • Dati nidificati e ripetuti (ARRAY di STRUCT)

    • L'annidamento e la ripetizione si completano a vicenda.
    • Ad esempio, in una tabella di record di transazioni, potresti includere un array di STRUCT degli elementi pubblicitari.

Per ulteriori informazioni, vedi Specificare colonne nidificate e ripetute negli schemi delle tabelle.

Per ulteriori informazioni sulla denormalizzazione dei dati, vedi Denormalizzazione.

Esempio

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

Order_Id Item_Name
001 A1
001 B1
002 A1
002 C1

Se volessi analizzare i dati di questa tabella, dovresti utilizzare una clausola GROUP BY, simile alla seguente:

SELECT COUNT (Item_Name)
FROM Orders
GROUP BY Order_Id;

La clausola GROUP BY comporta un sovraccarico di calcolo aggiuntivo, ma questo 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 oggetti STRUCT. Utilizzi l'operatore UNNEST per appiattire 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 produce risultati simili ai seguenti:

Output della query con dati non nidificati

Se questi dati non fossero nidificati, potresti avere diverse righe per ogni ordine, una per ogni articolo venduto in quell'ordine, il che comporterebbe una tabella di grandi dimensioni e un'operazione GROUP BY costosa.

Esercizio

Per visualizzare la differenza di rendimento nelle query che utilizzano campi nidificati rispetto a quelle che non li utilizzano, segui 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 primo commento di 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 crea un campo comments utilizzando un tipo STRUCT per archiviare i dati post_id e creation_date, anziché 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. 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 non ti servono più.