1923

都内と業界の隅っこで生活しているエンジニアのノート

Cosmos DBにSQL Serverのデータをインポート

Cosmos DBのDocumentDB APIで使用するデータをインポートします。今回はローカルSQL Serverのデータ(AdventureWorks2016CTP3)がソースです。 方法はいろいろありますが、今回試したのは簡単にできるツールとPowerShellの2つ。

データ移行ツールを使う

データ移行ツール使って簡単・高速でインポートできます。いろいろなデータソースに対応していますし、データベースやコレクションも同時に作成してくれる便利なツールです。
詳しい説明は、DocumentDB API 用に Azure Cosmos DB にデータをインポートする方法をご覧ください。

上記サイトのサンプルでクエリを外部ファイルと指定したするとこんな感じです。 使用するクエリで気をつけるところもIDをvarcharにCAST、階層構造をNestingSeparatorで指定した".“にする程度です。

コマンド

dt.exe /s:SQL /s.ConnectionString:"Data Source=.\;Initial Catalog=AdventureWorks2016CTP3;Integrated Security=true;" /s.QueryFile:"..\ex1.sql" /s.NestingSeparator:. /t:DocumentDBBulk /t.ConnectionString:"AccountEndpoint=https://cosmosdbtest.documents.azure.com:443/;AccountKey=XXXXXXXXXXXXXXXXXXXXXXXX==;Database=testdb2;" /t.Collection:testcoll1 /t.IdField:Id

クエリ(ex1.sql

SELECT
    CAST(BusinessEntityID AS varchar) as Id, 
    Name, AddressType as [Address.AddressType], 
    AddressLine1 as [Address.AddressLine1], 
    City as [Address.Location.City], StateProvinceName as [Address.Location.StateProvinceName], 
    PostalCode as [Address.PostalCode], CountryRegionName as [Address.CountryRegionName]
FROM Sales.vStoreWithAddresses 
WHERE AddressType='Main Office'

適当にPowerShellを書いて実行

PowerShellでもSQL Serverから読み取ってAPIでドキュメント作っても簡易的なインポートはできます。単純に1つ1つ登録しているので速度的なものは考慮なし。

PowerShell
Generate-MasterKeyAuthorizationSignatureとCreateは前回と同じなので省略。

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2016CTP3
$result = Invoke-Sqlcmd -InputFile "ex2.sql"
$items = $result.ItemArray | ConvertFrom-Json
ForEach ($item in $items) {
    $json = $item| ConvertTo-Json
    Create -EndPoint $EndPoint -DataBaseId $DataBaseId -MasterKey $Keys.primaryMasterKey -ResourceType "docs" -ResourceLink "dbs/$DatabaseId/colls/$CollectionId" -BodyJson $json
}

クエリ
クエリは列名「Id」を「id」に、クエリ結果がJSONで欲しいので FOR JSON PATH を追加。

[ex2.sql]

SELECT
    CAST(e.BusinessEntityID AS varchar) as id, 
    NationalIDNumber,
    LoginID,
    JobTitle,
    JSON_QUERY(HistoryDepartment.HistoryDepartmentID, '$') AS HistoryDepartmentID             
FROM
    HumanResources.Employee e
    INNER JOIN
        (SELECT 
            BusinessEntityID,
            '[' + REPLACE((SELECT DepartmentID AS [data()] 
            FROM    HumanResources.EmployeeDepartmentHistory
            WHERE H.BusinessEntityID = BusinessEntityID
            FOR XML PATH('')), ' ', ',') + ']' AS HistoryDepartmentID
        FROM
            HumanResources.EmployeeDepartmentHistory AS H
        GROUP BY BusinessEntityID) AS HistoryDepartment 
    ON HistoryDepartment.BusinessEntityID = e.BusinessEntityID
FOR JSON PATH

dt.exeだと配列を文字型として処理するので[1, 2, 3]が"[1, 2, 3]“となってしまったので、こちらを使用。例えば下記のような配列情報を持つデータのHistoryDepartmentIDも配列としてインポートできます。

id NationalIDNumber LoginID JobTitle HistoryDepartmentID
3 509647174 adventure-works\roberto0 Engineering Manager [1]
4 v112457891 adventure-works\rob0 Senior Tool Designer [1,2]

登録結果

{
    "id": "4",
    "NationalIDNumber": "112457891",
    "LoginID": "adventure-works\\rob0",
    "JobTitle": "Senior Tool Designer",
    "HistoryDepartmentID": [
        1,
        2
    ],
    "_rid": "AAAAAAAAAAAAAAAAAAAA==",
    "_self": "dbs/3mwJAA==/colls/0000000=/docs/AAAAAAAAAAAAAAAAAAAA==/",
    "_etag": "\"00000000-0000-0000-0000-000000000000\"",
    "_attachments": "attachments/",
    "_ts": 1503725821
}