【Athena の躓きシリーズ】Athena で MySQL の UNIXTIME を扱うクエリを変換した時にハマったこと

タダです.

業務で MySQL で扱っていたクエリをAthena で使えるように変換しながら利用者のサポートしていて UNIXTIME の扱いでハマったので Athena でどのように対応したかをメモとしてまとめておきます.

1, UNIXTIME を JST に変換して使いたいケースのクエリ
2, 現在の日付から1ヶ月先の日付を計算するケースのクエリ
まとめ
- 関連記事

1, UNIXTIME を JST に変換して使いたいケースのクエリ

SQL で下記のように UNIXTIME を JST 変換してYYYY-MM-DD形式で扱うクエリがありました.このままでは,Athena では扱えないのでエラーがでてしまうので,これを Athena 用に変換して利用可能に対応しました.なお,hogetime は UNIXTIME 形式でレコードで登録されているデータになります.

SELECT 
~中略~
date_format(min(FROM_UNIXTIME(hogetime/1000 + 9 * 3600)), "%Y-%m-%d") hoge_date
~中略~

結論として下記の変換を行ってます.FROM_UNIXTIME の位置を外側に持っていき,min関数の変換後に UNIXTIME タイムスタンプ変換で MySQL と同様にクエリの結果を得られるようにしました.

SELECT 
~中略~
date_format(FROM_UNIXTIME(min(hogetime/1000 + 9 * 3600)), '%Y-%m-%d') as hoge_date
~中略~

2, 現在の日付から1ヶ月先の日付を計算するケースのクエリ

次は,1ヶ月先のレコードを WHERE 句で扱いたい場合に対処した時の話です.between 以降で直近1ヶ月間のデータを扱うのですが,MySQL ではクエリ内で変数をセットしたりできましたが,Athena では変数のセットがサポートされてないようでエラーが出たので,この仕様も含んで対応しました.なお,hogetime は UNIXTIME 形式でレコードで登録されているデータになります.

SELECT 
~中略~
WHERE                                                           
~中略~                                                        
    and hogetime between @start_day - (30 * @end_day) and  @start_day
~中略~

結果として次のクエリにしました.

SELECT 
~中略~
WHERE                                                           
~中略~                                                        
    and from_unixtime(hogetime/1000)  <= (current_timestamp - interval '28' day)
~中略~

まず,比較演算子の左部分は先の1の問題に対応するために使った形式で UNIXTIME のデータを扱うよう変更しました.次に,Presto で何日後や何ヶ月後の計算にintervalを使うことで実現できるのでcurrent_timestamp関数と合わせて使って timestamp 形式で比較できるように対応して期待したクエリ結果を得られるようになりました.

prestodb.io