リカバリー用SQLをさくっと準備してからDBデータを一括更新する #MySQL

何かしらサービスを運営していると、「特定のデータを一括で変更して欲しい」というような、データ運用上の要望がしばしば発生する。そういう時はたいていデータ管理用のGUIツール等では対応できない、もしくは手動運用では手間がかかり過ぎておよそ現実的でない、というようなケースであることが多く、エンジニアがデータベースのデータを直接更新する必要が出てくるのだ。

（本音としては、データベースに慣れている私でも、商用DBで直接UPDATEクエリを実行するとかやりたくないんだよ。マジで。まぁ、やらざるを得ないからやるけどさ…w）

閑話休題、ここからが本題である。
DBの特定テーブルのカラム単位で一括データ更新を行う際に、リカバリー用のSQLをさくっと準備して、「できる限り心穏やかに」データ更新ができる方法を紹介してみようかと。

まぁ、そこまで頻度が高くない運用シーンなので、毎回うろ覚えになっていた手順を自分用の備忘録としてまとめておこうかと思って書いた記事でもある。

SELECT結果を外部ファイルに出力する

まず基本知識として、SQLのSELECTクエリの結果を外部ファイルとして出力する方法を知らねばならない。私が得意なデータベースはMySQLとPostgreSQLなので、その二つのDBMSでのやり方を紹介する。

MySQLでは、

mysql

SELECT * FROM post_master INTO OUTFILE 'output_my.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';

MySQLではGLOBALオプション secure_file_priv で指定されているパスに対してのみファイルの入出力が許可されるため、このオプションがNULLになっているとそもそも外部ファイル出力は行えない。また、このオプションは読み込み専用で SET GLOBAL では設定変更できないため、my.cnf等の設定ファイルで指定して再読み込みさせる必要がある（MySQLの再起動が必要）。
[mysqld]
secure-file-priv = ""
テスト環境とかなら、上記設定を追加した設定ファイルを /etc/my.cnf 等に設置してMySQLを再起動すればOKだ。商用環境ならば、DB構築時にサーバ上のセキュアなパスを指定しておくことが望ましい。

PostgreSQLでは、

psql

COPY post_master TO 'output_pg.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '';

スーパーユーザじゃなかったり、pg_write_server_files権限がないユーザで実行すると下記のようにエラーになるので注意が必要だ。
ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
そんな時は、一旦DBコンソールから抜けて、コマンドラインからpsql -c '\copy ...'コマンドを使用すればCSV出力ができる。
例えば、上記のCOPYクエリの場合なら、
psql -U ユーザ名 -c "\copy post_master TO 'output_pg.csv' WITH CSV DELIMITER ',' FORCE QUOTE * NULL AS '';" データベース名
─とやれば良い。

ただ、これだとテーブル単位で全データを出力することになるので、利用シーンによっては都合が悪いこともある。

実例がある方が理解しやすいので、サンプルテーブル（テーブル名は post_master ）を準備して外部ファイル出力をやってみよう。

post_id	post_type	author_id	title	content	regist_date	limit_date	status
シーケンシャルな整数値 (PrimaryKey)	文字列	整数値	文字列	文字列	DATE型	DATE型	真偽値（0か１）

上記のようなテーブル（MySQLとPostgreSQLにそれぞれ同じテーブルを作成した）には、Fakerでダミーデータを1000行ほど入れた状態だ（入れたデータもそれぞれ同じだ）。このテーブルに対して、

limit_date（論理値としては、このテーブルデータの「有効期限」みたいな意味付けとする）の日付が現在以降
status（論理値としては、この行データが利用可能かどうかの「状態」とする）が有効（=1）

──の条件に合致する行データを、CSV形式で出力してみる。
まずは、条件に合致するデータを検索するクエリを作成する（この辺はMySQLもPostgreSQLも同じ）。

> SELECT COUNT(*) FROM post_master;
count
-------
1000
(1 row)
> SELECT COUNT(*) FROM post_master WHERE limit_date >= CURRENT_DATE AND status = 1;
count
-------
121
(1 row)

ともに条件に該当する行データは121件であった。つまり、データ抽出用のSELECTクエリは下記のようになる。

SELECT * FROM post_master WHERE limit_date >= CURRENT_DATE AND status = 1

このSELECT文を使ってCSV出力を行ってみる。

まずMySQLでは、

mysql

SELECT * FROM post_master WHERE limit_date >= CURRENT_DATE AND status = 1 
INTO OUTFILE 'out_my_pick.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
ESCAPED BY '"' LINES TERMINATED BY '\n';

次にPostgreSQLでは、

psql

COPY (SELECT * FROM post_master WHERE limit_date >= CURRENT_DATE AND status = 1) 
TO 'out_pg_pick.csv' WITH CSV DELIMITER ',' FORCE QUOTE *;

実際に出力されたCSVファイルはどちらも下記のようになる。

$ cat out_my_pick.csv | wc -l
121
$ cat out_pg_pick.csv | wc -l
121
$ diff out_my_pick.csv out_pg_pick.csv
$ head -5 out_pg_pick.csv
"33","webpage","10","Perspiciatis similique debitis et ut autem tempore dolorum.","うきいわねえさんにも...(以下省略)","2019-11-29","2024-09-01","1"
"36","other","7","Ipsam error aut illum consectetur quaerat enim aut.","はしずかにはいったと思うわぎが来ま...(以下省略)","2018-04-01","2020-05-01","1"
"42","other","2","Provident sit ipsum perferendis quos vel.","い、その火が燃もえて寄よったまをつるつるして、も...(以下省略)","2018-09-23","2022-10-02","1"
"46","other","1","Voluptates qui et at reprehenderit.","ひじょジョバンニ、おいおうの方へ行きました。するだけないと...(以下省略)","2017-10-17","2020-11-27","1"
"49","webpage","5","Assumenda ducimus aut consequuntur consequatur ea cumque.","とでもわざとうもろこびにな...(以下省略)","2017-04-16","2021-11-02","1"

ともに同一のCSVが出力されていることがわかる（ともに121行のCSVファイルで、diff取っても差分がない。最後のhead -5は、先頭5行を表示してCSVファイルのフォーマットを確認した）。
蛇足だが、この後、nkfなどで文字コードをシフトJIS＋改行コードをCRLFに変換してあげれば、MSエクセルで開けるCSVファイルにすることも可能だ。

とりあえず、これで基本はOKだ。

データ更新前に切り戻すためのリカバリーファイルを作成する

データベースでUPDATE文を実行して「一括データ更新が完了しましたよ〜」って報告した後に、「ごめん、更新する条件が間違ってたよ」とか「あ、更新する前にやらなきゃいけないこと忘れてた」などの予期せぬ事態が発生して、データを更新前に切り戻さなければならないケースがしばしばある（苦笑）。そういう場合に困らないように、データ更新前の状態に切り戻すためのリカバリー手段を常備しておくことが大事だ。

ところが、前項で紹介したようなデータ更新前の行データをCSV等のリストデータでバックアップしただけだと、リカバリー時にはCSVをパースして再度SQLを生成するような「切り戻し用の処理」が必要になる場合もあり、切り戻すのに時間がかかってしまう。まぁ、テーブル全体のデータを出力しておけば、MySQLならLOAD DATA LOCAL INFILE、PostgreSQLならCOPY FROMを使うことでCSVからでも一括でデータを切り戻すことも可能なのだが、前項の例で云えば、1000件中121件発行すれば済むクエリを1000件分すべて発行しなければならず、コストパフォーマンスが悪い。1000件程度ならば大したことがないが、これが実サービスの商用DBとかだとデータ数が数千〜数万とかの場合もざらにあるので、できる限りクエリコストは抑えたいところだ。

さらに言えば、一括データ更新から切り戻し更新を行うまでの間に更新されたデータ等があった場合、それらのデータもすべからく一括データ更新前の状態に戻ってしまうので、切り戻し後にデータ不整合が発生するリスクもある。

そこで、更新が必要な最小単位のデータのみで即時切り戻しができるように、リカバリーファイルはSQLクエリの形式で出力しておくのがベストである。そのために、データ更新前の外部ファイル出力時にひと工夫しておくのだ。

例えば、前章で作成した post_master テーブルのデータについて、下記のような依頼が発生した場合を想定してみる。

limit_date（論理名「有効期限」）の日付が現在日より前である（＝有効期限切れ）
status（論理名「状態」）が有効（=1）である
上記 1. と 2. にマッチするデータの post_type （論理名「投稿種別」）を knowledge に変更する

該当する行データを調べてみると、

> SELECT COUNT(*) FROM post_master WHERE limit_date < CURRENT_DATE AND status = 1 AND post_type != 'knowledge';
count
-------
396
(1 row)

更新対象のデータ数は396件だ。
前章のように愚直に全行データをバックアップするよりも、対象となる行データだけを更新する方がクエリを実行するコストとしては、396/1000分、約60%もお得である。
さらに、1件あたりの行データにおいては、更新が必要なのは post_type カラムだけなので、その1カラムのみをリストアできるようにしておけば、例え切り戻しまでに他のカラムに更新が入ったとしても、全カラムの値が切り戻されないので、切り戻しによるデータ不整合リスクはかなり軽減されることになる。

それでは早速、更新対象を絞り込んだリカバリークエリファイルを作成してみよう。

MySQLでは、

SELECT CONCAT( 
  'UPDATE post_master SET post_type = \'', post_type, '\' WHERE post_id = ', post_id, ';' 
) FROM post_master 
WHERE limit_date < CURRENT_DATE AND status = 1 AND post_type != 'knowledge' 
INTO OUTFILE 'restore_my.sql' LINES TERMINATED BY '\n';

PostgreSQLでは、

COPY (
  SELECT 
    'UPDATE post_master SET post_type = ''' || post_type || ''' WHERE post_id = ' || post_id || ';' 
  FROM post_mster 
  WHERE limit_date < CURRENT_DATE AND status = 1 AND post_type != 'knowledge' 
) TO 'restore_pg.sql';

──を実行すると、更新対象である post_id を持つ post_type カラムの値に現在の値を設定するUPDATE文をSELECT結果として取得し、その一覧を外部ファイルとして出力してくれる。
出力されたリカバリーファイルの中身は下記のようなSQLクエリリストだ。

$ cat restore_my.sql | wc -l
396
$ cat restore_pg.sql | wc -l
396
$ diff restore_my.sql restore_pg.sql
$ head -5 restore_pg.sql
UPDATE post_master SET post_type = 'article' WHERE post_id = 1;
UPDATE post_master SET post_type = 'article' WHERE post_id = 2;
UPDATE post_master SET post_type = 'webpage' WHERE post_id = 6;
UPDATE post_master SET post_type = 'other' WHERE post_id = 8;
UPDATE post_master SET post_type = 'other' WHERE post_id = 9;

もしこの後、切り戻しが必要になった場合、このリカバリー用のSQLクエリリストを実行するだけで、最小コストと低リスクで元データに復旧できるという次第だ。

では、お題である依頼の対応を行ってみる。

> UPDATE post_master SET post_type = 'knowledge' WHERE limit_date < CURRENT_DATE AND status = 1 AND post_type != 'knowledge';
UPDATE 396
> SELECT COUNT(*) FROM post_master WHERE limit_date < CURRENT_DATE AND status = 1 AND post_type != 'knowledge';
count
-------
0
(1 row)

これで依頼の対応は完了である。「対応完了しました〜」と依頼者に報告しても構わないだろう。
なお、上記の実行例はPostgreSQLのものだが、MySQLでも同じクエリで実行可能だ。

次に、データの切り戻しも行ってみる。
DBコンソールを抜けて、コマンドラインに戻り、リカバリーファイルがあるディレクトリに移動した想定で進める。

MySQLでは、

$ mysql -u DBユーザ名 -p DB名 < restore_my.sql

PostgreSQLでは、

$ psql -U DBユーザ名 -f restore_pg.sql DB名

これで切り戻し完了だ。
データを確認してみよう。

> SELECT COUNT(*) FROM post_master WHERE limit_date < CURRENT_DATE AND status = 1 AND post_type != 'knowledge';
count
-------
396
(1 row)

元に戻っている。

まとめ・注意点

このデータ運用手法は私が実際のサービス保守作業で使っているので、けっこう実践的だと思う。
しかし、注意しないといけない点もいくつかある。

リカバリーファイル用のUPDATE文をSELECTクエリ内で成形する際、適切なエスケープを行う必要がある。 SQL上ではプレースホルダ型の値埋め込みや禁則文字のエスケープができないため、それぞれのDBMSに準じたエスケープ表記を自前で施してあげる必要がある。これを失敗すると、ぶっ壊れたUPDATE文が生成されて、最悪、リカバリー時にテーブルのデータが壊滅的な状況に陥る危険性を孕んでいる。例えば改行が含まれるようなフリーテキストのデータを更新対象にする場合などは、改行コードやクォート文字をSQL上で自前でエスケープするのには限界がある。そういうデータを取り扱う際にはこの方法はお勧めしない。シェル化したり、スクリプト言語で個別処理を組んだ方が良いだろう。
更新頻度の高いカラムの値を更新するのには向かない。 データ更新後に切り戻しが発生した場合、リカバリー時までに切り戻し対象のカラムに別の更新が発生してしまっていると、リカバリーでその更新が失われてしまう可能性がある。そもそもそういうカラムの値を更新するのであれば、サービスをメンテナンス状態にしたり、一時的にテーブルをロックするなりして、データ更新後の確認が完了するまでは他からの更新を抑止しておく等の措置が必要になってくる。
リカバリー用のUPDATE文が複雑になる場合はこの方法は避けた方が良い。 リカバリー用のUPDATE文の条件が煩雑だったり、サブクエリを介したり、複数のUPDATE文が必要だったりというようなケースでもこの運用方法は対応できるが、リカバリー時のリスクが大きくなることを認識しておく必要がある。そういうケースは対象テーブルの全データをCSV等に出力しておき、リカバリーも一括で全データを切り戻すという運用の方が無難だったりする（リカバリー時の発行クエリのコストにもよるが……）。

上記のような注意点を踏まえ、最低限テスト環境等でリカバリーのテストをした上で使えば、たった2つのSQLだけで「心穏やかに」データ更新が行えるはずだ。

まぁ、極力、商用データベース上でこういう胃の痛くなるような作業が発生しないことを願ってやまない──というのが本音であるw