データ基盤を運用していると、テーブル定義書の作成・更新作業に追われることってありませんか？
私も以前は、BigQueryのテーブルが増えるたびに手動でスプレッドシートを更新していました。カラム名、データ型、NULL許可…地味に面倒な作業ですよね。
そこで今回は、Google Apps Script（GAS）とBigQuery APIを使って、テーブル定義書を自動生成する仕組みを作ってみたので、その方法を共有します。
この記事でできるようになること
- BigQueryのテーブル情報を自動取得
- 論理名（カラムの説明）も自動で取得
- スプレッドシートに定義書フォーマットで出力
- メニューからワンクリックで実行
全体の仕組み
処理の流れはシンプルです。
- GASからBigQuery APIを呼び出す
- INFORMATION_SCHEMAからカラム情報を取得
- 取得したデータをスプレッドシートに書き込む
BigQueryにはINFORMATION_SCHEMAという便利なビューがあって、テーブルやカラムのメタ情報を取得できます。これをSQLで引っ張ってくるわけですね。
事前準備
1. BigQuery APIの有効化
GASエディタで「サービス」からBigQuery APIを追加します。左側の「＋」ボタンから「BigQuery API」を選択してください。
2. スプレッドシートの準備
出力先のスプレッドシートを用意します。今回は「テーブル定義書」というシートを作成し、12行目までをヘッダー領域として使います。
ヘッダー行（13行目）には以下の項目を設定しておきます：
- No.
- 物理名
- 論理名
- データ型
- Not Null
- デフォルト
- 備考
GASのコード
では実際のコードを見ていきましょう。
メイン処理
function generateTableDefinition() {
const projectId = 'your-project-id';
const datasetId = 'your_dataset';
const tableId = 'your_table';
// INFORMATION_SCHEMAからカラム情報を取得するクエリ
// COLUMN_FIELD_PATHSを使うとdescription（論理名）も取得できる
const query = `
SELECT
c.ordinal_position,
c.column_name,
c.data_type,
c.is_nullable,
cfp.description
FROM \`${projectId}.${datasetId}.INFORMATION_SCHEMA.COLUMNS\` c
LEFT JOIN \`${projectId}.${datasetId}.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS\` cfp
ON c.table_name = cfp.table_name
AND c.column_name = cfp.column_name
WHERE c.table_name = '${tableId}'
ORDER BY c.ordinal_position
`;
// BigQuery APIでクエリ実行
const request = {
query: query,
useLegacySql: false
};
const response = BigQuery.Jobs.query(request, projectId);
const rows = response.rows;
if (!rows || rows.length === 0) {
SpreadsheetApp.getUi().alert('データが見つかりませんでした');
return;
}
// スプレッドシートに書き込み
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('テーブル定義書');
const startRow = 14; // ヘッダーの次の行から
const startCol = 2; // B列から
// 既存データをクリア
const lastRow = sheet.getLastRow();
if (lastRow >= startRow) {
sheet.getRange(startRow, startCol, lastRow - startRow + 1, 7).clearContent();
}
// データを整形して書き込み
const outputData = rows.map((row, index) => {
const fields = row.f;
return [
index + 1, // No.
fields[1].v, // 物理名（column_name）
fields[4].v || '', // 論理名（description）
fields[2].v, // データ型
fields[3].v === 'NO' ? 'YES' : '', // Not Null
'', // デフォルト（手動入力用）
'' // 備考（手動入力用）
];
});
sheet.getRange(startRow, startCol, outputData.length, 7).setValues(outputData);
SpreadsheetApp.getUi().alert(`${outputData.length}件のカラム情報を出力しました`);
}メニューの追加
毎回スクリプトエディタを開くのは面倒なので、スプレッドシートにカスタムメニューを追加します。
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('定義書作成')
.addItem('カラム情報を生成', 'generateTableDefinition')
.addToUi();
}これで、スプレッドシートを開くと「定義書作成」というメニューが表示されるようになります。
ポイント解説
INFORMATION_SCHEMAの使い方
BigQueryのINFORMATION_SCHEMA.COLUMNSには、テーブルのカラム情報が格納されています。主なカラムは以下のとおりです。
ordinal_position：カラムの順番column_name：カラム名data_type：データ型（STRING、INT64など）is_nullable：NULL許可（YES/NO）
このビューをクエリするだけで、テーブル定義に必要な情報がほぼ揃います。
論理名（description）の取得
カラムの説明（description）はINFORMATION_SCHEMA.COLUMN_FIELD_PATHSに格納されています。BigQueryのコンソールやDDLでカラムに設定した説明がここに入ります。
COLUMNSとCOLUMN_FIELD_PATHSをJOINすることで、論理名も一緒に取得できるわけです。
-- descriptionを取得するにはCOLUMN_FIELD_PATHSをJOIN
SELECT
c.column_name,
cfp.description -- これが論理名になる
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.COLUMN_FIELD_PATHS cfp
ON c.table_name = cfp.table_name
AND c.column_name = cfp.column_nameLEFT JOINにしているのは、descriptionが設定されていないカラムも取得するためです。descriptionが空の場合はnullが返ってくるので、コード側で空文字に変換しています。
Not Nullの表記について
ちょっとややこしいのがis_nullableの値です。
is_nullable = 'YES'→ NULLを許可する（空欄でOK）is_nullable = 'NO'→ NULLを許可しない（Not Null制約あり）
定義書では「Not Null」列に「YES」と表示したいので、is_nullableが「NO」のときに「YES」を出力するよう、条件を反転させています。
複数データセットへの対応
実際の運用では、複数のデータセットを扱うことが多いと思います。その場合は、データセットごとに関数を分けるか、引数でデータセットIDを受け取るようにすると便利です。
function generateTableDefinitionForDataset(datasetId) {
// datasetIdを引数で受け取る
const projectId = 'your-project-id';
// ... 以下同様
}
// メニューから呼び出す用のラッパー関数
function generateForStagingDataset() {
generateTableDefinitionForDataset('staging_dataset');
}
function generateForProductionDataset() {
generateTableDefinitionForDataset('production_dataset');
}実行結果
メニューから「カラム情報を生成」をクリックすると、数秒でスプレッドシートにカラム情報が出力されます。
論理名はBigQueryに設定されているdescriptionから自動で取得されます。あとは「備考」を必要に応じて手動で埋めれば、定義書の完成です。カラムの追加・削除があった場合も、再度実行すれば最新の状態に更新できます。
ちなみに、BigQueryでカラムにdescriptionを設定するには、DDLで以下のように記述します。
ALTER TABLE your_dataset.your_table
ALTER COLUMN user_id SET OPTIONS (description = 'ユーザーID');Dataformを使っている場合は、sqlxファイルのconfigでcolumnsを定義すれば自動的にdescriptionが設定されます。
注意点
初回実行時の認証
初めて実行するときは、BigQuery APIへのアクセス許可を求められます。「許可」をクリックして進めてください。
実行時間の制限
GASには6分の実行時間制限があります。通常のテーブルであれば問題ありませんが、数百カラムあるような巨大テーブルの場合は注意が必要です。
権限について
スクリプトを実行するユーザーには、対象のBigQueryデータセットへの読み取り権限が必要です。権限がない場合はエラーになります。
まとめ
今回は、GASとBigQuery APIを使ってテーブル定義書を自動生成する方法を紹介しました。
手動でポチポチ入力していた作業が、ワンクリックで完了するようになると、地味に嬉しいですよね。特にテーブル数が多いプロジェクトでは、この仕組みがあるだけでかなり楽になります。
INFORMATION_SCHEMAには他にもTABLESやPARTITIONSなど便利なビューがあるので、興味があれば調べてみてください。
この記事が参考になれば幸いです。
コメント