UNIX/Linuxの部屋 bqコマンドの使い方


※空白区切りで AND 検索 (例:「ファイル 削除」)

コマンド bq BigQuery 操作用コマンド

最終更新


BigQuery とは、Google のクラウドサービスである Google Cloud Platform で動作するデータウェアハウスエンジンである。管理画面から操作することもできるが、bq コマンドを使うことで効率的にテーブル・データの管理が行える。MySQL の mysql コマンド、PostrgreSQL の psql コマンド、Oracle の sqlplus コマンドに相当する。


初回起動時
bq コマンドを初めて起動した場合、下記のようにプロジェクトを選択するよう促される。
% bq ls
Welcome to BigQuery! This script will walk you through the
process of initializing your .bigqueryrc configuration file.

First, we need to set up your credentials if they do not
already exist.

Credential creation complete. Now we will select a default project.

List of projects:
# projectId friendlyName
--- ---------------------- ------------------
1 my-gcp-test my-gcp-test
2 glassy-signal-1234 My First Project

Enter a selection (1 - 2): 1
上記では 1 の my-gcp-test プロジェクトを選択した。すると、下記のように mydataset というデータセットが自動的に生成される。
BigQuery configuration complete! Type "bq" to get started.

datasetId
-----------
mydataset

簡単チュートリアル
まずは BigQuery の基礎知識。

プロジェクトとは、GCP において請求や権限をまとめるための概念である。GCP では何をするにもまずプロジェクトを作成し、その中で仮想マシンや BigQuery 操作を行う。

データセットとは、BigQeury においてテーブルをグルーピングするための概念である。他の RDBMS ではスキーマやデータベースという名称で mydb.mytable と階層構造を表すことができるが、それと似たようなものである。ただ一点異なるのは、BigQuery においては常に「データセット名.テーブル名」と、データセット名が必須であること。

テーブルとは、あなたが知っている RDBMS のテーブル概念と同じである。

プロジェクト作成は、bq コマンドの範囲から大きく外れるため、各自で予め行ってほしい。

さて、データセットを新しく作ってみよう。データセットを作成するには bq mk コマンドを使う。そして bq ls で、データセットが作成できていることを確認しよう。
% bq mk mydataset2
Dataset 'myproject:mydataset2' successfully created.

% bq ls
datasetId
-----------
mydataset
mydataset2
データセットの下にテーブル mytable を作ろう。"CREATE TABLE" 文はないようなので、bq load を使う。下記は name と gender (いずれも型は string)、count (型は integer) という 3カラムを持つテーブル mytable を作成している。
% cp /dev/null empty.csv
% bq load mydataset2.mytable empty.csv name:string,gender:string,count:integer

テーブルの定義を確認するには bq show を使う。
% bq show mydataset2.mytable
Table my-gcp-test2:mydataset2.mytable

   Last modified         Schema         Total Rows   Total Bytes   Expiration   Time Partitioning   Labels   kmsKeyName
 ----------------- ------------------- ------------ ------------- ------------ ------------------- -------- ------------
  25 Jan 22:38:38   |- name: string     0            0
                    |- gender: string
                    |- count: integer

今度はカレントディレクトリに CSV ファイルを作成し、それをテーブルに格納しよう。
% echo "Yamada,M,1" > data.csv
% echo "Tanaka,F,2" >> data.csv
% cat data.csv
Yamada,M,1
Tanaka,F,2
% bq load mydataset2.mytable data.csv name:string,gender:string,count:integer
Upload complete.
Waiting on bqjob_r51fd050fd67424f4_00000161bd7e4aee_1 ... (1s) Current status: DONE

そして bq query で SQL 文が書けるので、テーブルの内容を参照してみよう。データセット名 (ここでは mydataset2) が必須であることに注意。
% bq query "select * from mydataset2.mytable"

bq help … ヘルプ表示
bq help とすると、ls・bq query などのサブコマンドの一覧と、簡単な説明やサンプルを表示する。
% bq help
(略)
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.
* With -j, show the jobs in the named project.
* With -p, show all projects.

Examples:
bq ls
bq ls -j proj
bq ls -p -n 1000
bq ls mydataset
bq ls -a
bq ls --filter labels.color:red
bq ls --filter 'labels.color:red labels.size:*'
(略)
bq --help は、共通オプションを表示する。
% bq --help
ls や query などのサブコマンドのヘルプを表示する場合は下記のようにする。
% bq ls --help
% bq help ls
→ 前者は共通オプションの説明も表示されるが、後者はそれが省略されるという違いがあるようだ。

bq load … テーブルにデータを挿入する (テーブル作成)
bq load で、テーブルにデータを挿入する。テーブルが存在しない場合は新規作成となる。テーブルが存在する場合は追記または置き換えを選択できる。

テーブルが存在しない場合、
bq load [テーブル名] [データファイル名] [スキーマ定義]
とする。 テーブルが存在する場合は、
bq load [テーブル名] [データファイル名]
→ 同じスキーマのままデータ追記
bq load --replace [テーブル名] [データファイル名]
→ 同じスキーマのままデータ置き換え
bq load --replace [テーブル名] [データファイル名] [スキーマ定義]
→ 新しいスキーマ・新しいデータで完全に置き換え
とする。

データを作成し、
% echo "Yamada,M,1" > data.csv
% echo "Tanaka,F,2" >> data.csv
% cat data.csv
Yamada,M,1
Tanaka,F,2
その後、新規テーブル mytable にデータをロードする。
% bq load mydataset.mytable ./data.csv name:string,gender:string,count:integer
もう一度同じコマンドを実行すると、追記となるため同じレコードが 2つになる。

--replace オプションを指定すると、一度テーブルを空にして、データファイルの内容で置き換えを行う。
% bq load --replace mydataset.mytable ./data.csv name:string,gender:string,count:integer

[テーブル名] の部分は、少なくとも
mydataset.mytable
とデータセット名とテーブル名を指定する。別プロジェクトのテーブルであれば、
myproject:mydataset.mytable
と指定する。

[データファイル] は、ローカルファイルでもよいし、GCS 上のオブジェクトでもよい。GCS オブジェクトの場合は下記のように gs:// とスキーマ指定を行う。
% bq load mydataset.mytable gs://mybucket/data.csv name:string,gender:string,count:integer

▷ --source-format オプション … データファイルのフォーマットを指定する。
  • CSV … カンマ区切りの形式。CSV がデフォルト。
  • NEWLINE_DELIMITED_JSON … 「{"foo":1, "bar":"abc"}」のような 1行 1 JSON 形式
  • DATASTORE_BACKUP
  • AVRO … Apache AVRO フォーマット
  • PARQUET … カラムナフォーマットの一つ。
  • ORC … カラムナフォーマットの一つ。もともとは Apache Hive が源流。
▷ --replace オプション … テーブルを空にした上で、データファイルの内容で置き換える。
デフォルトは --noreplace なので、置き換えではなく追記となる。

bq ls … データセット・テーブル・ジョブの一覧を表示
bq ls とするとデータセットの一覧を表示し、bq ls [データセット名] とすると、そのデータセット配下のテーブル一覧を表示する。
% bq ls
→ データセットの一覧を表示
% bq ls mydataset
→ データセット mydataset に含まれているテーブルの一覧を表示
% bq ls -n 1000 mydataset
→ 最大 1000件まで表示する
bq コマンドは、デフォルトでは最大 50 件までしか結果を表示しないようになっている (bq ls に限らず、bq コマンド全般に言える話)。これを緩和したい場合、-n 1000 などとすることで上限を指定できる。

また、-j オプションをつけることでジョブ一覧を表示できる。
% bq ls -j
             jobId               Job Type    State      Start Time      Duration
 ------------------------------ ---------- --------- ----------------- ----------
  bquxjob_de99e37_196cf410fce    query      SUCCESS   26 Oct 16:24:22   0:00:02
  bquxjob_334980f7_196cf10adc6   query      SUCCESS   26 Oct 16:22:49   0:00:02
  beam_load_c47a1ef76341c1986b   load       FAILURE   24 Oct 14:33:21   0:15:22
(略)

bq ls -j では自分自身が発行したジョブのみであるため、-a オプションをつけると全ユーザの発行したジョブを確認できる。
% bq ls -j -a

bq mk … データセット・テーブル・ビューの作成
bq mk でデータセットを作成できる。
% bq mk mydataset
→ データセット mydatase を作成
さらにテーブルも作成できる。
% bq mk mydataset.mytable
→ データセット mydatase の下にテーブル mytable を作成
ただしこのようにしてテーブルを作成すると、カラムがひとつもないテーブルができてしまうので、あまりうれしくない。

下記のように --schema オプションを指定することで、カラム名と型を指定できる。
% bq mk --schema name:string,gender:string,count:integer mydataset.mytable
しかしながら上記は簡易指定であり、STRUCT (RECORD)・ARRAY (REPEATED) といった BigQuery 独自の構成を指定することはできない。おすすめは下記のように JSON 形式のファイルにカラム情報を含め、--schema オプションにて JSON ファイル名を指定することである。
% cat schema.json
[
{"name": "name", "type": "string", "mode": "required"},
{"name": "gender", "type": "string"},
{"name": "count", "type": "integer"}
{"name": "country", "type": "string", "mode": "repeated"}
]
% bq mk --schema schema.json mydataset.mytable
このようにすれば、name を必須 (required) としたり、country を配列 (repeated) にしたり、さらなる BigQuery の機能を活用することができる。

bq mk でのテーブル作成は、あくまでテーブル作成のみでありデータ投入は行えない。bq mk でテーブル作成し、bq load でデータ投入を行うとよい。なお、bq load で --schema オプションを使うとテーブル作成とデータ投入を同時に行うこともできる。

ただ、不慣れなうちは bq mk と bq load を使った方がよいと当ページ管理人は考える。なぜならば、エラーになった場合にテーブル作成に問題があったのか、データに不備があったのかがわかりやすいからである。

bq rm … データセット・テーブルの削除
% bq rm mydataset.mytable
rm: remove table 'mydataset.mytable'? (y/N)
→ テーブルの削除。ここで y を入力すると実際に削除する
% bq rm -f mydataset.mytable
→ y/n の問い合わせなしで強制的に削除する
% bq rm mydataset
→ データセットの削除。データセットの下にテーブルが存在すると削除できない。
% bq rm -r mydataset
→ -r オプションを付けると、テーブルごと削除する。
% bq rm -r -f mydataset
→ 問い合わせなしで、データセットごと削除

bq query … クエリの実行
bq query の後に SQL 文を指定することで、SELECT 結果を表示することができる。
% bq query "select * from mydataset.mytable"
Waiting on bqjob_r5f9dd2ae86f62273_00000161bdb01d2f_1 ... (0s) Current status: DONE
+--------+--------+-------+
|  name  | gender | count |
+--------+--------+-------+
| Yamada | M      |     1 |
| Tanaka | F      |     2 |
(略)
デフォルトでは先頭 100行しか表示しないようになっている。1万行を取得したい場合は -n 10000 とする。
% bq query -n 10000 "select * from mydataset.mytable"

結果を別テーブルに格納したい場合、--destination_table で指定する。
% bq query --destination_table='mydataset.mytable_copy' "select * from mydataset.mytable"
すでに mytable_copy が存在する場合、"Already Exists: Table" というエラーになる。その場合は、下記のように --replace オプションで新しいデータで置き換えるか、--append_table オプションで既存レコードを残したままレコードを追加するかのいずれかが必要である。
% bq query --destination_table='mydataset.mytable_copy' --replace "select (略)"
→ 一度テーブルを消してから新規作成する。
% bq query --destination_table='mydataset.mytable_copy' --append_table "select (略)"
→ テーブルはそのままで、レコードを追加する。

下記のように --dry_run オプションを付けると、SQL として正しいかどうかと、クエリのデータ処理量を表示する。
% bq query --dry_run "select * from publicdata:samples.github_timeline"
Query successfully validated. Assuming the tables are not modified, running this query will process 3801936185 bytes of data.
→ 3,801,936,185 なので 3.5GB。1TB あたり $5 なので、1.9円ほど。
% bq query --dry_run "select * from publicdata:samples.trigrams"
Query successfully validated. Assuming the tables are not modified, running this query will process 277168458677 bytes of data.
→ 258GB なので上記を --dry_run なしで実行すると 138円!

各テーブルのレコード数とサイズを調べたい場合、__TABLES__ というメタテーブルを SELECT するとよい。
% bq query "select * from  mydataset.__TABLES__"
Waiting on bqjob_r60ace7f493442c33_000001616a1d5302_1 ... (0s) Current status: DONE
+-------------+--------------+-------------------+---------------+--------------------+-----------+------------+------+
| project_id  |  dataset_id  |     table_id      | creation_time | last_modified_time | row_count | size_bytes | type |
+-------------+--------------+-------------------+---------------+--------------------+-----------+------------+------+
| my-gcp-test | mydataset    | mytable1          | 1510724876132 |      1510724876132 |      3233 |    3723234 |    1 |
| my-gcp-test | mydataset    | mytable2          | 1517900879992 |      1517903279644 |    887392 |  835341389 |    1 |
+-------------+--------------+-------------------+---------------+--------------------+-----------+------------+------+

StandardSQL を使う場合、下記のように --use_legacy_sql オプションを使用する。
% bq query --use_legacy_sql=false "select * ..."

bq head … テーブル内容を無料で確認する
bq head で、テーブルの内容を確認することができる。これは無料である。
% bq head mydataset2.mytable
+--------+--------+-------+
|  name  | gender | count |
+--------+--------+-------+
| Yamada | M      |     1 |
| Tanaka | F      |     2 |
+--------+--------+-------+

bq show … テーブルのスキーマ情報を表示
下記は Google による公開データのスキーマ情報を表示したものである。
% bq show publicdata:samples.github_timeline
   Last modified                              Schema                              Total Rows   Total Bytes   Expiration   Time Partitioning   Labels   kmsKeyName
 ----------------- ------------------------------------------------------------- ------------ ------------- ------------ ------------------- -------- ------------
  02 May 08:45:50   |- repository_url: string                                     6219749      3801936185
                    |- repository_has_downloads: boolean
                    |- repository_created_at: string
                    |- repository_has_issues: boolean
                    |- repository_description: string
                    |- repository_forks: integer
                    |- repository_fork: string
(略) |- payload_page_html_url: string
|- url: string |- type: string

bq show に --schema オプションを付けると、レコード数などの情報が表示されず、スキーマ定義のみが表示される。
% bq show --schema --format=prettyjson publicdata:samples.github_timeline
[
  {
    "mode": "NULLABLE",
    "name": "repository_url",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "repository_has_downloads",
    "type": "BOOLEAN"
  },
  (略)
]

bq extract … テーブルのエクスポート
テーブルの内容をエクスポートする。エクスポート先は Google Cloud Strorage のみ。
下記例では、Cloud Storage にバケット my-bucket を作成済みとする (事前に作成しておかないとエラーになる)。
% bq extract mydataset.mytable gs://my-bucket/mytable.csv
→ データセット mydataset の mytable テーブルを、Cloud Storage の my-bucket の下に mytable.csv としてエクスポートする。
% bq extract mydataset.mytable --compression GZIP gs://my-bucket/mytable.csv.gz
→ gzip 圧縮する。
% bq extract mydataset.mytable --destination_format=NEWLINE_DELIMITED_JSON gs://my-bucket/mytable.json
→ CSV ではなく JSON フォーマットで出力する。

bq extract では、出力したファイルが 1GB を超えるときはエラーとなる。その場合、下記のように出力ファイル名にワイルドカード "*" を指定するとよい。
% bq extract mydataset.mytable "gs://my-bucket/mytable-*.csv"
こうしておくと、
  • mytable-000000000000.csv
  • mytable-000000000001.csv
  • mytable-000000000002.csv
  • mytable-000000000003.csv
というふうに、連番が付与された複数ファイルとして配置されるため、1GB 制限には影響されない。

上記の結果を Cloud Shell 上に持ってくる場合は、下記のように gsutil cp するとよい。
% gsutil cp gs://my-bucket/mytable.csv .

なお、REPEATED なカラムがある場合、CSV で出力しようとすると、
Operation cannot be performed on a nested schema.
となるかもしれない。その場合は JSON で出力してみてほしい。

ちなみに正しい JSON フォーマットは
[
{1行目},
{2行目},

]
であるが、bq extract の出力では抽出処理をしやすいようにするため、
{1行目}(改行)
{2行目}(改行)
という形式になっている。この形式は世の中では JSONL とか JSON Line などと呼ぶようだ。

bq cp … テーブルのコピー 【2018-05-09 追加】
bq cp で、テーブルをコピーすることができる。
% bq cp mydataset.mytable mydataset.newtable
→ データセット mydataset 配下のテーブル mytable を、mydataset.newtable としてコピーする。
コピー先のテーブルは、スキーマもデータ内容も、コピー元テーブルと同じになる。

bq cp のいくつかのパターンを以下に示す。
% bq cp mydataset.mytable mydataset.newtable
→ コピー元・コピー先とも、データセットとテーブルの指定は必須である。
% bq cp mydataset.mytable newdataset.newtable
→ 別データセットにコピーしても問題ない (コピー先データセットは bq mk などで作成しておくこと)。
% bq cp myproject:mydataset.mytable mydataset.newtable
→ 別プロジェクトから、デフォルトプロジェクトにコピー
% bq cp mydataset.mytable anotherproject:mydataset.newtable
→ デフォルトプロジェクトから、別プロジェクトにコピー
% bq cp project1:mydataset.mytable project2:mydataset.newtable
→ 別プロジェクトから別プロジェクトへにコピー
コピー元テーブルをカンマで複数つなげることで、複数テーブルをひとつのテーブルにコピーすることもできる。
% bq cp mydataset.mytable1,mydataset.mytable2 mydataset.newtable
→ データセット mydataset の mytable1・mytable2 の内容を、newtable にコピーする。
なお、-a オプション (--append_table オプション) を付けて複数回 bq cp を実行しても同じことになる。
% bq cp mydataset.mytable1 mydataset.newtable
% bq cp -a mydataset.mytable1 mydataset.newtable
→ bq cp --append_table でも同じ

bq cp の際、コピー先テーブルが存在すると、下記のようにテーブルを置き換えてよいかというプロンプトが表示され、y と入力すると実際に置き換えがされる。
cp: replace myproject:mydataset.mytable? (y/N)
bq cp に -f オプション (--force オプション) を付けると、コピー先テーブルが存在しても強制的にコピーを行う。また、bq cp に -f オプション (--force オプション) と -a (--append_table オプション) の両方を付けると、コピー先テーブルが存在しても強制的に追記を行う。なお、2018/08 現在ではテーブルのリネームはできないため、bq cp でテーブルをコピーし、bq rm でテーブル削除という二段階の作業が必要になる。


頑張って書いたおすすめコンテンツ!