使用参数化查询 - Amazon Athena
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

本文属于机器翻译版本。若本译文内容与英语原文存在差异,则一律以英文原文为准。

使用参数化查询

您可以使用 Athena 参数化查询在执行时使用不同的参数值重新运行同一个查询,从而帮助防止 SQL 注入攻击。在 Athena 中,参数化查询可以在任何 DML 查询或 SQL 预准备语句中采用执行参数的形式。

  • 带有执行参数的查询可以在单个步骤中完成,而不是特定于工作组。您可以在任何 DML 查询中为要参数化的值放置问号。运行查询时,按顺序声明执行参数的值。参数的声明和参数的赋值可以在同一查询中完成,但单独进行。与预准备语句不同,您可以在提交带有执行参数的查询时选择工作组。

  • 预准备语句需要两个单独的 SQL 语句:PREPAREEXECUTE。首先,在 PREPARE 语句中定义参数。然后,运行为您定义的参数提供值的 EXECUTE 语句。预准备语句特定于工作组;您不能在预准备语句所属的工作组的上下文以外运行它们。

注意事项和限制

  • 参数化查询仅在 Athena 引擎版本 2 及更高版本中受支持。有关 Athena 引擎版本的更多信息,请参阅 Athena 引擎版本控制

  • 目前,参数化查询仅在 SELECTINSERT INTOCTASUNLOAD 语句中受支持。

  • 在参数化查询中,参数是位置参数,并用 ? 指示。参数按其在查询中的顺序赋值。命名参数不受支持。

  • 目前,? 参数只能放在 WHERE 子句中。不支持 SELECT ? FROM table 之类的语法。

  • 问号参数不能放在双引号或单引号以内(即 '?'"?" 不是有效的语法)。

  • 要将 SQL 执行参数视为字符串,它们必须用单引号而不是双引号括起来。

  • 如有必要,您可以在为参数化术语输入值时使用 CAST 函数。例如,如果您有一列 date 类型在查询中参数化,并且想要查询日期 2014-07-05,则为参数值输入 CAST('2014-07-05' AS DATE) 将返回结果。

  • 预准备语句特定于工作组,预准备语句名称在工作组中必须唯一。

  • 预准备语句的 IAM 权限是必需的。有关更多信息,请参阅允许访问预编译语句

  • 在 Athena 控制台中使用执行参数的查询最多只能有 25 个问号。

使用执行参数查询

您可以在任何 DML 查询中使用问号占位符创建参数化查询,而无需先创建预准备语句。要运行这些查询,您可以使用 Athena 控制台,或者使用 Amazon CLI 或 AWS SDK,并在 execution-parameters 参数中声明变量。

在 Athena 控制台中运行具有执行参数的查询

在 Athena 控制台中运行具有执行参数(问号)的参数化查询时,系统会按照问号在查询中出现的顺序提示您输入值。

要运行具有执行参数的查询
  1. 请在 Athena 编辑器中输入带有问号占位符的查询,如以下示例所示。

    SELECT * FROM "my_database"."my_table" WHERE year = ? and month= ? and day= ?
  2. 选择 运行

  3. Enter parameters(输入参数)对话框中,按查询中每个问号的顺序输入值。

    
                            按顺序输入查询参数的值
  4. 输入完参数后,选择 Run(运行)。编辑器会显示您输入的参数值的查询结果。

在这种情况下,您可以执行下列操作之一:

  • 为同一个查询输入不同的参数值,然后再次选择 Run(运行)

  • 要立即清除输入的所有值,请选择 Clear(清除)

  • 要直接编辑查询(例如,添加或删除问号),请关闭 Enter parameters(输入参数)对话框。

  • 要保存参数化查询以供后续使用,请选择 Save(保存)或者 Save as(另存为),然后为查询命名。有关使用已保存查询的更多信息,请参阅 使用已保存的查询

为方便起见,Enter parameters(输入参数)对话框会记住先前为查询输入的值,只要您在查询编辑器中使用同一选项卡。

使用 Amazon CLI 运行具有执行参数的查询

要使用 Amazon CLI 运行具有执行参数的查询,请使用 start-query-execution 命令并在 query-string 参数中提供参数化查询。然后,在 execution-parameters 参数中,提供执行参数的值。以下示例对此方法进行了说明。

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"

使用预准备语句进行查询

您可以使用预编译语句重复执行具有不同查询参数的同一查询。预准备语句包含参数占位符,其值在执行时提供。

注意

工作组中的最大预处理语句数为 1000。

SQL 语句

您可以使用 PREPAREEXECUTEDEALLOCATE PREPARE SQL 语句在 Athena 控制台查询编辑器中运行参数化查询。

  • 要指定通常会使用文字值的参数,请在 PREPARE 语句中使用问号。

  • 若要在运行查询时将参数替换为值,请在 EXECUTE 语句中使用 USING 子句。

  • 要从工作组中的预准备语句中删除预准备语句,请使用 DEALLOCATE PREPARE 语句。

以下各部分提供了有关这些语句的其他详细信息。

PREPARE

准备要在之后运行的语句。预准备语句将以您指定的名称保存在当前工作组中。语句可以包含参数来代替要在查询运行时替换的文字。要替换为值的参数用问号表示。

语法
PREPARE statement_name FROM statement

下表介绍了这些参数。

参数 描述
statement_name 要执行的预准备语句的名称。此名称在工作组范围内必须唯一。
语句 SELECTCTASINSERT INTO 查询。
PREPARE 示例

以下示例将演示如何使用 PREPARE 语句。问号表示在运行查询时由 EXECUTE 语句提供的值。

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

运行预准备语句。参数的值在 USING 子句中指定。

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

statement_name 是预准备语句的名称。value1value2 是要为语句中的参数指定的值。

EXECUTE 示例

以下示例将运行 my_select1 预准备语句,该语句不包含任何参数。

EXECUTE my_select1

以下示例将运行 my_select2 预准备语句,其中包含一个参数。

EXECUTE my_select2 USING 2012

以下示例将运行 my_select3 预准备语句,它使用两个参数。

EXECUTE my_select3 USING 346078, 12

以下示例为预准备语句 my_insert 中的参数提供字符串值。

EXECUTE my_insert USING 'usa'

以下示例为预准备语句 my_unload 中的 productid 参数提供数字值。

EXECUTE my_unload USING 12

DEALLOCATE PREPARE

从当前工作组中的预准备语句列表中删除具有指定名称的预准备语句。

语法
DEALLOCATE PREPARE statement_name

statement_name 是要删除的预准备语句的名称。

示例

以下示例从当前工作组中删除 my_select1 预准备语句。

DEALLOCATE PREPARE my_select1

在 Athena 控制台中执行不带 USING 子句的预准备语句

如果在查询编辑器中使用语法 EXECUTE prepared_stament 运行现有预准备语句,Athena 会打开 Enter parameters(输入参数)对话框,这样您就可以输入通常出现在 EXECUTE ... USING 语句的 USING 子句中的值。

使用 Enter parameters(输入参数)对话框运行预准备语句
  1. 在查询编辑器中,使用语法 EXECUTE prepared_stament 而不是 EXECUTE prepared_statement USING Value1 , Value2 ...

  2. 选择 运行。将显示 Enter parameters(输入参数)对话框。

    
                            在 Athena 控制台中输入预准备语句的参数值。
  3. 按照 Execution parameters(执行参数)对话框中的顺序输入值。由于查询的原始文本不可见,因此必须记住每个位置参数的含义或使预准备语句可供参考。

  4. 选择 运行

使用 Amazon CLI 创建预准备语句

要使用 Amazon CLI 创建预准备语句,您可以使用以下 athena 命令中的一个:

  • 使用 create-prepared-statement 命令并提供包含执行参数的查询语句。

  • 使用 start-query-execution 命令并提供使用 PREPARE 语法的查询字符串。

使用 create-prepared-statement

create-prepared-statement 命令中,定义 query-statement 参数中的查询文本,如以下示例所示。

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

使用 start-query-execution 和 PREPARE 语法

使用 start-query-execution 命令。在 query-string 参数中放置 PREPARE 语句,如以下示例所示:

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

使用 Amazon CLI 执行预准备语句

要使用 Amazon CLI 执行预准备语句,您可以使用以下方法之一为参数提供值:

  • 使用 execution-parameters 参数。

  • 使用 query-string 参数中的 EXECUTE ... USING SQL 语法。

使用 execution-parameters 参数

在此方法中,您可以使用 start-query-execution 命令并提供 query-string 参数现有预准备语句的名称。然后,在 execution-parameters 参数中,提供执行参数的值。以下示例说明了这一方法:

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

使用 EXECUTE... 使用 SQL 语法

要使用 EXECUTE ... USING 语法运行现有的预准备语句,您可以使用 start-query-execution 命令并将预准备语句的名称和参数值都放在 query-string 参数中,如以下示例所示:

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

列出预准备语句列表

要列出特定工作组的预准备语句,可以使用 Athena list-prepared-statements Amazon CLI 命令或 ListPreparedStatements Athena API 操作。--work-group 参数是必需的。

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

另请参阅

请参阅 Amazon 大数据博客中的以下相关文章。