データ分析用のテーブルやGoogle Analytics、FirebaseなどからBigQueryにインポートされたテーブルが更新されていなくて、分析結果が狂ってしまったって事はありませんか？特にGoogle Analytics360のデータは遅れてデータが取り込まれることがよくあるうえに、後から差分データがしれっとインポートされることもあります。
そのため、日次バッチなどで集計をかけていると、集計よりも後にデータがインポートされた場合に「データの件数や内容と集計結果が合わない」ということが発生します。
そういった経緯から「分析用データも監視が必要」とは思っていたのですが、勘定系や基幹系のシステムのようにミッションクリティカルではないので、監視ツールにお金をなかなかかけられません。それにそこに費用をかけるくらいなら、BigQuery MLなどに充てたいですよね？
GoogleスプレッドシートとGoogle Apps Scriptで監視ツールを作成
「PythonでローカルからBigQueryにアクセスできるから、Pythonプログラムを叩けばいいのでは？」と思ったのですが、それだと毎回自分で叩かないといけないし、PCも起動しっぱなしにしておかないといけない、といった課題があるので、他の方法を探してみました。
そして、Google Apps Scriptを使えばBigQueryの結果を取得できて、しかもトリガーで毎日定期的に実行ができる、さらに自分宛てにメールも配信できる、というまさに私の希望をすべて満たしてくれることが判明！本当にGoogleサマサマです。
BigQuery監視ツール作成手順
それではさっそく作成に入ります。監視ツールの基本的な流れは以下になります。
- Google Apps Scriptのトリガーで関数を実行
- BigQueryでクエリを実行
- クエリの結果の件数が1レコードでもあれば結果をメールで送付
- Googleスプレッドシートにログを出力
クエリの作成
最初にクエリを作成しましょう。まずは実行確認のためにBigQueryのWebコンソール上でクエリを作成します。このとき、結果が1件でも返ってきたらエラーとするようなクエリにしてください。
たとえば、Google Analytics 360から前日のデータが格納されていることを確認するには下記のように、「最新の日付が前日ではない場合に結果を返す」というクエリを作成します。
-- BigQuery内にある最新のテーブルの日付とそのテーブルのレコード数を返す
SELECT MAX(_TABLE_SUFFIX) AS tid , COUNT(1) AS cnt_row FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20*`
-- 最新の日付が前日ではないという条件
HAVING MAX(_TABLE_SUFFIX) != FORMAT_DATE('%y%m%d',DATE_SUB(DATE(CURRENT_TIMESTAMP, 'Asia/Tokyo'),INTERVAL 1 DAY))
;上記で使用しているテーブルは2017年までのデータしかないため、tid: 170801 、cnt_row: 903653 という結果を返します。とりあえず、このクエリは後ほど使いますので、メモ帳などに保存しておいてください。
Googleスプレッドシートの作成
クエリ格納用シートの作成
次にGoogleスプレッドシートに先ほど作成したクエリや各種設定情報を書き込んでいきます。このシート名を「Queries」としてください。今回は入力するのはメール送信時に使用する件名とBigQueryで実行するクエリの2つだけにします。
- A列：タイトル（メール送信時の件名）
- B列：BigQueryで実行するクエリ
実行したいクエリが2つ以上ある場合は、3行目以降に追記してください。空行を入れなければ続けて実行するようなスクリプトにしています（スクリプトは後述）。
クエリによってメール送信先を変えたい場合は、C列にメールアドレスの列を追加し、C列で取得したメールアドレスを後ほど説明するGoogle Apps Scriptの配信関数（sendEmail）の引数に入れれば可能です。
ログ用シートの作成
次に、「log」というシート名のシートを作成します。こちらは空で構いません。
Google Apps ScriptでBigQueryにクエリを投げる
シートから情報を取得
今度は肝心の機能の部分をGoogle Apps Scriptで実装します。Google Apps ScriptはGoogleスプレッドシートの「ツール」→「スクリプトエディタ」をクリックすると開きます。Google Apps Scriptのサイトからアクセスするとスプレッドシートとの連携が面倒なので、この方法がおすすめです。
Google Apps Scriptのウィンドウが表示されたら「コード.gs」というタブのエディタ部分に下記のコードを書いて保存します。ファイル名は何でも構いませんが、ここでは「BigQuery監視」としておきます。
function main() {
// スプレットシートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// ’Queries’シートを取得
var queries_sheet = spreadsheet.getSheetByName("Queries");
// 各行のデータを取得（1行目はヘッダーなので飛ばす）
for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
// A列の値（件名）を取得
var query_title = queries_sheet.getRange(i, 1).getValue();
// B列（クエリ）を取得
var query = queries_sheet.getRange(i, 2).getValue();
// 件名やクエリがない場合はその時点で終了（空行の以降の行は実行しない）
if(query_title && query){
// SQLを実行する関数（後述）
runQuery( query_title, query);
}else{
break;
}
}
}SQLを実行
次に、実際にSQLを実行する関数（runQuery）を作成します。この関数はいくつかの処理が混ざっていますが、下記のような流れになっています。
- SQLを実行
- 結果が返ってくるまでループで待つ
- 結果を文字列に変換
- 結果がある場合はエラーなのでメールで送付
- ログ出力
function runQuery(title, query) {
var projectId = 'xxxxx'; // プロジェクトID
var request = {
query: query
};
// standardSQLで実行するために必要
request.useLegacySql = false;
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
// ジョブが完了したか定期的にチェック
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// SQLの結果を取得.
var rows = queryResults.rows;
var totalRows = queryResults.totalRows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
var message = '';
// クエリの結果が1行以上ある場合、結果内容を文字列にしてメール送信＆ログ出力
if (rows) {
// ヘッダーを追加
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
message +=headers;
// 結果を連結して文字列を作成.
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
message += data[i].join(',') + '\n';
}
// 結果を指定したアドレスに送る
sendEmail(title, message);
// 結果をログに出力
writeLog(title + ' ' + rows.length + ' rows returned.');
} else {
// 結果が0件であることをログに出力
writeLog(title + ' No rows returned.');
}
}projectIdはご自身のプロジェクトIDを入れます。プロジェクトIDについてはBigQueryのUIコンソールのプロジェクト名をクリックすると「プロジェクトの選択」ウィンドウが表示されますので、そこの「ID」列がプロジェクトIDになります。
メールを配信
メール配信部分は非常にかんたんで、MailApp.sendEmail 関数を使うだけです。ポイントは以下になります。
- 送り主は今このスプレッドシートとスクリプトを作成しているGoogleアカウント（G Suiteでログインしている場合はそのメールアドレス）
- 件名はフィルタリングしやすいように接頭語＋各クエリのタイトル
- 送り先は任意ですが今回は固定
- 本文にこのスプレッドシートのURLを含める
function sendEmail(subject,message){
var to = '[email protected]'
if(MailApp.getRemainingDailyQuota() == 0) {return;}
// 件名
var subj = '[BigQuery]Alert : ' + subject;
// 今回のスプレッドシートのURL
var url = '\n Script: https://docs.google.com/spreadsheets/d/xxxxxxxxxxx'
MailApp.sendEmail({
// 送り先
'to':to,
// 件名
'subject':subj,
// 本文
'htmlBody':message + url
})
}とくに、スプレッドシートのURLを入れておくと、Gmailで受け取ったときにそのスプレッドシートをすぐに開くことができるのでおすすめです。
なぜこれをしておくかというと、今回の場合、エラーが発生したとき（SQLの実行結果が1件以上）にのみメールが送られるので、突然メールが届いても「このメールなんだっけ？」と忘れがちです。それを防ぐためにURLを入れています。
ログを出力
エラーの有無に関係なく実行結果をログに残しておいたほうが良いので、その関数も作成します。ログは足所にスプレッドシートで作成した「log」シートに追記されます。ログはA列に実行日時、B列に結果が出力されます。
function writeLog(str){
var spreadsheetFile = SpreadsheetApp.getActiveSpreadsheet();
var logSheet = spreadsheetFile.getSheetByName("log");
var lastRow = logSheet.getLastRow();
var lastCol = logSheet.getLastColumn();
var startRow = lastRow+1;
var input = [[formatDateTimeAsString(new Date()),str]];
logSheet.getRange(startRow,1,1,2).setValues(input);
}
// 日次を返す
function formatDateTimeAsString(d) {
var dateString = Utilities.formatDate(d, 'GMT+9:00', 'yyyy/MM/dd HH:mm:ss');
return dateString;
}
トリガーの設定
最後はトリガーの設定になります。トリガーはGoogle Apps Scriptの編集画面から「編集」→「現在のプロジェクトのトリガー」を選択します。
トリガーの画面が表示されますので、右下の「トリガーを追加」ボタンをクリックします。毎日チェックしたい場合は下記のように「日付ベースのタイマー」を選んでください。その他、時間や分ベースでの指定も可能です。
- 実行する関数を選択： main
- 実行するデプロイを選択： Head
- イベントのソースを選択： 時間主導型
- 時間ベースのトリガータイプを選択： 日付ベースのタイマー
- 時刻を選択：任意
トリガーは時間だけでなく、スプレッドシートの起動や変更時なども選択できます。
以上で設定は完了です。
具体的な用途
データの有無の監視以外で私は以下のような用途でもこのツールを使っています。
- 売上高や件数の前日比が80％を下回った場合
- 予期しない異常な値が入った場合（例：平均売上単価が5000円なのに5万円以上の購入があった場合）
- 日時バッチのミスやデータの入れ間違いなどにより重複データが発生した場合
このツールはSQLの結果を判断材料にしているため、好きなように条件を変えられるのがウリです。
応用編
上記以外に関数等を変えてどのようなことができるか？考えてみました。
BigQueryにアクセスできないけど、データを共有したい場合
代理店などとデータを共有したいときなどに有効かと思います。
データをスプレッドシートに書き込むことでレポートの作成なども可能
メールで数値を共有するだけでなく、スプレッドシートにデータを出力してそれを共有するのもありかと思います。ただ、Googleデータポータルを使う場合はBigQueryから直接参照できるのであまり意味はないかもしれません。
「こんな使い方してます」といったおすすめがありましたらコメント欄に書いてもらえると嬉しいです。
[amazon asin=”4798061271″ kw=”Google Apps Script” rakuten id=” ” yahoo=” “] [amazon asin=”B07BNB1Z9L” kw=” Google Apps Script完全入門” rakuten id=” ” yahoo=” “]Google Apps Scriptは情報も少ないし、Googleのドキュメントも例が少ないので、事例や使い方が書かれている本を購入しておくと便利です。
]]>
