Utilisation de requêtes paramétrées

Vous pouvez utiliser des requêtes paramétrées Athena pour réexécuter la même requête avec des valeurs de paramètres différentes au moment de l'exécution et aider à prévenir les attaques par injection SQL. Dans Athena, les requêtes paramétrées peuvent prendre la forme de paramètres d'exécution dans n'importe quelle requête DML ou instructions préparées SQL.

• Les requêtes avec des paramètres d'exécution peuvent être effectuées en une seule étape et ne sont pas spécifiques à un groupe de travail. Vous placez des points d'interrogation dans n'importe quelle requête DML pour les valeurs que vous souhaitez paramétrer. Lorsque vous exécutez la requête, vous déclarez les valeurs des paramètres d'exécution de manière séquentielle. La déclaration des paramètres et l'attribution de valeurs pour les paramètres peuvent être effectuées dans la même requête, mais de manière découplée. Contrairement aux instructions préparées, vous pouvez sélectionner le groupe de travail lorsque vous soumettez une requête avec des paramètres d'exécution.

• Les instructions préparées nécessitent deux instructions SQL distinctes :PREPARE et EXECUTE. Tout d'abord, vous définissez les paramètres dans l'instruction PREPARE. Ensuite, vous lancez une instruction EXECUTE qui fournit les valeurs des paramètres que vous avez définis. Les instructions préparées sont spécifiques au groupe de travail ; vous ne pouvez pas les exécuter en dehors du contexte du groupe de travail auquel elles appartiennent.

Considérations et restrictions

• Les requêtes paramétrées sont prises en charge dans la version 2 du moteur Athena et les versions ultérieures. Pour plus d'informations sur les versions du moteur Athena, voir Gestion des versions du moteur Athena.

• Actuellement, les requêtes paramétrées ne sont prises en charge que pour les instructions SELECT, INSERT INTO, CTAS et UNLOAD.

• Dans les requêtes paramétrées, les paramètres sont positionnels et sont désignés par ?. Les paramètres se voient attribuer des valeurs en fonction de leur ordre dans la requête. Les paramètres nommés ne sont pas pris en charge.

• Actuellement, les paramètres ? ne peuvent être placés que dans la clause WHERE. Les syntaxes comme SELECT ? FROM table ne sont pas prises en charge.

• Les paramètres de point d'interrogation ne peuvent pas être placés entre guillemets doubles ou simples (c'est-à-dire, '?' et "?" ne sont pas des syntaxes valides).

• Pour que les paramètres d'exécution SQL soient traités comme des chaînes, ils doivent être placés entre guillemets simples plutôt que entre guillemets doubles.

• Si nécessaire, vous pouvez utiliser la fonction CAST lorsque vous entrez la valeur d'un terme paramétré. Par exemple, si vous avez une colonne du type date que vous avez paramétré dans une requête et que vous souhaitez rechercher la date 2014-07-05, la saisie de CAST('2014-07-05' AS DATE) pour la valeur du paramètre renverra le résultat.

• Les instructions préparées sont spécifiques au groupe de travail, et les noms des instructions préparées doivent être uniques au sein du groupe de travail.

• Des autorisations IAM pour les instructions préparées sont requises. Pour de plus amples informations, veuillez consulter Autorisation d'accès aux instructions préparées.

• Les requêtes avec des paramètres d'exécution dans la console Athena sont limitées à un maximum de 25 points d'interrogation.

Interrogation utilisant les paramètres d'exécution

Vous pouvez utiliser des espaces réservés de point d'interrogation dans n'importe quelle requête DML pour créer une requête paramétrée sans créer d'instruction préparée au préalable. Pour exécuter ces requêtes, vous pouvez utiliser la console Athena ou utiliser AWS CLI ou le kit SDK AWS et déclarez les variables dans l’argument execution-parameters.

Exécution de requêtes avec paramètres d'exécution dans la console Athena

Lorsque vous exécutez une requête paramétrée qui possède des paramètres d'exécution (points d'interrogation) dans la console Athena, vous êtes invité à saisir les valeurs dans l'ordre dans lequel les points d'interrogation apparaissent dans la requête.

Pour exécuter une requête contenant des paramètres d'exécution

1. Saisissez une requête avec des espaces réservés de point d'interrogation dans l'éditeur Athena, comme dans l'exemple suivant.

   SELECT * FROM "my_database"."my_table"
   WHERE year = ? and month= ? and day= ?

2. Cliquez sur Run (Exécuter).

3. Dans Enter parameters (Saisir des paramètres), saisissez une valeur dans l'ordre pour chacun des points d'interrogation de la requête.

   

4. Lorsque vous avez fini de saisir les paramètres, choisissez Run (Exécuter). L'éditeur affiche les résultats de la requête pour les valeurs de paramètres que vous avez saisies.

À ce stade, vous pouvez réaliser l'une des actions suivantes :

• Saisissez différentes valeurs de paramètres pour la même requête, puis choisissez Run again (Exécutez à nouveau).

• Pour effacer toutes les valeurs que vous avez saisies en même temps, choisissez Clear (Effacer).

• Pour modifier directement la requête (par exemple, pour ajouter ou supprimer des points d'interrogation), fermez d’abord la boîte de dialogue Enter parameters (Saisir des paramètres).

• Pour enregistrer la requête paramétrée pour une utilisation ultérieure, choisissez Save (Enregistrer) ou Save as (Enregistrer sous), puis donnez un nom à la requête. Pour plus d'informations sur l'utilisation de requêtes enregistrées, consultez Utilisation de requêtes enregistrées.

Pour plus de commodité, la boîte de dialogue Enter parameter (Saisir des paramètres) mémorise les valeurs que vous avez saisies précédemment pour la requête tant que vous utilisez le même onglet dans l'éditeur de requête.

Exécution de requêtes avec paramètres d'exécution utilisant AWS CLI

Pour utiliser AWS CLI pour exécuter des requêtes avec des paramètres d'exécution, utilisez la commande start-query-execution et fournissez une requête paramétrée dans l’argument query-string. Ensuite, dans l’argument execution-parameters, fournissez les valeurs des paramètres d'exécution. L'exemple suivant illustre cette technique.

aws athena start-query-execution 
--query-string "SELECT * FROM table WHERE x = ? AND y = ?"
--query-execution-context "Database"="default" 
--result-configuration "OutputLocation"="s3://..."
--execution-parameters "1" "2"

Interrogation avec des instructions préparées

Vous pouvez utiliser une instruction préparée pour l'exécution répétée d'une même requête avec des paramètres de requête différents. Une instruction préparée contient des paramètres dont les valeurs sont fournies au moment de l'exécution.

Note

Le nombre maximal d'instructions préparées dans un groupe de travail est de 1 000.

Instructions SQL

Vous pouvez utiliser les instructions SQL PREPARE, EXECUTE et DEALLOCATE PREPARE pour exécuter des requêtes paramétrées dans l'éditeur de requête de la console Athena.

• Pour spécifier des paramètres là où vous utiliseriez normalement des valeurs littérales, utilisez des points d'interrogation dans l'instruction PREPARE.

• Pour remplacer les paramètres par des valeurs lorsque vous exécutez la requête, utilisez la clause USING dans l'instruction EXECUTE.

• Pour supprimer une instruction préparée des instructions préparées dans un groupe de travail, utilisez l'instruction DEALLOCATE PREPARE.

Les sections suivantes fournissent des détails supplémentaires sur chacune de ces instructions.

PREPARE

Prépare une instruction à exécuter ultérieurement. Les instructions préparées sont enregistrées dans le groupe de travail actif avec le nom que vous spécifiez. L'instruction peut inclure des paramètres à la place des libellés qui seront remplacés lors de l'exécution de la requête. Les paramètres à remplacer par des valeurs sont signalés par des points d'interrogation.

Syntaxe

PREPARE statement_name FROM statement

Le tableau suivant décrit ces paramètres.

Paramètre	Description
statement_name	Nom de l'instruction à préparer. Le nom doit être unique au sein du groupe de travail.
déclaration	Une requête SELECT, CTAS ou INSERT INTO.

Exemple PREPARE

Les exemples suivants montrent l'utilisation de l'instruction PREPARE. Les points d'interrogation indiquent les valeurs à fournir par l'instruction EXECUTE lors de l'exécution de la requête.

PREPARE my_select1 FROM
SELECT * FROM nation

PREPARE my_select2 FROM
SELECT * FROM "my_database"."my_table" WHERE year = ?

PREPARE my_select3 FROM
SELECT order FROM orders WHERE productid = ? and quantity < ?

PREPARE my_insert FROM
INSERT INTO cities_usa (city, state)
SELECT city, state
FROM cities_world
WHERE country = ?

PREPARE my_unload FROM
UNLOAD (SELECT * FROM table1 WHERE productid < ?)
TO 's3://my_output_bucket/'
WITH (format='PARQUET')

EXECUTE

Exécute une instruction préparée. Les valeurs des paramètres sont spécifiées dans la clause USING.

Syntaxe

EXECUTE statement_name [USING value1 [ ,value2, ... ] ]

statement_name est le nom de l'instruction préparée. value1 et value2 sont les valeurs à spécifier pour les paramètres de l'instruction.

Exemples EXECUTE

L'exemple suivant exécute l'instruction préparée my_select1, qui ne contient aucun paramètre.

EXECUTE my_select1

L'exemple suivant exécute l'instruction préparée my_select2, qui contient un seul paramètre.

EXECUTE my_select2 USING 2012

L'exemple suivant exécute l'instruction préparée my_select3, qui contient deux paramètres.

EXECUTE my_select3 USING 346078, 12

L'exemple suivant fournit une valeur de chaîne pour un paramètre dans l'instruction préparée my_insert.

EXECUTE my_insert USING 'usa'

L'exemple suivant fournit une valeur numérique pour le paramètre productid dans l'instruction préparée my_unload.

EXECUTE my_unload USING 12

DEALLOCATE PREPARE

Supprime l'instruction préparée portant le nom spécifié de la liste des instructions préparées dans le groupe de travail actuel.

Syntaxe

DEALLOCATE PREPARE statement_name

statement_name est le nom de l'instruction préparée à supprimer.

Exemple

L'exemple suivant supprime l'instruction préparée my_select1 du groupe de travail actuel.

DEALLOCATE PREPARE my_select1

Exécution d'instructions préparées sans la clause USING dans la console Athena

Si vous exécutez une instruction préparée existante avec la syntaxe EXECUTE prepared_statement dans l'éditeur de requête, Athena ouvre la boîte de dialogue Enter parameters (Saisir des paramètres) pour que vous puissiez saisir les valeurs qui devraient normalement figurer dans la clause USING de l’instruction EXECUTE ... USING.

Pour exécuter une instruction préparée à l'aide de la boîte de dialogue Enter parameters (Saisir des paramètres)

1. Dans l'éditeur de requête, au lieu d'utiliser la syntaxe EXECUTE prepared_statement USING Value1, Value2 ..., utilisez plutôt la syntaxe EXECUTE prepared_statement.

2. Cliquez sur Exécuter. La boîte de dialogue Enter parameters (Saisir des paramètres)s'affiche.

   

3. Saisissez les valeurs dans l'ordre dans la boîte de dialogue Execution parameters (Paramètres d'exécution). Comme le texte d'origine de la requête n'est pas visible, vous devez vous souvenir de la signification de chaque paramètre positionnel ou disposer de l'instruction préparée pour référence.

4. Cliquez sur Exécuter.

Création d'instructions préparées à l'aide de AWS CLI

Pour utiliser AWS CLI pour créer une instruction préparée, vous pouvez utiliser l'une des commandes athena suivantes :

• Utilisez la commande create-prepared-statement et fournissez une instruction de requête qui a des paramètres d'exécution.

• Utilisez la commande start-query-execution et fournissez une chaîne de requête qui utilise la syntaxe PREPARE.

Utilisation de create-prepared-statement

Dans une commande create-prepared-statement, définissez le texte de la requête dans l'argument query-statement, comme dans l'exemple suivant.

aws athena create-prepared-statement 
--statement-name PreparedStatement1 
--query-statement "SELECT * FROM table WHERE x = ?" 
--work-group athena-engine-v2

Utilisation de start-query-execution et de la syntaxe PREPARE

Utilisez la commande start-query-execution. Placez l’instruction PREPARE dans l'argument query-string, comme dans l'exemple suivant :

aws athena start-query-execution 
--query-string "PREPARE PreparedStatement1 FROM SELECT * FROM table WHERE x = ?" 
--query-execution-context '{"Database": "default"}' 
--result-configuration '{"OutputLocation": "s3://..."}'

Exécution d'instructions préparées à l'aide de AWS CLI

Pour exécuter une instruction préparée avec AWS CLI, vous pouvez fournir des valeurs pour les paramètres à l'aide de l'une des méthodes suivantes :

• Utilisez l'argument execution-parameters.

• Utilisez la syntaxe SQL EXECUTE ... USING dans l’argument query-string.

Utilisation de l'argument execution-parameters

Dans cette approche, vous utilisez la commande start-query-execution et saisissez le nom d'une instruction préparée existante dans l’argument query-string. Ensuite, dans l’argument execution-parameters, vous fournissez les valeurs des paramètres d'exécution. L'exemple suivant montre cette methode.

aws athena start-query-execution 
--query-string "Execute PreparedStatement1" 
--query-execution-context "Database"="default" 
--result-configuration "OutputLocation"="s3://..."
--execution-parameters "1" "2"

Utilisation de EXECUTE ... Utilisation de la syntaxe SQL

Pour exécuter une instruction préparée existante à l'aide de la syntaxe EXECUTE ... USING, vous utilisez la commande start-query-execution et placez le nom de l'instruction préparée et les valeurs des paramètres dans l’argument query-string, comme dans l'exemple suivant :

aws athena start-query-execution 
--query-string "EXECUTE PreparedStatement1 USING 1"
--query-execution-context '{"Database": "default"}' 
--result-configuration '{"OutputLocation": "s3://..."}'

Listage d'instructions préparées

Pour répertorier les instructions préparées pour un groupe de travail spécifique, vous pouvez utiliser la commande Athena AWS CLI list-prepared-statements ou l'action d'API Athena ListPreparedStatements. Le paramètre --work-group est obligatoire.

aws athena list-prepared-statements --work-group primary

Consulter aussi

Consultez les articles connexes suivants dans le Blog Big Data AWS.

• Améliorer la réutilisabilité et la sécurité grâce aux requêtes paramétrées Amazon Athena

• Utiliser des requêtes paramétrées Amazon Athena pour fournir des données en tant que service