データ現新比較テストを用いた Lift & Shift のすゝめ

datatech-jp Casual Talks #4
2022/12/12 登壇資料となります。

Transcript

1. データ現新比較テストを用いた Lift のすゝめ datatech-jp Casual Talks #4
2. 自己紹介 Twitter： a1008u(@eeemptyyy) 所属：データ基盤@株式会社インタースペース (interspace.ne.jp) 職種：データエンジニア（6名）　
3. 登壇背景と思い 素晴らしいデータ基盤の話はたくさんあるのですが、 今のデータ基盤を移行する事例はそこまで多くなく、 データ基盤を移行する際に 手探りだった。 背景 思い 数年前に作ったデータ基盤がまだ稼働していて、 移行やリプレースを考えてるが、 進め方がわからない人に向けて 何かお伝えできれば 。。
4. システムの移行って大変ですよね？？ クラウド オンプレ クラウド ネイティブ LIFT SHIFT ビジネス要件やシステムの制約と作り手の願望を考慮して、 今できる最善策を選ばないといけないので。。。
5. 今回の移行(Lift)について 自作BI （オンプレ） 現行 移行後 自作のBIツール（Webアプリケーション）を BIツールに変更する必要があった。
6. 当時のシステム状況 target LIFT SHIFT DB WEB(BI) 自作BI(オンプレ) LIFT SHIFT
7. Liftにおける要件 自作BI （オンプレ） 現行 移行後 一致してるのか の確認が必要 プロダクト要件：自作 BIとTableauのBIの出力データは基本一致させてほしい
8. 現状把握 自作BI （オンプレ） table table table back (API) Front (view) 詳細 問題点：API側でtable結合と動的にfilterをしている。 　　　　ロジックの全てが API側にある。 サービス規模：10機能（社内サービス） エンジニア：2人（対応できる人数） 期間：2022/01 - 2022/09末
9. ？？ あれ？？ データ整備も必要？？
10. 今回のゴール？？ LIFT SHIFT DB WEB(BI)
11. データ現新比較テストを用いた Lift & Shift のすゝめ datatech-jp Casual Talks #4 改め
12. Shiftの取り組み データの整備方針 データ整備の手法 lake dwh mart レイヤー分ける 従来は、lakeに対してJavaでSQLを生成してクエリを実行してたが、 データを整備し全体の見通しをよくすることにしました。 dbtの導入 データモデリングを簡単にする + git管理で開発しやすい体制を整える。 + データモデリングの心理的ハードルを下げる。
13. データ整備を盛り込んだデータ基盤 table table table back (API) 現行 • 事前に整備したテーブルを用意して、数値計算などをしておく。 • 動的クエリはテーブル関数を利用して対応する。 table table table table (dwh) dimention fact table (mart) table (mart) table function (mart) table function (mart) 移行 Front (view)
14. 現新データ比較テストについて back (API) 現行 前提：BIツール側にロジックを持たせない（ viewとmodelの分離） 　＊BIのビジュアル側は、ビジネス側で確認 table (mart) table (mart) table function (mart) table function (mart) 移行 Front (view) 手動 全件比較
15. 全件比較テストについて 問題点①：データ比較のためにデータを用意するのが難しい。 問題点②：手動での比較はつらい。。自動でやれる方法が必要 <データA> APIで動的クエリを利用して取得 したデータの比較 <データB> APIで動的クエリを利用して取得 したデータを、さらに API側で追 加処理したデータ の比較 back (API) 現行 Front (view)
16. 問題点①の対策 -全件比較テストについて- 意図：BigQuery上でデータ比較をしたかったため。 <データA> APIで動的クエリを利用して取得 したデータの比較 Datalakeにテーブルは存在してるため、 API側のクエリをログから取得し、 そのクエリを利用することで、 同じデータを取得可能 back (API) Front (view) ログからSQL取得 *ログありがとう
17. 問題点①の対策 -全件比較テストについて- 意図：BigQuery上でデータ比較をしたかったため。 <データB> APIで動的クエリを利用して取得 したデータを、さらに API側で追 加処理したデータ の比較 APIを修正して全ケースのデータを取得しました。 取得データはjsonlで取得してBigQueryに格納しました。 back (API) Front (view) jsonl APIを修正し全パターンの jsonlを 取得しBQへ格納する
18. 問題点②の対策 -全件比較テストについて- table (mart) table function <データA> APIで動的クエリを利 用して取得したデータ の比較 <データB> APIで動的クエリを利用し て取得したデータを、さら にAPI側で追加処理した データ の比較 データ比較 移行後 現行 全件比較のため • 比較の実行はBigQueryで行います。 • データ比較はSQL(dbtのマクロ)を利用します。 • 最終結果はGitHub issueでまとめる。 データ比較
19. 問題点②の対策（dbtのマクロ） -全件比較テストについて- {% macro get_query() %} {% set args = [渡したい引数] %} {% for arg in args %} {% set query %} WITH a AS ( 比較クエリ ), b AS ( 比較クエリ ), a_intersect_b AS (SELECT * FROM a INTERSECT DISTINCT SELECT * FROM b ), a_except_b AS (SELECT * FROM a EXCEPT DISTINCT SELECT * FROM b ), b_except_a AS (SELECT * FROM b EXCEPT DISTINCT SELECT * FROM a ), {# all_records AS ( SELECT *, TRUE AS in_a, TRUE AS in_b FROM a_intersect_b UNION ALL SELECT *, TRUE AS in_a, FALSE AS in_b FROM a_except_b UNION ALL SELECT *, FALSE AS in_a, TRUE AS in_b FROM b_except_a ), summary_stats AS ( SELECT in_a, in_b, COUNT(*) AS count FROM all_records GROUP BY 1,2 ) SELECT *, ROUND(100.0 * count / SUM(count) OVER (), 2) AS percent_of_total FROM summary_stats ORDER BY in_a DESC, in_b DESC #} all_records AS ( SELECT "a_intersect_b" as name, *, TRUE AS in_a, TRUE AS in_b FROM a_intersect_b UNION ALL SELECT "a_except_b" as name, *, TRUE AS in_a, FALSE AS in_b FROM a_except_b UNION ALL SELECT "b_except_a" as name, *, FALSE AS in_a, TRUE AS in_b FROM b_except_a ) select * from all_records {% endset %} {% do querys.append(query) %} {% endfor %} {{ return(querys) }} {% endmacro %} {% macro test_sample() %} {%- set ns = namespace() -%} {%- set ns.all = 0 -%} {%- set ns.error = 0 -%} {%- set ns.success = 0 -%} {%- set ns.empty = 0 -%} {%- set ns.a_except_b = 0 -%} {%- set ns.b_except_a = 0 -%} {%- set ns.no_problem = 0 -%} {%- set ns.problem = 0 -%} {%- set ns.dict_a_except_b = {} -%} {%- set ns.dict_b_except_a = {} -%} {{ log('テスト開始', true) }} {% for query in get_query() %} {%- set ns.all = ns.all + 1 -%} {% set results = run_query(query) %} {% if not results.rows[0] %} {# {{ log("戻り値なし---------------------", true) }} #} {%- set ns.empty = ns.empty + 1 -%} {% else %} {% for row in results.rows %} {% if row[0] == "a_except_b" %} {%- set ns.a_except_b = ns.a_except_b + 1 -%} {%- set id = row[1] -%} {%- do ns.dict_a_except_b.update({id:[row[2], row[3], row[4], row[5],row[6]]}) -%} {% elif row[0] == "b_except_a" %} {%- set ns.b_except_a = ns.b_except_a + 1 -%} {%- set id = row[1] -%} {%- do ns.dict_b_except_a.update({id:[row[2], row[3], row[4], row[5],row[6]]}) -%} {% else %} {%- set ns.success = ns.success + 1 -%} {% endif %} {% endfor %} {% endif %} {% endfor %} {% if ns.a_except_b == ns.b_except_a %} {% for k,v in ns.dict_a_except_b.items() %} {% if ns.dict_b_except_a[k][0] - v[0] <= 0.1 and ns.dict_b_except_a[k][1] - v[1] <= 0.1 and ns.dict_b_except_a[k][2] - v[2] <= 0.1 and ns.dict_b_except_a[k][3] - v[3] <= 0.1 and ns.dict_b_except_a[k][4] - v[4] <= 0.1 %} {%- set ns.no_problem = ns.no_problem + 1 -%} {% else %} {%- set ns.problem = ns.problem + 1 -%} {% endif %} {% endfor %} {% else %} {%- set ns.problem = ns.problem + 1 -%} {{ log('a_except_b と ns.b_except_a の不一致数が同じではないので、クエリの見直しをしてください。', true) }} {% endif %} {% set message %} テスト終了(test_{{ arg_target_month }}_3_atinsight_tvf_grows_sales_merchant_site) a_intersect_b[{{ ns.success }}] : a_except_b[{{ ns.a_except_b }}] : b_except_a[{{ ns.b_except_a }}] : empty[{{ ns.empty }}] 詳細 ::: 問題なし(カラムのいずれかに四捨五入誤差あり**それ以外の値は全て一致**)[{{ ns.no_problem }}] 問題あり(調査しましょう)[{{ ns.problem }}] {% endset %} {{ log(message, true) }} {% endmacro %} 実行は下記コマンドをshellで並列実行 ＊Liftのみで利用のため、実行環境は作り込みしない dbt run-operation test_sample & dbt run-operation test_sample1 & dbt run-operation test_sample2 & dbt-audit-helper を参考にクエリを作成 ＊取得データが用途によって異 なるため、dbt-audit-helperをそ のまま利用することができなかっ たです。 ＊マクロでテストができるので、 テストをすることが非常に楽にな りました。
20. 問題点②の対策（dbtのマクロ） -全件比較テストについて- GitHub issue 後日見る可能性があったので、 このような形で取りまとめました。 ＊実際は赤枠の部分だけで、 　取りまとめております。 ＊こちらは、後で見てもわかるように、 　テスト結果の読み方の説明資料です。
21. 現時点の構成 LIFT SHIFT DB WEB(BI) SHIFT LIFT
22. まとめ ポイント① マシーンパワーは大切 テーブル比較だけでなく、SQLのフィルタパターンで全ケーステストする必要があるので、 Database側にマシーンパワーはあった方が絶対に楽になります（選択肢が増える） ポイント② Data Modelling and Transformation 基本的にSQLでの操作となりますが、リネージュなど細かなツールが利用できることで、 作業を細かくしたり、手直しが簡単になるので、変更前提に作業ができる ポイント③ Lift&Shiftは細かく分けて DB側はShiftで、BI側はLiftをしましたが、 DBとBIを同時にLift&Shiftしていたら、今よりも時間がかかった。。 ポイント④ Lift&Shiftはテスト設計を最初に決めておくことが重要 要件にもよりますが、LiftやShiftの前後で結果を比較をするなら、 データテストは自動化しやすく、費用対効果も高いのでテスト設計はしっかりしましょう。 ポイント⑤ 整合性が取れているレイヤーを明確にする テスト失敗時に原因調査をする際にどこまでが正しいのかわからないと、 原因調査に膨大な時間をかけてしまうので、ここまでは確実に正しい部分を明確にする。
23. ご清聴ありがとうございました。 エンジニア募集してるので、気になった方はDMください。 □採用ページ https://en-gage.net/interspace_saiyo/work_3592292/?via_recruit_page=1 https://en-gage.net/interspace_saiyo/work_870292/?via_recruit_page=1 今回の発表が少しでも移行（ Lift）をする予定の組織やチームに対して、 何かしらの参考になれば幸いです。