こんにちは。freeeでWebアプリケーションエンジニアをしている金山（@tkanayama_）です。
普段はいわゆるプレイングマネージャとして、freee人事労務というプロダクトの新機能開発を行っています。ですが今回は、そのメインの業務のかたわらで行った、データベースにまつわる小話を記事にしたいと思います。
- モチベーション編: アプリケーション内にどんなデータがあるのか明確にしたい！
- 実現方法編1: SQL文の検討
- 実現方法編2: マイグレーションファイルの作成
- 実現方法編3: マイグレーションの実行
- おわりに
モチベーション編: アプリケーション内にどんなデータがあるのか明確にしたい！
freee人事労務のアプリケーションのデータベースは300を超えるテーブルから構成され、そのカラム数を合計すると5,000カラム弱も存在します。しかしながら、これまでそれらのカラムのうち半分未満のカラムにしかカラムコメントが付与されておらず、そのカラムにどんなデータが格納されているのかを推測するための情報が不足していました。特に、もはや直接関わった人が誰もいないような開発初期に作成されたテーブルにおいて、この傾向が特に顕著でした。
この状態だと、特に新たに参画したエンジニアがデータ構造の概要を掴むのに時間がかかってしまいます。また、プロダクトマネージャなどがプロダクトに対する新たな機能を検討する際にも、現状どのようなデータが存在するのか把握しづらいことは障壁になり得ます。
そこで、今回はこうしたカラムコメントが付与されていない大量のカラムに対して一括で安全にコメントを付与する方法を検討し、実行しました（なお、本記事では簡単のためカラムコメントにのみ言及していますが、テーブルコメントに関しても同様の議論が成り立ちます）。
補足: カラムコメント以外の方法の検討
既存のテーブルに対してカラムコメントを付与しようと考えたとき、最も気になるのはやはりそのリスクやコストです。なぜなら、本番データベースに対する操作を行う必要が出てくるからです。そこで、もっと手軽に前述のモチベーションを達成できる方法も検討しておきます。
例えば、データに関する説明をGoogle スプレッドシートのような外部のドキュメントで管理するという方法が考えられます。この方法であれば、非常にカジュアルにコメントの追加・編集ができます。しかしながら、今後新しいテーブルを定義したり既存のカラムを変更したりするたびに、外部ドキュメントも同時に変更することを習慣づける必要が出てきてしまうというデメリットがあります。継続的に保守が必要な外部ドキュメントを増やしてしまうのは極力避けたいです。他にもBigQueryにおけるスキーマ説明欄を活用するなども検討しましたが、コメントを書けるのがBigQueryに連携されているデータだけに限定されてしまうというデメリットがあります。
そこで、コストがかかることは承知の上でできるだけ安全にカラムコメントを一括で付与することにトライし、もし難しそうであればこうした代替案を検討しようという方向で進めました。
実現方法編1: SQL文の検討
※ freee人事労務では現在、RDBMSとしてMySQL 5.7を、WebアプリケーションフレームワークとしてRuby on Rails 6系を採用しています。以下では、この組み合わせを前提として話を進めます。
MySQLのドキュメントによると、カラムコメントは以下のようなSQL文により変更できます。
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
また、以下のように ALGORITHM および LOCK を明示すると、より安心して実行することができます。
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column' ALGORITHM=INPLACE, LOCK=NONE;
例えば、以下のようにカラムの型を VARCHAR(32) から VARCHAR(16) に変更するような操作は ALGORITHM=INPLACE では実行できないため、これを明示しておくことによって意図しない操作を防ぐことができます。
mysql> SHOW CREATE TABLE t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` varchar(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql> alter table t1 modify c1 varchar(16), algorithm=inplace, lock=none; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. mysql> alter table t1 modify c1 varchar(16); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
さらに、1テーブルに対して複数のカラムのコメントを変更したい場合は、以下のように記述すると1回のALTERだけで実行できます。
ALTER TABLE `t1` MODIFY COLUMN `col1` BIGINT UNSIGNED DEFAULT 1 COMMENT 'comment1', MODIFY COLUMN `col2` BIGINT UNSIGNED DEFAULT 1 COMMENT 'comment2', ALGORITHM=INPLACE, LOCK=NONE;
そこで、このようなSQL文を発行すためのマイグレーションファイルを記述する方法を考えます。
補足: Railsが提供しているAPIを活用する方法の検討
Ruby on Railsでは、カラムコメントを変更するためのAPIとして change_column_comment というメソッドが用意されており、例えばマイグレーションファイル内に以下のように記述することで、カラムコメントを変更できます。
change_column_comment :t1, :col1, "this is a comment"
この方法であれば、必要な情報は変更したいテーブル名・カラム名、およびコメントのみであり、先ほどのSQL文のように明示的にカラムの型などを記述する必要がありません。単一のカラムに付与されたカラムコメントを変更する場合は、この方法で変更するのが最も手軽だと考えられます。
しかしながら、（少なくとも現時点で私たちが利用しているRailsのバージョンでは、）同一テーブルの複数のカラムに対するコメント変更を、1回のALTER文のみで変更するためのAPIは存在しないようでした。
つまり、例えば
change_table(:t1, bulk: true) do |t| t.change_column_comment :col1, "comment1" t.change_column_comment :col2, "comment2" end
のようなことはできないようです。t.change などを用いればカラムコメントを更新できますが、この方法では結局カラムの型情報を明示する必要が出てきます。
実現方法編2: マイグレーションファイルの作成
さて、さきほど検討したSQL文を発行すためのマイグレーションファイルを記述していきます。
しかしながらこのSQL文を構築するためには、カラムコメントだけでなく型情報（およびデフォルト値やNOT NULL制約など）も明記する必要があります。少数のカラムコメントを変更するだけなら手作業でなんとかなりそうですが、今回は300テーブル5000カラムに対して適用したいので、効率よくSQL文を構築する必要があります。
Ruby on Railsでは、ActiveRecord::Base.connection.tables や ActiveRecord::Base.connection.columns といったAPIを利用して、Rubyスクリプト上から容易にデータベースの情報にアクセスすることができます。これを利用して、以下のようなスクリプトを実行することで一気にマイグレーションファイルの生成を行いました。
def execute filenames = %w[comments1 comments2] comments = filenames.reduce({}) { |result, filename| result.merge(csv2hash("path/to/comments/#{filename}.csv")) } statements = generate_statements(comments) statements.each do |table_name, statement| script = generate_script(statement, table_name) dump_script(script, table_name) sleep(1) # timestampが被らないようにする end end private def csv2hash(path) csv = CSV.read(path) csv_without_header = csv.drop(1) csv_without_header .map .with_object(Hash.new { |h, k| h[k] = {} }) do |row, hash| key = row[2] value = { row[3] => row[4] } hash[key] = hash[key].merge(value) end end def generate_statements(comments_hash) ActiveRecord::Base .connection .tables .each_with_object({}) do |table_name, statements| column_comments = comments_hash[table_name] next if column_comments.nil? modify_column_statements = ActiveRecord::Base .connection .columns(table_name) .each_with_object([]) do |column, modify_column_statements| comment = column_comments[column.name] next if comment.nil? default = if column.default.nil? '' elsif column.default.is_a?(String) "DEFAULT '#{column.default}' " else "DEFAULT #{column.default} " end modify_column_statements.push( "MODIFY COLUMN `#{column.name}` #{column.sql_type} #{column.null ? '' : 'NOT NULL '}#{default}COMMENT '#{comment}',", ) end next if modify_column_statements.empty? statement = <<~EOS execute \" ALTER TABLE `#{table_name}` #{modify_column_statements.join("\n ")} ALGORITHM=INPLACE, LOCK=NONE;\" EOS statements[table_name] = statement end end def generate_script(statement, filename) <<~EOS class AddCommentForColumns#{filename.camelize} < ActiveRecord::Migration[6.0] def up #{statement.chomp} end def down raise ActiveRecord::IrreversibleMigration end end EOS end def dump_script(script, filename) timestamp = Time.zone.now.strftime('%Y%m%d%H%M%S') File.open("path/to/output/#{timestamp}_add_comment_for_columns_#{filename}.rb", 'w') { |f| f.puts script } end
なお、このスクリプト上に出てくる comments1.csv などは、今回付与したいコメントが記述されたトータル5000行のファイルです。これは、開発チームのメンバーで手分けして記入していただきました。
これを実行すると、例えば以下のようなマイグレーションファイルがテーブル数分（つまり今回の事例では300ファイルほど）生成されます。
class AddCommentForColumnsUsers < ActiveRecord::Migration[6.0] def up execute " ALTER TABLE `users` MODIFY COLUMN `year` int(11) NOT NULL COMMENT '年', MODIFY COLUMN `month` int(11) NOT NULL COMMENT '月', ALGORITHM=INPLACE, LOCK=NONE;" end def down raise ActiveRecord::IrreversibleMigration end end
あとはこの生成スクリプトの信頼性をどのように担保するのかというのが問題になります。Ruby on Railsを用いたWebアプリケーションでは現時点での最新のスキーマを db/schema.rb というファイルに吐き出している場合が多いため、このファイルを活用できそうです。つまり、この生成スクリプトで生成したマイグレーションファイルを用いてマイグレーションを行い、その結果出力される db/schema.rb を変更前の db/schema.rbと比較し、差分がカラムコメントのみであることを確認できれば、結果的にこのスクリプトにより生成されたマイグレーションファイルが安全であると言えそうです。
ただし、開発環境のDBのスキーマが必ずしも本番環境のものと完全に同一とは言い切れない*1ため、なるべく本番のスキーマと同等の環境を用いて動作確認するのが良いでしょう。
実現方法編3: マイグレーションの実行
先ほど説明した方法で生成したマイグレーションファイルを用いて、検証用環境で問題なく動作することを確認します。その結果、各migrationの実行がテーブルの大きさに関わらず0.05秒程度で完了すること・CPU負荷にも問題がないことを確認できました。
== 20230614182534 AddCommentForColumnsAbPatterns: migrating ===================
-- execute("\n ALTER TABLE `ab_patterns`\n MODIFY COLUMN `pattern` varchar(255) NOT NULL COMMENT 'ABパターン名',\n MODIFY COLUMN `mapping` varchar(255) NOT NULL COMMENT 'ABパターン内でどのパターンへ振り分けられているか',\n ALGORITHM=INPLACE, LOCK=NONE;")
-> 0.0560s
== 20230614182534 AddCommentForColumnsAbPatterns
（中略）
== 20230614182958 AddCommentForColumnsWorkingHoursWeekdaySettings: migrating ==
-- execute("\n ALTER TABLE `working_hours_weekday_settings`\n MODIFY COLUMN `day_of_week` varchar(255) NOT NULL COMMENT '曜日',\n MODIFY COLUMN `applied_holiday` tinyint(1) NOT NULL COMMENT '所定休日かどうか',\n MODIFY COLUMN `normal_work_start_time` varchar(255) COMMENT '出勤時刻',\n MODIFY COLUMN `normal_work_end_time` varchar(255) COMMENT '退勤時刻',\n ALGORITHM=INPLACE, LOCK=NONE;")
-> 0.0418s
== 20230614182958 AddCommentForColumnsWorkingHoursWeekdaySettings: migrated (0.0418s)
あとは本番環境に適用するのみですが、ここで注意が必要です。このDDLの実行時間は、検証環境で試した通り0.05秒程度と一瞬で、かつOnline DDLであるためDDLの実行中に他のDMLの実行をブロックしません。しかしながら、だからといっていつでも気軽に本番環境に適用できるわけではありません。
今回のカラムコメント変更DDLを実行するために、当然metadata lockを取得する必要があるわけですが、すでに他のトランザクションで使用されているテーブルはmetadata lockが解放されないため、metadata lockが取得できるまでこのDDLの実行は待機させられます。freee人事労務には、給与計算に関する情報（勤怠入力状態や基本給設定など）が更新されるたび自動的に給与計算をし直して常に最新の結果に更新する機能があり、そのためユーザのアクティビティの多い日勤帯は給与計算処理のトランザクションが切れ目なく存在している状態です。このような時間帯は、metadata lockの取得ができずDDLがブロックされ続けることが予想されます。
さらに、単に待機時間が長くなるという話では終わりません。なぜなら、待機中のDDLが存在すると他のDMLの実行をブロックしてしまうため、長期間待機しているDDLが存在することは非常に危険だからです。該当テーブルに対する読み書きがいつまで経ってもできず、最悪の場合、システムダウンに繋がる恐れもあります。freee人事労務ではこれを防ぐために、DDLの待機時間を最大3秒までに制限しているため、実際にはそこまで危険なことにはなりませんが、何度実行してもカラムコメント変更が成功しないことが予想されます。
そこで、以下の3点を心がけました。
ユーザーのアクティビティの少ない時間帯に実行すること
- これは言うまでもない工夫ですが、DBの読み書きが多く発生している時間帯はmetadata lockの取得が困難です。freee人事労務はそのサービスの性質上、夜間はアクティビティが一気に減少するので、その時間帯を狙って実行しました。
ALTER文の抑制
- これは「SQL文の検討」の項で少し触れましたが、1 テーブルに対して1 ALTER文で済むようなクエリを書くことが重要です。さもなくば、例えば1テーブルに20カラムほど存在する場合、20回 metadata lockを取得する必要があり、その間に割り込みでlockを獲得されてしまうとDDLが失敗してしまいます。
マイグレーションファイルを分割する
- Ruby on Railsでは、1度実行が完了したマイグレーションファイルは
schema_migrationsというテーブルに記録され、再度マイグレーションが実行されないようになるという仕組みがあります。そこで、1テーブルごとにマイグレーションファイルを分割しておくと、例えば100テーブルぶんのカラムコメント変更が完了したタイミングでmetadata lockを取得できずに終了してしまった場合でも、再実行すれば101テーブルめから再実行することができます。実際、夜間帯に実行しても何度かLock wait timeoutが発生してしまったため、マイグレーションの再実行を何度か行いました。
- Ruby on Railsでは、1度実行が完了したマイグレーションファイルは
以上で、無事に5,000カラムのコメントを更新することができました（完）
おわりに
今回はカラムコメントの変更という些細な題材ではありましたが、大量のカラムに対して変更を加えようとすると考慮すべきことが多くとても楽しく取り組むことができました。
freeeでは新機能の開発だけでなく、こうした品質改善にも十分時間を割いて取り組んでいます。また、今回のプロジェクトの実施にあたり、品質改善チームのkeikさん・Database Reliability EngineeringチームのJuniさんに、技術的なアドバイスをいただきました。こうした突発的なプロジェクトに対しても、経験豊富なメンバーが力を貸してくださるので心強いですね。
この記事を読んで、少しでもfreeeでの開発ご興味持ってくだされば幸いです。
*1:余談ですが、私たちも今回のプロジェクトがきっかけで、開発環境のDBのスキーマが本番環境のものとわずかに乖離していることに気づいたため、修正を行いました
