bqコマンドを使ってBigQueryのViewに対する操作をCLIから行う

: 2020-07-08 | : 2020-07-08

運用フロー改善の一環として、BigQueryのViewをGitHubで一元管理できるようにしました。

その過程で、CLIからViewの操作（Create, Backup, Update, Delete）を行えるようbqコマンドを利用したシェルスクリプトを組みました。この課題で得られたノウハウをまとめます。

BigQueryのViewについて

BigQueryにはViewを作成、保存する機能があります。複数のテーブルをJOINしたような複雑なクエリを保存し、あとから繰り返し呼び出すことができて便利です。

BigQueryのコンソール画面にはQuery Editorと呼ばれるエディターがあるのですが、リンターや入力補完機能が標準で備わっているため、BigQueryのSQLを書く場合はこのQuery Editorをよく使います。

bqコマンド

bqコマンドラインツールというものがあります。

Cloud SDKをインストールすると使えるようになるのですが、手元のPCのターミナルからBigQueryのViewを作成・更新・削除したり、Viewの実行結果を出力などができます。（ちなみにCloud SDKは、GCPのリソースを管理するためのツールが一つにまとまったパッケージのようなものです）

シェルスクリプトやGo言語と組み合わせれば自動化も可能です。

GCPプロジェクトの指定

bqコマンドを使うには、CloudSDKをインストールしたのち、GCPプロジェクトを指定します。

# プロジェクトの指定

gcloud config set project your-gcp-project-111
gcloud config list

bqコマンドでViewを操作

Linuxのコマンドと似ていて、作成は bq mk、削除は bq rm というスタイルなので直感的でわかりやすいです。

Viewの作成

# --viewオプションにSQL文を直接指定する場合
bq mk --view='select 1 as num' ${PROJECT_ID}:${DATASET_NAME}.${VIEW_NAME}

# ↓ファイルを指定する場合
bq mk --view_udf_resource=path/to/file.jq

Viewの更新

bq update --use_legacy_sql=false --view="${SQL}"

Viewの削除

bq rm ${PROJECT_ID}:${DATASET_NAME}.${VIEW_NAME}

Viewの情報取得

BigQueryのdatasetに対してlsコマンドを打つことができます。

# datasetのオブジェクト取得
bq ls --format=pretty ${PROJECT_ID}:${DATASET_NAME}

bq ls コマンドでもViewの情報を取得できますが、Viewの情報のみを取得する場合は bq query コマンドが楽でした。

# 対象のデータセット内のView情報を取得
bq query --nouse_legacy_sql 'SELECT * FROM ${DATASET_NAME}.INFORMATION_SCHEMA.VIEWS'

bq query は柔軟なコマンドで、例えば下記のように、テーブル名とView定義（SQL）のみ指定して、json形式で出力することもできます。

# --formatオプションでjson形式を指定し、テーブル名とView定義を出力。
bq query --nouse_legacy_sql --format=json \
        "SELECT table_name, view_definition FROM ${DATASET_NAME}.INFORMATION_SCHEMA.VIEWS"

json形式で出力できれば、jqコマンドなどでパースしたり、好きなように処理が可能になります。

シェルスクリプトでbqコマンドを使う

下記はコーディングしたシェルスクリプトの一部です。 bq query コマンドでBigQueryからViewの情報を取得し、jsonファイルとしてダウンロード。その後jsonファイルをjqコマンドでパースしてViewの定義情報を抜き取り、sqlファイルにして保存しています。

# The save_temporary_sql saves SQL files ./tmp/ directory.
function save_temporary_sql () {
    local TABLE_NAME=`cat ./tmp/view_list.json | jq -r ".[${i}].table_name"`
    # touch "${PWD}/sql/${TABLE_NAME}.sql"
    cat ${PWD}/tmp/view_list.json | jq -r ".[${i}].view_definition" > "./tmp/${TABLE_NAME}.sql"
}

# The backup_bigquery_view backs up BigQuery View.
function backup_bigquery_view () {
    mkdir -p "${PWD}/tmp"
    output_log "INFO" "Download views: ${PROJECT_ID}.${DATASET_NAME}."
    bq query --nouse_legacy_sql --format=json \
        "SELECT table_name, view_definition FROM ${DATASET_NAME}.INFORMATION_SCHEMA.VIEWS" \
        > ${PWD}/tmp/view_list.json
    local VIEW_LIST_LENGTH=`cat ./tmp/view_list.json | jq -r '. | length'`

    for (( i=0; i<${VIEW_LIST_LENGTH}; i++ ))
    do
       save_temporary_sql
    done

    output_log "INFO" "Update views in ./sql/"
    rsync --checksum -av --exclude='view_list.json' ${PWD}/tmp/ ${PWD}/sql/
    # rm -rf "${PWD}/tmp"
}

まとめ

bqコマンドでViewを操作する方法について簡単にまとめました。

業務ではViewのバックアップ、新規作成、更新、削除をコマンドラインから実行できるシェルスクリプトを組んだのですが、シェルスクリプトでやるには少し複雑なコードになってしまいました。

次回似たようなCLIツールを作る場合はGoで作ろうと思います。