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

まえおき

ログを日付別テーブル(例：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とか使うと内部的に同じことをやってるらしいです¹
  • __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 ↩