Gunosyデータ分析ブログ

Gunosyで働くデータエンジニアが知見を共有するブログです。

BigQueryのクエリ課金額をslack通知する

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
    );
  }
}

Tierに関しては無視してしまいました。 APIレスポンスの中に、billingTierというのがあるのでこれを使えばより正確な課金額を知ることができるでしょう。

悩み

Google Apps Script はGDriveで手軽に書けるので便利ですが、会社の社員アカウントにスクリプトが紐付けされてしまいます。 人に紐付かない形で管理できればいいですが、どうすればよいのでしょう。

おわりに

Gunosyでは課金額を通知して可視化することで必要以上にコストかからないようにしています。 こうすることで安心してクエリを投げることができ、快適に分析が進められるのではないでしょうか。