この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
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は、シンプルながらとても強力で汎用性の高い新機能でした!お使いのワークロードにぜひ組み込んでみてください。