Data Factory による SQL Server と Cosmos DB のデータ連携

  • Article

Microsoft Japan Data Platform Tech Sales Team

 

 

森本 信次

 

今回は、クラウドベースのデータ統合サービス Azure Data Factory (以降 ADF) を使って Azure IaaS 環境の SQL Server のデータを Cosmos DB へコピーする手順を、Step 1 ～ 6 に沿ってご紹介します。ADF を使えば、データの移動や変換をオーケストレート自動化を、データが存在する場所（クラウド、オンプレミス）、データ型やデータ ソース（SQL, NoSQL, Hadoop 等）を意識することなくデータ統合を簡単に実現できます。

データ統合のシナリオの全体感としては下図のようになります。
adfv2_senario

Step 1. Azure Data Factory (v2) を作成し、統合ランタイムを設定
まず始めに Azure Portal から ADF を作成し、統合ランタイムの設定を行います。今回使用する Azure Data Factory バージョン 2 (プレビュー) では、現行の ADF のデータ移動と変換サービスを基に構築されていますが、データの増分読み込みやイベントトリガー型の実行のサポート、および既存の SSIS パッケージをクラウドに移行することができるようになるなど、対応できるデータ統合シナリオの幅が広がりました。バージョン 2 の変更点については詳しくは こちらをご参照ください。

1.1 Azure Portal から 新しく Data Factory を作成 adfv2_step1.1

1.2 PowerShell を起動して、作成した Data Factory に対して自己ホスト型統合ランタイムを設定

次の Set-AzureRmDataFactoryV2IntegrationRuntime コマンドレットを実行します。リソースグループ、データファクトリ および 統合ランタイムの名称は適宜変更ください。

 $resouceGroupName = "mydfv2-rg"$dataFactoryName = "my1stdfv2"$selfHostedIntegrationRuntimeName = "myirforadfv2"Login-AzureRmAccountSet-AzureRmDataFactoryV2IntegrationRuntime -ResourceGroupName $resouceGroupName -DataFactoryName $dataFactoryName -Name $selfHostedIntegrationRuntimeName -Type SelfHosted -Description "selfhosted IR description"

1.3 Data Factory に設定した統合ランタイムの使用に必要となる認証キーを取得

 

次の Get-AzureRmDataFactoryV2IntegrationRuntimeKeyコマンドレットを使用して認証キーを取得します。

 Get-AzureRmDataFactoryV2IntegrationRuntimeKey -ResourceGroupName $resouceGroupName -DataFactoryName  $dataFactoryName -Name 'myirforadfv2

コマンドレットの実行結果、認証キーが表示されるのでコピーしておきます。 adfv2_ir_4
Step 2. SQL Server 環境 および 使用するサンプルデータの準備
SQL Server の仮想マシン (IaaS) を Azure 上にデプロイします。使用するデータについては、サンプルデータベース “AdventureWorks” のバックアップファイルをこちらからダウンロードしてリストアしておきます。

2.1 Azure Portal から SQL Server の仮想マシンをデプロイします。 adfv2_step2.1

2.2 こちらからファイルをダウンロードして解凍後、サンプルデータベース “AdventureWorks” のバックアップファイルをローカルのフォルダなどに保存しておきます。
adfv2_step2.2

2.3 SQL Server Management Studio などから SQL Server に接続してバックアップファイルを使用してサンプルデータベース “AdventureWorks” をリストアします。 adfv2_step2.3

Step 3. 統合ランタイムのダウンロードおよびインストール
統合ランタイムをダウンロードして、Step 2 で準備した SQL Server (IaaS) 環境にインストールします。統合ランタイム(IR)は、異なるネットワーク環境間で、データ移動、アクティビティのディスパッチ、SSIS パッケージの実行などのデータ統合機能を提供するためのコンピューティングインフラストラクチャーです。

既定のインストールで進めます、完了すると 構成マネージャーが起動します。
adfv2_ir_1

構成マネージャーでの統合ランタイム(セルフホスト)の登録には認証キーが必要です。Step 1 で保存しておいた認証キーを貼付けます。 adfv2_ir_2

[登録]を行います。
adfv2_ir_3

Step 4. Azure Cosmos DB 環境の準備 データのコピー(Sink )先となる Azure Cosmos DB のアカウントおよびコレクションを作成します。Azure Portal から Azure Cosmos DB  アカウントを 作成します、この例では Cosmos DB アカウント名を “planet-cosmos“ 、コレクション名を “PersonAddress”、パーティションキーとしては “/AddressID” としています。Azure Cosmos DB アカウントの作成方法については、こちらのBlogをご覧頂ければと思います。 adfv2_step4.1

Step 5. リンクされたサービス、データセット、パイプラインの定義および登録
リンクされたサービスは、接続文字列によく似ており、Data Factory が外部リソースに接続するために必要な接続情報を定義します。データセットとは、アクティビティで入力と出力として使用するデータを単に指定または参照するデータの名前付きビューです。パイプラインは、1つのタスクを連携して実行するアクティビティの論理的なグループです。パイプライン内の複数のアクティビティは、データに対して実行するアクションを定義します。

例えば、コピー アクティビティを使用して、オンプレミスの SQL Server から Azure Blob Storage にデータをコピーすることができます。 その後、Azure HDInsight クラスターで Hive スクリプトを実行する Hive アクティビティを使用して、Blob Storage のデータを処理し、出力データを生成できます。 最後に、別のコピー アクティビティを使用して、ビジネス インテリジェンス (BI) レポート ソリューションが構築されている Azure SQL Data Warehouse に出力データをコピーできます。詳細についてはこちらをご参照ください。以降で使用する JSON ファイルはこちらからダウンロード頂けますので、ご自身の環境に合わせて適宜変更してご利用ください。

5.1 SQL Server にリンクされたサービスとデータセット SQL Server にリンクされたサービスの定義 JSON ファイル (SqlServerLinkedService.json)です、この例ではSQL認証を使用しています。

 {    "properties": {        "type": "SqlServer",        "typeProperties": {            "connectionString": {                "type": "SecureString",                "value": "Server=xxx;Database=AdventureWorks;User ID=sa;Password=xxx;Timeout=60"            }        },        "connectVia": {            "type": "integrationRuntimeReference",            "referenceName": "myirforadfv2"        }    },    "name": "SqlServerLinkedService"}

リンクされたサービスの定義には Set-AzureRmDataFactoryV2LinkedService コマンドレットを使用します。

 Set-AzureRmDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resouceGroupName -Name "SqlServerLinkedService" -File ".\SqlServerLinkedService.json"

SQL Server 用データセット定義の JSONファイル (SqlServerDataset.json)です。

 {   "properties": {        "type": "SqlServerTable",        "typeProperties": {            "tableName": "Person.Address"        },        "structure": [             {                "name": "AddressID",                "type": "Integer"            },            {                "name": "AddressLIne1",                "type": "String"            },            {                "name": "PostalCode",                "type": "String"            }        ],        "linkedServiceName": {            "referenceName": "SqlServerLinkedService",            "type": "LinkedServiceReference"        }    },    "name": "SqlServerDataset"}

SQL Server のデータセットは Set-AzureRmDataFactoryV2Dataset コマンドレットを使用して定義します。

 Set-AzureRmDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resouceGroupName -Name "SqlServerDataset" -File ".\SqlServerDataset.json"

5.2 Cosmos DB にリンクされたサービスとデータセット Cosmos DB にリンクされたサービスの定義 JSON ファイル(AzureCosmosDbLinkedService.json)です。

 {    "name": "AzureCosmosDbLinkedService",    "properties": {        "type": "CosmosDb",        "typeProperties": {            "connectionString": {                "type": "SecureString",                "value": "AccountEndpoint=xxx;AccountKey=xxx;Database=PersonAddressDB"            }        },        "connectVia": {            "referenceName": "myirforadfv2",            "type": "IntegrationRuntimeReference"        }    }}

同様に Set-AzureRmDataFactoryV2LinkedService コマンドレットを使用します。

 Set-AzureRmDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resouceGroupName -Name "SqlServerLinkedService" -File ".\SqlServerLinkedService.json"

Cosmos DB のデータセット定義の JSON ファイル (AzureCosmosDbDataset.json)です。

 {    "name": "AzureCosmosDbDataset",    "properties": {        "type": "DocumentDbCollection",        "linkedServiceName":{            "referenceName": "AzureCosmosDbLinkedService",            "type": "LinkedServiceReference"        },        "typeProperties": {            "collectionName": "PersonAddress"        }    }}

同様に Set-AzureRmDataFactoryV2Dataset コマンドレットを使用して定義します。

 Set-AzureRmDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resouceGroupName -Name "AzureCosmosDbDataset" -File ".\AzureCosmosDbDataset.json"

5.3 処理内容(ソースからシンクへのコピー)を定義するパイプライン

 {   "name": "SQLServerToCosmosDbPipeline",    "properties": {        "activities": [                   {                "type": "Copy",                "typeProperties": {                    "source": {                        "type": "SqlSource"                    },                    "sink": {                        "type":"DocumentDbCollectionSink"                    }                },                "name": "CopySqlServerToCosmosDbActivity",                "inputs": [                    {                        "referenceName": "SqlServerDataset",                        "type": "DatasetReference"                    }                ],                "outputs": [                    {                        "referenceName": "AzureCosmosDbDataset",                        "type": "DatasetReference"                    }                ]            }        ]    }}

 

5.4 Set-AzureRmDataFactoryV2Pipeline コマンドレットを使用してパイプラインを作成します

 Set-AzureRmDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resouceGroupName -Name "SQLServerToCosmosDbPipeline" -File ".\SQLServerToCosmosDbPipeline.json”

Step 6. 登録したパイプラインを実行してデータが登録されているかを確認
Step 5 で登録済みのパイプラインを実行して SQL Server のサンプルデータが ターゲットの Cosmos DB にコピーされているかを確認します。

6.1 Invoke-AzureRmDataFactoryV2Pipeline
コマンドレットを使用して Step 4 で作成した “SQLServerToBlobPipeline" パイプラインの実行を開始し、後で監視できるようパイプライン実行 ID をキャプチャ(変数に格納)します。

 $runId = Invoke-AzureRmDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resouceGroupName -PipelineName 'SQLServerToCosmosDbPipeline'

6.2 Azure Portal の Cosmos DBアカウントのデータエクスプローラを開きます。 adfv2_step6.2

6.3 Azure Portal からアクティビティの実行結果を確認 adfv2_step6.3

6.4 Get-AzureRmDataFactoryV2ActivityRun コマンドレットによるアクティビティの実行結果の確認
Step 6.1 で取得しておいた パイプライン実行ID ($runId)を使って、Get-AzureRmDataFactoryV2ActivityRun コマンドレットによるアクティビティの実行結果の確認も行っておきます。

while ($True){$result = Get-AzureRmDataFactoryV2ActivityRun -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineRunId $runId -RunStartedAfter (Get-Date).AddMinutes(-30) -RunStartedBefore (Get-Date).AddMinutes(30)if (($result | Where-Object { $_.Status -eq "InProgress" } | Measure-Object).count -ne 0) { Write-Host "Pipeline run status: In Progress" -foregroundcolor "Yellow" Start-Sleep -Seconds 30 } else { Write-Host "Pipeline 'SQLServerToCosmosDbPipeline' run finished. Result:" -foregroundcolor "Yellow" $result break } }

 

adfv2_step6.4

最後に
いかがだったでしょうか？ 今回は SQL Server と Cosmos DB のデータ連携 を Azure Data Factory によってどのように実現可能かをご紹介しました。今回、使用した　SQL Server の環境は Azure 上の IaaS ですが、自社オンプレミス環境で稼働している SQL Server の場合についても、基本的な設定の流れは変わりませんので、オンプレミス と クラウド 間のデータ連携のニーズがある場合には、Azure Data Factory の使用を選択肢の一つとしてご検討頂ければと思います。