BigQueryのbqコマンドを全部試してみたので解説する

$ gcloud auth login

$ 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

$ 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].

$ gcloud config set project <project>

$ 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 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.
…(省略)

$ bq --help

$ bq ls
datasetId
----------------
dataset_sample

$ bq ls dataset_sample
tableId Type
--------------- ----------
isono_brother TABLE
isono_family TABLE

$ bq ls -p
projectId friendlyName
---------------------- ----------------------
morita-demo Morita Demo

$ bq mk --data_location=US new_dataset

$ bq mk new_dataset.new_table

$ bq --dataset_id=new_dataset mk new_table

$ bq rm dataset_sample
rm: remove dataset 'morita-demo:dataset_sample' (y/N) y

$ bq rm dataset_sample.isono_brother
rm: remove table 'morita-demo:dataset_sample3.isono_brother' (y/N) y

$ bq rm -r -f dataset_sample

$ 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.
...

$ 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 |
+-----------+----------+-----+

$ bq head -s 1 -n 3 dataset_sample.isono_family
+-----------+----------+-----+
| firstName | lastName | age |
+-----------+----------+-----+
| wakame | isono | 9 |
| tama | isono | 9 |
| katsuo | isono | 11 |
+-----------+----------+-----+

$ bq extract ①dataset_sample2.isono_family_copy ②gs://morita-demo/save.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

$ 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'

$ 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 |
+-----------+----------+-----+

$ echo ‘{"firstName":"norisuke","lastName":"irie","age":24}’ > add_isono_family.json

$ bq insert dataset_sample2.isono_family_copy /Users/tomorier/add_isono_family.json

$ 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 |
+-----------+----------+-----+

namihei,isono,54
fune,isono,52
wakame,isono,9
katsuo,isono,11
sazae,isono,24
masuo,isono,28
tarao,isono,3
tama,isono,9

$ 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

$ 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 |
+-----------+----------+

$ bq shell
Welcome to BigQuery! (Type help for more information.)
morita-demo>

morita-demo> ls
datasetId
-----------------
dataset_sample
dataset_sample2
morita-demo>

$ bq show dataset_sample
Dataset morita-demo:dataset_sample
Last modified ACLs
----------------- ------------------
12 May 14:41:23 Owners:
projectOwners
Writers:
projectWriters
Readers:
projectReaders

Last modified Schema Total Rows Total Bytes Expiration
----------------- ---------------------- ------------ ------------- ------------
12 May 14:17:34 |- firstName: string 3 45
|- lastName: string

$ bq update --description 'new description' dataset_sample
Dataset 'morita-demo:dataset_sample' successfully updated.

$ bq update --description 'new description' dataset_sample.isono_brother
Dataset 'morita-demo:dataset_sample' successfully updated.

$ bq version

This is BigQuery CLI 2.0.24

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

$ 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"
]
}

$ bq ls -j

jobId Job Type State Start Time Duration
-------------------------------------------- ---------- --------- ----------------- ----------
bqjob_r240ff03af9ba9dd6_00000154c8dae8a3_1 extract RUNNING 19 May 20:51:26

$ bq cancel bqjob_r240ff03af9ba9dd6_00000154c8dae8a3_1

$ 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

$ bq wait bqjob_r78265b1f2e755f05_00000154c91d0a40_1 100

$ 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