この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
先日はダイアログベースでCTAS（CREATE TABLE AS）クエリを自動生成して、テーブル作成する方法をご紹介しました。CTASクエリをSQLで作成するとより詳細なオプション指定が可能であり、コードの変更管理という観点でも有用です。今回は前回書ききれなかった、データ分析環境で使うCTASクエリでSQLで作成する様々なユースケースについてご紹介します。さらに実用性がどうかを確認するため、約24.6GBファイル(GZIP)をParquetに変換するヘビーな検証実施しました。
初めて、CTASクエリを利用する場合は以下のブログを御覧ください。
CTASクエリでテーブルを作成する方法
CTASクエリで作成する場合は、SELECTクエリの先頭にCREATE TABLE ASを先頭に追加することで簡単に利用できます。
構文
指定した名前とパラメータでビューを作成します。WITH句で詳細なオプション指定が可能になります。
CREATE TABLE table_name
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]執筆時点では、日本語マニュアルは公開されていませんので、詳細は英語マニュアルCREATE TABLE ASを参照してください。
CTASの例
基本的には、一般的な構文で可能ですが、S3ファイルの出力先やファイルフォーマット、パーティションの有無に応じて変更可能です。
CREATE TABLE test AS
SELECT
orderkey,
orderstatus,
totalprice / 2 AS half
FROM orders新規にCTASクエリを作成する
新規にCTASクエリを作成するには、[テーブルの作成]リンクから[CREATE TABLE AS SELECT]テンプレートを利用します。
選択すると、クエリビューに以下のようなクエリテンプレートが設定されます。このテンプレートも基に作成することもできます。
CREATE TABLE IF NOT EXISTS new_table_name
WITH (format='FORMAT_NAME', external_location='s3://table-data-location') AS
SELECT
column_name1, column_name2
FROM
table_name
WHERE
conditionダイアログベースで自動生成されたCTASクエリをテンプレートとして利用する
[テーブルの作成]リンクからテンプレートを利用するご紹介しましたが、私はダイアログベースでCTAS（CREATE TABLE AS）を利用したときに表示される、自動生成されたCTASクエリをテンプレートとして利用する方がおすすめです。
CTASクエリを使うユースケース
CASE１: CTASでフォーマット変換、簡易なETLする
表のすべての列をコピーして表を作成します。以下の例では、old_tableのデータがより分析に適したカラムナファイルフォーマット（Parquet）が生成され、new_tableからクエリが実行できるようになります。
CREATE TABLE new_table AS
SELECT *
FROM old_table;指定するSELECT文は、特定の列を選択、テーブルの結合、WHERE句に条件を指定、集計・ソートなど、一般的なSELECT文と変わりません。
CREATE TABLE new_table AS
SELECT column_1, column_2, ... column_n, count(*) AS count
FROM old_table_1, old_table_2, ... old_table_n
WHERE condition
GROUP BY 1,2, ... n;
ORDER BY 1,2, ... n;これだけで簡単なETLが可能です。
CASE２: CTASを使用して既存のテーブルの空のコピーを作成する
WITH NO DATA指定することで、元のテーブルと同じスキーマおよび空の新しいテーブルを作成できます。正直、利用用途が思いつきませんが、執筆時点ではサポートされていないINSERT INTO SELECTが使えるようになることを期待したいです。
CREATE TABLE new_table
WITH NO DATA
AS SELECT *
FROM old_table;CASE３: CTASクエリ結果のデータ格納形式と圧縮形式の指定する
CASE1のようにデータ格納形式と圧縮形式を指定しないと、データ格納形式はParquet、圧縮形式はGZIPとなります。
サポートしているデータ格納形式
下記のデータ格納形式からいずれかを選択して指定します。カラムなファイルフォーマットであるPARQUETやORCで出力するとパフォーマンスの改善やコストの削減が期待できます。一方、CSVやJSONなどのテキストフォーマットはヒューマンリーダブルなので扱いやすく、様々なツールと連携可能です。
- PARQUET
- ORC
- AVRO
- JSON
- TEXTFILE
サポートしている圧縮形式
全てのデータ格納形式でGZIPを選択可能です。ParquetとORCのみSNAPPYを選択できます。指定可能なプロパティを見る限り非圧縮という選択肢はありません。
- GZIP
- SNAPPY
JSONの例
まずは一番単純なJSON変換です。formatにJSONを指定するとGZIP圧縮したデータファイルが生成されます。
CREATE TABLE new_table
WITH (
format = 'JSON')
AS SELECT *
FROM old_table;CSV/TSVの例
サポートしているデータ格納形式にCSVやTSVはありませんが、データ格納形式にTEXTFILE、field_delimiterプロパティにカンマ(field_delimiter=',')やタブ(field_delimiter='\t')を指定することで、CSV/TSV出力できます。以下はCSV出力の例です。
CREATE TABLE new_table
WITH (
format = 'TEXTFILE',
field_delimiter=',')
AS SELECT *
FROM old_table;なお、field_delimiterのデフォルトとは'\001'です。
Parquetの例
デフォルトは圧縮形式はGZIPですが、PARQUETとORCのみサポートされている圧縮形式です。下記ではより圧縮・展開が高速な圧縮形式であるSNAPPYに指定しています。圧縮形式の指定をパラメタがparquet_compressionに指定します。
CREATE TABLE new_table
WITH (
format = 'PARQUET',
parquet_compression = 'SNAPPY')
AS SELECT *
FROM old_table;ORCの例
ほとんど、PARQUETと変わりませんが、圧縮形式の指定を指定するパラメタがorc_compressionに変わります。
CREATE TABLE new_table
WITH (format = 'ORC',
orc_compression = 'SNAPPY')
AS SELECT *
FROM old_table ;PartitioningとBucketingの例
Partitioningとは、データを指定したキーごとにファイルにまとめてグループ化します。一方、Bucketingとは、データファイルを指定したキーとファイル数で分割します。Partitioningによってデータのスキャンを削減し、Bucketingによってファイルサイズの最適化によるスキャンの効率化や並列性を向上します。
先日、解説とサンプルを紹介していますので、以下のブログを御覧ください。
CTASクエリでテーブルを再作成したい場合は？
CTASクエリは、テーブルやデータファイルが既に存在するとエラーになりますので、再作成したい場合はテーブルとデータを削除します。
- テーブルを削除
- external_locationに指定したS3パスの下のデータファイルを全て削除
頻繁にテーブルを再作成したいユースケースでは、external_locationは指定しないで、デフォルトのLOCATION（s3://aws-athena-query-results--///<month///）にデータファイルを出力して、古いデータのクリーナップはS3のライフサイクルマネジメントで、削除するように設定して運用を簡素化すると良いでしょう。そうすると、再作成はテーブルの削除・再作成のみなのでAthenaもAPI操作のみで自動化できます。
本当に実用性があるのか？ 約24.6GBファイル(GZIP)を変換してみると..
今回は、GZIPにて圧縮した約24.6GBファイルをPartitioningとBucketingして、さらにSNAPPYで圧縮したParquetファイルに変換してみました。結果は「実行時間: 5分33秒, スキャンしたデータ: 24.59GB」です。
Athenaは、1TB(1024GB)当たり5USDなので、コストを試算すると、$0.123でした。
$0.123 = 5(USD) * 24.6(GB)/1024(GB)
生成されたファイルは以下のとおりです。Bucketingしたキーの選定が良くないため一部偏りが生じてしまいましたが、いい感じでPartitioningとBucketingされていることが確認できます。処理時間とコストともに合格点と言えるでしょう。
$ aws s3 ls s3://aws-athena-query-results-318507007885-us-east-1/Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/ --recursive --profile tableau-labo
2018-10-19 00:33:11 415153183 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00000
2018-10-19 00:33:10 415011721 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00001
2018-10-19 00:33:04 415069949 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00002
2018-10-19 00:33:11 415120711 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00003
2018-10-19 00:35:29 396 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00004
2018-10-19 00:33:07 415060708 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00005
2018-10-19 00:33:07 415241768 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00006
2018-10-19 00:35:29 396 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00007
2018-10-19 00:33:02 415149707 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00008
2018-10-19 00:35:29 396 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1992-01-01/20181018_152956_00006_2va4k_bucket-00009
2018-10-19 00:33:10 414148181 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1993-01-01/20181018_152956_00006_2va4k_bucket-00000
2018-10-19 00:33:07 413968508 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1993-01-01/20181018_152956_00006_2va4k_bucket-00001
2018-10-19 00:33:07 413991782 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1993-01-01/20181018_152956_00006_2va4k_bucket-00002
:
:
2018-10-19 00:35:28 396 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1998-01-01/20181018_152956_00006_2va4k_bucket-00007
2018-10-19 00:34:53 242766538 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1998-01-01/20181018_152956_00006_2va4k_bucket-00008
2018-10-19 00:35:28 396 Unsaved/2018/10/18/tables/eb5e4c4c-1f56-456d-8927-28b485a4e28c/lo_orderyear=1998-01-01/20181018_152956_00006_2va4k_bucket-00009最後に
様々なデータ格納形式と圧縮形式はもちろん、PartitioningとBucketingについてもプロパティ指定するだけでデータの作成、テーブル定義、パーティションの自動設定に至る一連の作業をCTASクエリによって自動化されます。ホント、Glueで作成した簡単なものはAthenaのCTASクエリに置き換わってしまいそうです..でも検証結果もいい感じだったのでガンガン置き換えていこうかと考えています。
24
