GoogleシートとGoogle Apps ScriptでBigQueryを監視

Posted at 2024-12-23

本記事は2021年3月時点での内容になります。最新情報については公式サイトなどを確認願います。

分析データも監視が必要

データ分析用のテーブルや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件でも返ってきたらエラーとするようなクエリにしてください。

たとえば、GA4から前日のデータが格納されていることを確認するには下記のように、「最新の日付が前日ではない場合に結果を返す」というクエリを作成します。

-- BigQuery内にある最新のテーブルの日付とそのテーブルのレコード数を返す
SELECT MAX(_TABLE_SUFFIX) AS tid , COUNT(1) AS cnt_row FROM `project_id.analytics_ga4property_id.event_20*`
-- 最新の日付が前日ではないという条件
HAVING MAX(_TABLE_SUFFIX) != FORMAT_DATE('%y%m%d',DATE_SUB(DATE(CURRENT_TIMESTAMP, 'Asia/Tokyo'),INTERVAL 1 DAY))
;

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 = 'abcd@gmail.com'
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から直接参照できるのであまり意味はないかもしれません。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up