Archivierung von Google BigQuery Tabellen

Kosten sparen durch die Archivierung mit Google Cloud Storage

Die tägliche Arbeit mit Daten in Google BigQuery hat viele Vorteile und hilft einem, den Überblick zu behalten. Tabellen und Views helfen uns, verschiedene Perspektiven auf die Daten einzunehmen.

Dennoch tritt der Fall ein, dass Tabellen nach einer gewissen Zeit nicht mehr genutzt oder aktualisiert werden. Diese ungenutzten Tabellen verschlechtern mit der Zeit nicht nur die Übersichtlichkeit, sondern sind auch ein nicht zu unterschätzender Kostenfaktor.

Insbesondere durch die Preiserhöhung von Google BigQuery steigt der Druck, die Kosten bestmöglich zu senken. Die ungenutzten Tabellen bieten hier ein großes Potential.

Wir zeigen in diesem Artikel wie

1. Ungenutzte Tabellen identifiziert und
2. BigQuery Tabellen im Google Cloud Storage archiviert werden

Identifikation ungenutzter Tabellen

In einem früheren Blogartikel haben wir bereits erklärt, wie die Metadaten zu den Tabellen in BigQuery abgefragt werden können. Dies kann nun angewandt werden. Mithilfe des INFORMATION SCHEMA kann das Datum der letzten Modifikation der einzelnen Tabellen abgefragt werden.

Diese Query könnte beispielhaft so aussehen:

1SELECT 
2  project_id, 
3  dataset_id, 
4  table_id as table_name, 
5  TIMESTAMP_MILLIS(creation_time) as creation_time, 
6  TIMESTAMP_MILLIS(last_modified_time) as last_modified, 
7  row_count, 
8  round(safe_divide(size_bytes, (1000*1000)),1) as size_mb, 
9  round(safe_divide(size_bytes, (1000*1000*1000)),2) as size_gb, 
10  CASE
11    WHEN type = 1 THEN "BASE TABLE" 
12    WHEN type = 2 THEN "VIEW" 
13    WHEN type = 3 THEN "EXTERNAL"
14    ELSE "OTHERS" 
15    END AS type 
16FROM `project.dataset.__TABLES__`

Liegt dieses Datum > 365 Tage zurück, gilt es diese Tabelle potenziell zu archivieren. Aber vorsichtig: Hier sollte im Einzelfall geprüft werden,

• ob die Tabelle benötigt wird, obwohl sie lange nicht modifiziert wurde (bspw. Tabellen für Metadaten) und
• ob sich auch wirklich Daten in der Tabelle regelmäßig verändern. Laufen zum Beispiel täglich Scheduled Queries, die aber keine neuen Daten hinzufügen oder bestehende Daten ändern, ist zwar das last modification date aktuell, aber die Daten in der Tabelle sind vielleicht veraltet. In solchen Fällen helfen Datumsfelder. Wir nutzen dafür technische Felder, um zu sehen, wann eine Zeile hinzugefügt und zuletzt geändert wurde.

Nach der Identifizierung der veralteten Tabellen und Views stehen zwei Möglichkeiten zur Verfügung:

• die Tabellen oder Views löschen, wenn sie nicht mehr gebraucht werden
• die Tabellen oder Views archivieren mithilfe von Google Cloud Storage & anschließender Löschung

BigQuery-Daten im Google Cloud Storage

Wenn Google Cloud Storage bisher noch nicht genutzt wird, sollte dies dringend in Betracht gezogen werden. Insbesondere, weil Google auch hier die Preise angepasst hat und das Archivieren von Tabellen teilweise sogar günstiger geworden ist. Google Cloud Storage ist der ideale Ort, um Tabellen langfristig zu sichern, die

• nicht genutzt werden
• aber dennoch erhalten bleiben sollen.

In Google Cloud Storage lässt sich ein eigener Bucket anlegen, der ausschließlich als Archiv dient. Der Vorteil eines separaten Buckets für solche Tabellen, die nicht in naher Zukunft genutzt werden, liegt in den Speicherklassen von Google Cloud Storage.

Die Speicherklasse beschreibt ein Objekt (in diesem Fall ein Storage Bucket) und hat Auswirkungen auf die Verfügbarkeit und die Kosten. Es gibt 4 verschiedene Speicherklassen, aus denen eine ausgewählt werden muss:

• Standard Storage
• Nearline Storage (minimum duration 30 days)
• Coldline Storage (minimum duration 90 days)
• Archive Storage (minimum duration 365 days)

Je nachdem, in welcher zeitlichen Frequenz auf die Daten in Cloud Storage zugegriffen wird, werden die Kosten zur Speicherung und die Gebühren zum Abrufen der Daten festgelegt. Ein Beispiel:

Die Speicherklasse Archive Storage hat eine Mindestspeicherdauer von 365 Tagen und mit 0,0025 $ pro GB und Monat die geringsten Speicherkosten. Die Speicherklasse eignet sich also für die Daten, die mit großer Sicherheit nicht innerhalb der nächsten 365 Tage gelöscht oder verschoben werden. Sollte dies doch der Fall sein, ist es möglich, aber verursacht zusätzliche Aufrufgebühren.

Die genaue Auflistung der Preise hinsichtlich der einzelnen Speicherklassen sind hier zu finden.

Archivierung von BigQuery Tabellen

Der Prozess BigQuery Tabellen im Cloud Storage zu archivieren ist nicht kompliziert und besteht aus zwei grundsätzlichen Schritten:

1. Bucket im Cloud Storage anlegen, Region und Speicherklasse auswählen.
2. BigQuery Tabelle mithilfe des User Interface in Cloud Storage verschieben. Hierzu bei der entsprechenden Tabelle Export > Nach GCS exportieren auswählen, den vorher erstellten Bucket auswählen und speichern.

Wichtig: Damit man durch die Archivierung der nicht genutzten Tabellen auch profitiert, sollten die Tabellen in BigQuery gelöscht werden, sobald sie in Cloud Storage verschoben wurden. So lassen sich die Speicherkosten in BigQuery einsparen und eine aktualisierte Verwaltung der Tabellen ist sichergestellt.

Tipp: Daten aus dem Google Cloud Storage können sogar später noch mittels External Tables direkt aus dem Google Cloud Storage abgefragt werden. Wie das funktioniert, ist hier nachzulesen.