Federazione dei dati senza ETL da BigQuery ad AlloyDB

1. Panoramica

In Parte 1, abbiamo trasformato con successo PDF caotici e non strutturati in tabelle pulite, intelligenti e strutturate in BigQuery utilizzando Knowledge Catalog e DataScan. Ora abbiamo un data warehouse robusto.

Se hai bisogno di un riepilogo rapido, nel lab della parte 1 abbiamo preso in considerazione il caso d'uso di un franchising di yogurt gelato fittizio e abbiamo convertito 400 dei suoi file PDF non strutturati, contenenti testo, tabelle e immagini, in tabelle BigQuery strutturate in modo pulito con relazioni dedotte automaticamente tra loro utilizzando BigQuery Knowledge Catalog e Dataplex.

Cosa creerai

In questa sessione, configureremo AlloyDB per PostgreSQL e faremo qualcosa di magico: federare i nostri dati BigQuery direttamente in AlloyDB. Ciò significa che la nostra app transazionale può eseguire query sui dati del nostro warehouse in tempo reale, senza copiarli o duplicarli.

In questa fase, in qualità di sviluppatore, devi porti questa domanda:

"Se i dati sono già in BigQuery, perché aggiungere AlloyDB? Perché l'applicazione non esegue semplicemente un'istruzione SELECT direttamente su BigQuery?"

Ecco perché:

Con Lakehouse Federation, puoi utilizzare il motore di query di AlloyDB per gestire i carichi di lavoro transazionali e analitici della tua applicazione dalla stessa interfaccia. Puoi anche materializzare o importare questi dati in AlloyDB per un accesso più rapido per l'utilizzo nelle tue applicazioni, il che ti consente di utilizzare AlloyDB AI e il motore colonnare.

Puoi utilizzare AlloyDB come database transazionale e avere anche grandi quantità di dati in BigQuery o BigLake. In genere, le applicazioni si integrano in modo indipendente con entrambi i sistemi per accedere ai dati in questi diversi servizi Google Cloud. Lakehouse Federation per AlloyDB ti consente di utilizzare il supporto per le query federate di AlloyDB implementato come foreign data wrapper per accedere ai dati di BigQuery e AlloyDB utilizzando un'interfaccia SQL in AlloyDB.

Anziché creare una pipeline ETL fragile per eseguire query sui dati BigQuery da AlloyDB, utilizzeremo le query federate. AlloyDB fungerà da endpoint unificato, raggiungendo BigQuery senza problemi quando necessario.

Iniziamo a creare.

Obiettivi didattici

• Come configurare il cluster, l'istanza e la rete AlloyDB con un clic di un pulsante
• Come configurare l'estensione per prepararsi alla federazione
• Come configurare la federazione da BigQuery ad AlloyDB
• Provare

Requisiti

• Un browser, ad esempio Chrome o Firefox.
• Un progetto Google Cloud con la fatturazione abilitata.
• Conoscenza di base di SQL.

2. Prima di iniziare

Crea un progetto

1. Nella console Google Cloud, nella pagina di selezione del progetto, seleziona o crea un progetto Google Cloud.
2. Verifica che la fatturazione sia attivata per il tuo progetto Cloud. Scopri come verificare se la fatturazione è abilitata per un progetto.

In questo lab sono disponibili funzionalità limitate che puoi provare anche senza un account di fatturazione. Se vuoi esplorare la parte pratica in questa veste, troverai le note pertinenti all'interno dei qualificatori "NON HAI UN ACCOUNT DI FATTURAZIONE?". Fai attenzione.

3. Utilizzerai Cloud Shell, un ambiente a riga di comando in esecuzione in Google Cloud. Fai clic su Attiva Cloud Shell nella parte superiore della console Google Cloud.

4. Una volta eseguita la connessione a Cloud Shell, verifica che il tuo account sia già autenticato e che il progetto sia impostato sul tuo ID progetto utilizzando il seguente comando:

gcloud auth list

5. Esegui il seguente comando in Cloud Shell per verificare che il comando gcloud conosca il tuo progetto.

gcloud config list project

6. Se vuoi eseguire l'autenticazione

gcloud auth login

7. Se il progetto non è impostato, utilizza il seguente comando per impostarlo:

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

8. Abilita le API richieste: esegui questo comando per abilitare tutte le API richieste:

gcloud services enable alloydb.googleapis.com

Problemi e risoluzione dei problemi

La sindrome del "progetto fantasma"	Hai eseguito gcloud config set project, ma in realtà stai visualizzando un progetto diverso nell'interfaccia utente della console. Controlla l'ID progetto nel menu a discesa in alto a sinistra.
La barriera della fatturazione	Hai abilitato il progetto, ma hai dimenticato l'account di fatturazione. AlloyDB è un motore ad alte prestazioni; non si avvierà se il "serbatoio" (fatturazione) è vuoto.
Ritardo nella propagazione dell'API	Hai fatto clic su "Abilita API", ma la riga di comando continua a visualizzare Service Not Enabled. Attendi 60 secondi. Il cloud ha bisogno di un attimo per attivare i suoi neuroni.
Problemi di quota	Se utilizzi un nuovo account di prova, potresti raggiungere una quota regionale per le istanze AlloyDB. Se us-central1 non funziona, prova us-east1.

3. Breve riepilogo dei dati della Parte 1

In questa sezione, devi assicurarti che i dati strutturati che abbiamo estratto dai PDF non strutturati siano disponibili in BigQuery. Se hai perso la parte 1 o non hai un account di fatturazione, non preoccuparti, puoi completare i seguenti passaggi e iniziare:

Vai alla console Google Cloud dal tuo account Gmail personale e fai clic sul pulsante Attiva Cloud Shell nell'angolo in alto a destra della console:

Poi segui i passaggi nella sezione relativa all'account di fatturazione riportata di seguito:

NON HAI UN ACCOUNT DI FATTURAZIONE?

Se stai leggendo rapidamente questo codelab, in modo pratico ma di alto livello senza un account di fatturazione, probabilmente non sei riuscito a configurare il job DataScan o a eseguire l'inferenza di Knowledge Catalog. Oppure hai perso il lab della parte 1, quindi ecco i dati resi disponibili per te, dedotti tramite i passaggi precedenti dai 400 PDF di ricette e fornitori: https://github.com/AbiramiSukumaran/raw-data-to-gold/tree/main/data.

Passaggi per continuare a utilizzare i dati con l'account di fatturazione:

1. Scarica i file CSV (dati BigQuery) dal link del repository GitHub riportato sopra.
2. Esegui il comando riportato di seguito dal terminale Cloud Shell:

____________________________________________

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

____________________________________________

3. Esegui i seguenti comandi uno alla volta per creare queste tabelle con i dati nel set di dati appena creato

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

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

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

Una volta creati il set di dati, le tabelle e i dati, puoi procedere a testare e utilizzare i dati di cui abbiamo appena parlato.

NON HAI UN ACCOUNT DI FATTURAZIONE?

Ora che abbiamo i dati in BigQuery, passiamo ai passaggi successivi.

4. Configura il cluster, l'istanza e la rete AlloyDB

Esiste un'applicazione di avvio rapido basata sul web che ti aiuterà a configurare il cluster, l'istanza e altre dipendenze di AlloyDB. Puoi seguire i passaggi da 2 a 4 in questo lab per configurarlo con un clic di un pulsante:

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

Una volta creato il cluster, vai alla pagina Panoramica del cluster e copia i dettagli del service account da lì.

5. Configurazione delle autorizzazioni

Concedi le autorizzazioni BigQuery a questo service account

1. Vai a IAM e amministrazione > IAM.
2. Fai clic su Concedi accesso.
3. Incolla l'indirizzo del service account AlloyDB nel campo Nuove entità.
4. Assegna i seguenti ruoli:

• Visualizzatore dati BigQuery (roles/bigquery.dataViewer): consente di leggere i dati.
• Utente BigQuery (roles/bigquery.user): consente di eseguire le query.
• (Facoltativo, ma consigliato) Utente sessione di lettura BigQuery (roles/bigquery.readSessionUser): ottimizza la lettura di set di dati di grandi dimensioni tramite l'API Storage Read.

6. Connettiti ad AlloyDB e abilita l'estensione BigQuery

Ora ci connettiamo alla nostra nuova istanza AlloyDB per configurare l'estensione di federazione. Per farlo, utilizzeremo AlloyDB Studio.

1. Dalla pagina Panoramica del cluster (console AlloyDB), fai clic su AlloyDB Studio.

2. Connettiti con il database, il nome utente e la password che hai configurato durante il passaggio di configurazione rapida di AlloyDB.
3. Una volta connesso, nella scheda Editor di query sul lato destro, inserisci le seguenti istruzioni ed esegui una alla volta:

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. Una volta completata l'operazione, vai al riquadro dell'explorer a sinistra e scorri verso il basso fino alle tabelle BigQuery:

5. Fai clic sui tre puntini e poi su "Connetti tabella BigQuery".
6. Nel popup Connetti tabella BigQuery che si apre, seleziona project_id e il nome del set di dati BigQuery (creato nella parte 1) da cui vuoi eseguire query sui dati nel tuo database AlloyDB.

7. Seleziona ogni tabella una alla volta per connettere tutti i dati ad AlloyDB. In questo modo, convalidiamo i tipi di colonne per assicurarci che siano supportati in AlloyDB.

Se vuoi fare la stessa cosa con SQL anziché tramite l'approccio point-and-click:

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

La magia!!!

Abbiamo appena creato le "tabelle esterne" in AlloyDB. Queste tabelle hanno lo stesso aspetto e si comportano come le normali tabelle PostgreSQL, ma non memorizzano alcun dato. Quando esegui query su di esse, AlloyDB passa immediatamente la query a BigQuery, recupera i risultati e li restituisce.

7. Testa la federazione in AlloyDB

Verifichiamo se possiamo eseguire query sul nostro set di dati BigQuery analitico di grandi dimensioni direttamente dal nostro database PostgreSQL transazionale.

Sempre in AlloyDB Studio, eseguiamo una query per scoprire quali allergeni sono presenti in "Midnight Swirl" (la stessa domanda che abbiamo posto nella Parte 1, ma questa volta da 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;

Ma non finisce qui. Dovresti vedere gli stessi risultati che hai visto in BigQuery.

8. Libera spazio

Al termine di questo lab, non dimenticare di eliminare il cluster e l'istanza AlloyDB.

Vai a https://console.cloud.google.com/alloydb/clusters. Seleziona il cluster che vuoi eliminare facendo clic sui puntini di sospensione verticali accanto e poi su ELIMINA.

Il cluster e le relative istanze verranno eliminati.

9. Congratulazioni per il tuo livello di dati unificato

Pensa a cosa abbiamo appena fatto:

1. La nostra app transazionale (in esecuzione su AlloyDB) può gestire sessioni utente rapide e simultanee.
2. Quando ha bisogno di dati analitici o di un contesto storico (come i dettagli del fornitore o mappature complesse degli ingredienti), esegue query sullo schema froyo_data di BigQuery.
3. Nessun ETL. Nessuna interruzione delle pipeline di dati. Nessun database non sincronizzato. Archiviamo una sola volta (in BQ) ed eseguiamo il calcolo dove ci serve.

Ora che la nostra base di dati, sia analitica che transazionale, è solida e interconnessa, siamo pronti per la parte divertente.

Nella Parte 3, creeremo l'applicazione multi-agente che si basa su questa architettura per eseguire le operazioni aziendali di Froyo.