Athenaでtimestamp型が空白になる場合の対応とutcからjstへの変換

timestamp型で困ったこと

2020-04-21 04:51:25 UTC
2020-04-26 16:01:22 UTC

といった時刻表記をAthenaでjstの時刻でtimestamp型に変換する際に調べたことを記録しておく。
上記表記はUTCという余計な文字列が入っていたりとそのままではtimestamp型に変換できないのでいくらか変換の手順を踏んだ。
今回はカラム名をtime_columnとする

結論

cast(substr(time, 1, 20) as timestamp)  + interval '9' hour as time

これで変換できた

変換手順

文字列削除

まず、UTCといった文字列を削除するためにsubstr関数で文字列を消去する。

substr(time_column, 1, 19)

キャスト

余計な文字列の削除ができたのでtimestamp型にキャストする

cast(substr(time, 1, 20) as timestamp)

utcからjstに変換

• intervalで時間の加減算が可能となる。今回はhourだがdayなども可能。 参考

cast(substr(time, 1, 20) as timestamp)  + interval '9' hour

timestamp型を指定すると値が空白になってしまう

今回の元データがもし

2020-04-21 04:51:25
2020-04-26 16:01:22

だったとしても元からtimestamp型を選択したとしても、エラーはでないが値がとれない（空白となる）。
これはYYYY-MM-DD HH:MM:SS.fffffffffという形式でないといけないかららしい。
AWSのドキュメントにもその対応がある
Amazon Athena のテーブルにクエリを実行すると、TIMESTAMP の結果が空になる
リンク先の解決でも良いが、自分は今回parquetにも変換したかったのでcreate table asで対応した
AthenaでCSVファイルからParquet形式へ変換
こんなドキュメント用意するくらいならYYYY-MM-DD HH:MM:SSに対応してくれないのかな…とはちょっと思う（キャストはうまくいくわけだし）。