BigQueryのコスト可視化ダッシュボードをGoogle Apps Script/Google Sheets/Google Sitesを使ってお手軽に作る

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

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

TL;DR

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

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

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

「課金警察」の導入

その対策の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はグラフを埋め込んでいるだけになります。

この「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

ちなみに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で書きます。

次に「current」シートを作り、INDIRECT 関数で対象月のデータを読み込んできます。(月ごとにスプレッドシートを作成してデータを書き込んでいます) INDIRECT 関数は初めて使ったのですが、文字列をevalしてくれるような関数で便利でした。

INDIRECT - ドキュメント エディタ ヘルプ

読み込んできたら、「current」シートでグラフを作成します。 こうすることによって、「state」シートの対象月を書き換えるだけで、データ及びグラフが対象月のデータに切り替わります。

あとは、Google Sitesのグラフ埋め込み機能を使うだけです。

これで月ごとに自動で切り替わるダッシュボードができました。めでたしめでたしです！

解決/まとめ/補足

Google Apps Script/Google Sheets/Google Sitesを使って、アカウントごと/日付ごとのBigQueryコストを可視化できるダッシュボードを作成できました。

可視化してわかったこと

社内で公開してみたところ、課金警察だけではわからなかった傾向や、利用料の多いアカウントが判明しました。また、アカウント管理の面でも、割とざっくりとアカウントを分けていたので、今後SRE中心に整理していこう、ということになり、コスト削減の流れに貢献することができて良かったです！

ちなみにプロトタイプはPythonで作りました。

最初はJupyter＆Matplotlibでグラフをさっと作ってみて完成形のイメージを固めました。プロトタイプ -> 本番プロダクト作成、という流れは定番ですが、今回も完成形のイメージに関しては手戻りがなかったのでとても有用だな、と改めて感じました。

反省

最初、お手軽に作ろうと考えていたのですが、作ってみたら意外と考えることが多くて、あんまりお手軽じゃなくなってしまったな、と思っています。笑 この記事でも全ての手順を紹介しきれなかったので、もし不明点等ありましたら、Twitterなどで質問していただければ幸いですmm

最後に

Kaizen Platformは、今回のような自由研究的なことにもどんどん挑戦させてもらえる面白い環境なので、またどんどん挑戦していきたいと思ってます。そんな面白い環境で働いてみたい方は採用ページを是非チェックしてみてください！ではでは。

Kaizen Platform採用サイト