シェルの結果をJSONで出力 - awkを使いこなしたい -

こんにちは!インフラの奥村です!

インフラエンジニア歴1年半になりました。
日々の業務にも慣れだし、「まぁまぁこなせてるんじゃないか?」と思ってしまうまでになりました。

とは思いつつも
いざ、障害、トラブルなどが発生したときに迅速に対処できるかと言われるとまだまだです。
入社当初から先輩社員に言われている

「まずログを見よう」

「まずログを見る」という行為 はしているものの、必要なログを抜き出す能力が明らかに足りていません。
ワンライナーのシェルコマンドを実行し、必要な情報を抜き出しているエンジニア見るたびに「すげぇ」としか思えていません。

そこで!そういった能力を身につけるために「awk」の勉強をはじめました!

今回は勉強の一環として「awk」を使ったシェルスクリプトを作成しました。

スクリプト

標準入力から値を受け取りそれをJSON形式で出力するスクリプトです。

  • loj.sh
#!/bin/sh
usage() {
  echo 'outjson.sh [-k] key colum number [-c] "1,colum name 2,columm name" [-e] Except from top 0..*  [-s] <,> <:> default Value is space'
}
EXCEPT=1
while getopts k:c:e:s: OPT
do
  case $OPT in
    k ) KEY=$OPTARG ;;
    c ) COLUMM_TEXT=$OPTARG ;;
    e ) EXCEPT=$OPTARG ;;
    s ) SEP="-F ${OPTARG}" ;;
    * ) usage
          exit 1 ;;
  esac
done

if [ -p /dev/stdin ] ; then
    a=$(cat -)
    echo "$a" |
    tail -n +$EXCEPT |
    awk $SEP -v key=${KEY} -v colum_text="${COLUMM_TEXT}" ' \
    BEGIN {
      split(colum_text, colum, " ")
      for (i in colum ) {
         split(colum[i], sp_colum, ",")
         colums_hash[sp_colum[1]]=sp_colum[2]
      }
      print "{"
    }
    {
       printf("\"%s\": {",$key)
       for(i in colums_hash ) {
         printf("\"%s\": \"%s\",",colums_hash[i], $i);
       }
       printf( "},")
    }
    END{
       print "}"
    }' |
    sed -e 's/,}/}/g'

else
    echo "need STDIN"
    usage
fi

説明

全プロセスからsystemdのプロセスを探す例で説明します。

パイプで値を渡し、必要な引数を与えます

ps aux | grep systemd | ./loj.sh -k 2 -c "2,PID 11,CMD" -e 2
  • -k
    • JSONのキーにしたいもの。標準出力されたものの列番号を入れる(数字)-k 2
  • -c
    • JSONの値として入れたいもの。標準出力されたものの列番号とキー名をダブルクウォートで囲って入れる。 -c "2,PID 11,CMD"
  • -e
    • 一番最初の行から無視する行数を入れる。 -e 2

として出力された結果がこちらです。

{
"319": {"CMD": "/lib/systemd/systemd-udevd","PID": "319"},"807": {"CMD": "/usr/bin/dbus-daemon","PID": "807"},"853": {"CMD": "/lib/systemd/systemd-logind","PID": "853"},"13703": {"CMD": "/lib/systemd/systemd","PID": "13703"},"22499": {"CMD": "grep","PID": "22499"}}

これをjqに渡してるみると

{
  "319": {
    "CMD": "/lib/systemd/systemd-udevd",
    "PID": "319"
  },
  "807": {
    "CMD": "/usr/bin/dbus-daemon",
    "PID": "807"
  },
  "853": {
    "CMD": "/lib/systemd/systemd-logind",
    "PID": "853"
  },
  "13703": {
    "CMD": "/lib/systemd/systemd",
    "PID": "13703"
  },
  "22489": {
    "CMD": "grep",
    "PID": "22489"
  }
}

こんな感じで出力されます。

lsのパターン

  • 通常の出力
$ ls -la
合計 8
drwxrwxr-x 2 vagrant vagrant 4096 1116 17:35 2017 .
drwxrwxr-x 3 vagrant vagrant 4096 1116 17:35 2017 ..
-rw-rw-r-- 1 vagrant vagrant    0 1116 17:35 2017 file1
-rw-rw-r-- 1 okumura okumura    0 1116 17:35 2017 file2
-rwx------ 1 vagrant vagrant    0 1116 17:35 2017 file3
ls -la | loj -k 10 -c "1,PERMISSION 3,USER 4,GROUP 5,SIZE 6,MONTH 7,DAY 8,TIME, 9,YEAR" -e 4 | jq

出力結果

{
  "file1": {
    "GROUP": "vagrant",
    "SIZE": "63",
    "MONTH": "11月",
    "DAY": "16",
    "TIME": "17:41",
    "YEAR": "2017",
    "PERMISSION": "-rw-rw-r--",
    "USER": "vagrant"
  },
  "file2": {
    "GROUP": "okumura",
    "SIZE": "0",
    "MONTH": "11月",
    "DAY": "16",
    "TIME": "17:35",
    "YEAR": "2017",
    "PERMISSION": "-rw-rw-r--",
    "USER": "okumura"
  },
  "file3": {
    "GROUP": "vagrant",
    "SIZE": "223",
    "MONTH": "11月",
    "DAY": "16",
    "TIME": "17:42",
    "YEAR": "2017",
    "PERMISSION": "-rwx------",
    "USER": "vagrant"
  }
}

行と列がある出力ならJSON形式で出力でます!

まとめ

このスクリプト作成を通して「awk」、「シェル」の奥深さを知りました。

加えて、自分が入り口にすら立っていなかったことを思い知らされました。

今後も日々鍛錬を重ね、自分の頭とシェルが繋がっているかのようなスピードでワンライナーを生み出す

一流のシェル芸人になりたいです。

ScalaのMapを使って2つのサマリーテーブルの結果を結合する

こんにちは。エンジニアのまっちゃんです!

現在は広告サービスのレポート機能に携わっています。

その中で2つのサマリーテーブルから集計したい場面が出てきたのですが、

自分だけの力では解決できず、チーフとペアプロを行って解決できたのでそれについて書いていきます。

想定の仕様

※ 仕様については置き換えて書かせていただきます。

仕様としては下記のようなテーブルがあるとします。 f:id:AdwaysEngineerBlog:20171109154949p:plain

summary_guestテーブルでは、イベント、チケットごとのゲスト人数を集計してます。 summary_action_guestテーブルでは、イベント、チケット、アクションごとのアクションを起こしてくれたゲスト人数を集計してます。

  • イベントは具体的なイベント名
  • チケットはチケット区別(前売り券、当日券など)
  • アクションは行動(アンケート記入、本購入など)

を想定してます。

この2つのテーブル結果をScalaのMap経由で1つのシーケンスへと結合します。

※ このコードはIntelliJのScala WorkSheetで実行確認ができます。

前準備1: Entity を作成

テーブルと結果を表すEntityをcase classで作成します。

// summary_guest
case class SummaryGuestEntity(
  eventId: Option[Int]    = None,
  ticketId: Option[Int]   = None,
  guestCount: Option[Int] = None
)

// summary_action_guest
case class SummaryActionGuestEntity(
  eventId: Option[Int]          = None,
  ticketId: Option[Int]         = None,
  actionId: Option[Int]         = None,
  actionGuestCount: Option[Int] = None
)

// 結果 Entity
case class SummaryEntity(
  eventId: Option[Int]          = None,
  ticketId: Option[Int]         = None,
  actionId: Option[Int]         = None,
  guestCount: Option[Int]       = None,
  actionGuestCount: Option[Int] = None
)

前準備2: 共通Key を設定

case class SummaryKey(
  eventId:  Option[Int] = Some(0),
  ticketId: Option[Int] = Some(0)
)

前準備3: 取得結果イメージを作成

本来はDBの結果をシーケンスで取得しますが、 今回は仮データで作成します

// summary_guest からの取得結果イメージ
val guestResults = Seq(
  SummaryGuestEntity(
    eventId    = Some(1),
    ticketId   = Some(1),
    guestCount = Some(10)
  ),
  SummaryGuestEntity(
    eventId    = Some(1),
    ticketId   = Some(2),
    guestCount = Some(20)
  ),
  SummaryGuestEntity(
    eventId    = Some(1),
    ticketId   = Some(3),
    guestCount = Some(30)
  ),
  SummaryGuestEntity(
    eventId    = Some(2),
    ticketId   = Some(1),
    guestCount = Some(5)
  )
)

// summary_action_guest の取得結果イメージ
val actionGuestResults = Seq(
  SummaryActionGuestEntity(
    eventId          = Some(1),
    ticketId         = Some(1),
    actionId         = Some(1),
    actionGuestCount = Some(2)
  ),
  SummaryActionGuestEntity(
    eventId          = Some(1),
    ticketId         = Some(2),
    actionId         = Some(1),
    actionGuestCount = Some(4)
  ),
  SummaryActionGuestEntity(
    eventId          = Some(2),
    ticketId         = Some(1),
    actionId         = Some(1),
    actionGuestCount = Some(2)
  ),
  SummaryActionGuestEntity(
    eventId          = Some(2),
    ticketId         = Some(2),
    actionId         = Some(2),
    actionGuestCount = Some(1)
  )
)

処理1: 取得結果を Map にする

DBから取得した結果をMapにします。

// guest の結果を Map にする
val guestMap = guestResults.map { guest =>
  (SummaryKey(
    eventId  = guest.eventId,
    ticketId = guest.ticketId
  ), guest)
}.toMap

// actionGuest の結果を Map にする
val actionGuestMap = actionGuestResults.map { actionGuest =>
  (SummaryKey(
    eventId  = actionGuest.eventId,
    ticketId = actionGuest.ticketId
  ), actionGuest)
}.toMap

処理2: Map の結合処理メソッドを作成

SummaryKeyを見て、データを結合します。

def combine(guestMap: Map[SummaryKey, SummaryGuestEntity], actionGuestMap: Map[SummaryKey, SummaryActionGuestEntity]): Map[SummaryKey, (Option[SummaryGuestEntity], Option[SummaryActionGuestEntity])] = {
  // 重複排除した guest の key
  val guestKey = Set(guestMap.keysIterator.toList: _*)
  // 重複排除した actionGuest の key
  val actionGuestKey = Set(actionGuestMap.keysIterator.toList: _*)
  // guest と actionGuest に存在している key
  val intersection = guestKey & actionGuestKey

  // guest actionGuest 両方の key に存在するデータ
  val bothData =
    intersection.map { keyName =>
      (keyName, (Some(guestMap(keyName)), Some(actionGuestMap(keyName))))
    }.toMap
  // guest の key にしか存在しないデータ
  val guestData = guestMap.filterKeys(!intersection.contains(_)).map{ case (key, guestEntity) =>
    (key, (Some(guestEntity), None))
  }
  // actionGuest の key にしか存在しないデータ
  val actionGuestData = actionGuestMap.filterKeys(!intersection.contains(_)).map{ case (key, actionGuestEntity) =>
    (key, (None, Some(actionGuestEntity)))
  }
  
  bothData ++ guestData ++ actionGuestData
}

処理3: 結果Entityを生成するメソッドを作成

guest、actionGuest 両方のデータがあれば結合した結果を返します。 片方しかない場合は片方のデータのみを返します。

def makeSummaryEntity(maybeGuestEntity: Option[SummaryGuestEntity], maybeActionGuestEntity: Option[SummaryActionGuestEntity]): Option[SummaryEntity] = {
  // guest actionGuest ともに結果がない場合は None を返す
  if (maybeGuestEntity.isEmpty && maybeActionGuestEntity.isEmpty) return None

  val guestSummaryEntity = maybeGuestEntity.flatMap{ guest =>
    Some(
      SummaryEntity(
        // guest actionGuest で共通な要素
        eventId    = guest.eventId,
        ticketId   = guest.ticketId,
        // guest のみに存在している要素
        guestCount = guest.guestCount
      )
    )
  }.getOrElse(SummaryEntity())

  Some(
    maybeActionGuestEntity.flatMap { actionGuest =>
      Some(
        guestSummaryEntity.copy(
          // guest actionGuest で共通な要素
          eventId = actionGuest.eventId,
          ticketId = actionGuest.ticketId,
          // actionGuest のみに存在している要素
          actionId = actionGuest.actionId,
          actionGuestCount = actionGuest.actionGuestCount
        )
      )
    }.getOrElse(guestSummaryEntity)
  )
}

処理4: 作成したメソッドを実行

Mapにした取得結果と作成したメソッドを使います。

combine(guestMap, actionGuestMap).flatMap { case (key, data) =>
  makeSummaryEntity(data._1, data._2)
}.toSeq

これで期待通り、1つのシーケンスで返ってきます。

以下が出力結果です。

List(
  SummaryEntity(
    Some(1),  // eventId
    Some(1),  // ticketId
    Some(1),  // actionId
    Some(10), // guestCount
    Some(2)   // actionGuestCount
  ),
  SummaryEntity(
    Some(2),  // eventId
    Some(2),  // ticketId
    Some(2),  // actionId
    None,     // guestCount
    Some(1)   // actionGuestCount
  ),
  SummaryEntity(
    Some(2),  // eventId
    Some(1),  // ticketId
    Some(2),  // actionId
    Some(5),  // guestCount
    Some(1)   // actionGuestCount
  ),
  SummaryEntity(
    Some(1),  // eventId
    Some(3),  // ticketId
    None,     // actionId
    Some(30), // guestCount
    None      // actionGuestCount
  ),
  SummaryEntity(
    Some(1),  // eventId
    Some(2),  // ticketId
    Some(1),  // actionId
    Some(20), // guestCount
    Some(4)   // actionGuestCount
  )
)

まとめ

自分一人では行き詰まり期待通りの実装ができませんでしたが、

チーフとのペアプロを行う事により、仕様通りの動きを書くことができました。

またペアプロでは新たな気付きもあったので、タイミングがあえばチーム内でペアプロをして行きたいです。

slackでサーバーレスにプログラムを実行~slack-lambda~

こんにちは、久保田です。

最近よくslackにRTMBotを作り、色んな発言をきっかけに動くBotを作り業務を改善しています。
例えば、

  • IPSより攻撃の報告があった場合にチケットページにアクセスし情報をまとめる
  • ファン故障などの障害があった際に対象の仮想基盤に乗っている仮想マシンの一覧を出力する

などです。

こんな事をしていると、slackの可能性には驚かされます。
そしてある日、ふと思いました。

slackでプログラムが動かせたら面白くない?

と。

botは基本的にslack上で起こる何かに反応して動きますし、その先にプログラムがあるだけならば、
いっその事slackでプログラムが実行できたら色々と夢が広がる気がします。

例えば

  • チームのメンバーがいるチャンネルで簡単な検証プログラムを動かす
  • 障害時に社外から、スマホでプログラムが動かせる
  • slackのreminder機能を使って、登録しておいたプログラムを動かす

などなどです。

というわけで今回はslack上でサーバーレスでプログラムを実行できる環境を作ったお話、「slack-lambda」のお話です。

動作例

百聞は一見に如かずなので、早速ご覧ください。

rubyでfib

f:id:AdwaysEngineerBlog:20171031184320g:plain

以下、入力値です。


@slack-lambda exec
language: ruby
version: 2.4

def num(n)
  return $n_0 if n == 0
  return $n_1 if n == 1
  num(n-1) + num(n-2)
end

n = 10
$n_0 = 1  #num(0)
$n_1 = 2  #num(1)
puts num(n)

goでbubble sort

f:id:AdwaysEngineerBlog:20171031184110g:plain

以下、入力値です。


@slack-lambda exec
language: go
version: 1.8

package main

import (
    "fmt"
)

func main() {
    arr := []int{6,10,2,1,5}

    fmt.Println(bubbleSort(arr))
}

func bubbleSort(arr []int) []int {
    lastIndex := len(arr) - 1

    for {
        if lastIndex == 0 {
            return arr
        }

        for i := 0; i < lastIndex; i++ {
            a := arr[i]
            b := arr[i+1]
            if a > b {
                arr[i] = b
                arr[i+1] = a
            }
        }

        lastIndex--
    }

}

システム構成

システムの構成は以下の様になっています。

f:id:AdwaysEngineerBlog:20171031185042p:plain

dockerをメインで使うようにしており、その後ろにgoでできたサーバーが2台存在しています。

proxy-serverの方はリクエストとして投稿された文字列を受け取り、jsonに変換します。
そしてjsonを後ろのlambda-serverに送ります。

lambda-serverがjsonを受け取り、渡されたパラメータに沿ってdockerのコンテナを立ち上げ、プログラムを実行します。
そしてその標準出力を返し、proxy-serverがまた送信元に返す、という流れです。

全てGoでできており、lambda-serverの処理はゴルーチンにより並列化がされているので、多くのリクエストを捌ける(はず)です。 今回は並列化のため、Go並列化パターンのpipelineを使用しました。(https://blog.golang.org/pipelines)

github.com

機能

機能は、以下のものがあります。

  • プログラムの実行(インライン)
  • プログラムの登録
  • 登録されたプログラムの実行
  • 登録されたプログラムの削除
  • 登録されたプログラムの一覧表示

まとめ

プログラムって便利だなって思いました。

今はgoとrubyしかサポートしていませんが、色々やりたいと思います。

Mackerelを使ってeject自動化

こんにちは!
2017年新卒インフラの戸田です!
日々、素敵な先輩方の下、インフラ業務を学んでおります。

今までの2017新卒はまじめなことをしていますが、
今回僕はmackerelを使ってちょっと遊んでみたいと思います。
やることはタイトルに書いてある通りです。

ejectの自動化をする意味があるのかどうかは、気にしないでください。
(そもそもejectって何だ?って人はググってもらえると助かります)

使うもの

  • エアコンのリモコン
  • mackerel
  • DVDROM
  • slack
  • raspberry pi (温度をとるため回路も使います)

どれも家にある身近なもの(?)ばかりですね。これらを使っていきたいと思います。

やってみる的な文言

部屋の温度を取得する

  • temperature.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import glob
from time import sleep

os.system('modprobe w1-gpio')
os.system('modprobe w1-therm')

base_dir = '/sys/bus/w1/devices/'
device_folder = glob.glob(base_dir + '28*')[0]
device_file = device_folder + '/w1_slave'

def read_temp_raw():
    f = open(device_file, 'r')
    lines = f.readlines()
    f.close()
    return lines

def read_temp():
    lines = read_temp_raw()
    while lines[0].strip()[-3:] != 'YES':
        sleep(0.2)
        lines = read_temp_raw()
    equals_pos = lines[1].find('t=')
    if equals_pos != -1:
        temp_string = lines[1][equals_pos + 2:]
        temp_c = float(temp_string) / 1000.0
        return temp_c

try:
    while True:
        t = str(read_temp())
        f = open('room_log','w')
        f.write(t + '\n')
        f.close()
        sleep(30)

except KeyboardInterrupt:
    pass

30秒間に一回部屋の温度を取得し、room_logファイルに書き込みます。

Mackerlで温度のグラフを表示

次に、mackerelを使うために、mackerel-agentを使いますが、導入方法は割愛させていただきます。 mackerelにデータを送りますが、こんな感じです。

  • temper-agent.sh
#!/bin/bash
SECONDS=`date '+%s'`

NAME='room.temp'
VALUE=`cat room_log`

echo -e "${NAME}\t${VALUE}\t${SECONDS}"

先ほどのroom_logファイルをmackerelに送っています。

簡単ですね! これだけでグラフが表示されるなんて夢みたいです。

こうゆう具合で表示されます。

f:id:AdwaysEngineerBlog:20171025163452p:plain

今回は通知の設定を - WARNING 24度以下 - CRITICAL 23度以下

これでslackに通知される設定を加えれば、通知されます。

botを使ってコマンドを実行

後はejectをする環境さえできれば完成です。というわけで、肝心のDVDROMを動かすコマンドですが

  • eject.sh
#!/bin/sh
eject -T
eject -t

はい、これだけです。-Tで出して、-tで戻ります。

あとは、slackでAircon_ejectという文字列があればコマンドを実行するhubotを作って完成です。

  • aircon.coffee
module.exports = (robot) ->
  robot.catchAll (msg) ->
    r = new RegExp "(.*)(Aircon_eject)(.*)", "i"
    matches = msg.message.text.match(r)
    if matches == null or matches.length == 0
      return
    @exec = require('child_process').exec
    command = "sh eject.sh"
    msg.send "ejectしました"
    @exec command, (error, stdout, stderr) ->
      msg.send error if error?
      msg.send stdout if stdout?
      msg.send stderr if stderr?

以上で、完成となります。
長々と書いてしまいましたが、こちらが実機です。

f:id:AdwaysEngineerBlog:20171025163533j:plain

どんな角度でとっても写るのはTwitter映えする写真ばかり...
インスタ映えは難しいですね

動いている様子はこんな感じです。

f:id:AdwaysEngineerBlog:20171027174144g:plain

実際にslack上で動いてる画像

f:id:AdwaysEngineerBlog:20171025163558p:plain

こんな具合で簡単に作れちゃいます。

おわりに

10月は新卒が記事を投稿していましたが、私はネタがなかったので、最後にしてもらいました。
なんとかネタとしてはいいものができたのではないかと思っています。
mackerelを初めて触った人でも簡易的なグラフなら簡単に作れます。
自分で定義したものをグラフにできるのはちょっとした特別感がありますね。

これで一人暮らしでも「家に帰っても寒い!!」なんて状況がなくなるわけです!!
快適に冬を過ごせますね!!()

BigQueryの課金額をグラフで見たい!(BigQuery・Data Studio・Stackdriver Logging)

こんにちは!
2017年新卒SEの渡辺です!
社内で扱っている様々なサービスの改善、新技術の取り入れ等を行う部署で日夜勉強しています!!
今回はGoogleのクラウドサービスであるGCP(Google Cloud Platform)の利用法のひとつについて話したいと思います。

目的

GCPには様々なサービスが存在するなか今回はBigQueryの利用状況をイケてる感じに視覚化したいです。
その実現のために

  • Stackdriver LoggingからBigQueryへの連携
  • BigQueryからData Studioへの連携

を行う方法をハンズオン形式で紹介します。

やること

BigQueryの利用状況を見る(特にクエリに関して調べる)ためにはクエリを発行した履歴を見る必要があります。
そこで、BigQueryのクエリジョブのログを収集してそこから情報を引っ張り出します。
それらの情報を自分好みに並べることで情報を一目で確認することができるようになります。

必要なもの

  • GCPのプロジェクト
  • BigQueryの読み込み権限
  • Stackdriver Loggingのシンクを作成する権限

シンクを作成する

BigQueryでのログを取得して保存するにはStackdriver Loggingを使います。
f:id:AdwaysEngineerBlog:20171016112525p:plain Loggingにはログのエクスポート機能が備わっていて、任意の媒体にログを流すことができます。
今回はBigQueryでクエリが実行された際のログを取得し、そのログをBigQueryに送るようにします。

Stackdriver Loggingの画面に遷移したらまず色々なログが垂れ流しの状態になっていると思うので、
フィルターを変えてBigQueryのみのログを出すように設定します。

対象に BigQuery を選択
種類に data_access を選択
その他の項目はデフォルトで問題なし

f:id:AdwaysEngineerBlog:20171016113352p:plain フィルターの設定ができたらそのままエクスポート先の設定を行います。

エクスポートを作成 を選択
シンク名 を好きなように命名 (このエクスポートを行うジョブの名前です)
シンクサービス に BigQuery を選択
シンクのエクスポート先 にエクスポートしたいBigQueryのデータセットを選択 (ここで作成することも可)

f:id:AdwaysEngineerBlog:20171016113315p:plain 入力を終えて シンクを作成 を押すことでシンク作成が完了です。
こうして、BigQueryで実行されたクエリの情報が指定したデータセットの
cloudaudit_googleapis_com_data_access_YYYYMMDD
テーブルに格納されるようになりました。

なお、ログのエクスポートはリアルタイムに行われるので常に最新の情報が貯まっていくことになります。

Data StudioでBigQueryをデータソースとして登録する

ログがBigQuery上に格納されるようになり、あとは視覚化するだけなので様々な方法がありますが、
今回お勧めするのは Google Data Studio
Google Data Studio は様々なデータを用いて容易にレポートを作ることができるBIツールです。
メリットとしては、現在はベータ版で無償利用することができることに加え、
様々なデータソースのサポートがされている(もちろんBigQueryも)ので面倒な手間いらずに導入することができます。
さっそく先ほどエクスポートしたBigQuery情報をデータソースとして利用するための準備を行います。

Data Studioのホーム画面にアクセスしたら、
サイドメニューからデータソースを選択し、右下の+から新しいデータソースを作成します。
f:id:AdwaysEngineerBlog:20171016113510p:plain 新規のデータソース作成画面に移ったらBigQueryのデータを利用するため設定を行っていきます。

左上のデータソース名には自分で分かりやすい名前をつける
コネクタ で BigQuery を選択
方法 に カスタムクエリ を選択
プロジェクト で 先ほどエクスポート先に指定したデータセットのプロジェクト を選択
クエリオプション の 以前のSQLを使用する のチェックボックスを オフ に切り替える

f:id:AdwaysEngineerBlog:20171016113534p:plain カスタムクエリとは、その場でクエリを実行しその結果をデータソースとして用いる方法です。
既にテーブル上に必要なデータが揃っている場合は マイプロジェクト を選択してデータセットを選べばクエリを書く必要はありません。

これが今回実行するカスタムクエリです。

SELECT  
  protopayload_auditlog.authenticationInfo.principalEmail as Email,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes as Bytes,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes * 5 / (2^40) as Cost_In_Dollars,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1024 / 1024 as MB,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query as Query,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime
FROM
  `<MY-GCP-PROJECT>.bigquery_audit_logs.cloudaudit_googleapis_com_data_access_*`
WHERE
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = 'query_job_completed'

上記を入力し、画面右上から接続を選択すればデータソースとして登録できます。

無事にBigQueryとの接続ができればフィールドの編集画面へと遷移することができ、
データソースを利用することができます。

グラフを作る

カスタムクエリによってBigQueryのデータがData Studio上のフィールドに出すことができました。
ここからは、このフィールドを色々な形に変えて利用することになります。

ここで詳しく説明をしてしまうと日が暮れてしまうので、
一例としてカスタマイズしたフィールドとそれによって作成したレポートです。
f:id:AdwaysEngineerBlog:20171016113627p:plain

f:id:AdwaysEngineerBlog:20171016113719p:plain ほらできた!ね、それっぽいでしょ!

日付を持つフィールドを作っておけば期間フィルターを使うことができ、(レポート右上)
任意のフィールドを指定すればそれによるフィルターをつけることもできます。(レポート右)

おわりに

今回のGoogle Cloud Platformをふんだんに利用した便利なレポート作成について紹介しました。
データを視覚化するために利用したData Studio。このサービスの可能性は無限に広がりますよ!
今回の例であれば、クエリにかかるコストを視覚的に知ることができるので、これを見てチューニング対象のクエリを決めよう!ってなるかも?

また、BigQueryの情報だけでなく、様々なデータ・データソースと連携することができるので
自由にレポートを作ることができます。

さらに!データソースの登録は少し複雑な面もありますが、登録ができたあとの操作はエクセル並みに簡単!
Data Studioを使うことで誰でもレポートを作ることができてしまいます。

簡単に導入できて、素早くデータを視覚化。
みなさんも始めてみませんか?