$ bq
Python script for interacting with BigQuery.
USAGE: bq.py [--global_flags] <command> [--command_flags] [args]
Any of the following commands:
cancel, cp, extract, get-iam-policy, head, help, init, insert, load, ls, mk, mkdef, partition, query, rm, set-iam-policy, shell, show, update, version, wait
cancel Request a cancel and waits for the job to be cancelled.
# (以下省略)
Run 'bq.py --help' to get help for global flags.
Run 'bq.py help <command>' to get help for <command>.
試しにbq --helpを打ってみると、
$ bq --help
Python script for interacting with BigQuery.
USAGE: bq.py [--global_flags] <command> [--command_flags] [args]
Global flags:
/<PATH TO SCRIPT>/bq.py:
--api: API endpoint to talk to.
(default: 'https://www.googleapis.com')
--api_version: API version to use.
# (以下省略)
$ sw_vers
ProductName: Mac OS X
ProductVersion: 10.15.3
BuildVersion: 19D76
$ bq version
This is BigQuery CLI 2.0.54
今回扱う確認用データセットの作成
まずはデータセットを作成して、検証用のデータを登録します。
$ bq query <<-EOF
> CREATE TABLE test_kj.sample
> AS
> SELECT 1 AS id, 'a' AS key1, 'aa' AS key2, 'aaaa' AS key3, 'aaaaaaaa' AS key4
> UNION ALL
> SELECT 2 AS id, 'b' AS key1, 'bb' AS key2, 'bbbb' AS key3, 'bbbbbbbb' AS key4
> UNION ALL
> SELECT 3 AS id, 'c' AS key1, 'cc' AS key2, 'cccc' AS key3, 'cccccccc' AS key4
> UNION ALL
> SELECT 4 AS id, 'd' AS key1, 'dd' AS key2, 'dddd' AS key3, 'dddddddd' AS key4
> EOF
Waiting on bqjob_r1367xxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1 ... (0s) Current status: DONE
Created 【Project Name】.test_kj.sample
$ bq query --dry_run "SELECT * FROM test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 124 bytes of data.
--dry_runオプションをつけ忘れた場合、後で読み込んだbyte数を確認するには、ジョブIDがわかっていればbq show -j <ジョブID>で確認できます。
$ bq show -j bqjob_r1367xxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1
Job 【Project Name】:bqjob_r1367xxxxxxxxxxxxxxxxxxxxxxxxxxxxx_1
Job Type State Start Time Duration User Email Bytes Processed Bytes Billed Billing Tier Labels
---------- --------- ----------------- ---------------- --------------------------------------------- ----------------- -------------- -------------- --------
query SUCCESS 14 Mar 17:57:16 0:00:00.927000 account@【Project Name】.iam.gserviceaccount.com 0 0 1
Created 【Project Name】.test_kj.sample
$ bq query --dry_run "SELECT * FROM test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 124 bytes of data.
$ bq query --dry_run "SELECT id, key1 FROM test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 44 bytes of data.
$ bq query --dry_run "SELECT id, key1 FROM test_kj.sample WHERE id = 1"
Query successfully validated. Assuming the tables are not modified,
running this query will process 44 bytes of data.
$ bq query --dry_run "SELECT id, key1 FROM test_kj.sample LIMIT 1"
Query successfully validated. Assuming the tables are not modified,
running this query will process 44 bytes of data.
$ bq query --dry_run "SELECT COUNT(*) FROM test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 0 bytes of data.
$ bq query --dry_run "SELECT COUNT(1) FROM test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 0 bytes of data.
$ bq query --dry_run "SELECT COUNT(id) FROM test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 32 bytes of data.
$ bq query --dry_run "DELETE FROM test_kj.sample WHERE TRUE"
Query successfully validated. Assuming the tables are not modified,
running this query will process 124 bytes of data.
てカンジで、全レコードのサイズが課金対象になります。
$ bq query --dry_run "DROP TABLE test_kj.sample"
Query successfully validated. Assuming the tables are not modified,
running this query will process 0 bytes of data.