Charger des données PostgreSQL dans BigQuery

Vous pouvez charger des données de PostgreSQL vers BigQuery à l'aide du connecteur du service de transfert de données BigQuery pour PostgreSQL. Le connecteur est compatible avec les instances PostgreSQL hébergées dans votre environnement sur site, Cloud SQL et d'autres fournisseurs de services cloud public tels qu'Amazon Web Services (AWS) et Microsoft Azure. Le service de transfert de données BigQuery vous permet de planifier des tâches de transfert récurrentes qui ajoutent vos dernières données de PostgreSQL à BigQuery.

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

• Vous ne pouvez choisir que TIMESTAMP colonnes comme colonnes de filigrane.
• L'ingestion incrémentielle n'est possible que pour les composants dont les colonnes de filigrane sont 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. 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.

Options d'ingestion de données

Les sections suivantes fournissent des informations sur les options d'ingestion de données lorsque vous configurez un transfert de données PostgreSQL.

Configuration TLS

Le connecteur PostgreSQL est compatible avec la configuration de la sécurité au niveau du transport (TLS) pour chiffrer vos transferts de données vers BigQuery. Le connecteur PostgreSQL est compatible avec les configurations TLS suivantes :

• Mode Chiffrer les données et valider l'autorité de certification et le nom d'hôte. Ce mode effectue une validation complète du serveur à l'aide de TLS sur le protocole TCPS. Il chiffre toutes les données en transit et vérifie que le certificat du serveur de base de données est signé par une autorité de certification (CA) de confiance. Ce mode vérifie également que le nom d'hôte auquel vous vous connectez correspond exactement au nom commun (CN) ou à un autre nom de l'objet (SAN) du certificat du serveur. Ce mode empêche les pirates informatiques d'utiliser un certificat valide pour un autre domaine afin d'usurper l'identité de votre serveur de base de données.

  Si votre nom d'hôte ne correspond pas au CN ou au SAN du certificat, la connexion échoue. Vous devez configurer une résolution DNS correspondant au certificat ou utiliser un autre mode de sécurité. Utilisez ce mode pour bénéficier de l'option la plus sécurisée et éviter les attaques de type "person in the middle" (PITM).

• Le mode Chiffrer les données et valider uniquement l'AC. Dans ce mode, toutes les données sont chiffrées à l'aide de TLS sur le protocole TCPS. Il permet également de vérifier que le certificat du serveur est signé par une autorité de certification à laquelle le client fait confiance. Toutefois, ce mode ne valide pas le nom d'hôte du serveur. Ce mode permet d'établir une connexion tant que le certificat est valide et émis par une autorité de certification de confiance, que le nom d'hôte du certificat corresponde ou non à celui auquel vous vous connectez.

  Utilisez ce mode si vous souhaitez vous assurer de vous connecter à un serveur dont le certificat est signé par une autorité de certification de confiance, mais dont le nom d'hôte n'est pas vérifiable ou dont vous ne contrôlez pas la configuration.

• Le mode Chiffrement uniquement. Ce mode chiffre toutes les données transférées entre le client et le serveur. Il n'effectue aucune validation de certificat ni de nom d'hôte.

  Ce mode offre un certain niveau de sécurité en protégeant les données en transit, mais il peut être vulnérable aux attaques PITM.

  Utilisez ce mode si vous devez vous assurer que toutes les données sont chiffrées, mais que vous ne pouvez pas ou ne souhaitez pas valider l'identité du serveur. Nous vous recommandons d'utiliser ce mode lorsque vous travaillez avec des VPC privés.

• Le mode Aucun chiffrement ni aucune validation. Ce mode ne chiffre aucune donnée et n'effectue aucune vérification de certificat ni de nom d'hôte. Toutes les données sont envoyées en texte brut.

  Nous vous déconseillons d'utiliser ce mode dans un environnement où des données sensibles sont traitées. Nous vous recommandons de n'utiliser ce mode qu'à des fins de test sur un réseau isolé où la sécurité n'est pas un problème.

Certificat de serveur approuvé (PEM)

Si vous utilisez le mode Chiffrer les données et valider l'autorité de certification et le nom d'hôte ou le mode Chiffrer les données et valider l'autorité de certification, vous pouvez également fournir un ou plusieurs certificats encodés au format PEM. Ces certificats sont requis dans certains cas où le service de transfert de données BigQuery doit vérifier l'identité de votre serveur de base de données lors de la connexion TLS :

• Si vous utilisez un certificat signé par une autorité de certification privée au sein de votre organisation ou un certificat autosigné, vous devez fournir la chaîne de certificats complète ou le certificat autosigné unique. Cela est nécessaire pour les certificats émis par les autorités de certification internes des services de fournisseurs de cloud gérés, tels qu'Amazon Relational Database Service (RDS).
• Si le certificat de votre serveur de base de données est signé par une autorité de certification publique (par exemple, Let's Encrypt, DigiCert ou GlobalSign), vous n'avez pas besoin de fournir de certificat. Les certificats racine de ces autorités de certification publiques sont préinstallés et approuvés par le service de transfert de données BigQuery.

Vous pouvez spécifier des certificats encodés au format PEM dans le champ Certificat PEM de confiance de la configuration du transfert, en respectant les exigences suivantes :

• Le certificat doit être une chaîne de certificats valide encodée au format PEM.
• Le certificat doit être entièrement correct. Toute absence de certificat dans la chaîne ou tout contenu incorrect entraîne l'échec de la connexion TLS.
• Pour un seul certificat, vous pouvez fournir un seul certificat autosigné à partir du serveur de base de données.
• Pour une chaîne de certificats complète émise par une AC privée, vous devez fournir la chaîne de confiance complète. Cela inclut le certificat du serveur de base de données, ainsi que tous les certificats CA intermédiaires et racines.

Transferts complets ou incrémentiels

Vous pouvez spécifier la façon dont les données sont chargées dans BigQuery en sélectionnant la préférence d'écriture Complète ou Incrémentielle dans la configuration du transfert lorsque vous configurez un transfert PostgreSQL. Les transferts incrémentiels sont disponibles en version preview.

Vous pouvez également configurer un transfert de données incrémentiel (aperçu) pour ne transférer que les données modifiées depuis le dernier transfert de données, au lieu de charger l'intégralité de l'ensemble de données à chaque transfert. Si vous avez configuré un transfert de données incrémentiel, vous devez spécifier les modes d'écriture append (ajouter) ou upsert (insérer/mettre à jour) pour définir la façon dont les données sont écrites dans BigQuery lors d'un transfert de données incrémentiel. Les sections suivantes décrivent les modes d'écriture disponibles.

Mode d'écriture "Ajouter"

Le mode d'écriture "Ajouter" n'insère que de nouvelles lignes dans votre table de destination. Cette option ajoute strictement les données transférées sans vérifier s'il existe des enregistrements, ce qui peut entraîner une duplication des données dans la table de destination.

Lorsque vous sélectionnez le mode "Ajouter", vous devez sélectionner une colonne de filigrane. Une colonne de filigrane est requise pour que le connecteur PostgreSQL puisse suivre les modifications apportées à la table source.

Mode d'écriture "Upsert"

Le mode d'écriture "upsert" met à jour une ligne ou en insère une nouvelle dans votre table de destination en recherchant une clé primaire. Vous pouvez spécifier une clé primaire pour permettre au connecteur PostgreSQL de déterminer les modifications nécessaires pour que votre table de destination reste à jour par rapport à votre table source. Si la clé primaire spécifiée est présente dans la table BigQuery de destination lors d'un transfert de données, le connecteur PostgreSQL met à jour cette ligne avec les nouvelles données de la table source. Si aucune clé primaire n'est présente lors d'un transfert de données, le connecteur PostgreSQL insère une nouvelle ligne.

Lorsque vous sélectionnez le mode "Upsert", vous devez sélectionner une colonne de filigrane et une clé primaire :

• Une colonne de filigrane est requise pour que le connecteur PostgreSQL puisse suivre les modifications apportées à la table source.

  Sélectionnez une colonne de filigrane qui est mise à jour chaque fois qu'une ligne est modifiée. Nous vous recommandons d'utiliser des colonnes semblables à celles de UPDATED_AT ou LAST_MODIFIED.

• La clé primaire peut être une ou plusieurs colonnes de votre tableau. Elle est requise pour que le connecteur PostgreSQL puisse déterminer s'il doit insérer ou mettre à jour une ligne.

  Sélectionnez les colonnes contenant des valeurs non nulles uniques pour toutes les lignes du tableau. Nous vous recommandons d'inclure des colonnes contenant des identifiants générés par le système, des codes de référence uniques (par exemple, des ID à incrémentation automatique) ou des ID de séquence immuables basés sur le temps.

  Pour éviter toute perte ou corruption de données, les colonnes de clé primaire que vous sélectionnez doivent contenir des valeurs uniques. Si vous avez des doutes sur l'unicité de la colonne de clé primaire choisie, nous vous recommandons d'utiliser plutôt le mode d'écriture "Ajouter".

Comportement d'ingestion incrémentielle

Lorsque vous modifiez le schéma de table dans votre source de données, les transferts de données incrémentiels à partir de ces tables sont reflétés dans BigQuery de la manière suivante :

Avant de commencer

• Créez un utilisateur dans la base de données PostgreSQL.
• Vérifiez que vous avez effectué toutes les actions requises pour activer le service de transfert de données BigQuery.
• Créez un ensemble de données BigQuery pour stocker vos données.
• Assurez-vous de disposer des rôles requis pour effectuer les tâches décrites dans ce document.

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 :

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 :

• Si vous effectuez un transfert depuis Cloud SQL, consultez Configurer l'accès à l'instance Cloud SQL.
• Si vous effectuez un transfert depuis AWS, consultez Configurer le VPNCloud de Confiance -AWS et le rattachement de réseau.
• Si vous effectuez un transfert depuis Azure, consultez Configurer le VPN Azure-Cloud de Confiance et l'association réseau.

Configurer un transfert de données PostgreSQL

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

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

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"}'

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

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

Mappage des types de données

Le tableau suivant mappe les types de données PostgreSQL avec les types de données BigQuery correspondants.

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.

Tarifs

Pour en savoir plus sur les tarifs des transferts PostgreSQL, consultez la page Tarifs du service de transfert de données.

Étapes suivantes

• Consultez la présentation du service de transfert de données BigQuery.
• Découvrez comment gérer les transferts, y compris obtenir des informations sur une configuration de transfert, lister les configurations de transfert et afficher l'historique d'exécution d'un transfert.
• Découvrez comment charger des données avec des opérations multicloud.