LoginSignup
28
19

More than 3 years have passed since last update.

@chan-p(chan-p)

【BigQuery】bqコマンド チートシート

Last updated at Posted at 2020-07-29

はじめに

チームでBigQueryをシステムに導入しようとなり、本格的に向き合うことになりそうなので一通りbqコマンドを触ってみようと思います。

以前書いた記事では、レガシーな使い方をしていたりしたのでアップデートしたいと思います。
【BigQuery】知っておくと便利なbqコマンドのオプション【GCP】【bq】

チートシート

前提として下記の情報を使っています。

  • データセット名：data_qiita
  • サービスアカウント名：test@[Project名].iam.gserviceaccount.com
  • 暗号鍵名：projects/[Project名]/locations/asia-northeast1/keyRings/[KeyRing名]/cryptoKeys/[Key名]
  • ロケーション：asia-northeast1

bqの省略

$ bq shell
Welcome to BigQuery! (Type help for more information.)
[プロジェクト名]>select * from data_qiita.test_202007

データセット/テーブルの一覧表示

$ bq ls
  datasetId  
 ----------- 
  data_qiita   
  g90001

$ bq ls data_qiita
    tableId     Type    Labels   Time Partitioning   Clustered Fields  
 ------------- ------- -------- ------------------- ------------------ 
  test_202006   TABLE                                                  
  test_202007   TABLE

-n

  • デフォルトで50行くらいが制限になっているため、このオプションでさらに表示を増やせる
$bq ls -n 1000 data_qiita
    tableId     Type    Labels   Time Partitioning   Clustered Fields  
 ------------- ------- -------- ------------------- ------------------ 
  test_20200601   TABLE
    ・・・・・                                                  
  test_20201031   TABLE

ジョブ履歴の表示

$ bq ls --jobs
                    jobId                      Job Type    State      Start Time         Duration     
 -------------------------------------------- ---------- --------- ----------------- ---------------- 
  bqjob_r350795b155d507d6_0000017395de7f89_1   load       SUCCESS   28 Jul 14:40:54   0:00:03.013000  
  bqjob_r2228f453d05a1384_000001739589af1a_1   extract    SUCCESS   28 Jul 13:08:15   0:00:03.866000  
  bqjob_r1f064a9ce743da3f_000001739589a25d_1   query      SUCCESS   28 Jul 13:08:12   0:00:00.240000  
  bqjob_r7acf7abdad16d904_000001739589961a_1   query      SUCCESS   28 Jul 13:08:09   0:00:00.185000

ジョブの詳細履歴の表示

$ bq show --job [Project名]:asia-northeast1.[jobID]
Job [Project名]:asia-northeast1.[jobID]

  Job Type    State      Start Time         Duration                           User Email                        Bytes Processed   Bytes Billed   Billing Tier   Labels  
 ---------- --------- ----------------- ---------------- ------------------------------------------------------ ----------------- -------------- -------------- -------- 
  load       SUCCESS   28 Jul 14:40:54   0:00:03.013000   test@[Project名].iam.gserviceaccount.com

データセットを作成

$ bq mk --dataset --location=asia-northeast1 data_qiita
Dataset '[Project名]:data_qiita' successfully created.

--use_legacy_sql=false

  • 作成したデータセット配下のテーブルでデフォルトがstadardSQLになる

--default_kms_key

  • 指定した鍵でデータが暗号化される
$ bq mk --dataset --location=asia-northeast1 \
  --use_legacy_sql=false \
  --default_kms_key=projects/[Project名]/locations/asia-northeast1/keyRings/[KeyRing名]/cryptoKeys/[Key名] 
　data_qiita
Dataset '[Project名]:data_qiita' successfully created.

--force

  • 同一名のデータセットがすでに存在していてもエラーにならない
$ bq mk --dataset data_qiita
BigQuery error in mk operation: Dataset '[Project名]:data_qiita' already exists.
$ bq mk --dataset --force data_qiita
Dataset '[Project名]:data_qiita' already exists.

空のテーブルの作成

$ bq mk --table --location=asia-northeast1 data_qiita.test_202008
Table '[Project名]:data_qiita.test_202008' successfully created.

--schema

  • スキーマを定義したファイルを指定してテーブル作成
$ bq mk --table --location=asia-northeast1 --schema=schema.json data_qiita.test_202009
Table '[Project名]:data_qiita.test_202009' successfully created.
schema.json
[
  {
    "description":"",
    "name":"key",
    "type":"INTEGER",
    "mode":""
  },
  {
    "description":"",
    "name":"word",
    "type":"STRING",
    "mode":""
  }
]

データをロード

bq load --source_format=AVRO data_qiita.test_202010 gs://qiita/[ファイルパス]

--autodetect

  • 読み込みファイルから自動でスキーマも読み込む
bq load --source_format=CSV --autodetect data_qiita.test_202010 gs://qiita/[ファイルパス].csv

--replace

  • テーブル構造をそのものを上書きしてロードする
bq load --source_format=CSV --replace data_qiita.test_202010 gs://qiita/[ファイルパス].csv

--schema_update_option=ALLOW_FIELD_ADDITION

  • ロードする際にテーブル構造が変わっても自動でカラムを追加してロードする
bq load --source_format=CSV --schema_update_option=ALLOW_FIELD_ADDITION data_qiita.test_202010 gs://qiita/[ファイルパス].csv

データセットのメタデータを表示

$ bq show data_qiita
Dataset [Project名]:data_qiita

   Last modified                             ACLs                             Labels                                                      kmsKeyName                                                      
 ----------------- --------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------- 
  28 Jul 13:17:13   Owners:                                                            projects/{Project名}/locations/asia-northeast1/keyRings/[KeyRing名]/cryptoKeys/[Key名]  
                      test@[Project名].iam.gserviceaccount.com,                                                                                                                               
                      projectOwners                                                                                                                                                                       
                    Writers:                                                                                                                                                                              
                      projectWriters                                                                                                                                                                      
                    Readers:                                                                                                                                                                              
                      projectReaders

テーブルのメタデータを表示

$ bq show data_qiita.tmp2_202010
Table [Project名]:data_qiita.tmp2_202010

   Last modified        Schema        Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Labels                                                      kmsKeyName                                                      
 ----------------- ----------------- ------------ ------------- ------------ ------------------- ------------------ -------- ------------------------------------------------------------------------------------------------------------------- 
  28 Jul 16:02:51   |- f0_: integer   1            8

データセットの権限の一覧表示

$ bq show --format=prettyjson data_qiita > qiita.json
qiita.json
{
  "access": [
    {
      "role": "WRITER", 
      "specialGroup": "projectWriters"
    }, 
    {
      "role": "OWNER", 
      "specialGroup": "projectOwners"
    }, 
    {
      "role": "OWNER", 
      "userByEmail": "test@[Project名].iam.gserviceaccount.com"
    }, 
    {
      "role": "READER", 
      "specialGroup": "projectReaders"
    }
  ], 
  "creationTime": "1595942233609", 
  "datasetReference": {
    "datasetId": "data_qiita, 
    "projectId": [Project名]
  }, 
  "defaultEncryptionConfiguration": {
    "kmsKeyName": "projects/[Project名]/locations/asia-northeast1/rojects/{Project名}/locations/asia-northeast1/keyRings/[KeyRing名]/cryptoKeys/[Key名]"
  }
}

データセットの権限を更新

$ bq update --source=qiita_re.json data_qiita
Dataset '[Project名]:data_qiita' successfully updated.
qiita_re.json
{
  "access": [
    {
      "role": "WRITER", 
      "specialGroup": "projectWriters"
    }, 
    {
      "role": "OWNER", 
      "specialGroup": "projectOwners"
    }, 
    {
      "role": "OWNER", 
      "userByEmail": "test@[Project名].iam.gserviceaccount.com"
    }
  ], 
  "creationTime": "1595942233609", 
  "datasetReference": {
    "datasetId": "data_qiita, 
    "projectId": [Project名]
  }, 
  "defaultEncryptionConfiguration": {
    "kmsKeyName": "projects/[Project名]/locations/asia-northeast1/rojects/{Project名}/locations/asia-northeast1/keyRings/[KeyRing名]/cryptoKeys/[Key名]"
  }
}

クエリを投げる

$ bq query 'select count(*) from data_qiita.test_202010'
Waiting on bqjob_r292b672551aa59b9_0000017395f86254_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|  27 |
+-----+
  • SQLファイルを読み込ませる
$ bq query < test.sql 
Waiting on bqjob_r44bc1c144c0c51b_0000017395fb2daf_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|  27 |
+-----+
test.sql
select count(*) from data_qiita.test_202010

--use_legacy_sql=false

  • データセットをレガシーオフにしていない場合、legacySQLがデフォルトになる
  • stadardSQLを使用する
bq query --use_lecacy_sql=false 'select count(*) from data_qiita.test_202010'

--destination_table

  • クエリの結果をテーブルに出力する
$ bq query --destination_table=data_qiita.tmp_202010 'select count(*) from data_qiita.test_202010'
Waiting on bqjob_r440946d3cc02908d_0000017395ff9411_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|  27 |
+-----+

$ bq ls data_qiita
    tableId     Type    Labels   Time Partitioning   Clustered Fields  
 ------------- ------- -------- ------------------- ------------------ 
  test_202009   TABLE                                                  
  test_202010   TABLE                                                  
  tmp_202010    TABLE     

$ bq head data_qiita.tmp_202010
+-----+
| f0_ |
+-----+
|  27 |
+-----+

--dry_run

  • 実際に実行はされずにデータのスキャン量が計測できる
$ bq ls --jobs
                    jobId                      Job Type    State      Start Time         Duration     
 -------------------------------------------- ---------- --------- ----------------- ---------------- 
  bqjob_r440946d3cc02908d_0000017395ff9411_1   query      SUCCESS   28 Jul 15:17:02   0:00:00.738000  
  bqjob_r79c9d4c27d870e48_0000017395ff6947_1   query      FAILURE   28 Jul 15:16:51   0:00:00.381000 

$ bq query --dry_run 'select * from data_qiita.test_202010'
Query successfully validated. Assuming the tables are not modified, running this query will process 41478 bytes of data.

$ bq ls --jobs
                    jobId                      Job Type    State      Start Time         Duration     
 -------------------------------------------- ---------- --------- ----------------- ---------------- 
  bqjob_r440946d3cc02908d_0000017395ff9411_1   query      SUCCESS   28 Jul 15:17:02   0:00:00.738000  
  bqjob_r79c9d4c27d870e48_0000017395ff6947_1   query      FAILURE   28 Jul 15:16:51   0:00:00.381000

--maximum_bytes_billed

  • クエリに対して課金されるバイト数を制限する
$ bq query --maximum_bytes_billed=3000  'select * from data_qiita.test_202010'
Waiting on bqjob_r49b23566f45a238d_000001739605edb8_1 ... (0s) Current status: DONE   
BigQuery error in query operation: Error processing job '[Project名]:bqjob_r49b23566f45a238d_000001739605edb8_1': Query exceeded limit for bytes billed: 3000. 10485760 or higher required.

--parameter

  • クエリパラメータを含むSQLを実行できる
$ bq query 'select count(*) from data_qiita.test_202010 where principalEmail="test@[Project名].iam.gserviceaccount.com"'
Waiting on bqjob_r6f551d87c5aa84fb_00000173960c7db9_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|   8 |
+-----+

$ bq query --use_legacy_sql=false --parameter=param::test@[Project名].iam.gserviceaccount.com < test_param.sql
Waiting on bqjob_r486bc94d854a2b67_0000017396109223_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|   8 |
+-----+
test_param.sql
select count(*) from data_qiita.test_202010 where principalEmail = @param

テーブルの数行を表示

$ bq head data_qiita.tmp_202010
+-----+
| f0_ |
+-----+
|  27 |
+-----+

ジョブIDから結果を取得

$ bq head --job [Project名]:asia-northeast1.[jobID]
+-----+
| f0_ |
+-----+
|   8 |
+-----+

テーブルの外部への出力

$ bq extract --location=asia-northeast1 --destination_format=AVRO data_qiita.test_202010 gs://qiita/[ファイル名].avro

--print_header

  • カラム名が出力されなくなる
$ bq extract --location=asia-northeast1 --destination_format=AVRO data_qiita.test_202010 gs://qiita/[ファイル名].avro

テーブルのコピー

$ bq ls data_qiita
    tableId     Type    Labels   Time Partitioning   Clustered Fields  
 ------------- ------- -------- ------------------- ------------------ 
  test_202010   TABLE                                                  
  tmp_202010    TABLE                                                  

$ bq cp data_qiita.tmp_202010 data_qiita.tmp2_202010
Waiting on bqjob_r6c94a687e178598a_000001739629836d_1 ... (0s) Current status: DONE   
Table '[Project名]:data_qiita.tmp_202010' successfully copied to '[Project名]:data_qiita.tmp2_202010'

$ bq ls data_qiita
    tableId     Type    Labels   Time Partitioning   Clustered Fields  
 ------------- ------- -------- ------------------- ------------------ 
  test_202010   TABLE                                                  
  tmp2_202010   TABLE                                                  
  tmp_202010    TABLE

テーブルの削除

bq rm data_qiita.test_202008
rm: remove table '[Project名]:data_qiita.test_202008'? (y/N) y

--force

  • 確認されず削除される
$ bq rm --force data_qiita.test_202009

データセットの削除

$ bq rm --dataset data_qiita
rm: remove dataset '[Project名]:data_qiita'? (y/N) y

--recursive

$ bq rm data_qiita
rm: remove dataset '[Project名]:data_qiita'? (y/N) y
BigQuery error in rm operation: Dataset [Project名]:data_qiita is still in use

$ bq rm --recursive data_qiita
rm: remove dataset '[Project名]:data_qiita'? (y/N) y

ジョブのキャンセル

$ bq cancel [Project名].asia-northeast1.[jobID]

--sync=false

  • すぐにコマンドの結果が返される
  • たまにキャンセルしてもすぐに停止しないジョブが現れるためこのオプションを適用するとキャンセルされる
$ bq cancel --sync [Project名].asia-northeast1.[jobID]

参考

コマンドラインツールリファレンス

28
19
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
28
19