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

26647 ワード

BQ BigQuery gcloud gcloud テキストリンク

はじめに

チームで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]

参考

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

Author And Source

この問題について(【BigQuery】bqコマンドチートシート), 我々は、より多くの情報をここで見つけました https://qiita.com/chan-p/items/5c3e6c10ed68650e8da3

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .

Visual Studio 2010およびC#4.0のプレビュー版リソースリンク

classloaderまとめ

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

はじめに

チートシート

bqの省略

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

-n

ジョブ履歴の表示

ジョブの詳細履歴の表示

データセットを作成

--use_legacy_sql=false

--default_kms_key

--force

空のテーブルの作成

--schema

データをロード

--autodetect

--replace

--schema_update_option=ALLOW_FIELD_ADDITION

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

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

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

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

クエリを投げる

--use_legacy_sql=false

--destination_table

--dry_run

--maximum_bytes_billed

--parameter

テーブルの数行を表示

ジョブIDから結果を取得

テーブルの外部への出力

--print_header

テーブルのコピー

テーブルの削除

--force

データセットの削除

--recursive

ジョブのキャンセル

--sync=false

参考

Author And Source

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