【AWS Athena】str型Timestampをクエリする

AWS Lambda Athena

課題

以下のようなデータがあったとする。

{
  "timestamp": "2019-02-01 00:00:00",
  "data": 100
}
{
  "timestamp": "2019-03-01 00:00:00",
  "data": 100
}
{
  "timestamp": "2019-04-01 00:00:00",
  "data": 100
}

このデータを年月ごとに集計するクエリを作成したい。。 正直に以下のSQLでクエリすると次のようになる

SELECT year(timestamp) as year,
            month(timestamp) as month,
            sum(data)
FROM <your_year(timestamp),month(timestamp)database>.<your_table> 
GROUP BY  year(timestamp),month(timestamp);
SYNTAX_ERROR: line 1:8: Unexpected parameters (varchar) for function year. Expected: year(timestamp) , year(timestamp with time zone) , year(date) , year(interval year to month)

原因と対策

これは、str型のタイムスタンプがタイムスタンプとして認識されていない事が原因である。 したがって以下のように記述する必要がある。

SELECT year(date_parse(timestamp, \'%Y/%m/%d %H:%i:%s\')),
            month(date_parse(timestamp, \'%Y/%m/%d %H:%i:%s\')),
            sum(data)
FROM <your_year(timestamp),month(timestamp)database>.<your_table> 
GROUP BY  year(date_parse(timestamp, \'%Y/%m/%d %H:%i:%s\')),month(date_parse(timestamp, \'%Y/%m/%d %H:%i:%s\'));

参考

AWS AthenaはPrestoベースであることがわかったため、以下のサイトを参考にした * Prestoでの日付の扱い方 - ★データ解析備忘録★ * prestoの気持ち 時間関係(Date and Time Functions and Operators) - Qiita