Zero-ETL-Datenföderation von BigQuery zu AlloyDB

1. Übersicht

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

Zur Erinnerung: Im Lab zu Teil 1 haben wir den Anwendungsfall eines fiktiven Frozen Yogurt-Franchiseunternehmens verwendet und 400 unstrukturierte PDF-Dateien mit Text, Tabellen und Bildern in sauber strukturierte BigQuery-Tabellen umgewandelt. Dabei wurden die Beziehungen zwischen den Tabellen automatisch mit BigQuery Knowledge Catalog und Dataplex abgeleitet.

Aufgaben

In dieser Sitzung richten wir AlloyDB for PostgreSQL ein und führen etwas Magisches aus: Wir föderieren unsere BigQuery-Daten direkt in AlloyDB. Das bedeutet, dass unsere Transaktionsanwendung unsere Data-Warehouse-Daten in Echtzeit abfragen kann, ohne sie kopieren oder duplizieren zu müssen.

Als Entwickler müssen Sie sich an dieser Stelle Folgendes fragen:

„Wenn die Daten bereits in BigQuery vorhanden sind, warum dann AlloyDB? 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 transaktionalen und analytischen Arbeitslasten Ihrer Anwendung über dieselbe Oberfläche zu verarbeiten. Sie können diese Daten auch in AlloyDB materialisieren oder importieren, um schneller darauf zugreifen zu können. So können Sie AlloyDB AI und die spaltenbasierte Engine nutzen.

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

Anstatt eine fragile 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.

Beginnen wir mit der Erstellung.

Lerninhalte

• AlloyDB-Cluster, -Instanz und -Netzwerk mit einem Klick einrichten
• Erweiterung für die Vorbereitung auf die Föderation einrichten
• Föderation von BigQuery zu AlloyDB einrichten
• Testen

Voraussetzungen

• Ein Browser wie 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 der Projektauswahl ein Google Cloud-Projekt aus oder erstellen Sie eines.
2. Die Abrechnung für das Cloud-Projekt muss aktiviert sein. Informationen zum Prüfen, ob die Abrechnung für ein Projekt aktiviert ist .

In diesem Lab sind einige Funktionen verfügbar, die Sie auch ohne Abrechnungskonto nutzen können. Wenn Sie diese Funktionen nutzen möchten, finden Sie entsprechende Hinweise unter dem Qualifier „KEIN ABRECHNUNGSKONTO?“. Achten Sie darauf.

3. Sie verwenden die 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, prüfen Sie mit dem folgenden Befehl, ob Sie bereits authentifiziert sind und für das Projekt schon Ihre Projekt-ID eingestellt ist:

gcloud auth list

5. Führen Sie in der 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, legen Sie es mit dem folgenden Befehl fest:

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 „Ghost Project“-Syndrom	Sie haben gcloud config set project ausgeführt, sehen aber in der Console-UI ein anderes Projekt. Prüfen Sie die Projekt-ID im Drop-down-Menü oben links.
Die Abrechnungsbarriere	Sie haben das Projekt aktiviert, aber das Abrechnungskonto vergessen. AlloyDB ist eine leistungsstarke Engine. Sie wird nicht gestartet, wenn der „Tank“ (Abrechnung) leer ist.
Verzögerung bei der API-Weitergabe	Sie haben auf „APIs aktivieren“ geklickt, aber in der Befehlszeile wird immer noch Service Not Enabled angezeigt. Warten Sie 60 Sekunden. Die Cloud braucht einen Moment, um ihre Neuronen zu aktivieren.
Kontingentprobleme	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 privates Gmail-Konto auf und klicken Sie rechts oben in der Console auf die Schaltfläche „Cloud Shell aktivieren“:

Folgen Sie dann der Anleitung im Abschnitt „Kein Abrechnungskonto“ unten:

KEIN ABRECHNUNGSKONTO?

Wenn Sie dieses Codelab nur überfliegen und die Übungen auf hoher Ebene ohne Abrechnungskonto durchführen, konnten Sie den DataScan-Job wahrscheinlich nicht einrichten oder die Knowledge Catalog-Inferenz nicht ausführen. Oder Sie haben das Lab zu Teil 1 verpasst. Hier sind die Daten, die durch die oben genannten Schritte aus den 400 PDFs mit Rezepten und Lieferanten abgeleitet wurden, für Sie verfügbar: https://github.com/AbiramiSukumaran/raw-data-to-gold/tree/main/data.

So können Sie die Daten mit dem Abrechnungskonto weiter nutzen:

1. Rufen Sie die CSV-Dateien (BigQuery-Daten) über den Link zum GitHub-Repository oben auf.
2. Führen Sie den folgenden Befehl im Cloud Shell-Terminal aus:

____________________________________________

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

____________________________________________

3. Führen Sie die folgenden Befehle einzeln 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_data1.supplier \

./froyo_data.supplier.csv supplier_id:STRING,supplier_name:STRING,production_site_id:STRING,facility_grade:STRING

-----------------------------------------------------------------

Sobald Dataset, Tabellen und Daten erstellt wurden, können Sie die Daten testen und nutzen, die wir gerade besprochen haben.

KEIN ABRECHNUNGSKONTO?

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 Kurzanleitung, die Ihnen beim Einrichten von AlloyDB-Cluster, -Instanz und anderen Abhängigkeiten hilft. Sie können die Schritte 2 bis 4 in diesem Lab ausführen, um die Einrichtung mit einem Klick vorzunehmen:

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

Nachdem Ihr Cluster erstellt wurde, rufen Sie die Seite „Clusterübersicht“ auf und kopieren Sie die Details des Dienstkontos.

5. Berechtigungen einrichten

BigQuery-Berechtigungen für dieses Dienstkonto gewähren

1. Rufen Sie „IAM und Verwaltung“ > „IAM“ auf.
2. Klicken Sie auf „Zugriff erlauben“.
3. Fügen Sie die AlloyDB-Dienstkontoadresse 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. Mit AlloyDB verbinden und die BigQuery-Erweiterung aktivieren

Jetzt stellen wir eine Verbindung zu unserer neuen AlloyDB-Instanz her, um die Föderationserweiterung zu konfigurieren. Dazu verwenden wir AlloyDB Studio.

1. Klicken Sie auf der Seite „Clusterübersicht“ (AlloyDB Console) auf „AlloyDB Studio“.

2. Stellen Sie eine Verbindung mit Ihrer Datenbank, Ihrem Nutzernamen und Ihrem Passwort her, die Sie beim AlloyDB-Schnellstart konfiguriert haben.
3. Geben Sie nach der Verbindung auf dem Tab „Abfrageeditor“ rechts 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;

4. Navigieren Sie nach Abschluss der Einrichtung im Explorer-Bereich links nach unten zu den BigQuery-Tabellen:

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

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

Wenn Sie dasselbe mit SQL anstelle der Point-and-Click-Methode ausführen möchten, gehen Sie so vor:

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>>'
    );

Die Magie!!!

Wir haben gerade „externe Tabellen“ in AlloyDB erstellt. Diese sehen aus wie normale PostgreSQL-Tabellen und verhalten sich auch so, speichern aber keine Daten. Wenn Sie sie abfragen, übergibt AlloyDB die Abfrage sofort an BigQuery, 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 in „Midnight Swirl“ enthalten sind. Das ist dieselbe Frage, die wir in Teil 1 gestellt haben, aber dieses Mal aus AlloyDB:

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/clustersauf. Wählen Sie den Cluster aus, den Sie löschen möchten, indem Sie auf die vertikalen Auslassungspunkte daneben klicken, und klicken Sie auf „LÖSCHEN“.

Der Cluster und seine Instanzen werden bereinigt.

9. Herzlichen Glückwunsch zu Ihrer einheitlichen Datenschicht

Denken Sie darüber nach, was wir gerade erreicht haben:

1. Unsere Transaktionsanwendung (die in AlloyDB ausgeführt wird) kann schnelle, gleichzeitige Nutzersitzungen verarbeiten.
2. Wenn sie umfangreiche analytische Daten oder einen historischen Kontext benötigt (z. B. Lieferantendetails oder komplexe Zuordnungen von Inhaltsstoffen), fragt sie das BigQuery-Schema „froyo_data“ ab.
3. Keine ETL. Keine fehlerhaften Datenpipelines. Keine nicht synchronisierten Datenbanken. Wir speichern einmal (in BigQuery) und führen die Berechnungen dort aus, wo sie benötigt werden.

Nachdem unsere Datenbasis – 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 Froyo-Geschäftsabläufe auszuführen.