bqコマンドを活用してBigQueryを使いこなそう！ – bqコマンド入門 –

Google Cloudのコンソールからの操作に慣れたら直接bqコマンドを実行できるようになろう！

bq ls、bq mk、bq queryなどのbqコマンドを活用してBigQueryを操作することによってBigQueryを快適に利用することができます！

BigQueryをまだ利用し始めたばかりの方や、非エンジニアの方はGoogle CloudのコンソールからGUIを利用して操作をしている方が多いかと思います。ある程度GUIでの操作に慣れた方はコマンドラインからbqコマンドを直接利用してみることをおススメします。

なお、手動でのBigQueryへのファイルアップロードやBigQuery上のテーブルへのSQL実行方法については非エンジニアにもおススメ、BigQueryの始め方で説明していますので、こちらも参照いただけると幸いです。

bqコマンドでBigQueryを自在に操作してみよう

一旦おさらいですが、BigQueryを操作するためには、主に以下の3つの方法があります。

Cloud Console
bqコマンドラインツール
クライアントライブラリ

Cloud ConsoleはGoogle Cloudで用意されているGUIのコンソール画面からの操作、bqコマンドはCLIベースでのコマンドラインでの操作、クライアントライブラリはBigQueryに用意されているクライアントライブラリを利用して、BigQueryの操作をGolangやPython、Javaなど様々なプログラミング言語から行うというものです。

今回はbqコマンドラインツールを利用して、BigQueryの操作をしてみたいと思います。

基本的なbqコマンドについて

bqコマンドを実行するにはCloud SDKのインストールが必要となるのですが、GCPの機能であるCloud Shellを利用することで、既にCloud SDKがインストールしている環境でコマンドを実行することが可能です。

まずはGCPのコンソールにログインし、Cloud Shellを開きます。Cloud Shellはchrome等のブラウザから利用できる、GCPで用意されているターミナル機能のようなもので、Linux環境(Debianベース)を利用してGCPのサービスを利用できます。特にgcloudなどのコマンドラインツールもインストールされている状態のため、GCPのサービスを利用する際にとても便利です。

bqコマンドの例

bqコマンドラインツールを利用することによって実行できるbqコマンドの例は以下の通りです。

bq show ：テーブルの確認
bq help ：helpコマンド(bqコマンドの詳細情報)の実行
bq query ：クエリ(BigQueryのSQL)の実行
bq mk ：データセット、テーブル、ビューなどのBigQueryリソースの作成
bq ls ：データセットの表示
bq load ：BigQueryテーブルの作成または更新、データの読み込み
bq rm ：データセットの削除

データセットの表示・確認、テーブルの作成、クエリの実行など、BigQueryの操作は基本、全てbqコマンドで実行できます。試しにいくつかのコマンドを確認してみましょう。

bqコマンドの実行

ではまずはbq lsコマンドで、現在のプロジェクト内のBigQueryのデータセットを確認してみましょう。

bq ls

前回作成した、”testdataset”という名前のデータセットが表示されました。

続いて、新規のデータセット”testdataset2″を作成します。

bq mk --dataset --location=asia-northeast1 testdataset2

再び、bq lsコマンドを実行し、データセットが作成されていることを確認します。

bq ls

続いて、bq mkコマンドで今度はテーブルを作成します。

bq mk --table --location=asia-northeast1 testdataset2.test_tbl

bq lsコマンドでデータセットの内容を確認します。

bq ls testdataset2

最後に、Cloud Console上のエクスプローラーに作成したものが反映されていることを確認します。

bq loadコマンドを利用してCSVファイルからテーブルにデータを読み込む

前回Cloud Console上でGUIを利用して行った、CSVファイルをBigQueryのテーブルとして取り込む、なんていう作業も当然、bqコマンドで実施できます。

利用するコマンドはbq loadコマンドとなります。Cloud ConsoleでしかGCPを操作したことがない人にとっては若干、オプションが複雑に感じるかもしれませんが、基本的には全て、Cloud Console上で作業するときに発生している作業をオプションとして設定しているだけなので、よく見ればそこまで複雑ではありません。BigQuery上のテーブルにデータを読み込むコマンドを見てみましょう。

bq --location=location load \
--source_format=format \
dataset.table \
path_to_source \
schema

bqのコマンド直後に
–location でBigQueryのサービスを利用する際に選択する、対象のロケーションを設定します。

–source_format では対象のデータ形式(CSV、TSVなど)を設定し、対象のデータセットやテーブル名、読み込み対象のデータなどを設定して、bqコマンドを実行します。

他にも、skip_leading_rowsでBigQueryへのデータ読み込み時にスキップするヘッダーの行数なども設定できます。

bq load --source_format=CSV \
--skip_leading_rows=2 mydataset.mytable \
gs://mybucket/mydata.csv \
./myschema.json

bq queryコマンドを利用してコマンドラインからテーブルに対してSQLを実行する

bq queryコマンドを利用することで、Cloud ConsoleのBigQuery用コンソールを開かなくてもBigQuery上のテーブルに対してSQLを実行することができます。

ちなみにbq queryコマンドについてはオプションが非常に多く、また必要な権限なども含めて色々と考慮する点があるため、こちらのドキュメントも参考にしていただけるとよいかと思います。

https://cloud.google.com/bigquery/docs/running-queries?hl=ja

bq queryコマンドの利用例

では実際にbq queryコマンドの利用イメージを見てみましょう。

bq --location=[location] query \
--use_legacy_sql=false \
'[実行するSQL]'

基本的には上記の構文のイメージを基にして、あとは実際に実行する環境に合わせてオプション等を設定する形になります。

Google Cloudの公式ドキュメント上にbq queryで公開データセットに対してクエリを発行しているサンプルがあるので、こちらを参考にすると分かりやすいかと思います。

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
name,
number
FROM
`bigquery-public-data.usa_names.usa_1910_current`
WHERE
gender = "M"
ORDER BY
number DESC'

いかがでしょうか。Cloud Consoleでもたいていの作業はできてしまいますが、CLI(コマンドでの操作)に慣れておくと、操作がサクサク進むのと、作業の自動化にもつながりますので、ぜひbqコマンドを使いこなしてください。

また本記事で記載しているようなBigQueryの操作に関する内容に加え、Google Cloudのデータエンジニアリング関連のサービスについては以下の書籍で詳しく記載があるので、ご興味があれば目を通していただけると幸いです。

https://amzn.to/3oZrDv0

BigQuery