SQL Server 2008 徹底検証シリーズ

SQL Server 2008 徹底検証シリーズ
大規模データ ウェアハウス実践ガイド（運用管理 - 詳細編）
マージ処理パッケージの作成手順
Published．2008 年 11 月 20 日
© 2008 Microsoft Corporation. All rights reserved.
本書に記載した情報は、本書各項目に関する発行日現在の Microsoft の見解を表明するものです。Microsoft は絶えず変化する市
場へ対応しなければならないため、ここに記載した情報に対していかなる責務を負うものではなく、提示された情報の信憑性につい
ては保証できません。
本評価ガイドは情報提供のみを目的としています。Microsoft は、明示的または暗示的を問わず、本書にいかなる保証も与えるもの
ではありません。
すべての当該著作権法を遵守することはユーザーの責務です。Microsoft 書面による明確な許可なく、本書の如何なる部分について
も、転載や検索システムへの格納または挿入を行うことは、どのような形式または手段（電子的、機械的、複写、レコーディング、
その他）および目的であっても禁じられています。これらは著作権保護された権利を制限するものではありません。
Microsoft は、本書の内容を保護する特許、特許出願書、商標、著作権、またはその他の知的財産権を保有する場合があります。
Microsoft から書面によるライセンス契約が明確に供給される場合を除いて、本書の提供はこれらの特許、商標、著作権、またはそ
の他の知的財産へのライセンスを与えるものではありません。
Microsoft、Windows、MSDN、Visual Studio、SQL Server は、米国および（または）その他の国において、Microsoft Corporation
の登録商標または商標です。
その他記載されている実際の社名および製品名は、各社の商標です。
Microsoft Corporation ・ One Microsoft Way ・ Redmond、 WA 98052-6399 ・ US
2
バッチ アプリ 1（RDBMS 月次バッチ マージ処理）実装手順
この資料では、CQI-DWH（データ ウェアハウス）シナリオにおける、RDBMS の月次バッチ処理の 1
つである「マージ処理」の実装手順を詳しく説明します。
説明内容は、以下のとおりです。
目次
1．RDBMS 月次バッチ（マージ処理）の作成概要 ............................................................. 4
4
4
マージ処理とは
検証対象のシナリオ
2．UPDATE によるマージ処理（SSIS の参照と条件分割） .............................................. 6
新規プロジェクトの作成
データ フロー タスクの追加
参照コンポーネントの追加
条件分割コンポーネントの追加
ワーク テーブルへの更新データの格納
新規データの INSERT 処理
更新データの一括更新（UPDATE ステートメントの実行）
ワーク テーブルの初期化
6
7
10
13
16
19
21
23
3．MERGE ステートメントによるマージ処理 .................................................................. 26
26
28
31
データ フロー タスクの追加
MERGE ステートメントによるマージ処理
ワーク テーブルの初期化
3
1．RDBMS 月次バッチ（マージ処理）の作成概要
マージ処理とは
DWH（データ ウェアハウス）シナリオでは、更新されたデータセットを一括でテーブルへマ
ージする処理を行います。マージとは、テーブルに対する更新情報を反映する際に、既存のレ
コードの更新であれば列の更新を行い、新規レコードであれば挿入を行う処理のことです。
SQL Server 2005 では、SSIS（SQL Server Integration Services）の複数の変換コンポーネ
ントを組み合わせることによって、マージ処理を行っていましたが、SQL Server 2008 では
「MERGE」T-SQL ステートメントを使用して同様の処理を行うことができます。MERGE ス
テートメントは、UPSERT 処理と呼ばれることもあります。
検証対象のシナリオ
今回の検証では、次のシナリオを想定しました。

DWH シナリオにおいて、マスター データベースで更新されたデータを、データ ウェアハ
ウス DB のテーブルへマージします。

更新されたデータには、「新規レコード」と「更新レコード」があり、新規レコードにつ
いては「挿入」を、更新レコードについては「列の更新」処理を行います。
上記を実現するために、以下の 2 つ方法をテストしています。
方法 1
Update によるマージ処理
1 つ目の方法は、SSIS の「参照」および「条件分割」コンポーネントを利用して、新規レコ
ードと更新レコードを分別し、新規レコードはそのまま INSERT、更新レコードは UPDATE
ステートメントによる一括更新を行う方法です。
実装の流れは、次のとおりです。
1.
SSIS のデータ フロー タスクの「参照」および「条件分割」変換コンポーネントを利
用して、新規データと更新データを分別
2.
新規データは、OLE DB 変換先コンポーネントを利用して、ディメンション テーブル
へ挿入
3.
更新データは、ワーク テーブルへ挿入
4.
「SQL 実行タスク」から UPDATE ステートメントを実行して、ワーク テーブルのデ
ータをもとにディメンション テーブルを一括更新
方法 2
Merge ステートメントによるマージ処理
2 つ目の方法は、SQL Server 2008 からの新機能である「MERGE」ステートメントを利用し
て、マージ処理を行います。
実装の流れは、次のとおりです。
4
1.
SSIS のデータ フロー タスクを利用して、更新データをワーク テーブルへ挿入
2.
「SQL 実行タスク」から MERGE ステートメントを実行して、ワーク テーブルのデ
ータをもとにディメンション テーブルをマージ処理
検証で利用したテーブル
今回の検証は、顧客テーブルに対して、更新データをマージするシナリオを想定しています。
顧客テーブルのスキーマは、以下のとおりです。
CREATE TABLE[dbo].[MCustomer］（
［顧客ID］［int］NOT NULL,
［姓］［nvarchar］（10）NULL,
［名］［nvarchar］（10）NULL,
［氏名］［nvarchar］（20）NULL,
［性別］［nchar］（4）NULL,
［年齢］［int］NULL,
［持家］［nchar］（2）NULL,
［婚姻］［nchar］（4）NULL,
［収入ランクコード］［int］NULL,
［住所ID］［int］NULL,
［職業コード］［int］NULL
CONSTRAINT［PK_MCustomer］PRIMARY KEY CLUSTERED（［顧客ID］ASC））
更新されたデータセットは、全体で 5,374 件で、そのうちの 100 件が新規レコードです。
既存レコードで更新されたレコードは、「婚姻」列の値が更新されています（実際のシナリオ
ではすべての列が変更対象となりますが、今回はテスト目的であるため、また T-SQL ステー
トメントを単純化するために、1 つの列のみを対象としました）
。
以降では、それぞれの方法によりマージ処理を実現する SSIS パッケージの作成方法を説明し
ます。
5
2．UPDATE によるマージ処理（SSIS の参照と条件分割）
ここでは、方法 1 の UPDATE ステートメントを利用してマージ処理を行う SSIS パッケー
ジの実装方法を説明します。
手順の流れは、次のとおりです。
1.
SSIS のデータ フロー タスクの「参照」および「条件分割」変換コンポーネントを利
用して、新規データと更新データを分別
2.
新規データは、OLE DB 変換先コンポーネントを利用して、ディメンション テーブル
へ挿入
3.
更新データは、ワークテーブルへ挿入
4.
「SQL 実行タスク」から UPDATE ステートメントを実行して、ワーク テーブルのデ
ータをもとにディメンション テーブルを一括更新
具体的な実装手順は、次のとおりです。
新規プロジェクトの作成
1.
2.
SQL Server Business Intelligence Development Studio（BIDS）を立ち上げ、新規プロジ
ェクトを作成します。
Integration Services プロジェクトを選択します。
6
データ フロー タスクの追加
3.
ツールボックスを開き、
「データ フロー タスク」を「制御フロー」ペインへドラッグ＆
ドロップします。
4.
配置した「データ フロー タスク」をダブルクリックして、
「データ フロー」タブへ移動
します。
5.
「データ フロー」タブで、ツールボックスを開きます。
7
6.
ツールボックスから「OLE DB ソース」を「データ フロー」ペインへドラッグ＆ドロッ
プします。
配置した「OLE DB ソース」を処理の分かる名前（更新データソースなど）へ変更します。
7.
「OLE DB ソース」をダブルクリックして、エディタを起動します。
8.
「OLE DB 接続マネージャ」で「新規作成」をクリックします。
9.
「OLE DB 接続マネージャの構成」で「新規作成」をクリックします。
8
10. 更新データを保持するサーバーとデータベースを選択します。
11. 「OK」をクリックします。
12. 更新データが存在するテーブルまたはデータセットを選択します。
9
参照コンポーネントの追加
13. ツールボックスを開き、
「参照」コンポーネントを「データ フロー」ペインへドラッグ＆
ドロップします。
14. 「OLE DB ソース」から出ている緑色の矢印を、配置した「参照」コンポーネントへ接
続します。
15. 「参照」コンポーネントをダブルクリックして、エディタを起動します。
「接続」ページへ移動します。
16. 「OLE DB 接続マネージャ」で「新規作成」をクリックします。
10
17. 「OLE DB 接続マネージャの構成」で「新規作成」をクリックします。
18. 更新先データを保持するサーバーとデータベースを選択します。
19. 「OK」をクリックします。
11
20. 更新先テーブルを選択します。
21. 「列」タブへ移動します。
22. 「顧客 ID」で一致するレコードがあるか、否かを判定します。
12
23. 更新される可能性のある「婚姻」列を参照テーブルから追加します。
「OK」をクリックします。
条件分割コンポーネントの追加
24. ツールボックスを開き、
「条件分割」コンポーネントを「データ フロー」ペインへドラッ
グ＆ドロップします。
13
25. 「参照」コンポーネントから出ている緑色の矢印を、配置した「条件分割」コンポーネン
トへ接続します。
「参照の一致出力」を選択します。
26. 「条件分割」コンポーネントをダブルクリックして、エディタを起動します。
27. 出力名を設定します。
28. 「婚姻」列が更新されたか、否かを判定する条件式を作成するために、
「列」を展開して、
14
「更新データソース.婚姻」をダブルクリックします。
29. 条件へ [更新データソース].[婚姻] が追加されました。
30. 条件へ「!=」を追加記述して、
「列」から「参照.婚姻」をダブルクリックします。
31. 条件を [更新データソース].[婚姻] != [参照].[婚姻] へ設定できました。
15
「OK」をクリックします。
ワーク テーブルへの更新データの格納
32. 更新された列は、一旦ワーク テーブルへ格納します。
ツールボックスを開き、
「OLE DB 変換先」を「データ フロー」ペインへドラッグ＆ドロ
ップします。
配置した「OLE DB 変換先」を処理の分かる名前（ワーク テーブルに挿入など）へ変更し
ます。
33. 「条件分割」コンポーネントから出ている緑色の矢印を、配置した「OLE DB 変換先」
へ接続します。
16
「出力」で「更新された列」を選択して、「OK」をクリックします。これで、条件分割コ
ンポーネントで設定した条件 [更新データソース].[婚姻] != [参照].[婚姻] に該当するレコ
ード（婚姻が等しくない、つまり婚姻が更新された行データ）を OLE DB 変換先へ転送す
ることができます。
34. 「OLE DB 変換先」をダブルクリックして、エディタを起動します。
35. ワーク テーブルを作成する先となるデータベースへの接続を選択して、
「新規作成」をク
リックします。
36. ワーク テーブル（更新データの格納先）を、更新先テーブルと同様のスキーマで作成し
17
ます（変更対象列の「婚姻」に注意します）
。
37. 「バッチごとの行数」には、実際よりも大きい行数を指定します。
38. 「マッピング」ページへ移動して、更新先データソースの列が挿入されるようにマッピン
グします。
マッピングを設定後、
「OK」をクリックします。
18
新規データの INSERT 処理
39. 更新データのうちの新規データを更新先テーブルへ INSERT するために、ツールボック
スから「OLE DB 変換先」を「データ フロー」ペインへドラッグ＆ドロップします。
40. 配置した「OLE DB 変換先」を処理の分かる名前（更新先テーブルへ挿入など）へ変更
します。
41. 「参考」コンポーネントから出ている緑色の矢印を、配置した「OLE DB 変換先」へ接
続します。
19
「入出力の選択」では、
「参照の不一致出力」を選択します。これにより、参照コンポーネ
ントで参照できなかったレコード（更新先テーブルに「顧客 ID」が存在しないデータ、つ
まり新規データ）を OLE DB 変換先へ転送することができます。
42. 「OLE DB 変換先」をダブルクリックして、エディタを起動し、更新先テーブルが存在
するデータベースへの接続、更新先テーブルを指定します。
43. 「マッピング」ページへ移動して、そのまま「OK」をクリックします。
以上で、更新データのうちの新規データを更新先テーブルへ INSERT できるようになりま
す。
20
更新データの一括更新（UPDATE ステートメントの実行）
44. 「制御フロー」タブへ移動して、ツールボックスを開きます。
45. ワーク テーブルのデータをもとに、更新先テーブルを一括更新するために、ツールボッ
クスから「SQL 実行タスク」を「制御フロー」ペインへドラッグ＆ドロップします。
配置した「SQL 実行タスク」は、処理の分かる名前（更新レコードを一括更新など）へ変
更します。
46. 「データ フロー タスク」コンポーネントから出ている緑色の矢印を、
「SQL 実行タスク」
へ接続します。
「SQL 実行タスク」をダブルクリックして、エディタを起動します。
47. 「Connection」で、更新先テーブルが存在するデータベースへの接続を指定します。
21
48. 「SQLStatement」で「...」をクリックします。
49. ワーク テーブルに挿入されたレコードで、更新先テーブルを一括更新する UPDATE 文
を記述します。
Tips: UPDATE ステートメントによる一括更新
ワーク テーブルに挿入されたレコードをもとに、更新先テーブルを一括更新する
UPDATE ステートメントは、次のように記述します。
update MCustomer
set 婚姻 = src.婚姻
from MCustomer trg join MCustomer_work src
on trg.顧客ID = src.顧客ID
22
50. 「OK」をクリックします。
ワーク テーブルの初期化
51. ワーク テーブルを初期化するために、ツールボックスから「SQL 実行タスク」を「制御
フロー ペイン」へドラッグ&ドロップします。
配置した「SQL 実行タスク」を、処理の分かる名前（ワーク テーブルの初期化など）へ
変更します。
23
52. 「SQL 実行タスク」から出ている緑色の矢印を、
「データ フロー タスク」へ接続します。
「SQL 実行タスク」をダブルクリックして、エディタを起動します。
53. ワーク テーブルが存在するデータベースへの接続を選択して、
「SQLStatement」で「...」
をクリックします。
54. Truncate Table ステートメントでワーク テーブルを初期化するようにクエリを記述し
ます。
55. 「OK」をクリックします。
24
56. 以上で、SSIS の「参照」および「条件分割」コンポーネント、UPDATE ステートメン
トを利用して、マージ処理を行うパッケージが完成です。
25
3．MERGE ステートメントによるマージ処理
ここでは、マージ処理を行う 2 つ目の方法である、SQL Server 2008 からの新機能の
「MERGE」ステートメントを利用した SSIS パッケージの実装方法を説明します。
手順の流れは、次のとおりです。
1.
SSIS のデータ フロー タスクを利用して、更新データをワーク テーブルへ挿入
2.
「SQL 実行タスク」から MERGE ステートメントを実行して、ワーク テーブルのデ
ータをもとにディメンション テーブルをマージ処理
具体的な実装手順は、次のとおりです。
データ フロー タスクの追加
1.
方法 1 の「Update によるマージ処理」のときと同様、SSIS プロジェクトを新規作成しま
す。
2.
ツールボックスを開き、「データ フロー タスク」を「制御フロー」ペインへドラッグ&ド
ロップします。
配置した「データ フロー タスク」を、処理の分かる名前（更新データをワーク テーブル
へ挿入など）へ変更します。
3.
「データ フロー タスク」をダブル クリックして、
「データ フロー」タブへ移動します。
更新データを取得するするために、ツールボックスから「OLE DB ソース」を「データ フ
ロー」ペインへドラッグ＆ドロップします。
配置した「OLE DB ソース」を、処理の分かる名前（更新データソースなど）へ変更しま
す。
26
4.
「OLE DB ソース」をダブルクリックして、エディタを起動し、更新データが存在する
テーブルの接続とテーブル名を選択します。
5.
ツールボックスから「OLE DB 変換先」を「データ フロー」ペインへドラッグ＆ドロッ
プします。
配置した「OLE DB 変換先」を、処理の分かる名前（ワーク テーブルに挿入など）へ変更
します。
6.
「OLE DB ソース」から出ている緑色の矢印を、「OLE DB 変換先」コンポーネントへ
接続します。
7.
「OLE DB 変換先」コンポーネントをダブルクリックして、エディタを起動します。
27
更新データをそのまま、ワーク テーブルへ挿入するようにします。
8.
「マッピング」ページへ移動して、
「OK」をクリックします。
MERGE ステートメントによるマージ処理
9.
「制御フロー」タブで、ツールボックスを開きます。
10. MERGE ステートメントを実行するために、ツールボックスから「SQL 実行タスク」を
「制御フロー」ペインへドラッグ＆ドロップします。
28
配置した「SQL 実行タスク」を、処理の分かる名前（Merge 実行など）へ変更します。
11. 「データ フロー タスク」から出ている緑色の矢印を、
「SQL 実行タスク」コンポーネン
トへ接続します。
12. 「SQL 実行タスク」をダブルクリックして、エディタを起動し、
「Connection」で更新先
テーブルが存在する接続を指定し、
「SQLStatement」で「...」をクリックします。
13. テーブルに挿入されたレコードで、更新先テーブルをマージする T-SQL 文を記述します。
29
Tips: MERGE ステートメント
ワーク テーブルのデータをもとに、更新先テーブルを一括更新（マージ処理）する MERGE
ステートメントは、次のように記述します。
merge into MCustomer as trg
using MCustomer_work as src
on trg.顧客ID = src.顧客ID
when matched then update set trg.婚姻= src.婚姻
when not matched then insert values (
src.顧客ID,
src.姓,
src.名,
src.氏名,
src.性別,
src.年齢,
src.持家,
src.婚姻,
src.収入ランクコード,
src.住所ID,
src.職業コード
);
14. 「OK」をクリックします。
30
ワーク テーブルの初期化
15. ワーク テーブルを初期化するために、ツールボックスから「SQL 実行タスク」を「制御
フロー」ペインへドラッグ＆ドロップします。
配置した「SQL 実行タスク」を、処理の分かる名前（ワーク テーブルの初期化など）へ
変更します。
16. 「SQL 実行タスク」から出ている緑色の矢印を、
「データ フロー タスク」コンポーネン
トへ接続します。
17. 「SQL 実行タスク」をダブルクリックして、エディタを起動し、
「SQLStatement」で「...」
をクリックします。
18. Truncate Table でワーク テーブルを初期化するようにクエリを記述します。
31
19. 「OK」をクリックします。
20. 以上で、MERGE ステートメントを利用してマージ処理を行う SSIS パッケージが完成
です。
以上
32