【SSIS】SSIS覚書 - データフローのコンポーネント(2)
前書き
前回の続きです。今回も飽きるまで説明していきます。
参照変換
個人的には非常によく使うんですが、バッドノウハウだらけの面倒臭いやつです。
機能
DBのキー項目となるカラムと入力列のキー項目でマッピングし、好きなカラムを新規列として取得します。
DBのキー項目と取得する項目はSQLで記述します。参照変換エディターの「接続」ペインでOLE DB接続マネージャーを設定し、「SQL クエリの結果を取得する(S)」にチェックを入れてがりがり書きましょう。
テーブルやビューを選択する方法もありますが、あれはSELECT *を使って全部とってくるクソクエリを生成するので絶対に使ってはいけません。
また、GUIで選択するだけでクエリを作ってくれる機能もありますが、この世の地獄みたいなインデントのクエリが生成されます。SQLを書くぐらいだったら死を選ぶほどの意志を持ってない限りは自前で書いてくれると私が後で読みやすいです。
キャッシュ
参照変換は同期変換の一種なので、基本的にはデフォルトで設定されているフルキャッシュで使用すべきだと思います。「部分キャッシュ」や「キャッシュなし」は下手すると入力データの行数分だけクエリを発行してしまいます。
ただし、注意しなくてはならないのはフルキャッシュであっても一度にキャッシュできるサイズに制限があると言うことです。と言っても明示的にこのサイズまでと言う制限があるわけではなく、実行環境のメモリ（RAM）に依存します。
もし参照変換から巨大なクエリを発行するようであれば、事前にキャッシュ変換でキャッシュファイルを作成しておき、それを接続マネージャーとして使う方法があるので、そちらをオススメします。
「入力行は極端に少ないがクエリがめちゃめちゃでかい」と言う場合には部分キャッシュやキャッシュなしを選択することを視野にいれてもいいと思います。まぁ、あまりないと思いますが。
更に細かい話をしておくと、フルキャッシュの参照変換はDBへのアクセスを二回行います。
一回目は検証フェーズです。取得する項目の型やLengthなどのメタデータを取得し、デザイン時に取得したメタデータと一致するかどうか検証します。
二回目は実行前フェーズです。実際にクエリを発行し、キャッシュを作成します。実行時フェーズの段階ではキャッシュと入力行（のキー項目）をぶつけているだけです。
関数による変換だとか、テーブル同士の結合だとかを大量に行っているクエリだと、検証フェーズの段階でも結構な時間がかかってしまうことがあります。かと言ってSSISレベルでできることは特になく、結局のところクエリそのものを見直したりインデックスをはったりしないとチューニングできませんが…。
参照変換のキー
参照変換は以下の原則に基づいて行われます。
- テーブルのキー項目の値は絶対に重複していない
- 入力行のキー項目の値はマッピング先の項目に必ず存在する
世の中のテーブルレイアウトが全部そんなんだったら私の残業時間も減るんですけどね…。上記の二条件を満たしていないと想定外の結果になることがあります。そして割とハマりやすいです。
まず「テーブルのキー項目の値が重複しているケース」ですが、これは（キャッシュあり / なしに関わらず）一番最初にヒットしたものを取得し、残りは捨てられます。当然ORDER BYを指定していなければ実行するごとに値が変わる可能性もあります。
一応参照変換の「TreatDuplicateKeysAsError」と言うプロパティをいじればキー項目の値が重複しているときにエラーにできますが、結合できた行はすべて欲しいと思うことも当然あるでしょう。しかしそれは参照変換ではできません。諦めてマージ結合を使いましょう。
次に「入力行のキー項目の値がマッピング先の項目に存在しないケース」ですが、これは設定でいじることができます。デフォルトではエラーです。
「全般」ペインの「エントリが一致しない行の処理方法を指定する(S)」と言うコンボボックスから以下の4つを選択することができます。
- エラーを無視する
- エラー出力に行をリダイレクトする
- エラーコンポーネント（デフォルトの設定）
- 一致なしの出力に行を出力する
キーがヒットしなくてもいいなら「エラーを無視する」を選択しましょう。取得しようとした値は自動でNULLになります。
一致する / しないで処理を分岐させたい場合は「エラー出力に行をリダイレクトする」か「一致なしの出力に行を出力する」を選択しましょう。どちらも処理的な意味合いは同じです。やろうとしていることに対してどちらが適切かで判断しましょう。
一致しなかったらまずい場合は「エラーコンポーネント」です。その場でデータフローが異常終了します。
その他もろもろ
どう頑張っても変数は使えません。使えたら色々便利だったんですけどね…。あ、スクリプトコンポーネントなら（省略）
「使用できる参照列」は書いたクエリの情報から取得します。CONVERT関数を使ったりカラムにエイリアスを設定しておくと色々捗ります。
また、値を必ず取得しなきゃいけないわけではないので、「テーブルにこの値があるかどうか」の判断のためだけにも使えます。
マルチキャスト変換
非常に単純明快なコンポーネントです。入力行をまるまるコピーした出力を増やすことができます。
同じ入力に対して出力が複数ある場合はこれを使いましょう。
条件分割変換
式（Expression）を使って入力行を分岐させることができます。
ふつーのプログラムのifブロックと同じです。上から順番に式をチェックし、一番最初にTrueを返してきた出力に分岐します。一つもTrueを返さないと「条件分割の規定出力」とか言うところに流れていきます。
NULLはFalseではありません。必ずNULLチェックをしましょう。エラーで死にます。
また、出力を作るだけ作っておいてパスを通さなければ、そのデータを捨てることもできます。
行数変換
そのパスを通る入力の行数を取得することができます。
注意する点は二つです。
- 事前に数値型の変数を作成しておく必要がある
- 実際に値が変数に出力されるのは実行後フェーズ
上記の通り、実際に値が入るのはデータフローの実行後です。なので、行数によって条件分割で処理を分岐、なんてことはできません。
これの応用で行数変換非同期版とか、行番号付与とか、作っといてくれればよかったのにね。
並べ替え変換
ソートしてくれます。ソートしなきゃいけないので非同期変換です。主に使う理由は別の理由です。
（開発者的に）もっとも手軽に重複行を削除してくれる変換です。これ以外では集計変換でやるしかないんですが、そっちは結構面倒です。
また、マージ結合を行うためには必ず事前に各入力のキーとなる項目を同じ順番に並べ替えておく必要があります。
並べ替えも非同期変換でマージ結合も非同期変換で、コストが非常に高そうに見えるんですが、並べ替え済みのおかげでマージ結合はめっちゃめちゃ速いです。
まとめ
飽きたので一旦終わりにします。
