目次
bqコマンドって何?
bqコマンドとは、Googleの最強DWHであるBigQueryを操作するためのコマンドラインツールのことです。bqコマンドはGoogle Cloud SDKをインストールすることで、利用できるようになります。今回apps-gcpでは、全てのbqコマンドの利用方法を説明します(※1)。
※1 2016/05/12現在の全bqコマンド(v.2.0.24)を解説します。ただし、本記事公開後に解説したコマンドが削除されたり、または新規コマンドが追加される可能がありますのでご注意ください。また、全bqコマンドの概要・利用方法については説明しますが、各コマンド毎のオプションについては重要であると思われるものだけピックアップして解説します。
本記事はBigQuery経験者向けの記事となります。BigQueryの概要を理解したい場合は以下の記事を参考にしてください。
-はじめの一歩が踏み出せない人のためのBigQuery入門
https://apps-gcp.com/bigquery-introduction/
bqコマンド実行環境を構築する手順
bqコマンドの実行環境を構築するための手順を説明します。以下手順に従って実行環境のセットアップをおこなってください。ただし、構築環境はMac OS Xを想定しています。
GCPプロジェクトを作成する
BigQuery(bqコマンド)を利用するためには、最初にGCPプロジェクトの作成が必要となります。以下URLの「GCPプロジェクトの作成(1)~(3)」を参考にプロジェクトの作成をおこなってください。
-GCPプロジェクトの作成
https://apps-gcp.com/bigquery-introduction/#GCP
Google Cloud SDKのセットアップ
Google Cloud SDKのセットアップ手順について説明します。以下の手順に従ってセットアップをおこなってください。
(1) Google Cloud SDKのインストール
ご利用の環境に合わせて、以下URLを参考にCloud SDKのインストールを完了させてください。
-Google Cloud SDKのインストール
https://apps-gcp.com/cloud-sdkinstall-commentary/
(2) 認証コマンドの実行
BigQueryを操作するアカウントで認証処理をおこないます。ユーザ認証はGCPプロジェクト作成時のアカウントをご利用ください。ターミナルを立ち上げ、以下のコマンドを実行してください。
$ gcloud auth login
コマンドを実行すると、認証URLが発行されます。ブラウザから認証URLにアクセスし、「許可」ボタンを押下してください。
$ gcloud auth login
Your browser has been opened to visit:
https://accounts.google.com/o/oauth2/auth redirect_uri=http%3A%2F%2Flocalhost%3A8085%2F&prompt=select_account&response_type=code&client_id=32555940559.apps.googleusercontent.com&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcloud-platform+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fappengine.admin+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcompute&access_type=offline
「You are now logged in as [******@gmail.com].」というメッセージがコマンドライン上に出力されれば認証は成功となります。
$ gcloud auth login
Your browser has been opened to visit:
https://accounts.google.com/o/oauth2/auth redirect_uri=http%3A%2F%2Flocalhost%3A8085%2F&prompt=select_account&response_type=code&client_id=32555940559.apps.googleusercontent.com&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcloud-platform+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fappengine.admin+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcompute&access_type=offline
Saved Application Default Credentials.
You are now logged in as [******@gmail.com].
以下のコマンドを実行し、操作対象のプロジェクトを設定してください。<project>に指定するのは「1. GCPプロジェクトを作成する」の章で作成したプロジェクトのIDとなります。
$ gcloud config set project <project>
bqコマンドの種類を確認する
bqコマンドの種類を確認するのはとても簡単です。ターミナル上で「bq help」を実行してみてください(※2)。サポートされているbqコマンドの一覧が表示されます(赤文字部分)。数えていただければわかる通リ、現在bqコマンドには計19種類のコマンドが存在します。
※2 helpコマンドについては後ほど説明します。
$ bq help
Python script for interacting with BigQuery.
USAGE: bq.py [--global_flags] <command> [--command_flags] [args]Any of the following commands: cancel, cp, extract, head, help, init, insert, load, ls, mk, mkdef, partition, query, rm, shell, show, update, version, wait
...
全てのコマンドを解説してみる
全てのbqコマンドについて解説をおこないます。理解を深めるために、計19個のコマンドを実際に実行することをお勧めします。
helpコマンド
helpコマンドは各コマンドの利用方法を確認するためのコマンドです。helpコマンドを使って「ls」というbqコマンドの利用方法を確認する場合は以下のコマンドを実行します(※3)。
(例1-1)「ls」コマンドの利用方法を確認するコマンド
$ bq help ls
Python script for interacting with BigQuery.
USAGE: bq.py [--global_flags] <command> [--command_flags] [args]・・・①
ls List the objects contained in the named collection.・・・②
List the objects in the named project or dataset. A trailing : or . can
be used to signify a project or dataset.
…(省略)
※3 コマンドを指定しないでhelpコマンドを実行した場合は、全bqコマンドの一覧と各コマンドの利用方法が表示されます。
コマンド(例1-1)を実行すると上記結果が出力されます。①がコマンドのフォーマット、②がコマンドの説明となります。今後bqコマンドの詳細を確認する場合は、helpコマンドを使う癖を身につけましょう。
また、bqコマンドには「global flag(※4)」という各コマンド共通で利用できるオプションのフラグが存在します。global flagを確認する場合は以下のコマンドを実行します。
(例1-2) global flagの詳細確認コマンド
$ bq --help
※4 global flagの詳細について本記事では説明しません。興味がある方は実際に上記コマンドを実行し、フラグの詳細を確認してください。
lsコマンド
lsコマンドは、プロジェクト・データセット・テーブルの一覧を表示するためのコマンドです。例えば、データセットの一覧を表示したい場合は以下のコマンドを実行します。
(例2-1) データセット一覧を出力するコマンド
$ bq ls
datasetId
----------------
dataset_sample
(例2-2) テーブル一覧を出力するコマンド
$ bq ls dataset_sample
tableId Type
--------------- ----------
isono_brother TABLE
isono_family TABLE
(例2-3) プロジェクト一覧を出力するコマンド(※5)
$ bq ls -p
projectId friendlyName
---------------------- ----------------------
morita-demo Morita Demo
※5「projectId」はGCPプロジェクトのID、「friendlyName」はGCPプロジェクトの名称を意味します。
mkコマンド
mkコマンドは、データセットやテーブルを作成するためのコマンドです。LocationはUSまたはEUから選択可能で、指定しない場合はUSがデフォルトで設定されます。データセットの作成が成功したか否かは「bq ls」コマンドでご確認ください。USロケーションにデータセットを作成するコマンドは以下の通リです。
(例3-1) USロケーションにデータセットを作成するコマンド(※6)
$ bq mk --data_location=US new_dataset
以前は、
$ bq mk new_dataset –data_location=US
と説明していましたが、現在では、こちらのコマンドだとエラーメッセージが表示されます。
引数の順番を変更する事でコマンドが通ります。実行する際はご注意下さい。
※6 「new_dataset」は作成するデータセットの名称です。前述した通リ「–data_location」オプションを利用しない場合はUSロケーションにデータセットが作成されます。
テーブルを作成する場合は「bq mk データセットID.作成したいテーブル名」、または「bq –dataset_id=データセットID mk テーブル名」のフォーマットでコマンドを実行します。コマンド例はそれぞれ「例3-2」「例3-2」の通リです。ただし、データセットIDは存在するIDを指定する必要があります。
(例3-2) テーブルの作成コマンド①
$ bq mk new_dataset.new_table
(例3-3) テーブルの作成コマンド②
$ bq --dataset_id=new_dataset mk new_table
rmコマンド
rmコマンドは、データセットやテーブルを削除するためのコマンドです。データセットを削除する場合は以下のコマンドを実行します。
(例4-1) データセットを削除するためのコマンド
$ bq rm dataset_sample
rm: remove dataset 'morita-demo:dataset_sample' (y/N) y
テーブルを削除する場合は「データセットID.テーブル名」のフォーマットでテーブルを指定します。テーブルの削除コマンドは以下の通リです。
(例4-2) テーブルを削除するためのコマンド
$ bq rm dataset_sample.isono_brother
rm: remove table 'morita-demo:dataset_sample3.isono_brother' (y/N) y
データセットと配下に存在する1つ以上のテーブルをまとめて削除したい場合は「-r」オプションを利用します。
(例4-3) データセットとテーブルをまとめて削除するためのコマンド
$ bq rm -r -f dataset_sample
initコマンド
initコマンドはユーザ認証用のコマンドです。コマンドを実行すると、以下のメッセージ(赤文字)が出力されます。どうやら、initコマンドはCloud SDKではもう必要がないコマンドのようです。initコマンドの代わりとして利用されているのが、本記事の「ユーザ認証」の章で説明した「gcloud auth login」になります(※7)。
(例5-1) initコマンドの実行
$ bq init
It looks like you are trying to run "/Users/tomorier/test/test/google-cloud-sdk/bin/bootstrapping/bq.py init".
The "init" command is no longer needed with the Cloud SDK.
...
※7 Google Cloud SDKでは既に必要ないコマンドですので、本記事では上記以上の説明はしません。
headコマンド
headコマンドはテーブルのレコード一覧を出力するためのコマンドです。コマンド例は以下の通リです。
(例6-1) レコード一覧を出力するためのコマンド
$ bq head dataset_sample.isono_family
+-----------+----------+-----+
| firstName | lastName | age |
+-----------+----------+-----+
| tarao | isono | 3 |
| wakame | isono | 9 |
| tama | isono | 9 |
| katsuo | isono | 11 |
| sazae | isono | 24 |
| masuo | isono | 28 |
| fune | isono | 52 |
| namihei | isono | 54 |
+-----------+----------+-----+
※8 行は0からカウントするので「-s」に指定するのは2ではなくて1となります。
(例6-1) テーブルの2行目から3レコードを出力するためのコマンド
$ bq head -s 1 -n 3 dataset_sample.isono_family
+-----------+----------+-----+
| firstName | lastName | age |
+-----------+----------+-----+
| wakame | isono | 9 |
| tama | isono | 9 |
| katsuo | isono | 11 |
+-----------+----------+-----+
extractコマンド
extractコマンドはBigQueryテーブルをGCS上にCSVファイルとして出力するためのコマンドです。コマンド実行例は以下の通リです。
上記テーブルをGCSにCSVファイルとして出力する場合は以下のコマンドを実行します。
(例7-1) GCSへのテーブル出力(CSV)するためのコマンド(※9)
$ bq extract ①dataset_sample2.isono_family_copy ②gs://morita-demo/save.csv
※9 各引数の説明は以下の通リです。
- dataset_sample2.isono_family_copy:出力対象のテーブル
- gs://morita-demo/save.csv:出力先のファイルパス
コマンド(例7-1)実行後、Developerコンソールの「Storage」メニューからファイルの保存を確認すると、以下の通リCSVファイルが出力されています。
GCSから保存したCSVファイル(save.csv)をダウンロードして確認すると、以下の通リ問題なくエクスポートできていることがわかります。
– 出力結果CSV
firstName,lastName,age
tarao,isono,3
wakame,isono,9
tama,isono,9
katsuo,isono,11
sazae,isono,24
masuo,isono,28
fune,isono,52
namihei,isono,54
cpコマンド
cpコマンドは、テーブルをコピーするためのコマンドです。テーブルは同一データセット内だけでなく、もちろん他のデータセットにもコピー可能です。以下は異なるデータセット間でテーブルをコピーするコマンド例です。以下の例では、データセット「dataset_sample」のテーブル「isono_family」を、異なるデータセット「dataset_sample2」にコピーしています。
(例8-1) 異なるデータセット間でテーブルをコピーするためのコマンド
$ bq cp dataset_sample.isono_family dataset_sample2.isono_family_copy
Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (0s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (1s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (2s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (4s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (5s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (6s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (7s) Current status: R Waiting on bqjob_r19aca8327cb96cb2_00000154a5449987_1 ... (7s) Current status: DONE Tables 'morita-demo:dataset_sample.isono_family' successfully copied to 'morita-demo:dataset_sample2.isono_family_copy'
– headコマンドによるテーブルのレコード確認
$ bq head dataset_sample2.isono_family_copy
+-----------+----------+-----+
| firstName | lastName | age |
+-----------+----------+-----+
| tarao | isono | 3 |
| wakame | isono | 9 |
| tama | isono | 9 |
| katsuo | isono | 11 |
| sazae | isono | 24 |
| masuo | isono | 28 |
| fune | isono | 52 |
| namihei | isono | 54 |
+-----------+----------+-----+
insertコマンド
insertコマンドはテーブルにレコードを挿入するためのコマンドです。実行例は以下の通リです。
レコード追加対象のテーブルは上記とします。まずは挿入するレコードをjsonファイルに保存します。JSONファイルの作成は、以下のUnix(Linux)コマンドから実行します。
– 追加レコード用JSONファイルの作成
$ echo ‘{"firstName":"norisuke","lastName":"irie","age":24}’ > add_isono_family.json
(例9-1) 追加レコードをテーブルにinsertするためのコマンド
$ bq insert dataset_sample2.isono_family_copy /Users/tomorier/add_isono_family.json
– headコマンドによるテーブルのレコード確認
$ bq head dataset_sample2.isono_family_copy
+-----------+----------+-----+
| firstName | lastName | age |
+-----------+----------+-----+
| tarao | isono | 3 |
| wakame | isono | 9 |
| tama | isono | 9 |
| katsuo | isono | 11 |
| sazae | isono | 24 |
| masuo | isono | 28 |
| fune | isono | 52 |
| namihei | isono | 54 |
| norisuke | irie | 24 |
+-----------+----------+-----+
loadコマンド
loadコマンドはローカル、またはGCS上のCSVデータをテーブルデータとしてインポートするためのコマンドです。
– isono_family.csv
namihei,isono,54
fune,isono,52
wakame,isono,9
katsuo,isono,11
sazae,isono,24
masuo,isono,28
tarao,isono,3
tama,isono,9
(例10-1) GCS上のCSVファイルをBigQueryのテーブルとしてインポートするコマンド(※10)
$ bq load ①dataset_sample.isono_family ②gs://morita-demo/isono_family.csv ③firstName:string,lastName:string,age:integer
Waiting on bqjob_r6e3f12e8ef39425c_00000154a3ecd2d3_1 ... (9s) Current status: DONE
※10 コマンドの各引数の説明は以下の通リです。
- 「dataset_sample.isono_family」:作成するテーブル名
- 「gs://morita-demo/isono_family.csv」:インポートをおこなうGCSファイルのパス
- 「firstName:string,lastName:string,age:integer」:テーブルスキーマ
queryコマンド
queryコマンドは、BigQueryに対してクエリを発行するためのコマンドです。コマンド例の説明は以下の通リです。
例えば、上記のようなBigQueryテーブル(データセット名:dataset_sample/テーブル名:isono_brother)がある場合、下記のコマンドを実行します。
(例11-1) BigQueryテーブルに対するクエリ発行をおこなうためのコマンド
$ bq query "select * from dataset_sample.isono_brother"
Waiting on bqjob_rb79ed148990c574_00000154a3670a87_1 ... (0s) Current status: DONE
+-----------+----------+
| firstName | lastName |
+-----------+----------+
| sazae | fuguta |
| katsuo | isono |
| wakame | isono |
+-----------+----------+
shellコマンド
shellコマンドはターミナル環境をbqコマンド専用のシェル環境に切り替えるためのコマンドです。以下はコマンドの実行例です。
(例12-1) bqコマンドのシェル環境に切り替えるためのコマンド
$ bq shell
Welcome to BigQuery! (Type help for more information.)
morita-demo>
morita-demo> ls
datasetId
-----------------
dataset_sample
dataset_sample2
morita-demo>
showコマンド
showコマンドはデータセットやテーブルの情報を表示するためのコマンドです。データセットの情報を確認したい場合は以下のコマンドを実行します。それでは実際にコマンドを実行してみましょう。
(例13-1) データセット情報を確認するためのコマンド
$ bq show dataset_sample
Dataset morita-demo:dataset_sample
Last modified ACLs
----------------- ------------------
12 May 14:41:23 Owners:
projectOwners
Writers:
projectWriters
Readers:
projectReaders
(例13-2) デーブル情報を確認するためのコマンド
Last modified Schema Total Rows Total Bytes Expiration
----------------- ---------------------- ------------ ------------- ------------
12 May 14:17:34 |- firstName: string 3 45
|- lastName: string
updateコマンド
updateコマンドはデータセットやテーブルの情報を更新するためのコマンドです。例えば、データセットの説明(description)を変更したい場合は以下のコマンドを実行します。
(例14-1) データセットの説明文を更新するためのコマンド
$ bq update --description 'new description' dataset_sample
Dataset 'morita-demo:dataset_sample' successfully updated.
テーブルの説明文を更新する場合は以下のコマンドを実行します。
(例14-2) テーブルのスキーマを更新するためのコマンド(※11)
$ bq update --description 'new description' dataset_sample.isono_brother
Dataset 'morita-demo:dataset_sample' successfully updated.
※11 その他更新可能なパラメータを確認したい場合はhelpコマンドで確認してください。
versionコマンド
versionコマンドはbqコマンドのバージョンを出力するためのコマンドです。以下はコマンドの実行例です。
(例15-1) bqコマンドのバージョンを確認するためのコマンド
$ bq version
This is BigQuery CLI 2.0.24
mkdefコマンド
mkdefコマンドはGCS上にバックアップされたBigQueryデータをJSONフォーマットで出力するためのコマンドです。
firstName,lastName,age
tarao,isono,3
wakame,isono,9
tama,isono,9
katsuo,isono,11
sazae,isono,24
masuo,isono,28
fune,isono,52
namihei,isono,54
norisuke,irie,24
(例16-1) テーブルデータをGCS上に出力するためのコマンド
$ bq mkdef gs://morita-demo/output.csv firstName:string,lastName:string,age:integer
{
"csvOptions": {
"allowJaggedRows": false,
"allowQuotedNewlines": false,
"encoding": "UTF-8",
"fieldDelimiter": ",",
"quote": "\"",
"skipLeadingRows": 0
},
"schema": {
"fields": [
{
"name": "firstName",
"type": "STRING"
},
{
"name": "lastName",
"type": "STRING"
},
{
"name": "age",
"type": "INTEGER"
}
]
},
"sourceFormat": "CSV",
"sourceUris": [
"gs://morita-demo/output.csv"
]
}
cancelコマンド
cancelコマンドはBigQueryの実行ジョブをキャンセルするためのコマンドです。例えば、以下のような実行中のジョブがあるとします。
$ bq ls -j
jobId Job Type State Start Time Duration
-------------------------------------------- ---------- --------- ----------------- ----------
bqjob_r240ff03af9ba9dd6_00000154c8dae8a3_1 extract RUNNING 19 May 20:51:26
(例17-1) Jobをキャンセルするためのコマンド
$ bq cancel bqjob_r240ff03af9ba9dd6_00000154c8dae8a3_1
waitコマンド
waitコマンドはBigQuery実行ジョブの完了を待機するためのコマンドです。以下はサンプルコマンドです。ステータスがDONE(完了)になると、waitコマンドは終了します。
(例18-1) Jobの実行完了を待機するためのコマンド
$ bq wait bqjob_r78265b1f2e755f05_00000154c91d0a40_1
Waiting on bqjob_r78265b1f2e755f05_00000154c91d0a40_1 ... (25s) Current status: DONE
Job morita-demo:bqjob_r78265b1f2e755f05_00000154c91d0a40_1
Job Type State Start Time Duration User Email Bytes Processed Bytes Billed Billing Tier
---------- --------- ----------------- ---------- ---------------------- ----------------- -------------- --------------
extract SUCCESS 19 May 22:03:44 0:00:11 morita@yoshidumi.co.jp
(例18-2) Jobの実行を100秒待機するためのコマンド
$ bq wait bqjob_r78265b1f2e755f05_00000154c91d0a40_1 100
partitionコマンド
このpartitionコマンドを使用することで、既に日付別に作られた一連のテーブル(test_20180101、test_20180102…)を単一の分割テーブルに変換することができます。
(例19-1) 一連の日付別テーブルをpartitionedという単一の分割テーブルに変換
$ bq partition dataset:test_ dataset:partitioned
Copying 2 source partitions to test-project:dataset.partitioned
Creating table: test-project:dataset.partitioned with schema from test-project:dataset.test_20180102 and partition spec {'type': 'DAY'}
test-project:dataset.partitioned successfully created.
Copying test-project:dataset.test_20180101 to test-project:dataset.partitioned$20180101
Waiting on bqjob_r167cceadbb9ebb84_000001622dcf78f0_120180101 ... (0s) Current status: DONE
Successfully copied test-project:dataset.test_20180101 to test-project:dataset.partitioned$20180101
Copying test-project:dataset.test_20180102 to test-project:dataset.partitioned$20180102
Waiting on bqjob_r167cceadbb9ebb84_000001622dcf78f0_120180102 ... (0s) Current status: DONE
Successfully copied test-project:dataset.test_20180102 to test-project:dataset.partitioned$20180102
まとめ
以上で全bqコマンドについての説明は終了となります。如何でしたでしょうか。BigQueryの操作を普段Webのコンソールからおこなっている方も、コマンドラインからの操作に興味を持たれたのではないでしょうか。bqコマンドはとても便利なツールなので、慣れてしまえばもうWebコンソールには戻れなくなると思います。快適なBigQueryライフを送るために、ぜひbqコマンドを極めましょう!!