Importer des données BigQuery dans AlloyDB

Vous pouvez importer des données dans AlloyDB pour PostgreSQL à partir de tables intégrées, de vues matérialisées et de vues BigQuery, ainsi que de tables externes BigLake (telles que les tables gérées Apache Iceberg) et de tables externes standards. Iceberg est un format de table ouvert permettant de gérer et d'échanger des données.

En important des données, vous n'avez plus besoin de créer et de gérer des pipelines de données complexes et sujets aux erreurs qui déplacent manuellement les données de BigQuery vers AlloyDB. Pour en savoir plus, consultez la présentation de la synchronisation des données.

Cette page suppose que vous disposez d'un cluster AlloyDB et d'une instance principale, ainsi que d'un ensemble de données et de tables BigQuery. Pour en savoir plus, consultez Créer des ensembles de données et Créer et utiliser des tables.

Avant de commencer

  1. Configurez l'option bigquery_fdw.enabled flag sur l'instance AlloyDB.
  2. Consultez les mappages des types de données pour comprendre comment les types BigQuery sont représentés dans PostgreSQL lorsque vous utilisez 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. Pour créer une instance AlloyDB et vous y connecter, activez les APIs Cloud requises.

    Activer les API

  7. À l'étape Confirmer le projet, cliquez sur Suivant pour confirmer le nom du projet que vous allez modifier.

  8. À l'étape Activer les API, cliquez sur Activer pour activer les éléments suivants :

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

    L'API Service Networking est requise si vous prévoyez de configurer la connectivité réseau à AlloyDB à l'aide d'un réseau VPC qui réside dans le même Google Cloud projet qu'AlloyDB.

    Les API Compute Engine et Cloud Resource Manager sont requises si vous prévoyez de configurer la connectivité réseau à AlloyDB à l'aide d'un réseau VPC réseau qui réside dans un autre Google Cloud projet.

Rôles requis

Pour accorder un accès en lecture à l'ensemble de données BigQuery au compte de service du cluster AlloyDB, vous avez besoin des autorisations suivantes :

  • Lecteur de données BigQuery (roles/bigquery.dataViewer) ou tout rôle personnalisé disposant des autorisations bigquery.tables.get et bigquery.tables.getData. Lorsqu'il est accordé sur une table ou une vue, ce rôle fournit des autorisations permettant de lire les données et les métadonnées de la table ou de la vue.
  • Utilisateur de sessions de lecture BigQuery (roles/bigquery.readSessionUser) ou tout rôle personnalisé disposant des autorisations bigquery.readsessions.create et bigquery.readsessions.getData. Permet de créer et d'utiliser des sessions de lecture.

Accorder à AlloyDB l'accès à l'ensemble de données BigQuery

Après avoir configuré l'extension bigquery_fdw sur votre cluster AlloyDB, accordez au compte de service du cluster AlloyDB l'accès à l'ensemble de données BigQuery.

Pour utiliser la gcloud CLI, vous pouvez installer et initialiser la Google Cloud CLI, ou utiliser Cloud Shell.

  1. Ouvrez la gcloud CLI. Si la gcloud CLI n'est pas installée, installez-la et initialisez-la, ou utilisez Cloud Shell.

  2. Exécutez la gcloud beta alloydb clusters describe commande :

    gcloud beta alloydb clusters describe CLUSTER --region=REGION

    Remplacez les éléments suivants :

    • CLUSTER : ID du cluster AlloyDB.
    • REGION: emplacement du cluster AlloyDB, par exemple asia-east1 ou us-east1. Consultez la liste complète des régions dans Emplacements AlloyDB.

    Le résultat contient un champ serviceAccountEmail, qui correspond au compte de service de ce cluster. Vous pouvez également trouver le compte de service sur la page Détails du cluster.

  3. Accordez les autorisations requises. Pour en savoir plus, consultez Contrôler l'accès aux ressources avec IAM.

    Si le compte de service du cluster ne dispose pas des autorisations requises, les erreurs suivantes s'affichent lorsqu'une requête est exécutée sur la table BigQuery :

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

Configurer l'extension

  1. Créez l'extension.

    1. Connectez-vous à l'instance AlloyDB à l'aide du client psql en suivant les instructions de la section Connecter un client psql à une instance. Vous pouvez également utiliser AlloyDB Studio. Pour en savoir plus, consultez Gérer vos données à l'aide de la Google Cloud console.

    2. Exécutez la commande suivante :

      CREATE EXTENSION bigquery_fdw;

  2. Créez un serveur externe pour définir les paramètres de connexion de l'ensemble de données BigQuery distant.

    CREATE SERVER BIGQUERY_SERVER_NAME FOREIGN DATA WRAPPER bigquery_fdw;

    Remplacez les éléments suivants :

    • BIGQUERY_SERVER_NAME: identifiant unique du serveur externe. Définissez-le une seule fois dans une base de données donnée. Vous pouvez remplacer BIGQUERY_SERVER_NAME par le nom de votre serveur.

  3. Créez le mappage d'utilisateur en exécutant la commande CREATE USER MAPPING, qui spécifie les identifiants à utiliser lorsque vous vous connectez au serveur externe.

    CREATE USER MAPPING FOR USERNAME SERVER BIGQUERY_SERVER_NAME ;

    Remplacez les éléments suivants :

    • USERNAME: nom d'utilisateur de base de données ou utilisateur IAM qui accède à la table externe. Pour un utilisateur IAM, le nom doit être en minuscules et placé entre guillemets, car il contient des caractères spéciaux tels que @ et .).
    • BIGQUERY_SERVER_NAME: identifiant unique du serveur externe que vous avez créé.

  4. Définissez les tables externes qui correspondent aux tables auxquelles vous souhaitez accéder dans BigQuery à l'aide de la commande CREATE FOREIGN TABLE. Cette commande vous permet de définir la structure d'une table distante. La table externe peut contenir toutes les colonnes de la table source dans BigQuery ou un sous-ensemble de celles-ci.

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

    Remplacez les éléments suivants :

    • TABLENAME : nom de la table externe dans la base de données locale.
    • COLUMNX_NAME : nom de la colonne AlloyDB. Le nom de la colonne doit correspondre exactement au nom de la colonne correspondante dans la table source BigQuery. X indique que la table peut être créée avec plusieurs colonnes. Le nom doit également correspondre à la casse exacte de la colonne BigQuery. Si le nom de la colonne BigQuery contient des majuscules (par exemple, employeeID), l'identifiant AlloyDB doit être placé entre guillemets doubles (par exemple, "employeeID") pour conserver les lettres mixtes ou majuscules.
    • DATA_TYPE : type de données de la colonne.
    • BIGQUERY_SERVER_NAME: identifiant unique du serveur externe que vous avez créé.
    • BIGQUERY_PROJECT_ID: ID du projet dans lequel réside l'ensemble de données BigQuery.
    • BIGQUERY_DATASET_NAME: nom de l'ensemble de données BigQuery pour la table.
    • BIGQUERY_TABLE_NAME : nom de la table BigQuery.

    Une fois la table externe créée, vous pouvez l'interroger de la même manière que n'importe quelle table dans AlloyDB.

Importer des données

Pour importer des données BigQuery ou des données BigLake Iceberg stockées dans BigQuery vers AlloyDB, procédez comme suit :

  1. Identifiez une source de données existante ou créez une table BigQuery intégrée ou de nouvelles tables gérées Iceberg.

  2. Utilisez psql pour créer local_table en exécutant la commande suivante :

    CREATE TABLE local_table AS (SELECT * from foreign_table);

    Cette commande crée une copie de la table BigQuery dans une table AlloyDB locale standard. En fonction du workflow de votre application, vous pouvez configurer l'extension PostgreSQL pg_cron pour actualiser la table AlloyDB à intervalles réguliers.

Configurer une programmation pour importer régulièrement des données

Lorsque vous importez des données BigQuery, bigquery_fdw se connecte à la table distante en tant que table externe, ce qui vous permet de copier les données dans l'espace de stockage AlloyDB local à l'aide de CREATE TABLE ... AS (SELECT * FROM foreign_table).

Pour que votre table importée reste à jour, vous pouvez utiliser l'extension pg_cron pour réexécuter régulièrement cette requête et actualiser les données locales selon une programmation.

Pour configurer une programmation afin d'importer régulièrement des données BigQuery ou des données BigLake Iceberg dans AlloyDB, procédez comme suit :

  1. Configurez l'extension bigquery_fdw.
  2. Activez l'extension pg_cron sur l'instance AlloyDB. Pour en savoir plus, consultez Extensions de base de données compatibles.
    1. Définissez l'option alloydb.enable_pg_cron sur on. Pour en savoir plus, consultez alloydb.enable_pg_cron.
    2. Définissez l'option cron.database_name sur le nom de la base de données dans laquelle vous avez installé l'extension bigquery_fdw et dans laquelle vous souhaitez exécuter les requêtes SQL pour actualiser les données. Pour en savoir plus, consultez Options de base de données compatibles.

  3. Pour actualiser régulièrement une copie locale de la table externe, exécutez les commandes suivantes dans la base de données où vous avez installé l'extension 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;');

    Remplacez les éléments suivants :

    • JOB_NAME : nom du job.
    • SCHEDULE : programmation du job.

    Pour en savoir plus, consultez Qu'est-ce que pg_cron ?.

Mappages des types de données

Lorsque vous définissez une table externe à l'aide de bigquery_fdw, vous mappez les types de données BigQuery sur les types PostgreSQL appropriés.

Le tableau suivant répertorie les mappages des types de données entre BigQuery et AlloyDB.

Types de données de la table BigQuery data types Types de données de la table externe PostgreSQL recommandés

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

Pour en savoir plus, consultez PostGIS_Geography.

DATETIME

TIMESTAMP

ARRAY

VECTOR(N)

N correspond à la dimension du vecteur. Vous devez définir l'option bigquery_fdw.enable_vector_downcasting dans la session. Étant donné que le type VECTOR dans AlloyDB utilise float4 type, vous risquez de perdre en précision lors de cette conversion.

Pour en savoir plus, consultez l'extension pgvector

Étape suivante