やりたいこと
IDのリストを外部からもらい、自分達のサービスのDBにデータパッチ(データの更新 or 削除など)を行いたい。
このとき、IDのリストはかなり大きいもの (>100)とする。
このとき、SQLのレビューや再利用性の観点から IN句を使うのは避けたい。
避けたい例 id in (id1, id2, id3, ... , id1000)
どういう状況でそういうことが起こるか
IDのリストを自サービスで抽出できる場合、ファイルから渡さなくてもサブクエリ等で対処可能な場合が多い。 しかし、以下のようなケースではこのようなワークアラウンドが有効。 - SELECTクエリが複雑すぎて負荷が心配 - 今回のやり方なら抽出用のReader instanceからIDのリストを持ってきて、Writer instanceで更新ということができる - マイクロサービス化していてDBが分かれており、IDのリストの検索が外部サービスによって行われている - 対象データをマニュアルでレビューして確認が取れたものだけに対して更新を実行したい
今回用いるシンプルな例
外部からもらったファイルid_list
1 3 5
自サービスのtableの状況
person=# select * from person; id | name | deleted ----+------+--------- 1 | a | f 2 | b | f 3 | c | f 4 | d | f 5 | e | f (5 rows)
できたもの
id_list に含まれているIDを持つテーブル上のレコードを論理削除する。
対象テーブル: person
変更カラム: person.deleted (false -> true)
実行
$ cat id_list
1
3
5
$ export PGPASSWORD=${pass} && sh run.sh
1
UPDATE 1
3
UPDATE 1
5
UPDATE 1
結果
$ psql -h localhost -p 5432 -U root person psql (14.6 (Homebrew), server 12.13 (Debian 12.13-1.pgdg110+1)) Type "help" for help. person=# select * from person; id | name | deleted ----+------+--------- 2 | b | f 4 | d | f 1 | a | t 3 | c | t 5 | e | t (5 rows)
対象レコード(id:1,3,5)だけが deleted: t (true) になっていることを確認。
つかうもの
server side - postgres:12.13
client side - shell script - SQL file - psql command
スクリプト/SQL
コード: https://github.com/yoshikipom/sandbox/tree/main/postgres/task/update-target
3ファイルを同じフォルダに置く。
$ ls id_list run.sh update.sql
id_list
1 3 5
今回はheaderなしの改行区切りのファイルとする。フォーマット変更や複数変数に対応する場合、 run.sh でハンドリング。
update.sql
\echo :target_id UPDATE person SET deleted = true WHERE id = :target_id;
:{変数名}で変数を展開できる。SQL上で文字列に展開したい場合は:'{変数名}'(例:'target_id') とする。- 今回はIDがintegerなので
:target_id。 - ログに削除したIDを表示するために
\echoで変数の値を表示。
run.sh
#!/bin/bash
while read id
do
if [ -n "${id}" ]; then
psql -h localhost -p 5432 -U root person -v target_id=${id} -f update.sql
fi
done < id_list
-v key=valueでSQLに変数の値を渡せる。< id_listとread idで${id}にファイルの各行にあるIDを代入。- 通常はpsqlでパスワードを求められるため、今回は環境変数を通して先に渡した。
$ export PGPASSWORD=${pass} && sh run.sh
- if文は
id_listに空行があった場合にスキップするため。
感想
エンジニア的には1つのSQLでなんとかしようとしがちだが、読みやすさ (+ レビューしやすさ)、拡張性、再利用性、パフォーマンスの予想しやすさなどを考えるとシェルスクリプトを通してデータパッチするのもありだと学んだ。
