【BigQuery】INFORMATION_SCHEMAを使ってテーブル定義書を自動作成する方法

はじめに

テーブル定義書を手動で更新していると、更新の遅れや漏れ、記載ミスが発生することがあります。
解決策としてBigQueryのINFORMATION_SCHEMAとGoogleスプレッドシートを使ってテーブル定義書を自動更新する方法を検討したので紹介します。

今回作成したテーブル定義書の構成

定義書作成までの全体像

1. INFORMATION_SCHEMAになかったり、取りづらい情報（カラム論理名やカラム説明など）をスプレッドシートの入力シートに記載し、BigQueryのカラム情報テーブルに取り込む
2. カラム情報テーブルとINFORMATION_SCHEMAの情報を組み合わせてスキーマテーブルを作成する
3. スキーマテーブルをスプレッドシートの抽出データシートに取り込む
4. 抽出データシートから各テーブルの定義書シートに反映する

※ 簡易的なテーブル定義書であれば、入力シートとカラム情報テーブルは省略してもOK

BigQuery

●スキーマテーブル

BigQueryのINFORMATION_SCHEMAから取出したメタ情報に、カラム情報テーブル（スプレッドシートで入力してBigQueryに接続させたテーブル）をJOINして作成したビュー（物理テーブルではない）です。

●カラム情報テーブル

スプレッドシートで入力した情報を、BigQueryに接続させたものです。スプレッドシートに入力したデータはBigQuery側で物理的に保存しているわけではなく、実データを確認するためには以下のテーブルを参照してクエリを書く必要があります。

スプレッドシート

●入力シート

INFORMATION_SCHEMAになかったり、取りづらいメタ情報を入力してBigQueryに取り込むシートです。pkやnot_nullのような情報はBigQuery側で設定すればINFORMATION_SCHEMAから取得可能です。しかし、メタ情報の設定有無はテーブル作成者に大きく依存してしまうため、元からスプシで管理と入力することを想定しています。

●抽出データシート

BiqQueryで作成したスキーマテーブルを、スプレッドシートに取り込んだものです。

●定義書シート

上記の抽出データシートを使用して作成する、各テーブルごとの定義書です。

作成手順と更新方法

スプレッドシートとBigQueryのテーブルを連携する

手順

1. テーブルを作成したいデータセットで「テーブルを作成」を選択
2. 必要事項を入力
   • ソース
     • テーブルの作成元: 「ドライブ」を選択
     • ドライブのURIを選択: 連携したいスプレッドシートのURLを入力
     • ファイル形式: 「Googleスプレッドシート」を選択
     • シート範囲: 必要に応じてシート名やセル範囲を入力
   • 送信先
     • テーブル: テーブル名を入力
   • スキーマ
     • 自動検出だとうまくいかない場合があるので、手動で入力がおすすめ
   • 詳細オプション
     • スキップするヘッダー行: ヘッダーの行数を入力
       
3. 「テーブル作成」をクリック

更新方法

スプレッドシートが更新されると、自動でBigQueryのテーブルにも反映されます。

BigQueryのテーブルとスプレッドシートを連携する

手順

1. 「データ」→「データコネクタ」→「BigQueryに接続」を選択
   
   
   
2. プロジェクトを選択
   
   
   
3. データセットを選択
   
   
   
4. テーブルまたはビューを選択し「接続」をクリック
   
   
   
5. 接続が完了するとプレビュー画面が開くので、「抽出」をクリック
   
   
   
6. 挿入先のシートを選択
   
   
   
7. 抽出データシートが作成される

更新方法

• 画面左下の更新ボタンを押すと更新できる
  
  
  
• 更新オプションから更新スケジュールを設定することも可
  

※ クエリコストが発生する場合あり

スプレッドシートのテーブル定義書に反映する

手順

1. 定義書のフォーマットを作成する
2. QUERY関数を使って「テーブル名」欄に入力されたテーブルの情報を抽出データシートから取得する
   

更新方法

• シートを複製してテーブル名を変更するだけで定義書の作成が可能

まとめ

INFORMATION_SCHEMAにない情報は手動で更新する必要があるものの、カラム名やデータ型は常に最新の状態を定義書に反映することができるようになります。

特にテーブル数が多かったり、共同で作業する人がいる場合は役に立つので参考にしてください。

お役立ち資料をご活用ください！

データプロフェッショナルであるDXAメンバーが、業務経験をもとにお役立ち資料を作成しています！
おすすめ3選はこちら！

１．GA4 サンプルレポート｜Looker Studio　無料プレゼント
GA4の基本KPIが網羅されたテンプレートです！データを差し替えればすぐにご活用いただけます。

２．サンプル経営ダッシュボード | Tableau無料プレゼント
実際にご提供しているダッシュボードをテンプレート化したものです！是非構成やグラフ選択の参考にしてください。

３．データ活用の部長さん必見！データ活用 内製化の正しい進め方
これまでご支援してきた実績をもとに、データ活用の内製化の進め方を解説しています！組織のDXを担われている方、必読です！

以上、おすすめ3選でした！
すべて無料でダウンロードできますので、お気軽にご利用ください。