ログ集計でよく使うbigqueryの基本的な関数

bigqueryにアクセスログやアプリケーションログを蓄積していて、色々クエリを書いている中で、BIGQueryの便利な関数を使う機会が多かったのでメモを公開しとく。
いつも忘れてて毎回調べるのでまとめてみた。

個人的によく使う4パターン！！

定番 日付テーブル(tablename_yyyymmdd)に対して、テーブルまたいで期間で検索したい

BigQueryに時系列のデータを入れる際のスタンダードパターン(?)で、テーブル名をtablename_yyyymmddとかにして日次でテーブルを作るケースがよくある。そんな時、テーブルまたいで日付で条件指定したい時のケース

(例)accesslog_20160801
accesslog_20160802...
accesslog_20160901...
上記のようなテーブル構成から2016-08-20から2016-09-01までのあるデータを抽出したい

• TABLE_DATE_RANGE関数を使う

SELECT *
FROM (TABLE_DATE_RANGE( [プロジェクト名:dataset名.accesslog_],
       TIMESTAMP('2016-08-20'),
       TIMESTAMP('2016-09-01'))) 

定番 TIMESTAMP型のデータをUTC→JSTに変換

BigQueryに入るデータはUTCフォーマットされてインサートされるが、分析するときはJSTで見たいって時。

• STRFTIME_UTC_USEC,TIMESTAMP_TO_USECを使う

(例)datetimeというTIMESTAMP型のデータに+09:00して日本時間に変換する

SELECT (STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(datetime) + 32400000000, "%Y/%m/%d %H:%M:%S") AS jsttime
FROM [プロジェクト名:dataset名.accesslog_20160906]

日付単位でデータを集計したい

rowデータに対して毎回クエリを投げるとそれなりのスキャンが走るので、日付毎にアクセス数を別テーブルに保存してそっちで集計する時に使う

• DATE関数を使う

(例)2016-08-20から2016-09-01間の日単位のレコード数をだす

SELECT 
  (STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(datetime) + 32400000000, "%Y/%m/%d %H:%M:%S")) AS jsttime,
  count(*)
FROM (TABLE_DATE_RANGE( [プロジェクト名:dataset名.accesslog_],
       TIMESTAMP('2016-08-20'),
       TIMESTAMP('2016-09-01'))) 
GROUP BY jsttime

特定フィールドのデータをコンバートする

これは、特殊な事情かもしれないが、Re:dashで可視化する事を想定した時に、わかりやすい値でないとグラフ化してもあんまりうれしくない事がある。

例えば、IISログでポート番号毎にアクセス数だしたいけどRe:dashグラフ上にポート番号出てきても直感的に何だかわかる人は少ないとかそういう時に使う。

• REGEXP_REPLACE関数

(例)s_portフィールドの値が、8888→xxx.com,8889→yyy.comとデータをコンバートして、s_port単位のレコード数を出す

SELECT DATE(STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(datetime) + 32400000000, "%Y/%m/%d %H:%M:%S") ) as day,
  CASE
    WHEN s_port = '8888'
  　　THEN REGEXP_REPLACE(s_port,".+",'xxx.com')
    WHEN s_port = '8889'
  　　THEN REGEXP_REPLACE(s_port,".+",'yyy.com')  
    ELSE
        s_port
     END AS s_port,
  count(s_port) AS requerst_count
FROM [プロジェクト名:dataset名.accesslog_20160906]
GROUP BY day,s_port
ORDER BY requerst_count DESC

Re:dashで見た時に、ポート番号で何件とか出るより、xxx.com何件とか見れたほうが視覚的にやさしい。

※CASE分でひたすら分岐するのはバッドケースな気もするので、UDFとか使えばもうちょい綺麗に書けるのかもですがまだそこには手つけてない。

参考

ttps://cloud.google.com/bigquery/query-reference#datetimefunctions