この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、藤本です。
先日、Amazon Athena のクエリ結果を SPICE にインポートして、 QuickSight で可視化するというブログをエントリしました。その時に得られた知見をもう一つご紹介します。
タイトルが何を言っているのか分かりづらいですが、、、ELB のアクセスログのタイムスタンプはフォーマット上、Athena の Timestamp にも、QuickSight の Date タイプにも適合しません。でも、グラフ表示上、Date として扱いたいです。タイムスタンプの文字列でも、Athena の場合は、timestamp like '2017-01-01T10:%'というようなクエリで、QuickSight の場合は、Start with フィルターである程度の絞り込みはできますが、柔軟なクエリをしたい場合に複雑なクエリや、細かいフィルターの指定が必要となります。何より、QuickSight による時間単位の集計ができなくなってしまいます。
そこで ELB のアクセスログのタイムスタンプを QuickSight の Date として利用する方法を考えました。
ELB のアクセスログのタイムスタンプフォーマット
まず、ELB のアクセスログのフォーマットは以下のようになります。
yyyy-MM-ddTHH:mm:ss.SSSSSSZ
例えば、日本時間の2017年1月8日18時33分40.123456秒、であれば、
2017-01-08T09:33:40.123456Z
となります。
QuickSight で Date への変換
QuickSight の Date タイプのフォーマット
QuickSight の Date タイプで認識される日付フォーマットは 35 種類あります！ELB のアクセスログのタイムスタンプの日付フォーマットは 35種類ともにヒットしません。。。
例えば、以下のようなフォーマットです。
yyyy-MM-dd, for example 2015-10-01.
yyyy-MM-ddTHH, for example 2015-10-01T16.
yyyy-MM-ddTHH:mm, for example 2015-10-01T16:08.
yyyy-MM-ddTHH:mm:ss, for example 2015-10-01T16:08:29.
yyyy-MM-ddTHH:mm:ss.SSS, for example 2015-10-01T16:08:29,322.
yyyy-MM-ddTHH:mm:ss.SSSZZ, for example 2015-10-01T16:08:29.322-08:00.35種類は下記ドキュメントページに記載されています。
マイクロ秒じゃなければ、ヒットしますね。。サポートしてほしいものです。
計算フィールドを利用した変換
それでどうやって、Date タイプにするのかと言えば、計算フィールドを利用します。計算フィールドは他のフィールドの値や、固定値に関数を適用した値を新しいフィールドとして追加することができます。
計算フィールドに関しては下記エントリをご参照ください。
アクセスログのタイムスタンプをyyyy-MM-ddTHH:mm:ss.SSSで切り取った文字列を Date タイプに変換する計算フィールドを作成します。
それでは実際にやってみましょう。
Amazon Athena を設定する
ELB のアクセスログを Athena でクエリするには下記ブログにあるCREATE TABLEでテーブルを作成できます。
データセットを作成する
QuickSight の New Data set の画面へ遷移します。今回は直接 Athena から取得します。Data Set に Athena を選択します。Data source name は適当に elblog とし、Create data source をクリックします。
作成したテーブルを選択し、Select をクリックします。
計算フィールドを追加するため、Edit/Preview data をクリックします。
request_timestampが文字列として認識されています。
試しに Date タイプに変えてみましょう。
エラーとなりました。
文字列に戻します。
計算フィールドから Date タイプのフィールドを作成します。New Field をクリックします。Fomula に関数を書きます。先頭 23文字（ミリ秒まで）を切り取って、その 23文字合わせた日付フォーマットで Date タイプに変換する関数です。
parseDate(left({request_timestamp}, 23), "yyyy-MM-dd'T'HH:mm:ss.SSS")
下記でも出来たけど、いいのかな。。S を 6つでマイクロ秒？
parseDate({request_timestamp}, "yyyy-MM-dd'T'HH:mm:ss.SSSSSSZ")timestamp フィールドが表示されます。フィールドタイプも時計マークで Date タイプとして認識されています。
Save & visualize からグラフを作成します。
Date タイプですので、一時間単位のアクセス数の集計表示が可視化できました。
ELB のアクセスログの場合は、Athena のテーブル作成時の正規表現で Date タイプの日付フォーマットに合わせた方が計算フィールドを作成しなくてもいいので、楽かもしれません。
Athena で Timestamp への変換
合わせて Athena の Timestamp への変換もしらべてみました。Athena は日付タイプに Date、Timestamp をサポートしています。Date は年月日までを保存することができます。日付で利用したいことは少なそうなので今回は対象外とします。
Athena の Timestamp フォーマット
Athena が Timestamp で認識する日付フォーマットはドキュメントには言及されていません。たぶん、Presto の仕様なのかな。Presto のドキュメントには以下の記載があります。
Instant in time that includes the date and time of day without a time zone. Values of this type are parsed and rendered in the session time zone.
7.1. Data Types - TIMESTAMP
Example: TIMESTAMP '2001-08-22 03:04:05.321'
yyyy-MM-dd hh:mm:ss.SSSの日付フォーマットに対応しています。
ELB のアクセスログのタイムスタンプの日付フォーマットとは一致しませんね。
テーブルのフィールドタイプとしては Timestamp で取り込むことができなさそうですが、SELECT時にfrom_iso8601_timestamp関数を利用することで、ISO8601形式の文字列から Timestamp へ変換して取り出すことができます。
以下のような感じです。
テーブル名がelb_log、ELB のタイムスタンプの文字列のカラムがrequest_timestampです。
SELECT from_iso8601_timestamp(request_timestamp) FROM elb_log条件で利用する場合は以下のようなクエリで絞り込みを行うことができます。
SELECT count(*)
FROM (
SELECT from_iso8601_timestamp(request_timestamp) AS time
FROM elb_log
)
WHERE
time>date_parse('2016-12-21', '%Y-%m-%d')まとめ
いかがでしたでしょうか？
QuickSight は Date タイプを扱えるかどうかで解析の幅に大きく影響します。ELB のタイプスタンプに限らず、Date タイプで認識させたいけど、認識されない値がある場合の参考になれば幸いです。
19
