既存テーブルを手作業更新

序

昨今データは何でもデータベースに入ることが多くなり、SQLの需要はなくなるどころか高まりつつある気がします。ORMなどで直に扱うことのないプロジェクトでも、何か問題があれば知らずに済むことはないでしょう。

そこで今回は基本の基本、既存のテーブルに何かトラブルがあり、

手作業でデータを追加したり変更したりする作業

が必要なときの基本となるやり方を説明します。あまりにも基本すぎて説明する人がいなさそうだけど、割と泥臭いし、ここでやらかされると多分とっても困るので…

前提

• 対象テーブルは1つ
• 対象データベースはPostgreSQLとする
• 手作業更新をする時間はシステムから対象テーブルにアクセスはないものとする

※PostgreSQLでなくても似たようなもの。SQLを記述するに当たり想定が必要なだけ。

要件

対象は以下のテーブルとする。

initialize.sql

-- create
CREATE TABLE products (
    product_code VARCHAR(50) PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    category_id INTEGER NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
-- insert
begin;
INSERT INTO products VALUES ('0000-0000-0000-0001', '商品１', 100, 1, '商品1');
INSERT INTO products VALUES ('0000-0000-0000-0002', '商品２', 150, 1, '商品2');
INSERT INTO products VALUES ('0000-0000-0000-0003', '商品３', 200, 1, '商品3');
commit;
-- fetch 
SELECT * FROM products;

変更は以下のとおり。

• 商品１(0000-0000-0000-0001)のpriceを150とする
• 商品２(0000-0000-0000-0002)のpriceを200とする
• 商品４(0000-0000-0000-0004)、price=100、category=1、description='商品4'を追加する

方法

手作業更新をする時間以前に準備をし、時間が来たら所定の作業を行うだけにしておきます。こうすることで、リスクを減らすことが出来ます。手作業トランザクションの時間は短いに越したことはありません。

準備

CSVのインポート

今回はほんの数行程度の変更ですが、結構大きいことも多く、CSVファイルなどが入力となります(Excelもよくあります)。今回は以下のようなCSVとします。

requirements.csv

update,0000-0000-0000-0001,,150,,
update,0000-0000-0000-0002,,200,,
insert,0000-0000-0000-0004,商品４,100,1,商品4

上記CSVをロードして、更新前に必要なデータを作るSQLスクリプトが以下になります。

prepare.sql

create table requirements(
    operation VARCHAR(20),
    product_code VARCHAR(50),
    product_name VARCHAR(255),
    price NUMERIC(10,2),
    category_id INTEGER,
    description TEXT
);

\copy requirements from 'requirements.csv' with csv;

create table products_insert as
    select product_code, product_name, price, category_id, description
        from requirements
        where operation = 'insert';

create table products_update as
    select product_code, price
        from requirements
        where operation = 'update';

create table products_backup as
    select *
        from products
        where false;

create table products_new as
    select *
        from products
        where false;

実施

所定の時間になったら始める作業です。一応SQLスクリプトにしていますが、この作業は目視確認が入るので、手作業ですることも多いです。ストアードプログラム(簡単ですが「基本」としているので今回は使いません)を使える人は目視確認部分もコーディングできるので、スクリプトにして実施出来ます。

※原則この手の作業はセキュリティの厳しい場所にあったり、任意のツールが使用できない場合も多く、データベース付属のコマンドでの実施を前提にしています。

exec.sql

begin;

insert into products_backup
    select A.*
    from products A
    inner join products_update B
        on A.product_code = B.product_code;

insert into products
    select *
    from products_insert;

update products A
    set price = B.price,
        updated_at = now()
    from products_update B
    where A.product_code = B.product_code;

insert into products_new
    select A.*
    from products A
    inner join products_insert B
        on A.product_code = B.product_code;
insert into products_new
    select A.*
    from products A
    inner join products_update B
        on A.product_code = B.product_code;

-- 正しくバックアップされていることを確認する
select count(*)=2 from products_backup;

-- 正しくproducts_newが構築されていることを確認する
select count(*)=3 from products_new;

-- 正しくインサートされていることを確認する
select count(*) = 1
    from products A
    inner join products_insert B
        on A.product_code = B.product_code
    where A.product_name = B.product_name
        and A.price = B.price
        and A.category_id = B.category_id
        and A.description = B.description
        and A.created_at >= TIMESTAMP'2024-09-20 00:00:00'  -- 作業時間以降であること
        and A.updated_at >= TIMESTAMP'2024-09-20 00:00:00'; -- 作業時間以降であること

-- 正しくアップデートされていることを確認する
select count(*) = 2
    from products_update A
    inner join products B
        on A.product_code = B.product_code
    inner join products_backup C
        on B.product_code = C.product_code
    where B.product_name = C.product_name
        and B.price = A.price
        and B.category_id = C.category_id
        and B.description = C.description
        and B.created_at = C.created_at
        and B.updated_at >= TIMESTAMP'2024-09-20 00:00:00'; -- 作業時間以降であること

select count(*) = 2
    from products_update A
    inner join products B
        on A.product_code = B.product_code
    full outer join products_backup C
        on B.product_code = C.product_code;

-- ざっくり他に影響がないか確認する
select count(*) = 3
    from products
    where updated_at >= TIMESTAMP'2024-09-20 00:00:00'; -- 作業時間以降であること

commit; -- テストでOKしたらcommitする

-- 追加・削除したデータをcsvに保存する
\copy products_backup to 'products_backup.csv' with csv;
\copy products_new to 'products_new.csv' with csv;

変更前・変更後の差分データをCSVで保存していますが、これは重要です。何か問題があった場合に、調べたり戻したりする場合に使用するエビデンスとなります。システムは稼働しており、時間が経てば色々書き換わってしまい、あとからこれらの情報を得ることはできないからです。

一部のデータベースではコミット後でも過去の一時点のテーブルに対してクエリを発行したりできます(ログが残っている場合)。なので、作業後しばらくの間に起きた問題については比較的容易に対処できますが、それでも確実なわけではないので、通常この程度のエビデンスは残します。

なお、今回はクライアント側でCSVファイルを読み書きする想定にしていますが、\を付けないCOPYをサーバー側で行うことも多いです(速いので)。

後始末

作業後数日～数ヶ月の間はトラブルが起こる可能性があるので、作成したテーブルは原則残しておきます。不要になったら、適切なタイミングで消しましょう。

drop.sql

drop table requirements cascade;
drop table products_insert cascade;
drop table products_update cascade;
drop table products_backup cascade;
drop table products_new cascade;

大量の更新がある場合は、統計情報が不適切になったりインデックスが偏ったりすることがあるので、統計情報の更新や最適化を実施した方がいいこともあります。PostgreSQLならvacuumなどもやりたいタイミングでしょう。今回は基本なので割愛しています(それらは運用作業の中でそのうち行われるので)。

まとめ

• 既存テーブルを手作業更新という基本作業を解説した