【備忘録】Excelで管理してるテーブル定義からBigQuery用のDDLを作成したい

はじめに

初投稿なので簡単なものだけどただ欲しかったので作ってみたものの備忘録

あんまり色んなとこで仕事したことないから一般的な管理はわからないが
テーブル定義の仕様書をExcelで管理しているところも多いはず

正直生成AIに丸投げしてDDL吐かせた方が早い気もするが仕様書をそもそも外部に読み込ますの自体が許されないことも多いのでこの記事では以下を実現します：

1. Excelセルからテーブル名を取得
2. 各シートのカラム情報をもとにDDLを生成

使用するライブラリ

以下のライブラリを使用します：

• pandas：Excelファイルの読み取り
• openpyxl：Excelファイルの操作（pandasに依存）

インストールされていない場合、以下のコマンドでインストールしてください。

pip install pandas openpyxl

Excelデータの例

サンプルのExcelファイルは以下のような構造です。(1.1のシートもおんなじ構造)

実装方法

1.Excelの読み込み

大体複数テーブルの定義があると思うのでsheet_nameをNoneにして全シート読み込み
・sheet_name=None：すべてのシートを辞書形式で取得。
・header=None：ヘッダー行を自動認識せずすべてのセルを取得。

import pandas as pd

# Excelファイルを読み込む
file_path = 'input.xlsx'
# sheet_name=Noneで全シート読み込み
sheets = pd.read_excel(file_path, sheet_name=None, header=None)

2.テーブル名を取得

サンプルだとB1セルにテーブル定義があるのでそこの値を取ってDDLに使用(1行目の２列目(0始まり))

table_names = {}

for sheet_name, df in sheets.items():
    # B1セルの値を取得
    table_name = df.iloc[0, 1]  # 1行目、2列目

print(table_names)

3.テーブル名を取得

サンプルだとB1セルにテーブル定義があるのでそこの値を取ってDDLに使用(1行目の２列目(0始まり))

for sheet_name, df in sheets.items():
    # B1セルの値を取得
    table_name = df.iloc[0, 1]  # 1行目、2列目
    df.columns = ["カラム名", "型", "モード", "備考"]
    print(table_name)

    df = df.iloc[3:]  # データ定義までスキップ

    ddl = f"CREATE OR REPLACE TABLE `{table_name}` (\n"
    columns = []
    for _, row in df.iterrows():
        # df.columnsで指定した列の値をそれぞれDDLに活用
        column_name = row["カラム名"].strip()
        data_type = row["型"].strip()
        mode = row["モード"].strip()
        description = row["備考"].strip()
        column_ddl = f"  `{column_name}` {data_type} {mode}"
        if description:
            column_ddl += f" OPTIONS(description='{description}')"
        columns.append(column_ddl)
    ddl += ",\n".join(columns) + "\n);"
    print(ddl)

出力結果

こんなのが出てくる

CREATE OR REPLACE TABLE `test.test1` (
  `column1` INTEGER NOT NULL OPTIONS(description='column1'),
  `column2` INTEGER REQUIRED OPTIONS(description='column2'),
  `column3` INTEGER NOT NULL OPTIONS(description='column3'),
  `column4` INTEGER NOT NULL OPTIONS(description='column4'),
  `column5` INTEGER NOT NULL OPTIONS(description='column5'),
  `column6` STRING NOT NULL OPTIONS(description='column6'),
  `column7` INTEGER NOT NULL OPTIONS(description='column7')
);

まとめ

これだけだとそこまで便利にはならないかもだけど、CREATE部分を仕様書ベースにしとけば変な間違いも減るかな？？
ロジックに少しは注力できそうだし、ある程度Excelの構成変わっても多分調整は少しでいいはず！！！！！！