パイプライン内のトランザクションについて
コレ、前から気になってたこと。
案件でハマりそうだったので、中の人に聞いてみた。
結論は以下。
- コピーアクティビティだと、トランザクションの制御は実現できない。
- Mapping Data Flow だと、一つの操作（insert/delete/upsert/update）に対してトランザクション制御は可能。しかし、「Pre SQL Scripts」に記載されている SQL についてはロールバック不可能。
- Azure Data Factory で厳密なトランザクション管理を求められる場合は、ストアドプロシージャアクティビティを使用すると良い（トランザクション管理を DB 側に任せることで実現）。
以下、質問と回答。
※いろいろ前提事項を書いてたけど、ここでは端折ってる。
＜質問内容＞
コピーアクティビティを使ってデータの洗い替えを実施しようと考えています。
（オンプレの SQL Server → SQLDB ）
洗い替えのデータ削除部分はコピーアクティビティのシンクにある「事前コピースクリプト」に DELETE 処理を記載して実現しようとしており、データ追加部分はコピーアクティビティのソースにある「クエリ」で実現しようとしています。
上記の条件で ETL ジョブ全体（パイプライン全体）でトランザクション管理を実施したい場合は、どのようにすれば良いのでしょうか。
Mapping Data Flow では「Transaction Commit（トランザクション コミット）」を設定できる箇所があるのは認識しているのですが、コピーアクティビティではトランザクション管理はできない、という認識でよろしいでしょうか。
＜回答＞
現在の Azure Data Factory のコピーアクティビティにおいて、トランザクションの制御はできない。
Mapping Data Flow では、一つの操作（insert/delete/upsert/update）に対してトランザクション制御は可能だが、複数の操作を一つのトランザクションとして制御することはできない。
例えば、データ削除とデータ挿入のデータフローを構成する場合、データ削除後、データコピーが失敗した際にデータ削除後の状態に戻るが、データ削除前の状態に戻すことはできない。
その代替案として、ストアドプロシージャアクティビティの利用を提案する。
ストアドプロシージャアクティビティを使用して、SQLDB のストアドプロシージャを呼び出すことができる。
SQLDB のストアドプロシージャにてトランザクション制御を定義することが可能であるため、Azure Data Factory ストアドプロシージャアクティビティから SQLDB のストアドプロシージャを呼び出すことで、要望の動作が実現できる。
～ストアドプロシージャの処理仕様～
1. SQL Server から SQLDB のバックアップ用テーブル A に、対象となるデータをコピー。
2. SQLDB のストアドプロシージャを呼び出すことで、SQLDB 側の更新対象となるテーブル B に対してデータの削除と挿入を行う。
SQLDB のストアドプロシージャにて、以下の処理を行う。
* トランザクションを開始。
* SQLDB のテーブル B に対して指定した期間のデータを削除。
* テーブル A からテーブル B にデータコピーを行う。
* コピーが成功した場合、トランザクションをコミット。
* コピーが失敗した場合、トランザクションをロールバック。
SQLDB 側のトランザクションに関する情報は以下を参照のこと。
【参考：トランザクション (Transact-SQL)】
うん、想定した答えだったな。
できないならできないって知りたいもんね。
今回は「コケたらもう一回やり直せばええやん」って考えで押し切ったわ。
