Google BigQueryをCLIで使ってみる #SQL

前回、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コマンドを使ってみる

テーブルの確認

$ bq ls
   datasetId
 --------------
  test_dataset

前回作ったテーブルが確認出来ました。

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

$ bq ls -p

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

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

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

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

mkコマンドを使います。

$ 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コマンドから作ってみます。

$ 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を修正しました。

気を取り直してもう一度

$ 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してくれるみたいです。

三度目の正直！

$ 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

成功したっぽい。

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

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

$ 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. クエリを実行してみる

$ 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です。

$ 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も使ってみようと思います。