Exporter des données vers AlloyDB (ETL inversé)

Un workflow d'extraction, de transformation et de chargement inversé (ETL inversé) de BigQuery vers AlloyDB pour PostgreSQL vous permet de déplacer des données de BigQuery vers une base de données AlloyDB. Vous pouvez utiliser l'instruction EXPORT DATA pour traiter ou filtrer les données avec une requête avant de les exporter, ou vous pouvez synchroniser votre table BigQuery avec AlloyDB pour copier ou dupliquer l'intégralité de la table.

Avant de commencer

  • Créez un cluster et une instance AlloyDB, qui incluent une base de données, un schéma et une table, pour recevoir les données exportées. Vous devez disposer du schéma et de la table cibles avant d'exécuter le job d'exportation. Pour le mode UPSERT, la table de destination doit comporter une contrainte PRIMARY KEY ou au moins une contrainte UNIQUE.

  • L'instance AlloyDB cible doit être une instance PRIMARY à l'état READY.

  • Créez une connexion BigQuery pour vous connecter à votre instance AlloyDB. La connexion doit utiliser l'authentification par nom d'utilisateur et mot de passe. L'utilisateur de la base de données doit disposer des droits suivants :

    Type de table Droits associés à la table de destination Droits du schéma de destination Droits de base de données Remarques
    Tables avec une contrainte PRIMARY KEY ou UNIQUE INSERT, UPDATE, SELECT USAGE CONNECT, TEMPORARY Obligatoire pour les modes UPSERT et APPEND afin de prendre en charge le remplacement automatique des nouvelles tentatives.
    Tables sans contraintes INSERT USAGE CONNECT
  • Pour utiliser cette fonctionnalité, vous devez créer une réservation de niveau BigQuery Enterprise ou Enterprise Plus. Les exportations vers AlloyDB ne sont compatibles qu'avec les réservations associées à une attribution QUERY.

  • Attribuez aux utilisateurs des rôles IAM (Identity and Access Management) incluant les autorisations nécessaires pour effectuer l'ensemble des tâches du présent document.

Rôles requis

Pour obtenir les autorisations nécessaires à l'exportation des données BigQuery vers AlloyDB, demandez à votre administrateur de vous accorder les rôles IAM suivants sur votre projet :

  • Pour exporter des données depuis une table BigQuery : Lecteur de données BigQuery (roles/bigquery.dataViewer)
  • Exécuter un job d'extraction : Utilisateur BigQuery (roles/bigquery.user)
  • Utiliser une connexion BigQuery : Utilisateur de connexion BigQuery (roles/bigquery.connectionUser)
  • Se connecter à une instance AlloyDB : client AlloyDB (roles/alloydb.client) - le compte de service de connexion

Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Vous pouvez également obtenir les autorisations requises via des rôles personnalisés ou d'autres rôles prédéfinis.

Limites

  • Les exportations AlloyDB ne sont compatibles qu'avec les exportations par lot. Vous ne pouvez pas utiliser de requêtes continues pour exporter vers AlloyDB.

  • Pour utiliser le mode UPSERT, la table AlloyDB de destination doit comporter une contrainte PRIMARY KEY ou au moins une contrainte UNIQUE, et votre requête SELECT doit projeter toutes les colonnes appartenant à cette contrainte. Vous ne pouvez exporter des tables sans clé primaire ni contrainte unique qu'en mode APPEND.

  • L'exportation de données en mode UPSERT présente un débit d'écriture inférieur et une surcharge de processeur et d'E/S plus élevée sur l'instance AlloyDB qu'en mode APPEND. Alors que le mode APPEND diffuse les lignes directement à l'aide de la commande COPY groupée de PostgreSQL, le mode UPSERT effectue une opération d'insertion ou de mise à jour (INSERT ... ON CONFLICT DO UPDATE), ce qui nécessite qu'AlloyDB vérifie les index uniques pour les clés existantes et mette à jour les lignes correspondantes. Lorsque vous n'exportez que les nouveaux enregistrements, utilisez le mode APPEND pour des performances optimales.

  • Si aucune contrainte n'est définie pour la table de destination, l'exécution de plusieurs exportations APPEND avec les mêmes données entraîne des lignes en double.

  • Nous vous déconseillons d'exécuter plusieurs jobs EXPORT DATA simultanés sur la même table AlloyDB. Cela peut entraîner un comportement imprévisible, comme une perte de données ou des échecs de job. Nous vous recommandons de vérifier qu'un seul job d'exportation écrit dans une table spécifique à la fois.

  • Seule l'authentification par nom d'utilisateur et mot de passe via une connexion BigQuery est acceptée.

  • Les types de données BigQuery ARRAY, BYTES, GEOGRAPHY, INTERVAL et STRUCT ne sont pas acceptés.

  • Si l'instruction SELECT BigQuery omet des colonnes qui existent dans la table AlloyDB cible, ces colonnes doivent autoriser les valeurs NULL ou avoir des valeurs par défaut définies dans AlloyDB. Si elles ont une contrainte NOT NULL et aucune valeur par défaut, l'exportation échouera.

  • Les exportations vers AlloyDB ne sont compatibles qu'avec les éditions BigQuery Enterprise ou Enterprise Plus. L'édition standard de BigQuery et le calcul à la demande ne sont pas compatibles. Pour en savoir plus, consultez Fonctionnalités d'administration.

  • La durée maximale d'un job BigQuery, tel qu'un job d'extraction vers AlloyDB, est de six heures. Pour les exportations très volumineuses, nous vous recommandons de les diviser en plusieurs tâches plus petites.

Considérations relatives aux emplacements

L'exportation de données vers AlloyDB est soumise à des exigences spécifiques concernant l'emplacement de votre ensemble de données BigQuery et de votre instance AlloyDB :

  • Exportations dans la même région : l'instance AlloyDB cible doit se trouver exactement dans la même région Google Cloud que l'ensemble de données BigQuery. Par exemple, un ensemble de données situé dans us-east1 ne peut être exporté que vers une instance AlloyDB située dans us-east1.

  • Exportations multirégionales :

    • Les ensembles de données de l'emplacement multirégional US ne peuvent être exportés que vers une instance AlloyDB située dans la région us-central1.
    • Les ensembles de données de l'emplacement multirégional EU ne peuvent être exportés que vers une instance AlloyDB située dans la région europe-west4.

Les exportations interrégionales autres que les combinaisons mentionnées précédemment ne sont pas acceptées.

Configurer des exportations avec alloydb_options

Vous pouvez utiliser l'option alloydb_options dans l'instruction EXPORT DATA pour spécifier le schéma, la table, le mode d'écriture et le nombre maximal de connexions AlloyDB de destination. La configuration est exprimée sous la forme d'une chaîne JSON. Seul le paramètre table est obligatoire. Tous les autres paramètres sont facultatifs.

Lors de la configuration de l'exportation, les colonnes de l'instruction SELECT doivent avoir des alias correspondant aux noms des colonnes de la table AlloyDB cible.

EXPORT DATA
  WITH CONNECTION `PROJECT_ID.LOCATION.CONNECTION_ID`
  OPTIONS(
    format='ALLOYDB',
    uri="https://alloydb.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/clusters/CLUSTER_ID/instances/INSTANCE_ID",
    alloydb_options="""{
      "schema": "SCHEMA_NAME",
      "table": "TABLE_NAME",
      "write_mode": "WRITE_MODE",
      "max_parallel_connections": MAX_CONNECTIONS
    }"""
  )
AS SELECT * FROM `mydataset.table1`;

Remplacez les éléments suivants :

  • PROJECT_ID : nom de votre projet Google Cloud .
  • LOCATION : emplacement de votre connexion et de votre instance cible.
  • CONNECTION_ID : nom de votre connexion BigQuery
  • CLUSTER_ID : nom de votre cluster AlloyDB.
  • INSTANCE_ID : nom de votre instance AlloyDB cible.
  • SCHEMA_NAME (facultatif) : nom du schéma de destination dans AlloyDB. Si aucune valeur n'est fournie, le schéma par défaut configuré pour l'utilisateur de la base de données est utilisé (généralement public).
  • TABLE_NAME : nom d'une table de destination existante dans AlloyDB, sans le préfixe de schéma.
  • WRITE_MODE (facultatif) : mode d'écriture à utiliser lors de l'exportation des données. Valeurs acceptées :
    • APPEND : par défaut, cette valeur insère directement les lignes exportées pour un débit plus rapide lorsque vous exportez de nouveaux enregistrements. Si la table de destination comporte une contrainte PRIMARY KEY ou UNIQUE et qu'un conflit de clé en double se produit (par exemple, parce que des clés correspondantes existent déjà dans la table ou qu'une tentative de job ayant échoué est relancée après l'écriture de données partielles), BigQuery relance automatiquement l'exportation en mode UPSERT. Si vous prévoyez que votre exportation mettra à jour des lignes existantes, spécifiez directement UPSERT pour éviter la surcharge d'une nouvelle tentative.
    • UPSERT : met à jour les enregistrements existants si une clé correspondante existe, ou insère de nouveaux enregistrements si aucune clé correspondante n'est trouvée. Nécessite une contrainte PRIMARY KEY ou UNIQUE sur la table de destination, avec toutes les colonnes de clé sélectionnées dans la requête. BigQuery met en correspondance les lignes à l'aide de la PRIMARY KEY de la table ou d'une contrainte UNIQUE si la table n'a pas de clé primaire. Si la table comporte des contraintes UNIQUE supplémentaires et qu'une ligne exportée contient une valeur en double dans l'une de ces colonnes, l'exportation échoue.
  • MAX_CONNECTIONS (facultatif) : nombre maximal de connexions parallèles simultanées entre les nœuds de calcul BigQuery et l'instance AlloyDB. Si elle est spécifiée, la valeur doit être supérieure ou égale à 1. Limiter les connexions peut éviter de surcharger l'instance cible lors d'exportations volumineuses.

Exemples d'instructions d'exportation

Cette section fournit des exemples d'instructions EXPORT DATA pour différents cas d'utilisation.

Exporter en mode APPEND

L'exemple suivant ajoute les champs sélectionnés de la table mydataset.table1 à une table AlloyDB nommée my_target_table :

EXPORT DATA
  WITH CONNECTION `myproject.us-central1.my-alloydb-conn`
  OPTIONS (
    format='ALLOYDB',
    uri="https://alloydb.googleapis.com/v1/projects/myproject/locations/us-central1/clusters/my-cluster/instances/my-instance",
    alloydb_options="""{
      "schema": "public",
      "table": "my_target_table",
      "write_mode": "APPEND"
    }"""
  )
AS SELECT
  col1 AS id,
  col2 AS name,
  col3 AS value
FROM
  `mydataset.table1`;

Exporter en mode UPSERT

L'exemple suivant effectue un upsert des enregistrements de mydataset.customers dans la table AlloyDB customer_profiles, ce qui inclut la mise à jour des enregistrements existants correspondant à la clé primaire customer_id et l'insertion de nouveaux enregistrements :

EXPORT DATA
  WITH CONNECTION `myproject.us-central1.my-alloydb-conn`
  OPTIONS (
    format='ALLOYDB',
    uri="https://alloydb.googleapis.com/v1/projects/myproject/locations/us-central1/clusters/my-cluster/instances/my-instance",
    alloydb_options="""{
      "schema": "public",
      "table": "customer_profiles",
      "write_mode": "UPSERT",
      "max_parallel_connections": 8
    }"""
  )
AS SELECT
  customer_id,
  email,
  status,
  updated_at
FROM
  `mydataset.customers`;

Synchroniser une table pour l'exportation

Pour les copies de tables complètes ou la mise en miroir automatisée sans transformations basées sur SQL, vous pouvez synchroniser votre table BigQuery directement avec AlloyDB. L'instruction EXPORT DATA vous permet d'utiliser une requête SELECT pour traiter, attribuer un alias ou filtrer les données avant leur déplacement. Vous pouvez également utiliser des synchronisations de tables pour une migration un-à-un d'une table BigQuery.

Les synchronisations de tables sont disponibles dans les modes suivants :

  • Importation ponctuelle : crée une copie indépendante et accessible en écriture d'une table BigQuery dans AlloyDB.
  • Synchronisation périodique (mise en miroir) : crée une table locale en lecture seule dans AlloyDB qui s'actualise automatiquement à partir de BigQuery selon une planification définie.

Pour en savoir plus, consultez Synchroniser des données BigQuery avec AlloyDB.

Tarifs

Lorsque vous exportez des données vers AlloyDB à l'aide de l'instruction EXPORT DATA, la tarification des calculs de capacité BigQuery s'applique.

Une fois les données exportées, leur stockage dans AlloyDB vous est facturé. Pour en savoir plus, consultez Tarifs d'AlloyDB pour PostgreSQL.