Google BigQuery を試してみる


ビッグデータ。どのような技術で対応するかは悩ましいところです。

各種術一長一短ありますが、

* データをクラウドに置いてよい
* 原則参照系※

ということであれば、黙ってBigQueryを使えばいいと思います。

>※BigQueryもStreaming Insertsを使えば挿入可能

なぜなら、

* 速い
* 設定が簡単

だからです。

##データの準備

###データの生成

Amazon EMRのImpalaのテスト用に容易されているジェネレーターが生成した1GBx3個のファイルを利用します。

ツールのダウンロード。

```bash
wget http://elasticmapreduce.s3.amazonaws.com/samples/impala/dbgen-1.0-jar-with-dependencies.jar
```

データの生成。

```bash
java -cp dbgen-1.0-jar-with-dependencies.jar DBGen -p ./ -b 1 -c 1 -t 1
```

上記の場合、books 1G,customer 1G, transanction 1Gの計3GBのデータができます。
実行にはJavaが必要です。

###データのアップロード

作成したら、Google Cloud Storageにアップロードします。
ブラウザからアップロードでき、かつ、並列処理されます。便利。

![bigquery](http://www.bluecode.jp/qiita/bigquery/003.png)

アップロードされるのを待ちます。

>なんで、AzureはBLOBのアップロード機能をつけないのでしょうか。。。

##BigQueryを使う

ではデータの準備ができましたので、BigQueryを使って行きます。

###メニューからBigQueryを選択

BigQuery等を選択する左メニューは折りたたまれていますので、開きます。

![bigquery](http://www.bluecode.jp/qiita/bigquery/001.png)

BigQueryを選択。

![bigquery](http://www.bluecode.jp/qiita/bigquery/002.png)

###datasetの作成

datasetを作成します。普通？で言うところのデータベース的なものです。

>なおここではWebUIを使って作業しますが、CLIでも可能なので自動化ももちろん可能です。

![bigquery](http://www.bluecode.jp/qiita/bigquery/004.png)

ここではhogedbという名前にします。

![bigquery](http://www.bluecode.jp/qiita/bigquery/005.png)

###Tableの作成

datasetが作成されたら、次にdataset内にTableを作成します。

* books
* customers
* transactions

の3つを作成していきます。
datasetの右にある小さなプルダウンからcreate Tableを選択します。

####books

![bigquery](http://www.bluecode.jp/qiita/bigquery/006.png)

Table名を設定。ここではbooksとします。

![bigquery](http://www.bluecode.jp/qiita/bigquery/007.png)

データ形式とデータの場所を選びます。
ローカルからもアップできるようです。便利。ここではCloud Storageを選択。

```
gs://<バケット名>/保存PATH
```

とシンプルでいいですね。
なお、データは「CSV」となっていますが、デリミタは後ほど選択できるようです。今回のデータはデリミタが|なので、変換するの大変・・・と思いましたが取り越し苦労でした。

![bigquery](http://www.bluecode.jp/qiita/bigquery/008.png)

スキーマを設定します。先にデータを読み込んでプレビューしながれできれば・・・と思いますが、しかたありません。
1つうまくいかなかったことは、今回のデータには2015-12-01といったDate型が含まれていますが、唯一時間計型として容易されているTimestamp型はYYYY-MM-DD HH:MM:SSの形式じゃないと受け入れてくれません（自動的にHH:MM:SSを00:00:00とはしてくれません）。

おそらく、入力前に整形するかUDFなどを作成して対応するかのどちらかになるかと。

今回は日付はクエリで利用していないので、とりあえすStringとして取り込みます。

![bigquery](http://www.bluecode.jp/qiita/bigquery/009.png)

デリミタやスキップする行数等を選びます。ここではスキップ等の必要はないのでデリミタだけPipeに変更します。

![bigquery](http://www.bluecode.jp/qiita/bigquery/010.png)

####customers

同様にcustomersテーブルを作成していきます。

![bigquery](http://www.bluecode.jp/qiita/bigquery/011.png)

形式とデータの場所を指定。

![bigquery](http://www.bluecode.jp/qiita/bigquery/012.png)

スキーマを設定。

![bigquery](http://www.bluecode.jp/qiita/bigquery/013.png)

デリミタ等を設定。

![bigquery](http://www.bluecode.jp/qiita/bigquery/014.png)

####transactions

最後にtransactions。

![bigquery](http://www.bluecode.jp/qiita/bigquery/015.png)

AzureのSQL Data Warehouseがtransactionsというファイル名を受け入れなかったため、transactionzというファイル名前になっていますが、BigQueryは別に関係ありません。

![bigquery](http://www.bluecode.jp/qiita/bigquery/016.png)

スキーマを設定。

![bigquery](http://www.bluecode.jp/qiita/bigquery/017.png)

各データが大きい場合は、並列ロードされるようです。

![bigquery](http://www.bluecode.jp/qiita/bigquery/018.png)


取り込んだデータのサマリを見ることができます。

![bigquery](http://www.bluecode.jp/qiita/bigquery/019.png)


クエリもUIで行うことができます。

![bigquery](http://www.bluecode.jp/qiita/bigquery/020.png)

##クエリの実行

###count（単一テーブル）

####クエリ

```sql
select count(*) from hogedb.books;
```

####結果
他のDBの参考値も。SDWとはAzure SQL Data Warehouseの略。

|     DB     |  Time(sec)  |     Memo     |
|:-----------|------------:|:------------:|
| local MySQL|        10.5 | noindex      |
| SDW 100DWU |         2.0 | nocache      |
| SDW 400DWU |         1.0 | nocache      |
| SDW 2000DWU|         1.0 | nocache      |
| BigQuery   |         0.9 | nocache      |

###検索（単一テーブル）

####クエリ

```sql
select * from hogedb.books where isbn='2-90323-440-7';
```

####結果

|     DB     |  Time(sec)  |     Memo     |
|:-----------|------------:|:------------:|
| local MySQL|        16.2 | noindex      |
| SDW 100DWU |         4.0 | nocache      |
| SDW 400DWU |         1.0 | nocache      |
| SDW 2000DWU|         1.0 | nocache      |
| BigQuery   |         0.7 | nocache      |

###group by（単一テーブル）

####クエリ

```sql
select category,count(*) from hogedb.books group by category;
```

####結果

|     DB     |  Time(sec)  |     Memo     |
|:-----------|------------:|:------------:|
| local MySQL|        36.8 | noindex      |
| SDW 100DWU |         5.0 | nocache      |
| SDW 400DWU |         1.0 | nocache      |
| SDW 2000DWU|         1.0 | nocache      |
| BigQuery   |         1.3 | nocache      |

###join（３つ結合）

####クエリ

```sql
select
	hogedb.transactions.id,
	hogedb.books.isbn,
	hogedb.books.category,
	hogedb.books.price,
	hogedb.transactions.quantity,
	hogedb.customers.id,
	hogedb.customers.name
 from hogedb.transactions 
 left join each hogedb.books on hogedb.transactions.book_id = hogedb.books.id
 left join each hogedb.customers on hogedb.transactions.customer_id = hogedb.customers.id
 limit 10
```

####結果

|     DB     |  Time(sec)  |     Memo     |
|:-----------|------------:|:------------:|
| local MySQL|      4:21.0 | noindex      |
| SDW 100DWU |      1:29.0 | nocache      |
| SDW 400DWU |        35.0 | nocache      |
| SDW 2000DWU|        42.0 | nocache      |
| BigQuery   |         4.6 | nocache      |

###join + group by（３つ結合）

####クエリ

```sql
select
	hogedb.customers.name,
	sum(hogedb.books.price) as sum
 from hogedb.transactions
 left join each hogedb.books on hogedb.transactions.book_id = hogedb.books.id
 left join each hogedb.customers on hogedb.transactions.customer_id = hogedb.customers.id
 group by hogedb.customers.name
 limit 10;
```

####結果

|     DB     |  Time(sec)  |     Memo     |
|:-----------|------------:|:------------:|
| local MySQL|     timeout | noindex      |
| SDW 100DWU |      2:52.0 | nocache      |
| SDW 400DWU |        48.0 | nocache      |
| SDW 2000DWU|      1:10.0 | nocache      |
| BigQuery   |         8.3 | nocache      |