使用參數化查詢

您可以使用 Athena 參數化查詢，在執行時間使用不同的參數值重新執行相同的查詢，並協助防範 SQL 隱碼攻擊。在 Athena 中，參數化查詢可以採用任何 DML 查詢或 SQL 預備陳述式中的執行參數形式。

• 具有執行參數的查詢可以在一個步驟中完成，而且並非針對特定工作群組。對於您想要參數化的值，可以在任何 DML 查詢中放置問號。當您執行查詢時，依序宣告執行參數值。參數的宣告和參數值的指派可以在同一個查詢中完成，但是以解耦的方式進行。與預備陳述式不同，您可以在提交具有執行參數的查詢時選取工作群組。

• 預備陳述式需要兩個獨立的 SQL 陳述式：PREPARE 和 EXECUTE。首先，您在 PREPARE 陳述式中定義參數。接下來，執行一個為您定義的參數提供值的 EXECUTE 陳述式。預備陳述式針對特定工作群組；您無法在其所屬的工作群組情境之外執行這些陳述式。

考量與限制

• Athena 引擎版本 2 和更新版本才支援參數化查詢。如需有關 Athena 引擎版本的資訊，請參閱Athena 引擎版本控制。

• 目前，參數化查詢僅支援 SELECT、INSERT INTO、CTAS 以及 UNLOAD 陳述式。

• 在參數化查詢中，參數具有位置性，以 ? 表示。依參數在查詢中的順序指派參數值。不支援具名參數。

• 目前，? 參數只能放置在 WHERE 子句。不支援類似 SELECT ? FROM table 的語法。

• 問號參數不能放在雙引號或單引號中 (也就是說，'?' 和 "?" 不是有效的語法)。

• 若要將 SQL 執行參數視為字串，它們必須使用單引號而非雙引號。

• 如有必要，您可以在輸入參數化術語的值時使用 CAST 函數。例如，如果您有一個已在查詢中參數化的 date 類型資料欄，並且想要查詢日期 2014-07-05，則輸入 CAST('2014-07-05' AS DATE) 參數值將會傳回結果。

• 預備陳述式針對特定工作群組，預備陳述式的名稱在工作群組中必須是唯一的。

• 需要預備陳述式的 IAM 權限。如需詳細資訊，請參閱 允許存取預備陳述式。

• 在 Athena 主控台中具有執行參數的查詢限制為最多 25 個問號。

使用執行參數進行查詢

您可以在任何 DML 查詢中使用問號預留位置來建立參數化查詢，無需建立預備陳述式。若要執行這些查詢，您可以使用 Athena 主控台，或使用 AWS CLI 或 AWS 開發套件，並在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 again (再次執行)。

• 若要一次清除輸入的所有值，請選擇 Clear (清除)。

• 若要直接編輯查詢 (例如，若要新增或移除問號)，請先關閉 Enter parameters (輸入參數) 對話方塊。

• 若要儲存參數化查詢以供日後使用，請選擇 Save (儲存) 或 Save as (另存為)，然後為查詢命名。如需使用已儲存查詢的詳細資訊，請參閱 使用已儲存的查詢。

為了方便起見，只要您在查詢編輯器中使用相同的索引標籤，Enter parameters (輸入參數) 對話方塊就會記住您先前為查詢輸入的值。

使用執行參數執行查詢 AWS CLI

若要使用 AWS 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://DOC-EXAMPLE-BUCKET;/..."
--execution-parameters "1" "2"

使用預備陳述式查詢

您可以使用預備陳述式來重複執行具有不同查詢參數的相同查詢。預備陳述式包含參數預留位置，其值在執行時提供。

注意

工作群組中預備陳述式的數量上限為 1000。

SQL 陳述式

您可以使用 PREPARE、EXECUTE 和 DEALLOCATE PREPARE SQL 陳述式，在 Athena 主控台查詢編輯器中執行參數化查詢。

• 若要指定您通常會使用常值的參數，請使用 PREPARE 陳述式中的問號。

• 若要在執行查詢時以值取代參數，請使用 EXECUTE 陳述式中的 USING 子句。

• 若要從工作群組的預備陳述式中移除預備陳述式，請使用 DEALLOCATE PREPARE 陳述式。

下列各節提供有關各個陳述式的其他詳細資訊。

PREPARE

預備好要在稍後執行的陳述式。預備陳述式會以您指定的名稱儲存在目前的工作群組中。該陳述式可以包含可代替文字的參數，以在查詢執行時替換文字。要由值取代的參數以問號表示。

語法

PREPARE statement_name FROM statement

下表描述了這些參數。

參數	描述
statement_name	要預備之陳述式的名稱。該名稱在工作群組內必須是唯一的。
陳述式	SELECT、CTAS 或 INSERT 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://DOC-EXAMPLE-BUCKET/'
WITH (format='PARQUET')

EXECUTE

執行預備陳述式。參數的值在 USING 子句中指定。

語法

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

statement_name 是預備陳述式的名稱。value1 和 value2 是要為陳述式中的參數指定的值。

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_statement 執行現有的預備陳述式，則 Athena 會開啟 Enter parameters (輸入參數) 對話方塊，以便您輸入通常會進入 EXECUTE ... USING 陳述式 USING 子句的值。

若要使用 Enter parameters (輸入參數) 對話方塊執行預備陳述式

1. 在查詢編輯器中，使用語法 EXECUTE prepared_statement，而不是使用語法 EXECUTE prepared_statement USING value1, value2 ...。

2. 選擇執行。出現 Enter parameters (輸入參數) 對話方塊。

   

3. 在 Execution parameters (執行參數) 對話方塊中依序輸入值。由於看不到查詢的原始文字，因此您必須記住每個位置參數的意義，或有預備陳述式可供參考。

4. 選擇執行。

使用建立準備好的陳述式 AWS CLI

若要使用 AWS 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 和準備語法

使用 start-query-execution 命令。將 PREPARE 陳述式放置在 query-string 引數中，如以下範例所示：

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

使用執行準備好的陳述式 AWS CLI

若要使用執行已準備好的陳述式 AWS CLI，您可以使用下列其中一種方法來提供參數的值：

• 使用 execution-parameters 引數。

• 在 query-string 引數中使用 EXECUTE ... USING SQL 語法。

使用執行參數引數

在此方式中，您可以使用 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://DOC-EXAMPLE-BUCKET/..."
--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://DOC-EXAMPLE-BUCKET/..."}'

列出準備陳述式

若要列出特定工作群組準備好的陳述式，您可以使用 Athena list-prepared-statements AWS CLI 命令或 ListPreparedStatementsAthena API 動作。--work-group 參數是必要參數。

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

其他資源

請參閱 AWS 大數據部落格中的下列相關文章。

• Improve reusability and security using Amazon Athena parameterized queries (使用 Amazon Athena 參數化查詢改善可再用性和安全性)

• Use Amazon Athena parameterized queries to provide data as a service (使用 Amazon Athena 參數化查詢提供資料即服務)