Mapping of offers to collections

[WatersJohn]

This may be more of a cosmosDB concepts issue rather than a CosmosDB Powershell issue, but thought I'd ask - feel free to close if out of scope.

I have a context for a given database, and have obtained a list of offers

$cosmosDbContext = New-CosmosDbContext -Account $account -Database $dd -ResourceGroup $rg -MasterKeyType 'SecondaryMasterKey'; $offers = Get-CosmosDbOffer -Context $cosmosDbContext; $colls = Get-CosmosDbCollection -Context $cosmosDbContext;

Then I have code that iterates through the offers, and eventually tries to match up the offer to a collection as follows:

foreach ($offer in $offers) { $collName = ($colls | where-object {$_._rid -eq $rid} ).Id }

However, it looks like those two objects really don't match up as noted by the different resource IDs below:

Offers:
Id: h-5E OfferVersion: V2 OfferType: invalid Etag: 00007801-0000-0000-0000-5acbb2610000 ResourceId: h-5E Timestamp: 4/9/2018 2:35:13 PM Uri: offers/h-5E/

Collections
id : Foo indexingPolicy : @{indexingMode=consistent; automatic=True; includedPaths=System.Object[]; excludedPaths=System.Object[]} _conflicts : conflicts/ _docs : docs/ _etag : "00000700-0000-0000-0000-5acd17070000" _rid : Q4QdAMy0QwE= _sprocs : sprocs/ _triggers : triggers/ _ts : 1523390215 _udfs : udfs/

Any thoughts on how i could match these two up?

[dl8on]

I'm not sure if this will help, but here's one way to link up and work with an Offer for a specific Collection:

Get-CosmosDbCollection -Context $cosmosContext -Id $collectionId | ForEach-Object {Get-CosmosDbOffer -Context $cosmosContext -Query ('SELECT * FROM root WHERE (root["resource"] = "{0}")' -f $_._self)} 

Where $cosmosContext is the context you've created, and $collectionId is the name of the Collection for which you're trying to retrieve the Offer details.

I hope that this helps you out!

[WatersJohn]

This appears to be working; thank you. Do you know where I can find out more about the query that's being issued? (not the powershell, but the select statement). Oddly, running it in Data Explorer returns no results.

Separately, I got a 429 at one point when running this against my subscription, which was unexpected. When I get some time, Ill look into creating a pull request to enhance Invoke-CosmosDBRequest for 429s.

[dl8on]

Generally, you can get info about querying here:

https://docs.microsoft.com/en-us/rest/api/cosmos-db/querying-cosmosdb-resources-using-the-rest-api

Ultimately, the REST API allows you to POST queries using the SQL-like syntax described here:

https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-sql-query

[PlagueHO]

Great discussion and info here @WatersJohn and @dl8on ! I've found this myself when implementing the Offers cmdlets - the link between Offers and Collections is not that clear. So I think improving the examples and even including @dl8on's example in the README.MD would be good. So is it OK if I reopen this to remind myself to do this?

As for the 429: the constant battle with RU's 😁 I always meant to add some support for backing off and retrying after a 429 like the DocumentDB SDK does. But haven't got round to it. So I've raised an issue over here: #87. So if you feel like implementing the support and submitting a PR it will be gratefully accepted!

[jasonchester]

Here's a script I use to report on throughput.

# Get-Offers.ps1
[CmdletBinding()]
param ()

$contexts = @()

$contexts += New-CosmosDbContext -Account dev01-mongodb01 -Database formio -Key (
    ConvertTo-SecureString -AsPlainText -Force -String "8v..."
)

$contexts +=  New-CosmosDbContext -Account qa01-mongodb01 -Database formio -Key (
    ConvertTo-SecureString -AsPlainText -Force -String "tK..."
)

$contexts += New-CosmosDbContext -Account prod01-mongodb01 -Database formio -Key (
    ConvertTo-SecureString -AsPlainText -Force -String "aR..."
)

$contexts |
Select-Object * -PipelineVariable ctx |
ForEach-Object -PipelineVariable collection {
    Get-CosmosDbCollection -Context $ctx
} |
ForEach-Object -PipelineVariable offer {
    $query = ('SELECT * FROM root WHERE (root["offerVersion"] = "V2" and root["offerResourceId"] = "' + $collection.ResourceId + '" )')
    Write-Verbose $query
    Get-CosmosDbOffer -Context $ctx -Query $query
} |
Select-Object @{n="Account"; e={$ctx.Account}},
    @{n="Database"; e={$ctx.Database}},
    @{n="Collection"; e={$collection.Id}},
    @{n="throughPut"; e={$offer.content.offerThroughPut}}

To invoke:

PS /Users/jason.chester/_pwsh> ./Get-Offers.ps1 | ft -AutoSize

Account          Database Collection     throughPut
-------          -------- ----------     ----------
dev01-mongodb01  formio   actions               400
dev01-mongodb01  formio   schema                400
dev01-mongodb01  formio   projects              400
dev01-mongodb01  formio   roles                 400
dev01-mongodb01  formio   submissions           400
dev01-mongodb01  formio   forms                 400
qa01-mongodb01   formio   actions               400
qa01-mongodb01   formio   schema                400
qa01-mongodb01   formio   roles                 400
qa01-mongodb01   formio   submissions           400
qa01-mongodb01   formio   projects              400
qa01-mongodb01   formio   forms                 400
prod01-mongodb01 formio   actions               400
prod01-mongodb01 formio   schema                400
prod01-mongodb01 formio   roles                 400
prod01-mongodb01 formio   forms                 400
prod01-mongodb01 formio   projects              400
prod01-mongodb01 formio   submissions           400
prod01-mongodb01 formio   system.indexes        400