Charger des données PostgreSQL dans BigQuery

Pour planifier des transferts de données récurrents de PostgreSQL vers BigQuery, vous pouvez créer une configuration de transfert afin de spécifier les objets de données à transférer et la fréquence de transfert des données. Une fois la configuration du transfert définie, le service de transfert de données BigQuery transfère les dernières données dans une table BigQuery selon la planification spécifiée.

Pour obtenir des informations générales sur les transferts PostgreSQL, y compris les options de configuration, consultez Présentation des transferts de données PostgreSQL.

Limites

Les transferts de données PostgreSQL sont soumis aux limitations suivantes :

  • Le nombre maximal d'exécutions de transfert simultanées vers une seule base de données PostgreSQL est déterminé par le nombre maximal de connexions simultanées acceptées par la base de données PostgreSQL. Le nombre de jobs de transfert simultanés doit être limité à une valeur inférieure au nombre maximal de connexions simultanées acceptées par la base de données PostgreSQL.

  • Une seule configuration de transfert ne peut prendre en charge qu'une seule exécution de transfert de données à un moment donné. Lorsqu'un deuxième transfert de données est programmé avant la fin du premier, seul le premier transfert de données est effectué. Tous les autres transferts de données qui chevauchent le premier sont ignorés.

    Pour éviter les transferts ignorés dans une même configuration de transfert, nous vous recommandons d'augmenter la durée entre les transferts de données volumineux en configurant la fréquence de répétition.

  • Lors d'un transfert de données, le connecteur PostgreSQL identifie les colonnes clés indexées et partitionnées pour transférer vos données par lots en parallèle. C'est pourquoi nous vous recommandons de spécifier des colonnes de clé primaire ou d'utiliser des colonnes indexées dans votre tableau pour améliorer les performances et réduire le taux d'erreur dans vos transferts de données. Tenez compte des points suivants :

    • Si vous avez des contraintes de clé primaire ou d'index, seuls les types de colonnes suivants sont acceptés pour la création de lots parallèles :
      • INTEGER
      • TINYINT
      • SMALLINT
      • FLOAT
      • REAL
      • DOUBLE
      • NUMERIC
      • BIGINT
      • DECIMAL
      • DATE
    • Les transferts de données PostgreSQL qui n'utilisent pas de clé primaire ni de colonnes indexées ne peuvent pas accepter plus de 2 000 000 d'enregistrements par table.

Limites des transferts incrémentiels

Les transferts PostgreSQL incrémentiels sont soumis aux limitations suivantes :

  • Vous ne pouvez choisir que TIMESTAMP colonnes comme colonnes de filigrane.
  • L'ingestion incrémentielle n'est possible que pour les composants comportant des colonnes de filigrane valides.
  • Les valeurs d'une colonne de filigrane doivent augmenter de manière monotone.
  • Les transferts incrémentiels ne peuvent pas synchroniser les opérations de suppression dans la table source.
  • Une même configuration de transfert ne peut prendre en charge que l'ingestion incrémentielle ou complète.
  • Vous ne pouvez pas mettre à jour les objets de la liste asset après la première exécution d'ingestion incrémentielle.
  • Vous ne pouvez pas modifier le mode d'écriture dans une configuration de transfert après la première exécution d'ingestion incrémentielle.
  • Vous ne pouvez pas modifier la colonne de filigrane ni la clé primaire après la première exécution d'ingestion incrémentielle.
  • La table BigQuery de destination est mise en cluster à l'aide de la clé primaire fournie et est soumise aux limites des tables en cluster.

  • Lorsque vous mettez à jour une configuration de transfert existante pour la première fois en mode d'ingestion incrémentielle, le premier transfert de données après cette mise à jour transfère toutes les données disponibles à partir de votre source de données. Tous les transferts de données incrémentiels ultérieurs ne transféreront que les lignes nouvelles et mises à jour de votre source de données.

  • Nous vous recommandons de créer des index sur la colonne de filigrane. Ce connecteur utilise des colonnes de filigrane pour les filtres dans les transferts incrémentaux. L'indexation de ces colonnes peut donc améliorer les performances.

  • Lorsque vous effectuez un transfert incrémentiel, vous devez utiliser le mappage des types de données mis à jour.

Avant de commencer

Rôles requis

Si vous avez l'intention de configurer des notifications d'exécution de transfert pour Pub/Sub, assurez-vous de disposer de l'autorisation Identity and Access Management (IAM) pubsub.topics.setIamPolicy. Les autorisations Pub/Sub ne sont pas nécessaires si vous ne configurez que des notifications par e-mail. Pour plus d'informations, consultez la page Notifications d'exécution du service de transfert de données BigQuery.

Pour obtenir les autorisations nécessaires pour créer un transfert de données Service de transfert de données BigQuery, demandez à votre administrateur de vous accorder le rôle IAM Administrateur BigQuery (roles/bigquery.admin) sur votre projet. Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Ce rôle prédéfini contient les autorisations requises pour créer un transfert de données du service de transfert de données BigQuery. Pour connaître les autorisations exactes requises, développez la section Autorisations requises :

Autorisations requises

Les autorisations suivantes sont requises pour créer un transfert de données du service de transfert de données BigQuery :

  • Autorisations du service de transfert de données BigQuery :
    • bigquery.transfers.update
    • bigquery.transfers.get
  • Autorisations BigQuery :
    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy
    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy
    • bigquery.jobs.create

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

Pour en savoir plus, consultez Accorder l'accès bigquery.admin.

Connexions réseau

Si aucune adresse IP publique n'est disponible pour la connexion à la base de données PostgreSQL, vous devez configurer un rattachement de réseau.

Pour obtenir des instructions détaillées sur la configuration réseau requise, consultez les documents suivants :

Configurer un transfert de données PostgreSQL

Ajoutez des données PostgreSQL dans BigQuery en configurant un transfert à l'aide de l'une des options suivantes :

Console

  1. Accédez à la page Transferts de données.

    Accéder à la page Transferts de données

  2. Cliquez sur Créer un transfert.

  3. Dans la section Type de source, sélectionnez PostgreSQL pour Source.

  4. Dans la section Data source details (Détails de la source de données), procédez comme suit :

    • Sous Rattachement de réseau, sélectionnez un rattachement de réseau existant ou cliquez sur Créer un rattachement de réseau. Pour en savoir plus, consultez la section Connexions réseau de ce document.
    • Pour Hôte, saisissez le nom d'hôte ou l'adresse IP du serveur de base de données PostgreSQL.
    • Pour Numéro de port, saisissez le numéro de port du serveur de base de données PostgreSQL.
    • Dans le champ Nom de la base de données, saisissez le nom de la base de données PostgreSQL.
    • Pour Nom d'utilisateur, saisissez le nom d'utilisateur PostgreSQL qui établit la connexion à la base de données PostgreSQL.
    • Pour Mot de passe, saisissez le mot de passe de l'utilisateur PostgreSQL qui établit la connexion à la base de données PostgreSQL.
    • Dans le champ Mode TLS, sélectionnez une option dans le menu. Pour en savoir plus sur les modes TLS, consultez Configuration TLS.
    • Dans le champ Certificat PEM approuvé, saisissez le certificat public de l'autorité de certification qui a émis le certificat TLS du serveur de base de données. Pour en savoir plus, consultez Certificat de serveur approuvé (PEM).
    • Pour Activer l'ancien mappage, sélectionnez true (par défaut) pour utiliser l'ancien mappage des types de données. Sélectionnez false pour utiliser le nouveau mappage des types de données. Si vous effectuez un transfert incrémentiel, cette valeur doit être définie sur false. Pour en savoir plus sur les modifications apportées au mappage des types de données, consultez 16 mars 2027. serveur de base de données.
    • Pour Type d'ingestion, sélectionnez Complet ou Incrémentiel.
      • Si vous sélectionnez Incrémentiel (Aperçu), pour Mode d'écriture, sélectionnez Ajouter ou Faire un upsert. Pour en savoir plus sur les différents modes d'écriture, consultez Transferts complets ou incrémentiels.

    • Pour Objets PostgreSQL à transférer, cliquez sur Parcourir.

      Sélectionnez les objets à transférer vers l'ensemble de données de destination BigQuery. Vous pouvez également saisir manuellement dans ce champ les objets à inclure dans le transfert de données.

      • Si vous avez sélectionné Ajouter comme mode d'écriture incrémentielle, vous devez sélectionner une colonne comme colonne de filigrane.
      • Si vous avez sélectionné Faire un upsert comme mode d'écriture incrémentielle, vous devez sélectionner une colonne comme colonne de filigrane, puis sélectionner une ou plusieurs colonnes comme clé primaire.

  5. Dans la section Nom de la configuration de transfert, sous Nom à afficher, saisissez le nom du transfert. Ce nom peut correspondre à n'importe quelle valeur permettant d'identifier le transfert si vous devez le modifier ultérieurement.

  6. Dans la section Options de programmation, procédez comme suit :

    • Sélectionnez une fréquence de répétition. Si vous sélectionnez l'option Heures, Jours (par défaut), Semaines ou Mois, vous devez également spécifier une fréquence. Vous pouvez également sélectionner l'option Personnalisée pour créer une fréquence de répétition plus spécifique. Si vous sélectionnez l'option À la demande, le transfert de données s'exécute uniquement lorsque vous le déclenchez manuellement.
    • Le cas échéant, sélectionnez l'option Commencer ou Commencer à l'heure définie, puis indiquez une date de début et une heure d'exécution.

  7. Dans la section Paramètres de destination, pour le champ Ensemble de données, sélectionnez l'ensemble de données que vous avez créé pour stocker vos données, ou cliquez sur Créer un ensemble de données et créez-en un à utiliser comme ensemble de données de destination.

  8. Facultatif : dans la section Options de notification, procédez comme suit :

    • Pour activer les notifications par e-mail, cliquez sur le bouton Notifications par e-mail pour l'activer. Lorsque vous activez cette option, l'administrateur de transfert reçoit une notification par e-mail en cas d'échec de l'exécution du transfert.
    • Pour configurer les notifications d'exécution Pub/Sub pour votre transfert, activez l'option Notifications Pub/Sub. Vous pouvez sélectionner le nom de votre sujet ou cliquer sur Créer un sujet pour en créer un.

  9. Cliquez sur Enregistrer.

bq

Saisissez la commande bq mk, puis spécifiez l'indicateur de création de transfert --transfer_config :

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

Remplacez les éléments suivants :

  • PROJECT_ID (facultatif) : ID de votre projet Google Cloud . Si l'indicateur --project_id n'est pas fourni pour spécifier un projet particulier, le projet par défaut est utilisé.
  • DATA_SOURCE : source de données, qui est postgresql.
  • DISPLAY_NAME : nom à afficher de la configuration de transfert de données. Ce nom peut correspondre à toute valeur permettant d'identifier le transfert si vous devez le modifier ultérieurement.
  • DATASET : ensemble de données cible de la configuration de transfert de données.

  • PARAMETERS correspond aux paramètres de la configuration de transfert créée, au format JSON. Exemple : --params='{"param":"param_value"}'. Voici les paramètres d'un transfert PostgreSQL :

    • connector.networkAttachment (facultatif) : nom du rattachement de réseau permettant de se connecter à la base de données PostgreSQL.
    • connector.database : nom de la base de données PostgreSQL.
    • connector.endpoint.host : nom d'hôte ou adresse IP de la base de données.
    • connector.endpoint.port : numéro de port de la base de données.
    • connector.authentication.username : nom d'utilisateur de la base de données.
    • connector.authentication.password : mot de passe de l'utilisateur de la base de données.
    • connector.tls.mode : spécifiez une configuration TLS à utiliser avec ce transfert :
      • ENCRYPT_VERIFY_CA_AND_HOST pour chiffrer les données et valider l'autorité de certification et le nom d'hôte
      • ENCRYPT_VERIFY_CA pour chiffrer les données et valider uniquement l'autorité de certification
      • ENCRYPT_VERIFY_NONE pour le chiffrement des données uniquement
      • DISABLE pour aucune vérification ni aucun chiffrement
    • connector.tls.trustedServerCertificate : (facultatif) fournissez un ou plusieurs certificats encodés au format PEM. Obligatoire uniquement si connector.tls.mode est ENCRYPT_VERIFY_CA_AND_HOST ou ENCRYPT_VERIFY_CA.
    • ingestionType : spécifiez full ou incremental. Les transferts incrémentiels sont disponibles en version preview. Pour en savoir plus, consultez Transferts complets ou incrémentiels.
    • writeMode : spécifiez WRITE_MODE_APPEND ou WRITE_MODE_UPSERT.
    • watermarkColumns : spécifiez les colonnes de votre tableau comme colonnes de filigrane. Ce champ est obligatoire pour les transferts incrémentaux.
    • primaryKeys : spécifiez les colonnes de votre tableau comme clés primaires. Ce champ est obligatoire pour les transferts incrémentaux.
    • connector.legacyMapping : défini sur true (par défaut) pour utiliser l'ancien mappage des types de données. Définissez la valeur sur false pour utiliser le nouveau mappage des types de données. Si vous effectuez un transfert incrémentiel, cette valeur doit être false. Pour en savoir plus sur les modifications apportées au mappage des types de données, consultez 16 mars 2027.
    • assets : liste des noms des tables PostgreSQL à transférer depuis la base de données PostgreSQL dans le cadre du transfert.

Par exemple, la commande suivante crée un transfert PostgreSQL appelé My Transfer :

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

Lorsque vous spécifiez plusieurs composants lors d'un transfert incrémentiel, les valeurs des champs watermarkColumns et primaryKeys correspondent à la position des valeurs dans le champ assets. Dans l'exemple suivant, dep_id correspond à la table DB1/USER1/DEPARTMENT, tandis que report_by et report_title correspondent à la table DB1/USER1/EMPLOYEES.

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],

API

Utilisez la méthode projects.locations.transferConfigs.create et fournissez une instance de la ressource TransferConfig.

Lorsque vous enregistrez la configuration du transfert, le connecteur PostgreSQL déclenche automatiquement une exécution du transfert en fonction de l'option de programmation que vous avez choisie. À chaque exécution du transfert, le connecteur PostgreSQL transfère toutes les données disponibles de PostgreSQL vers BigQuery.

Pour exécuter manuellement un transfert de données en dehors de votre calendrier habituel, vous pouvez lancer une exécution de remplissage.

Résoudre les problèmes

Si vous rencontrez des problèmes lors de la configuration de votre transfert de données, consultez la section Problèmes de transfert PostgreSQL.

Transférer les métadonnées

Vous pouvez également utiliser le connecteur PostgreSQL pour transférer des métadonnées vers Knowledge Catalog. Pour en savoir plus, consultez Charger des métadonnées PostgreSQL dans le catalogue de connaissances.

Étapes suivantes