Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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]

Google_BigQuery.jpg

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away