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

BigQuery accepte les paramètres de requête pour parer au risque d'injection SQL dans les requêtes conçues pour faire intervenir des entrées utilisateur. Cette fonctionnalité n'est disponible qu'avec la syntaxe GoogleSQL. Les paramètres de requête peuvent être utilisés pour remplacer des expressions arbitraires. Vous ne pouvez pas substituer un paramètre à un identifiant, à un nom de colonne, à un nom de table ni aucune autre partie de la requête.

Pour spécifier un paramètre nommé, utilisez le caractère @ suivi d'un identifiant, tel que @param_name. Vous pouvez également utiliser la valeur d'espace réservé ? pour spécifier un paramètre positionnel. Notez qu'une requête peut utiliser des paramètres positionnels ou des paramètres nommés, mais pas les deux à la fois.

Lorsque vous utilisez un paramètre, la valeur fournie n'est pas consignée dans les journaux BigQuery afin de protéger les informations potentiellement sensibles.

Vous pouvez exécuter une requête paramétrée dans BigQuery de plusieurs manières :

L'éditeur de requête BigQuery Studio dans la console Google Cloud
Commande bq query de l'outil de ligne de commande bq
L'API
Bibliothèques clientes

Les exemples suivants montrent comment transmettre des valeurs de paramètres à une requête paramétrée :

Console

Pour exécuter une requête paramétrée dans la console Google Cloud , configurez les paramètres dans Paramètres de la requête, puis référencez-les dans votre requête SQL en faisant précéder chaque nom de paramètre par le caractère @.

Types de données acceptés : la console Google Cloud n'accepte que les requêtes paramétrées de types de données primitifs, tels que BIGNUMERIC, BOOL, BYTES, DATE, DATETIME, FLOAT64, GEOGRAPHY, INT64, INTERVAL, NUMERIC, STRING, TIME ou TIMESTAMP. Les types de données complexes, tels que ARRAY et STRUCT, ne sont pas acceptés dans la console Google Cloud .

Ajouter les paramètres dans la console Google Cloud

Accédez à la page BigQuery.

Accéder à BigQuery
Dans la barre d'outils de l'éditeur de requête, cliquez sur Plus, puis sélectionnez Paramètres de requête.
Dans le volet Paramètres de la requête, recherchez la section Paramètres de la requête, puis cliquez sur Ajouter un paramètre.
Pour chaque paramètre de votre requête, fournissez les informations suivantes :
- Nom : saisissez le nom du paramètre (n'incluez pas le caractère @).
- Type : sélectionnez le type de données du paramètre.
- Valeur : saisissez la valeur que vous souhaitez utiliser pour cette exécution.
Cliquez sur Enregistrer.

Transmettre des valeurs de paramètre à une requête dans la console Google Cloud

Dans l'éditeur de requête, saisissez une requête SQL à l'aide des paramètres que vous avez configurés à l'étape précédente. Pour les référencer, faites précéder leur nom du caractère @, comme indiqué dans l'exemple.

Exemple :
```
SELECT
    word,
    word_count
  FROM
    `bigquery-public-data.samples.shakespeare`
  WHERE
    corpus = @corpus
  AND
    word_count >= @min_word_count
  ORDER BY
    word_count DESC;
```
Pour cet exemple, vous devez ajouter le paramètre corpus en tant que STRING avec la valeur romeoandjuliet, et le paramètre min_word_count en tant que INT64 avec la valeur 250.

Si la requête contient un paramètre manquant ou non valide, un message d'erreur s'affiche. Cliquez sur Définir le paramètre dans le message d'erreur pour ajuster les paramètres du paramètre.
Pour exécuter la requête paramétrée dans l'éditeur de requête, 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.
L'indicateur --parameter permet de fournir des valeurs pour les paramètres selon le format name:type:value. Un nom vide engendre un paramètre positionnel. Le type peut être omis s'il s'agit de STRING.

L'option --parameter doit être utilisé conjointement avec l'option --use_legacy_sql=false pour spécifier la syntaxe GoogleSQL.

(Facultatif) Spécifiez votre emplacement à l'aide de l'option --location.
```
bq query \
   --use_legacy_sql=false \
   --parameter=corpus::romeoandjuliet \
   --parameter=min_word_count:INT64:250 \
   'SELECT
     word,
     word_count
   FROM
     `bigquery-public-data.samples.shakespeare`
   WHERE
     corpus = @corpus
   AND
     word_count >= @min_word_count
   ORDER BY
     word_count DESC;'
```

API

Pour utiliser des paramètres nommés, définissez parameterMode sur NAMED dans la configuration de la tâche query.

Dans la configuration de la tâche query, insérez la liste des paramètres dans queryParameters. Définissez l'élément name de chaque paramètre sur le paramètre @param_name utilisé dans la requête.

Activez la syntaxe GoogleSQL en définissant useLegacySql sur false.

{
  "query": "SELECT word, word_count FROM `bigquery-public-data.samples.shakespeare` WHERE corpus = @corpus AND word_count >= @min_word_count ORDER BY word_count DESC;",
  "queryParameters": [
    {
      "parameterType": {
        "type": "STRING"
      },
      "parameterValue": {
        "value": "romeoandjuliet"
      },
      "name": "corpus"
    },
    {
      "parameterType": {
        "type": "INT64"
      },
      "parameterValue": {
        "value": "250"
      },
      "name": "min_word_count"
    }
  ],
  "useLegacySql": false,
  "parameterMode": "NAMED"
}

Testez cette requête dans l'explorateur d'API Google.

Pour utiliser des paramètres positionnels, définissez parameterMode sur POSITIONAL dans la configuration de la tâche query.

C#

Avant d'essayer cet exemple, suivez les instructions de configuration pour C# du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour C#.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.

Pour utiliser des paramètres nommés :


using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryQueryWithNamedParameters
{
    public void QueryWithNamedParameters(string projectId = "your-project-id")
    {
        var corpus = "romeoandjuliet";
        var minWordCount = 250;

        // Note: Standard SQL is required to use query parameters.
        var query = @"
            SELECT word, word_count
            FROM `bigquery-public-data.samples.shakespeare`
            WHERE corpus = @corpus
            AND word_count >= @min_word_count
            ORDER BY word_count DESC";

        // Initialize client that will be used to send requests.
        var client = BigQueryClient.Create(projectId);

        var parameters = new BigQueryParameter[]
        {
            new BigQueryParameter("corpus", BigQueryDbType.String, corpus),
            new BigQueryParameter("min_word_count", BigQueryDbType.Int64, minWordCount)
        };

        var job = client.CreateQueryJob(
            sql: query,
            parameters: parameters,
            options: new QueryOptions { UseQueryCache = false });
        // Wait for the job to complete.
        job = job.PollUntilCompleted().ThrowOnAnyError();
        // Display the results
        foreach (BigQueryRow row in client.GetQueryResults(job.Reference))
        {
            Console.WriteLine($"{row["word"]}: {row["word_count"]}");
        }
    }
}

Avant d'essayer cet exemple, suivez les instructions de configuration pour C# du guide de démarrage rapide de BigQuery à l'aide de bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour C#.