18
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

BigQueryで日付別テーブルをまとめて日付別に集計したい

Last updated at Posted at 2016-07-27

まえおき

ログを日付別テーブル(例:nginx_log_20160727とか)作ってBigQueryに毎日つっこんでる場合は

-- standard
select ...
from `dataset.nginx_log_*`
where _table_suffix between '20160701' and '20160727'

とかやって必要な期間のテーブルにまたがって集計できるんですが,そうではない,例えばシステムのユーザ情報テーブルを毎日BigQueryにつっこんで日次での変化を知りたいみたいな場合,日付のカラムがなかったりしてうまくいきません.

usersテーブルにBQにインポートした日的なカラムを持たせておけばそいつを使って集計できそうですが,そもそも日付別に分けてるのにさらに日付を持たせるのが無駄っぽくてつらい...

やったこと

  • __TABLES_SUMMARY__を見て必要なsuffixを取得
    • そもそもBQのTableQueryとか使うと内部的に同じことをやってるらしいです1
    • __TABLES_SUMMARY__は大文字じゃないとダメ(若干ハマった)
  • 目的のテーブル群とcross joinして,_table_suffix疑似カラムと一致するものを絞りこむ

たとえば,users_*xxx_statusごとの人数をある期間で日別に集計したい場合はこんな感じ.

-- standard
WITH ts as
  (SELECT substr(table_id, -8) t
   FROM dataset.__TABLES_SUMMARY__
   WHERE table_id LIKE 'users_%')
SELECT parse_date('%Y%m%d', ts.t) t,
       xxx_status,
       count(id) c
FROM `dataset.users_*`,
     ts
WHERE _table_suffix = ts.t
GROUP BY t,
         xxx_status

WITH ts as(...) を毎回書くのしんどいんでUDFにしてみる

Standard SQLを使ってるとそもそもUDFが動かない..?(管理コンソール上のエディタ上のサンプル(passthrough)もStandardだと動かなかった)

// Example user-defined function, documentation: https://goo.gl/6KR8O0
// Sample SQL: SELECT outputA, outputB FROM (passthrough(SELECT "abc" AS inputA, "def" AS inputB))


function generate_date_series(row, emit) {
    var start = new Date(row.start_date.substr(0, 4), row.start_date.substr(4, 2) - 1, row.start_date.substr(6, 2));
    var end = new Date(row.end_date.substr(0, 4), row.end_date.substr(4, 2) - 1, row.end_date.substr(6, 2));
    if (start.getTime() > end.getTime()) {
      var tmp = start;
      start = end;
      end = tmp;
    }
    do {
           emit({date: start});
           var s = new Date(start.getUTCFullYear(), start.getUTCMonth(), start.getUTCDate() + 1);
           start = s;
    } while (end.getTime() >= start.getTime())
}

bigquery.defineFunction(
  'generate_date_series',                           // Name of the function exported to SQL
  ['start_date', 'end_date'],                    // Names of input columns
  [{'name': 'date', 'type': 'timestamp'}  // Output schema
  ],
  generate_date_series                       // Reference to JavaScript UDF
);

エラー処理もなにもないが

SELECT FORMAT_UTC_USEC(date) 
FROM (generate_date_series(SELECT "20160701" AS start_date, "20160710" AS end_date))

とかやれば連続する日付のTimestampが取得できる.

あとがき

Legacy SQLだと難しいかな...?思いついたら追記しよう...


  1. http://stackoverflow.com/a/22735031

18
10
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
18
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?