５分でわかる！BigQuery Tips集 - Gunosyデータ分析ブログ

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

今回はBigQueryについてです。

GunosyではもともとRedshiftで運用していましたが、

クエリによっては時間がかかり処理しきれない
同時にクエリを投げると詰まる

などの課題を解決するためにBigQueryを一部で導入しました。今回はBigQuery導入するときのTipsを紹介したいと思います。

Standard SQL
テーブルワイルドカード関数
Partitioned Table
Re:dashに接続する方法
Google Apps Script を使ってクエリ結果をSlackに通知する方法
まとめ

Standard SQL

現時点でベータ版ですが、ついにBigQueryでも標準SQLを使ってクエリを書けるようになりました。これまでの SQL (Legacy SQL) は癖が強く、 distinct を使って重複を取り除くこともできなかったりと不便なところもありました。一方今回導入されたStandard SQLでは標準SQLで書けるので、比較的違和感無く覚えることができます。

Standard SQLを使うには、Webコンソールからは Show Options ボタンを押して Use Legacy SQL のチェックを外すだけです。

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

また、CLIでは --use_legacy_sql=false とオプションを指定します。

詳細はこちら

https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql

テーブルワイルドカード関数

BigQueryはクエリを処理するときに取得したデータ量に応じて課金されます。また、カラムな型のためにいくらlimitしたところで課金額は変わりません。そのため、課金額を減らすためにテーブルを日付単位で分割するというような手法があります。例えば、Gunosyではテーブル名に以下のようにサフィックスを付けています。

table_20160901
table_20160902
table_20160903

こうしておくと、Webコンソール上では、テーブルが自動的にまとまって表示されるので見やすいです。

もし fluent-plugin-bigquery を使ってデータを入れる場合には以下のように設定すると良いでしょう。

    table テーブル名_%Y%m%d
    auto_create_table true

テーブルはワイルドカードで指定することができます。

SELECT ... FROM `データベース.テーブル_*` ...

こうすると table_ ではじまるすべてのテーブルにクエリを投げられます。ただデータ量が増えると課金額が増えるため、使うテーブルを絞りたくなるでしょう。そんなときには

SELECT
  ...
FROM
  `データベース.テーブル_*`
WHERE
  _TABLE_SUFFIX BETWEEN REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 8 day) AS string), '-', '')
  AND REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 1 day) AS string), '-', '')

例えばこんな感じで、8日前から1日前のテーブルを指定することができます。

ちょっとめんどうですね。よく使うときには View を使って保存しておくと良いでしょう。雑に

SELECT
  *
FROM
  `データベース.テーブル_*`
WHERE
  _TABLE_SUFFIX BETWEEN REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 8 day) AS string), '-', '')
  AND REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 1 day) AS string), '-', '')

というクエリをViewにしておくだけで良いでしょう。高そうなクエリですが、このViewを使うときのカラム分のデータだけが取得されるので大丈夫です。

Partitioned Table

テーブルを分けるのが面倒というときには Partitioned Table という選択肢もあります。 Partitioned Table を使うときは、テーブルを作るときにbqコマンドで --time_partitioning_type=DAY というオプションを指定します。

$ bq mk --time_partitioning_type=DAY mydataset.table1

Partitioned Table を利用すると、日付を指定してクエリを投げるには、 mydatabase.table1$20161001 のようにします。また、

SELECT
  field1
FROM
  mydataset:partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(“2016-05-01”)
  AND TIMESTAMP(“2016-05-06”)

とするとシンプルなクエリで期間を絞ることができます。

ただし、Gunosyではこの機能を使うことは見送っています。それは

無意識にテーブルの全期間にクエリを投げてしまいそうで、課金額が増えそう
fluentdのプラグインが対応していなかった

という理由からで、少々めんどうでもワイルドカードを使ってクエリを書くようにしました。

Re:dashに接続する方法

BigQueryはRe:dashから使うこともできます。(関連記事: 【Slack×Re:dash】リアルタイムKPI通知をコード0行で実現する - Gunosyデータ分析ブログ) BigQueryを使うにはデータソースを追加するところで、BigQueryを選択します。

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

jsonキーはGCPのWebコンソールで然るべきService Accountを作って、赤丸で囲んだところをクリックするとダウンロードできます。

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

データソース追加選択画面の下の方に Use Standard SQL というチェックボックスがあり、これをチェックすることで標準SQLを使ってクエリを書けるようになります。この機能はRe:dashの右下に出ている丸いアイコンをクリックしてRe:dashの開発者にリクエストして一日待たずに機能を追加してもらえました。

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

Google Apps Script を使ってクエリ結果をSlackに通知する方法

BigQueryはGoogle Apps Script (以下GAS) を使ってアクセスすることもできます。 GunosyではGASからBigQueryのクエリを実行し、incoming webhooks を使ってSlackにKPIはテーブル異常の通知させています。

GASからBigQueryを使うにはまずResourcesメニューからAdvanced Google Servicesを選び、BigQueryのAPIを有効化します。

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

その後ダイアログの下の方のリンクをクリックして、Google Developers ConsoleからもAPIを有効化します。

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

こんな感じのコードを書いて runQuery を実行するとBigQueryのクエリが実行され、Slackに結果が通知されるはずです。なお、この記事を書いた時点では標準SQLはまだ対応していないようでした。

function runQuery() {
  var request = {
    query: "ここにクエリを書く"};
  var queryResults = BigQuery.Jobs.query(request, 'GCPプロジェクト名');
  var jobId = queryResults.jobReference.jobId;

  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults('GCPプロジェクト名', jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  var headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });

  var result = '```';
  
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    for (var j = 0; j < cols.length; j++) {
      result += cols[j].v;
      result += ' ';
    }
    result += '\n';
  }
  result += '```';
  
  result +=  '\n';
  
  notify(result);
}

function notify(message) {
  var url = 'slack の incoming webhooks の URL';

  var payload = {
    'channel': '#チャンネル名',
    'text': message
  };
  
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };
   
  return UrlFetchApp.fetch(url, options);
}

GASでは時間を決めて自動的に関数を実行することもできます。 ResourcesメニューのCurrent Project's Triggerをクリックすると、表示されるダイアログから設定することができます。

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