Umgekehrtes ETL von Snowflake zu Spanner mit BigQuery

1. Reverse-ETL-Pipeline von Snowflake zu Cloud Spanner mit Google Cloud Storage und BigQuery erstellen

Einführung

In diesem Codelab wird eine Reverse-ETL-Pipeline von Snowflake nach Spanner erstellt. Normalerweise werden mit ETL-Pipelines (Extrahieren, Transformieren, Laden) Daten aus Betriebsdatenbanken in ein Data Warehouse wie Snowflake für Analysen verschoben. Eine Reverse-ETL-Pipeline macht das Gegenteil: Sie verschiebt kuratierte, verarbeitete Daten aus dem Data Warehouse zurück in Betriebssysteme, wo sie für Anwendungen, nutzerorientierte Funktionen oder Echtzeitentscheidungen verwendet werden können.

Ziel ist es, ein aggregiertes Dataset aus einer Snowflake Iceberg-Tabelle in Spanner zu verschieben, eine global verteilte relationale Datenbank, die sich ideal für Anwendungen mit hoher Verfügbarkeit eignet.

Dazu werden Google Cloud Storage (GCS) und BigQuery als Zwischenschritte verwendet. Hier sehen Sie eine Aufschlüsselung des Datenflusses und die Gründe für diese Architektur:

1. Snowflake für Google Cloud Storage (GCS) im Iceberg-Format:

• Der erste Schritt besteht darin, die Daten aus Snowflake in einem offenen, genau definierten Format zu exportieren. Die Tabelle wird im Apache Iceberg-Format exportiert. Bei diesem Vorgang werden die zugrunde liegenden Daten als eine Reihe von Parquet-Dateien und die Metadaten der Tabelle (Schema, Partitionen, Dateispeicherorte) als JSON- und Avro-Dateien geschrieben. Durch das Bereitstellen dieser vollständigen Tabellenstruktur in GCS sind die Daten portierbar und für jedes System zugänglich, das das Iceberg-Format unterstützt.

2. Iceberg-Tabellen in GCS in eine externe BigLake-Tabelle in BigQuery konvertieren:

• Anstatt die Daten direkt aus GCS in Spanner zu laden, wird BigQuery als leistungsstarkes Zwischenmedium verwendet. Sie erstellen in BigQuery eine externe BigLake-Tabelle, die direkt auf die Iceberg-Metadatendatei in GCS verweist. Dieser Ansatz bietet verschiedene Vorteile:
• Keine Datenduplizierung:BigQuery liest die Tabellenstruktur aus den Metadaten und fragt die Parquet-Datendateien direkt ab, ohne sie aufzunehmen. Das spart viel Zeit und Speicherkosten.
• Föderierte Abfragen:Damit können komplexe SQL-Abfragen für GCS-Daten ausgeführt werden, als wären sie eine native BigQuery-Tabelle.

3. BigQuery zu Spanner:

• Der letzte Schritt besteht darin, die Daten aus BigQuery in Spanner zu verschieben. Dazu verwenden Sie eine leistungsstarke Funktion in BigQuery, die als EXPORT DATA-Abfrage bezeichnet wird. Dies ist der Schritt „Reverse-ETL“.
• Betriebsbereitschaft:Spanner ist für transaktionale Arbeitslasten konzipiert und bietet Anwendungen eine hohe Konsistenz und Verfügbarkeit. Durch die Übertragung der Daten in Spanner werden sie für nutzerorientierte Anwendungen, APIs und andere Betriebssysteme zugänglich, die Punktabfragen mit niedriger Latenz erfordern.
• Skalierbarkeit:Mit diesem Muster lässt sich die Analyseleistung von BigQuery nutzen, um große Datasets zu verarbeiten und die Ergebnisse dann effizient über die global skalierbare Infrastruktur von Spanner bereitzustellen.

Dienste und Terminologie

• Snowflake: Eine Cloud-Datenplattform, die ein Data Warehouse-as-a-Service bietet.
• Spanner: Eine vollständig verwaltete, global verteilte relationale Datenbank.
• Google Cloud Storage: Das Blob-Speicherangebot von Google Cloud.
• BigQuery: Ein vollständig verwaltetes, serverloses Data Warehouse für die Analyse.
• Iceberg: Ein von Apache definiertes offenes Tabellenformat, das eine Abstraktion über gängige Open-Source-Datendateiformate bietet.
• Parquet: Ein spaltenorientiertes binäres Open-Source-Dateiformat von Apache.

Lerninhalte

• Daten in Snowflake laden
• GCS-Bucket erstellen
• Snowflake-Tabelle im Iceberg-Format in GCS exportieren
• Spanner-Instanz einrichten
• BigLake-Tabellen in BigQuery in Spanner laden

2. Einrichtung, Anforderungen und Einschränkungen

Vorbereitung

• Ein Snowflake-Konto
• Für den Export von BigQuery nach Spanner ist ein Google Cloud-Konto mit einer Reservierung auf Enterprise-Ebene oder höher für BigQuery erforderlich.
• Zugriff auf die Google Cloud Console über einen Webbrowser
• Ein Terminal zum Ausführen von Google Cloud CLI-Befehlen
• Wenn in Ihrer Google Cloud-Organisation die Richtlinie iam.allowedPolicyMemberDomains aktiviert ist, muss ein Administrator möglicherweise eine Ausnahme gewähren, damit Dienstkonten aus externen Domains verwendet werden können. Das wird gegebenenfalls in einem späteren Schritt behandelt.

Beschränkungen

Es ist wichtig, sich über bestimmte Einschränkungen und Inkompatibilitäten von Datentypen im Klaren zu sein, die in dieser Pipeline auftreten können.

Snowflake zu Iceberg

Die Datentypen von Spalten unterscheiden sich zwischen Snowflake und Iceberg. Informationen zum Übersetzen zwischen ihnen finden Sie in der Snowflake-Dokumentation.

Iceberg zu BigQuery

Bei der Verwendung von BigQuery zum Abfragen von Iceberg-Tabellen gelten einige Einschränkungen. Eine vollständige Liste finden Sie in der BigQuery-Dokumentation. Typen wie BIGNUMERIC, INTERVAL, JSON, RANGE oder GEOGRAPHY werden derzeit nicht unterstützt.

BigQuery für Spanner

Der Befehl EXPORT DATA von BigQuery nach Spanner unterstützt nicht alle BigQuery-Datentypen. Wenn Sie eine Tabelle mit den folgenden Typen exportieren, tritt ein Fehler auf:

• STRUCT
• GEOGRAPHY
• DATETIME
• RANGE
• TIME

Wenn im BigQuery-Projekt der GoogleSQL-Dialekt verwendet wird, werden die folgenden numerischen Typen auch nicht für den Export nach Spanner unterstützt:

• BIGNUMERIC

Eine vollständige und aktuelle Liste der Einschränkungen finden Sie in der offiziellen Dokumentation: Einschränkungen beim Exportieren nach Spanner.

Snowflake

Für dieses Codelab können Sie ein vorhandenes Snowflake-Konto verwenden oder ein Konto für den kostenlosen Testzeitraum einrichten.

Google Cloud Platform-IAM-Berechtigungen

Das Google-Konto benötigt die folgenden Berechtigungen, um alle Schritte in diesem Codelab auszuführen.

Dienstkonten
iam.serviceAccountKeys.create	Ermöglicht das Erstellen von Dienstkonten.
Spanner
spanner.instances.create	Ermöglicht das Erstellen einer neuen Spanner-Instanz.
spanner.databases.create	Ermöglicht das Ausführen von DDL-Anweisungen zum Erstellen
spanner.databases.updateDdl	Ermöglicht das Ausführen von DDL-Anweisungen zum Erstellen von Tabellen in der Datenbank.
Google Cloud Storage
storage.buckets.create	Ermöglicht das Erstellen eines neuen GCS-Buckets zum Speichern der exportierten Parquet-Dateien.
storage.objects.create	Ermöglicht das Schreiben der exportierten Parquet-Dateien in den GCS-Bucket.
storage.objects.get	Ermöglicht BigQuery, die Parquet-Dateien aus dem GCS-Bucket zu lesen.
storage.objects.list	Ermöglicht BigQuery, die Parquet-Dateien im GCS-Bucket aufzulisten.
Dataflow
Dataflow.workitems.lease	Ermöglicht das Übernehmen von Arbeitselementen aus Dataflow.
Dataflow.workitems.sendMessage	Ermöglicht dem Dataflow-Worker, Nachrichten an den Dataflow-Dienst zurückzusenden.
Logging.logEntries.create	Ermöglicht Dataflow-Workern, Logeinträge in Google Cloud Logging zu schreiben.

Sie können auch vordefinierte Rollen verwenden, die diese Berechtigungen enthalten.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Wiederverwendbare Eigenschaften einrichten

In diesem Lab werden einige Werte wiederholt benötigt. Um dies zu vereinfachen, legen wir diese Werte auf Shell-Variablen fest, die später verwendet werden.

• GCP_REGION: Die spezifische Region, in der sich die GCP-Ressourcen befinden. Eine Liste der Regionen finden Sie hier.
• GCP_PROJECT: Die zu verwendende GCP-Projekt-ID.
• GCP_BUCKET_NAME: Der Name des GCS-Buckets, der erstellt werden soll und in dem die Datendateien gespeichert werden.

Bucket-Namen müssen global eindeutig sein. Ein häufiges Muster ist: [organization_name]-[project_id]-[environment]-[purpose].

export GCP_REGION = <GCP REGION HERE> 
export GCP_PROJECT= <GCP PROJECT HERE>
export GCS_BUCKET_NAME = <GCS BUCKET NAME HERE>
export SPANNER_INSTANCE = <SPANNER INSTANCE ID HERE>
export SPANNER_DB = <SPANNER DATABASE ID HERE>

Beachten Sie, dass diese Umgebungsvariablen nur in der Shell-Instanz festgelegt werden, in der sie ausgeführt werden. Wenn Sie die Befehle in diesem Lab später in einer anderen Shell-Instanz ausführen, müssen Sie sie noch einmal festlegen.

Google Cloud-Projekt

Ein Projekt ist eine Grundeinheit für die Organisation in Google Cloud. Wenn ein Administrator einen solchen Schlüssel zur Verfügung gestellt hat, kann dieser Schritt übersprungen werden.

Ein Projekt kann mit der CLI so erstellt werden:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Weitere Informationen zum Erstellen und Verwalten von Projekten

Spanner einrichten

Wenn Sie Spanner verwenden möchten, müssen Sie eine Instanz und eine Datenbank bereitstellen. Weitere Informationen zum Konfigurieren und Erstellen einer Spanner-Instanz

Instanz erstellen

gcloud spanner instances create $SPANNER_INSTANCE \
--config=regional-$GCP_REGION \
--description="Codelabs Snowflake RETL" \
--processing-units=100 \
--edition=ENTERPRISE

Datenbank erstellen

gcloud spanner databases create $SPANNER_DB \
--instance=$SPANNER_INSTANCE

3. Google Cloud Storage-Bucket erstellen

Google Cloud Storage (GCS) wird verwendet, um die von Snowflake generierten Parquet-Datendateien und Iceberg-Metadaten zu speichern. Dazu muss zuerst ein neuer Bucket als Dateiziel erstellt werden. Führen Sie die folgenden Schritte in einem Terminalfenster auf einem lokalen Computer aus.

Bucket erstellen

Verwenden Sie den folgenden Befehl, um einen Speicher-Bucket in einer bestimmten Region (z.B. „us-central1“) zu erstellen.

gcloud storage buckets create gs://$GCS_BUCKET_NAME --location=$GCP_REGION

Bucket-Erstellung prüfen

Wenn der Befehl erfolgreich ausgeführt wurde, können Sie das Ergebnis prüfen, indem Sie alle Buckets auflisten. Der neue Bucket sollte in der Ergebnisliste angezeigt werden. Bucket-Verweise werden normalerweise mit dem Präfix gs:// vor dem Bucket-Namen angezeigt.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Schreibberechtigungen testen

Mit diesem Schritt wird sichergestellt, dass die lokale Umgebung richtig authentifiziert ist und die erforderlichen Berechtigungen zum Schreiben von Dateien in den neu erstellten Bucket hat.

echo "Hello, GCS" | gcloud storage cp - gs://$GCS_BUCKET_NAME/hello.txt

Hochgeladene Datei prüfen

Listen Sie die Objekte im Bucket auf. Der vollständige Pfad der gerade hochgeladenen Datei sollte angezeigt werden.

gcloud storage ls gs://$GCS_BUCKET_NAME

Es sollte folgende Ausgabe angezeigt werden:

gs://$GCS_BUCKET_NAME/hello.txt

Mit gcloud storage cat können Sie den Inhalt eines Objekts in einem Bucket aufrufen.

gcloud storage cat gs://$GCS_BUCKET_NAME/hello.txt

Der Inhalt der Datei sollte sichtbar sein:

Hello, GCS

Testdatei bereinigen

Der Cloud Storage-Bucket ist jetzt eingerichtet. Die temporäre Testdatei kann jetzt gelöscht werden.

gcloud storage rm gs://$GCS_BUCKET_NAME/hello.txt

Die Ausgabe sollte das Löschen bestätigen:

Removing gs://$GCS_BUCKET_NAME/hello.txt...
/ [1 objects]
Operation completed over 1 objects.

4. Export von Snowflake nach GCS

In diesem Lab verwenden Sie das TPC-H-Dataset, einen Branchenstandard-Benchmark für Entscheidungsunterstützungssysteme. Das Schema bildet eine realistische Geschäftsumgebung mit Kunden, Bestellungen, Lieferanten und Teilen ab und eignet sich daher perfekt, um ein reales Analyse- und Datenübertragungsszenario zu demonstrieren. Dieser Datensatz ist standardmäßig in allen Snowflake-Konten verfügbar.

Anstatt die Rohdaten der normalisierten TPC-H-Tabellen zu verwenden, erstellen Sie eine neue aggregierte Tabelle. In dieser neuen Tabelle werden Daten aus den Tabellen orders, customer und nation zusammengeführt, um eine denormalisierte, zusammengefasste Ansicht der nationalen Gesamtumsätze zu erstellen. Dieser Schritt der Vorabaggregation ist in der Analyse üblich, da er die Daten für einen bestimmten Anwendungsfall vorbereitet – in diesem Fall für die Nutzung durch eine operative Anwendung.

Snowflake den Zugriff auf Google Cloud Storage erlauben

Damit Snowflake Daten in den GCS-Bucket schreiben kann, müssen zwei Dinge erstellt werden: ein externes Volume und die erforderlichen Berechtigungen.

• Ein externes Volume ist ein Snowflake-Objekt, das eine sichere Verbindung zu einem bestimmten Speicherort in einem GCS-Bucket bietet. Darin werden keine Daten gespeichert, sondern die Konfiguration, die Snowflake für den Zugriff auf Cloud-Speicher benötigt.
• Aus Sicherheitsgründen sind Cloud Storage-Buckets standardmäßig privat. Wenn ein externes Volume erstellt wird, generiert Snowflake ein dediziertes Dienstkonto. Diesem Dienstkonto müssen Berechtigungen zum Lesen aus dem Bucket und zum Schreiben in den Bucket gewährt werden.

Datenbank erstellen

1. Bewegen Sie den Mauszeiger im Menü auf der linken Seite unter Horizon Catalog auf Catalog und klicken Sie dann auf Database Explorer.
2. Klicken Sie auf der Seite Datenbanken rechts oben auf die Schaltfläche + Datenbank.
3. Benennen Sie die neue Datenbank mit codelabs_retl_db.

Arbeitsblatt erstellen

Um SQL-Befehle für die Datenbank auszuführen, sind Arbeitsblätter erforderlich.

So erstellen Sie ein Arbeitsblatt:

1. Bewegen Sie den Mauszeiger im Menü auf der linken Seite unter Mit Daten arbeiten auf Projekte und klicken Sie dann auf Arbeitsbereiche.
2. Klicken Sie in der Seitenleiste Meine Arbeitsbereiche auf die Schaltfläche + Neu hinzufügen und wählen Sie SQL-Datei aus.

Alle weiteren SQL-Befehle für Snowflake können ausgeführt werden, indem der Inhalt dieser einen Datei ersetzt wird. Absatzteile der Datei können ausgeführt werden, indem Sie den Cursor auf den entsprechenden Absatz setzen und Strg + Eingabetaste auf der Tastatur drücken.

Externes Volume erstellen

Führen Sie den folgenden Befehl in einem Snowflake-Arbeitsblatt aus, um das Volume zu erstellen.

Beachten Sie, dass Snowflake für Google Cloud Storage-Pfade das Präfix gcs:// und nicht gs:// verwendet.

Da der folgende SQL-Code nicht als Teil der Shell-Instanz mit den festgelegten Umgebungsvariablen ausgeführt wird, müssen Sie die Werte für Ihren GCS-Bucket-Namen ersetzen, wenn er angezeigt wird.

CREATE EXTERNAL VOLUME codelabs_retl_ext_vol
STORAGE_LOCATIONS = 
(
  (
    NAME = 'codelabs_retl_ext_vol'
    STORAGE_PROVIDER = 'GCS'
    STORAGE_BASE_URL = 'gcs://<Your bucket name>/snowflake_extvol'
  )
); 

Snowflake-Dienstkonto abrufen

DESC (describe) das neu erstellte externe Volume, um das eindeutige Dienstkonto zu erhalten, das Snowflake dafür generiert hat.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;

1. Suchen Sie im Ergebnisbereich nach den JSON-Attributen und nach dem Eintrag property_value, der einen JSON-String enthält, der mit "NAME":"codelabs_retl_ext_vol" beginnt.
2. Suchen Sie im JSON-Objekt nach dem Attribut STORAGE_GCP_SERVICE_ACCOUNT und kopieren Sie seinen Wert (er sieht wie eine E-Mail-Adresse aus). Dies ist die Dienstkonto-ID, die Zugriff auf den GCS-Bucket benötigt.
3. Speichern Sie dieses Dienstkonto in einer Umgebungsvariablen in Ihrer Shell-Instanz, um es später wiederzuverwenden.

export GCP_SERVICE_ACCOUNT=<Your service account>

Hinweis: Wenn in Ihrer Google Cloud-Organisation die Einschränkung iam.allowedPolicyMemberDomains erzwungen wird, muss die Domain dieses Dienstkontos (der Teil nach dem Symbol @) von einem Sicherheitsadministrator zur Zulassungsliste hinzugefügt werden.

Snowflake GCS-Berechtigungen erteilen

Jetzt muss dem Snowflake-Dienstkonto die Berechtigung zum Schreiben in den GCS-Bucket erteilt werden.

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.objectAdmin"

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.legacyBucketReader"

Zugriff in Snowflake überprüfen

Führen Sie in der Snowflake-Arbeitsmappe den folgenden Befehl aus, um zu prüfen, ob Snowflake jetzt eine Verbindung zum GCS-Bucket herstellen kann.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

Das Ergebnis sollte ein JSON-Objekt mit "success":true sein.

Weitere Informationen zu externen Volumes in Snowflake finden Sie in der offiziellen Dokumentation.

Beispieldaten für Bestellungen exportieren

Jetzt können Sie eine Iceberg-Tabelle in Snowflake erstellen. Mit dem folgenden Befehl wird Snowflake angewiesen, eine Abfrage auszuführen und die Ergebnisse im Iceberg-Format in GCS zu speichern. Die Datendateien sind im Parquet-Format und die Metadaten im Avro- und JSON-Format. Alle werden am Speicherort gespeichert, der durch das codelabs_retl_ext_vol-externe Volume definiert ist.

Datenbank erstellen

1. Bewegen Sie den Mauszeiger im Menü auf der linken Seite unter Horizon Catalog auf Catalog und klicken Sie dann auf Database Explorer.
2. Klicken Sie auf der Seite Datenbanken rechts oben auf die Schaltfläche + Datenbank.
3. Benennen Sie die neue Datenbank mit codelabs_retl_db.

USE DATABASE codelabs_retl_db; 

CREATE ICEBERG TABLE REGIONAL_SALES_ICEBERG (
    NATION_NAME STRING,
    MARKET_SEGMENT STRING,
    ORDER_YEAR INTEGER,
    ORDER_PRIORITY STRING,
    TOTAL_ORDER_COUNT INTEGER,
    TOTAL_REVENUE NUMBER(24,2),
    UNIQUE_CUSTOMER_COUNT INTEGER
) 
EXTERNAL_VOLUME = 'codelabs_retl_ext_vol'
CATALOG = 'SNOWFLAKE'
BASE_LOCATION = 'regional_sales_iceberg'
AS (
    SELECT 
        n.n_name AS nation_name,
        c.c_mktsegment AS market_segment,
        YEAR(o.o_orderdate) AS order_year,
        o.o_orderpriority AS order_priority,
        COUNT(o.o_orderkey) AS total_order_count,
        ROUND(SUM(o.o_totalprice), 2) AS total_revenue,
        COUNT(DISTINCT c.c_custkey) AS unique_customer_count
    FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders AS o
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.customer AS c 
        ON o.o_custkey = c.c_custkey
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.nation AS n
        ON c.c_nationkey = n.n_nationkey
    GROUP BY 
        n.n_name, 
        c.c_mktsegment, 
        YEAR(o.o_orderdate), 
        o.o_orderpriority
);

Weitere Informationen zum Erstellen und Verwalten von Iceberg-Tabellen mit Snowflake finden Sie in der offiziellen Dokumentation.

Daten in der GCP überprüfen

Sehen Sie sich jetzt den GCS-Bucket an. Die von Snowflake erstellten Dateien sollten sichtbar sein. Das bestätigt, dass der Export erfolgreich war. Die Iceberg-Metadaten befinden sich im Ordner metadata und die eigentlichen Daten als Parquet-Dateien im Ordner data.

gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**"

Die genauen Dateinamen variieren, aber die Struktur sollte so aussehen:

gs://$GCS_BUCKET_NAME/snowflake_extvol/
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/data/snow_cbsKIRmdDmo_wLg128fugxg_0_2_009.parquet
...
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/metadata/00001-62f831ff-6708-4494-94c5-c891b7ad447f.metadata.json
...

Die Daten wurden jetzt aus Snowflake in Google Cloud Storage im Iceberg-Format kopiert.

Speichern wir die Datei „metadata.json“ in einer Umgebungsvariablen, da wir sie später noch benötigen.

export GCS_METADATA_JSON=$(gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**" | grep .metadata.json)

5. Externe BigQuery-Tabelle konfigurieren

Nachdem sich die Iceberg-Tabelle in Google Cloud Storage befindet, muss sie für BigQuery zugänglich gemacht werden. Dazu können Sie eine externe BigLake-Tabelle erstellen.

BigLake ist eine Speicher-Engine, mit der Tabellen in BigQuery erstellt werden können, die Daten direkt aus externen Quellen wie Google Cloud Storage lesen. Für dieses Lab ist es die Schlüsseltechnologie, die es BigQuery ermöglicht, die gerade exportierte Iceberg-Tabelle zu verstehen, ohne die Daten aufnehmen zu müssen.

Dazu sind zwei Komponenten erforderlich:

1. Cloud-Ressourcenverbindung:Dies ist eine sichere Verbindung zwischen BigQuery und GCS. Für die Authentifizierung wird ein spezielles Dienstkonto verwendet, damit BigQuery die erforderlichen Berechtigungen zum Lesen der Dateien aus dem GCS-Bucket hat.
2. Definition einer externen Tabelle:Damit wird BigQuery mitgeteilt, wo die Metadatendatei der Iceberg-Tabelle in GCS zu finden ist und wie sie interpretiert werden soll.

Verbindung zu Google Cloud Storage konfigurieren

Zuerst wird die Verbindung erstellt, über die BigQuery auf GCS zugreifen kann. Mit diesem Befehl wird eine Verbindungsressource in BigQuery erstellt.

bq mk \
  --connection \
  --project_id=$GCP_PROJECT \
  --location=$GCP_REGION \
  --connection_type=CLOUD_RESOURCE \
  codelabs-retl-connection

Bei Erfolg sollte die Ausgabe so aussehen:

Connection 12345678.region.codelabs-retl-connection successfully created

Weitere Informationen zu Cloud-Ressourcenverbindungen in BigQuery finden Sie in der Google Cloud-Dokumentation.

BigQuery-Verbindung zum Lesen von Daten autorisieren

Die neue BigQuery-Verbindung hat ein eigenes Dienstkonto, das die Berechtigung zum Lesen von Daten aus dem Google Cloud Storage-Bucket benötigt.

1. Dienstkonto der Verbindung abrufen

Rufen Sie zuerst die Dienstkonto-ID aus der gerade erstellten Verbindung ab:

bq show \
  --location $GCP_REGION \
  --connection codelabs-retl-connection

In den Ergebnissen wird eine Tabelle mit übereinstimmenden Verbindungen angezeigt.

Legen Sie die serviceAccountId in einer Umgebungsvariablen fest, die später verwendet wird.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. Berechtigungen gewähren

Autorisieren Sie das Dienstkonto, Daten im GCS-Bucket aufzurufen, indem Sie den folgenden Befehl ausführen.

gcloud storage buckets add-iam-policy-binding \
  gs://$GCS_BUCKET_NAME \
  --member serviceAccount:$GCP_BQ_SERVICE_ACCOUNT \
  --role roles/storage.objectViewer

Externe Tabelle erstellen

Erstellen Sie nun die externe BigLake-Tabelle in BigQuery. Mit diesem Befehl werden keine Daten verschoben. Es wird lediglich ein Zeiger auf die vorhandenen Daten in GCS erstellt. Sie benötigen den Pfad zu einer der .metadata.json-Dateien, die von Snowflake erstellt wurden.

Ersetzen Sie im folgenden Befehl den Wert external_table_definition durch den entsprechenden Pfad zu Ihrer Datei „metadata.json“.

Achten Sie darauf, dass diese Tabelle in derselben Region wie die Verbindung aus dem vorherigen Schritt erstellt wird.

bq mk --dataset --location=$GCP_REGION codelabs_retl

bq mk \
    --table \
    --location=$GCP_REGION \
--external_table_definition=ICEBERG=$GCS_METADATA_JSON@projects/$GCP_PROJECT/locations/$GCP_REGION/connections/codelabs-retl-connection \
    codelabs_retl.regional_sales

Daten in BigQuery überprüfen

Diese Tabelle kann jetzt mit Standard-SQL abgefragt werden, genau wie jede andere BigQuery-Tabelle. BigQuery verwendet die Verbindung, um die Parquet-Dateien aus GCS im laufenden Betrieb zu lesen.

bq query \
  --location=$GCP_REGION \
  --nouse_legacy_sql "SELECT * FROM \`$GCP_PROJECT.codelabs_retl.regional_sales\` LIMIT 10;"

6. Daten aus BigQuery in Spanner importieren: Der letzte Schritt

Der letzte und wichtigste Teil der Pipeline ist erreicht: die Daten aus der BigLake-Tabelle in Spanner verschieben. Dies ist der Schritt „Reverse ETL“, bei dem die Daten, die im Data Warehouse verarbeitet und kuratiert wurden, in ein Betriebssystem geladen werden, damit sie von Anwendungen verwendet werden können.

Spanner ist eine vollständig verwaltete, global verteilte relationale Datenbank. Sie bietet die Transaktionskonsistenz einer herkömmlichen relationalen Datenbank, aber mit der horizontalen Skalierbarkeit einer NoSQL-Datenbank. Daher ist es eine ideale Wahl für die Entwicklung skalierbarer, hochverfügbarer Anwendungen.

Der Prozess läuft so ab:

1. Erstellen Sie in der Spanner-Datenbank ein Tabellenschema, das der Struktur der Daten entspricht.
2. Führen Sie eine BigQuery-EXPORT DATA-Abfrage aus, um die Daten aus der BigLake-Tabelle direkt in die Spanner-Tabelle zu laden.

Spanner-Tabelle erstellen

Bevor Sie Daten aus BigQuery übertragen können, muss in Spanner eine Zieltabelle mit einem kompatiblen Schema erstellt werden.

gcloud spanner databases ddl update $SPANNER_DB \
  --instance=$SPANNER_INSTANCE \
  --ddl="$(cat <<EOF
CREATE TABLE regional_sales (
    nation_name STRING(MAX),
    market_segment STRING(MAX),
    order_year INT64,
    order_priority STRING(MAX),
    total_order_count INT64,
    total_revenue NUMERIC,
    unique_customer_count INT64
) PRIMARY KEY (nation_name, market_segment, order_year, order_priority);
EOF
)"

Daten aus BigQuery exportieren

Das ist der letzte Schritt. Nachdem die Quelldaten in einer BigQuery BigLake-Tabelle verfügbar sind und die Zieltabelle in Spanner erstellt wurde, ist die eigentliche Datenübertragung überraschend einfach. Es wird eine einzelne BigQuery-SQL-Abfrage verwendet: EXPORT DATA.

Diese Abfrage wurde speziell für solche Szenarien entwickelt. Damit lassen sich Daten effizient aus einer BigQuery-Tabelle (einschließlich externer Tabellen wie der BigLake-Tabelle) an ein externes Ziel exportieren. In diesem Fall ist das Ziel die Spanner-Tabelle.

bq query --location=$GCP_REGION --use_legacy_sql=false <<EOF
EXPORT DATA OPTIONS (
uri="https://spanner.googleapis.com/projects/${GCP_PROJECT}/instances/${SPANNER_INSTANCE}/databases/${SPANNER_DB}",
  format='CLOUD_SPANNER',
  spanner_options="""{ 
      "table": "regional_sales", 
      "priority": "HIGH" 
      }"""
) AS 
SELECT * FROM \`${PROJECT_ID}.codelabs_retl.regional_sales\`
EOF

Wenn die Abfrage abgeschlossen ist, sollte im Bereich „Ergebnisse“ die Meldung „Aktualisierung abgeschlossen“ angezeigt werden.

7. Daten in Cloud Spanner überprüfen

Glückwunsch! Eine vollständige Reverse-ETL-Pipeline wurde erfolgreich erstellt und ausgeführt. Im letzten Schritt müssen Sie prüfen, ob die Daten wie erwartet in Spanner angekommen sind.

gcloud spanner databases execute-sql \
  --instance=$SPANNER_INSTANCE \
  $SPANNER_DB \
  --sql='SELECT * FROM regional_sales LIMIT 10'

Die importierten Beispieldaten werden wie gewünscht angezeigt:

nation_name  market_segment  order_year  order_priority   total_order_count  total_revenue  unique_customer_count
ALGERIA      AUTOMOBILE      1992        1-URGENT         375                59232423.66    298
ALGERIA      AUTOMOBILE      1992        2-HIGH           328                47371891.08    269
ALGERIA      AUTOMOBILE      1992        3-MEDIUM         346                52823195.87    262
ALGERIA      AUTOMOBILE      1992        4-NOT SPECIFIED  365                52935998.34    288
ALGERIA      AUTOMOBILE      1992        5-LOW            380                54920263.68    293
ALGERIA      AUTOMOBILE      1993        1-URGENT         394                63145618.78    312
ALGERIA      AUTOMOBILE      1993        2-HIGH           340                50737488.4     277
ALGERIA      AUTOMOBILE      1993        3-MEDIUM         383                55871057.46    298
ALGERIA      AUTOMOBILE      1993        4-NOT SPECIFIED  365                56424662.05    291
ALGERIA      AUTOMOBILE      1993        5-LOW            363                54673249.06    283

Die Lücke zwischen der Welt der Analyse- und Betriebsdaten wurde erfolgreich geschlossen.

8. Klären

Spanner bereinigen

Spanner-Datenbank und ‑Instanz löschen

gcloud spanner instances delete $SPANNER_INSTANCE

GCS bereinigen

GCS-Bucket löschen, der zum Hosten der Daten erstellt wurde

gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

BigQuery bereinigen

bq rm -r codelabs_retl
bq rm --connection --location=$GCP_REGION codelabs-retl-connection

Snowflake bereinigen

Datenbank löschen

1. Bewegen Sie den Mauszeiger im Menü auf der linken Seite unter Horizon Catalog auf Katalog und klicken Sie dann auf Database Explorer.
2. Klicken Sie rechts neben der Datenbank CODELABS_RETL_DB auf das Dreipunkt-Menü ..., um die Optionen zu maximieren, und wählen Sie Löschen aus.
3. Wählen Sie im Bestätigungsdialogfeld, das eingeblendet wird, Drop Database (Datenbank löschen) aus.

Arbeitsmappen löschen

4. Bewegen Sie den Mauszeiger im Menü auf der linken Seite unter Mit Daten arbeiten auf Projekte und klicken Sie dann auf Arbeitsbereiche.
5. Bewegen Sie den Mauszeiger in der Seitenleiste Mein Arbeitsbereich auf die verschiedenen Arbeitsbereichsdateien, die Sie für dieses Lab verwendet haben, um die zusätzlichen Optionen ... aufzurufen, und klicken Sie darauf.
6. Wählen Sie Löschen und dann im Bestätigungsdialogfeld noch einmal Löschen aus.
7. Wiederholen Sie diesen Schritt für alle SQL-Arbeitsbereichsdateien, die Sie für dieses Lab erstellt haben.

Externe Volumes löschen

8. Bewegen Sie den Mauszeiger im Menü auf der linken Seite unter Horizon-Katalog auf Katalog und klicken Sie dann auf Externe Daten.
9. Klicken Sie rechts neben CODELABS_RETL_EXT_VOL auf , wählen Sie Externes Volume entfernen aus und klicken Sie im Bestätigungsdialogfeld noch einmal auf Externes Volume entfernen.

9. Glückwunsch

Herzlichen Glückwunsch zum Abschluss des Codelabs.

Behandelte Themen

• Daten in Snowflake laden
• GCS-Bucket erstellen
• Snowflake-Tabelle im CSV-Format in GCS exportieren
• Spanner-Instanz einrichten
• CSV-Tabellen mit Dataflow in Spanner laden