AthenaでUTF時間を日本時間に変換して表示したい

Question

AWS　Athenaについての質問です。

やりたい事：
・AthenaでELBアクセスログの時間をUTC時間から日本時間に変更したい
・変更した日本時間をYYYY-MM-DD hh:mm表示で別カラムに表示したい
・ドメイン名がhoge.comのアクセスログだけに絞りたい
・更にそこから重複する時間をクエリし、重複した総数を多い順に並べたい

テーブル名はhoge_logで時間のカラム名はtime、日本時間に変更した別カラム名はtime_j、ドメイン名のカラム名はdomain_nameです。

SELECT time,
この辺りでたぶん時間を変換する関数を入れる…？
AS time_j
FROM hoge_log
HAVING domain_name = 'hoge.com'
COUNT(time_j) > 1 , ASC;

色々調べて何となくこんな感じになるのかなとやってみたのですが、エラーが出て上手くいきませんでした。
期待する結果を出力するSQLについてご教授願います。

Comments

[y_waiwai]

エラーが出たなら、エラーメッセージを提示しましょう
エラーメッセージは、いらぬ翻訳省略しないで、出たそのママをコピペで提示してください

[yu_1985]

まず、何か調べてみましたか？同様のことをやっている例は多数出てくると思うのでそちらを参考にしてみてください。

[minmee0126]

エラーメッセージについては、時刻変更のSQL

SELECT time,time at time zone 'Asia/Tokyo'
AS time_j
FROM hoge_log

を流した時に

Your query has the following error(s):
[ErrorCategory:USER_ERROR,ErrorCode:SYNTAX_ERROR],line 1:14:Type of value must be a time or timestamp with or without time zone(actual varchar)
This query ran against the "default" database,unless qualified by the query.Please post the error message on our forum or contact customer support with Query Id:xxxxxxxxxxxxxx

というものが出てきました。

[minmee0126]

はい、
「aws Athena 日本時間にする」等で検索して調べてみたのですが、タイムゾーンやデータ型を変えるだけのものばかりが引っ掛かってしまいます…

[yu_1985]

どうやら変換はできたようなので、次はクエリそのものの構文が正しいかを確認してみてください。
パッとみただけで複数怪しい箇所があります
- GROUP BYをせずにHAIVNGをつかっている
- COUNTを変なところで使っている

[minmee0126]

あれから再度調査して

select　from_iso8601_timestamp(substr（time,1,16）|| ’:00’) at time zone 'Asia/Tokyo' as time_j,
count(*) as request_cnt
FROM hoge_log
WHERE domain_name = ’hoge.com’
GROUP BY time_j
ORDER BY request_cnt asc;

と流してみたのですが、Column ’time_j’cannot be resolved
のエラーが出てしまいました。

Answer 1

日本時間への変更については
SELECT from_iso8601_timestamp(time),from_iso8601_timestamp(time) at time zone 'Asia/Tokyo' as time_j FROM hoge_log
で出す事が出来ました。

ELBの時間が文字列だったので、from_iso8601_timestamp関数でtimestamp型にする必要があったようです。