BigQueryを簡単にグラフにするGoogle Apps ScriptにもあるとおりApps ScriptからBigQueryにSQLを発行して、Google Sheetsのシート上にその結果を入れる事ができます。

ただ、テーブルが大きすぎるとJOINするにも時間がかかってしまったり、クエリのコストがあがってしまったりします。
そのようなときに便利なのが、テーブルのデータをサマライズした中間テーブルを作る事です。

例えば、user_idごとのアクセスログが保持してあるテーブルがあるとしたら、user_idごと、アクセスURLごとのアクセス数を中間テーブルとして作ります。そうすれば中間テーブルごとにJOINをかけるのが比較的短いクエリ実行時間で出来るので中間テーブルを作るのは大きなデータをもっているところでは必須であったりします。

それではどのように設定するのか手順を紹介します。

手順

Spreadsheet上に「SummaryQueries」というシートを作り設定する
Apps Scriptを作成し、プロジェクトをBigQueryが所属しているプロジェクトと同じにする
中間テーブル用のデータセットをBigQuery上につくります。
中間テーブルを作るApps Scriptを書き、試しに実行してみる
現在のプロジェクトトリガーに設定

Spreadsheet上に「SummaryQueries」というシートを作り設定する

SummaryQueriesというシートをつくり以下のように設定します。（シート名はかならずSummaryQueriesにしてください)

A列には、BigQueryに発行したいSQLを書きます。この結果が中間テーブルの結果となります。
B列には、中間テーブルのテーブル名が入ります。日別で作成していくイメージなので、user_daily_summary_なら実際には、user_daily_summary_20161122のようなテーブル名の中間テーブルが作られます。
C列には用途の説明が入ります。何の中間テーブルか、作った人しかわからなくなってしまうのを防ぐコメントの役割です。

Apps Scriptを作成し、プロジェクトをBigQueryが所属しているプロジェクトと同じにする

Google Sheet上から[Tools] => [Script Editor]でApps Scriptを開きます。
Apps Script上で、[Resources] => [Developers Console Project]を開き、Projectを変更します。
このプロジェクトはGoogle Cloud PlatformのConsole上から調べ、BigQueryが所属しているプロジェクトと同じにしてください。

BigQuery APIを有効にする

[Resources] => [Advanced Google Services]からBigQuery APIを有効にしてください。

中間テーブル用のデータセットをBigQuery上につくります。

今回はsummaryという名前にしました。

中間テーブルを作るApps Scriptを書き、試しに実行してみる

以下のようなクエリをシート上に書きます。
サンプルテーブルにあるハッカーニュースの日付別のコメント数を中間テーブルにするものです。

SELECT 
  DATE(USEC_TO_TIMESTAMP(time*1000000 + 9 * 3600 * 1000 * 1000)) as date,
  parent,
  COUNT(*) as comment_count
FROM 
  [bigquery-public-data:hacker_news.comments] 
WHERE
  DATE(USEC_TO_TIMESTAMP(time*1000000 + 9 * 3600 * 1000 * 1000)) = '__TODAY__'
GROUP BY
  1, 2

Apps Scriptは以下のようになります。

var projectId = 'xxx';
var ssId = '1qbBIpigyq-yyy';
var datasetId = 'summary';


///////////////////////////////////////////////////////////////////////////////
// BigQueryで中間テーブル(Summary Table)を作る

// 特定の期間分、中間テーブル用クエリを実行
function runAllSummaryQueriesBatch() {
  var functionName = "runAllSummaryQueriesBatch";
  deleteTrigger(functionName);
  var daysBefore = 450;  // どのくらい前の日からテーブルを作るか
  var daysBeforeEnd = 440;  // どのくらい前の日までテーブルを作るか
  var specificRow = 0;  // 実行させたい行数を選ぶ。すべて実行させたい場合は0
  var nextDaysBeforeKey = "nextDaysBefore";
  var properties = PropertiesService.getScriptProperties();
  var nextDaysBefore = parseInt(properties.getProperty(nextDaysBeforeKey));
  if (nextDaysBefore <= daysBefore && nextDaysBefore != -1) {
    daysBefore = nextDaysBefore;
  }

  for(var i = daysBefore; i >= daysBeforeEnd; i--) {
    if (specificRow == 0 && (daysBefore - i) % 4 == 3) {
      // トリガーを設定
      setTrigger(functionName);
      return;
    }
    properties.setProperty(nextDaysBeforeKey, i);
    Logger.log("#################################################################");
    Logger.log("##### Start => daysBefore: " + i, ", specificRow:" + specificRow);
    runAllSummaryQueries(null, i, specificRow);
    Logger.log("##### Finish => daysBefore: " + i, ", specificRow:" + specificRow);
    Logger.log("");
  }

  properties.setProperty("nextDaysBefore", -1);
  deleteTrigger(functionName);
}


// シートの全中間テーブル用クエリを実行. 引数なしで実行すると、1日前のすべてのクエリを実行する
function runAllSummaryQueries(event, daysAgo, specificRow) {
  var daysAgo = (typeof daysAgo !== 'undefined') ?  daysAgo : 1;
  var specificRow = (typeof specificRow !== 'undefined') ?  specificRow : 0;  // 特定の行だけ実行する. 0ならすべて
  var spreadsheet = SpreadsheetApp.openById(ssId);
  var queries_sheet = spreadsheet.getSheetByName("SummaryQueries");
  var targetDate = new Date((new Date()).getTime() - daysAgo * 24 * 60 * 60 * 1000);

  for (var i = 2; i <= queries_sheet.getLastRow(); i++) {
    if (specificRow != 0 && specificRow != i) {
      continue; 
    }
    var tableId = queries_sheet.getRange(i, 2).getValue().toString() + formatDate(targetDate, "YYYYMMDD");
    runSummaryQuery(tableId, queries_sheet.getRange(i, 1).getValue().toString(), targetDate);
  }
}

function runSummaryQuery(tableId, templateSql, targetDay) {
  var sql = replaceSqlPlaceholder(templateSql, targetDay);
  Logger.log(tableId);
  Logger.log(sql);  // 実行SQLのログ
  createTableBySQL(tableId, sql);
}

// テンプレートSQLのプレースホルダーを置換する. 例えば__TODAY__などを指定の文字列に置き換える
function replaceSqlPlaceholder(templateSql, today) {
  var todayStr = formatDate(today, "YYYY-MM-DD");
  var sql = templateSql.replace(/__TODAY__/g, todayStr);

  for (var i = 1; i <= 30; i++) {
    var daysAgo = new Date(today.getTime() - i * 24 * 60 * 60 * 1000);
    var daysAgoStr = formatDate(daysAgo, "YYYY-MM-DD");
    var regExp = new RegExp("__" + i + "_DAYS_AGO__", "g");
    sql = sql.replace(regExp, daysAgoStr);
  }

  for (var i = 1; i <= 30; i++) {
    var daysSince = new Date(today.getTime() + i * 24 * 60 * 60 * 1000);
    var daysSinceStr = formatDate(daysSince, "YYYY-MM-DD");
    var regExp = new RegExp("__" + i + "_DAYS_SINCE__", "g");
    sql = sql.replace(regExp, daysSinceStr);
  }
  return sql;
}

///////////////////////////////////////////////////////////////////////////////


///////////////////////////////////////////////////////////////////////////////
// ライブラリ関数

// SQLを実行し、tableIdにテーブルを作成する. すでにテーブルが存在していた場合は削除する
function createTableBySQL(tableId, sql) {
  var job = {
    configuration: {
      query: {
        query: sql,
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        }
      }
    }
  }

  try {
    var getResult = BigQuery.Tables.get(projectId, datasetId, tableId);
    // テーブルがすでに存在していたとき
    BigQuery.Tables.remove(projectId, datasetId, tableId);
  } catch(e) {
    if (e.message.indexOf("Not found") < 0) {
      Logger.log("Error: " + e.message);
      return;
    }
  }

  var queryResults = BigQuery.Jobs.insert(job, projectId);
  Logger.log(queryResults.status);
}

function formatDate(date, format) {
  if (!format) format = 'YYYY-MM-DD hh:mm:ss.SSS';
  format = format.replace(/YYYY/g, date.getFullYear());
  format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));
  format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));
  format = format.replace(/hh/g, ('0' + date.getHours()).slice(-2));
  format = format.replace(/mm/g, ('0' + date.getMinutes()).slice(-2));
  format = format.replace(/ss/g, ('0' + date.getSeconds()).slice(-2));
  if (format.match(/S/g)) {
    var milliSeconds = ('00' + date.getMilliseconds()).slice(-3);
    var length = format.match(/S/g).length;
    for (var i = 0; i < length; i++) format = format.replace(/S/, milliSeconds.substring(i, i + 1));
  }
  return format;
}

// 指定の関数のトリガーを削除する
function deleteTrigger(functionName) {
  var allTriggers = ScriptApp.getProjectTriggers();
  for(var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(allTriggers[i]); 
    }
  }
}

// トリガーを設定する
function setTrigger(functionName) {
  ScriptApp.newTrigger(functionName).timeBased().everyMinutes(1).create(); 
}

///////////////////////////////////////////////////////////////////////////////

抜粋して、各変数の意味は以下になります。

グローバル変数
- projectId: BigQueryが所属しているプロジェクトと同じid
- ssId: Google Sheetsと同じID. URLに記載されているやつ
- datasetId: 先程作成したデータセットの名前
runAllSummaryQueriesBatch関数: トリガーではなく直接AppsScriptの実行ボタンから実行したいときのための関数
- daysBefore, daysBeforeEnd: 一気に数日分の中間テーブルを作るために設定しています。今回のハッカーニュースは2015/10
- specificRow: 「SummaryQueries」シートの特定の行の中間テーブルのみ作成したいときはこれを選びます。

結果として以下の中間テーブルが作れました。

現在のプロジェクトトリガーに設定

[Resources] => [Current Project's Triggers] から runAllSummaryQueries を任意のタイミングで実行します。

Tips

今回のApps Scriptで考慮すること

Apps Scriptは実行時間の最大が5分間ですので、あまりにも実行時間が長い場合は強制的に停止されます。今回はそれを防ぐために以下の措置をしています。

Triggerをつかい、一定クエリごとに実行をスクリプト自体で停止し、1分後のトリガーを設定し、続きから開始する
クエリの実行完了をまたずにつぎのクエリを実行している。

上記によりすべての中間テーブルのクエリが実行できるのですが、落とし穴もあって、作成する中間テーブルが他の中間テーブルに依存しているとテーブルが作られる前に実行されてしまいエラーとなる場合があります。
その場合は、Apps Scriptで工夫し、待つようにするとかできると思うのですが、例の5分制限もありやや面倒だと思うので、特定の行だけ実行する関数をつくりそれをプロジェクトトリガーで実行するのが楽だと思います。

FQ7などを出すときの方法

例えば、2016/11/01のFQ7を出すときは、複数のテーブルに渡ってクエリを発行する必要がありますが、今回のApps Scriptを使えば、table定義は例えば以下のようにできます。

    TABLE_DATE_RANGE([summary.user_access_],
      TIMESTAMP('__6_DAYS_AGO__'),
      TIMESTAMP('__TODAY__')
    )

トリガーで実行されたもののログを長期的に保存して確認する

BetterLogを使えばスプレッドシート上にログを保存できます。

まとめ

サーバをたてず、Google SheetsとApps Scriptだけで中間テーブルをBigQuery上に作る方法を紹介しました。
ミッションクリティカルなテーブルを作る以外で分析するだけだったら手軽に安定したシステムがほぼ無料でできるのでおすすめです。

もっと改善できるなどのツッコミはどしどしお待ちしております。

BigQueryでクエリのコストと実行時間を節約！ - Apps ScriptでBigQueryのテーブルからBigQuery上に中間テーブルを作成する

手順