Utiliser des requêtes Dataflow SQL paramétrées

Cette page explique comment exécuter des requêtes Dataflow SQL paramétrées.

Dataflow SQL accepte les paramètres de requête nommés et positionnels. Spécifiez les paramètres de requête avec la syntaxe suivante :

Paramètres nommés avec le caractère @ suivi d'un identifiant, tel que @param_name
Paramètres positionnels avec le caractère ?

Vous pouvez utiliser des paramètres nommés ou positionnels dans une requête, mais pas les deux.

Exécuter des requêtes paramétrées

Pour exécuter une requête paramétrée, définissez le type de données et la valeur des paramètres de requête. Le type et la valeur des paramètres de requête doivent correspondre au type des objets de base de données représentés par les paramètres.

Vous pouvez définir des paramètres de requête à l'aide de la console Google Cloud ou Google Cloud CLI :

Console

Pour définir des paramètres de requête, utilisez l'interface utilisateur Dataflow SQL :

Accédez à l'interface utilisateur Dataflow SQL.

Accéder à l'interface utilisateur Dataflow SQL
Saisissez la requête Dataflow SQL dans l'éditeur de requête.

Par exemple, la requête suivante sélectionne les courses en taxi qui coûtent au moins le prix spécifié :
```
SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
 ride_status = "dropoff"
 AND meter_reading >= @price_min
```
Cliquez sur Créer une tâche Cloud Dataflow pour ouvrir un panneau d'options de tâche.
Dans la section Paramètres de requête SQL du panneau, renseignez les champs Nom, Type et Valeur. Dans l'exemple ci-dessus, le nom du paramètre est price_min et le type est FLOAT64.

Si le paramètre est positionnel, omettez le champ Nom.
(Facultatif) Si la requête contient plus de paramètres, cliquez sur Ajouter un paramètre.

La console Google Cloud n'accepte pas les paramètres ARRAY ni STRUCT. Pour spécifier des paramètres ARRAY ou STRUCT, utilisez la CLI Google Cloud.

gcloud

Pour définir des paramètres de requête, utilisez la commande gcloud dataflow sql query et les options --parameter.

Vous pouvez répéter l'option --parameter pour chaque paramètre de requête. Définissez chaque option --parameter sur name:type:value.

La commande suivante exécute une requête paramétrée sur un flux Pub/Sub de courses en taxi :

gcloud dataflow sql query \
  --job-name=job-name \
  --region=region \
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter=status:STRING:dropoff \
  --parameter=price_min:FLOAT64:5.5 \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  ride_status = @status
  AND meter_reading >= @price_min'

Si le paramètre est positionnel, définissez les options --parameter sur :type:value.

Utiliser des tableaux dans des requêtes paramétrées

Pour utiliser un type de tableau dans un paramètre de requête, définissez le type sur ARRAY<data-type>. Construisez la valeur sous la forme d'une liste entre crochets d'éléments séparés par des virgules, comme ["pickup", "enroute", "dropoff"].

Console

L'interface utilisateur Dataflow SQL n'accepte pas les paramètres ARRAY dans les requêtes Dataflow SQL.

gcloud

Pour définir un paramètre de tableau, définissez l'option --parameter de la commande gcloud dataflow sql query sur name:ARRAY<data-type>:value.

La commande suivante exécute une requête avec un paramètre de tableau nommé dans un flux Pub/Sub de courses en taxi :

gcloud dataflow sql query \
  --job-name=job-name
  --region=region
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter='status:ARRAY<STRING>:["pickup", "enroute", "dropoff"]' \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  ride_status IN UNNEST(@status)'

Pour en savoir plus, consultez la documentation de référence de Dataflow SQL sur les types ARRAY.

Utiliser des valeurs d'horodatage dans des requêtes paramétrées

Pour utiliser un horodatage dans un paramètre de requête, définissez le type sur STRING, mais construisez la valeur au format d'horodatage canonique.

Console

Pour définir des paramètres d'horodatage, utilisez l'interface utilisateur Dataflow SQL :

Accédez à l'interface utilisateur Dataflow SQL.

Accéder à l'interface utilisateur Dataflow SQL
Saisissez la requête Dataflow SQL dans l'éditeur de requête.

Par exemple, la requête suivante sélectionne les courses en taxi effectuées après la date spécifiée :
```
SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  event_timestamp >= TIMESTAMP (@date_min)
```
Cliquez sur Créer une tâche Cloud Dataflow pour ouvrir un panneau d'options de tâche.
Dans la section Paramètres de requête SQL, cliquez sur Ajouter un paramètre.
Dans le champ Type, sélectionnez STRING.
Dans le champ Valeur, saisissez un horodatage.

gcloud

Pour définir un paramètre d'horodatage, définissez l'option --parameter de la commande gcloud dataflow sql query sur name:STRING:value.

La commande suivante exécute une requête avec un paramètre d'horodatage nommé sur un flux Pub/Sub de courses en taxi :

gcloud dataflow sql query \
  --job-name=job-name \
  --region=region \
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter='date_min:STRING:2020-01-01 00:00:00.000 UTC' \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  event_timestamp >= TIMESTAMP (@date_min)'

Pour en savoir plus, consultez la documentation de référence de Dataflow SQL sur les types TIMESTAMP.

Utiliser des structures dans des requêtes paramétrées

Pour utiliser une structure dans un paramètre de requête, définissez le type sur STRUCT<field-name data-type, ...>. Construisez la valeur sous la forme d'une liste de paires clé/valeur séparées par une virgule.

Par exemple, STRUCT<date_min TIMESTAMP, status STRING> définit une structure contenant un champ nommé date_min de type TIMESTAMP et un second champ nommé status de type STRING.

Console

L'interface utilisateur Dataflow SQL n'accepte pas les paramètres STRUCT dans les requêtes Dataflow SQL.

gcloud

Pour définir un paramètre de tableau, définissez l'option --parameter de la commande gcloud dataflow sql query sur name:STRUCT<field-name data-type, ...>:value.

La commande suivante exécute une requête avec un paramètre de structure nommé sur un flux Pub/Sub de courses en taxi :

gcloud dataflow sql query \
  --job-name=job-name \
  --region=region \
  --bigquery-dataset=destination-dataset \
  --bigquery-table=destination-table \
  --parameter='rides:STRUCT<riders_min INT64, status STRING>:
  {"riders_min": 2, "status": "dropoff"}' \
'SELECT *
FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`
WHERE
  passenger_count >= @rides.riders_min
  AND ride_status = @rides.status'

Pour en savoir plus, consultez la documentation de référence de Dataflow SQL sur les types STRUCT.