Kazuyuki Honda
DevOps Engineer@Quipper
実践 Google BigQuery
自己紹介
自己紹介
本多 一行
DevOps Engineer @ Quipper
Twitter: @hakobera
Qiita: hakobera
最近はすっかり GCP と
戯れる日々を過ごしています
自己紹介
Quipper, Ltd
「知の流通」を社是として K-12 向けの教育サービスを提供。先日100万ユーザ、2億問の解答達成。
自己紹介
Quipper, Ltd
「知の流通」をキーに、K12 向けの教育サービスを提供。先日100万ユーザ、2億問の解答達成。
We are hiring!
今日話すこと
「BigQuery は速い！」
は聞き飽きた
BigQuery を実践的に
使うために「速い」以外
の知見
今日話すこと
実践 BigQuery 目次
— How BigQuery Works
— BigQuery の導入
— BigQuery の運用
— BigQuery の応用
— BigQuery のコミュニティ
How BigQuery Works
Google の BigData 処理に対する１つの解答
How BigQuery Works
「レベルを上げて、物理で殴ればいい」
— データが増えたなら、サーバを増やせばいいじゃない
・1TBのデータを1秒でスキャンするにはどうすればよいのか、からスタート
・数千台のサーバ、数万個のストレージにデータを分散すればできる
— Dremel と呼ばれる分散SQLエンジンを実装して、Managed Service として提供
— やっていることは至って普通
・データ圧縮をサポートした Columnar Storage
・Mixer と Shard による分割統治
How BigQuery Works
詳細は Google BigQuery Analytics を読もう！
— BigQuery の中の人 Jordan Tigani さんと Siddartha Naidu さんが書いた本
— もっとも詳しい BigQuery のアーキテクチャ解説本。日本語版も３月に出たよ！
How BigQuery Works
4/24(金) bq_sushi
イベントあるってさ
Jordan Tigani さんが来日して講演されます。
中の人に質問したり、要望を直接言えるのはこのイベントだけ！
抽選なので、今から申し込んでもチャンスあるよ！
#bq_sushi tokyo #1
01 / 23
BigQuery の導入
BigQuery を
選択する理由、
選択しない理由
BigQuery は
圧倒的に速いのか？
答えは「NO」
BigQuery の導入
速度だけが BigQuery を選ぶ理由ではない
— Presto, Redshift, Impala でも同等かそれ以上の速度が得られることがある
・オンプレ環境は用意できないのでチューニングのしようがない
— 既に上記のような環境を持っている場合、
速度の向上を期待して乗り換えても、期待はずれになる可能性がある
BigQuery の利点は
Full Managed Service
であること
BigQuery の導入
我々はサーバーを管理したいのではない、データを分析したいのだ
— 数百台規模のサーバーを管理することに本当に意味があるのか考えてみよう
— 以下のメリットを上回るメリットを見出だせるなら BigQuery を選ぶ必要はない
・ 容量無制限のディスク
・ 初期費用の圧倒的な安さ（クエリ: $5/TB、ストレージ: $0.026/GB）
・ WebUI, CLI が標準装備
・Google Apps Script / Google AppEngine / Google Cloud Logging
/ Google Analytics Premium との連携
BigQuery の導入
小さく始められて、遅くならないことが重要
Google BigQuery Analytics
Chapter 2 より引用
BigQuery の導入
小さく始められて、遅くならないことが重要
こんな場合でも困らない！
— スタートアップには最適
— 小さく安く始められて、大きくなっても遅くならない
Hadoop クラスタを
持っていないなら、 BigQuery を導入してみて
損はない
Hadoop クラスタを
持っていても、
管理が嫌になったのならBigQuery を導入してみて
損はない
BigQuery の運用
BigQuery の料金
BigQuery の運用
BigQuery の課金体系
無料： Loading data(Bulk Insert), Export data, Table reads, Table copies
有料：
Storage $0.02/GB/月 = 500GBで1,000円
Streaming Inserts $0.01/10万行 = 1億行で1,000円
Query: $5/TB = 3TBで1,000円（1TBは無料枠）
参考: https://cloud.google.com/bigquery/pricing http://qiita.com/satoru_mag/items/33e6c759fd29e5987a0d http://qiita.com/satoru_mag/items/37de63e9b23e8fbe5567
Streaming Inserts と
Query 課金に気をつけろ
BigQuery が
やってくれること、
やってくれないこと
BigQuery の運用
BigQuery がやってくれること
— ストレージの管理（容量無制限、安い！）
— シンプルだけど有用な Web UI の提供
— CLI の提供（ https://cloud.google.com/bigquery/bq-command-line-tool）
— 高可用性 (SLA 99.9% 以上 https://cloud.google.com/bigquery/sla)
BigQuery の運用
BigQuery がやってくれないこと
— スキーマの管理
・スキーマレスではないので、自分でスキーマを管理する必要がある
— 統計解析、機械学習関連の機能
— 可視化（https://cloud.google.com/bigquery/third-party-tools）
— ETL 関連の処理
・ロード用のAPI、ツールは用意してあるが仕組みは自分で作る必要がある
・用途に応じて、Streaming Insert / Bulk Insert を使い分ける
BigQuery の運用
BigQuery が教えてくれないこと
— Backend Error の原因
・Backend Error が出た場合はお茶でも飲んで心を落ち着かせてからリトライ
・Backend Error の起こるタイミングは予測はできないので、
運用時には常にリトライ処理と簡単にリトライできる仕組みを用意しておく。
これは BigQuery に限った話ではないですが
— 詳細な機能アップデートの内容
・いつのまにか機能が増えていることがある
BigQuery の運用
BigQuery の ETL ツール
— Streaming Insert
・fluent-plugin-bigquery
・Google Cloud Logging の BigQuery 連携
— Bulk Insert
・bq コマンドの load 機能 (直接アップロード／GCS経由）
・embulk-plugin-bigquery
BigQuery の運用
BigQuery の ETL ツール
各種言語のクライアントライブラリ
https://cloud.google.com/bigquery/client-libraries
公式ライブラリとして、
.NET, Go, Java, JavaScript, Node.js, Objective-C, PHP, Python, Ruby
を提供。
01 / 23
BigQuery の応用
これは知っておこう！
BigQuery の応用
これは知っておこう！
ー Table Decorator
ー Table wildcard functions
ー JSON functions
ー Streaming Inserts のリミット解除できる
BigQuery の応用
Table Decorator
Query 速度の改善とコスト削減の両方に有効！
<table name>@[-]<start time>[-[[-]<end time>]]��- 単位は ms�- 先頭に `-` をつけると相対時間、つけないと絶対時間�- 時間指定はテーブル作成日時より後でないといけない（前にすると NOT_FOUND エラーが発生する）�- `@0` は特殊で、取得できる最も古い時点での revision を取得できる�- 最後を `-` で終えると end time を現在の時間を指定したのと同じ意味になる
BigQuery の応用
Table Decorator
・例��table@1000 => 時刻 1000 時点での revision�table@-1000 => 現在時刻より 1000ms 前の revision�table@0 => 取得可能な最も古い revision。最大7日前。�table@1000- => 時刻 1000 から 現在時刻までを含む revision�table@1000-2000 => 時刻 1000 から 2000 までを含む revision�table@-2000--1000 => 現在時刻より 1000ms 前から 2000ms 前までを含む revision��・料金��table decorator によりスキャン範囲が絞り込まれ、その範囲でしか課金されなくなる
BigQuery の応用
Table Decorator
・テーブルデータのリカバリにも使える�間違ってデータのインポートをしてしまった場合に利用できる。�ただし、Jobの終了時間ではなくてテーブルが最後に変更された時間を指定しないといけないの Job の実行時間近辺で試行錯誤してくれ。�
・注意点�BigQuery は7日間しか revision を保持してくれない。��・その他注意事項�- Streaming データは実際にバッファされて、バッチインサートされている。revision はこのバッチインサートされた時刻を対象に動作する�- load, query ジョブの出力先テーブルには使えない�- revision の削除はできない
BigQuery の応用
Table wildcard functions
Table Query : 正規表現などでテーブルをまとめて指定できる
例: テーブル名が “boo” で始まり、その後 3〜5文字の数字が続くテーブル
SELECT
speed
FROM
(TABLE_DATE_RANGE(mydata, ‘REGEXP_MATCH(table_id, r”^boo[\d]{3,5}”)’))
BigQuery の応用
Table wildcard functions
Table Date Range Query : 日時で分割したテーブルをまとめて指定できる
フォーマットは <prefix>YYYYMMDD 形式
例: people20150401, people20150402, people20150403 を一気に選択
SELECT
name
FROM
(TABLE_DATE_RANGE(mydata.people,
TIMESTAMP(‘2015-04-01’),
TIMESTAMP(‘2015-04-03’)))
BigQuery の応用
Table wildcard functions
Table Date Range Query : 日時で分割したテーブルをまとめて指定できる
<prefix>YYYYMMDD 形式
例: 直近7日間のテーブルを取得
SELECT
name
FROM
(TABLE_DATE_RANGE(mydata.people,
DATE_ADD(CURRENT_TIMESTAMP(), -7, ‘DAYS’),
CURRENT_TIMESTAMP()))
Table Date Range Query は View にも使えるので、LAST_7_DAYS view が作れる！
BigQuery の応用
JSON functions
ー とりあえずスキーマに悩んだ場合に使える
ー Google Cloud Logging のログに複数フィールド入れたい場合にも使える
BigQuery の応用
JSON functions
BigQuery の応用
JSON functions
BigQuery の応用
Streaming Inserts のリミットは解除できる
ー Streaming Inserts は 1万行/秒
ー しかし、申請すれば 10万行/秒 までリミット解除できる
ー Table sharding は Table Decorator と組み合わせて使えないので悪手
Quipper での活用事例
10万行以下のCSVに落とすまでがエンジニアの仕事
環境さえ整えれば、
ビジネス側の人は
SQL くらい書いてくれる
それも見たこともない位い長いやつを・・・
MongoBQ
EBS Snapshot
BigQuery
BigQuery on Quipper
Apps Script
AdHook Query using WebUI
BigQuery の応用
GAS を利用した定期レポート
BigQuery と Google Apps Script の相性は最高。とにかく認証が楽！
SQLシートを書いて、トリガーで実行！
BigQuery の応用
MixPanel データのよりディープな分析
MixPanel のデータを BigQuery にインポートしてよりディープな分析をやっています
MixPanel は便利だけど、JOIN ができない！
BigQuery の応用
データアナリストの採用試験用のDBとして使ってみた
データアナリストのSQLテストを
BigQuery/AppEngine を使って作りました。
SQL の実行履歴なども見れる。
Skype など遠隔面接などで活用。
BigQuery のコミュニティ
BigQuery のコミュニティ
#bq_sushi グループ作りました！
日本語での情報が少ないとお嘆きの皆様。
Google BigQuery の日本語コミュニティです。BigQuery に関することならなんでもOKです。BigQuery に関する質問、活用事例、勉強会の呼びかけなど自由に書き込みしてください。 参加申請はご自由に！
#bq_sushi
https://www.facebook.com/groups/bq.sushi/
BigQuery のコミュニティ
4/24(金) bq_sushi
イベントあるってさ
Jordan Tigani さんが来日して講演されます。
中の人に質問したり、要望を直接言えるのはこのイベントだけ！
抽選なので、今から申し込んでもチャンスあるよ！
#bq_sushi tokyo #1
Thanks !
Kazuyuki Honda
twitter — @hakobera
github — github.com/hakobera