まえおき
ログを日付別テーブル(例: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だと難しいかな...?思いついたら追記しよう...