Importare dati BigQuery in AlloyDB

Puoi importare dati in AlloyDB per PostgreSQL da tabelle, viste materializzate e viste BigQuery integrate, nonché da tabelle esterne BigLake (ad esempio tabelle gestite Apache Iceberg) e tabelle esterne standard. Iceberg è un formato di tabella aperto per la gestione e lo scambio di dati.

Importando i dati, non è più necessario creare e gestire pipeline di dati complesse e soggette a errori che spostano manualmente i dati da BigQuery ad AlloyDB. Per saperne di più, vedi Panoramica della sincronizzazione dei dati.

Questa pagina presuppone che tu abbia un cluster AlloyDB e un'istanza principale, nonché un set di dati e tabelle BigQuery. Per saperne di più, vedi Creare set di dati e Creare e utilizzare tabelle.

Prima di iniziare

  1. Configura il bigquery_fdw.enabled flag nell'istanza AlloyDB.
  2. Esamina i mapping dei tipi di dati per capire come i tipi BigQuery vengono rappresentati in PostgreSQL quando utilizzi bigquery_fdw.

  3. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  4. Verify that billing is enabled for your Google Cloud project.

  5. Enable the AlloyDB, Compute Engine, Resource Manager, and BigQuery APIs.

    Roles required to enable APIs

    To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

    Enable the APIs

  6. Per creare e connetterti ad AlloyDB, abilita le API Cloud richieste.

    Abilita le API

  7. Nel passaggio Conferma progetto, fai clic su Avanti per confermare il nome del progetto a cui apporterai le modifiche.

  8. Nel passaggio Abilita API, fai clic su Abilita per abilitare quanto segue:

    • API AlloyDB
    • API Compute Engine
    • API Cloud Resource Manager
    • API Service Networking
    • API BigQuery Storage

    L'API Service Networking è necessaria se prevedi di configurare la connettività di rete ad AlloyDB utilizzando una rete VPC che si trova nello stesso Google Cloud progetto di AlloyDB.

    L'API Compute Engine e l'API Cloud Resource Manager sono necessarie se prevedi di configurare la connettività di rete ad AlloyDB utilizzando una rete VPC che si trova in un progetto diverso Google Cloud .

Ruoli obbligatori

Per concedere l'accesso in lettura al set di dati BigQuery all'account di servizio del cluster AlloyDB, devi disporre delle seguenti autorizzazioni:

  • Visualizzatore dati BigQuery (roles/bigquery.dataViewer) o qualsiasi ruolo personalizzato con le autorizzazioni bigquery.tables.get e bigquery.tables.getData. Quando viene concesso a una tabella o a una vista, questo ruolo fornisce le autorizzazioni per leggere i dati e i metadati dalla tabella o dalla vista.
  • BigQuery Read Session User (roles/bigquery.readSessionUser) o qualsiasi ruolo personalizzato con le autorizzazioni bigquery.readsessions.create e bigquery.readsessions.getData. Fornisce la possibilità di creare e utilizzare sessioni di lettura.

Concedi ad AlloyDB l'accesso al set di dati BigQuery

Dopo aver configurato l'estensione bigquery_fdw nel cluster AlloyDB, concedi all'account di servizio del cluster AlloyDB l'accesso al set di dati BigQuery.

Per utilizzare gcloud CLI, puoi installare e inizializzare Google Cloud CLI oppure utilizzare Cloud Shell.

  1. Apri gcloud CLI. Se non hai installato gcloud CLI, installalo e inizializzalo oppure utilizza Cloud Shell.

  2. Esegui il gcloud beta alloydb clusters describe comando:

    gcloud beta alloydb clusters describe CLUSTER --region=REGION

    Sostituisci quanto segue:

    • CLUSTER: l'ID del cluster AlloyDB.
    • REGION: la località del cluster AlloyDB, ad esempio asia-east1, us-east1. Visualizza l'elenco completo delle regioni in Località AlloyDB.

    L'output contiene un campo serviceAccountEmail, che è l'account di servizio per questo cluster. Puoi trovare il account di servizio anche nella pagina Dettagli cluster.

  3. Concedi le autorizzazioni richieste. Per saperne di più, consulta Controllare l'accesso alle risorse con IAM.

    Se l'account di servizio del cluster non dispone delle autorizzazioni richieste, quando viene eseguita una query sulla tabella BigQuery vengono visualizzati i seguenti errori:

    • The user does not have bigquery.readsessions.create permissions
    • Permission bigquery.tables.get denied on table
    • Permission bigquery.tables.getData denied on table

Configurare l'estensione

  1. Crea l'estensione.

    1. Connettiti all'istanza AlloyDB utilizzando il client psql seguendo le istruzioni riportate in Connettere un client psql a un'istanza. In alternativa, puoi utilizzare AlloyDB Studio. Per saperne di più, vedi Gestire i dati utilizzando la Google Cloud console.

    2. Esegui questo comando:

      CREATE EXTENSION bigquery_fdw;

  2. Crea un server esterno per definire i parametri di connessione per il set di dati BigQuery remoto.

    CREATE SERVER BIGQUERY_SERVER_NAME FOREIGN DATA WRAPPER bigquery_fdw;

    Sostituisci quanto segue:

    • BIGQUERY_SERVER_NAME: identificatore univoco per il server esterno. Definisci questo valore una sola volta in un determinato database. Puoi sostituire BIGQUERY_SERVER_NAME con il nome del server.

  3. Crea il mapping utente eseguendo il comando CREATE USER MAPPING, che specifica le credenziali da utilizzare quando ti connetti al server esterno.

    CREATE USER MAPPING FOR USERNAME SERVER BIGQUERY_SERVER_NAME ;

    Sostituisci quanto segue:

    • USERNAME: un nome utente del database o un utente IAM che accede alla tabella esterna. Per un utente IAM, il nome deve essere tutto in minuscolo e utilizzare le virgolette perché il nome contiene caratteri speciali come @ e .).
    • BIGQUERY_SERVER_NAME: identificatore univoco per il server esterno che hai creato.

  4. Definisci le tabelle esterne che corrispondono alle tabelle a cui vuoi accedere in BigQuery utilizzando il comando CREATE FOREIGN TABLE. Questo comando ti consente di definire la struttura di una tabella remota. La tabella esterna può avere tutte o un sottoinsieme delle colonne della tabella di origine in BigQuery.

    CREATE FOREIGN TABLE TABLENAME (
  COLUMNX_NAME DATA_TYPE,
  COLUMNX_NAME DATA_TYPE,
  ...
) SERVER  BIGQUERY_SERVER_NAME
  OPTIONS (project 'BIGQUERY_PROJECT_ID',
           dataset  'BIGQUERY_DATASET_NAME',
           table  'BIGQUERY_TABLE_NAME');

    Sostituisci quanto segue:

    • TABLENAME: nome della tabella esterna nel database locale.
    • COLUMNX_NAME: il nome della colonna AlloyDB. Il nome della colonna deve corrispondere esattamente al nome della colonna corrispondente nella tabella di origine BigQuery. X indica che la tabella può essere creata con più colonne. Il nome deve anche corrispondere esattamente alle maiuscole/minuscole della colonna BigQuery. Se il nome della colonna BigQuery contiene lettere maiuscole, ad esempio employeeID, l'identificatore AlloyDB deve essere racchiuso tra virgolette doppie, ad esempio "employeeID", per conservare le lettere miste o maiuscole.
    • DATA_TYPE: il tipo di dati della colonna.
    • BIGQUERY_SERVER_NAME: identificatore univoco per il server esterno che hai creato.
    • BIGQUERY_PROJECT_ID: ID del progetto in cui si trova il set di dati BigQuery.
    • BIGQUERY_DATASET_NAME: nome del set di dati BigQuery per la tabella.
    • BIGQUERY_TABLE_NAME: nome della tabella BigQuery.

    Dopo aver creato la tabella esterna, puoi eseguire query su questa tabella nello stesso modo in cui esegui query su qualsiasi tabella in AlloyDB.

Importa dati

Per importare dati BigQuery o dati BigLake Iceberg archiviati in BigQuery in AlloyDB:

  1. Identifica un'origine dati esistente o crea una tabella BigQuery integrata o nuove tabelle gestite Iceberg.

  2. Utilizza psql per creare local_table eseguendo questo comando:

    CREATE TABLE local_table AS (SELECT * from foreign_table);

    Questo comando crea una copia della tabella BigQuery in una tabella AlloyDB locale standard. A seconda del flusso di lavoro dell'applicazione, puoi configurare l'estensione pg_cron di PostgreSQL per aggiornare la tabella AlloyDB a intervalli periodici.

Configura una pianificazione per importare periodicamente i dati

Quando importi dati BigQuery, bigquery_fdw si connette alla tabella remota come tabella esterna, consentendoti di copiare i dati nell'archivio AlloyDB locale utilizzando CREATE TABLE ... AS (SELECT * FROM foreign_table).

Per mantenere aggiornata la tabella importata, puoi utilizzare l'estensione pg_cron per eseguire nuovamente questa query periodicamente e aggiornare i dati locali in base a una pianificazione.

Per configurare una pianificazione per importare periodicamente dati BigQuery o dati BigLake Iceberg in AlloyDB:

  1. Configura l'estensione bigquery_fdw.
  2. Abilita l'estensione pg_cron nell'istanza AlloyDB. Per saperne di più, vedi Estensioni di database supportate.
    1. Imposta il flag alloydb.enable_pg_cron su on. Per saperne di più, vedi alloydb.enable_pg_cron.
    2. Imposta il flag cron.database_name sul nome del database in cui hai installato l'estensione bigquery_fdw e in cui vuoi eseguire le query SQL per aggiornare i dati. Per saperne di più, vedi Flag di database supportati.

  3. Per aggiornare periodicamente una copia locale della tabella esterna, esegui questi comandi nel database in cui hai installato l'estensione bigquery_fdw:

    CREATE EXTENSION pg_cron;
SELECT cron.schedule(JOB_NAME, SCHEDULE, 'CREATE TABLE IF NOT EXISTS local_table_copy AS (SELECT * FROM foreign_table); DROP TABLE IF EXISTS local_table; ALTER TABLE local_table_copy RENAME TO local_table;');

    Sostituisci quanto segue:

    • JOB_NAME: il nome del job.
    • SCHEDULE: la pianificazione del job.

    Per saperne di più, vedi Che cos'è pg_cron?.

Mapping dei tipi di dati

Quando definisci una tabella esterna utilizzando bigquery_fdw, esegui il mapping dei tipi di dati BigQuery ai tipi PostgreSQL appropriati.

La tabella seguente elenca i mapping dei tipi di dati tra BigQuery e AlloyDB.

Tipi di dati delle tabelle BigQuery Tipi di dati delle tabelle esterne PostgreSQL consigliati

BOOLEAN

BOOLEAN

INTEGER (INT64)

BIGINT

FLOAT (FLOAT64)

DOUBLE PRECISION

STRING

VARCHAR

NUMERIC

NUMERIC(38, 9)

NUMERIC(P[, S])

NUMERIC(P, S)

BIGNUMERIC

NUMERIC(77, 38)

BIGNUMERIC(P[, S])

NUMERIC(P, S)

DATE

DATE

TIMESTAMP

TIMESTAMPTZ

TIME

TIME

JSON

JSONB

BYTES

BYTEA

GEOGRAPHY

GEOGRAPHY(POINT), ...

Per saperne di più, vedi PostGIS_Geography.

DATETIME

TIMESTAMP

ARRAY

VECTOR(N)

N è la dimensione del vettore. Devi impostare il bigquery_fdw.enable_vector_downcasting flag nella sessione. Poiché il tipo VECTOR in AlloyDB utilizza il tipo float4 potresti riscontrare una perdita di precisione in questa conversione.

Per saperne di più, vedi pgvector estensione.

Passaggi successivi