Google BigQueryをCLIで使ってみる


[前回](http://qiita.com/ritukiii/items/a1dd0da0ac33b18df6be)、BigQueryを初めからweb上で使ってみるという事をやってみました。

今回は、CLIツールを使ってBQを操作しようという事をやってみようと思います。

## ゴール

* GCPのCLIツールでデータセット/テーブル作成-削除,クエリの発行結果の取得までをやる


## 1. SDKのダウンロードとインストール & 初期設定

* インストール
  * `$ curl https://sdk.cloud.google.com | bash`
* bash再起動
  * `$ exec -l $SHELL`
* gcloudコマンド初設定
	* `$ gcloud init`
	* GCPのページがwebページで開かれて、承認画面に行くのでOKとすれば大丈夫です
* 使うプロジェクトを選択する
	* アカウントでのログインが成功すると、次にどのプロジェクトを使うか聞かれます
	* `Pick cloud project to use` こんな表示が出て、選択待ち状態になります
	* ここで、自分が使いたいプロジェクトの番号を選んでEnter

## 2. 実際にbqコマンドを使ってみる

* テーブルの確認

```bash
$ bq ls
   datasetId
 --------------
  test_dataset
```

[前回](http://qiita.com/ritukiii/items/a1dd0da0ac33b18df6be)作ったテーブルが確認出来ました。

* 因みに選択した以外の全てのプロジェクトをみるには`-pオプション`をつけます

```bash
$ bq ls -p

           projectId                 friendlyName
 ------------------------------ -----------------------
  test-bigquery-project-xxxxxx   test-bigquery-project
  test-project-with-php          test-project-with-php
```

以前適当に作ったプロジェクトも確認出来ました。

#### 新しくデータセットを作ってみる

前回はweb上で作ってましたが、今回はCLIツールから作ってみましょう。

`mk`コマンドを使います。

```bash
$ bq mk test2
Dataset 'test-bigquery-project-166901:test2' successfully created.

$ bq ls
   datasetId
 --------------
  test2
  test_dataset
```

先程までの、`test_dataset`とは別で今作った`test2`が追加されてるのがわかります。

#### テーブルを作ってみる

テーブルの作成・更新もbqコマンドで可能です。

`bq load [--source_format=NEWLINE_DELIMITED_JSON|CSV] destination_table data_source_uri table_schema`

* `destination_table`
	* 作成するテーブルの名前
* `--source_format`
	* `CSV`/`NEWLINE_DELIMITED_JSON`/`DATASTORE_BACKUP`/`AVRO`/`PARQUET`を選択
* `data_source`
	* テーブルに入れるデータソースのCSVファイル
* `table_schema`
	* `column_name:datatype`をカンマ区切りで指定します。
	* 例えば、`氏名` / `性別` / `年齢` とかであれば下記のように指定します
		* `name:string,sex:string,age:integer`
	* このスキーマは、長くなると面倒なので、↓のように別でファイルを作って、`table_schema`として渡す方法もあるそうです

```schema.json
[
  {"name": "name", "type": "string", "mode": "required"},
  {"name": "sex", "type": "string", "mode": "required"},
  {"name": "age", "type": "integer", "mode": "required"}
]
```

では、実際に、前回作ったテーブルと同じデータをbqコマンドから作ってみます。

```bash
$ bq load --source_format=CSV test2.sample_table ~/Desktop/personal_infomation.csv schema.json
Upload complete.
Waiting on bqjob_r2776d007192b2796_0000015be1373ca8_1 ... (0s) Current status: DONE
BigQuery error in load operation: Error processing job 'test-bigquery-project-166901:bqjob_r2776d007192b2796_0000015be1373ca8_1': Too many errors encountered.
Failure details:
- mediaupload-snapshot: Too many values in row starting at position:
0.
```

失敗しました。
これは、data_sorceに指定したデータと、table_schemaで指定したものが異なるとこのように怒られます。
今回は、元データには、idという列があったので、`schema.json`を修正しました。

気を取り直してもう一度

```bash
$ bq load --source_format=CSV test2.sample_table ~/Desktop/personal_infomation.csv schema.json
Upload complete.
Waiting on bqjob_r1b1627242c9c2f70_0000015be13b6ef8_1 ... (0s) Current status: DONE
BigQuery error in load operation: Error processing job 'test-bigquery-project-166901:bqjob_r1b1627242c9c2f70_0000015be13b6ef8_1': Too many errors encountered.
Failure details:
- mediaupload-snapshot: Could not parse '������' as int for field id
(position 0) starting at location 0
```

今度は、1列目にid指定したのに、integerになってない行があるよという怒られ方です。
ヘッダ行に`連番`という文字列が入っている為ですね。

`--skip_leading_rows=1`としてやると、1行目は無視してloadしてくれるみたいです。

三度目の正直！

```bash
$ bq load --skip_leading_rows=1 --source_format=CSV test2.sample_table ~/Desktop/personal_infomation.csv schema.json
Upload complete.
Waiting on bqjob_r75b195d56702e53f_0000015be13e02d9_1 ... (1s) Current status: DONE
```

成功したっぽい。

```bash
$ bq ls test2
    tableId      Type    Labels
 -------------- ------- --------
  sample_table   TABLE
```

新しくテーブルが完成してます。
showを使うとスキーマも確認出来ます。

```bash
$ bq show test2.sample_table
Table test-bigquery-project-166901:test2.sample_table

   Last modified              Schema             Total Rows   Total Bytes   Expiration   Labels
 ----------------- ---------------------------- ------------ ------------- ------------ --------
  07 May 13:50:00   |- id: integer (required)    100          3542
                    |- name: string (required)
                    |- sex: string (required)
                    |- age: integer (required)
```


### 3. クエリを実行してみる


```bash
$ bq query "SELECT id, sex, age FROM [test2.sample_table] order by age LIMIT 10"
Waiting on bqjob_r3d20fd726b13afd5_0000015be1417437_1 ... (0s) Current status: DONE
+----+-----+-----+
| id | sex | age |
+----+-----+-----+
| 15 | 男   |  20 |
| 53 | 男   |  20 |
| 68 | 男   |  20 |
| 70 | 男   |  21 |
| 37 | 女   |  21 |
| 17 | 女   |  22 |
| 11 | 女   |  22 |
| 32 | 女   |  22 |
| 34 | 女   |  22 |
| 61 | 男   |  23 |
+----+-----+-----+
```

いい感じですね。

#### 4. 検索結果をcsvに出力する

`--format`を指定するとOKです。

```bash
$ bq query --format csv "SELECT id, sex, age FROM [test2.sample_table] order by age LIMIT 10" > result.csv

$ cat result.csv

id,sex,age
15,男,20
53,男,20
68,男,20
70,男,21
37,女,21
17,女,22
11,女,22
32,女,22
34,女,22
61,男,23
```

## 次回予告

* S3/Google Cloud Storageからデータをロードする
* 今回はローカルファイルからデータをロードしてテーブルを作りました。ローカルファイルでは1ファイルしか指定出来ませんが、Google Cloud Storage URIなら複数指定することが可能なようですので
* 大量データを扱ってみる(web上だと大量データを扱うことが出来ないので、それを扱ってみます
* 余裕があれば、re::dashも使ってみようと思います。