💡

スプレッドシートで管理していたテーブルコメントをDDLに反映する仕組みを作った

に公開
Google Cloud
MySQL
DB
tech

はじめに

既存システムのテーブル定義は、スプレッドシートで管理されていました。

カラムの意味や補足などの情報はスプレッドシート側にしか存在せず、
DBのDDLにはコメントがほとんど付いていない状態でした。

そのため、

  • カラムの意味を確認するたびにスプレッドシートを見に行く必要がある
  • DDLと定義書の二重管理になっている
  • コメントの整備や更新が大変

といった課題がありました。

そこで、スプレッドシートにあるコメント情報を元に、
DDLへCOMMENTを反映する仕組みを作りました。

やりたかったこと

  • スプレッドシートからコメントを取得
  • 既存のDDLと比較
  • ALTER TABLEでコメントを付与

全体の流れ

  1. 既存のDDLを取得
  2. スプレッドシートからコメントを取得
  3. 差分をもとにALTER文を生成
  4. SQLとして出力

既存DDLの取得

DDLはmysqldumpを使って取得しました。

mysqldump --no-data --skip-comments --compact \
  -u user -p --host=127.0.0.1 --port=3306 db_name \
| grep -vE '^(CREATE TABLE|DROP TABLE).*20[0-9]{2}' > create_tables.sql

ログテーブル（YYYYMM系）を除外するため、grepでフィルタしています。

実行時に以下のような警告が出たため、

  • --set-gtid-purged=OFF
  • --single-transaction

を追加しました。

mysqldump --no-data --skip-comments --compact --set-gtid-purged=OFF --single-transaction \
  -u user -p --host=127.0.0.1 --port=3306 db_name \
| grep -vE '^(CREATE TABLE|DROP TABLE).*20[0-9]{2}' > create_tables.sql

スプレッドシートからのデータ取得

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/spreadsheets"
]

credentials = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
client = gspread.authorize(credentials)

spreadsheet = client.open("テーブル定義")
worksheet = spreadsheet.worksheet("シート1")

data = worksheet.get_all_records()

ALTER文の自動生成

def generate_alter_table_sql(table_name, column_name, data_type, is_not_null, default_value, comment):
    sql = f"ALTER TABLE {table_name} MODIFY COLUMN {column_name} {data_type}"

    if is_not_null:
        sql += " NOT NULL"

    if default_value and default_value != "NULL":
        sql += f" DEFAULT {default_value}"

    if comment:
        sql += f" COMMENT '{comment}'"

    sql += ";"

    return sql

実装上のポイント

既存定義を壊さない

MODIFY COLUMNは定義をすべて書く必要があるため、
既存DDLから情報を取得してそのまま使うようにしました。

YYYYMMテーブルの扱い

import re

re.sub(r"YYYYMM", target_date, table_name)

ハマったポイント

Google Sheets API のレート制限

Quota exceeded for quota metric 'Read requests'

対応

ディレイ

import time
time.sleep(1.5)

リトライ

from tenacity import retry, stop_after_attempt, wait_fixed

@retry(stop=stop_after_attempt(3), wait=wait_fixed(2))
def get_data():
    pass

結果

Before After
コメントはスプシにしかない DDLにもコメントが付く
定義確認のたびにスプシを見る DBだけで意味が分かる
手作業 自動生成

おわりに

大きな変更ではないですが、
スプレッドシート依存を減らす第一歩になりました。

Discussion