背景・目的
こちらの記事を読んで、Athenaでパラメータ化されたクエリが強化されたということを知ったので、試してみたいと思います。
なお、自分は知りませんでしたが、パラメータ化されたクエリ自体は以前から利用可能で、今回は強化されたとのことでした。
今回のアップデートによりは、コンソールからパラメータ化されたクエリが利用できるようになったようです。
まとめ
- AthenaでもPrepared Statementが使える。
- マネコン、API、CLIから利用可能。
概要
パラメータ化されたクエリ
実行時に、異なるパラメータ値で同じクエリを再利用することで、再利用性、シンプル化、SQLインジェクション攻撃を防ぐことができます。
考慮事項と制限事項
- Athenaエンジンバージョン2のみ
- SELECT、INSERT INTO、CTAS、およびUNLOADのみサポート
- ステートメントは、ワークグループで一意である必要がある。
- 最大25個までのパラメータのみ指定可能
- パラメータに名前はつけることができず、順番により指定する。
- ステートメントに対するIAM権限が必要
実践
前提
以下のテーブルとデータを使用します。
テーブル定義
SHOW CREATE TABLE `sales`;
===
CREATE EXTERNAL TABLE `sales`(
`salesid` int,
`listid` int,
`sellerid` int,
`buyerid` int,
`eventid` int,
`dateid` smallint,
`qtysold` smallint,
`pricepaid` decimal(8,2),
`commission` decimal(8,2),
`saletime` timestamp)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://20220305-redshift-handson/tickit/spectrum/sales'
TBLPROPERTIES (
'numRows'='172000',
'transient_lastDdlTime'='1647694373')
データ
SELECT qtysold, count(1) FROM "tickit"."sales" group by qtysold order by 1
===
# qtysold _col1
1 1 67774
2 2 64831
3 3 12767
4 4 26423
5 5 212
6 6 343
7 7 46
8 8 60
実行パラメータを使用したクエリ
Prepared Statementを作成せずにプレースホルダーを指定したクエリ
1.以下のクエリを入力し、実行をクリックします。
select count(1) from tickit.sales where qtysold =?
2.パラメータ入力のペインをが表示されるので、パラメータを指定して実行をクリックします。
3.結果を確認します。表示されました。
4.パラメータを変えて実行してみます。
5.結果を確認します。クエリが再利用され結果が変わりました。
Prepared Statementを使用
- ステートメントに名前を指定して再利用します。
1.ステートメントに名前をつけてクエリを実行します。
PREPARE tickit_count FROM
select count(1) from tickit.sales where qtysold =?
すぐに完了しました。スキャンデータが表示されていないことからこの時点ではデータは読み込まれていません。（当然ですが）
2.Executeで実行します。
名前のつけたステートメントを実行します。
EXECUTE tickit_count USING 3
表示されました。
Prepared Statementを削除
1.ステートメントを削除します。
DEALLOCATE PREPARE tickit_count
2.再実行してみます。
EXECUTE tickit_count USING 3
WorkGroupsから削除済みなので、エラーになりました。想定どおりです。
考察
AthenaでPrepared Statementが使えることで、プログラムから安全に効率的に実行できるようになったと思います。
参考