Verschachtelte und wiederkehrende Felder verwenden

BigQuery kann mit vielen verschiedenen Methoden zur Datenmodellierung verwendet werden und bietet in der Regel eine hohe Leistung bei vielen Datenmodellmethoden. Um ein Datenmodell weiter zu optimieren, können Sie die Datendenormalisierung in Betracht ziehen. Dabei werden einer einzelnen Tabelle Datenspalten hinzugefügt, um Tabellenjoins zu reduzieren oder zu entfernen.

Best Practice: Verwenden Sie verschachtelte und wiederkehrende Felder, um den Datenspeicher zu denormalisieren und die Abfrageleistung zu erhöhen.

Die Denormalisierung ist eine gängige Strategie zur Steigerung der Leseleistung bei relationalen Datasets, die zuvor normalisiert wurden. Die empfohlene Methode zur Denormalisierung von Daten in BigQuery ist die Verwendung verschachtelter und wiederkehrender Felder. Diese Strategie eignet sich am besten, wenn die Beziehungen hierarchisch sind und häufig zusammen abgefragt werden, beispielsweise in Beziehungen zwischen über- und untergeordneten Elementen.

Die Speicherplatzeinsparungen, die durch die Verwendung normalisierter Daten erzielt werden, sind in modernen Systemen nebensächlich. Die zusätzlichen Speicherkosten werden durch die Leistungssteigerungen, die durch Verwendung denormalisierter Daten erzielt werden, amortisiert. Joins erfordern eine Datenkoordinierung und damit Kommunikationsbandbreite. Durch die Denormalisierung werden die Daten in individuellen Slots platziert, was eine parallele Ausführung ermöglicht.

Um bei der Denormalisierung Ihrer Daten Beziehungen aufrechtzuerhalten, können Sie verschachtelte oder wiederkehrende Felder verwenden, anstatt Ihre Daten zu vereinfachen. Wenn Sie relationale Daten vollständig zusammenfassen, kann die nach dem Zufallsprinzip erfolgende Netzwerkkommunikation die Abfrageleistung beeinträchtigen.

Nehmen wir an, Sie denormalisieren ein Auftragsschema, ohne verschachtelte und wiederkehrende Felder zu verwenden. In diesem Fall ist möglicherweise eine Gruppierung nach einem Feld wie order_id erforderlich – wenn eine 1:n-Beziehung vorliegt. Aufgrund der involvierten Netzwerkkommunikation nach dem Zufallsprinzip ist das Gruppieren der Daten weniger effektiv als deren Denormalisierung mit verschachtelten und wiederkehrenden Feldern.

Unter bestimmten Umständen führt die Denormalisierung Ihrer Daten und die Verwendung verschachtelter und wiederkehrender Felder nicht zu einer Leistungsverbesserung. Beispielsweise sind Sternschemas in der Regel optimierte Schemas für Analysen. Daher kann die Leistung nicht erheblich abweichen, wenn Sie versuchen, weiter zu denormalisieren.

Verschachtelte und wiederkehrende Felder verwenden

BigQuery erfordert keine vollständig abgeflachte Denormalisierung. Sie können Beziehungen durch verschachtelte und wiederkehrende Felder aufrechterhalten.

• Daten verschachteln (STRUCT)

  • Durch das Verschachteln von Daten können Sie externe Elemente inline darstellen.
  • Zum Abfragen verschachtelter Daten wird die "Dot"-Syntax verwendet, um ähnlich der Join-Syntax Blattfelder zu referenzieren.
  • Verschachtelte Daten werden in GoogleSQL als STRUCT-Typ dargestellt.

• Wiederkehrende Daten (ARRAY)

  • Wenn Sie ein Feld vom Typ RECORD mit dem Modus REPEATED erstellen, können Sie eine 1:n-Beziehung beibehalten, sofern die Beziehung keine hohe Kardinalität hat.
  • Bei wiederkehrenden Daten ist die Anwendung des Zufallsprinzips nicht erforderlich.
  • Wiederkehrende Daten werden als ARRAY dargestellt. Sie können in GoogleSQL für das Abfragen der wiederkehrenden Daten eine ARRAY-Funktion verwenden.

• Verschachtelte und wiederkehrende Daten (STRUCT-ARRAY)

  • Verschachtelung und Wiederholung ergänzen sich gegenseitig.
  • Sie können in einer Tabelle mit Transaktionsdatensätzen beispielsweise ein Array von Positions-STRUCTs einbeziehen.

Weitere Informationen finden Sie unter Verschachtelte und wiederkehrende Spalten in Tabellenschemas angeben.

Weitere Informationen zum Denormalisieren von Daten finden Sie unter Denormalisierung.

Beispiel

Betrachten Sie eine Orders-Tabelle mit einer Zeile für jede verkaufte Position:

Order_Id	Item_Name
001	A1
001	B1
002	A1
002	C1

Wenn Sie Daten aus dieser Tabelle analysieren möchten, müssen Sie eine GROUP BY-Klausel verwenden, die in etwa so aussieht:

SELECT COUNT (Item_Name)
FROM Orders
GROUP BY Order_Id;

Die GROUP BY-Klausel erfordert zusätzlichen Rechenaufwand. Dies kann jedoch durch das Verschachteln wiederkehrender Daten vermieden werden. Sie können die Verwendung einer GROUP BY-Klausel vermeiden, wenn Sie eine Tabelle mit einem Auftrag pro Zeile erstellen, in der die Auftragspositionen in einem geschachtelten Feld stehen:

Order_Id	Item_Name
001	A1 B1
002	A1 C1

In BigQuery geben Sie in der Regel ein verschachteltes Schema als ARRAY von STRUCT-Objekten an. Sie verwenden den Operator UNNEST, um die verschachtelten Daten zu vereinfachen, wie in der folgenden Abfrage gezeigt:

SELECT *
FROM UNNEST(
  [
    STRUCT('001' AS Order_Id, ['A1', 'B1'] AS Item_Name),
    STRUCT('002' AS Order_Id, ['A1', 'C1'] AS Item_Name)
  ]
);

Diese Abfrage liefert Ergebnisse wie die folgenden:

Wenn die Daten nicht verschachtelt sind, könnten Sie mehrere Zeilen für jede Bestellung haben, eine für jeden in dieser Reihenfolge verkauften Artikel. Dies würde zu einer großen Tabelle und einem teuren GROUP BY-Vorgang führen.

Übung

Sie können den Leistungsunterschied zwischen Abfragen, die verschachtelte Felder verwenden, und solchen, die dies nicht tun, anhand der Schritte in diesem Abschnitt erkennen.

1. Erstellen Sie eine Tabelle mit dem öffentlichen Dataset 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. Führen Sie mithilfe der Tabelle stackoverflow die folgende Abfrage aus, um den frühesten Kommentar für jeden Nutzer anzuzeigen:

   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;

   Die Abfrage dauert ca. 25 Sekunden. Dabei werden 1,88 GB Daten verarbeitet.

3. Erstellen Sie eine zweite Tabelle mit identischen Daten, die das Feld comments mit dem Typ STRUCT zum Speichern der Daten post_id und creation_date erstellt, anstatt zwei einzelne Felder zu verwenden:

   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. Führen Sie in der Tabelle stackoverflow_nested die folgende Abfrage aus, um den ersten Kommentar für jeden Nutzer anzusehen:

   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;

   Diese Abfrage dauert ca.10 Sekunden und verarbeitet 1,28 GB Daten.

5. Löschen Sie die Tabellen stackoverflow und stackoverflow_nested, wenn Sie mit ihnen fertig sind.