【新機能】Amazon Athenaでパラメータ変更できるSQLを実行可能になりました！

Amazon Athenaで、SQLの再利用性・シンプル化・セキュリティの向上を強化するParameterized Queriesが新しく追加されました！

本記事で、概要と使ってみた様子をご紹介していきます。

Parameterized Queriesとは？

Parameterized Queriesとは、よく使用されるSQLをパラメータのみ変更して実行できる新機能です。これによって、Athena上で実行されている日々のワークロードを単純化できる他、SQLインジェクションに対する保護としても有効します。

Parameterized Queriesの実行には、事前にPrepared Statements（準備ステートメント）を作成する必要があります。Prepared Statementsには、パラメータ用のプレースホルダーが含まれ、PREPARE、EXECUTE、DEALLOCATE PREPAREの3種類のステートメントが用意されています。

• PREPARE
  • パラメータ用のプレースホルダーを組み込んだPrepared Statementsを定義する
  • プレースホルダーには?を使用
  • 複数パラメータも可能
• EXECUTE
  • Prepared Statementsにパラメータを組み込んで実行する
  • プレースホルダーをパラメータで置換するにはUSINGを使用
• DEALLOCATE PREPARE
  • Prepared Statementsを削除する

制限

• Prepared Statementsはワークグループ単位で保管され、Statementの名前はワークグループ内でユニークでなければならない
• Athena engine version 2のみの対応
• 実行には、Prepared Statement用のIAM権限が必要
• 2021年7月7日現在、SELECT INSERT INTO CTAS のみ対応

実際に、まずはPrepared Statementsから作成していきます！

環境準備

サンプルテーブルとして、cm-harutaデータベース配下に公式で用意されているcloudfront_logsのテーブルを作成します。

Getting Started - Amazon Athena

CREATE EXTERNAL TABLE IF NOT EXISTS `cm-haruta`.`cloudfront_logs` (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-ap-northeast-1/cloudfront/plaintext/';

また、SQLを実行するワークグループはAthena engine version 2を指定しています。

実際に使ってみた！

まずはSELECT文でPrepared Statementsを作成してみます。

PREPARE test1 FROM
SELECT * FROM "cm-haruta"."cloudfront_logs"
WHERE status = ?
LIMIT 10

特に特殊なレスポンスはありませんでした。ちなみにですが、既に存在する名前でPrepared Statementsを作成すると、エラーやログもなく上書きされます。ここは要注意ですね。

作成したPrepared Statementsでパラメータを付与して実行してみます。

EXECUTE test1 USING 200

status = 200のレコードが無事出力されました。これは超便利！DEALLOCATE PREPAREで削除してみます。

DEALLOCATE PREPARE test1

こちらも特に特殊なレスポンスはありませんでした。消去したPrepared Statementsを実行しようとすると、当然ですがエラーが返されます。

PreparedStatement test1 was not found in workGroup cm-haruta

気になったのが、保管されているPrepared Statementsをどこから一覧で確認できるのかという点です。今のところAthenaのマネジメント・コンソールからはそれらしきタブやリンクが見つかりません。

ということでAWS CLIを見てみましたが、SDK側では実装済みでした！

• create-prepared-statement — AWS CLI 1.19.105 Command Reference
• delete-prepared-statement — AWS CLI 1.19.105 Command Reference
• get-prepared-statement — AWS CLI 1.19.105 Command Reference
• list-prepared-statements — AWS CLI 1.19.105 Command Reference
• update-prepared-statement — AWS CLI 1.19.105 Command Reference

SDKの方は一通りの機能が揃っているので、SQLよりかはSDK側で制御を行った方が安心ですね。

試しに適当にPrepared Statementsを追加して、list-prepared-statementsとget-prepared-statementを実行してみます。

$ aws athena list-prepared-statements --work-group cm-haruta
{
    "PreparedStatements": [
        {
            "StatementName": "test2",
            "LastModifiedTime": "2021-07-07T10:59:00.542000+09:00"
        },
        {
            "StatementName": "test1",
            "LastModifiedTime": "2021-07-07T10:58:19.893000+09:00"
        }
    ]
}

listではステートメント名と更新日が帰ってきます。test1の情報を取得してみます。

$ aws athena get-prepared-statement --statement-name test1 --work-group cm-haruta
{
    "PreparedStatement": {
        "StatementName": "test1",
        "QueryStatement": "SELECT *\nFROM\n  \"cm-haruta\".cloudfront_logs\nWHERE (status = ?)\nLIMIT 10\n",
        "WorkGroupName": "cm-haruta",
        "Description": "Created through SQL command.",
        "LastModifiedTime": "2021-07-07T10:58:19.893000+09:00"
    }
}

getではSQLの中身も返ってきますね。Descriptionとあるので、SDKで作成するとPrepared Statementsの概要も付与できそうです。

最後に、すでに存在しているステートメント名でcreate-prepared-statementを実行すると、ちゃんとエラーが出るのかどうか検証してみます。

$ aws athena create-prepared-statement --statement-name test1 --work-group cm-haruta --query-statement 'SELECT 1'
An error occurred (InvalidRequestException) when calling the CreatePreparedStatement operation: Prepared Statement test1 already exists in WorkGroup cm-haruta 

エラーになりました！こういった点からも、SQLのPREPAREステートメントは使わずに、SDKで作成・更新・削除を行った方が安全かつ管理しやすいですね。

所感

Parameterized Queriesは、シンプルながらとても強力で汎用性の高い新機能でした！お使いのワークロードにぜひ組み込んでみてください。