Google Data Studio auf BigQuery - Nutzung und Kostenkontrolle

Data Studio ist ein Reporting-Tool, das zusammen mit anderen Google Cloud Platform-Produkten eine einfache, aber zuverlässige BI-Plattform bildet. Es sind mehrere Konnektoren verfügbar, aber ein Tandem mit BigQuery ist wahrscheinlich die leistungsfähigste Kombination. Da Google Data Studio kostenlos ist, könnten wir den Eindruck gewinnen, dass wir Berichte ausführen können, ohne uns über Kosten oder Leistung Gedanken machen zu müssen. Nun, das stimmt nur zum Teil, denn es ist BigQuery, das den schwereren Teil übernimmt. Schauen wir uns an, wie das Gehirn die Muskeln steuert.

In diesem Artikel werden wir zwei wichtige Fragen behandeln, mit denen jedes Unternehmen, das Data Studio + BigQuery verwendet, früher oder später konfrontiert wird:

Lesen Sie weiter, um mehr über das Auditing von Data Studio zu erfahren und einige Best Practices zur Kostenkontrolle zu entdecken, wenn Ihre Daten in BigQuery gespeichert sind. Wir werden anhand des Data Studio Audit-Protokolls und des BigQuery INFORMATION_SCHEMA für Jobs einige Einblicke gewinnen. Wir werden eine Verbindung zu öffentlichen Datensätzen herstellen, so dass Sie diese ganz einfach bei sich selbst testen können.

‌

Audit-Protokoll und Metadaten

Bevor wir uns ein Urteil darüber bilden können, wie Data Studio die Daten von BigQuery nutzt, müssen wir in der Lage sein, mehr Daten, genauer gesagt Metadaten, zu verfolgen. Mitte 2020 wurde ein Audit-Protokoll für Data Studio eingeführt, was einen Meilenstein darstellt. Bis zu dieser Veröffentlichung erforderte die Verwaltung von Inhalten viel Aufwand und Selbstdisziplin von allen Berichterstellern im Unternehmen. Jetzt können wir alle in Data Studio erstellten Assets identifizieren, zusammen mit den Eigentumsverhältnissen und dem ereignisbasierten Aktionsprotokoll der Benutzer.

‌

Das Audit-Protokoll steht nur für Google Workspace-Konten zur Verfügung (persönliche @gmail.com-Konten sind nicht zugelassen) und kann auf Anfrage in Sheets heruntergeladen werden oder von einem Google-Administrator automatisch in BigQuery geladen werden (nur bei den Editionen Enterprise und Education Plus). Das Protokoll ist zwar eine großartige Funktion und ein großer Schritt in Richtung Transparenz bei der Nutzung des Tools, aber es fehlt eine Verbindung zu den BigQuery-Jobs, die hinter den generierten Anfragen stehen. Um diesen Einblick zu erhalten, können wir entweder Cloud Audit Logs oder INFORMATION_SCHEMA Metadaten verwenden. Beide Optionen beantworten die Fragen nach dem Wer, Was, Wann und Wieviel, aber INFORMATION_SCHEMA erfordert keine Einrichtung und ist einfacher zu verwenden(die Google Analytics-Option ist ebenfalls verfügbar). Jetzt müssen wir nur noch die Anfragen von Google Data Studio von allen anderen isolieren.

‌

Identifizierung der Anfragetypen von Google Data Studio

Da wir nun wissen, wie wir die Metadaten erhalten, müssen wir die Anfragen von Data Studio isolieren und die verschiedenen Arten von Aktionen identifizieren, von denen sie stammen. Diagramme sind zwar das Herzstück eines jeden BI-Tools, aber sie stehen nicht für sich allein. Wir müssen zuerst die Datenquelle verbinden und sie dann mit Filtern und Interaktionen manipulieren.

Verbindung

‌

Chart

‌

‌

Dropdown-Filter

‌

‌

Wir können die Muster für die von Data Studio generierten Abfragen leicht erkennen:

Es ist ziemlich sicher, dass niemand solche Abfragen schreibt. Die Verwendung von REGEXP, um die Anfrage von Data Studio von allen anderen zu trennen, sollte jetzt kein Problem mehr sein:

‌

Sie fragen sich vielleicht, ob dies alle Anfragen abdeckt, die Data Studio erzeugt. Nun, das tut es nicht. Wir vermissen eine Anfrage für eine benutzerdefinierte Abfrageverbindung. Schauen wir sie uns an:

‌

‌

‌

‌

Anstelle einer einzigen Anfrage erzeugt Google Data Studio zwei (oder mehr) für eine benutzerdefinierte Abfrageverbindung. Während die zweite dem Muster entspricht, sieht die erste eher wie eine von Menschen geschriebene Anfrage aus. Es gibt keine `clmn` oder `t0`. Es gibt `LIMIT 100;` aber das ist ein bisschen zu wenig, um sicher zu sein, dass es sich um eine von Data Studio generierte Anweisung handelt. Glücklicherweise gibt es in den Metadaten einen Hinweis, dem wir stattdessen folgen können.

‌

Es sei denn, wir verwenden “datastudiodev” Etikett für jede andere Datenquelle oder jeden anderen Dienst können wir sicher sein, dass es sich bei der Anweisung um eine Verbindungsanforderung handelt, die von Data Studio generiert wird, um das zugrunde liegende Schema abzurufen, unabhängig davon, ob es sich um eine direkte Verknüpfung oder eine benutzerdefinierte Abfrageverbindung handelt. Das ist wirklich eine tolle Sache! Und wenn Google dies nur für alle Data Studio-Anfragen tun würde, bräuchten wir uns gar nicht mit zu beschäftigen. Es wäre sogar noch nützlicher mit zusätzlichen Schlüsseln wie “datasourceid”, “reportid”, “pageid” und “userid” (derjenige, den wir in den Metadaten haben, ist der Eigentümer der Datenquelle, nicht der Viewer). Wenn Sie noch weiter gehen wollen, könnten Sie einen Link zu einer entsprechenden Zeile im Data Studio Audit-Protokoll einfügen, über das wir bereits gesprochen haben. Die Anreicherung der Etiketten mit diesen zusätzlichen Metadaten würde die detaillierte Kontrolle über die Data Studio-Nutzung bringen, die wir jetzt so sehr vermissen! Aber für den Moment müssen wir bei den Etiketten und den Funktionen von REGEXP bleiben, wie sie sind.

Um die Nutzung von BigQuery zu messen, müssen wir die Abfragen noch weiter unterteilen - nach einem Anfragetyp. Während Labels für den Verbindungstyp ausreichen, müssen wir eine Art Kompromiss finden, um zu unterscheiden ‘filter’ Anfragen (z.B. zum Abrufen von Werten für einen Dropdown-Filter) von ‘chart’ ein. Filteranfragen können genauso aussehen wie Anfragen für ein eindimensionales Diagramm. Für diese gibt es keine andere Möglichkeit und wir müssen eine einzige Entscheidung treffen: ob wir sie als Filter oder als Diagramm klassifizieren. Ich persönlich bevorzuge die ‘filter’ Option, da nicht so viele eindimensionale Diagramme erstellt werden. Solange eindimensionale Diagramme in Ihrem Unternehmen nicht die Mehrheit sind, können Sie dies wahrscheinlich auch tun.

CREATE OR REPLACE VIEW ds_usage.v_jobs_by_project
AS
-- NOTE: partitioned by the creation_time column and clustered by project_id and user_email
SELECT
  CASE
    WHEN EXISTS (SELECT TRUE FROM UNNEST(labels) AS label WHERE label.key = "datastudioenv") THEN "connection"
    WHEN REGEXP_CONTAINS(query, r'^SELECT \* FROM \(\nSELECT (clmn[0-9_]+, ){2}') THEN "chart" -- chart with at least two dimensions
    WHEN REGEXP_CONTAINS(query, r'^SELECT \* FROM \(\nSELECT clmn[0-9_]+') AND NOT REGEXP_CONTAINS(query, r'\) GROUP BY clmn[0-9_]+') THEN "chart" -- score card charts
    WHEN REGEXP_CONTAINS(query, r'(?s)^SELECT \* FROM \(\nSELECT clmn[0-9_]+.*LIMIT 20000000') THEN "filter" -- drop-down filter etc
    ELSE NULL
  END AS ds_reuest_type,
  * EXCEPT(referenced_tables, labels, timeline, job_stages) -- to have a flat result all repeated fields are excluded. You can add them using LEFT JOIN UNNEST
FROM
 `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS p -- mind the region
ORDER BY
  creation_time DESC;

Jetzt sind wir bereit, die ersten Diagramme für Data Studio über die Nutzung und die Kosten von Google BigQuery vorzubereiten.

‌

Die Kostengeneratoren von Data Studio

Wenn wir die Anfragen von Data Studio herausfiltern und in 3 Hauptkategorien einteilen, können wir uns die Details ansehen und Kosten- und Leistungsverschwendungen identifizieren, die wir in Zukunft vermeiden sollten.

‌

Kostengenerator #1 - nicht benötigter Trennwandfilter

In einem unserer Beispiele haben wir eine Verbindungsanfrage gesehen, die aufgrund eines fehlenden Partitionsfilters fehlgeschlagen ist.

‌

Während auf der BigQuery-Seite ein Fehler auftrat, verlief der Data Studio-Prozess völlig problemlos und das Schema wurde innerhalb einer Sekunde abgerufen. Das ist das Beste, was wir bekommen können: eine schnelle und kostenlose Antwort. Vielen Dank an partition_filter_required = true Option. Wenn wir die teuersten Verbindungsfälle anhand des uns vorliegenden Protokolls überprüfen, werden wir feststellen, dass sie auf große Tabellen verweisen, bei denen diese Option auf false. Nehmen wir ein Beispiel für eine Verbindung zur Tabelle bigquery-public-data.crypto_zcash.transactions. Was wir über diese spezielle Tabelle wissen müssen, ist, dass sie partitioniert ist, aber keine Filter benötigt, nicht geclustert ist und mehrere sich wiederholende Felder enthält (Verschachtelung). Wir werden überrascht sein, wie viele Anfragen Data Studio aussendet, um das Schema von BigQuery zu erhalten:

‌

Vielleicht sind wir auch überrascht, dass uns insgesamt eine vollständige Überprüfung der Tabelle in Rechnung gestellt wurde, da jede einzelne Anfrage eine vollständige Überprüfung über eine Gruppe von Spalten durchführt, wie hier:

‌

Bewährte Methode: Stellen Sie vor dem Verbinden einer großen Tabelle sicher, dass sie partitioniert ist und partition_filter_required = true. Im Falle von geclusterten Tabellen würde uns LIMIT n auch eine vollständige Überprüfung ersparen, aber die Aktivierung der Option Partitionsfilter erforderlich ist in jedem Fall eine gute Methode.

Kostenverursacher #2 - fehlender BigQuery-Cache

Data Studio bietet eine eigene Cache-Funktion auf Datenquellenebene. Wir können eine Aktualisierungshäufigkeit von 15 Minuten bis zu 12 Stunden (Standard) festlegen. In vielen Fällen werden jedoch trotz des Donnersymbols “fetched from cache” BigQuery-Anfragen erzeugt. Und wenn Ihre Datenquellen nur einmal am Tag aktualisiert werden, erhalten Sie möglicherweise eine zusätzliche Anfrage pro Berichtskomponente. Wenn es in der Zwischenzeit keinen Änderungsprozess gäbe, würden wir uns wünschen, dass solche Anfragen den Cache von BigQuery treffen, aber es gibt einige Blocker, die wir vermeiden müssen. Zunächst einmal muss die Datenquelle die Anmeldedaten des Eigentümers verwenden, um den Cache für alle Benutzer im Unternehmen zu aktivieren. Sobald diese allgemeine Anforderung erfüllt ist, können wir uns um die kleineren kümmern.

‌

Platzhaltertabellen

Bevor die Partitionierung eingeführt wurde, bot BigQuery sharded tables als Ersatz an. Die Idee dahinter ist trivial: Benennen Sie eine Reihe von Tabellen ähnlicher Schemata mit demselben Präfix, aber unterschiedlichen Suffixen und fragen Sie sie in einer einzigen SELECT Anweisung ab. Normalerweise ist das Suffix ein Datum (JJJJMMTT), aber das muss nicht sein. Sie können für jede Geschäftseinheit Ihres Unternehmens dieselben Tabellen erstellen (z.B. employee_hr und employee_it) und alle gleichzeitig abfragen mit SELECTFROM dataset.employee_* WHERE name =‘John’ wenn Sie John zu seinem Namenstag alles Gute wünschen möchten. Das ist zwar eine vielseitige Lösung, aber sie hat den Preis, dass sie nicht gecacht werden kann.

Bewährte Methode: Verwenden Sie nach Möglichkeit UNION ALL anstelle der Wildcard-Tabellen-Funktionalität, um das Caching von BigQuery zu ermöglichen.

Nicht-deterministische Funktionen

Nicht-deterministisch" ist ein technischer Begriff für eine Gruppe von Funktionen, die bei jedem Aufruf unterschiedliche Ergebnisse liefern können. Im Fall von Google BigQuery sind das die Funktionen CURRENT_DATE, CURRENT_TIMESTAMP, SESSION_USER oder RAND. Obwohl sie unter bestimmten Umständen nützlich sein können (vor allem unter CURRENT_DATE ), müssen wir bedenken, dass sie die Nutzung des Cache von BigQuery verhindern und somit die Abfragekosten in die Höhe treiben können. Data Studio bietet eine Lösung zur Umgehung einiger dieser Fälle mit der Verwendung von Custom Parameters für Custom Query-Verbindungen.

‌

Bewährte Praxis: Versuchen Sie, nicht-deterministische Funktionen in Datenquellen zu vermeiden, oder verwenden Sie wenn möglich benutzerdefinierte Parameter als Ersatz.

Kostengenerator #3 - unparametrisierte Fensterfunktionen

Einer der Hauptgründe für die Verwendung von benutzerdefinierten Abfragen als Quellverbindung ist die Notwendigkeit der Verwendung von Fensterfunktionen. Es gibt mehrere Berichtsfälle, die die Verwendung der Funktionen RANK, LEAD oder PERCENTILE über einen gefilterten Teil der Daten erfordern. Fensterfunktionen erben jedoch im Gegensatz zu Aggregationsfunktionen keine Filter von äußeren Unterabfragen. Lassen Sie uns ein Beispiel verwenden. Wenn wir eine Dimension und eine Kennzahl aus einer einzigen Partition nehmen möchten, wäre die einfachste SELECT:

‌

Da aber alle Google Data Studio-Anfragen die Datenquelle als Unterabfrage verwenden, wollen wir sehen, wie es mit einer benutzerdefinierten Abfrageverbindung funktionieren würde:

‌

Der Partitionsfilter ist zwar zwei Ebenen von der Unterabfrage der Datenquelle entfernt, verhält sich aber dennoch so, als befände er sich auf der gleichen Ebene. Er verhält sich so, als wäre er in die innerste Abfrage transponiert worden, und so erhalten wir die gleichen Kosten wie in der vorherigen Anweisung. Ändern wir nun SUM von der Aggregationsversion zu window:

‌

Während die Anweisung der vorherigen sehr ähnlich sieht, gibt es eine drastische Änderung bei den abgerechneten Bytes. Der Partitionsfilter wird aktiv, nachdem eine Fensterfunktion für alle Zeilen jeder einzelnen Partition aufgerufen wurde. Eine Änderung des Datumsbereichsfilters wirkt sich nicht auf die Anzahl der durchsuchten Partitionen aus und begrenzt auch nicht die Abfragekosten. Was wir jedoch tun können, ist, den Datumsfilter in dieselbe Unterabfrage wie unsere Fensterfunktion zu verschieben. Auch hier sollten wir die benutzerdefinierten Parameter von Data Studio verwenden, wie hier:

‌

Bewährte Methode: Verwenden Sie für benutzerdefinierte Abfragedatenquellen, die Fensterfunktionen erfordern, benutzerdefinierte Parameter, um eine vollständige Überprüfung zu verhindern.

Kostengenerator #4 - Filter für die Suche nach Datenquellen

Filter sind unerlässlich, um Berichte interaktiv zu gestalten. Normalerweise besteht ihre Hauptaufgabe darin, zwischen verschiedenen Teilen eines größeren Bildes zu wechseln oder mit Hilfe von Parametern sogar den Blickwinkel zu verändern. Auch die Begrenzung der Abfragekosten ist eine wichtige Funktion. Aber es gibt einige Eckfälle, in denen die Benutzerfreundlichkeit unsere Brieftasche hart treffen kann.

‌

Google Data Studio stellt uns 6 Arten von Filtersteuerelementen zur Verfügung, den Datumsbereich natürlich nicht mitgerechnet. Das wahrscheinlich am häufigsten verwendete Steuerelement ist ein Drop-down Listensteuerung. Der Erfolg dieses Filters beruht auf seiner Benutzerfreundlichkeit, sowohl für den Ersteller als auch für den Betrachter. Der Ersteller muss nicht an alle möglichen Werte denken, die der Filter abdecken soll, da er in der zugrunde liegenden Datenquelle nachschlägt und alle verfügbaren Werte zurückgibt. Ein Nachschlagen bedeutet zusätzliche SELECT auf der BigQuery-Seite. Wenn es eine Seitenebene gibt Date Range Kontrolle verwendet, wird zumindest eine Partitionsfilterung für diese Anweisung durchgeführt. Wenn es jedoch keine Partitionsbeschränkung gibt, wird ein einzelnes Feld (oder zwei Felder, wenn der Filter eine Maßnahme enthält) vollständig durchsucht. Selbst bei einer begrenzten Anzahl von Partitionen kann dies ein Overkill sein, wenn das Feld nur wenige Optionen enthält. Im Fall von "booleschen" Daten müssen wir nicht den gesamten Monat durchsuchen, um zu wissen, dass nur zwei Werte möglich sind! Anstelle einer Drop-down sollten wir eine Tickbox Filter zu verwenden, der keine zusätzlichen Kosten verursacht. Wenn unser Filter mehr als zwei Optionen bieten soll, aber dennoch eine bestimmte Anzahl (z.B. Ländercodes), können wir eine Inputbox-Komponente verwenden, oder wir verwenden eine Drop-down aber dieses Mal mit Verweis auf die Maßtabelle country_codes.

Es gibt zwei weitere Steuerelemente, die die Datenquelle abfragen, um eine Liste von Werten zu erhalten: ein Fixed-size list und eine Slider. Idealerweise sollten wir, bevor wir sie verwenden, prüfen, ob wir sie nicht durch eine der grundlegenden Quellen ersetzen können, oder den Anwendungsbereich auf Dimensionsquellen ändern, oder eine Kombination aus Data Studio-Parametern und berechneten Feldern verwenden. Im letzten Fall ist etwas zusätzliche Arbeit erforderlich, da wir einen Parameter erstellen müssen [optional eine Liste möglicher Werte definieren], ein zusätzliches Feld erstellen müssen, das den Wert des Parameters extrahiert, und schließlich ein zweites Feld, das den extrahierten Wert mit dem Datenbankfeld vergleicht. Das sieht überwältigend aus, macht sich aber im Laufe der Zeit bei jeder einzelnen Aktualisierung eines Berichts bezahlt.

‌

Bewährte Praxis: Begrenzen Sie die Anzahl der Drop-down Filter. Verwenden Sie stattdessen Eingabefelder oder erweiterte Filtertypen. Parameter und Filter im Bearbeitungsbereich können ein guter Ersatz für eine Auswahlliste von Werten wie Namen von Geschäftseinheiten sein. Die Verwendung einer Dimensionstabelle als separate Datenquelle nur für die Filterung ist ebenfalls eine gute Option.

Fazit

Google Data Studio ist ein kostenloses Reporting-Tool und wir müssen gewisse Kompromisse bei der Funktionalität und Effektivität eingehen. Eine der Funktionen, die wir bei der Verwendung eines BI-Tools in einem Unternehmen erwarten würden, ist das Auditing. Es gibt zwar ein neues Data Studio-Auditprotokoll für die Nutzung von Inhalten, aber die zugrunde liegende DWH-Nutzung ist immer noch ein Rätsel, da bisher keine integrierte Funktion eingeführt wurde. Wir können uns jedoch auf BigQuery verlassen, falls es sich um eine Verbindung handelt. Mit einer einzigen Metadatenansicht und ein paar Zeilen Code können wir Data Studio-Anfragen trennen und sogar in Kategorien unterteilen. Dann sind wir nur noch einen Schritt davon entfernt, einen Einblick in die Leistung von Data Studio zu bekommen. Die Kostenanalyse ist wahrscheinlich diejenige, die wir am meisten vermisst haben. Sobald wir sie haben, können wir Ineffizienzen und schlechte Angewohnheiten bei der gemeinsamen Nutzung dieser beiden Produkte erkennen. Mit den richtigen Erkenntnissen und dem Wissen über bewährte Verfahren sind wir in der Lage, diese Kostengeneratoren in Zukunft zu begrenzen.

‌

Nächster Schritt

Die Abfragen von Google Data Studio im Auge zu behalten ist zwar schön, aber wäre es nicht toll, aus jeder Abfrage eine Datenquelle zu extrahieren? Letztendlich sollte jeder Nutzer nur eine Verbindung für jede zugrunde liegende Tabelle oder Ansicht haben. Wenn nicht, handelt es sich wahrscheinlich um benutzerdefinierte Abfragen, d.h. sie unterscheiden sich voneinander, oder?

Und wäre es nicht gut zu wissen, wer die Anfrage ausgelöst hat und von welchem Bericht oder welcher Seite aus? Es besteht die Möglichkeit, dass die Produktentwickler von Data Studio diese fehlenden Puzzlesteine liefern, indem sie die Protokollbeschriftungen anreichern, aber bis es soweit ist, können wir über andere Verbesserungen nachdenken, wie z.B. ein "Web Beacon"-ähnliches Diagramm, das auf jeder Berichtsseite versteckt ist.