はじめに
最近、BigQueryを触っています。
bqコマンドでコマンドラインからアクセスする際の自分メモです。
この記事で使うschemaやcsvファイルは以下のリポジトリにおいてあります。
* https://github.com/tweeeety/bq-command-sample
アジェンダ
1. bqコマンドつかう前の確認
bqコマンドを使いはじめるにあたり確認です。
# bqコマンドのversionを確認 $ bq version This is BigQuery CLI 2.0.65 # projectを確認 $ gcloud config list [compute] region = asia-northeast1 zone = asia-northeast1-b [core] account = [your account] disable_usage_reporting = False project = [your project] Your active configuration is: [default] # 設定されていない場合はprojectを設定してから行う gcloud config set project [project name]
bqコマンドを使うには、cloud SDKをインストールする必要があります。
もしbq version
でコマンドが確認できない場合は、以下2点をやってみてください。
- cloud SDKをインストール
- Google Cloud Consoleでのプロジェクトの作成
- gcloudコマンドでのログイン
2. bqコマンドつかう前の設定
フラグ
bqコマンドは大きく2つのフラグがあります。
- グローバル フラグ(共通フラグ): すべてのコマンドで使用可
- コマンド固有のフラグ: 特定のコマンドに適用される
詳しくは以下をご参照ください。
* コマンドライン ツール リファレンス
.bigqueryrc
bqコマンドは予めフラグ(コマンドのオプション)を設定しておくことができます。
vim $HOME/.bigqueryrc
に設定することで毎回オプションを指定せずに実行できるようになります。
グローバルフラグは直で、
コマンド固有のフラグは[command]でセクションをきって指定します。
$ vim ~/.bigqueryrc ---- vim ---- --credential_file="[path to file]/key.json" [query] --use_legacy_sql=false [mk] --use_legacy_sql=false -------------
デバッグ フラグ
特に使いそうなbqフラグにログとステータスを表示することものがあります。
うまくいかないときは設定してみると良いでしょう。
- --apilog=path_to_file: ログ・ファイルを指定
- --format=prettyjson: レスポンスをJSON形式で出力
3. bqコマンドでprojectの確認
# projectの確認
$ gcloud config list
4. bqコマンドでdatasetの作成/確認
bqコマンドでデータセットを作成します。
# 東京:asia-northeast1 にデータセットを作成 $ bq mk --data_location=asia-northeast1 bq_command_dataset Dataset '[your project]:bq_command_dataset' successfully created. # データセットの確認 $ bq ls datasetId -------------------- hogehoge fugafuga bq_command_dataset
5. bqコマンドでパーティション無し/有りtableの作成
bqコマンドにてテーブルを作成します。
schemaはjson形式にて記述したローカルのファイルを参照できます。
まずは、bqコマンドにてパーティション無し
のテーブルを作成します。
$ bq mk \ --table \ --description "no partition table" \ --label hoge:fuga \ --schema ./schema.json \ hoge-project:bq_command_dataset.bq_command_no_pt_table
以下は、今回のschema.json
のサンプルです。
BigQueryでのデータ型は以下を参照してください。
* 標準 SQL のデータ型
$ cat schema.json [ { "mode": "NULLABLE", "name": "employee_id", "type": "STRING" }, { "mode": "NULLABLE", "name": "name", "type": "STRING" }, { "mode": "NULLABLE", "name": "attendance", "type": "STRING" }, { "mode": "NULLABLE", "name": "partition_date", "type": "date" } ]
テーブルが作成されました。
consoleでもデータセットとテーブルが確認できます。
また、Details
タブを開くとno partition table
と表示されてるのが確認できます。
次に、bqコマンドにてパーティション有り
のテーブルを作成してみます。
$ bq mk \ --table \ --description hogehoge \ --schema ./schema.json \ --label hoge:piyo\ --require_partition_filter \ --time_partitioning_field partition_date \ --time_partitioning_type DAY \ hoge-project:bq_command_dataset.bq_command_pt_table
上記フラグはこんな感じの意味です。
--require_partition_filter
: クエリを実行するパーティションを指定するためにWHERE句の使用を必須にする。--time_partitioning_type
: DAY/HOUR/MONTH/YEARを指定。デフォルトDAY。--time_partitioning_field
: パーティションに利用するカラムの指定
再度、consoleにて確認してみます。
また、details
タブをひらくと、今度はパーティションについての情報が表示されるのが確認できます。
6. bqコマンドでdataset/table/パーティション情報の確認
consoleで確認しても良いですが、
毎回ブラウザを開いてはリロードで確認するのは面倒です。
そこで、コマンドラインにてbq show
でテーブルの情報を確認します。
# dataset/table情報の確認 $ bq show \ --format=prettyjson \ hoge-project:bq_command_dataset.bq_command_pt_table { "creationTime": "1617024639811", "description": "hogehoge", "etag": "Q/5/uQq/jZawUbYZchsKvw==", "id": "hoge-project:bq_command_dataset.bq_command_pt_table", "kind": "bigquery#table", "labels": { "hoge": "piyo--require_partition_filter" }, "lastModifiedTime": "1617025188641", "location": "asia-northeast1", "numBytes": "168", "numLongTermBytes": "0", "numRows": "8", "schema": { "fields": [ { "mode": "NULLABLE", "name": "employee_id", "type": "STRING" }, { "mode": "NULLABLE", "name": "name", "type": "STRING" }, { "mode": "NULLABLE", "name": "attendance", "type": "BOOLEAN" }, { "mode": "NULLABLE", "name": "partition_date", "type": "DATE" } ] }, "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/hoge-project/datasets/bq_command_dataset/tables/bq_command_pt_table", "tableReference": { "datasetId": "bq_command_dataset", "projectId": "hoge-project", "tableId": "bq_command_pt_table" }, "timePartitioning": { "field": "partition_date", "type": "DAY" }, "type": "TABLE" }
--schema
フラグをつけることで、スキーマ情報のみを取得することも可能です。
# schemaのみを取得 $ bq show \ --schema \ --format=prettyjson \ hoge-project:bq_command_dataset.bq_command_pt_table [ { "mode": "NULLABLE", "name": "employee_id", "type": "STRING" }, { "mode": "NULLABLE", "name": "name", "type": "STRING" }, { "mode": "NULLABLE", "name": "attendance", "type": "STRING" }, { "mode": "NULLABLE", "name": "partition_date", "type": "date" } ]
また、bq ls
コマンドにてテーブルの一覧表示を確認できます。
$ bq ls \ --format=prettyjson \ hoge-project:bq_command_dataset [ { "creationTime": "1617024216763", "id": "hoge-project:bq_command_dataset.bq_command_no_pt_table", "kind": "bigquery#table", "labels": { "hoge": "fuga" }, "tableReference": { "datasetId": "bq_command_dataset", "projectId": "hoge-project", "tableId": "bq_command_no_pt_table" }, "type": "TABLE" }, { "creationTime": "1617024639811", "id": "hoge-project:bq_command_dataset.bq_command_pt_table", "kind": "bigquery#table", "labels": { "hoge": "piyo--require_partition_filter" }, "tableReference": { "datasetId": "bq_command_dataset", "projectId": "hoge-project", "tableId": "bq_command_pt_table" }, "timePartitioning": { "field": "partition_date", "type": "DAY" }, "type": "TABLE" } ]
__PARTITIONS_SUMMARY__
メタテーブルを使うとパーティションに関する情報を取得できます。
$ bq query \ --use_legacy_sql=true \ 'SELECT partition_id FROM [bq_command_dataset.bq_command_pt_table$__PARTITIONS_SUMMARY__]' Waiting on bqjob_r3c1cbe985379d7e7_000001787e541b29_1 ... (0s) Current status: DONE +--------------+ | partition_id | +--------------+ | 20210101 | | 20210102 | +--------------+
7. bqコマンドでdataのinsert
これまで作成したデータセットとテーブルにデータを入れてみます。
今回はcsvですが、json形式でも大丈夫です。
# 01.csvを読み込む $ bq load \ --replace \ --source_format=CSV \ --schema=./schema.json \ hoge-project:bq_command_dataset.bq_command_pt_table \ ./source01.csv Upload complete. Waiting on bqjob_r78d80973f8b7b92c_000001787e35fed2_1 ... (0s) Current status: DONE # 02.csvを読み込む $ bq load \ --source_format=CSV \ --schema=./schema.json \ hoge-project:bq_command_dataset.bq_command_pt_table \ ./source02.csv Upload complete. Waiting on bqjob_r448ceceb57f44638_000001787e3639bc_1 ... (0s) Current status: DONE
--replace
フラグを追加すると、データの追加ではなく上書きになります。
delete or insertのような感じでしょうか。
8. bqコマンドでdataのselect
bqコマンドにてselectする方法は主に2つあります。
- bqコマンドに生SQLを書く
- bqコマンドにSQLファイルを読み込ませる
まず最初に、生SQLでのbqコマンドでのselectです。
# bqコマンドに生SQL $ bq query \ --use_legacy_sql=false \ 'SELECT employee_id, name, attendance, partition_date FROM `bq_command_dataset.bq_command_pt_table`'
次に、SQL文を記載したSQLファイルを読み込んでのselectです。
- SQLファイル
SELECT employee_id,name FROM bq_command_dataset.bq_command_pt_table WHERE _PARTITIONDATE = "2021-01-01"
- コマンド
# bqコマンドにSQLファイルを読み込ませる $ bq query \ --use_legacy_sql=false \ < query.sql Waiting on bqjob_r4ce8a710bfc9077f_000001787de9e611_1 ... (0s) Current status: DONE +-------------+------+------------+----------------+ | employee_id | name | attendance | partition_date | +-------------+------+------------+----------------+ | 1001 | hoge | true | 2021-01-02 | | 1002 | fuga | true | 2021-01-02 | | 1003 | piyo | true | 2021-01-02 | | 1004 | fufu | false | 2021-01-02 | | 1001 | hoge | true | 2021-01-01 | | 1002 | fuga | true | 2021-01-01 | | 1003 | piyo | true | 2021-01-01 | | 1004 | fufu | true | 2021-01-01 | +-------------+------+------------+----------------+
consoleからパーティションを指定したSQLで容量が変わるかを確認してみます。
まずは、パーティションの指定無しSQLから。
168B
と表示されているのがわかります。
次に、パーティションを2つとも指定したSQL。
こちらも168B
と表示されています。まぁそうですよね。
最後に、パーティションを1つだけ指定したSQL。
84B
と表示されました。指定カラムをそのままでも容量が減っている事が確認できます。
9. bqコマンドでtableのdelete
最後は、作成したtableを削除して終わりにします。
$ bq rm \ -f \ -t \ hoge-project:bq_command_dataset.bq_command_no_pt_table
参照
- Google Cloud SDK 認証の完了
- Cloud のロケーション
- bq コマンドライン ツールの使用
- コマンドライン ツール リファレンス
- データセットの作成
- データセットのロケーション
- テーブルの作成と使用 > スキーマ定義を含む空のテーブルの作成
- 標準 SQL のデータ型
- テーブルの作成と使用 > テーブルの情報の取得
- 時間単位の列パーティション分割テーブルの作成と使用 > パーティション分割テーブルの一覧表示
- スキーマの指定
- 時間単位の列パーティション分割テーブルの作成と使用>スキーマ定義を持つ空のパーティション分割テーブルの作成
- テーブルの操作 > テーブルの削除
おわりに
bqコマンドが以外に便利だという事がわかりました!!enjoy!