BigQueryからテーブル定義情報をCSV抽出して、BigQueryで一覧化してみる

はじめに

担当しているプロジェクトでテーブル定義情報をスプレッドシート等で一覧管理していて、
可読性やメンテナンスに課題を感じたことはないでしょうか？

フィルタ・ソート・検索・関数等を使ってデータを検索している
共同利用していて他のメンバが誤ってデータを更新してしまった
テーブル定義のドキュメントとBigQuery上のテーブル定義が違っている

今回その課題に対して、BigQueryのテーブル定義情報をCSV出力し、
その情報をもとにBigQuery上のテーブルで一覧で見れるようにしてみました。

またCSV出力したものをそのままドキュメントとして一覧管理することもできるかと思います。

BigQueryへの取込の為のリソースを準備する

はじめにBigQueryに取り込む為のリソースを準備します。
使用するリソースは下記の3つとなります。

BigQuery（Table）
Google Cloud Storage（Bucket）
BigQuery Data Transfer Service

BigQuery（Table）の準備

まずBigQueryに任意の名前でデータセットを作成します。
※既存のデータセットを利用する場合は、スキップしてください

作成したデータセット（または既存のデータセット）に任意の名前でテーブルを作成します。
この際にスキーマは、上記で抽出したテーブル定義と対応したカラムを用意します。
※「作成日時」に関しては「TIMESTAMP」型を指定して作成

Google Cloud Storage（Bucket）の準備

次に次工程で作成するテーブル定義情報のCSVファイルをBigQueryにアップロードする為の
Cloud StorageのBucketを任意の名前で作成します。

BigQuery Data Transfer Serviceの準備

最後にCloud StorageのBucketに格納したテーブル定義情報のCSVファイルを
BigQueryのテーブル定義テーブルに取り込む為の転送サービスを任意の名前で作成します。
※今回は手動取込とする為、「スケジュールオプション」は、オンデマンドを選択

BigQueryからテーブル定義情報をCSV出力する

GoogleCloudのリソース準備が整いましたら、BigQueryからテーブル定義情報をCSV出力してみます。
今回は、Cloud Shellを利用して、bqコマンドでBigQueryから定義情報をCSVファイルに出力します。

Cloud Shellをアクティブにし、対象プロジェクトを指定した後、ターミナルから以下コマンドを実行します。
コマンドを実行したディレクトリと、コマンド内で指定したGoogle Storageのバケット^※2の2か所に「定義情報抽出対象テーブル名^※1_実行日時.csv」のファイル名でCSV出力されます。
※1　"DATASET_NAME"と"TABLE_NAME"は、定義情報抽出対象のテーブルのものを記載してください
※2　"GCS_URL"は、CSVファイル出力先のGoogle StorageのバケットURLを記載してください

DATASET_NAME=test_dataset;TABLE_NAME=reference_table;GCS_URL="gs://xxxxx_xxxxx/";DATE=`TZ=JST-9 date '+%F %T'`;CSV_FILE_NAME=${TABLE_NAME}_`TZ=JST-9 date +%Y%m%d%H%M`.csv; bq show --schema ${DATASET_NAME}.${TABLE_NAM E} | jq -r ".[] | [\"${DATASET_NAME}\", \"${TABLE_NAME}\", .name, .type, .mode, .description, \"${DATE}\"] | @csv" > ${CSV_FILE_NAME} | gcloud storage cp ${CSV_FILE_NAME} ${GCS_URL}CSV出力されるカラムは、
"データセット名","テーブル名","フィールド名","種類","モード","説明","作成日時"の7カラムとなります。
出力されたCSVファイルイメージは下記となります。
"test_dataset","reference_table","USER_ID","STRING","NULLABLE","ユーザーID","2024-12-20 18:04:26" "test_dataset","reference_table","BIRTH_DATE","DATE","NULLABLE","生年月日","2024-12-20 18:04:26" "test_dataset","reference_table","SEX","STRING","NULLABLE","性別","2024-12-20 18:04:26"

テーブル定義情報CSVファイルのBigQuery取込実行

GoogleCloudのリソースとテーブル定義情報CSVファイルの準備が整いましたので、
実際にテーブルへの取込を行います。

上記CloudShellから実行したコマンドで作成したテーブル定義情報CSVファイルが、
CloudStorageのバケットに出力されているか確認します。

CSVファイルがあることを確認したら、BigQuery Data Transfer Serviceを手動実行し、
BigQueryのテーブルに書き込みを行います。

OKボタンを押下すると転送がはじまりますので、しばらく待ちます。

処理が正常に終了すると、ステータスが変わります。

BigQueryのテーブルにも想定通りに書き込まれていることを確認できました。

まとめ

本ブログでは、「BQコマンド」と「CloudStorage」「TransferService」を使い、簡易にBigQueryのテーブル上にテーブル定義情報を一覧化する方法に関して、ご紹介させて頂きました。
冒頭にも記載致しましたが、「BQコマンド」で出力したCSVファイルをもとに、スプレッドシート等でテーブル定義情報を一覧化することも可能かと考えます。
今後の展望と致しましては、Google Cloudの「Dataproc」サービスを利用し、手動ではなく、自動でテーブル定義を一覧化し、管理できるような仕組みを検討していきたいと思っています。