【BigQuery】知っておくと便利なbqコマンドのオプション【GCP】【bq】

はじめに

DWHが業務で必要そうなのでまずスタートとしてGCPのBigQueryを使ってみました。
その中でbqコマンドのオプション色々調べてみたのでまとめてました。ロードの仕方も簡単にまとめてあります。

処理時間がどんなものかを設定したシナリオを再現したクエリで調査するということも少しやっています。

BigQueryとは

BigQuery は、 Web ブラウザからの操作だけで、気軽にテラバイト、ペタバイト級のデータを扱って解析が行えます。

GCP入門編・第12回】 BigQuery を使って気軽にビッグデータの解析を行ってみよう！
より引用

実際に使ってみて手軽さや実行スピードを実感しました。上記の引用サイトは入門にもちょうど良いので合わせてぜひやってみてください。

ちなみにBigQueryの元となったGoogle内のプロダクトである「Dremel」の論文もあったのでDeepDiveしたい人はどうぞ(笑)

検証

まずロードからやっていきます。
今回は少し大きいデータセットを用意したのでGCS→BigQueryの流れでLOADします。

ロード

GCSに転送

まずGCSにデータを入れます。大きめのデータセットは一度GCSに置いたからBigQueryに投入するようです。
今回はS3にデータを置いてあったので、Cloud Strorage Transfer Serviceを使います。

• 転送元_s3バケット名 s3://poc-sample
• アクセスキーID,シークレットアクセスキー
• ファイルフィルタ_接頭辞 table1/
• 転送先_gsバケット名 gs://poc-sample

のように必要事項を入力してジョブを作成すると転送が始まります。
この転送はかなり速いと思いました。

BQロード

前準備ができたらBQにロードしていきます。
もちろんbqコマンドからもできますが、UIで操作していきます(笑)

1. リソースにデータを追加
   プロジェクトが作られていれば、プジェクト名でリソースが作成されているかと思います。
   ここではプロジェクト名をsampleとします。

2. データセットを追加
   プロジェクト配下にデータセットを作成します。
   

• データセットID poc_data
• データのロケーション 東京(asia-northeast1)

上記を入力してデータセットを作成します。

3 . テーブル作成

• ソース GoogleCloudStorage
• GCSバケット gs://poc-sample/table1/*
• ファイル形式 CSV
• プロジェクト名 sample
• データセット名 poc_data
• テーブルタイプ ネイティブテーブル(※1)
• テーブル名 table1
• スキーマ テキスト(変数名:データ型,変数名:データ型,変数名：データ型,・・・,変数名:データ型) または 直接入力の形式でスキーマを設定

この3stepでテーブルの作成ができます。
※1　ネイティブテーブルはBigQueryの内部にデータをロードするイメージです。これを外部テーブルに設定するとクエリをこのテーブルに投げた際にGCSを参照しにいくようになります。処理速度はおそろしく遅くなるのでネイティブテーブルでロードするのが良いかと思います。

クエリオプション

本題のクエリオプションになります。
ここからはcloudshellでbqコマンドを実行していきます。

.sql | bq query

同じクエリを何度も投げることが考えて、SQLスクリプトを用意しておきます。

sample.sql

#standardSQL
select count(*) from `poc_data.table1`

行頭に#standardSQLと記載しておくとSQLスクリプトでの実行時にstandardSQLとして扱われます。
ブラウザから実行するのと同様に実行できます。

このようなSQLスクリプトを用意しておき、下記コマンドで実行できます。

$ sample.sql | bq query

--allow_large_results

BigQueryには割り当てと制限(Quotas&limits)があり、大きいデータセットに対してクエリを投げるとちょいちょいこの限度にあたり実行がうまくいかないことがあります。

その中の1つの最大レスポンスサイズが圧縮して128MBを越えるとこのオプションをつけろと言われます。

このオプションつけると出力するテーブルを指定しろとも言われます。
最終的にレスポンス限界にぶちあったたら、下記のbqコマンドで通るはずです。

$ sample.sql | bq query --allow_large_results --destination_table=poc_data.RESULT_sample

--nouse_cache

検証では素の処理時間が知りたかったのでキャッシュの無効化をしました。
キャッシュを使うと一度投げたクエリが速くなったり、あらかじめ統計情報を計算しておきその情報がクエリの結果として返ってきたりするらしいです。

$ sample.sql | bq query --nouse_cache

--udf_resource

ちょっと重い計算をさせたいときはUDF関数を使いたくなりますよね。
javascriptっぽくUDF関数を書くこともできるようですが、今回はSQLスクリプトで書きました。

udf関数のSQLスクリプトを用意したら下記コマンドで読み込ませます。

$ sample_udf.sql | bq query --udf_resource udf.sql

--location

基本的にはプロジェクトとテーブル作成時に設定したリージョンは一致しているかと思います。

一致していないとsqlがエラーになるかと思うのですが、これの理由がcloudshellはプロジェクト作成時に設定したリージョンに建てれらたコンテナだからだそうです。

ですが、もしかしたらプロジェクトで設定したリージョンとテーブル作成時のリージョンが異なるテーブルにクエリが投げたいことがあるかもしれません！そんな時にlocationオプションを使用します。

$ sample.sql | bq query --location=EU

このようにしてリージョンが異なるテーブルにcloudshellからクエリを投げることができます。

その他

bq rm -f

bqコマンドでテーブル削除しようとすると毎回消してもよいかと確認されます。
検証でテーブルを作成して同じテーブルを作成するときにこれが結構面倒なので強制テーブル削除したいと思いました。

bq rm -f [データセット].[テーブル]

これで確認がなくテーブルが削除されます。

bq ls -j

ジョブ一覧が取得できます。
ターミナルで実行していると処理完了がわかりにくいことがあるのでこのコマンドで一覧を表示するといつ始まってクエリの状況が実行中なのか完了しているのか一目でわかります。

bq ls -j

                    jobId                      Job Type    State      Start Time      Duration  
 -------------------------------------------- ---------- --------- ----------------- ---------- 
  job_ZlIz0GARiKA3fqF0YV0u195RFygf             query      SUCCESS   18 Oct 07:06:07   0:00:01   
  job_24GOSEFeJB182cXMDSj9sxqFYkLM             query      SUCCESS   18 Oct 07:06:07   0:00:01   
  job_t640F7QFmS6ftBvHFjEFvThTHHnd             query      SUCCESS   18 Oct 07:05:34   0:00:02   
  job_Kuv0XyybJqlyqiU2fp9trc4FEpEc             query      SUCCESS   18 Oct 07:05:34   0:00:02  

bq cancel

たまにUIからクエリをストップしても反映されづらいことがあります。
そんな時は下記コマンドで強制キャンセルが可能です。

bq cancel --sync=false [jobid]

強制キャンセルの時はこれが一番だと思います。

おわりに

まとめてみるとただの自分の備忘録になってしまいました。
意外とbqコマンドのオプションとか見つけづらかったりするので参考にしてもらえればと思います。

参考

• bqコマンドラインツール
• 割り当て&制限