ETL inverso da Snowflake a Spanner utilizzando BQ

1. Crea una pipeline ETL inversa da Snowflake a Spanner utilizzando Google Cloud Storage e BigQuery

Introduzione

In questo codelab, una pipeline Reverse ETL viene creata da Snowflake a Spanner. Tradizionalmente, le pipeline ETL (Extract, Transform, Load) spostano i dati dai database operativi a un data warehouse come Snowflake per l'analisi. Una pipeline ETL inversa fa il contrario: sposta i dati curati ed elaborati dal data warehouse nei sistemi operativi in cui possono alimentare le applicazioni, fornire funzionalità rivolte agli utenti o essere utilizzati per il processo decisionale in tempo reale.

L'obiettivo è spostare un set di dati aggregato da una tabella Snowflake Iceberg a Spanner, un database relazionale distribuito a livello globale ideale per applicazioni ad alta disponibilità.

Per raggiungere questo obiettivo, vengono utilizzati Google Cloud Storage (GCS) e BigQuery come passaggi intermedi. Di seguito è riportata una suddivisione del flusso di dati e del ragionamento alla base di questa architettura:

1. Da Snowflake a Google Cloud Storage (GCS) in formato Iceberg:

• Il primo passaggio consiste nell'estrazione dei dati da Snowflake in un formato aperto e ben definito. La tabella viene esportata in formato Apache Iceberg. Questo processo scrive i dati sottostanti come un insieme di file Parquet e i metadati della tabella (schema, partizioni, posizioni dei file) come file JSON e Avro. L'organizzazione di questa struttura completa della tabella in GCS rende i dati portatili e accessibili a qualsiasi sistema che comprenda il formato Iceberg.

2. Converti le tabelle Iceberg in GCS in una tabella esterna BigLake BigQuery:

• Anziché caricare i dati direttamente da GCS in Spanner, BigQuery viene utilizzato come potente intermediario. Creerai una tabella esterna BigLake in BigQuery che punta direttamente al file di metadati Iceberg in GCS. Questo approccio presenta diversi vantaggi:
• Nessuna duplicazione dei dati:BigQuery legge la struttura della tabella dai metadati ed esegue query sui file di dati Parquet sul posto senza importarli, il che consente di risparmiare tempo e costi di archiviazione significativi.
• Query federate:consente di eseguire query SQL complesse sui dati GCS come se fossero una tabella BigQuery nativa.

3. BigQuery a Spanner:

• Il passaggio finale consiste nello spostare i dati da BigQuery a Spanner. Per farlo, utilizzerai una potente funzionalità di BigQuery chiamata query EXPORT DATA, che è il passaggio "Reverse ETL".
• Operational Readiness:Spanner è progettato per i carichi di lavoro transazionali, fornendo elevata coerenza e alta disponibilità per le applicazioni. Spostando i dati in Spanner, questi diventano accessibili ad applicazioni rivolte agli utenti, API e altri sistemi operativi che richiedono ricerche puntuali a bassa latenza.
• Scalabilità:questo pattern consente di sfruttare la potenza analitica di BigQuery per elaborare set di dati di grandi dimensioni e quindi pubblicare i risultati in modo efficiente tramite l'infrastruttura scalabile a livello globale di Spanner.

Servizi e terminologia

• Snowflake: una piattaforma dati cloud che fornisce un data warehouse as a service.
• Spanner: un database relazionale completamente gestito e distribuito a livello globale.
• Google Cloud Storage: offerta di archiviazione di blob di Google Cloud.
• BigQuery: un data warehouse serverless completamente gestito per l'analisi.
• Iceberg: un formato di tabella aperto definito da Apache che fornisce un'astrazione sui formati di file di dati open source comuni.
• Parquet: un formato di file di dati binari colonnari open source di Apache.

Obiettivi didattici

• Come caricare dati in Snowflake
• Come creare un bucket GCS
• Come esportare una tabella Snowflake in GCS nel formato Iceberg
• Come configurare un'istanza di Spanner
• Come caricare tabelle esterne BigLake in BigQuery in Spanner

2. Configurazione, requisiti e limitazioni

Prerequisiti

• Un account Snowflake
• Per l'esportazione da BigQuery a Spanner è necessario un account Google Cloud con una prenotazione di livello Enterprise o superiore.
• Accesso alla console Google Cloud tramite un browser web
• Un terminale per eseguire i comandi di Google Cloud CLI
• Se la tua organizzazione Google Cloud ha attivato il criterio iam.allowedPolicyMemberDomains, un amministratore potrebbe dover concedere un'eccezione per consentire i service account di domini esterni. Questo aspetto verrà trattato in un passaggio successivo, se applicabile.

Limitazioni

È importante essere consapevoli di alcune limitazioni e incompatibilità dei tipi di dati che possono verificarsi in questa pipeline.

Snowflake to Iceberg

I tipi di dati delle colonne differiscono tra Snowflake e Iceberg. Le informazioni sulla traduzione tra questi tipi di dati sono disponibili nella documentazione di Snowflake.

Iceberg to BigQuery

Quando utilizzi BigQuery per eseguire query sulle tabelle Iceberg, esistono alcune limitazioni. Per un elenco completo, consulta la documentazione di BigQuery. Tieni presente che i tipi come BIGNUMERIC, INTERVAL, JSON, RANGE o GEOGRAPHY non sono attualmente supportati.

Da BigQuery a Spanner

Il comando EXPORT DATA da BigQuery a Spanner non supporta tutti i tipi di dati BigQuery. L'esportazione di una tabella con i seguenti tipi genererà un errore:

• STRUCT
• GEOGRAPHY
• DATETIME
• RANGE
• TIME

Inoltre, se il progetto BigQuery utilizza il dialetto GoogleSQL, i seguenti tipi numerici non sono supportati per l'esportazione in Spanner:

• BIGNUMERIC

Per un elenco completo e aggiornato delle limitazioni, consulta la documentazione ufficiale: Limitazioni dell'esportazione in Spanner.

Snowflake

Per questo codelab, puoi utilizzare un account Snowflake esistente o configurare un account di prova senza costi.

Autorizzazioni IAM di Google Cloud Platform

L'Account Google deve disporre delle seguenti autorizzazioni per eseguire tutti i passaggi di questo codelab.

Service account
iam.serviceAccountKeys.create	Consente la creazione di service account.
Spanner
spanner.instances.create	Consente di creare una nuova istanza di Spanner.
spanner.databases.create	Consente l'esecuzione di istruzioni DDL per creare
spanner.databases.updateDdl	Consente di eseguire istruzioni DDL per creare tabelle nel database.
Google Cloud Storage
storage.buckets.create	Consente di creare un nuovo bucket GCS per archiviare i file Parquet esportati.
storage.objects.create	Consente di scrivere i file Parquet esportati nel bucket GCS.
storage.objects.get	Consente a BigQuery di leggere i file Parquet dal bucket GCS.
storage.objects.list	Consente a BigQuery di elencare i file Parquet nel bucket GCS.
Dataflow
Dataflow.workitems.lease	Consente di rivendicare le voci di lavoro da Dataflow.
Dataflow.workitems.sendMessage	Consente al worker Dataflow di inviare messaggi al servizio Dataflow.
Logging.logEntries.create	Consente ai worker Dataflow di scrivere voci di log in Google Cloud Logging.

Per comodità, è possibile utilizzare ruoli predefiniti che contengono queste autorizzazioni.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Configurare le proprietà riutilizzabili

In questo lab avrai bisogno di alcuni valori più volte. Per semplificare la procedura, imposteremo questi valori sulle variabili della shell da utilizzare in un secondo momento.

• GCP_REGION: la regione specifica in cui si troveranno le risorse GCP. L'elenco delle regioni è disponibile qui.
• GCP_PROJECT: l'ID progetto GCP da utilizzare.
• GCP_BUCKET_NAME: il nome del bucket GCS da creare e in cui verranno archiviati i file di dati.

I nomi bucket devono essere univoci globalmente. Un pattern comune è: [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>

Tieni presente che queste variabili di ambiente verranno impostate solo nell'istanza della shell in cui vengono eseguite e dovranno essere impostate di nuovo se esegui i comandi di questo lab in un'altra istanza della shell in un secondo momento.

Progetto Google Cloud

Un progetto è un'unità di base di organizzazione in Google Cloud. Se un amministratore ne ha fornito uno da utilizzare, questo passaggio può essere saltato.

Un progetto può essere creato utilizzando la CLI nel seguente modo:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Scopri di più sulla creazione e sulla gestione dei progetti qui.

Configura Spanner

Per iniziare a utilizzare Spanner, devi eseguire il provisioning di un'istanza e di un database. I dettagli sulla configurazione e la creazione di un'istanza Spanner sono disponibili qui.

Crea l'istanza

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

Crea il database

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

3. Creare un bucket Google Cloud Storage

Google Cloud Storage (GCS) verrà utilizzato per archiviare i file di dati Parquet e i metadati Iceberg generati da Snowflake. A questo scopo, è necessario creare un nuovo bucket da utilizzare come destinazione del file. Da una finestra del terminale su una macchina locale, segui questi passaggi.

Crea il bucket

Utilizza il seguente comando per creare un bucket di archiviazione in una regione specifica (ad es. us-central1).

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

Verifica la creazione del bucket

Una volta eseguito correttamente il comando, controlla il risultato elencando tutti i bucket. Il nuovo bucket dovrebbe essere visualizzato nell'elenco risultante. I riferimenti ai bucket in genere vengono visualizzati con il prefisso gs:// davanti al nome del bucket.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Test delle autorizzazioni di scrittura

Questo passaggio garantisce che l'ambiente locale sia autenticato correttamente e disponga delle autorizzazioni necessarie per scrivere file nel bucket appena creato.

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

Verificare il file caricato

Elenca gli oggetti nel bucket. Dovrebbe essere visualizzato il percorso completo del file appena caricato.

gcloud storage ls gs://$GCS_BUCKET_NAME

Dovresti vedere l'output seguente:

gs://$GCS_BUCKET_NAME/hello.txt

Per visualizzare i contenuti di un oggetto in un bucket, è possibile utilizzare gcloud storage cat.

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

I contenuti del file dovrebbero essere visibili:

Hello, GCS

Esegui la pulizia del file di test

Il bucket Cloud Storage è ora configurato. Ora puoi eliminare il file di test temporaneo.

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

L'output dovrebbe confermare l'eliminazione:

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

4. Esportazione da Snowflake a GCS

Per questo lab, utilizzerai il set di dati TPC-H, che è un benchmark standard del settore per i sistemi di supporto alle decisioni. Il suo schema modella un ambiente aziendale realistico con clienti, ordini, fornitori e parti, il che lo rende perfetto per dimostrare uno scenario reale di analisi e spostamento dei dati. Questo set di dati è disponibile per impostazione predefinita in tutti gli account Snowflake.

Invece di utilizzare le tabelle TPC-H non elaborate e normalizzate, creerai una nuova tabella aggregata. Questa nuova tabella unirà i dati delle tabelle orders, customer e nation per produrre una visualizzazione denormalizzata e riepilogativa dei totali delle vendite nazionali. Questo passaggio di preaggregazione è una pratica comune nell'analisi, in quanto prepara i dati per un caso d'uso specifico, in questo scenario, per il consumo da parte di un'applicazione operativa.

Consentire a Snowflake di accedere a Google Cloud Storage

Per consentire a Snowflake di scrivere dati nel bucket GCS, è necessario creare due elementi: un volume esterno e le autorizzazioni necessarie.

• Un volume esterno è un oggetto Snowflake che fornisce un link sicuro a una posizione specifica in un bucket GCS. Non archivia i dati, ma contiene la configurazione necessaria a Snowflake per accedere allo spazio di archiviazione cloud.
• Per motivi di sicurezza, i bucket di archiviazione cloud sono privati per impostazione predefinita. Quando viene creato un volume esterno, Snowflake genera un service account dedicato. A questo service account devono essere concesse le autorizzazioni di lettura e scrittura per il bucket.

Crea un database

1. Nel menu a sinistra, in Catalogo Horizon, passa il mouse sopra Catalogo e poi fai clic su Database Explorer.
2. Nella pagina Database, fai clic sul pulsante + Database in alto a destra.
3. Assegna un nome al nuovo database codelabs_retl_db

Creare un foglio di lavoro

Per eseguire comandi SQL sul database, sono necessari i fogli di lavoro.

Per creare un foglio di lavoro:

1. Nel menu a sinistra, in Utilizza i dati, passa il mouse sopra Progetti, quindi fai clic su Spazi di lavoro.
2. Nella barra laterale I miei spazi di lavoro, fai clic sul pulsante + Aggiungi nuovo e seleziona File SQL.

Tutti gli altri comandi SQL per Snowflake possono essere eseguiti sostituendo i contenuti di questo file. Le porzioni di paragrafo del file possono essere eseguite posizionando il cursore sul paragrafo e premendo Ctrl + Invio sulla tastiera.

Creare un volume esterno

Esegui questo comando in un foglio di lavoro Snowflake per creare il volume.

Tieni presente che Snowflake utilizza un prefisso gcs:// per i percorsi Cloud Storage, non gs://.

Poiché il seguente codice SQL non verrà eseguito come parte dell'istanza della shell con le variabili di ambiente impostate, assicurati di sostituire i valori del nome del bucket GCS dove viene visualizzato.

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

Ottieni il service account Snowflake

DESC (descrivi) il volume esterno appena creato per ottenere il service account univoco generato da Snowflake.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;

1. Nel riquadro dei risultati, cerca le proprietà JSON e trova la voce property_value che contiene una stringa JSON che inizia con "NAME":"codelabs_retl_ext_vol"
2. Trova la proprietà STORAGE_GCP_SERVICE_ACCOUNT all'interno dell'oggetto JSON e copia il relativo valore (che avrà l'aspetto di un indirizzo email). Si tratta dell'identificatore del service account che deve accedere al bucket GCS.
3. Archivia questo service account in una variabile di ambiente nell'istanza della shell per riutilizzarlo in un secondo momento.

export GCP_SERVICE_ACCOUNT=<Your service account>

Nota: se la tua organizzazione Google Cloud applica il vincolo iam.allowedPolicyMemberDomains, il dominio di questo service account (la parte dopo il simbolo @) dovrà essere aggiunto all'elenco consentito da un amministratore della sicurezza.

Concedere le autorizzazioni GCS a Snowflake

Ora, all'account di servizio Snowflake deve essere concessa l'autorizzazione di scrittura nel bucket GCS.

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"

Verifica l'accesso in Snowflake

Nella scheda di lavoro Snowflake, esegui questo comando per verificare che Snowflake ora possa connettersi correttamente al bucket GCS.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

Il risultato deve essere un oggetto JSON contenente "success":true.

Per saperne di più sui volumi esterni in Snowflake, consulta la documentazione ufficiale.

Esportare i dati di esempio degli ordini

Ora puoi creare una tabella Iceberg in Snowflake. Il comando seguente indica a Snowflake di eseguire una query e archiviare i risultati in GCS utilizzando il formato Iceberg. I file di dati saranno Parquet e i metadati saranno Avro e JSON, tutti archiviati nella posizione definita dal codelabs_retl_ext_vol External Volume.

Crea un database

1. Nel menu a sinistra, in Catalogo Horizon, passa il mouse sopra Catalogo e poi fai clic su Database Explorer.
2. Nella pagina Database, fai clic sul pulsante + Database in alto a destra.
3. Assegna un nome al nuovo database 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
);

Per ulteriori informazioni sulla creazione e la gestione delle tabelle Iceberg utilizzando Snowflake, consulta la documentazione ufficiale.

Verificare i dati in GCP

Ora controlla il bucket GCS. I file creati da Snowflake dovrebbero essere visibili. Ciò conferma che l'esportazione è riuscita. I metadati Iceberg si trovano nella cartella metadata e i dati effettivi come file Parquet nella cartella data.

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

I nomi esatti dei file variano, ma la struttura dovrebbe essere simile alla seguente:

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
...

I dati sono stati copiati da Snowflake a Google Cloud Storage in formato Iceberg.

Mentre abbiamo questo elenco, salviamo il file metadata.json in una variabile di ambiente, perché ci servirà in un secondo momento.

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

5. Configura una tabella esterna BigQuery

Ora che la tabella Iceberg si trova in Google Cloud Storage, il passaggio successivo consiste nel renderla accessibile a BigQuery. A questo scopo, crea una tabella esterna BigLake.

BigLake è un motore di archiviazione che consente di creare tabelle in BigQuery che leggono i dati direttamente da origini esterne come Google Cloud Storage. Per questo lab, è la tecnologia chiave che consente a BigQuery di comprendere la tabella Iceberg appena esportata senza dover importare i dati.

Per far funzionare il tutto, sono necessari due componenti:

1. Una connessione alle risorse Cloud:si tratta di un collegamento sicuro tra BigQuery e GCS. Utilizza un service account speciale per gestire l'autenticazione, assicurandosi che BigQuery disponga delle autorizzazioni necessarie per leggere i file dal bucket GCS.
2. Una definizione di tabella esterna:indica a BigQuery dove trovare il file di metadati della tabella Iceberg in GCS e come deve essere interpretato.

Configurare una connessione a Google Cloud Storage

Innanzitutto, verrà creata la connessione che consente a BigQuery di accedere a GCS. Questo comando crea una risorsa di connessione in BigQuery.

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

Il successo avrà un aspetto simile a questo:

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

Per ulteriori informazioni sulle connessioni delle risorse Cloud in BigQuery, consulta la documentazione di Google Cloud.

Autorizza la connessione BigQuery a leggere i dati

La nuova connessione BigQuery ha un proprio service account che richiede l'autorizzazione per leggere i dati dal bucket Google Cloud Storage.

1. Recupera il service account di connessione

Per prima cosa, recupera l'ID service account dalla connessione appena creata:

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

I risultati mostreranno una tabella delle connessioni corrispondenti.

Impostiamo serviceAccountId su una variabile di ambiente da utilizzare in un secondo momento.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. Concedi autorizzazioni

Autorizza il service account a visualizzare i dati nel bucket GCS eseguendo questo comando.

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

Crea la tabella esterna

Ora crea la tabella esterna BigLake in BigQuery. Questo comando non sposta alcun dato. Crea semplicemente un puntatore ai dati esistenti in GCS. Avrai bisogno del percorso di uno dei file .metadata.json creati da Snowflake.

Sostituisci il valore external_table_definition con il percorso appropriato del file metadata.json nel seguente comando.

Assicurati che questa tabella sia creata nella stessa regione della connessione del passaggio precedente.

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

Verifica dei dati in BigQuery

Ora è possibile eseguire query su questa tabella utilizzando SQL standard, proprio come per qualsiasi altra tabella BigQuery. BigQuery utilizzerà la connessione per leggere i file Parquet da GCS al volo.

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

6. Importazione dei dati da BigQuery a Spanner: il passaggio finale

È stata raggiunta la parte finale e più importante della pipeline: lo spostamento dei dati dalla tabella BigLake a Spanner. Questo è il passaggio "Reverse ETL", in cui i dati, dopo essere stati elaborati e curati nel data warehouse, vengono caricati in un sistema operativo per essere utilizzati dalle applicazioni.

Spanner è un database relazionale completamente gestito e distribuito a livello globale. Offre la coerenza transazionale di un database relazionale tradizionale, ma con la scalabilità orizzontale di un database NoSQL. Ciò lo rende una scelta ideale per la creazione di applicazioni scalabili e ad alta disponibilità.

La procedura sarà la seguente:

1. Crea uno schema della tabella nel database Spanner che corrisponda alla struttura dei dati.
2. Esegui una query BigQuery EXPORT DATA per caricare i dati dalla tabella BigLake direttamente nella tabella Spanner.

Crea la tabella Spanner

Prima di trasferire i dati da BigQuery, è necessario creare una tabella di destinazione in Spanner con uno schema compatibile.

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
)"

Esportare i dati da BigQuery

Questo è l'ultimo passaggio. Con i dati di origine pronti in una tabella BigLake di BigQuery e la tabella di destinazione creata in Spanner, lo spostamento effettivo dei dati è sorprendentemente semplice. Verrà utilizzata una singola query SQL BigQuery: EXPORT DATA.

Questa query è progettata appositamente per scenari come questo. Esporta in modo efficiente i dati da una tabella BigQuery (incluse quelle esterne come la tabella BigLake) a una destinazione esterna. In questo caso, la destinazione è la tabella Spanner.

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

Al termine della query, nel riquadro Risultati dovrebbe essere visualizzato il messaggio "Aggiornamento completato".

7. Verifica i dati in Spanner

Complimenti! Una pipeline Reverse ETL completa è stata creata ed eseguita correttamente. Il passaggio finale consiste nel verificare che i dati siano arrivati in Spanner come previsto.

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

I dati di esempio importati vengono visualizzati come richiesto:

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

Il divario tra i mondi dei dati analitici e operativi è stato colmato.

8. Eliminazione

Pulire Spanner

Elimina il database e l'istanza Spanner

gcloud spanner instances delete $SPANNER_INSTANCE

Pulire GCS

Elimina il bucket GCS creato per ospitare i dati

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

Pulire BigQuery

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

Pulire Snowflake

Elimina il database

1. Nel menu a sinistra, in Catalogo Horizon, passa il mouse sopra Catalogo e poi fai clic su Esplora database.
2. Fai clic su … a destra del database CODELABS_RETL_DB per espandere le opzioni e seleziona Elimina.
3. Nella finestra di dialogo di conferma visualizzata, seleziona Elimina database.

Eliminare le cartelle di lavoro

4. Nel menu a sinistra, in Utilizza i dati, passa il mouse sopra Progetti, quindi fai clic su Spazi di lavoro.
5. Nella barra laterale Il mio spazio di lavoro, passa il mouse sopra i diversi file dello spazio di lavoro che hai utilizzato per questo lab per visualizzare le opzioni aggiuntive ... e fai clic.
6. Seleziona Elimina, quindi fai di nuovo clic su Elimina nella finestra di dialogo di conferma visualizzata.
7. Esegui questa operazione per tutti i file dello spazio di lavoro SQL che hai creato per questo lab.

Elimina volumi esterni

8. Nel menu a sinistra, in Catalogo Horizon, passa il mouse sopra Catalogo e poi fai clic su Dati esterni.
9. Fai clic su a destra di CODELABS_RETL_EXT_VOL e seleziona Riduci volume esterno, quindi di nuovo su Riduci volume esterno nella finestra di dialogo di conferma.

9. Complimenti

Congratulazioni per aver completato il codelab.

Argomenti trattati

• Come caricare dati in Snowflake
• Come creare un bucket GCS
• Come esportare una tabella Snowflake in GCS in formato CSV
• Come configurare un'istanza di Spanner
• Come caricare tabelle CSV in Spanner con Dataflow