Business Intelligence/22. Juli 2024 -Aktualisiert am 21. Februar 2025/3 Min. Lesezeit

Optimieren der BigQuery-Kosten: Häufig abgefragte Views in Tabellen umwandeln

Wenn Unternehmen wachsen, wachsen auch die Daten, so kann die Abfrage von Daten zu einem erheblichen Kostenfaktor werden. Eine effektive Möglichkeit, die steigenden BigQuery-Kosten zu reduzieren, besteht darin, häufig abgefragte Views zu identifizieren und sie durch Tabellen zu ersetzen. In diesem Artikel gehen wir den Prozess Schritt für Schritt durch:

Schritt 1: Views identifizieren, die häufig abgefragt werden

Bevor Views in Tabellen umgewandelt werden, ist es notwendig zu ermitteln, welche Views am häufigsten abgefragt werden. Mithilfe des INFORMATION_SCHEMA, welches wir bereits in einem anderen Blogartikel genutzt haben, kann eine Liste von Views sowie deren Abfragehäufigkeit für einen bestimmten Zeitraum abgerufen werden.

1SELECT
2  views.table_catalog,
3  views.table_schema,
4  views.table_name,
5  COUNT(jobs.job_id) AS jobs,
6  SUM(jobs.total_bytes_processed) AS total_bytes_processed,
7  SUM(((total_bytes_billed / 1000000000) / 1000) * 5) AS total_cost
8FROM
9`your_project_name`.`your_region_name`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS jobs
10INNER JOIN
11  `your_project_name`.`your_region_name`.INFORMATION_SCHEMA.VIEWS AS views
12ON
13  query LIKE '%'|| table_schema || '.' || table_name || '%'
14WHERE
15  job_type = 'QUERY'
16  AND state = 'DONE'
17GROUP BY
18  views.table_catalog,
19  views.table_schema,
20  views.table_name
21ORDER BY
22  jobs DESC;

Diese Abfrage ruft Daten aus der View INFORMATION_SCHEMA.JOBS_BY_PROJECT und .VIEWS ab und zeigt die Anzahl Abfragen sowie die jeweiligen Kosten je View.

Schritt 2: Konvertierung von Views in Tabellen

Nachdem die am häufigsten abgefragten Views identifiziert wurden, besteht der nächste Schritt darin, diese in Tabellen umzuwandeln. Dabei werden neue Tabellen mit demselben Schema wie die Views erstellt und mit den entsprechenden Daten gefüllt.

Hierfür kann die Anweisung CREATE TABLE verwendet werden, um eine neue Tabelle mit den Daten aus der View zu erstellen:

1CREATE TABLE IF NOT EXISTS `your_project_id.your_dataset_id.new_table`
2AS
3SELECT 
4  *
5FROM `your_project_id.your_dataset_id.existing_view`

Schritt 3: Sicherstellung der Datenaktualität

Ein Problem bei der Umwandlung von Views in Tabellen ist die Aktualität der Daten. Views liefern Echtzeitdaten, während Tabellen manuelle oder automatische Aktualisierungen erfordern können. Um die Datengenauigkeit aufrechtzuerhalten, können beispielsweise geplante Abfragen oder Pipelines in Google Dataflow genutzt werden.

Geplante Abfragen

Erstellung von geplanten Abfragen zur regelmäßigen Aktualisierung der Daten in den Tabellen. Dies kann durch SQL-Anweisungen erreicht werden, die die Tabelle mit den neuesten Daten aus Quellen oder Views aktualisieren.

Pipelines mit Google Dataflow

Für komplexere Daten-Pipelines können Google Dataflow verwendet werden, um Daten-Aktualisierungen zu automatisieren. Dies ist besonders nützlich, wenn Transformationen oder Aggregationen beteiligt sind.

Schritt 4: Überwachung und Validierung

Sobald die neuen Tabellen eingerichtet sind, ist es wichtig, diese zu überwachen, um die Korrektheit der Daten sicherzustellen. Die Ausführungszeiten der Abfragen sollten beobachtet und die Daten zunächst anhand der ursprünglichen Views validiert werden, um die Konsistenz zu gewährleisten.

Fazit

Die Konvertierung häufig abgefragter Views in Tabellen ist ein strategischer Ansatz zur Optimierung der BigQuery-Kosten. Durch Befolgen der in diesem Blog beschriebenen Schritte können häufig abgefragte Views effektiv identifiziert, durch Tabellen ersetzt und die Aktualität der Daten beibehalten werden. Dieser Ansatz hilft nicht nur bei der Kostensenkung, sondern trägt auch zu einer verbesserten Abfrageleistung und Gesamteffizienz in den Datenanalyseprozessen bei.

Referenz: