.webp)
Wenn Sie mit verschachtelten und wiederholten Spalten gearbeitet haben und diese als etwas schwierig empfunden haben, dann ist dieser Artikel genau das Richtige für Sie. In diesem Blog erklären wir die Einzelheiten solcher Felder in BigQuery. Wir werden ausführlich erläutern, warum sie im Data Warehousing bevorzugt werden und wie sie erstellt und abgefragt werden. Definieren wir zunächst, was verschachtelte und sich wiederholende Felder sind.
Was sind verschachtelte Daten?
In relationalen Datenbanken sind Daten so organisiert, dass Beziehungen zwischen wichtigen Datenpunkten leicht identifiziert werden können. Wenn wir beispielsweise das Kaufverhalten eines Kunden analysieren möchten, können wir Informationen aus drei Tabellen ableiten, die Informationen über den Kunden, Produkte und Bestellvorgänge enthalten. Ein einzelnes Produkt in der Produkttabelle kann mehrere Zeilen in der Bestelltabelle haben, was es schwierig macht, die Daten in einer einzigen Zeile zusammenzufassen. Der Prozess, relationale Datenbanken so zu organisieren, dass Datenredundanz minimiert und die Datenintegrität verbessert wird, wird als Normalisierung bezeichnet.
Beim Data Warehousing sind Datenanalysten und Techniker häufig daran interessiert, den Weg der Denormalisierung einzuschlagen und mehrere separate Tabellen ohne Datenredundanz zu einer großen Berichtstabelle zu kombinieren. BigQuery von Google bietet eine Möglichkeit, diese Prozesse mithilfe verschachtelter und wiederholter Felder abzuwickeln.
Schauen wir uns ein Beispiel für verschachtelte und wiederholte Daten an. Die Daten bestehen aus den folgenden Feldern:
- id
- Name
- Standort
- Ort.Stadt
- Ort. Land
So würde die JSON-Datendatei des obigen Beispiels aussehen.
{"id“ :"1", "name“ :"Xavier Williams“, „location“: ["city“ :"Berlin“, "country“ :"Deutschland "]}
{"id“ :"1", "name“ :"Crystal Wolf“, „location“: ["city“ :"München“, "country“ :"Deutschland "]}
Im Grunde handelt es sich bei den verschachtelten Daten um mehrere Felder an jedem Standort, und die wiederholten Daten sind die mehreren Standorte.
Warum verschachtelte und wiederholte Felder?
Schauen wir uns nun die verschachtelten und sich wiederholenden Felder in BigQuery genauer an und sehen wir uns an, warum dieses Design bei Datenanalysten und Ingenieuren so beliebt ist. Wir werden dies anhand eines E-Commerce-Beispiels veranschaulichen, das in Google Cloud ausgeführt wird. Dieses Unternehmen speichert Transaktionsbestellungen in BigQuery in der Tabelle „Bestellungen“.
- Jede Bestellung enthält Informationen über das Produkt und den Kunden, der die Bestellung aufgegeben hat
- Eine Bestellung kann ein oder mehrere Produkte enthalten
- Daten, die detaillierte Informationen über die Kunden liefern, die die jeweilige Bestellung aufgegeben haben
Wie können Sie als Datenanalyst oder Ingenieur diese unterschiedlichen Informationen effizient in Ihrem Data Warehouse speichern? Beachten Sie, dass das Unternehmen eine große Benutzerbasis unterstützen muss, die täglich große Datenmengen anfordert.
Wie in Abbildung 1 unten dargestellt, besteht eine Methode darin, dass wir jeden Fakt an einem Ort speichern können, wie es in relationalen Datenbankverwaltungssystemen üblich ist. Was sind also die Nachteile dieser Methode? Bei der Arbeit mit Kommunikationsprogrammen müssen unsere Daten von verschiedenen Standorten zur Analyse zusammengeführt werden. Das erfordert viele Verbindungen. Das Zusammenfügen, insbesondere bei sehr großen Tabellen, bedeutet einen hohen Analyseaufwand und ist daher teuer. Das liegt daran, dass DBMS auf Datensätzen basieren und sie jeden Datensatz vollständig öffnen und den Join-Schlüssel aus der Tabelle extrahieren müssen, mit der sie verknüpft sind.
Normalisiertes Schema
Ein anderer Ansatz besteht darin, dass wir den vollständig denormalisierten Weg gehen und alle Granularitätsebenen in einer großen Tabelle speichern können. Diese Option beschleunigt zwar das Lesen von Daten, hat jedoch mehrere Nachteile. In dem in Abbildung 2 gezeigten Schema wiederholen sich die Informationen, die jeder Bestellung gemeinsam sind. Das bedeutet, dass wir bei der Analyse von Daten auf verschiedenen Granularitätsebenen sehr vorsichtig sein müssen. Wir müssen aufpassen, dass wir bei der Berechnung nicht doppelt oder dreifach zählen.
Vollständig denormalisiertes Schema
Hier bieten sich verschachtelte und wiederholte Datenfelder an. Wir können für jede Bestellung eine Zeile und für detailliertere Daten wiederholte Werte hintereinander verwenden. Die folgende wiederholte und verschachtelte Tabelle zeigt beispielsweise deutlich, dass es nur drei Zeilen für drei eindeutige Bestell-IDs gibt.
Denormalisieren von Daten mit verschachtelten und wiederholten Feldern
Wenn Sie sich zum ersten Mal eine verschachtelte und wiederholte Tabelle wie die in Abbildung 3 ansehen, wundern Sie sich vielleicht über Spaltennamen wie „orders.price“ und fragen sich: „Was macht ein Punkt in diesen Spaltennamen?“ Gepunktete Spalten werden in SQL STRUCT oder strukturierte Datentypen genannt. Wir können sie uns als vorverkettete Tabellen innerhalb einer Tabelle vorstellen. Anstatt eine separate Tabelle für Produkte, Bestellungen und Kunden zu haben, können wir sie einfach alle auf eine große Tabelle legen.
STRUCTs im BigQuery-Schema
Durch die Verwendung von STRUCT vereinfachen wir die Abfrage, da die Daten konzeptionell vorverknüpft sind. Aufgrund des spaltenbasierten Charakters von BigQuery ist eine riesige Tabelle mit sich wiederholenden und verschachtelten Feldern die effizienteste und kostengünstigste Option. Wenn wir beispielsweise wissen möchten, wie viele Bestellungen insgesamt bestellt wurden, gibt BigQuery das gewünschte Ergebnis zurück, indem es auf die Spalte „order.quantity“ zugreift, ohne dass wir uns andere Spalten ansehen müssen. Die Möglichkeit, viele Daten an einem Ort zu haben und sie einfach analysieren zu können, ohne durch das Problem der Multigranularität behindert zu werden, ist für Analysten eine sehr wünschenswerte Funktion.
STRUCTs sind Container, in denen mehrere Felder und Datentypen verschachtelt werden können. ARRAYs können einer der Feldtypen innerhalb einer STRUCT sein. Beachten Sie den leeren Raum zwischen den Zeilen. Das liegt daran, dass einige Spalten eine tiefere Granularitätsebene aufweisen — was bedeutet, dass es mehrere doppelte Werte für diese Spalten gibt. Ein ARRAY ist der perfekte Datentyp, um diesen wiederholten Wert zu verarbeiten und diese Daten in einer einzigen Zeile zu speichern. In einem Schema werden ARRAY-Datentypen als sich wiederholend identifiziert, indem ihr Modus im Tabellenschema beobachtet wird.
Arrays im BigQuery-Schema
Eine Sache, die hier zu beachten ist, ist, dass die STRUCT- und ARRAY-Datentypen in SQL unabhängig voneinander sind. Eine Tabelle kann eine ARRAY-Spalte haben, die keine Beziehung zu einer Struktur hat. Es kann auch eine STRUCT ohne ARRAY-Felder geben. Wir wollen beide gleichzeitig verwenden, weil wir ihre einzigartigen Funktionen nutzen wollen. Während ein ARRAY es uns ermöglicht, tief in verschiedene Granularitätsebenen vorzudringen, hilft uns STRUCT dabei, weit zu gehen, indem verwandte Felder vorab miteinander verknüpft werden.
Wann sollten verschachtelte und wiederholte Felder verwendet werden?
Das traditionelle relationale Datenbanksystem ist vorteilhaft, wenn wir Tabellen haben, die kleiner als 10 Gigabyte sind. Daher ist es besser, die Tabellen normalisiert zu halten. Je kleiner die Größe, desto geringer ist der Einfluss von JOIN auf die Leistung. Mit zunehmender Datengröße nehmen jedoch auch die Auswirkungen eines JOIN auf die Leistung zu. In diesem Fall wäre eine Denormalisierung unserer Daten die wünschenswerte Option. Als Faustregel gilt, dass 10 Gigabyte als Schwellenwert verwendet werden.
Wie erstelle ich verschachtelte und wiederholte Felder?
Lassen Sie uns die STRUCT-Syntax ausprobieren und eine Tabelle erstellen.
WÄHLEN
STRUCT ('2254' ALS ID),
„Sonnenbrillen“ (AS (Name)) AS-Produkte
Da 'id' und 'name' eine Teilmenge von Produkten sind, haben wir eine Punktnotation.
Wenn Sie die folgende Abfrage ausführen, wird die wiederholte und verschachtelte Tabelle erstellt, die in Abbildung 3 dargestellt ist.
TABELLE ERSTELLEN ODER ERSETZEN
Datenbank.Tabelle AS (
WÄHLEN SIE 1 ALS order_id,
DATUM ('2022-02-08') ALS BESTELLDATUM,
[STRUCT (1 AS-Menge, 27,90 AS-Preis),
STRUCT (2 AS-Menge, 15,90 AS-Preis)] AS-Bestellungen,
[STRUCT ('101' ALS ID, 'Xavier Williams' ALS Name, 'xwilliams@example.com'AS-E-Mail,' Berlin 'ALS Stadt)] AS-Kunden,
[STRUCT ('2254' AS-ID, 'Sonnenbrille' ALS Name),
STRUCT ('4568' AS-ID, 'Schal' ALS Name)] AS-Produkte
ALLE VEREINIGEN
WÄHLEN SIE 2 ALS order_id,
DATUM ('2022-02-07') ALS BESTELLDATUM,
[STRUCT (2 AS-Menge, 27,9 AS-Preis),
STRUCT (1 AS-Menge, 120,75 AS-Preis),
STRUCT (2 AS-Menge, 29,90 AS-Preis)] AS-Bestellungen,
[STRUCT ('102' ALS ID, 'Crystal Wolf' ALS Name, 'wolfcrystal@example.com'AS-E-Mail,' München 'ALS Stadt)] AS-Kunden,
[STRUCT ('2254' AS-ID, 'Sonnenbrille' ALS Name),
STRUCT ('5487' AS-ID, 'Rucksack' ALS Name),
STRUCT ('1558' AS-ID, 'T-Shirt' ALS Name)]
ALLE VEREINIGEN
WÄHLEN SIE 3 ALS order_id,
DATUM ('2022-02-07') ALS BESTELLDATUM,
[STRUCT (1 AS-Menge, 120,75 AS-Preis)
] AS-Bestellungen,
[STRUCT ('103' ALS ID, 'Laura Brooks' ALS Name, 'laura11@example.com'AS (E-Mail),' Coburg '(AS Stadt)
] ALS Kunden
[STRUCT ('5487' AS-ID, 'Rucksack' AS Name)])
Wie fragt man verschachtelte und wiederholte Felder ab?
Das Abfragen verschachtelter und wiederholter Felder ist etwas schwierig. Versuchen wir, den Namen eines Produkts abzufragen, das von einem Kunden mit der ID 101 gekauft wurde.
WÄHLEN
products.name AS product_name
VON `data.table`
WO customers.id = „101"
Die obige Abfrage wird den folgenden Fehler auslösen:
„Auf die Feld-ID eines Werts mit dem Typ ARRAY <STRUCT><id STRING, name STRING, email STRING,... > bei [4:17] kann nicht zugegriffen werden“.
Da sich wiederholende Felder (Arrays) in einer einzigen Zeile gespeichert werden, müssen wir sie zuerst wieder in Zeilen aufteilen, damit wir sie normal abfragen können. Um Informationen aus einem wiederholten Feld in BigQuery zu extrahieren, müssen wir ein exotischeres Muster verwenden. Dies erfolgt normalerweise mithilfe der BigQuery-Funktion UNNEST, die ein Array von Werten aus einer Tabelle in Zeilen umwandelt. Diese Unnest-Funktion hilft dabei, die Daten zu glätten und den Zugriff auf verschachtelte Daten zu erleichtern.
Versuchen wir nun, den vorherigen Fehler mithilfe von UNNEST zu beheben.
WÄHLEN
p.name ALS Produktname
VON
`data.table` AS t1,
UNNEST (t1.products) ASP,
UNNEST (t1.customers) AS c
WO
c.id = „101"
Sehen wir uns weitere Beispiele an. Die folgende Abfrage extrahiert product_id und product_name aus den verschachtelten und wiederholten Daten.
WÄHLEN
bestellung_id,
p.id ALS product_id,
p.name ALS Produktname
VON `data.table` t1,
UNNEST (t1.products) als p
Lassen Sie uns in ähnlicher Weise extrahieren bestellen.preis und Bestellung. Menge aus dem Feld Bestellungen
WÄHLEN
bestellung_id,
p.id ALS product_id,
p.name ALS Produktname,
o.Preis,
o.Menge
VON `data.table` t1,
UNNEST (t1.products) ASP,
UNNEST (t1.orders) ALS
MIT t1 AS (
WÄHLEN
bestellung_id,
p.id ALS product_id,
p.name ALS Produktname,
o.price AS order_price,
o.quantity AS order_quantity
VON `data.table` t1,
UNNEST (t1.products) ASP,
UNNEST (t1.orders) ALS
)
WÄHLEN
t1. Bestell-ID,
t1. Produkt_ID,
t1. Produktname,
t1.order_price *t1.order_quantity AS-Umsatz
VON t1
Jetzt sind Sie mit der Funktion „Verschachtelte Daten“ und „Unnest“ vertraut. Lassen Sie uns das am meisten verkaufte Produkt aus unserer Datenbank berechnen
MIT t1 AS (
WÄHLEN
bestellung_id,
COUNT (o.quantity) AS QTY_ORDERED,
SUM ((o.price) * (o.quantity)) ALS total_revenue
VON
`datentabelle` t1,
UNNEST (Bestellungen) AB
GRUPPIEREN NACH
1
BESTELLEN NACH
2 DESK
)
WÄHLEN
t1.order_id,
t1.QTY_BESTELLT,
t1.total_revenue
VON t1
Das war's! Jetzt sind Sie an der Reihe, alles, was Sie gelernt haben, anzuwenden, um komplexe Daten abzufragen.
Hier sind ein paar Open-Source-Daten, die Sie in BigQuery üben können.
Fazit
In diesem Artikel wurden die Schritte vorgestellt, die für verschachtelte und wiederholte Felder in BigQuery erforderlich sind. Darüber hinaus haben Sie erfahren, was und wie Sie verschachtelte und wiederholte BigQuery-Felder erstellen und abfragen und wie Sie wiederholte BigQuery-Felder entschachteln.
Wenn Ihr Data Warehouse, Google BigQuery, live und aktiv ist, müssen Sie Daten von mehreren Plattformen extrahieren, um Ihre Analyse durchzuführen. Die Integration und Analyse Ihrer Daten aus einer Vielzahl von Datenquellen kann jedoch eine Herausforderung sein, und genau hier kommt Datadice ins Spiel.
Weitere Links
Folge uns auf LinkedIn für Einblicke in unsere tägliche Arbeit und wichtige Updates zu BigQuery, Data Studio und Marketing Analytics.
Abonniere unseren YouTube-Kanal für Diskussionen zu DWH, BigQuery, Looker Studio und Google Tag Manager.
Wenn Sie BigQuery von Grund auf lernen möchten, erhalten Sie Zugriff auf unsere kostenloser BigQuery-Kurs
Verbessern Sie Ihre Fähigkeiten mit Google Data Studio und BigQuery, indem Sie sich für unser Udemy-Kurs.
Benötigen Sie Hilfe bei der Einrichtung eines modernen, kostengünstigen Data Warehouse oder analytischen Dashboards? Senden Sie uns eine E-Mail an hello@datadice.io um einen Anruf zu vereinbaren.
