Considerations and limitations Querying using execution parameters Querying with prepared statements See also

Using parameterized queries

You can use Athena parameterized queries to re-run the same query with different parameter values at execution time and help prevent SQL injection attacks. In Athena, parameterized queries can take the form of execution parameters in any DML query or SQL prepared statements.

Queries with execution parameters can be done in a single step and are not workgroup specific. You place question marks in any DML query for the values that you want to parameterize. When you run the query, you declare the execution parameter values sequentially. The declaration of parameters and the assigning of values for the parameters can be done in the same query, but in a decoupled fashion. Unlike prepared statements, you can select the workgroup when you submit a query with execution parameters.
Prepared statements require two separate SQL statements: PREPARE and EXECUTE. First, you define the parameters in the PREPARE statement. Then, you run an EXECUTE statement that supplies the values for the parameters that you defined. Prepared statements are workgroup specific; you cannot run them outside the context of the workgroup to which they belong.

Considerations and limitations

Parameterized queries are supported in Athena engine version 2 and later versions. For information about Athena engine versions, see Athena engine versioning.
Currently, parameterized queries are supported only for SELECT, INSERT INTO, CTAS, and UNLOAD statements.
In parameterized queries, parameters are positional and are denoted by ?. Parameters are assigned values by their order in the query. Named parameters are not supported.
Currently, ? parameters can be placed only in the WHERE clause. Syntax like SELECT ? FROM table is not supported.
Question mark parameters cannot be placed in double or single quotes (that is, '?' and "?" are not valid syntax).
For SQL execution parameters to be treated as strings, they must be enclosed in single quotes rather than double quotes.
If necessary, you can use the CAST function when you enter a value for a parameterized term. For example, if you have a column of the date type that you have parameterized in a query and you want to query for the date 2014-07-05, entering CAST('2014-07-05' AS DATE) for the parameter value will return the result.
Prepared statements are workgroup specific, and prepared statement names must be unique within the workgroup.
IAM permissions for prepared statements are required. For more information, see Allow access to prepared statements.
Queries with execution parameters in the Athena console are limited to a maximum of 25 question marks.

Querying using execution parameters

You can use question mark placeholders in any DML query to create a parameterized query without creating a prepared statement first. To run these queries, you can use the Athena console, or use the Amazon CLI or the AWS SDK and declare the variables in the execution-parameters argument.

Running queries with execution parameters in the Athena console

When you run a parameterized query that has execution parameters (question marks) in the Athena console, you are prompted for the values in the order in which the question marks occur in the query.

To run a query that has execution parameters

Enter a query with question mark placeholders in the Athena editor, as in the following example.
```
SELECT * FROM "my_database"."my_table"
WHERE year = ? and month= ? and day= ?
```
Choose Run.
In the Enter parameters dialog box, enter a value in order for each of the question marks in the query.
When you are finished entering the parameters, choose Run. The editor shows the query results for the parameter values that you entered.

At this point, you can do one of the following:

Enter different parameter values for the same query, and then choose Run again.
To clear all of the values that you entered at once, choose Clear.
To edit the query directly (for example, to add or remove question marks), close the Enter parameters dialog box first.
To save the parameterized query for later use, choose Save or Save as, and then give the query a name. For more information about using saved queries, see Using saved queries.

As a convenience, the Enter parameters dialog box remembers the values that you entered previously for the query as long as you use the same tab in the query editor.

Running queries with execution parameters using the Amazon CLI

To use the Amazon CLI to run queries with execution parameters, use the start-query-execution command and provide a parameterized query in the query-string argument. Then, in the execution-parameters argument, provide the values for the execution parameters. The following example illustrates this 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"

Querying with prepared statements

You can use a prepared statement for repeated execution of the same query with different query parameters. A prepared statement contains parameter placeholders whose values are supplied at execution time.

Note

The maximum number of prepared statements in a workgroup is 1000.

SQL statements

You can use the PREPARE, EXECUTE and DEALLOCATE PREPARE SQL statements to run parameterized queries in the Athena console query editor.

To specify parameters where you would normally use literal values, use question marks in the PREPARE statement.
To replace the parameters with values when you run the query, use the USING clause in the EXECUTE statement.
To remove a prepared statement from the prepared statements in a workgroup, use the DEALLOCATE PREPARE statement.

The following sections provide additional detail about each of these statements.

PREPARE

Prepares a statement to be run at a later time. Prepared statements are saved in the current workgroup with the name that you specify. The statement can include parameters in place of literals to be replaced when the query is run. Parameters to be replaced by values are denoted by question marks.

Syntax


PREPARE statement_name FROM statement

The following table describes these parameters.

Parameter	Description
`statement_name`	The name of the statement to be prepared. The name must be unique within the workgroup.
`statement`	A `SELECT`, `CTAS`, or `INSERT INTO` query.

PREPARE examples

The following examples show the use of the PREPARE statement. Question marks denote the values to be supplied by the EXECUTE statement when the query is run.


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

Runs a prepared statement. Values for parameters are specified in the USING clause.

Syntax


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

statement_name is the name of the prepared statement. value1 and value2 are the values to be specified for the parameters in the statement.

EXECUTE examples

The following example runs the my_select1 prepared statement, which contains no parameters.


EXECUTE my_select1

The following example runs the my_select2 prepared statement, which contains a single parameter.


EXECUTE my_select2 USING 2012

The following example runs the my_select3 prepared statement, which has two parameters.


EXECUTE my_select3 USING 346078, 12

The following example supplies a string value for a parameter in the prepared statement my_insert.


EXECUTE my_insert USING 'usa'

The following example supplies a numerical value for the productid parameter in the prepared statement my_unload.


EXECUTE my_unload USING 12

DEALLOCATE PREPARE

Removes the prepared statement with the specified name from the list of prepared statements in the current workgroup.

Syntax


DEALLOCATE PREPARE statement_name

statement_name is the name of the prepared statement to be removed.

Example

The following example removes the my_select1 prepared statement from the current workgroup.


DEALLOCATE PREPARE my_select1

Executing prepared statements without the USING clause in the Athena console

If you run an existing prepared statement with the syntax EXECUTE prepared_statement in the query editor, Athena opens the Enter parameters dialog box so that you can enter the values that would normally go in the USING clause of the EXECUTE ... USING statement.

To run a prepared statement using the Enter parameters dialog box

In the query editor, instead of using the syntax EXECUTE prepared_statement USING value1, value2 ..., use the syntax EXECUTE prepared_statement.
Choose Run. The Enter parameters dialog box appears.
Enter the values in order in the Execution parameters dialog box. Because the original text of the query is not visible, you must remember the meaning of each positional parameter or have the prepared statement available for reference.
Choose Run.

Creating prepared statements using the Amazon CLI

To use the Amazon CLI to create a prepared statement, you can use one of the following athena commands:

Use the create-prepared-statement command and provide a query statement that has execution parameters.
Use the start-query-execution command and provide a query string that uses the PREPARE syntax.

Using create-prepared-statement

In a create-prepared-statement command, define the query text in the query-statement argument, as in the following example.


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

Using start-query-execution and the PREPARE syntax

Use the start-query-execution command. Put the PREPARE statement in the query-string argument, as in the following example:


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

Executing prepared statements using the Amazon CLI

To execute a prepared statement with the Amazon CLI, you can supply values for the parameters by using one of the following methods:

Use the execution-parameters argument.
Use the EXECUTE ... USING SQL syntax in the query-string argument.

Using the execution-parameters argument

In this approach, you use the start-query-execution command and provide the name of an existing prepared statement in the query-string argument. Then, in the execution-parameters argument, you provide the values for the execution parameters. The following example shows this method.


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

Using the EXECUTE ... USING SQL syntax

To run an existing prepared statement using the EXECUTE ... USING syntax, you use the start-query-execution command and place the both the name of the prepared statement and the parameter values in the query-string argument, as in the following example:


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

Listing prepared statements

To list the prepared statements for a specific workgroup, you can use the Athena list-prepared-statements Amazon CLI command or the ListPreparedStatements Athena API action. The --work-group parameter is required.


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