Tabellen-Snapshots mit einer geplanten Abfrage erstellen

In diesem Dokument wird beschrieben, wie Sie mit einem Dienstkonto, das eine geplante DDL-Abfrage ausführt, monatliche Snapshots einer Tabelle erstellen. Das Dokument führt Sie durch das folgende Beispiel:

  1. Erstellen Sie im Projekt PROJECT ein Dienstkonto mit dem Namen snapshot-bot.
  2. Gewähren Sie dem Dienstkonto snapshot-bot die Berechtigungen, die es zum Erstellen von Tabellen-Snapshots der Tabelle TABLE benötigt, die sich im DATASET-Dataset befindet. Der Tabellen-Snapshot wird dann im Dataset BACKUP gespeichert.
  3. Schreiben Sie eine Abfrage, die monatliche Snapshots der Tabelle TABLE erstellt und sie im Dataset BACKUP speichert. Da Sie einen vorhandenen Tabellen-Snapshot nicht überschreiben können, müssen die Tabellen-Snapshots eindeutige Namen haben. Dazu hängt die Abfrage das aktuelle Datum an die Namen der Tabellen-Snapshots an. Beispiel: TABLE_20220521. Die Tabellen-Snapshots laufen nach 40 Tagen ab.
  4. Planen Sie das Dienstkonto snapshot-bot so, dass die Abfrage am ersten Tag jedes Monats ausgeführt wird.

Dieses Dokument richtet sich an Nutzer, die mit BigQuery und BigQuery-Tabellen-Snapshots vertraut sind.

Berechtigungen und Rollen

In diesem Abschnitt werden die IAM-Berechtigungen (Identity and Access Management – Identitäts- und Zugriffsverwaltung) beschrieben, die Sie benötigen, um ein Dienstkonto zu erstellen und eine Abfrage zu planen, sowie die vordefinierten IAM-Rollen, die diese Berechtigungen gewähren.

Berechtigungen

Um mit einem Dienstkonto zu arbeiten, benötigen Sie die folgenden Berechtigungen:

Berechtigung Ressource Ressourcentyp
iam.serviceAccounts.* PROJECT Projekt

Zum Planen einer Abfrage benötigen Sie die folgenden Berechtigungen:

Berechtigung Ressource Ressourcentyp
bigquery.jobs.create PROJECT Projekt

Rollen

Die vordefinierten Rollen, die die erforderlichen Berechtigungen bereitstellen, um mit einem Dienstkonto zu arbeiten, sind:

Rolle Ressource Ressourcentyp
Beliebige von Folgendem:

roles/iam.serviceAccountAdmin
roles/editor
roles/owner 		PROJECT Projekt

Folgende vordefinierte BigQuery-Rollen enthalten die Berechtigungen, die zum Planen einer Abfrage erforderlich sind:

Rolle Ressource Ressourcentyp
Beliebige von Folgendem:

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin 		PROJECT Projekt

Dienstkonto snapshot-bot erstellen

Führen Sie die folgenden Schritte aus, um das Dienstkonto snapshot-bot zu erstellen und ihm die Berechtigungen zuzuweisen, die zum Ausführen von Abfragen im Projekt PROJECT erforderlich sind:

Console

  1. Rufen Sie in der Cloud Console die Seite Dienstkonten auf.

    Zur Seite „Dienstkonten“

  2. Wählen Sie das Projekt PROJECT aus.

  3. Erstellen Sie das Dienstkonto snapshot-bot:

    1. Klicken Sie auf Dienstkonto erstellen.

    2. Geben Sie im Feld Name des Dienstkontos snapshot-bot ein.

    3. Klicken Sie auf Erstellen und fortfahren.

  4. Gewähren Sie dem Dienstkonto die Berechtigungen, die zum Ausführen von BigQuery-Jobs erforderlich sind:

    1. Wählen Sie im Abschnitt Diesem Dienstkonto Zugriff auf das Projekt erteilen die Rolle BigQuery-Nutzer aus.

    2. Klicken Sie auf Fertig.

BigQuery erstellt das Dienstkonto mit der E-Mail-Adresse snapshot-bot@PROJECT.iam.gserviceaccount.com.

So können Sie prüfen, ob BigQuery das Dienstkonto mit den von Ihnen angegebenen Berechtigungen erstellt hat:

Console

So prüfen Sie, ob BigQuery das Dienstkonto erstellt hat:

  1. Rufen Sie in der Cloud Console die Seite Dienstkonten auf.

    Zur Seite „Dienstkonten“

  2. Wählen Sie das Projekt PROJECT aus.

  3. Klicken Sie auf snapshot-bot@PROJECT.iam.gserviceaccount.com.

  4. Prüfen Sie, ob in der Meldung Dienstkontostatus angezeigt wird, dass Ihr Dienstkonto aktiv ist.

So prüfen Sie, ob BigQuery Ihrem Dienstkonto die Berechtigung erteilt hat, Abfragen auszuführen:

  1. Wechseln Sie in der Google Cloud Console zur Seite Ressourcen verwalten:

    Zur Seite „Ressourcen verwalten“

  2. Klicken Sie auf PROJECT.

  3. Klicken Sie auf Infofeld anzeigen.

  4. Erweitern Sie auf dem Tab Berechtigungen den Knoten BigQuery-Nutzer.

  5. Prüfen Sie, ob das Dienstkonto snapshot-bot aufgeführt wird.

Berechtigungen für das Dienstkonto gewähren

In diesem Abschnitt wird beschrieben, wie Sie dem Dienstkonto snapshot-bot die Berechtigungen zum Erstellen von Tabellen-Snapshots der Tabelle DATASET.TABLE im Dataset BACKUP erteilen.

Berechtigung zum Erstellen von Snapshots der Basistabelle

So erteilen Sie dem Dienstkonto snapshot-bot die Berechtigungen, die zum Erstellen von Snapshots der Tabelle DATASET.TABLE erforderlich sind:

Console

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Maximieren Sie im Bereich Explorer den Projektknoten PROJECT.

  3. Maximieren Sie den Dataset-Knoten DATASET.

  4. Wählen Sie die Tabelle TABLE aus.

  5. Klicken Sie auf Freigeben. Der Bereich Freigeben wird geöffnet.

  6. Klicken Sie auf Hauptkonto hinzufügen. Der Bereich Zugriff erlauben wird geöffnet.

  7. Geben Sie unter Neue Hauptkonten die E-Mail-Adresse des Dienstkontos ein: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  8. Wählen Sie im Drop-down-Menü Rolle auswählen die Rolle BigQuery-Datenbearbeiter aus.

  9. Klicken Sie auf Speichern.

  10. Maximieren Sie im Bereich Freigeben den Knoten BigQuery-Datenbearbeiter und prüfen Sie, ob das Dienstkonto snapshot-bot@PROJECT.iam.gserviceaccount.com aufgeführt wird.

  11. Klicken Sie auf Schließen.

bq

  1. Aktivieren Sie Cloud Shell in der Cloud Console.

    Cloud Shell aktivieren

  2. Geben Sie den folgenden Befehl bq add-iam-policy-binding ein:

    bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

BigQuery bestätigt, dass die neue Richtlinienbindung hinzugefügt wurde.

Berechtigung zum Erstellen von Tabellen im Ziel-Dataset

So weisen Sie dem Dienstkonto snapshot-bot die Berechtigungen zum Erstellen von Tabellen-Snapshots im Dataset BACKUP zu:

Console

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Maximieren Sie im Bereich Explorer den Projektknoten PROJECT.

  3. Klicken Sie auf das Menü für den Dataset-Knoten BACKUP und wählen Sie Öffnen aus.

  4. Klicken Sie auf Share Dataset (Dataset freigeben). Der Bereich Dataset-Berechtigungen wird geöffnet.

  5. Geben Sie im Feld Mitglieder hinzufügen die E-Mail-Adresse des Dienstkontos ein: snapshot-bot@PROJECT.iam.gserviceaccount.com.

  6. Wählen Sie im Drop-down-Menü Rolle auswählen die Rolle BigQuery-Datenbearbeiter aus.

  7. Klicken Sie auf Hinzufügen.

  8. Prüfen Sie im Bereich Dataset-Berechtigungen, ob das Dienstkonto snapshot-bot@PROJECT.iam.gserviceaccount.com unter dem Knoten BigQuery Dateninhaber aufgeführt wird.

  9. Klicken Sie auf Fertig.

Ihr Dienstkonto snapshot-bot hat jetzt die folgenden IAM-Rollen für die folgenden Ressourcen:

Rolle Ressource Ressourcentyp Zweck
BigQuery-Dateneditor PROJECT:DATASET.TABLE Tabelle Snapshots der Tabelle TABLE erstellen.
BigQuery-Dateninhaber PROJECT:BACKUP Dataset Tabellen-Snapshots im Dataset BACKUP erstellen und löschen.
BigQuery-Nutzer PROJECT Projekt Geplante Abfrage zum Erstellen der Tabellen-Snapshots ausführen.

Diese Rollen gewähren die Berechtigungen, die das Dienstkonto snapshot-bot benötigt, um Abfragen auszuführen, mit denen Tabellen-Snapshots der Tabelle DATASET.TABLE erstellt und die Tabellen-Snapshots im Dataset BACKUP platziert werden.

Abfrage mit mehreren Anweisungen schreiben

In diesem Abschnitt wird beschrieben, wie Sie eine Abfrage mit mehreren Anweisungen schreiben, die einen Tabellen-Snapshot der Tabelle DATASET.TABLE mithilfe der DDL-Anweisung CREATE SNAPSHOT TABLEerstellt. Der Snapshot wird im Dataset BACKUP gespeichert und läuft nach einem Tag ab.

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

Monatliche Abfrage planen

Planen Sie Ihre Abfrage so, dass sie um 5:00 Uhr morgens am ersten Tag jedes Monats so ausgeführt wird:

bq

  1. Aktivieren Sie Cloud Shell in der Cloud Console.

    Cloud Shell aktivieren

  2. Geben Sie den folgenden bq query-Befehl ein:

    bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

  3. BigQuery plant die Abfrage.

Die Abfrage mit mehreren Anweisungen im Befehl des bq-Befehlszeilentools unterscheidet sich von der Abfrage, die Sie in der Google Cloud Console ausgeführt haben:

  • In der Abfrage des bq-Befehlszeilentools wird @run_date anstelle von current_date() verwendet. In einer geplanten Abfrage enthält der Parameter @run_date das aktuelle Datum. In einer interaktiven Abfrage wird der Parameter @run_date jedoch nicht unterstützt. Sie können current_date() anstelle von @run_date verwenden, um eine interaktive Abfrage zu testen, bevor Sie sie planen.
  • Die Abfrage des bq-Befehlszeilentools verwendet aus einem ähnlichen Grund @run_time anstelle von current_timestamp(). Der Parameter @run_time wird in interaktiven Abfragen nicht unterstützt, current_timestamp() kann jedoch statt @run_time zum Testen der interaktiven Abfrage verwendet werden.
  • Die Abfrage des bq-Befehlszeilentools verwendet einen Schrägstrich und ein doppeltes Anführungszeichen \" anstelle eines einfachen Anführungszeichens ', da einfache Anführungszeichen zum Umschließen der Abfrage verwendet werden.

Dienstkonto für die Ausführung der geplanten Abfrage konfigurieren

Die Abfrage wird derzeit mit Ihren Anmeldedaten ausgeführt. Aktualisieren Sie Ihre geplante Abfrage so, dass sie mit den Anmeldedaten des Dienstkontos snapshot-bot ausgeführt wird:

  1. Führen Sie den Befehl bq ls aus, um die Identität des geplanten Abfragejobs abzurufen:

    bq ls --transfer_config=true --transfer_location=us

    Die Ausgabe sieht dann ungefähr so aus:

    name displayName dataSourceId state
    projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

  2. Führen Sie mit der ID im Feld name den folgenden bq update-Befehl aus:

    bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

Cloud Shell bestätigt, dass die geplante Abfrage erfolgreich aktualisiert wurde.

Ergebnisse prüfen

In diesem Abschnitt wird beschrieben, wie Sie prüfen können, ob Ihre Abfrage ordnungsgemäß geplant wurde, ob beim Ausführen der Abfrage Fehler aufgetreten sind und wie die monatlichen Snapshots erstellt werden.

Geplante Abfrage ansehen

So können Sie prüfen, ob BigQuery die Abfrage für Ihre monatlichen Tabellen-Snapshots geplant hat:

Console

  1. Rufen Sie in der Google Cloud Console die Seite Geplante Abfragen auf.

    Geplante Abfragen aufrufen

  2. Klicken Sie auf Monatliche Snapshots der Tabelle TABLE.

  3. Klicken Sie auf Konfiguration.

  4. Prüfen Sie, ob der Abfragestring Ihre Abfrage enthält und ob die Abfrage am ersten Tag jedes Monats ausgeführt werden soll.

Ausführungsverlauf der geplanten Abfrage aufrufen

Nachdem die geplante Abfrage ausgeführt wurde, können Sie anhand der folgenden Informationen erkennen, ob sie erfolgreich war:

Console

  1. Rufen Sie in der Google Cloud Console die Seite Geplante Abfragen auf.

    Geplante Abfragen aufrufen

  2. Klicken Sie auf die Abfragebeschreibung Monatliche Snapshots der Tabelle TABLE.

  3. Klicken Sie auf Ausführungsverlauf.

Sie erkennen am Datum und der Uhrzeit der Ausführung der Abfrage, ob die Ausführung erfolgreich war und falls nicht, welche Fehler aufgetreten sind. Wenn Sie weitere Details zu einer bestimmten Ausführung aufrufen möchten, klicken Sie auf die entsprechende Zeile in der Tabelle Ausführungsverlauf. Im Bereich Ausführungsdetails werden zusätzliche Details angezeigt.

Tabellen-Snapshots ansehen

So prüfen Sie, ob die Tabellen-Snapshots erstellt wurden:

Console

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Öffnen Sie im Explorer-Bereich das Dataset BACKUP und prüfen Sie, ob die TABLE_YYYYMMDD-Snapshots erstellt wurden, wobei YYYYMMDD der erste Tag jedes Monats ist.

    Beispiel:

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

Nächste Schritte