Amazon Athena が待望のCTAS（CREATE TABLE AS）をサポートしました！
はじめに
待望のアップデート、Amazon Athena がCTAS（CREATE TABLE AS）をサポートしました！これまでは、SELECTクエリ（いわゆる参照系クエリ）のみでしたが、CTASによる書き込みクエリがサポートされました。更新系クエリのご要望は多く、かなり大きなアップデートなのでご紹介したいと思います。
過去には、こんなブログも書きましたが、今後はこんなことは不要です。
CTAS（CREATE TABLE AS）とは
CTAS（CREATE TABLE AS）とは、SELECTクエリの実行結果に基づいて、新しいテーブルとそのデータファイルをS3に作成する機能です。テーブルの列には、クエリの出力列に関連付けられた名前とデータ型が指定されます。S3に出力されるデータファイルはフォーマット指定可能で、デフォルトはカラムナファイルフォーマットである Apache Parquetのフォーマットで出力されます。
構文
指定した名前とパラメータでビューを作成します。
CREATE TABLE table_name [ WITH ( property_name = expression [, ...] ) ] AS query [ WITH [ NO ] DATA ]
CTAS（CREATE TABLE AS）の例
基本的には、一般的な構文で可能ですが、S3ファイルの出力先やファイルフォーマット、パーティションの有無に応じて変更可能です。
CREATE TABLE test AS SELECT orderkey, orderstatus, totalprice / 2 AS half FROM orders
執筆時点では、日本語マニュアルは公開されていませんので、詳細は英語マニュアルCREATE TABLE ASを参照してください。
では、実際にやってみましょう。
CTASでテーブルを作成する
今回は、sampledbのelb_logsテーブルからCTASでテーブルを作成します。最初はelb_logsテーブルに対する通常のSELECTクエリを実行します。[CREATE]ボタンを押して新たに追加されたCreate table from queryを選択します。
すると、以下のダイアログが表示されます。
必須項目は出力先のテーブル名Table nameのみでです。今回は、以下のようにそれぞれ設定しました。
Output locationは、SELECTクエリの結果を保存するS3のパスを指定できます。何も指定しない（デフォルト）場合は、AthenaコンソールのQuery result locationに設定されたパス（例.s3://aws-athena-query-results-123456789012-ap-northeast-1/Unsaved/2018/10/12/）の下に保存されます。
Output data formatは、SELECTクエリの結果を保存するファイルフォーマットを指定できます。何も指定しない（デフォルト）場合は、カラムナファイルフォーマットであるParquetで保存されます。
[Next]ボタンで次に進むと、条件に基づいて生成されたCTASのSQLが生成、表示されます。
[Create]ボタンを押すと、クエリエディタにてクエリが実行されて、テーブルが作成されます。右にelb_logs_parquetが追加されたことが確認できます。
この操作で作成されたテーブルのDDLは以下のとおりです。SERDEの指定など自動設定してくれるのはとても便利です。
CREATE EXTERNAL TABLE `elb_logs_parquet`( `timestamp` string COMMENT '', `elbname` string COMMENT '', `requestip` string COMMENT '', `requestport` int COMMENT '', `backendip` string COMMENT '', `backendport` int COMMENT '', `requestprocessingtime` double COMMENT '', `backendprocessingtime` double COMMENT '', `clientresponsetime` double COMMENT '', `elbresponsecode` string COMMENT '', `backendresponsecode` string COMMENT '', `receivedbytes` bigint COMMENT '', `sentbytes` bigint COMMENT '', `requestverb` string COMMENT '', `url` string COMMENT '', `protocol` string COMMENT '') ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://cm-bucket/elb_logs_parquet' TBLPROPERTIES ( 'has_encrypted_data'='false')
考慮点と制限事項
CTASクエリとビューの使い分け
CTASクエリはAmazon S3の指定された場所に新しいデータを書き込みますが、ビューはデータを書きません。
CTASクエリの結果の保存先
Amazon S3にCTASクエリ結果を格納する場所は空でなければなりません。CTASクエリは、バケット内のパスの場所（接頭辞）が空であることを確認し、その場所にすでにデータがある場合はデータを上書きしません。同じ場所を再度使用するには、バケット内のキープレフィックスの場所にあるデータを削除します。そうしないと、CTASクエリは失敗します。
CTASクエリ結果を保存する場所を指定することができます。省略すると、Athenaはデフォルトでこの場所を使用します。
s3://aws-athena-query-results-<account>-<region>/<query-name-or-unsaved>/<year>/<month/<date>/<query-id>/
CTASクエリの結果のフォーマット
CTASクエリの結果は、データ格納形式を指定しない場合、デフォルトでParquetに格納されます。CTASクエリの結果は PARQUET、ORC、AVRO、JSON、TEXTFILE のフォーマットで格納することができます。 CTASクエリは、フォーマットに適したSerDeを指定する必要はなく、代わりにフォーマット名指定するのみです。
圧縮フォーマット
GZIP圧縮は、デフォルトでCTASクエリ結果に使用されます。ParquetとORCについては、SNAPPYを指定することもできます。
パーティショニング
1つまたは複数の列でCTASクエリの結果データをパーティション化できます。パーティションテーブルを作成すると、Athenaは自動的にパーティションをAWSグルーデータカタログに追加します。しかし、Athenaは、CTASクエリ結果をAmazon S3の指定された場所に書き込むときに、Athenaが作成する最大100のパーティション以内でなければなりません。
パーティションキーは、テーブルの最後のカラムでなけばなりません。ワークアラウンドは、SELECT句に任意でカラム名を順に指定して、最後のカラムをパーティションキーに指定します。
暗号化
Athenaの他のクエリ結果を暗号化するのと同様に、Amazon S3でCTASクエリ結果を暗号化できます。
データタイプ
CTASクエリの列データ型は、元のクエリで指定されたものと同じです。
利用費
CTASということで更に課金が発生することはなく、従来通り、選択フェーズ（SELECTクエリ）でスキャンされたバイトに基づいて課金されます。テーブルをGlueデータカタログに登録した料金が発生しますが、最初の100万オブジェクトまで無料なのであまり気にする必要はないでしょう。
最後に
これまではデータの更新に関してはGlueやEMR、ETLサーバが必要でした。アドホックなクエリの結果を確認して、その流れで素早くテーブルを作成できるのはとても便利です。簡単かつ柔軟にテーブルが作成できるようになりましたので、Athenaだけで数百MB程度の簡易なETLやフォーマット変換等もこなせてしまうのではないかと期待が高まります。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
