Charger des données PostgreSQL dans BigQuery

Vous pouvez charger des données depuis PostgreSQL vers BigQuery à l'aide du connecteur 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, ainsi qu'avec 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 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é. Si une deuxième exécution de transfert de données est planifiée avant la fin de la première, seule la première exécution de transfert de données sera effectuée. Toutes les autres exécutions de transfert de données qui chevauchent la première seront ignorées.
    • 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.

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 :

  • 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 "man-in-the-middle" (MITM).
  • Chiffrer les données et valider l'AC uniquement : ce mode chiffre toutes les données à l'aide de TLS sur le protocole TCPS et vérifie que le certificat du serveur est signé par une AC à 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 validation 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.
  • 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 vérifier l'identité du serveur. Nous vous recommandons d'utiliser ce mode lorsque vous travaillez avec des VPC privés.
  • Aucun chiffrement ni aucune validation : ce mode ne chiffre aucune donnée et n'effectue aucune validation 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 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 fournir des certificats encodés au format PEM dans le champ Certificat PEM de confiance lorsque vous créez une configuration de transfert PostgreSQL, 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 certificat autosigné unique à partir du serveur de base de données.
  • Pour une chaîne de certificats complète émise par une autorité de certification 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.

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

  5. 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.

  6. 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.

  7. 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.
    • Pour Mode TLS, sélectionnez une option dans le menu. Pour en savoir plus sur les modes TLS, consultez Configuration TLS.
    • Pour 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 Objets PostgreSQL à transférer, procédez comme suit :

      • Cliquez sur Parcourir pour sélectionner les tables PostgreSQL requises pour le transfert, puis cliquez sur Sélectionner.
      • Saisissez manuellement les noms des tables dans les objets PostgreSQL à transférer.

  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.
    • 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,
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

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.

Mappage des types de données

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

Type de données PostgreSQL Type de données BigQuery
array STRING
bigint INTEGER
bigserial INTEGER
bit(n) STRING
bit varying(n) STRING
boolean BOOLEAN
box STRING
bytea BYTES
character STRING
character varying STRING
cidr STRING
circle STRING
circularstring STRING
compoundcurve STRING
curvepolygon STRING
date DATE
double precision FLOAT
enum STRING
geometrycollection STRING
inet STRING
integer INTEGER
interval STRING
json JSON
jsonb JSON
line STRING
linestring STRING
lseg STRING
macaddr STRING
macaddr8 STRING
money STRING
multicurve STRING
multilinestring STRING
multipoint STRING
multipolygon STRING
multisurface STRING
numeric(precision, scale)/decimal(precision, scale) NUMERIC
path STRING
point STRING
polygon STRING
polyhedralsurface STRING
range STRING
real FLOAT
serial INTEGER
smallint INTEGER
smallserial INTEGER
text STRING
time [ (p) ] [ without timezone ] TIMESTAMP
time [ (p) ] with time zone TIMESTAMP
tin STRING
timestamp [ (p) ] [ without timezone ] TIMESTAMP
timestamp [ (p) ] with time zone TIMESTAMP
triangle STRING
tsquery STRING
tsvector STRING
uuid STRING
xml STRING

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.

Étapes suivantes