Google BigQuery ist eine beliebte Cloud-Datenbank, die von Unternehmen auf der ganzen Welt verwendet wird. Dies ist besonders praktisch für die Arbeit mit Google Analytics-Rohdaten: In GA 360 wird die Integration in BigQuery mit wenigen Klicks konfiguriert, und für die kostenlose Version gibt es Skripte und Module von Drittanbietern.
In den Rohdaten von Google Analytics entspricht jeder Datensatz (jede Zeile) einer Sitzung. In einem solchen Eintrag befinden sich verschachtelte Felder, die den Treffern der Sitzung entsprechen:
Oft ist eine solche verschachtelte Datenstruktur für Benutzer verwirrend, die nicht verstehen, wie sie damit arbeiten und solche Daten entladen sollen.
Am Beispiel der benutzerdefinierten Parameter von Google Analytics werde ich versuchen, "an den Fingern" zu erklären, wie eingebettete Daten in Google BigQuery gespeichert werden und wie sie hochgeladen werden können.
Die angegebenen Abfragecodes funktionieren. Sie können zur Lösung von Problemen verwendet werden, indem ihre Tabellennamen und die erforderlichen Indexnummern für benutzerdefinierte Dimensionen ersetzt werden.
• Entladen von Leitungen• Entladen unter Beibehaltung der Verschachtelungsstruktur• Beispiel für das Ersetzen von BenutzerparameterwertenDie Grundlagen
BigQuery unterstützt zwei SQL-Dialekte: Legacy und Standard. Google empfiehlt die Verwendung des neueren SQL-Standards, auf den Abfragen zum Entladen geschrieben werden.
Jeder, der zumindest ein wenig mit SQL gearbeitet hat, kennt das Standardabfragekonstrukt:
SELECT * * FROM * * WHERE * *
Dieser Entwurf funktioniert, wenn die Tabellenstruktur einfach ist und keine anderen Felder in den Zellen verschachtelt sind:
Wir betrachten Tabellen mit verschachtelten Feldern. Die Struktur einer solchen Tabelle (z. B. Google Analytics-Benutzerparameter):
Benutzerdefinierte GA-Optionen in BQIn Google BigQuery hat eine solche Tabelle die folgenden Spaltennamen (das Trennzeichen "." Zeigt die Verschachtelungsstruktur an):
Wie entladen wir Daten aus verschachtelten Feldern?
Zeilen entladen
Zurück zur
Tabelle mit einem Beispiel für benutzerdefinierte GA-Parameter in BQ.
Die Spalten
customDimensions.index und
customDimensions.value sind die Indizes und Werte der
Sitzung und der
benutzerdefinierten benutzerdefinierten Dimensionen.
Die Spalten
Hits.customDimensions.index und
Hits.customDimensions.value sind die Indizes und Werte der
Treffer- benutzerdefinierten Dimensionen.
In Google BigQuery gibt es eine andere Ebene der Benutzerparameteraktion - das Produkt. Die Namen und Werte von Google Custom Big Dimension Custom Dimensions befinden sich in den
Spalten Hits.product.customDimensions.index und
Hits.product.customDimensions.value . Sie werden analog zu den getroffenen Benutzerparametern entladen. Sie müssen nur eine andere Verschachtelungsebene berücksichtigen.
Benutzeroptionen auf Sitzungs- und Benutzerebene
Was können wir tun, wenn wir die Werte der benutzerdefinierten Sitzungsdimensionen für jedes Datum entladen müssen, ohne die verschachtelte Struktur zu speichern (d. H. Zeile für Zeile)?
Um die Frage zu beantworten, schauen wir uns die
Tabelle mit den GA-Benutzerparametern in BQ genauer an.
Es zeigt, dass die Werte der Zellen in der Spalte customDimensions
eine weitere Tabelle sind :
Es reicht aus, in der Hauptabfrage eine Unterabfrage zu dieser Tabelle zu erstellen:
SELECT
Die Ausgabe ist eine Tabelle:
Wenn wir eine Spalte mit dem Wert eines anderen Benutzerparameters hinzufügen müssen, erstellen wir eine weitere Unterabfrage:
SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1,
Wir bekommen folgendes:
Benutzerdefinierte Optionen auf Hit-Ebene
Trefferbenutzerparameter werden auf die gleiche Weise wie Sitzungsparameter (Benutzerparameter) entladen, mit der Ausnahme, dass die Unterabfrage für die verschachtelte Treffertabelle durchgeführt werden muss. Mit anderen Worten, die Werte der Zellen in der Spalte "Treffer" in der Rohdatentabelle von Google Analytics sind verschachtelte Tabellen, in denen die Tabelle "customDimensions" verschachtelt ist:
Die Anforderung zum Herunterladen von zeilenweisen Hit-Benutzerparametern lautet wie folgt:
SELECT
Das Ergebnis der Abfrage ist eine Tabelle:
Sie können mehrere Trefferbenutzerparameter entladen und den Parameter hitNumber (die Sequenznummer des Treffers in der Sitzung) hinzufügen:
SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3,
Holen Sie sich den Tisch:
Sitzung (Benutzer) + Benutzerparameter drücken
Wenn wir in einer Abfrage die Sitzung entladen und Benutzerparameter treffen möchten, müssen Sie nur die erforderlichen Unterabfragen für die Haupttabelle und die verschachtelte Tabelle vornehmen:
SELECT date, h.hitNumber AS hitNumber,
Die Tabelle, die als Ergebnis der Abfrage erhalten wird:
Entladen unter Beibehaltung der Verschachtelungsstruktur
Ein solches Entladen kann erforderlich sein, wenn die Werte eines Benutzerparameters in Google BigQuery ersetzt werden.
Beispiel
In Google Analytics wird der Name des Landes im Vollformat an den Sitzungsbenutzerparameter mit einem Index von 12 und den Trefferbenutzerparameter mit einem Index von 25 für Benutzer aus Russland übertragen: RUSSLAND. Das Länderformat muss in abgekürzt geändert werden: RUS.Dazu müssen Sie die erforderlichen Werte des Benutzerparameters für den gesamten Datenverlauf in Google BigQuery durch das Land des Benutzers ersetzen.
Das Verfahren zur Lösung des Problems:
- Entladen Sie alle Daten, während Sie die Verschachtelungsstruktur beibehalten
- Ersetzen Sie den Wert des Benutzerparameters durch das Land
- Schreiben Sie den Tisch neu
Um Daten hochzuladen, während die Verschachtelungsstruktur beibehalten wird, müssen Sie die ARRAY-Funktion und das Konstrukt SELECT AS STRUCT verwenden. Lassen Sie uns herausfinden, was es ist.
Die Syntax der ARRAY-Funktion lautet wie folgt:
ARRAY(**)
Es gibt ein Array von Elementen zurück.
Vergleichen eines Arrays mit zeilenweiser Aufzeichnung:
Links ist ein Array, rechts ist eine ZeilenaufnahmeWenn wir eine verschachtelte Struktur speichern und ein Array mit mehreren Spalten entladen möchten, müssen wir
ARRAY (SELECT AS STRUCT ...) verwenden :
Verschachteltes ArrayBenutzeroptionen auf Sitzungs- und Benutzerebene
Zum Entladen unter Beibehaltung der Struktur der benutzerdefinierten Sitzungsdimensionen (benutzerdefiniert) verwenden wir die folgende Abfrage:
SELECT date,
Als Ergebnis seiner Ausführung wird eine Tabelle erhalten, in der die Verschachtelungsstruktur der "rohen" Google Analytics-Daten gespeichert wird:
Benutzerdefinierte Optionen auf Hit-Ebene
Um die Werte der Trefferbenutzerparameter aus Google BigQuery zu entladen und gleichzeitig die Verschachtelungsstruktur beizubehalten, ist zu beachten, dass die customDimensions-Tabelle in der Treffer-Tabelle verschachtelt ist. Mit anderen Worten, Sie müssen die ARRAY-Unterabfrage (SELECT AS STRUCT ...) zweimal ausführen: zuerst zur verschachtelten Treffertabelle, dann zur verschachtelten customDimensions-Tabelle:
SELECT date,
Das Ergebnis dieser Abfrage ist eine Tabelle:
Sitzung (Benutzer) + Benutzerparameter drücken
Wie beim zeilenweisen Entladen müssen wir die ARRAY-Unterabfragen (SELECT AS STRUCT ...) in derselben Abfrage zu den gewünschten verschachtelten Tabellen kombinieren:
SELECT date,
Was ist das Ergebnis:
Beispiel für das Ersetzen von Benutzerparameterwerten
Kehren wir zu unserem
Beispiel zurück .
Im vorherigen Abschnitt haben wir eine Anfrage zum Hochladen einer Sitzung (Benutzer) erhalten und die Benutzerparameter von Google Analytics unter Beibehaltung der Verschachtelungsstruktur aufgerufen.
Wir ergänzen diese Abfrage mit SELECT * REPLACE-Konstrukten zum Entladen mit Ersetzung und CASE zum Aktualisieren der Werte der erforderlichen Benutzerparameter:
Als Ergebnis dieser Anfrage erhalten wir die Originaltabelle mit den Rohdaten von Google Analytics. Sie behält die ursprüngliche Verschachtelungsstruktur vollständig bei, aber die Werte der erforderlichen Benutzerparameter werden in neue geändert.
Das Thema Arbeiten mit einer verschachtelten Datenstruktur in Google BigQuery ist nicht einfach.
Ich hoffe, ich habe es geschafft, dieses Problem zu klären. Aber ich möchte Sie daran erinnern, dass der beste Weg, etwas zu lernen, darin besteht, mehr zu üben.