Exporter des données vers Pub/Sub (ETL inversé)

Ce document explique comment configurer un RETL inversé (extraction, transformation et chargement) depuis BigQuery vers Pub/Sub. Pour ce faire, utilisez l'instruction EXPORT DATA dans une requête continue pour exporter les données depuis BigQuery vers Sujet Pub/Sub.

Vous pouvez utiliser un workflow RETL vers Pub/Sub pour combiner les fonctionnalités d'analyse de BigQuery avec le service de messagerie instantanée asynchrone et évolutif à l'échelle mondiale de Pub/Sub. Ce workflow vous permet de diffuser des données vers des applications et des services en aval à l'aide d'une méthode basée sur des événements.

Prérequis

Vous devez créer un compte de service. Un compte de service est nécessaire pour exécuter une requête continue qui exporte les résultats vers un sujet Pub/Sub.

Vous devez créer un sujet Pub/Sub pour recevoir les résultats des requêtes continues sous forme de messages, ainsi qu'un abonnement Pub/Sub que l'application cible peut utiliser pour recevoir ces messages.

Rôles requis

Cette section fournit des informations sur les rôles et les autorisations nécessaires pour le compte utilisateur qui crée la requête continue, ainsi que pour le compte de service qui exécute la requête continue.

Autorisations du compte utilisateur

Pour créer un job dans BigQuery, le compte utilisateur doit disposer de l'autorisation IAM bigquery.jobs.create. Chacun des rôles IAM suivants accorde l'autorisation bigquery.jobs.create :

Pour envoyer un job qui s'exécute à l'aide d'un compte de service, le compte utilisateur doit disposer du rôle Utilisateur du compte de service (roles/iam.serviceAccountUser). Si vous utilisez le même compte utilisateur pour créer le compte de service, celui-ci doit disposer du rôle Administrateur de compte de service (roles/iam.serviceAccountAdmin). Pour obtenir des informations sur la manière de limiter l'accès d'un utilisateur à un seul compte de service plutôt qu'à tous les comptes de service d'un projet, consultez la section Attribuer un rôle unique.

Si le compte utilisateur doit activer les API nécessaires pour votre cas d'utilisation des requêtes continues, il doit disposer du rôle Administrateur Service Usage (roles/serviceusage.serviceUsageAdmin).

Autorisations de compte de service

Pour exporter des données à partir d'une table BigQuery, le compte de service doit disposer de l'autorisation IAM bigquery.tables.export. Chacun des rôles IAM suivants accorde l'autorisation bigquery.tables.export :

Pour que le compte de service puisse accéder à Pub/Sub, vous devez lui attribuer les deux rôles IAM suivants :

Vous pouvez également obtenir les autorisations requises via des rôles personnalisés.

Avant de commencer

Activer les API BigQuery and Pub/Sub.

Activer les API

Exporter vers Pub/Sub

Utilisez l'instruction EXPORT DATA pour exporter des données vers un sujet Pub/Sub :

Console

Dans la console Google Cloud, accédez à la page BigQuery.

Accéder à BigQuery
Dans l'éditeur de requête, cliquez sur Plus > Paramètres de requête.
Dans la section Requête continue, cochez la case Utiliser le mode Requête continue.
Dans la zone de texte Compte de service, sélectionnez le compte de service que vous avez créé.
Cliquez sur Enregistrer.
Dans l'éditeur de requête, saisissez l'instruction suivante :
```
EXPORT DATA
OPTIONS (
format = 'CLOUD_PUBSUB',
uri = 'https://pubsub.googleapis.com/projects/PROJECT_ID/topics/TOPIC_ID'
) AS
(
QUERY
);
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- TOPIC_ID : ID du sujet Pub/Sub. Vous pouvez obtenir l'ID du sujet à partir de la page Sujets de la console Google Cloud.
- QUERY : instruction SQL permettant de sélectionner les données à exporter. L'instruction SQL ne doit contenir que des opérations compatibles.
Cliquez sur Exécuter.

bq

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Sur la ligne de commande, exécutez la requête continue à l'aide de la commande bq query avec les options suivantes :
- Définissez l'option --continuous sur true pour que la requête soit continue.
- Spécifiez l'option --connection_property pour spécifier un compte de service à utiliser.
```
bq query --project_id=PROJECT_ID --use_legacy_sql=false \
--continuous=true --connection_property=service_account=SERVICE_ACCOUNT_EMAIL \
'EXPORT DATA OPTIONS (format = "CLOUD_PUBSUB", uri = "https://pubsub.googleapis.com/projects/PROJECT_ID/topics/TOPIC_ID") AS (QUERY);'
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- SERVICE_ACCOUNT_EMAIL : adresse e-mail du compte de service. Vous pouvez obtenir l'adresse e-mail du compte de service sur la page Comptes de service de la console Google Cloud.
- QUERY : instruction SQL permettant de sélectionner les données à exporter. L'instruction SQL ne doit contenir que des opérations compatibles.

API

Exécutez la requête continue en appelant la méthode jobs.insert. Définissez les champs suivants dans la ressource JobConfigurationQuery de la ressource Job que vous transmettez :
- Définissez le champ continuous sur true pour rendre la requête continue.
- Spécifiez un compte de service à utiliser à l'aide du champ connection_property.
```
curl --request POST \
  'https://bigquery.googleapis.com/bigquery/v2/projects/PROJECT_ID/jobs'
  --header 'Authorization: Bearer $(gcloud auth print-access-token) \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '("configuration":("query":"EXPORT DATA OPTIONS (format = 'CLOUD_PUBSUB', uri = 'https://pubsub.googleapis.com/projects/PROJECT_ID/topics/TOPIC_ID') AS (QUERY);","useLegacySql":false,"continuous":true,"connectionProperties":["key": "service_account","value":"SERVICE_ACCOUNT_EMAIL"]))' \
  --compressed
```
Remplacez les éléments suivants :
- PROJECT_ID : ID de votre projet.
- QUERY : instruction SQL permettant de sélectionner les données à exporter. L'instruction SQL ne doit contenir que des opérations compatibles.
- SERVICE_ACCOUNT_EMAIL : adresse e-mail du compte de service. Vous pouvez obtenir l'adresse e-mail du compte de service sur la page Comptes de service de la console Google Cloud.

Exporter plusieurs colonnes vers Pub/Sub

Si vous souhaitez inclure plusieurs colonnes dans votre résultat, vous pouvez créer une colonne de type Struct contenant les valeurs de colonne, puis convertir la valeur Struct en chaîne JSON à l'aide de la fonction TO_JSON_STRING. L'exemple suivant exporte des données à partir de quatre colonnes, mises en forme en tant que chaîne JSON :

EXPORT DATA
  OPTIONS (
    format = 'CLOUD_PUBSUB',
    uri = 'https://pubsub.googleapis.com/projects/myproject/topics/taxi-real-time-rides')
AS (
  SELECT
    TO_JSON_STRING(
      STRUCT(
        ride_id,
        timestamp,
        latitude,
        longitude)) AS message
  FROM `myproject.real_time_taxi_streaming.taxi_rides`
  WHERE ride_status = 'enroute'
);

Optimisation des exportations

Si les performances de votre job de requête continue semblent être limitées par les ressources de calcul disponibles, essayez d'augmenter la taille de votre attribution de réservation d'emplacements CONTINUOUS BigQuery.

Limites

Les données exportées doivent consister en une seule colonne STRING ou BYTES. Vous pouvez choisir le nom de la colonne.
Vous devez utiliser une requête continue pour exporter vers Pub/Sub.
Vous ne pouvez pas transmettre un schéma à un sujet Pub/Sub dans la requête continue.
Vous ne pouvez pas exporter de données vers un sujet Pub/Sub qui utilise un schéma.
Vous ne pouvez pas exporter de données contenant des valeurs NULL. Vous pouvez exclure les valeurs NULL des résultats de la requête en incluant un filtre WHERE message IS NOT NULL dans la requête continue.
Les données exportées ne doivent pas dépasser les quotas Pub/Sub.

Tarifs

Lorsque vous exportez des données dans une requête continue, la tarification des calculs de capacité BigQuery s'applique. Pour exécuter des requêtes continues, vous devez disposer d'une réservation qui utilise l'édition Enterprise ou Enterprise Plus et d'une attribution de réservation qui utilise le type de job CONTINUOUS.

Une fois les données exportées, l'utilisation de Pub/Sub vous est facturée. Pour plus d'informations, consultez les tarifs de Pub/Sub.