GW最終日だからBigQueryをちゃんと使ってみる #SQL

ゴール

BQにデータを流し込んで、ローカルからSQLを叩いて欲しい結果を得られるようにする

長くなりそうなんで何回かに分けます。

step1 - BigQueryとは & 実際にweb上で使ってみる
step2 - CLIツールを導入してローカルからBQを使ってみる
step3 - S3からBQにデータを流し込んで見る

はじめに

Google BigQueryとは何なのでしょうか。

何となく名前は聞いたことある人が多いと思います。
でも実際に何が出来るのか。何がスゴいのか。我々に何をもたらしてくれるのかをしらずに過ごしてる方も多いのでは。

まず、簡単にBigQueryについて説明します。

Googleが提供してくれているビッグデータの解析サービス
2012にローンチ
大量のデータを早く処理出来る(3億件を超えるデータですら10秒以内に結果を出せるとのこと)
SQLを使って、欲しいデータを抜き出す事が出来る
でもお高いんでしょう？って思ったら結構安い

BQが早い理由

カラム型アーキテクチャ

リレーショナルデータベース
- 行単位でデータを保存している
BigQuery
- 列単位でデータを格納
- メリット
  - トラフィックの最小化
    - 投げたクエリの対象とするデータにしかアクセスしないため、トラフィックが最小化出来るらしい
    - 例えば、SELECT host FROM access_logとすると、access_logテーブルのhost列にしかアクセスしないので、トラフィックを少なく出来るとのこと(普通のデータベースの仕組みを知らない...)
  - 高圧縮率
    - 通常のデータベースだとデータの圧縮率が3:1になるのに対して、このBigQueryが採用しているカラム型アーキテクチャだと、列単位のデータなので圧縮率が10:1になるらしい
    - 同じ列のデータは大体性質が似ているためカーディナリティが小さくなる為)
    - (例えば、ageというカラムが存在した場合、大体0~100くらいのデータにまとまる)

ツリーアーキテクチャ

もう一つの特徴が、我々が投げたクエリを大規模分散処理に展開するためのツリーアーキテクチャである。
我々がクエリを投げると、まずはroot serverにリクエストが送られるが、
その後更にしたのleaf serverにクエリをツリー構造で広がっていく事で大規模分散処理を実現している。

BigQueryの抽象概念

このような構造になっている。

プロジェクト

BigQueryにおける抽象概念のトップコンテナ
各データセットや、ジョブは全てこのプロジェクト毎に置かれる事になります
- プロジェクトがなければ、データセットやジョブを作れない

データセット

RDBにおけるデータベースのようなもの
このデータセットに対してアクセスの制御を行う事が可能です
- 要は、誰でもアクセスOKとか、自分の部署の人だけ。などもこのデータセット単位で制御することになります

テーブル

これは構造化されたデータ(行)の集合
(正直良くわかってない)
このテーブルに対しては、↑のデータセットの様にアクセス制限は出来ませんので注意

ジョブ

クエリの実行
データの追加
テーブルのコピー
etc… の実行単位
これらのジョブは全て非同期で行われる

料金体系

BigQueryの料金(公式)を参照してます。
これは随時更新される可能性があるので注意して下さい。

対象	詳細	料金
ストレージ利用料	BQ内に保存してるデータ量に対しての課金	$0.02/GB/月
長期保存	テーブルが90日間編集されない場合(古いデータ)は勝手に値引きされるらしいです	$0.01/GB/月
クエリ処理(オンデマンド)	クエリ実行時にスキャン対象となったデータ量(月に1TBを超えたときだけ発生	$5/TB (※ エラーまたはキャッシュされたクエリには料金はかからない)
クエリ処理(定額)	企業などで大容量を使う場合にはお得な定額プランもあります	$40,000/2000スロット/月
ストリーミングインサート	リアルタイム収集で使うAPIの場合、インサートしたデータ量に対して課金	$0.01/200MB
データの読み込み	データ分析の為のデータの読み込み	無料
データのコピー	既存データのコピー	無料
データのエクスポート	データをCSVや、スプレッドシートにエクスポートが出来る	無料

BigQueryを使ってみる

下記の順番で使えるようにしていきます。

GCPプロジェクトを作る
無料トライアル登録を行う
データセットを作ってみる
テーブルを作ってみる
他のユーザとデータセットを共有

1. GCPプロジェクトを作る

Developer Consoleにアクセス
ここで、プロジェクトの作成をクリック
- これが抽象概念の１番上のプロジェクトとなります
- 適当に名前をつけて作成を押します
プロジェクト一覧ページへ
先程作ったプロジェクトを選択します
左上のハンバーガーメニュを押すと、API Managerという選択肢があるので、クリックしてライブラリを選択します

このライブラリの中から、Bigquery APIを選択すると、下記の画面に遷移します

Cloud Consoleのこのサービスに移動をクリック
ここで、自分のプロジェクトから、create datasetをしようとしても、請求情報が登録されてないと作れないという旨の警告がでますので、まずは無料トライアルに登録しておきます。

2. 無料トライアル登録

こちらから無料トライアルを登録します。

これで登録すると初めて、GCPの諸々のAPIなどへのアクセス権を貰える
今登録すると、12ヶ月感使える$300分のチケットがついてくる
ロボットでない事を確認するために、クレカ登録するが、手動で有料版にアップデートしない限りは勝手に請求しません

という事が書かれてるので、Googleを信用して登録しておきます。

こんな感じで、勝手に請求されないから大丈夫だよというのが強調されてます。

3. データセットを作る

この登録をしただけではまだBigQueryは使えません。
次の2つの手順を踏みましょう。

請求を有効にする
- GCPのダッシュボードから「お支払」を選択して、自分の作ったプロジェクトの請求先情報を紐付けておきます
BigQueryAPIを有効にする
- これもGCPのダッシュボードのAPI Managerから、BigQueryAPIを有効にすればOKです。

で、準備が整ったので、データセットを作ってきます。

BigQueryの画面左側の自分のプロジェクト名横に三角形がありますので、クリックするとcreate datasetが出てきます
テスト用なので1日で消す様に設定して作ってみます
完了するとデータセットが完成します

4. テーブルを作ってみる

データセットが出来ると、テーブルを作れるようになります
先程データセットを作ったときと同様に、データセット横の三角形を押すとcreate tableが出来るようになるのでポチります
- これをポチると、Emptyなテーブルを作るか、元データからテーブルを作るか選択できます
  - 元データはCSV,json,avro(?)から選べます
- せっかくなのでそれっぽいデータを使ってみます
- 今回は、疑似個人情報生成というサービスを使ってみました

テーブルが出来て、Detailを見るとデータが入っているのが確認出来ます。
(サンプルデータらしいのでランダムなのですが、一応名前はぼかします)

5. 他のユーザと共有する

先程も説明したとおり、アクセス権限の制御はデータセット単位でしかできません。
なので、自分のデータセット横の三角形を選択すると、share datasetというものが出てきます
- shareしようとすると、招待者のメアドを入力する項目が出て来るので、ここで追加してあげればデータセットを共有出来るようになります

おまけ

実際に、クエリを実行してみます。BigQuery画面左上のCOMPOSE Queryをクリックすればクエリ入力画面が出て来るので、欲しいデータを得る為のクエリを入力してRUN QUERYを押します。

実行結果は下記の通り

次回予告

今回は、BigQueryの使う準備から、無料トライアルの登録、請求情報の設定、APIの有効化、データセット/テーブルの作成を行ってみました。
次回はGCPのCLIツールを導入し、コマンドラインから、データセット/テーブルの作成を行っていきます