astamuse Lab

astamuse Labとは、アスタミューゼのエンジニアとデザイナーのブログです。アスタミューゼの事業・サービスを支えている知識と舞台裏の今を発信しています。

初めてBigQueryを触って学んだ節約トピックを紹介するよ

GCP BigQuery データ開発エンジニア

みなさんこんにちは。たぶんアプリケーションエンジニアのkjです。

私にはもうすぐ1歳半になる娘がおりまして、リモート勤務の傍ら、ホンヨンデー！って訴えてきます。 その表情、振る舞いが愛おしく(親バカ)、スキマ時間をとって本を読んでいます。

ちなみに、娘の最近のお気に入りはハッピーセットのオマケでついてきた「小学館の図鑑 危険生物」

f:id:astamuse:20200315225806p:plain
お気に入りの本は「小学館の図鑑NEO 危険生物」一度手にとったら、もう離さないよ！

こちら、世界の様々な人食いザメや凶暴なピラニアなどが掲載され、海にはアブナイ生き物がいるよ！気をつけてね！と紹介されているダイジェスト本です。

自分の娘ながら嗜好が理解できません。。。

閑話休題

突然ですが、BigQueryって便利ですよね！最近は業務でbqコマンドをよく使っています。bqコマンドとは、ざっくりというと、Google社が提供しているCloudSDKに内包されている、BigQueryのデータを操作するためのコマンドです。コマンドの裏側ではPythonが動いており、GoogleCloudのAPIを叩いています。

ちなみに私、前職はコテコテのJavaエンジニアで、システムはオンプレやろ！って世界に住んでおました。 「くらうど？？ナニソレ オイシイノ？」ってところから、都度都度調べながらBigQueryを触っております。 今回は、自分の理解の整理がてら、bqコマンド(+ときどきBigQuery)について、トピックをご紹介したいと思います。

本日のポイント

本日触れないこと

  • BigQuery, GCPとはなにか
  • bqコマンドのインストール方法について。こちらは公式サイトを参照してね！

その他注意書き

  • 以下、bqコマンドでこんなことできるぞ！とドヤりますが、実はBigQueryのWebコンソールにて特殊な操作することなく表示されるものが多いです。

    ですので、BigQueryにこなれている方は、コマンドでもできるんだフーンとしていただければ。まぁでも、コマンドでできた方がいろんなツールと連携しやすいので、知っておいて損はないよね！

  • 2020年3月時点での内容になりますので、将来変わる可能性もあります。

  • GoogleCloud Japanのブログにて弊社が紹介されております。むしろそちらをご覧ください。

    cloud.google.com

BigQueryの料金について把握する

さて、はじめてBigQueryを触るにあたって、まず気になったのが料金です。ネットにはクラウドサービスでウン万円溶かしたって記事は事欠きません。ちなみに私、しらないお店はお金にビビって入れないタイプです。

なので、どういう時に課金される、されない、もしくはどれくらい課金されるか、を調べました。 結論、扱うデータサイズによりますが、よっぽどな操作をしない限りは大丈夫かと思っています。 2020年3月時点でざっと把握したカンジは以下となります。（なお、正確な情報は公式サイトをご参照ください）

課金タイプは2種類

BigQueryの課金パターンとして以下2つとなります。カッコ内の金額は参照値。実際は操作内容やリージョン、契約内容、無料枠などによって変わってきます

  • 保存しているデータ量に応じて課金(月に$0.020/GB @ US multi-region)

  • クエリを実行した際に読み込んだデータ量に応じて課金($5.00/TB @ US multi-region)

    ただし、読み込んだ1テーブルあたり最小サイズは10MBで、以降1MB単位(端数は四捨五入)で課金。つまり1テーブル読み込むごとに10MBが課金対象となる

なお、BigQueryでは内容によってはクエリ無料のものがあります。全部あげるとキリがないですが、私がよく使う無料枠のものは以下のようなものになります。(以下、保存データに関する課金は考慮していません)

  • bq ls, bq show などのデータを直接参照しない閲覧コマンド
  • bq head, bq cp によるフィルタリングや加工を伴わない照会コマンド。(コピーは照会とは違うけど、readしても課金されないよ！という意味でここに)

  • bq mk, bq rm などのテーブル作成、および削除。

    bq queryで実行するCREATE (OR REPLACE) TABLE, DROP TABLEなども、テーブルをSELECTしていなければ課金されないよ！

一方で有料となるクエリは以下のようなものがあります。詳細は以降で一部紹介します。

  • bq query にてテーブルをSELECTするもの
  • bq query にて、「INSERT INTO 〜〜 SELECT 〜 FROM 〜〜」のようにテーブルSELECTしてレコード挿入するもの
  • bq query にて、「UPDATE SET 〜〜」や「DELETE 〜〜」するもの。

bqコマンドを色々触ってみる

さてここまでウンチクを記載してましたが、実際のデータを参照しながらbqコマンドを触ってみたいと思います。

プロジェクトの作成、CloudSDKのインストール

詳細の手順は公式サイトをご参照ください。 なお公式サイトでは、サンプルデータセットを利用したクイックスタートも載っています。GCPの1年間の無料クレジットがある方はこちらを試すのもありかと思います。

また、以降のクエリはStandardSQLを前提にしています。bqコマンドの設定方法は以下をご確認ください。 cloud.google.com

bqコマンドのヘルプをみてみる

bqコマンドにはヘルプが用意されています。とりあえずbqだけ打ってみると（ただし、CloudSDKインストール後、初めて実行する場合はアカウントの設定等、諸々初期設定が行われる場合があります）

$ 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.

# (以下省略)

という風に、bqで指定できるサブコマンドとその概要が表示されます。ちなみに上記出力の最後に以下のような表示があるはずです。

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.

# (以下省略)

という風に、違う内容が表示されました。（ちなみにbq helpを打つとbqと同じ結果が表示されます）

つまり、bqコマンドには大きく2種類のヘルプがあります。bqの後に続けるサブコマンドに関するヘルプと、bqコマンドのフラグに関するヘルプです。 前者についてはWebドキュメントにも詳細が記載されているので利用する機会は少ないのですが、フラグについてはWebドキュメントに記載されていないものもあり、中には便利なものがあります。是非見てみてください。

動作環境

$ 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

ちなみに「Waiting on」の後に表示されている文字列(上記のbqjob_r1367xxxx....)が、そのクエリを実行しているジョブIDになります。ジョブIDは後でその処理を照会する場合に利用します。

データの照会

テーブルの確認、データの確認にはそれぞれbq show, bq headを利用します。

$ bq show test_kj.sample
Table 【Project Name】:test_kj.sample

   Last modified        Schema        Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Labels
 ----------------- ----------------- ------------ ------------- ------------ ------------------- ------------------ --------
  14 Mar 17:58:23   |- id: integer    4            124
                    |- key1: string
                    |- key2: string
                    |- key3: string
                    |- key4: string

$ bq head test_kj.sample
+----+------+------+------+----------+
| id | key1 | key2 | key3 |   key4   |
+----+------+------+------+----------+
|  3 | c    | cc   | cccc | cccccccc |
|  1 | a    | aa   | aaaa | aaaaaaaa |
|  2 | b    | bb   | bbbb | bbbbbbbb |
|  4 | d    | dd   | dddd | dddddddd |
+----+------+------+------+----------+

なお、bq headでデータを読み込んだ場合は課金されませんが、bq queryでテーブルを読み込んだ場合は課金対象となります。--dry_runオプションをつければ、クエリを実行することなく、実行した場合に何byte読み込むかを確認できます(下記の例では124byte)。

$ 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

上記はsampleテーブルを作成した際のジョブIDを指定したものです。今回のCREATE TABLEの場合は「Bytes Processed」、「Bytes Billed」が0であることが確認できます。(「Billing Tier」は、過去は利用されていたけど現時点では課金に利用されていない項目のようです)

bq head で使える表示オプション

bq headコマンドの表示結果はテーブル形式で人にとっては見やすいですが、コマンドで連携したり、ファイルに出力する場合には扱いづらいです。この場合はbqコマンドのオプションである--formatを利用できます。 例えば、CSV形式にする場合は以下のように--format=csvを指定します。

$ bq head --format=csv test_kj.sample
id,key1,key2,key3,key4
3,c,cc,cccc,cccccccc
1,a,aa,aaaa,aaaaaaaa
2,b,bb,bbbb,bbbbbbbb
4,d,dd,dddd,dddddddd

json形式も指定できます。改行やインデントがない--format=jsonの他、人に見やすくインデントされた--format=prettyjsonもあります。 たとえば、

$ bq head test_kj.sampleo_long
+----------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+
|                                            value1                                            |                                         value2                                          |
+----------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+
| toooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo LONG!! | sample2                                                                                 |
| sample1                                                                                      | toooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo long! |
+----------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+

テーブル形式の場合、1レコードのある値が長いと、その値で1列の幅が調整されてしまい見づらいですが、

$ bq head --format=prettyjson test_kj.sampleo_long
[
  {
    "value1": "toooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo LONG!!",
    "value2": "sample2"
  },
  {
    "value1": "sample1",
    "value2": "toooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo long!"
  }
]

インデントしたjson形式なら多少は見やすくなります。

その他bqのオプションとして、デフォルト表示件数100件を変更できる-n <表示件数>オプション 1 があります。またbq headのオプションとしては、表示する列を指定できる-c <カラム1>,<カラム2>,...オプション、表示開始のレコード位置を指定できる-s <表示開始インデックス>オプション(-s 0とすれば、1行目から表示される)があります。 --formatオプションとこれらを組み合わせれば、テーブルをちらっと閲覧したいケースは大方カバーできるかと思います。

SELECT文に関するトピック

ここまでbq headを推してきましたが、SELECT文を使ってレコードをフィルタしたり、値の変換等、諸々操作したい場合もあるかと思います。 SELECTするにあたって、表示する列を限定することで課金額を節約できます。

たとえば、以下のように、とりあえずSELECT *すると、、、

$ 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.

全ての列の全データが課金対象になります。

なお計算式は以下の通り。データ型によるサイズは BigQuery の料金  |  Google Cloud を参照。

id=1のレコードについて、

  • id列はinteger型なので、8 byte
  • key1列はstring型の'a'なので、2 + 1 = 3 byte
  • key2列はstring型の'aa'なので、 2 + 2 = 4 byte
  • key3列はstring型の'aaaa'なので、 2 + 4 = 6 byte
  • key4列はstring型の'aaaaaaaa'なので、 2 + 8 = 10 byte

id=1のレコードの読み込んだbyte数は 8 + 3 + 4 + 6 + 10 = 31 byte

id=2,3,4も同様に計算して、全4レコードを読み込んだbyte数は 31 + 31 + 31 + 31 = 124 byte

ところが、実際に閲覧したい列はidとkey1のみだったとすると、この2つの列に絞ってSELECTすることで、

$ 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.

となり、元のクエリと比べて読み込みサイズが3分の1になりました！ この場合は (id列の 8 byte + key1列の 3 byte) * 4 レコード = 44 byte

まぁこの例の場合だと大したサイズではないので有り難みは全くないのですが、何百万、何千万件と扱うデータサイズが大きくなるとじわじわと懐に響いてきます。

あと、公式ドキュメントにも記載されていますが、WHEREやLIMITによるレコードの絞り込みは課金に影響しません。

$ 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.

ただ、私個人的な体感ですが、LIMITで表示件数を絞るとクエリの応答速度が速くなるようです。 特にbqコマンドのデフォルト表示件数は100件ですが、LIMIT 100があると応答速度が速くっているカンジがします。（もちろん、クエリ結果の件数が多い場合です） なので、結果件数が多い場合は、bqコマンドの-nオプションと合わせてLIMITの数を合わせるとよいでしょう。

ちなみに余談ですが、SELECT COUNT(*)やSELECT COUNT(1)はSELECT文ですが、課金されません。

$ 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 showで確認できるから、ですかね。中々粋なことをしてくれます。

しかし、列を指定すると課金されます。

$ 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.

こっちはCOUNT(DISTINCT <列名>)とすると、単純なレコード数ではなくなるから、ですかね？？

あるテーブルの全レコードを削除するぞ。もちろん課金なしで

ちょっと長くなってきました。ここからはちょっとした小ネタを紹介します。

テーブルの全レコードを削除する場合、普通のDBと同じ感覚でDELETE FROMを実行すると、、、

$ bq query --dry_run "DELETE FROM test_kj.sample"
Error in query string: DELETE must have a WHERE clause at [1:1]

という風に、DELETE文を実行する場合はWHERE句が必須だよ！と怒られます。仕方ないのでトートロジーな条件を入れてあげると、、、

$ 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.

テーブルそのものを削除する場合は課金されないのに、全レコード削除は課金対象なんかい！と。世知辛い世の中ですね。

ところで、我々はテーブルの削除と作成は課金されないことを知っています。ならば、これを上手く活用する方法はないか？と。

結論、あります。 jqコマンド 2 を利用すれば楽にできるので、それを紹介します。 なお、jqコマンドの説明は省略しますが、一言でいうとJSONフォーマットをよしなに扱えるコマンドです。詳細はぐぐってください。(だんだんテキトーになってきたな)

$ bq show --format=json test_kj.sample | jq .schema.fields > table_schema_sample.json
$ bq rm -f test_kj.sample
$ bq mk --schema table_schema_sample.json test_kj.sample
Table '【Project Name】:test_kj.sample' successfully created.

1つ目のbq show ... | jq ...にて、テーブルのスキーマに関するjson表記を取得して、それをリダイレクトしてファイルに保存しています。ここでbqコマンドに--forma=jsonオプションを指定しているのがポイントです。こうすることで、jqコマンドで必要な情報のみフィルタできます。 2つ目のbq rmで全レコードを削除したいテーブルを削除しています。 3つ目のbq mkにて、テーブルスキーマを記載したjsonファイルを指定して空テーブルを作成しています。

これにより、課金なしで全レコードを削除することを実現しています。ちなみにNOT NULL制約はスキーマの定義に記載されるので、テーブル作成した際にも再現できます。パーティション等については未確認なのであしからず。。。

まぁそもそも全レコードを削除する、というユースケースが実際あるのか？と。ここでは疑問に思ってはいけません。

間違えてクエリを発行してしまった！中断や！

bq queryをよく叩く方にはあるあるかと思いますが、実際は実行したくなかったのに--dry_runオプションをつけ忘れてしまい、クエリが走りだします。 コンソール上にRunningのメッセージが表示されるので、慌ててCTRL + Cで止めて「危なかったー！ヒヤリハットやったわ！」ってなりますよね？？

いえ、それアウトですから。

実際はコンソールにRunningが表示された時点で、手元のマシンでCTRL + Cで止めたとしても、GCPにクエリの要求が届いておりクエリは処理されたままになります。 GCP側のクエリそのものを中断するにはbq cancel <ジョブID>を打ってあげる必要があります。

ちなみに、ジョブをキャンセルしても課金される可能性があります。それでもキャンセルを実行するのは、GCPを安く利用させてもらっている分、不要なマシンリソースを解放する心遣いですね。

その他

BigQueryにあまり慣れてない時によく参照したドキュメントはこちらです。

cloud.google.com cloud.google.com

あとは、上記のドキュメントから左側の目次から気になったのをピックアップすれば、色々と知れるのでおすすめです。

さいごに

ここまで、bqコマンドに触って知ったウンチクを色々記載しましたが、みなさんの参考になるものがあれば幸いです。

  1. bqコマンドの結果の件数が思ったより少ないときは、このデフォルト100件に引っかかっている場合が多いです。例えば、bq ls <データセット名> でテーブル一覧を出力したり、bq query "SELECT 〜〜"でレコードを出力した時に100件しか表示されていない場合は、-n <表示件数>オプションを設定してみてください。↩

  2. https://stedolan.github.io/jq/ ↩

Copyright © astamuse company, ltd. all rights reserved.

20160610170904