4月からKaizen Platformにジョインしたアプリケーションエンジニアのikedayu@つくばです。業務ではデータ解析や解析基盤構築を主に行っています。

Kaizen Platformでは、許可を求めるな、謝罪せよの文化が深く浸透していて、エンジニア一人ひとりの裁量が大きいのが特徴です。自分は、その裁量でしばしば自由研究的に身近な問題に挑戦していて、今回は、その1つについて紹介させてもらいます。

TL;DR

↓のようなBigQueryコストを可視化するダッシュボードを作ります
「日付ごとのコストの推移」と「IAMユーザーごとのコスト」を可視化することでコスト削減の方策が立てやすくなります
G Suiteだけで完結するので、(G Suiteを使っている企業であれば) すぐに導入可能です

f:id:kaizenplatform:20180816121446p:plain

課題: BigQueryのコストはざっくりとしかわからない

Kaizen Platformでは他のたくさんの会社と同じように様々な用途でBigQueryを使っています。サービスで利用したり、ChartioなどのBIツールや、自分のようなデータ解析者が直接BigQueryを叩くなど、エンジニア/非エンジニア、人類/非人類に関わらず使用方法は多岐に渡ります。こういった使い方をしていて、会社の規模が大きくなってくると、当然「あれ、今月のBigQuery料金高すぎ…？」という状態になったりします。しかし、GCPのコンソールだと誰が各日でどのくらい使っているのかがわからない、という課題がありました。

f:id:kaizenplatform:20180816163504p:plain

「課金警察」の導入

その対策の1つとしてKaizen Platformでは↓のGunosyさんがやっているような課金警察を導入しており、高コストなQueryは検知することができていました。 data.gunosy.io

ただ課金警察は威力のあるワンパンチを検知することには向いているのですが、毎日コツコツ積み重なる後から効いてくるジャブを検知するのは難しいという課題がありました。

「費用管理」機能の前に

GCPもアカウントごとにコストを管理できる機能を提供しているのですが、アカウントごとに設定するのが大変＆どの程度の閾値を設定すべきか検討をつけるのが難しいという問題があります。

費用管理 | BigQuery | Google Cloud

ということで、とりあえず可視化してみることにしました。

G Suiteを使おう

可視化にあたって色々な選択肢がありましたが、今回は自由研究的な性質が強かったので、予算的な面や運用的な面を考えなくて良いG Suiteを選択しました。(ちなみにKaizen Platform的には前述したように裁量が大きいので「サーバー立てて運用したいです」という話でも余裕でOKだと思いますが、今回は単純に自分が考えたくなかった、という話です。笑)

構成はこんな感じです。コア部分はGoogle Apps ScriptとGoogle Sheetsで、Google Sitesはグラフを埋め込んでいるだけになります。

f:id:kaizenplatform:20180816163433p:plain

この「1. BQ(BigQuery) APIでJob取得」「2. SS(Spreadsheet) APIで書き出し」「3.グラフを作成/埋め込み」を簡単に解説させていただきます。

[Google Apps Script] BigQuery APIでJob取得

Google Apps ScriptにはBuilt-inでBigQuery APIにアクセスするObjectが用意されているので、認証を全く考えることなくAPIに楽々アクセスできます。 BigQuery Service | Apps Script | Google Developers

APIアクセス部分はこんな感じで書いています。

utility.getJobsFromBqApi = function(maxResults, minCreationTime, nextPageToken){
  maxResults = maxResults || 1000
  minCreationTime = minCreationTime || null
  nextPageToken = nextPageToken || null

  const projectId = 'myProjectId'
  var options = {
   'maxResults': maxResults,
   'stateFilter': 'done',
   'projection': 'full',
   'allUsers': true,
   'minCreationTime': minCreationTime,
   'pageToken': nextPageToken
  }
  
  const response = BigQuery.Jobs.list(projectId, options)

  return response
}

注意点: 一度にJobを全て取得/処理できない

Google Apps Scriptは一度に実行できるJobの実行時間の上限が5分になっています。このためBigQueryの使い方にもよりますが、サービスやBIツールでの利用がある場合は一度にできないケースが多いと思います。このため、データ管理用のスプレッドシートに state シートを作成し、下記をそれぞれ保存しておく作りになっています。

tmpLatestTime: 全件取得できなかったときに次回 minCreationTime パラメータとして使うUnixTimestamp
nextPagetoken: 全件取得できなかったときに次回 pageToken パラメータとして使うtoken

f:id:kaizenplatform:20180816133417p:plain

ちなみにlatestTimeは取得したデータの最新日時(UnixTimestamp)を保存しています。

claspでコード管理

今まではGoogle Apps Scriptをコード管理するのはGoogleDrive上で行わなければならず、Google Apps Script APIを使っていい感じにしてくれるthird party libraryはあったのですが、今年、公式CLIの clasp が発表されたので最近書くGoogle Apps Scriptコードは全てこれで管理してます。動作が不安定な時もありますが、継続的に開発がされていてどんどん修正されていますし、大体満足して使ってます。

github.com

[Google Apps Script] スプレッドシートにデータ書き出し

BigQuery APIから取得したJobデータをスプレッドシートに書き出します。要所要所の実装だけ切り出すとこんな感じになります。

var SheetManager = function(){
  this.thisMonth = Utilities.formatDate(new Date(), "UTC", "YYYYMM")
  this.spreadSheet = SpreadsheetApp.openById(SSID)
  this.priceSheet = this.spreadSheet.getSheetByName(this.thisMonth)
}

SheetManager.prototype.setPrices = function(prices){
  priceArr = this.convertPricesToArr(prices)
  this.priceSheet.getRange(2, 1, priceArr.length, priceArr[0].length).setValues(priceArr);
}

SheetManager.prototype.convertPricesToArr = function (prices){
  var priceArr = []

  Object.keys(prices).forEach(function(date) {
    var emailPrice = Object.keys(prices[date]).forEach(function(email) {
      priceArr.push([date, email, prices[date][email]]);
    });
  });
  // Sortしてから出力
  priceArr.sort(utility.sortFunction)
  return priceArr
}

注意点: スプレッドシートのセルの上限は40000

最初は取得したJob情報をスプレッドシートに書き出していたのですが、すぐにエラーが発生し始めました。これはスプレッドシートのセルの数に40000という上限があったためです。このため取得の度にアグリゲーションを行っています。コードを抜粋して紹介すると下のような感じです。アグリゲーションに関してはObjectを使った愚直な実装をしています。またGoogle SheetsとGoogle Apps ScriptでTimeZoneが違うので Utilities.formatDate を使うところもポイントです。

var CostManager = function(){
  this.sheetManager = new SheetManager()
}

CostManager.prototype.getCurrentPrices = function(){
  this.prices = this.sheetManager.getCurrentPrices()
}

CostManager.prototype.updateJobs = function(){
  var that = this
  this.jobs.forEach(function(job){
    const stats = job['statistics']
    const totalBytes = stats['query'] ? stats['query']['totalBytesBilled'] : 0
    const timeStamp = parseInt(stats['creationTime'])
    const date = Utilities.formatDate(new Date(timeStamp + 1000*60*60*13), "UTC", "YYYY-MM-DD")
    const teraBytes = parseInt(totalBytes, 10)/Math.pow(1000, 4)
    const price = teraBytes * 5
    const email = job['user_email']
    
    // もし金額が発生していればpricesをupdate
    if(price > 0){
      if(!that.prices[date]) { that.prices[date] = {} }
      if(!that.prices[date][email]){ that.prices[date][email] = 0 }
      that.prices[date][email] += price
    }
  })
}

SheetManager.prototype.getCurrentPrices = function(){
  data = this.priceSheet.getDataRange().getValues().slice(1)
  const prices = {}
  data.forEach(function(d){
    var date = Utilities.formatDate(d[0], "JST", "YYYY-MM-DD")
    var email = d[1]
    var price = d[2]

    if(!prices[date]) {prices[date] = {}}
    prices[date][email] = price
  })

  return prices
}

これで無事Jobデータを書き出すことが出来ました。

[Google Sheets/Google Sites] グラフを作成/埋め込み

もうここまで来れば、あとはGoogle SheetsとGoogle Sitesをいじっていくのみになります。ダッシュボードに表示させるデータはある程度絞りたいので今回は当月分の表示をさせることにします。最初は月ごとにGoogle Sitesのグラフを切り替える方法について悩んだのですが、これが一番お手軽かなと思っています。まず先ほど紹介した「state」シートに対象月(今月)を入れるようGoogle Apps Scriptで書きます。

f:id:kaizenplatform:20180816142240p:plain