こんにちは。データ分析部の阿部です。
今回は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
のチェックを外すだけです。
また、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を選択します。
jsonキーはGCPのWebコンソールで然るべきService Accountを作って、赤丸で囲んだところをクリックするとダウンロードできます。
データソース追加選択画面の下の方に Use Standard SQL というチェックボックスがあり、これをチェックすることで標準SQLを使ってクエリを書けるようになります。 この機能はRe:dashの右下に出ている丸いアイコンをクリックしてRe:dashの開発者にリクエストして一日待たずに機能を追加してもらえました。
Google Apps Script を使ってクエリ結果をSlackに通知する方法
BigQueryはGoogle Apps Script (以下GAS) を使ってアクセスすることもできます。 GunosyではGASからBigQueryのクエリを実行し、incoming webhooks を使ってSlackにKPIはテーブル異常の通知させています。
GASからBigQueryを使うにはまずResourcesメニューからAdvanced Google Servicesを選び、BigQueryのAPIを有効化します。
その後ダイアログの下の方のリンクをクリックして、Google Developers ConsoleからもAPIを有効化します。
こんな感じのコードを書いて 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をクリックすると、表示されるダイアログから設定することができます。
こうすることで、例えばデイリーでKPIを追うことができますね。
クエリ以外にもAPIが用意されているので、興味があったらこちらを見ると良いでしょう。
https://developers.google.com/apps-script/advanced/bigquery
まとめ
今回はBigQueryの一部での導入にあたり、便利な機能をいくつか紹介しました。 Redshiftからの移行ということもあり、特に標準SQLに対応したことはクエリの修正が少なくてすみました。 こちらはビッグクエラーの皆様も待望の機能だったのではないでしょうか。
BigQueryは進化が早く、次々と新しい機能がリリースされ続けています。 興味があればGoogleの公式ページにまとまっているので御覧ください。