こんにちは。梶原です。
これは TECHSCORE Advent Calendar 2018 の8日目の記事です。

AWS Glue 東京リージョンでの利用開始から早や1年。
動かしてみないことには内容の理解も追いつかないだろうとは思うものの、何から手を付ければ良いものか。手頃なとっかかりが無いかと途方に暮れておりましたところ、社内の AWS 通からやってみたらと教えてもらったのがデータ分析基盤を作るというものでした。

ありがたい、是非やります、やらせてくださいーーー（＞＜）

で、見事にハマったのです。エラーが起きて「さぁ乗り越えてみろ」と立ちはだかりました。
今回のブログでは Glue で作成したスキーマを Amazon Athena から timestamp 型でひたすらクエリを実行する内容でお届けします。

目次

• 環境と前提条件
• あるべきフォーマットは謎。AWS 公式ドキュメントでは触れられていない
• 実行手順
• timestamp 型に変換 → クエリを実行
• timestamp 型に変換 → クエリを実行 ( 値が無い場合 )
• まとめ
• おわりに

環境と前提条件

• Glue はコンソール画面から操作しました
• 入力データは S3 に JSON 形式で存在するものとします
• Glue クローラを実行してテーブルが作成できることを前提とします

Glue クローラの実行に関しては、AWS 公式に詳細な解説ページがあります。
英語版画面ではありますが実際の画面も張り付けられており、丁寧に作られたページです。
Glue と Amazon S3 を使用してデータレイクの基礎を構築する

Athena × Glue についても公式に素晴らしいページがあります。
Athena と Glue を併用する際のベストプラクティス

ドキュメントどおりに設定し、Glue のクローラを実行してデータベースにテーブルが追加されました。
ここから、話を続けます。

あるべきフォーマットは謎。AWS 公式ドキュメントでは触れられていない

項目型を timestamp 型に変更したい！　日時データなら文字データを timestamp 型に加工してから取り扱う方が便利でしょ！？ ( 多分... )
型の変換を Glue に委ねたところ、Athena でのクエリ実行時にエラーが発生しました。
日付のフォーマットによっては成功したり、エラーが発生したり。どうやらパターンが存在するようです。

そもそもの問題の発端はここにあると思っていますが、データがどのようなフォーマットだと timestamp 型として認識されるのか、AWS の公式ドキュメントでは触れられていません。
それでは自分で試してみるしかない、という訳でやってみました。

実行手順

Glue の画面からスキーマ編集を行い、timestamp 型に変換します。
変換後に Athena で SQL 文を書いて、クエリを実行します。

執筆時点 ( 2018年12月 ) では、Athena のクエリエンジンは Presto 0.172 に基づいています。
Athena は Presto の機能全てを網羅してる訳ではないとのこと。利用可能な SQL や関数は AWS のヘルプページで確認します。
SQL クエリ、関数、および演算子

timestamp 型に変換した際の予想は、Presto は Unix タイムスタンプを取り扱うので「YYYY-MM-DD HH:MI:SS」のようなフォーマットは問題なし、タイムゾーンの取り扱いも出来るので問題なし ( タイムゾーンについては今回は試していません ) 、ISO の「YYYY-MM-DD"T"HH:MI:SS」のようなフォーマットになるとどうかな？というところです。

timestamp 型に変換 → クエリを実行

S3 に準備した入力データです。見やすいように改行・整形していますが、実際には改行なしです。
timestamp_test_1.json

Glue クローラを実行して作られるテーブルでは、項目のデータ型を入力データから予想して自動設定します。テーブルのスキーマを確認したところ、全て string 型と推測されていました。安全な処理ですね。

まずは string1。値は「"20181208"」です。

Athena でクエリを実行すると、string1 は文字として検索可能です。

Glue の画面からスキーマ編集を行い、型を変換します。まずは string1 を string 型から timestamp 型に変更します。特にエラーが発生することもなく、画面操作だけで完了。

string1 が timestamp 型になったところで、もう一度 Athena でクエリを実行します。

エラーが発生することなく実行できましたが、string1 には謎の日付が...
察しの良い方はピンと来たと思います。「20181208」のUNIX 時間へと変換されていました。from_unixtime 関数で timestamp 型への変換が行われたのと同じ動きですね。

ここで、他の項目も全て timestamp 型に変換しました。

エラーが発生することもなく、変換できました。
1項目ずつ、Athena でクエリを実行してみます。

string2。値は「"2018-12-08"」です。

エラーが発生しました。
パースが出来ないとのエラー。内部の処理が謎で、どのような関数を利用しようとしてパースエラーが発生したのかが分かりません。先に試した string1 と同じく from_unixtime 関数かとも思い試してみましたが、エラーメッセージが違いました。内部の処理はこの関数ではないようです。

AWS 公式のドキュメントに記載がないか確認してみたのですが、見つけることは出来ませんでした。

string3。値は「"2018-12-08 15:00:00"」です。

エラーが発生することなく実行できました。

string4。値は「"2018-12-08T15:00:00"」です。

エラーが発生しました。timestamp 型のフォーマットエラーで「yyyy-mm-dd hh:mm:ss[.fffffffff]」にするべきとのこと。

Presto には ISO 8601 フォーマットに対応するための特別な関数「from_iso8601_timestamp」「from_iso8601_date」が用意されています。これらの関数を利用すると、日付である文字列を timestamp 型や date 型に変換することが可能です。

Glue だけで解決したい場合にはテーブルのスキーマに timestamp 型のフォーマットを予め適用しておくやり方がありそうな気がしますが、公式のドキュメントでの解説ページを見つけることが出来ませんでした。

string5。値は「"2018-12-08 15:00:00.123"」です。

エラーが発生することなく実行できました。

string6。値は「"2018-12-08 15:00:00.123456789"」です。

エラーが発生することなく実行できましたが、string6 の元の値と比較するとミリ秒部分が「.123456789」から「.123」へと変わっています。ミリ秒は小数点以下3桁までのようです。

timestamp 型に変換 → クエリを実行 ( 値が無い場合 )

値が無い場合についても試してみました。空データ、つまり Empty な場合と Null の場合です。
timestamp_test_2.json

項目はどちらも timestamp 型に変更しておきます。

string7。値は「""」です。

エラーが発生しました。string2 ( 値は「"2018-12-08"」) の場合と同じくパースが出来ないという内容です。

string8。値は「null」です。

エラーが発生することなく実行できました。

値がない場合は、前もって Null に置換しておく必要がありそうです。
Glue のジョブのスクリプトで簡単にデータの変換を行うことが出来るので、データ変換の流れを予め設計・実施しておけば問題なさそうですね。
AWS Glue Python コードサンプル

まとめ

Glue クローラで自動作成したテーブルのスキーマを変更して、項目型を string 型から timestamp 型に変更しました。 Athena で cast も関数も何も利用しないシンプルな SELECT 文を書き、クエリを実行した結果は以下の通りでした。

おわりに

Glue の理解を深める次のステップとしては、データのスキーマを判断できる分類子を利用すると具体的に何が出来るようになるのか、について取り組んでみようかと思います。

日時データ ( と文字コードも。日本語を取り扱う限りは避けて通ることが出来ません ^^; ) の取り扱いからは、プログラマは永遠に逃れられないことを再認識しました。
そういえば来年には改元もありますね。プログラマ人生においては滅多に遭遇出来ない案件ですから、頑張らねば。気を引き締めて新年を迎えたいと思います。