ETL
ぺい(pei0804)Incremental Loadしよう
メリット
全量を処理しないことによるメリットがほとんど。
- 実行時間が短縮される
- 失敗時の手戻りが小さい
- チェックするデータが少ない
- 時間経過によるパフォーマンスダウンが起きない（もちろん増えることはあるだろうけど
- ソースシステムのデータを消しても、取り込み済みのデータは消えずに残る
デメリット
- 全量ロード（ダムロード）に比べてロジックが複雑
- 変更の検出が容易または手段がなければインクリメントにデータはロードできない
インクリメンタルロードを採用するべきかどうかの判断基準
- データソースサイズが比較的大きい
- 変更を検出する方法がある
- 技術的な理由でソースへのクエリが時間かかる
- ソースから削除されても削除されたデータを保存したい
フルロードが便利な場面
- データサイズが小さい
- ソースのどのデータが新しいか検出する方法がない（簡単ではないなど
- 過去のデータは数ヶ月、数年前のものでも変更可能
- デスティネーションがソースと全く同じであってほしい
新しいデータと変更されたデータだけを読み込む場合、その処理方法は全く異なる。
読み込み元を検出する(Source Change Detection)
前回のロード処理を実行した時から、新規・変更データのみを取り出すパターン。
この方法は実際にロードする必要のないデータはロードから除外することで、ETLパイプラインに流れるデータ量を制限する。
読み込み先を比較する(Destination Change Comparison)
行レベルのcreated at, updated atを使用する。これが最もシンプルなソース城での変更検知を行うシンプルな方法。この方法は自分たちでコントロールできないサードパーティ製のデータでは適用が難しい。
変更検知方法がサポートできない場合
ソースがソース変更検知をサポートしていない場合、ソースとデスティネーションの比較を採用することになる。行ごとに分析を行うしかないので、変更検知がサポートされていたケースよりパフォーマンスは落ちます。また、この方法全てのデータを取り込みの対象とする必要がある。（または、変更監視したい範囲全て）
この方法はパフォーマンスが悪い一方で、どんなデータでも変更検知ができる。
行ハッシュ
比較に使用したい列を計算したバイナリを列に持つ。バイナリ値は指定した列から計算した集約値。この方法が良いのは、普通に比較していくよりも効率が良いとこです。（ハッシュ値の計算コストはかかるけど）
ブルートフォース比較
ソースシステムの変更があまり行われない場合は、行ごと列ごとに愚直に比較するのが確実。
ぺい(pei0804)ETLロギング
ロギングは大事らしい。
ETLロギングとは、ETLプロセスの関連イベントのアクティビティログ。このロギングはETLソフトウェア自体で行われる。または、統合された何らかのジョブスケジューリングツールのロギングなども含む場合もある。
負荷レベル、プロセスにかかった時間、ロードされたログ数などなど、
万能なアプローチは存在しない。それぞれのケースによってやりたいことは変わる。
all-or-nothing approach（絶対にうまくいかないとだめ）ではなく、いい感じの塩梅を模索したほうがいい。
最小限のアプローチは、プロセス全体の開始時刻と終了時刻だけを記録するだけでもよい。このようなアプローチは、起こりうるエラーが限定的で、再度実行すればいいだけな場合に有効である。
変なことが起きにくいなら、ロギングはシンプルで良い。
逆に全てのタスクをステップ・バイ・ステップにログがほしい場合もある。例えば10個くらいのステップがあるなら、各タスクの開始と停止や例外、監査ログなどがほしくなる。
開始および停止イベント。ETLプロセス全体、およびここのステップの開始と終了。
エラーやその他の例外、障害や異常のログは実装が大変。しかし、テストやトラブルシューティングの際に最も大きな価値を生む部分でもある。
ロギングインフラを構築して、期待通り動いてたとする。誰にその情報を届けるか。
ETLプロセスが完全にロギングされていても、誰もその情報にアクセスできないなら、それは単なるデータになる。
データ好きな人は勝手にSQLを書いてアクセスしてくれるのでいいけど、非エンジニアにも見れるようにしたいなら、提供方法は考える必要がある。
ログ情報がどのように消費されるかを考えるとき、次のことを考慮に入れてください。
ログの主な対象者は誰か？
このログの主な対象者は誰か、この情報へのアクセスを必要とする他の対象者は誰か？
データはアドホックにレビューされるのか、それともプッシュ配信やダッシュボードを用いて正式にレビューされる必要があるのか？
どのようなフォーマットで情報を提供すれば、最も分かりやすいか？
ユーザーレベルでのデータフィルタリングを必要とするようなセキュリティ上の懸念はないか？
ロギングにやりすぎによるコストとのバランスは大事。
ぺい(pei0804)Managing Bad Data in ETL
バットデータとは何か
不良データの定義は幅広い。一般的には、出どころや正確性が疑わしいもの。
何をもて悪いデータとするかは、人によって異なる。
ETLアーキテクチャを構築する際に、しばしば最初で最大の課題になるのが、何が不良データで何が不良データではないかを判断すること。最終的にはビジネスニーズがロジックを決定する。
バットデータの境界を定義する際の最後のポイントとしては、必ず2項対立的な決定ではない。しかし、時にはデータの品質をより詳細に評価する必要がある。このような品質管理は、必要なときにだけ作る。全部にやってると死ぬ。
粒度の定義
不良データの粒度を決める時に、行か全体かの2つのどちらかの方法を採用できる。
疑わしいデータを行単位で管理する。
疑わしいデータの処理は、通常は行単位で行われる。行ごとは圧倒的に一般的であって、取りやすい選択肢。
このアプローチでは、ETLプロセスは、前のフェーズで設定した基準に基づいて、疑わしい行をフィルタリングして、それぞれクリーンアップ、リダイレクトまたは削除する。
オール・オア・ナッシングのロードとしてデータを管理する
不良データに対してロードレベルのアプローチが必要なビジネスケースもある。ロードプロセス全体が一つのユニットとして成功するか失敗するかということ。
この設計では、数百万業のデータのうち疑わしい行が1つでもあったら、ロード全体を無効化する。これを採用する場合には、自動的にロールバックするまたは削除メカニズムが必要になる。
1つでもおかしくなったら、全てがおかしくなる場合には、全く書き込まれない方が良いなら、この方法を採用すると良い。
不良データをどうするかはビジネスニーズが決定するものであるので、第一の関心事をビジネス影響としましょう。
ETLでのクリーンアップ
ぺい(pei0804)Using ETL Staging Tables
従来のETLプロセスのほとんどは、抽出、変換、そして最終的に目的地へロードという、3つの明確かつ連続したプロセスでロードを実行します。しかし、大規模になってくると複雑になる。そこでETLステージングテーブルを使うことで、複雑さを軽減しつつパフォーマンス向上ができる。
抽出、変換、ロードの各プロセスは、以下のようなことをする。
- 抽出
- リレーショナルデータベース、フラットファイル、クラウドストレージなどのソースからデータを取得する
- 変換
- 変換先スキーマに適合させ、クレンジングやビジネスルールを適用するために、必要に応じてデータを再形成し、クレンジングする。
- ロード
- 変換されたデータを、通常はリレーショナルデータベースのテーブルである出力先に挿入する。
この典型的なワークフローは、各ETLプロセスがインラインで、通常はメモリ内で、データをディスティネーションに到着する前に変換処理することを想定しています。つまり、中間データ的なのがない。
シンプルであればｋろえでよい。そうならないケースもある。
例えば
- ロードする各行が同じデータセット内の他の1つ異常の行から何かを必要とする場合、（例えば、順序、グループ化、合計など）
- ETLプロセスはインクリメンタルロードであるが、データ量が多いため、変換ステップで行ごとの比較が出来ない。
- データ変換に複数のステップが必要で、ある変換ステップの出力が別の変換ステップ入力になる場合。（データが依存してる）
通常これらすべてを一つのプロセス内で変換することは可能ですが、パフォーマンスが低下したり、不必要に複雑になったりする可能性がある。
負荷を処理する最良の方法は、デスティネーションデータベースに一部の重たい荷物を任せることが多い。
ETLステージングテーブルの使用
変換処理の量や粒度の問題でETL処理のパフォーマンスが低下する場合、変換先データベース上のステージングテーブルを中間データ結果の処理手順として使用することを検討してください。
ステージングテーブルは通常、揮発性のテーブルとみなされ、実行ごとに結果を空にして再ロードすることを意味する。恒久的な保存には使用しない。
ステージングテーブルを使用したロードのフローは以下のようになる
- ステージングテーブルの既存データを削除する
- ソースからデータを抽出する
- ソースデータからステージングテーブルにデータをロードする
- SQLなどでデータをクリーンアップ、ビジネスルールの適用、必要に応じて変換処理をする
- ステージングテーブルから最終目的地テーブルへ変換済みデータをロードする
このロードデザインパターンは、従来のETLプロセスよりも多くのステップを必要とするが、同時に柔軟性ももたらす。
まずデータをステージングにロードすることで、データベースエンジンが得意としていることを任せることができる。例えば、検証やルックアップのための2つのデータセットの結合など。ETLツールでも可能だが、このようなタスクはデータベースエンジンが得意とするところ。ソートや集計の実行も同様。ETLツールもできるが、ほとんどの場合は、データベースエンジンがより高速に処理する。
ステージングテーブルを使用したデータトリアージをする場合、従来のETL変換では利用できない可能性が高いRDBMSの動作が可能。例えば、ステージングテーブルにインデックスを利用し、その後のパーマネントへのロードをパフォーマンス向上させるとか。同じデータセットに対しての複数変換を実行する場合、その変換の間、データをメモリ上で保持しなくてよくなるため、パフォーマンス影響を軽減できる可能性がある。
また、ステージングテーブルを使用すると、単純なSQLクエリでかんたんな中間結果を照会することができる。
これはまだETLなのか？
ステージングテーブルを用いた処理は、ETLなのか？という疑問。
一般的にこのやりかたの場合、ELTと呼ばれる手法になる。これは変換が行われる前に宛先にロードが行われるためである。
ELTLと呼ばれるバリエーションもあるが、ETLをあるソースからデータを取り出し、その過程で何らかの変換をして、最終的にロードするような処理すべてをETLと呼ぶ傾向にある。
ELTやELTLはETLという大きなカテゴリの一つであると考えられる。
また、ステージングテーブルの使用は、プロセスごとに評価する必要があることも覚えておいてください。
ステージングETLアーキテクチャは、いくつかのデザインパターンの一つであり、全ての負荷ニーズに理想的に適合するためではありません。成熟したETLインフラストラクチャは、従来のETL、ステージングETLおよび負荷に応じた仕様に応じたその他のバリデーションが混在しているものもある。
ぺい(pei0804)ETL Error Handling
ETLエラー処理を考える時に、考える必要があることが2つある。
1つは、このプロセスは何をするべきか。データの開始点と終了点、変換、フィルタリング、その他のステップを定義することは、他の作業をすすめる前に行う必要があります。
2つ目は、このプロセスが失敗したらどうするのか？多くの場合、この2つのシステムをすることで、それに答えることもなく設計が止まる。ETLのエラー処理は後周りにされがち。
エラー処理の重要性 フェイルファーストでやろう
ETL処理は最後にやるもんではない。むしろ設計初期段階からアーキテクチャにあるべき。ETLが構築されてからエラー処理をするのは可能だが、いびつになりえる。
最初から失敗ありきでやると強い設計になる。時間はかかるが予測可能なことが増える。
ETLエラー処理
エラーロジック管理を大きく2つある。
- エラーの防止。エラー状態を修正または回避して、プロセスが失敗することを阻止する
- エラーの対応。失敗が避けられない場合、エラーを適切に対応することが重要である。
どちらかだけではなく、どちらもやってよい。
エラー防止
ロードプロセスで障害が発生する条件には、ETLエラー処理ロジックで防ぐことができるものがあります。その中でエラー防止が有効なケースを紹介する。
- ロードプロセスは複数ステップあるが、1つの失敗が残りのロードを中断させてはならない
- データの一部に既知の欠陥があり、ETLプロセスの一部として修正可能である
- ETLプロセスの一部は冪等性であり、障害が発生した場合に再実行する必要がある。
ここはもうちょい書く
エラー対応
全てのETLエラーが防止されるべきではなく、実際はほとんどのビジネスケースはエラーを防止するよりも、エラーに対応することに適している。奇妙に聞こえるかもしれませんが、ETLプロセスの失敗を許容することに価値がある。
ぺい(pei0804)
A practice that I have seen at many places is that data teams try to patch the buggy data and handle-it-for-the-time-being. It usually works until the sprint is over and invariably the same issue comes back to haunt you. Resist the temptation of getting into a habit of fixing issues like that.
私が多くの場所で見てきた慣習は、データチームがバグのあるデータにパッチを当て、その場しのぎで処理しようとすることです。スプリントが終了するまでは、たいていうまくいきますが、必ず同じ問題が頭をもたげてきます。このような問題を修正する習慣を身につけようとする誘惑に負けないようにしましょう。