Amazon AthenaはParquetにすると速くて安いし美味しいし簡単に作れる

背景

末尾に運用時の続編を追記しました

データレイクとしてS3を使って、そこに対してクエリでELTしていく目的でAthenaを使っていたので、パーティションのみ意識して、あとはtsvファイルでの運用がメインでした。

この度、データ量が多いプロジェクトで、かつ速度も求めてAthenaを使うことになり、Parquetと向き合うことにしました。

でもParquetって大変なんでしょ？

もちろんParquet自体も、メリットもふわっと知っていたのですが、何か変換めんどそうだし、、、実際その変換が面倒なことはネックです、みたいな話も聴いたし、、、と思ってました。

簡単に作れました

Athena上のCTASでParquet作成が出来るようになっています。
しかも、パーティションの作成まで出来るようです。

やってみる

Redshiftからunload

Redshiftに置いてあるデータをあれこれして元データを作ります。

unload('
select
yyyymm,
nullAllowedItems,
notNullVeryLongSizeStringItems,
notNullMediumSizeStringItems,
notNullOneCharacterItems,
.
.
.
partitioningprefix 
FROM redshiftTable 
') to 略

この時、最後のカラムをのちにパーティションとして指定することが出来ます。
つまり、この時点で、というかこの先もELTの過程においてS3に配置する際にパーティションを意識する必要が無い のです。

unloadによって、以下のようなデータが出来上がります

s3://xxxxxxxxxxx/athena/fromredshift_tmp/202001/0000_part_00.gz
s3://xxxxxxxxxxx/athena/fromredshift_tmp/202001/0001_part_00.gz
s3://xxxxxxxxxxx/athena/fromredshift_tmp/202001/0003_part_00.gz
...

とりあえずAthenaで読み込めるようにします。

CREATE EXTERNAL TABLE `XXXX_tmptsv`(
yyyymm string,
nullAllowedItems string,
notNullVeryLongSizeStringItems string,
notNullMediumSizeStringItems string,
notNullOneCharacterItems string,
.
.
.
partitioningprefix string)
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://xxxxxxxxxxx/athena/fromredshift_tmp'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'transient_lastDdlTime'='1590840586')

CTAS

すなわち CREATE TABLE AS SELECT がここで登場します。

CREATE TABLE XXXX
WITH (
  partitioned_by  = ARRAY['partitioningprefix'],
  format = 'PARQUET',
  external_location = 's3://xxxxxxxxxxx/athena/data/XXXX'
) AS
SELECT * FROM XXXX_tmptsv limit 0

これだけです。上記のように指定し実行することで

1．Parquet形式に変換され！
2．パーティションが考慮されて！
3．自動的にS3に配置される！

のです！！神かな？？？

注意点1

limit 0 ってなんやねんという話で、これはデータ作成時に対象となるパーティションが100個を閾値にエラーになってしまう仕様の回避策です。パーティションの数が少なければ、上記でlimitを外せばOKです。

今回パーティションを1000個ほど用意してしまったため、制約を回避するために「limit 0 で1回テーブルを作ってしまい、その後INSERT INTOを行う」という方法を取ります。

何となく第六感で100個ギリギリは避けつつ、13個ほどにSQLをINSERTのSQLを分割して実行しました。

INSERT INTO XXXX SELECT * FROM XXXX_tmptsv partitioningprefix in('a','b','c',略)

みたいな感じでOKです。

もちろん画面からもINSERTクエリの実行は行えますが、CUIでやると起きないエラー（サービスクォータ関連ではなかったと思います）に度々遭遇したのでコマンドラインからSQLを実行した方が良いのかもしれません。

注意点2

この時、INSERT INTO でスキャンが発生し料金がかかります。
SELECTが走るのでそりゃそうだよなと思いますが、私はやった後で「なるほど」という顔をしました。

さて、こうして、ねんがんのParquetテーブルをてにいれたぞ、という状態になりました。

作ったテーブル情報

今回、せっかくなのでtsvとParquetの差が見たかったので上記のCTASで

  format = 'TEXTFILE',

としたTSVテーブルも作成しました。
以後、TSV形式のテーブル（EXTFILE）と、Parquet形式のテーブル（Parquet）で速度を比較していこうと思います。

用意したデータのレコード数

150,000,000レコード

これはMySQLじゃちょっと嫌ですね～というくらいの規模かと思います。

用意したデータのボリューム

TEXTFILE：9.2 GB
Parquet：7.4 GB

データ量も減り良い感じです。

※今回圧縮形式の指定を漏らしております。指定しないとgzip、と このページ には書いてありますが、ちゃんと指定した方が良いなと思いました。

色々試してみました

YYYYMMをカウントを取ってみる。

select yyyymm ,count(1) from XXXXX group by 1 order by 2 desc

フォーマット	Run time	Data scanned
TEXTFILE	25.85 seconds	9.16 GB
Parquet	4.11 seconds	754.84 MB

えっ、こんな変わるの、、、？

必須ではなく日本語が入るカラムで試す

select nullAllowedItems ,count(1) from XXXXX group by 1 order by 2 desc

フォーマット	Run time	Data scanned
TEXTFILE	30.77 seconds	9.16 GB
Parquet	14.31 seconds	155.01 MB

必須じゃないからか、スキャン量が激減。なるほど。

必須、長め、カーディナリティ高めの英字が入るカラムで試す

select notNullVeryLongSizeStringItems ,count(1) from XXXXX group by 1 order by 2 desc

フォーマット	Run time	Data scanned
TEXTFILE	2 minutes 18 seconds	9.16 GB
Parquet	1 minute 49 seconds	2.22 GB

（あれ、、、？）

必須、やや短め、カーディナリティそこそこの英字が入るカラムで試す

select notNullMediumSizeStringItems ,count(1) from XXXXX group by 1 order by 2 desc

フォーマット	Run time	Data scanned
TEXTFILE	26.11 seconds	9.16 GB
Parquet	6.31 seconds	130.14 MB

実際には多いこのケース、速くなって嬉しい。

必須、1文字、カーディナリティ低めの英字が入るカラムで試す

select notNullOneCharacterItems ,count(1) from XXXXX group by 1 order by 2 desc

フォーマット	Run time	Data scanned
TEXTFILE	20.95 seconds	9.16 GB
Parquet	3.53 seconds	41.61 MB

速い。

所感

・想像していたよりも速かった
・AthenaはS3にあるものにSQLを実行できるから気軽に使える訳で、Parquetまで試さないケースが多い＝用途によっては評価を見誤っているケースもあるのではなかろうか。私はそうでした。
・150,000,000レコードくらいをニアリアルタイムで扱いたい場合、MySQLでは辛いのですがAthenaにするとインデックスとかも気にせず、項目によっては3秒ちょっとで返ってくるのがとても良い。
・カーディナリティ高い長い文字列カラムさんが想定外の動きをしましたが、ゆえにデータ検証は大切だし楽しいですね（つらい）

このあと

・以下のページで、まだ出来てないことを試したい。まだ速くなるんか。
Amazon Athena のパフォーマンスチューニング Tips トップ 10
・その後、BigQueryとも比較したい

参考

※いずれもDevelopers.IO / クラスメソッドさまより。
Amazon Athena が待望のCTAS（CREATE TABLE AS）をサポートしました！
Amazon Athena がついにINSERT INTOをサポートしたので実際に試してみました！

続編

本記事が結構閲覧頂けているようなので、運用時の知見などの続編も追記いたします。

・Amazon AthenaでCTASが失敗したときの対応方法
・Amazon Athenaのスキャンされるデータ量の検証とParquetのcount(1)が便利という話