Athenaのデータ比較ツールを作って工数削減と品質アップに取り組んでみた

データウェアハウス開発部の高野です。今回は、以前の当社ブログでもご紹介したことのある医療機関データベースの品質アップのためAthenaのテーブル新旧比較を仕組み化した取り組みと、結果、修正による想定外の影響を把握し易くなり、安定したリリースに繋がっていることを、システム構成も交えてご紹介したいと思います。

techblog.jmdc.co.jp techblog.jmdc.co.jp

オンプレからクラウドへの移行でテストの中心となったレコード新旧比較

運用管理画面や実行されるジョブについてはRuby on Rails、AWS Lambdaで開発しており、ある程度テストコードも書いてテストも自動化できていたのですが、ジョブから実行されるAthenaのクエリは、クレンジングも含め約1800近いクエリ数で、それぞれに対して、テストコード、テストデータを作るなどは現実的には難しい数でした。結果、テーブルを新旧比較するのが良いという判断になりました。

幸いだったのが、サービス提供側が先にクラウド移行していたので、納品データベースも旧オンプレシステムからクラウドに転送され、Athena(Glueデータカタログ)のデータベース化されていました。それと新システムで作ったデータベースの比較を繰り返し、無事稼働を迎えることができました。テストは数か月を要し、仕組み化をしないと効率が悪かったので、ベースとなる仕組みはこの時に作られました。

新旧比較イメージ

比較結果テーブル
各テーブルの以下の2つのクエリをUNION ALLしたレコードを入れたテーブルです。
- 新テーブル　－　旧テーブル　の差分レコード
  （差のある新テーブルのレコードだけ残る）
```
  SELECT 'new’ as src,カラム1,カラム2,カラム3,・・・ FROM 新テーブル
  EXCEPT
  SELECT 'new’ as src,カラム1,カラム2,カラム3,・・・ FROM 旧テーブル
```
- 旧テーブル　－　新テーブル　の差分レコード
  （差のある旧テーブルのレコードだけ残る）
```
  SELECT 'old’ as src,カラム1,カラム2,カラム3,・・・ FROM 旧テーブル
  EXCEPT
  SELECT 'old’ as src,カラム1,カラム2,カラム3,・・・ FROM 新テーブル
```
比較結果データベースにこれらをテーブル単位で作るスクリプトを作成しました。
下の表が比較結果テーブルイメージとなりますが、
SELECT～ORDER BY PKカラム,src
とすることにより、同一キーのレコードが、新、旧と順にソートされるので、新旧レコードを対にして確認が出来ます。

src カラム1 カラム2 カラム3 ・・・

new ID001 fuga 1 ・・・

old ID001 fugafuga 1 ・・・

登録日など、処理タイミングで値が変わってしまうカラムは比較から除外指定（NULLとしてSELECT）したり、システム移行時の要望等仕様変更が差として出ないように旧テーブルを予め加工しておいたり等、なるべく見るべき差分だけに絞られるように細かい対応もしました。
比較結果サマリ
差分がどのテーブルに多いか等の全体感を把握するため、各テーブル、パーティション（病院）ごとの、以下をまとめたCSVファイルも出力するようにしました。
- 旧テーブルのレコード件数
- 新テーブルのレコード件数
- 比較結果テーブルのレコード件数

src	カラム1	カラム2	カラム3	・・・
new	ID001	fuga	1	・・・
old	ID001	fugafuga	1	・・・

原始的と思われるかもしれませんが、上記が仕組み化されるだけでも問題点の洗い出しが捗るようになりました。

稼働後もリファクタリングや開発案件は継続して発生

大きな問題なく無事稼働は出来ましたが、リファクタリングなど細かい改善や、事業部門からの要望、診療報酬改定等で修正は継続して続いています。稼働当初、やはりネックになったのが、修正に対する影響をどう把握するかでした。
テストデータについても、取り込むファイルはcsv、tsvといったテキストで扱えるデータのため、テストに沿ったデータを作りやすい（テーブル化した以降はparquet形式）ということもあり、テスト用のデータファイルで修正前ソース、修正後ソースで処理を最後まで実施し、それぞれのデータベースのテーブルを比較してみることとしました。

新システムでテーブル新旧比較を行う上での改善点

システム移行時の特別対応を除く
登録日など、処理タイミングで値が変わってしまうカラムは比較から除外指定は残し、それ以外の差は逆に全て出るべきなので、システム移行に特化した対応は除きました。
新システムのデータベースの作り方に沿った比較
- データベースの作られ方
  - データベースは環境（Rails.env）ごとに作成
    ※local環境はRails.env＋ユーザごと
  - 別データベースに対して、更新したイメージのレコードをINSERT
    Athenaは基本的にEXTERNALテーブルなので、一部テーブルタイプを除いて、UPDATEは出来ません。UPDATEの代替えとタイムトラベル機能（各処理時点だけに限定されますが）的なものを実現しています。
    参考：Athenaでのテーブルの作成
     参考：Iceberg テーブルの使用(UPDATE可能なテーブル)
  - 1環境で計16個のデータベースを作成
    処理は大きく8個あり、それぞれにクレンジングも行うので、計16個のデータベースが作成されます。
    処理ごとにテーブルを引き継ぎますが、更新がないテーブルは、下図のように定義コピーして、S3ロケーションは同じ場所として、データそのものは、前の処理データベースのものを参照するようにしてます。処理2データベースにテーブルがコピーされたイメージになります。
    - テーブルA：処理１→処理２で更新（INSERT）
    - テーブルB：処理１→処理２で更新がないため定義をコピー
    - テーブルC：処理２で新たに作成したテーブル
      Athenaデータベース、テーブル作成イメージ
- 比較対象
  以下の2パターンとしました。
  - 異なる環境の16個のデータベース内のテーブルを比較
    （全体への影響確認想定）
    図のテーブルBについては、処理1データベースで比較されるので、コピー先の処理2データベースでの比較は無意味なため除きます。
  - 異なる環境の特定のデータベース内のテーブルを比較
    （修正した処理の影響確認などポイントを絞った確認想定）
    処理2データベースだけといった特定のデータベースの比較。この場合は、コピーされたテーブルも比較するようにします。
- local環境で比較用のRails.envを追加
  local環境は、ECSの代わりにローカルのアプリケーションコンテナ、RDSの代わりにローカルのMySQLのコンテナを使うイメージです。各メンバーの開発はlocal環境で行うため、2つ比較用のRails.envを追加して各個人で比較が出来るようにしました。
  片方の環境では修正前のソースで処理、もう片方では、修正後のソースで処理をして比較するイメージです。
  医療機関データ基盤構成

得られた効果

比較結果データベースがテストのエビデンスに
- リファクタリングなど結果が変わらない前提の修正では差分が0件である結果がとれれば、そのままエビデンスとして活用できるようになりました。
- 診療報酬改定時など差が出る修正についても、差の妥当性を見て問題ないことが確認できれば同様にエビデンスとなります。
- 方法はスクリプトを実行するコマンドだけなので、単純化できました。
想定外の影響把握が可能に
想定外の影響も比較結果に出てくるので、各個人の経験値に関係なく、影響があることを早めに把握することが可能となり、安心してリリースが可能となりました。