BigQueryの事始め。Web Consoleと戯れる。
Burikaigiも終わり、引っ越しも落ち着いたので、ようやく中途半端に書いていたBlogをせこせことまとめています。
今回はGoogleが提供する、これだけでもGCP（Google Cloud Platform）を使う価値があるであろう、BigQueryについてまとめてみました。
BigQueryとは？
Googleが提供する「フルマネージドのエンタープライズ向けアナリティクス データ ウェアハウス」です。
2012年にリリースされているものですが、もともとGoogleの内部でGmailや検索エンジンなどのビッグデータ処理に使われいたものを、フルマネージドサービスとして提供したのが始まりとのこと。
そのためペタバイト級のデータも数秒で処理することが可能と、あまりにも私が扱うデータ量からはかけ離れすぎて、実感が持ちづらいサービスです。
そんななかでも、わかりやすいBigqueryのデモが以下のYoutubeかと。120億行の正規表現マッチ付き集計が5秒で完了・・・。これは・・・早い・・・。
さすがに120億とは行きませんがサンプルデータとして、Wikipediaのデータ（313,797,035件、約3億件 35.7GB）が提供されているので、部分一致検索を試してみたところ、1.6秒・・・。早いですねー。
それ以外にも特徴として、基本的にはRDBと同じような形でSQLベース（ただし、BigQuery用として作られたスーパーセットのため、ある程度方言はあり）で扱えること
ストレージ・クエリ実行時のデータ量単位での使いやすい料金（安いかどうかは使い方次第）
REST APIや各種ライブラリ（C#・Java・Ruby・Python・GO・Node.js・PHP）の提供
などなど、開発者が扱いやすい環境がかなり整っているサービスです。
読んでおくといい感じの資料
以下、今回の記事を書くにあたって、参考にさせてもらいました、おすすめの資料です。
■ウェブ UI を使用したクイックスタート
https://cloud.google.com/bigquery/quickstart-web-ui?hl=ja
公式のクイックスタート。今回の記事の参考にさせてもらいました。結構丁寧なので、はじめに触ってもとっつきやすいと思います。
www.slideshare.net
課金については、このスライドが一番わかりやすいです。
www.slideshare.net
個人的に一番オススメの資料。中の人のQiita記事。なぜBigqueryがやばいのかよくわかる。
■はじめの一歩が踏み出せない人のためのBigQuery入門
https://apps-gcp-tokyo.appspot.com/bigquery-introduction/
今回やってみたこと
とりあえず、基本となるWeb ConsoleベースでのSQLの実行とDataset・Tableの作成方法
および、主な課金の要素をまとめてみました。
基本的なSQLの実行方法
前述の通り、BigQueryは様々なプラットフォームから実行できますが、同じような操作をWeb Consoleベースで実行できるので、今回はこれを使って試したいと思います。
それと、先のWikipediaデータのように予めHello Worldを始めやすいサンプルデータセットが提供されているので、それを使ってまず触ってみましょう。
GCPのアカウントとプロジェクトは作成しておいてください。
以下のGoogle Cloud Platformにログインして、左ナビゲーションからBigQueryをクリック
BigQueryのWeb Console画面が立ち上がるので、［Compose Query］クリックして、とりあえず単純なSELECT Queryを実行したいと思います。
Queryを記述するウインドウが表示されるので、以下のクエリを貼り付けます。
#standardSQL SELECT weight_pounds, state, year, gestation_weeks FROM `bigquery-public-data.samples.natality` ORDER BY weight_pounds DESC LIMIT 10;
詳しくは後述しますが、BigQueryはクエリ実行時のデータ量によって、課金が行われます。
その際の指標になる処理データ量を確認するのが、画面左下にあるチェックマークです。
これをクリックすることで、SQLのバリデーションと共に、処理される予定のデータ量が計算されます。
上記クエリを実行時に処理されるデータ量は「3.49GB」でした。
これでRun Queryをクリックすると、以下のように処理結果が表示されます。普段SQLを書いている人であれば、馴染み深いインターフェースではないでしょうか。
ちなみにテーブル全体のデータ量やカラムの情報は左ナビゲーションメニューから確認できます。
今回使用したのは、Public DatabaseのSamplesにある「Natality」というデータです。
ここを選択して、［Details］タブをクリックすると、データ件数などテーブルの細かな情報が確認できます。
Dataset・Tableの作成
先程はサンプルのデータをそのまま使いましたが、今度は実際に一から作ってみたいと思います。
データはBigquery用のサンプルのデータが以下のURLから提供されているのでこれを使います。
http://www.ssa.gov/OACT/babynames/names.zip
まず、最初にデータセット（RDBで言うところのデータベース）を作成します。
プロジェクト名の［▼］をクリックして、［Create new dataset］をクリックし
任意の名前をつけて作成します。
ロケーションは2018年2月現在ではUSとEUの2つのリージョンから選択できます。
Data expirationはデータが削除されるまでの日数です。BigQueryはクエリだけでなく、データを保持するのにも課金が発生するので要注意です。今回は5日間で削除されるようにしました。
これで、DataSetの作成は完了です。
続いて、テーブルを作成します。テーブルの作成はいろんなアプローチがありますが、今回はWeb ConsoleからCSVファイルベースでファイルをアップロードしつつテーブルを作成する方法で行います。
先程作成したDatasetの横に表示される［＋］ボタンをクリックすると、Table作成画面が表示されるので、Chose FileからダウンロードしておいたCSVファイルを選択します。
次にカラムの指定ですが、一度Edit as Textをクリックして、以下のテキストを貼り付けます。
今回はヘッダーが無いCSVファイルなので、以下のようにカンマ区切りでカラム名と型を指定しています。
name:string,gender:string,count:integer
貼付け後、Create TableをクリックすればOKです。
作成されました。
Previewで見てみると、定義したカラムと一緒にCSVファイルも同時にデータとして登録されていることがわかるかと思います。
課金について
BigQueryの課金については、主に以下の3種類の操作・管理を行うことで発生します。
・ストレージ（永続と非永続で値段が違う）
・ストリーミングインサート
・クエリー
https://cloud.google.com/bigquery/pricing?hl=ja
2018年2月12日現在、1ドルあたり「108円（小数点以下切り捨て）」として、ちょっとまとめてみます。
［ストレージ］はわかりやすいですね。データの保管時にかかる費用です。
1GBあたり、永続であれば0.02ドル
最初のWikipediaが313,797,035件、約3億件 35.7GBなので、1ヶ月あたり77.112円。安い！12ヶ月使っても925.344円と1000円いきません。
［ストリーミングインサート］はデータの登録時の費用。ちょっと高めです。
1GBあたり、0.05ドル
例えば1日1GBのデータが発生して、BigQueryにインサートしたとしたら、1ヶ月で1.5ドル、日本円で162円。年間1944円となります。
ちなみにこのストリーミングインサートとは、SQL のInsert文もしくは、REST APIやライブラリからのInsert処理を実行した場合に課金されるものです。
今回Tableを作るのにCSVをベースで実行しましたが、これは課金対象には含まれません。なので、用途や処理したいデータの種類に合わせて、どんなデータ追加方法を取るかは色々と検討の余地ありです。
［クエリ］は一番気をつけないといけない要素。SQL実行時の費用です。
クエリ1回、1TB（1000GB）あたり5ドル（540円）なので1GBあたり0.005ドル（0.54円）
最初に紹介した以下のクエリ、3.5GBだと、1.89円が1回分の消費となるみたいです。
ちなみにこの算出方法ですが、BigQueryが列指向データベースのため、SelectやWhereなどの条件で指定されたカラムに比例します。
なので、これでSelect文にワイルドカードを使おうものなら、以下のように「21.9GB（11.826円）」と、10倍くらいのお値段になります。
ちなみに、最初のクエリから列を一つ削ると「2.5GB」まで縮小されました。
これがやっぱりチリツモな感じかつ、最終的にBIツールに落とし込むのであれば、経過データとして逐次取得したいと思うので、1回10円だったとしても1日5回やれば1ヶ月1500円、年間18,000円です。
BigQueryで多額のお金を溶かした人も居るらしいので要注意。
ただ、ここも色々と節約ポイントがあるらしく、キャッシュされたデータで実行できるクエリであれば課金されないだったり、テーブルを分割するアプローチだったりと、話題が絶えない感じです。
あと以下のプレビュー画面ですが、ここでの表示は課金されないみたいなので、有効活用しないといけないですね。
所感
とりあえず今回はここまで。色々と戯れるべきポイントが多くて、面白いです。
しかし、並行して、色々と調べているので、なかなか進まなかったり・・・。
