1923

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

Cosmos DBでバルクアップデート

追加でCosmos DBのバルクアップデート。
複数のidに対して同じ値で更新する簡単なストアドです。取得・更新を1件ごとにREST APIで処理すると12RU/件前後(IDで取得=1, 更新=11)。10件程度をストアドで一括更新したときは75.18でした。やはりお得ですね。

bulkUpdate.js

function bulkUpdate(keys, value) {
    var context = getContext();
    var collection = context.getCollection();
    var collectionLink = collection.getSelfLink();
    if (!keys) throw new Error("The array is undefined or null.");

    var count = 0;
    var query = "SELECT * from root r WHERE r.id IN " + keys;
    collection.queryDocuments(collectionLink,
        query,
        function (err, docs) {
            if (err) throw new Error(err.message);
            for (var i = 0; i < docs.length; i++) {
                docs[i].JobTitle = value;
                tryUpdate(docs[i])
            }
            context.getResponse().setBody(count);
        });

    function tryUpdate(doc) {
        var isAccepted = collection.replaceDocument(doc._self, doc);
        if (!isAccepted) context.getResponse().setBody(count);

        count++;
    }
      
}

ステータスの一括更新的な用途で使用したかったのですが、サンプルデータにステータスがなかったので、idの1から10までをCEOにしてしまいましょう。

PowerShell

$json = "[[""('1', '2', '3', '4', '5', '6', '7', '8', '9', '10')""], [""Chief Executive Officer""]]"
exec -EndPoint $endPoint -DataBaseId $dataBaseId -MasterKey $keys.primaryMasterKey -ResourceType "sprocs" -ResourceLink "dbs/$databaseId/colls/$collectionId/sprocs/$storedProcedureId" -BodyJson $json -Uri "dbs/$databaseId/colls/$collectionId/sprocs/$storedProcedureId"

execなどはここと同じ

Cosmos DBにストアドプロシージャでバルクインサート

前回は1ドキュメント/APIでインポートしました。
Cosmos DBはJavaScriptでストアドプロシージャを記述することができますので、ストアドプロシージャを使って一括でインポートしてみます。

今回290件ほどデータを追加していますが、個別追加はRUが6.2-6.6/件くらい(合計1800超)、一括だと1227.86でした。RUも処理時間もお得ですね。

ストアドプロシージャは、Azure Cosmos DB server-side programming: Stored procedures, database triggers, and UDFs の Example: Bulk importing data into a database program にあるサンプルコードをコピーしてbulkImport.jsとして保存して使用。

bulkImport.js

function bulkImport(docs) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var count = 0;
    if (!docs) throw new Error("The array is undefined or null.");

    var docsLength = docs.length;
    if (docsLength == 0) {
        getContext().getResponse().setBody(0);
    }

    tryCreate(docs[count], callback);

    function tryCreate(doc, callback) {
        var isAccepted = collection.createDocument(collectionLink, doc, callback);
        if (!isAccepted) getContext().getResponse().setBody(count);
    }

    function callback(err, doc, options) {
        if (err) throw err;

        count++;

        if (count >= docsLength) {
            getContext().getResponse().setBody(count);
        } else {
            tryCreate(docs[count], callback);
        }
    }
}

PowerShellではストアドプロシージャをAPIで登録し、FOR JSON PATH付きの取得結果をBodyに設定するだけです。ただ、結果なJSONをそのままストアドに渡すと1番目のみしかdocsに入ってこないので、結果を新しい配列に入れてネストさせてます。 過去のブログに記載されているコードと重複している部分もありますが、まるっと掲載。

PowerShell

# いろいろExec 
function exec($endPoint, $masterKey, $resourceType, $resourceLink, $bodyJson, $uri)
{
    $verb = "POST" 
    $dateTime = [DateTime]::UtcNow.ToString("r") 
    $authHeader = Generate-MasterKeyAuthorizationSignature -verb $verb -resourceLink $resourceLink -resourceType $resourceType -key $masterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime 
    $header = @{authorization=$authHeader;"x-ms-version"="2017-02-22";"x-ms-date"=$dateTime} 
    $contentType= "application/json" 
    $queryUri = "$endPoint$uri"
    Echo $queryUri 
    $result = Invoke-RestMethod -Method $verb -ContentType $contentType -Uri $queryUri -Headers $header -Body $bodyJson
    $result | ConvertTo-Json -Depth 10 
}

# キーを取得
$keys = Invoke-AzureRmResourceAction -Action listKeys -ResourceType "Microsoft.DocumentDb/databaseAccounts" -ApiVersion "2016-03-31" -ResourceGroupName "RG01" -Name "cosmosdbtest"

# 作るモノの設定
$endPoint = "https://cosmosdbtest.documents.azure.com/"
$dataBaseId = "hogedb1"
$collectionId = "hogecoll1"
$storedProcedureId = "bulkImport"

# ストアド登録
$js = Get-Content "bulkImport.js"
$json = @{ "id"="$StoredProcedureId"; "body"="$js"; } | ConvertTo-Json 
exec -EndPoint $endPoint -DataBaseId $dataBaseId -MasterKey $keys.primaryMasterKey -ResourceType "sprocs" -ResourceLink "dbs/$databaseId/colls/$collectionId" -BodyJson $json -Uri "dbs/$databaseId/colls/$collectionId/sprocs"

# データ登録
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2016CTP3
$result = Invoke-Sqlcmd -InputFile "ex2.sql"
$json = "[" + $result.ItemArray + "]";
exec -EndPoint $endPoint -DataBaseId $dataBaseId -MasterKey $keys.primaryMasterKey -ResourceType "sprocs" -ResourceLink "dbs/$databaseId/colls/$collectionId/sprocs/$storedProcedureId" -BodyJson $json -Uri "dbs/$databaseId/colls/$collectionId/sprocs/$storedProcedureId"

これでデータ準備も含めたCosmos DBとPowerShellの3部作は一応終わりの予定。

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
}

Cosmos DB はじめました

Cosmos DB をちょっと使い始めてみたので書き付け。 最初にCosmos DBはAzureのNoSQLデータベースサービスという程度の知識しかないので、以下のサイトでお勉強。

Azure Cosmos DB Documentation

Azure Cosmos DB入門 - ryuichi111stdの技術日記

Azure Cosmos DB がやってきた

ざっくり概要や特徴などがわかったところで、手を動かして実際に試してみます。
たくさんデータモデルがあるようですが、今回はドキュメント データモデルでDocumentDB APIを使います。最初なので、まずはAzure上にリソースを作成。作ったり・消したりするのでPowerShellでさくっと準備。

データベース アカウントの作成

# ログイン
Login-AzureRmAccount 

# サブスクリプション選択
Select-AzureRmSubscription -SubscriptionId "00000000-0000-0000-0000-000000000000"

# リソースグループの作成
New-AzureRmResourceGroup -Name "RG01" -Location "Japan West" 

# データベース アカウントの作成
$locations = @(@{"locationName"="Japan West"; "failoverPriority"=0})
$ipRangeFilter = ""
$consistencyPolicy = @{"defaultConsistencyLevel"="Session";
                       "maxIntervalInSeconds"= "5";
                       "maxStalenessPrefix"= "100"}
$dbProperties = @{
                  "databaseAccountOfferType"="Standard";
                  "locations"=$locations; 
                  "consistencyPolicy"=$consistencyPolicy; 
                  "ipRangeFilter"=$ipRangeFilter
}

New-AzureRmResource -ResourceType "Microsoft.DocumentDb/databaseAccounts" -ApiVersion "2016-03-31" -ResourceGroupName "RG01" -Location "Japan West" -Name "cosmosdbtest" -PropertyObject $dbProperties

ちなみにApiVersionは以下で確認。

((Get-AzureRmResourceProvider -ProviderNamespace Microsoft.DocumentDb).ResourceTypes | Where-Object ResourceTypeName -eq databaseAccounts).ApiVersions

ということでリソースの準備完了。ついでなので、PowerShellからデータベース、コレクション、ドキュメントもREST APIを使って作成してみます。Authorizationを生成するコードなどは、How to query Azure Cosmos DB resources using the REST API by PowerShellから拝借。ApiVersionはSupported REST API Versionsで確認。

Add-Type -AssemblyName System.Web

# generate authorization key 
Function Generate-MasterKeyAuthorizationSignature 
{ 
    [CmdletBinding()] 
    Param 
    ( 
        [Parameter(Mandatory=$true)][String]$verb, 
        [Parameter(Mandatory=$false)][String]$resourceLink, 
        [Parameter(Mandatory=$true)][String]$resourceType, 
        [Parameter(Mandatory=$true)][String]$dateTime, 
        [Parameter(Mandatory=$true)][String]$key, 
        [Parameter(Mandatory=$true)][String]$keyType, 
        [Parameter(Mandatory=$true)][String]$tokenVersion 
    ) 
 
    $hmacSha256 = New-Object System.Security.Cryptography.HMACSHA256 
    $hmacSha256.Key = [System.Convert]::FromBase64String($key) 
 
    $payLoad = "$($verb.ToLowerInvariant())`n$($resourceType.ToLowerInvariant())`n$resourceLink`n$($dateTime.ToLowerInvariant())`n`n" 
    $hashPayLoad = $hmacSha256.ComputeHash([System.Text.Encoding]::UTF8.GetBytes($payLoad)) 
    $signature = [System.Convert]::ToBase64String($hashPayLoad); 
 
    [System.Web.HttpUtility]::UrlEncode("type=$keyType&ver=$tokenVersion&sig=$signature") 
} 

# いろいろCreate 
Function Create 
{ 
    [CmdletBinding()] 
    Param 
    ( 
        [Parameter(Mandatory=$true)][String]$EndPoint, 
        [Parameter(Mandatory=$true)][String]$DataBaseId, 
        [Parameter(Mandatory=$true)][String]$MasterKey, 
        [Parameter(Mandatory=$true)][String]$ResourceType, 
        [Parameter(Mandatory=$false)][String]$ResourceLink,
        [Parameter(Mandatory=$true)][String]$BodyJson 
    ) 

    $verb = "POST" 
    $dateTime = [DateTime]::UtcNow.ToString("r") 
    $authHeader = Generate-MasterKeyAuthorizationSignature -verb $verb -resourceLink $ResourceLink -resourceType $ResourceType -key $MasterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime 
    $header = @{authorization=$authHeader;"x-ms-version"="2017-02-22";"x-ms-date"=$dateTime} 
    $contentType"application/json" 
    $queryUri = "$EndPoint$ResourceLink/$ResourceType" 
 
    $result = Invoke-RestMethod -Method $verb -ContentType $contentType -Uri $queryUri -Headers $header -Body $bodyJson -Debug
    $result | ConvertTo-Json -Depth 10 
} 

# キーを取得
$Keys = Invoke-AzureRmResourceAction -Action listKeys -ResourceType "Microsoft.DocumentDb/databaseAccounts" -ApiVersion "2016-03-31" -ResourceGroupName "RG01" -Name "cosmosdbtest"

# 作るモノの設定
$EndPoint = "https://cosmosdbtest.documents.azure.com/"
$DataBaseId = "hogedb1"
$CollectionId = "hogecoll1"
$DocumentId = "doc01"

# データベース作成
$json = @{"id"="$DataBaseId"} | ConvertTo-Json 
Create -EndPoint $EndPoint -DataBaseId $DataBaseId -MasterKey $Keys.primaryMasterKey -ResourceType "dbs" -ResourceLink "" -BodyJson $json

# コレクション作成
$json = @{"id"="$CollectionId"} | ConvertTo-Json 
Create -EndPoint $EndPoint -DataBaseId $DataBaseId -MasterKey $Keys.primaryMasterKey -ResourceType "colls" -ResourceLink "dbs/$DatabaseId" -BodyJson $json

# ドキュメント作成
$json = @{ "id"="$DocumentId"; "name"="hogehoge"; "age" = 24; } | ConvertTo-Json 
Create -EndPoint $EndPoint -DataBaseId $DataBaseId -MasterKey $Keys.primaryMasterKey -ResourceType "docs" -ResourceLink "dbs/$DatabaseId/colls/$CollectionId" -BodyJson $json

という感じで基本的な環境の作成は完了。疲れたのでまるっと削除して本日は終了。

Remove-AzureRmResourceGroup -ResourceGroupName "RG01"