BigQueryのクエリ課金額をslack通知する - Gunosyデータ分析ブログ

f:id:y-abe-hep:20171115200343p:plain:w300

はじめに

こんにちは、データ分析部の阿部です。

Gunosyには社内警察と呼ばれる人がおり、たとえばデータ可視化の際に円グラフを使うと正しい使い方を教えてくれる、母数という言葉の使い方を正してくれる、方々がいます。

tech.gunosy.io

今回はBigQueryで課金額の高いクエリを投げると警告してくれる、課金警察というボットを作ったので紹介します。 BigQueryはクエリで使われるデータ量に対して従量的に課金されるため、クエリ毎の課金額が把握できると便利です。 Gunosyではエンジニア・非エンジニア問わず、インターン生でも自由にクエリを書いて分析できる環境となっているため、知らず知らずのうちに大胆なクエリが投げられることもあります。そのため、課金警察でクエリ毎の課金額をSlackに通知しお互いに監視して注意しようという意図です。

f:id:y-abe-hep:20171116142308p:plain

どうやってやるか

BigQueryにはクエリの情報を取得するAPIが用意されているので、Google Apps Scriptでそれを呼び出して使います。

https://developers.google.com/apps-script/advanced/bigquery

このJobs: list というAPI

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/list

のレスポンスで、totalBytesProcessedというのがあるので、これにBigQueryのクエリ課金額($5/TB)を掛けることで算出します。

https://cloud.google.com/bigquery/pricing

タイマートリガー（上にあるツールバーのタイマーアイコンをクリック）で10分ごとにスクリプトが動くようにして、データ使用量が一定以上のクエリをslackで通知します。

f:id:y-abe-hep:20171115200318p:plain

BigQueryのAPIにアクセスするためには、GCPのプロジェクトに権限を与える必要があります。ツールバーの Resources から Advanced Google Services を選択し、BigQuery APIをONにします。

Google Apps Script

こんな感じのスクリプトを書きます。

function listJobs(projectId, threMinutes, threDataSize) {
  var jobIds = [];
  
  var joblist = BigQuery.Jobs.list(
    projectId,
    {
      'maxResults': 100,
      'allUsers': true
    }
  );
  
  for (var i = 0; i < joblist['jobs'].length; i++) {
    if (!joblist['jobs'][i]['statistics']['query']) {
      continue;
    }
    
    // threMinutes 分以上前のジョブは検知しない
    var now = new Date();
    if (now.getTime() - parseInt(joblist['jobs'][i]['statistics']['startTime'], 10) > threMinutes * 60 * 1000) {
      continue;
    }
    
    // threDataSize GB 以下は検知しない
    if (joblist['jobs'][i]['statistics']['totalBytesProcessed'] / 1000000000 < threDataSize) {
      continue;
    }
    
    jobIds.push(joblist['jobs'][i]['id'].split(':')[1]);
  }
  
  return jobIds;
}

function fetchJobInfo(projectId, jobid) {
  var job = BigQuery.Jobs.get(projectId, jobid);
  var price = parseInt(job['statistics']['totalBytesProcessed'], 10) / 1000000000000 * 5  // 5ドル/TB
  var start_time = new Date(parseInt(job['statistics']['startTime'], 10));
  var query = job['configuration']['query']['query'];
  var user_email = job['user_email'];
  
  return {
    'job_id': jobid,
    'user_email': user_email,
    'price': price,
    'start_time': start_time,
    'query': query
  }
}

function notify(webhook_url, channel, username, icon_emoji, jobInfo) {
  var message = '```';
  message += 'jobid = ' + jobInfo['job_id'] + '\n';
  message += 'user_email = ' + jobInfo['user_email'] + '\n';
  message += 'start_time = ' + jobInfo['start_time'] + '\n';
  message += 'estimated cost = $' + jobInfo['price'] + '\n';
  message += 'query:\n';
  message += jobInfo['query'];
  message += '```\n';
  
  var payload = {
    'channel': channel,
    'username': username,
    'icon_emoji': icon_emoji,
    'text': message
  };
  
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };
  
  UrlFetchApp.fetch(webhook_url, options);
}

function run() {
  projectId = '<GCP Project ID>';
  var jobList = listJobs(projectId, 10, 100.0);
  
  for (var i = 0; i < jobList.length; i++) {
    var jobInfo = fetchJobInfo(projectId, jobList[i]);
    notify(
      '<slack webhook url>',
      '#hogehoge',
      '課金警察',
      ':money_with_wings:',
      jobInfo
    );
  }
}