Zero-ETL-Datenföderation von BigQuery zu AlloyDB

1. Übersicht

In Teil 1 haben wir mithilfe von Knowledge Catalog und DataScan chaotische, unstrukturierte PDFs in übersichtliche, intelligente und strukturierte Tabellen in BigQuery umgewandelt. Jetzt haben wir ein robustes Data Warehouse.

Zur Erinnerung: Im Lab 1 haben wir den Anwendungsfall eines fiktiven Frozen Yogurt-Franchise-Unternehmens betrachtet und 400 unstrukturierte PDF-Dateien mit Text, Tabellen und Bildern in übersichtlich strukturierte BigQuery-Tabellen konvertiert. Dabei wurden Beziehungen zwischen den Tabellen automatisch mit BigQuery Knowledge Catalog und Dataplex abgeleitet.

Aufgaben

In dieser Sitzung richten wir AlloyDB for PostgreSQL ein und tun etwas Magisches: Wir führen unsere BigQuery-Daten direkt in AlloyDB zusammen. Das bedeutet, dass unsere Transaktions-App unsere Warehouse-Daten in Echtzeit abfragen kann, ohne dass Daten kopiert oder dupliziert werden müssen.

Als Entwickler müssen Sie sich in dieser Phase folgende Frage stellen:

„Wenn die Daten bereits in BigQuery sind, warum dann AlloyDB verwenden? Warum führt die Anwendung nicht einfach eine SELECT-Anweisung direkt für BigQuery aus?“

Das hat folgende Gründe:

Mit Lakehouse Federation können Sie die Abfrage-Engine von AlloyDB verwenden, um die Transaktions- und Analysearbeitslasten Ihrer Anwendung über dieselbe Schnittstelle zu verarbeiten. Sie können diese Daten auch in AlloyDB materialisieren oder importieren, um schneller darauf zuzugreifen und sie in Ihren Anwendungen zu verwenden. So können Sie AlloyDB AI und die spaltenbasierte Engine nutzen.

Sie können AlloyDB als transaktionale Datenbank verwenden und gleichzeitig große Datenmengen in BigQuery oder BigLake speichern. Ihre Anwendungen werden in der Regel unabhängig in beide Systeme eingebunden, um auf Daten in den verschiedenen Google Cloud-Diensten zuzugreifen. Mit der Lakehouse-Föderation für AlloyDB können Sie die Unterstützung für föderierte Abfragen von AlloyDB nutzen, die als Foreign Data Wrapper implementiert ist, um über eine SQL-Schnittstelle in AlloyDB auf BigQuery- und AlloyDB-Daten zuzugreifen.

Anstatt eine anfällige ETL-Pipeline zu erstellen, um die BigQuery-Daten aus AlloyDB abzufragen, verwenden wir föderierte Abfragen. AlloyDB fungiert als einheitlicher Endpunkt und greift bei Bedarf nahtlos auf BigQuery zu.

Legen wir los!

Lerninhalte

• AlloyDB-Cluster, ‑Instanz und ‑Netzwerk mit einem Klick einrichten
• Erweiterung einrichten, um sich auf die Föderation vorzubereiten
• Föderation von BigQuery zu AlloyDB einrichten
• Ausprobieren

Voraussetzungen

• Ein Browser, z. B. Chrome oder Firefox.
• Google Cloud-Projekt mit aktivierter Abrechnungsfunktion.
• Grundkenntnisse in SQL

2. Hinweis

Projekt erstellen

1. Wählen Sie in der Google Cloud Console auf der Seite zur Projektauswahl ein Google Cloud-Projekt aus oder erstellen Sie eines.
2. Die Abrechnung für das Cloud-Projekt muss aktiviert sein. So prüfen Sie, ob die Abrechnung für ein Projekt aktiviert ist.

In diesem Lab gibt es nur wenige Funktionen, die Sie auch ohne Rechnungskonto nutzen können. Wenn Sie die praktischen Übungen in dieser Funktion nutzen möchten, beachten Sie die Hinweise, die unter „KEIN RECHNUNGSKONTO?“ aufgeführt sind. Achten Sie darauf.

3. Sie verwenden Cloud Shell, eine Befehlszeilenumgebung, die in Google Cloud ausgeführt wird. Klicken Sie oben in der Google Cloud Console auf „Cloud Shell aktivieren“.

4. Sobald die Verbindung mit der Cloud Shell hergestellt ist, können Sie mit dem folgenden Befehl prüfen, ob Sie bereits authentifiziert sind und für das Projekt schon Ihre Projekt-ID eingestellt ist:

gcloud auth list

5. Führen Sie in Cloud Shell den folgenden Befehl aus, um zu prüfen, ob der gcloud-Befehl Ihr Projekt kennt.

gcloud config list project

6. Wenn Sie sich authentifizieren möchten,

gcloud auth login

7. Wenn Ihr Projekt nicht festgelegt ist, verwenden Sie den folgenden Befehl, um es festzulegen:

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project <YOUR_PROJECT_ID>

8. Aktivieren Sie die erforderlichen APIs: Führen Sie diesen Befehl aus, um alle erforderlichen APIs zu aktivieren:

gcloud services enable alloydb.googleapis.com

Fallstricke und Fehlerbehebung

Das „Geisterprojekt“-Syndrom	Sie haben gcloud config set project ausgeführt, sehen sich in der Console-UI aber ein anderes Projekt an. Prüfen Sie die Projekt-ID im Drop-down-Menü oben links.
Die Abrechnungsbarrikade	Sie haben das Projekt aktiviert, aber das Rechnungskonto vergessen. AlloyDB ist eine leistungsstarke Engine, die nicht startet, wenn der „Tank“ (Abrechnung) leer ist.
Verzögerung bei der API-Weitergabe	Sie haben auf „APIs aktivieren“ geklickt, aber in der Befehlszeile wird weiterhin Service Not Enabled angezeigt. Warte 60 Sekunden. Es dauert einen Moment, bis die Cloud ihre Neuronen aktiviert hat.
Kontingent -Quags	Wenn Sie ein brandneues Testkonto verwenden, erreichen Sie möglicherweise ein regionales Kontingent für AlloyDB-Instanzen. Wenn us-central1 fehlschlägt, versuchen Sie es mit us-east1.

3. Kurze Zusammenfassung der Daten aus Teil 1

In diesem Abschnitt müssen Sie dafür sorgen, dass die strukturierten Daten, die wir aus unstrukturierten PDFs extrahiert haben, in BigQuery verfügbar sind. Wenn Sie Teil 1 verpasst haben oder kein Abrechnungskonto haben, können Sie die folgenden Schritte ausführen:

Rufen Sie die Google Cloud Console über Ihr persönliches Gmail-Konto auf und klicken Sie rechts oben in der Konsole auf den Button „Cloud Shell aktivieren“:

Führen Sie dann die Schritte im Abschnitt „Kein Abrechnungskonto“ unten aus:

KEIN RECHNUNGSKONTO?

Wenn Sie dieses Codelab nur überfliegen und es ohne Abrechnungskonto ausprobieren, konnten Sie den Datascan-Job wahrscheinlich nicht einrichten oder Knowledge Catalog-Inferenz durchführen. Hier sind die Daten, die durch die oben genannten Schritte aus den 400 PDFs mit Rezepten und Lieferanten abgeleitet wurden und hier zu Referenzzwecken verfügbar sind: https://github.com/AbiramiSukumaran/raw-data-to-gold/tree/main/data.

Im folgenden Abschnitt erfahren Sie, wie Sie weiterhin auf die Daten zugreifen können:

KEIN RECHNUNGSKONTO?

So können Sie weiterhin auf die Daten zugreifen, ohne ein Abrechnungskonto zu haben:

1. Sie können die CSV-Dateien (BigQuery-Daten) über den oben genannten GitHub-Link abrufen.
2. Erstellen Sie zuerst das BigQuery-Dataset, indem Sie den folgenden Befehl im Cloud Shell-Terminal ausführen:

bq mk --location us-central1 --dataset froyo_data

3. Laden Sie als Nächstes die acht Datendateien (CSV-Dateien) aus dem GitHub-Repository in Ihr Arbeitsverzeichnis herunter, indem Sie die folgenden Befehle einzeln ausführen:

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.allergen.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.consistsof.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.containsallergen.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.froyo_data_materialized.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.ingredient.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.product.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.suppliedby.csv

wget https://raw.githubusercontent.com/AbiramiSukumaran/raw-data-to-gold/refs/heads/main/data/froyo_data.supplier.csv

4. Führen Sie die folgenden Befehle nacheinander aus, um diese Tabellen mit den Daten in Ihrem neu erstellten Dataset zu erstellen.

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.allergen ./froyo_data.allergen.csv allergen_name:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.consistsof ./froyo_data.consistsof.csv product_id:STRING,ingredient_id:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.containsallergen ./froyo_data.containsallergen.csv ingredient_id:STRING,allergen_name:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.froyo_data_materialized ./froyo_data.froyo_data_materialized.csv allergen:STRING,containsallergen:STRING,ingredient:STRING,product:STRING,suppliedby:STRING,supplier:STRING,ref:STRING,md5_hash:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.ingredient ./froyo_data.ingredient.csv ingredient_id:STRING,ingredient_name:STRING,purity:STRING,moisture_content:STRING,ph_range:STRING,specific_gravity_range:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.product ./froyo_data.product.csv product_id:STRING,product_name:STRING,category:STRING,stability:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.suppliedby ./froyo_data.suppliedby.csv ingredient_id:STRING,supplier_id:STRING

bq load --autodetect --source_format=CSV --skip_leading_rows=1 --allow_quoted_newlines --quote="" froyo_data.supplier ./froyo_data.supplier.csv supplier_id:STRING,supplier_name:STRING,production_site_id:STRING,facility_grade:STRING

Nachdem wir die Daten in BigQuery haben, können wir mit den nächsten Schritten fortfahren.

4. AlloyDB-Cluster, ‑Instanz und ‑Netzwerk einrichten

Es gibt eine webbasierte Schnellstartanwendung, mit der Sie AlloyDB-Cluster, ‑Instanzen und andere Abhängigkeiten einrichten können. Sie können die Schritte 2 bis 4 in diesem Lab ausführen, um die Einrichtung mit einem Klick zu erledigen:

https://codelabs.developers.google.com/quick-alloydb-setup

Nachdem Sie Ihren Cluster erstellt haben, rufen Sie die Seite „Clusterübersicht“ auf und kopieren Sie die Dienstkontodetails.

5. Berechtigungen einrichten

BigQuery-Berechtigungen für dieses Dienstkonto gewähren

1. Rufen Sie „IAM & Verwaltung“ > „IAM“ auf.
2. Klicken Sie auf „Zugriff gewähren“.
3. Fügen Sie die Adresse des AlloyDB-Dienstkontos in das Feld „Neue Hauptkonten“ ein.
4. Weisen Sie die folgenden Rollen zu:

• BigQuery-Datenbetrachter (roles/bigquery.dataViewer): Ermöglicht das Lesen der Daten.
• BigQuery-Nutzer (roles/bigquery.user): Ermöglicht das Ausführen der Abfragen.
• (Optional, aber empfohlen) BigQuery-Lesesitzungsnutzer (roles/bigquery.readSessionUser): Optimiert das Lesen großer Datasets über die Storage Read API.

6. Verbindung zu AlloyDB herstellen und die BigQuery-Erweiterung aktivieren

Jetzt stellen wir eine Verbindung zu unserer neuen AlloyDB-Instanz her, um die Erweiterung für die Föderation zu konfigurieren. Dazu verwenden wir AlloyDB Studio.

1. Klicken Sie auf der Seite „Clusterübersicht“ (AlloyDB-Konsole) bei Ihrer primären Instanz auf Primäre bearbeiten und scrollen Sie nach unten zu Erweiterte Konfigurationsoptionen.
2. Gehen Sie zum Abschnitt Flags und aktivieren Sie die beiden Flags, wie unten dargestellt:

3. Klicken Sie auf die Schaltfläche **Instanz aktualisieren**. Das Update kann einige Minuten dauern. 4. Klicken Sie auf der Seite „Clusterübersicht“ (AlloyDB Console) auf „AlloyDB Studio“.

5. Stellen Sie eine Verbindung mit Ihrer Datenbank her. Verwenden Sie dazu den Nutzernamen und das Passwort, die Sie bei der AlloyDB-Schnelleinrichtung konfiguriert haben.
6. Geben Sie nach der Verbindung auf dem Tab „Abfrageeditor“ auf der rechten Seite die folgenden Anweisungen ein und führen Sie sie einzeln aus:

CREATE EXTENSION IF NOT EXISTS  bigquery_fdw;

CREATE SERVER bigquery_server FOREIGN DATA WRAPPER bigquery_fdw;

CREATE USER MAPPING FOR postgres SERVER bigquery_server;

7. Wenn Sie das getan haben, rufen Sie den Explorer-Bereich auf der linken Seite auf und scrollen Sie nach unten zu BigQuery-Tabellen:

8. Klicken Sie auf die drei Punkte und dann auf BigQuery-Tabelle verbinden.
9. Wählen Sie im Pop-up-Fenster „BigQuery-Tabelle verbinden“ Ihre project_id und den Namen des BigQuery-Datasets aus (im ersten Teil erstellt), aus dem Sie die Daten in Ihrer AlloyDB-Datenbank abfragen möchten.

10. Wählen Sie jede Tabelle einzeln aus, um alle Ihre Daten mit AlloyDB zu verbinden. So können wir die Spaltentypen validieren und sicherstellen, dass sie in AlloyDB unterstützt werden.

Wenn Sie dasselbe mit SQL anstatt über den Point-and-Click-Ansatz tun möchten:

CREATE FOREIGN TABLE <<TABLE_NAME>> (
      "cas_number" VARCHAR, "ingredient_name" VARCHAR, "max_moisture_percentage" DOUBLE PRECISION, "ph_range" VARCHAR, "purity_percentage" DOUBLE PRECISION, "shelf_life_months" BIGINT, "specific_gravity_range" VARCHAR
    ) SERVER "bigquery_server" OPTIONS (
      project '<<PROJECT_ID>>',
      dataset 'froyo_data',
      table '<<BQ_TABLE_NAME>>'
    );

Das Besondere

Wir haben gerade „Fremdtabellen“ in AlloyDB erstellt. Sie sehen aus wie normale PostgreSQL-Tabellen und verhalten sich auch so, speichern aber keine Daten. Wenn Sie sie abfragen, leitet AlloyDB die Abfrage sofort an BigQuery weiter, ruft die Ergebnisse ab und gibt sie an Sie zurück.

7. Föderation in AlloyDB testen

Wir prüfen, ob wir unser riesiges analytisches BigQuery-Dataset direkt aus unserer transaktionalen PostgreSQL-Datenbank abfragen können.

Führen Sie in AlloyDB Studio eine Abfrage aus, um herauszufinden, welche Allergene im „Midnight Swirl“ enthalten sind. Das ist dieselbe Frage, die wir in Teil 1 gestellt haben, aber diesmal aus AlloyDB heraus:

SELECT
    p.product_name,
    i.ingredient_name,
    a.allergen_name
FROM
    consistsof c
INNER JOIN product p
    ON c.product_id = p.product_id
INNER JOIN ingredient i
    ON c.ingredient_id = i.ingredient_name
LEFT OUTER JOIN containsallergen a
    ON i.ingredient_id = a.ingredient_id
WHERE
    UPPER(p.product_name) LIKE '%MIDNIGHT%SWIRL%'
    AND a.allergen_name IS NOT NULL;

Zack. Sie sollten genau dieselben Ergebnisse sehen wie in BigQuery.

8. Bereinigen

Vergessen Sie nicht, den AlloyDB-Cluster und die AlloyDB-Instanz zu löschen, wenn Sie dieses Lab abgeschlossen haben.

Rufen Sie https://console.cloud.google.com/alloydb/clusters auf. Wählen Sie den Cluster aus, den Sie löschen möchten, indem Sie auf das vertikale Dreipunkt-Menü daneben klicken, und klicken Sie auf „LÖSCHEN“.

Dadurch sollte der Cluster zusammen mit seinen Instanzen bereinigt werden.

9. Herzlichen Glückwunsch zu Ihrem Unified Data Layer

Denken Sie daran, was wir gerade erreicht haben:

1. Unsere Transaktions-App (die auf AlloyDB ausgeführt wird) kann schnelle, gleichzeitige Nutzersitzungen verarbeiten.
2. Wenn umfangreiche Analysedaten oder ein historischer Kontext (z. B. Lieferantendetails oder komplexe Zutatenzuordnungen) erforderlich sind, wird das BigQuery-Schema „froyo_dataschema“ abgefragt.
3. Zero ETL Keine Datenpipelines, die unterbrochen werden. Keine nicht synchronisierten Datenbanken. Wir speichern die Daten einmal (in BQ) und führen die Berechnungen dort durch, wo sie benötigt werden.

Nachdem unsere Datengrundlage – sowohl analytisch als auch transaktional – solide und miteinander verbunden ist, können wir uns dem interessanten Teil zuwenden.

In Teil 3 erstellen wir die Multi-Agenten-Anwendung, die auf dieser Architektur basiert, um die Geschäftsabläufe von Froyo auszuführen.