"Illustration einer Tabelle mit einem Snapshot Icon"
Business Intelligence/7. August 2023 -Aktualisiert am 7. August 2023/4 Min. Lesezeit
Backups von BigQuery Tabellen: Automatisiert mit Table Snapshots
Die Arbeit mit großen Datenmengen beinhaltet leider immer das Risiko, Daten zu verlieren, weshalb die Gewährleistung von Datensicherheit eine große Rolle spielt. Dafür ist es unverzichtbar, Backups zu erstellen. In diesem Blogartikel möchten wir einen Weg vorstellen, wie auf einem einfachen Weg Backups erstellt werden können: Mithilfe von Tabellen-Snapshots in Google BigQuery!
Bisher bietet Google BigQuery leider kein automatisches Feature zur Erstellung von Backups auf Dataset-Ebene an. Lediglich eine Anleitung zur automatisierten Erstellung von Snapshots für einzelne Tabellen. Doch wenn man für alle Tabellen eines Datasets automatisiert Backup Snapshots erstellen möchte, ist dies zu aufwendig. Daher haben wir eine Weg entwickelt automatisiert Snapshots für jede Tabelle eines Datasets zu erstellen.
Dieser Blogbeitrag behandelt folgende Fragen:
- Was ist ein Snapshot?
- Wie kann ich ihn verwenden?
- Was sind die Vorteile von Snapshots?
- Wie kann ich Snapshots mithilfe eines Serviceaccounts automatisieren?
Was ist ein Tabellen-Snapshot und wie kann er verwendet werden?
Ein BigQuery Tabellen Snapshot speichert den Inhalt einer Basistabelle zu einem bestimmten Zeitpunkt und stellt somit ein Backup der ausgewählten Tabelle dar. Snapshots von Views oder externen Tabellen sind nicht möglich. Ein Tabellen-Snapshot ist schreibgeschützt und kann nicht verändert werden. Um eine versehentlich gelöschte Basistabelle mithilfe eines Snapshots wiederherzustellen, muss lediglich eine neue Tabelle erstellen, die sich auf den Snapshot bezieht.
Beispiel um eine Snapshot Tabelle snapshot_customer_data zu erstellen mit der customer_data Tabelle als Basistabelle:
1CREATE SNAPSHOT TABLE
2 snapshot_customer_data
3CLONE
4 customer_datamysql
Beispiel für die Wiederherstellung der Daten der Tabelle customer_data unter Verwendung der Snapshot-Tabelle snapshot_customer_data durch Erstellen einer neuen Tabelle customer_data_restore:
1CREATE TABLE
2 customer_data_restore
3CLONE
4 snapshot_customer_datamysql
Vorteile von Tabellen Snapshots
Der Unterschied zwischen einem Tabellen-Snapshot und einem Export in Google Cloud Storage als Backup Alternative ist, dass der Snapshot weniger Speicherplatz benötigt. Ein regelmäßiger Export aller Tabellen in einen Cloud-Speicher scheint daher nicht sinnvoll zu sein. Für die Archivierung veralteter Tabellen ist der Export in einen Cloud-Speicher mit einer Archivierungseinstellung jedoch sinnvoll.
- Tabellen Snapshots können nicht aus Views erstellt werden.
- Tabellen Snapshots sind kostenlos - nur wenn Daten in der Basistabelle gelöscht werden, die im Snapshot noch vorhanden sind, werden Kosten für diese Daten berechnet.
Automatisierte Tabellen Snapshots je Dataset @ Peaks&Pies
Derzeit gibt es in Google BigQuery kein Feature zur automatischen Erstellung von Snapshots für alle Tabellen in einem Dataset. Wir empfehlen daher die folgende Prozedur vor:
1. Backup Dataset erstellen
Die Tabellen-Snapshots eines Datasets werden in einem separaten Backup Dataset gespeichert. Für jedes Dataset muss im Voraus ein Backup Dataset erstellt werden (Benennung: bkp_<datasetname>) data -> bkp_data.
2. SQL Prozedur erstellen
Die nachstehende SQL Prozedur durchläuft alle Tabellen innerhalb eines Datasets, erstellt einen Snapshot und speichert ihn in dem vorgesehenen Backup Dataset. Diese Prozedur sollte in einem separaten Dataset (z.B. Funktionen) gespeichert werden. Die Prozedur enthält auch ein Ablaufdatum (= Expiration Date). Durch dieses Ablaufdatum wird ein Snapshot immer nach einer gewissen Zeit gelöscht, sodass ein aktueller Snapshot erstellt werden kann.
1CREATE OR REPLACE PROCEDURE `project.functions.create_snapshots_for_backup`(datasetId STRING, backupDatasetId STRING)
2BEGIN
3 DECLARE snapshotDateSuffix STRING DEFAULT REPLACE(CAST(CURRENT_DATE() AS STRING), '-', '');
4 DECLARE tableName STRING;
5 DECLARE tableQuery STRING;
6 DECLARE snapshotTableName STRING;
7 DECLARE snapshotQuery STRING;
8
9 -- Get list of tables to snapshot
10 SET tableQuery = CONCAT('CREATE TEMPORARY TABLE tableList AS SELECT table_name FROM ',datasetId,'.INFORMATION_SCHEMA.TABLES WHERE table_type = \'BASE TABLE\' AND table_schema = "',datasetId,'"');
11 EXECUTE IMMEDIATE tableQuery;
12
13 -- Loop through tables and create snapshot tables
14 FOR row IN (SELECT * FROM tableList)
15 DO
16 SET tableName = row.table_name;
17 SET snapshotTableName = CONCAT(backupDatasetId, '.', tableName, '_', snapshotDateSuffix);
18 SET snapshotQuery = CONCAT('CREATE SNAPSHOT TABLE ', snapshotTableName, ' CLONE ', datasetId , '.', tableName,' OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 31 DAY)) ');
19 EXECUTE IMMEDIATE snapshotQuery;
20 END FOR;
21ENDmysql
3. Scheduled Query erstellen
Damit diese Prozedur lediglich einmal aufgesetzt werden muss und danach automatisch läuft, nutzen wir die Scheduled Queries von BigQuery. Hier muss in die Abfrage für jedes Dataset ein eigener CALL Befehl inkl. der entsprechenden Eingabeparametern (datasetId und backup_datasetId) enthalten sein. Im untenstehenden Beispiel werden für alle Tabellen der beiden Datasets metadata und data Table Snapshots erstellt. In den Einstellungen der Scheduled Query kann dann zusätzlich noch die Häufigkeit der Ausführung bestimmt werden.
1CALL `project.functions.create_snapshots_for_backup`("metadata", "bkp_metadata")
2CALL `project.functions.create_snapshots_for_backup`("data", "bkp_data")mysql
4. Servicekonto als Benutzer autorisieren
Um die geplante Abfrage unabhängig von einem Benutzer ausführen zu können, sollte ein Servicekonto erstellt und diesem die Rechte zur Ausführung zugewiesen werden. Die notwendigen Voraussetzungen sind unter diesem Link zu finden. Die Übertragung der Rechte der scheduled Query auf das Servicekonto ist derzeit nur über die Google Cloud Shell mit folgendem Snippet möglich:
1bq update \
2--transfer_config \
3--update_credentials \
4--service_account_name={mailadress service account} {resource name of scheduled query} \sh
Quellen:
Introduction to table snapshots | BigQuery | Google Cloud
Scheduling queries | BigQuery | Google Cloud
Create table snapshots with a scheduled query | BigQuery | Google Cloud
Alternativ zu diesem Weg gibt es weitere (in-)offizielle Workarounds, die von Google selbst veröffentlicht werden. Diese funktionieren mit Cloud Functions, Cloud Scheduler, Pub/Sub oder Workflows: