CosmosDB
CosmosDB copied to clipboard
PlagueHO
Getting all document data
Issue
Following documentation and getting all documents in a collection, each document is missing fields (until appending)
Proposed Solution
Update documentation to show examples of using ... | ConvertTo-Json | ConvertFrom-Json where appropriate
Below has a few examples of using ... | ConvertTo-Json | ConvertFrom-Json
For reference only: Too much code to be on the readme:
$url = Read-Host -Prompt "Enter the Cosmos URL"
$port = Read-Host -Prompt "Enter the Cosmos Port"
$key = Read-Host -Prompt "Enter the Cosmos key" -AsSecureString
$databaseName = Read-Host -Prompt "Enter the database name"
$containerName = Read-Host -Prompt "Enter the container name"
$backoffPolicy = New-CosmosDbBackoffPolicy -MaxRetries 20
$primaryKey = ConvertTo-SecureString -String $key.Trim() -AsPlainText -Force
if ($usesEmulator -eq $true) {
$cosmosDbContext = New-CosmosDbContext -Emulator -Database $databaseName -URI $url -PORT $port -Key $primaryKey -BackoffPolicy $backoffPolicy
} else {
$sourceCosmosDbContext = New-CosmosDbContext -Database $databaseName -URI $url -PORT $port -Key $sourceKey -BackoffPolicy $backoffPolicy
}
$collection = Get-CosmosDbCollection -Context $cosmosDbContext -Id $containerName
$collectionId = Get-CosmosDbCollectionResourcePath -Database $databaseName -Id $containerName
$partitionKey = ($collection | ConvertTo-Json | ConvertFrom-Json | % { $_.partitionKey.paths[0] }).TrimStart('/')
$documentsPerRequest = 20
$continuationToken = $null
$allDocumentHandles = $null
do {
$responseHeader = $null
$getCosmosDbDocumentParameters = @{
Context = $cosmosDbContext
CollectionId = 'MyNewCollection'
MaxItemCount = $documentsPerRequest
ResponseHeader = ([ref] $responseHeader)
}
if ($continuationToken) {
$getCosmosDbDocumentParameters.ContinuationToken = $continuationToken
}
$documentHandles = Get-CosmosDbDocument @getCosmosDbDocumentParameters
$allDocumentHandles += $documentHandles
$documentHandles | Foreach-Object {
# Format of output is different - both formats are helpful for different uses (please keep $_ same format)
Write-Output $_
Write-Output '----------------------------'
$document = ($_ | ConvertTo-Json | ConvertFrom-Json)
Write-Output $document
# Now if you want to query all documents in the same partition, you have partition value to do so!
Write-Output 'Partition Value'
$partitionValue = (Invoke-Expression "`$document.$partitionKey")
Write-Output $partitionValue
}
$continuationToken = Get-CosmosDbContinuationToken -ResponseHeader $responseHeader
} while (-not [System.String]::IsNullOrEmpty($continuationToken))
For reference only: Too much code to be on the readme
Environment
Before submitting your issue for the CosmosDB project, please take a moment to provide the following details:
- [ 5 ] Version of PowerShell you're using
- [ ISE ] PowerShell host you're using (eg. Console Host, ISE, Visual Studio)
- [ Windows 10 ] Operating system you're running
- [ 4.1.0 ] Version of CosmosDB PowerShell Module you're using (use
Get-Module -Name CosmosDB)
Call Out
Last issue from me, I promise!
Just documentation. But it was something I was briefly stuck on. And found the solution through reading open issues.
Full Sample
Sample that copies between collections, allowing you to programatically change the partitionKey value
It encourages copying from Azure to the Emulator, and only copying to empty collections
https://docs.microsoft.com/en-us/azure/cosmos-db/synthetic-partition-keys explains why you would want to change the partitionKey values
if (Get-Module -Name AzureRM -ListAvailable) {
Write-Warning -Message 'Az module not installed. Having both the AzureRM and Az modules installed at the same time is not supported.'
foreach ($module in (Get-Module -ListAvailable AzureRM*).Name |Get-Unique) {
write-host "Removing Module $module"
Uninstall-module $module
}
}
if (-Not (Get-Module -Name Az) -And -Not (Get-Module -Name AzureRm -ListAvailable)) {
Write-Output 'If the below does not work, try enabling scripting or downloading Az module manually'
Write-Output 'https://docs.microsoft.com/en-us/powershell/azure/install-az-ps-msi?view=azps-4.1.0'
Install-Module -Name PowerShellGet -Force
Install-Module -Name Az -AllowClobber #-Scope CurrentUser
Uninstall-AzureRM
Enable-AzureRmAlias -Scope LocalMachine
}
if (-Not (Get-MOdule -Name CosmosDB)) {
Install-Module -Name CosmosDB -RequiredVersion 4.1.0
}
Import-Module Az
Import-Module CosmosDB
### - The below is only useful if you are programatically creating databases and containers - Arm Templates seems like a better tool - ########################
## There is no need to choose between subscriptions, however above is needed since CosmosDB module depends on Az #
#try { #
# $subscriptions = Get-AzSubscription #
#} catch { #
# Connect-AzAccount #
# $subscriptions = Get-AzSubscription #
#} #
# #
#$options = $subscriptions | % { New-Object System.Management.Automation.Host.ChoiceDescription ('&' + $_.Name), ($_.Name + ' (' + $_.SubscriptionId + ')') } #
# #
#$title = 'Azure Subscription' #
#$message = 'Which Azure Subscription?' #
#$index = $host.ui.PromptForChoice($title, $message, $options, 0) #
# #
#$selectedSubscriptionId = $subscriptions[$index] | % { $_.SubscriptionId } #
#$selectedSubscriptionContext = Get-AzSubscription -SubscriptionId $selectedSubscriptionId #
#Set-AzContext -SubscriptionId "$selectedSubscriptionId" #
#$defaultSubscriptionContext = Get-AzSubscription -Default #
# #
#$cosmosResourceGroup = Read-Host -Prompt 'Please enter the Cosmos resource group name: ' #
# #
#Get-AzCosmosDBAccount -ResourceGroupName $cosmosResourceGroup #
#Get-AzCosmosDBAccountKey -ResourceGroupName $cosmosResourceGroup -Type "ConnectionStrings" #
###############################################################################################################################################################
$sourceUsesEmulator = ((Read-Host -Prompt "Use Azure Cosmos Emulator for source? [y/N]") -like "y")
if ($sourceUsesEmulator) {
$cosmosEmulatorPath = Get-Package | Where-Object{ $_.Name -like "Azure Cosmos DB Emulator" } | % { $_.Source }
if ([string]::IsNullOrEmpty($cosmosEmulatorPath)) {
Start "https://aka.ms/cosmosdb-emulator"
}
while ([string]::IsNullOrEmpty($cosmosEmulatorPath)) {
if ((Read-Host -Prompt "Has Azure Cosmos Emulator been downloaded and installed? [Press enter to check or y to skip]") -like "y") {
break
}
$cosmosEmulatorPath = Get-Package | Where-Object{ $_.Name -like "*Cosmos*" } | % { $_.Source }
}
Start -FilePath (Join-Path -Path $cosmosEmulatorPath -ChildPath "Microsoft.Azure.Cosmos.Emulator.exe")
Start "https://localhost:8081/_explorer/index.html"
Write-Output "CosmosDB should be open with the key showing"
}
if (-not $sourceUsesEmulator) {
Start "https://portal.azure.com/#blade/HubsExtension/BrowseResource/resourceType/Microsoft.DocumentDb%2FdatabaseAccounts"
Write-Output "Please navigate to the CosmosDB resource and open Keys under settings and select Read-only keys"
}
$sourceUrl = Read-Host -Prompt "Enter the source Cosmos URL"
$sourceKey = Read-Host -Prompt "Enter the source Cosmos key" -AsSecureString
$sourceDatabaseName = Read-Host -Prompt "Enter the source database name"
$sourceContainerName = Read-Host -Prompt "Enter the source container name"
# Work-around
$sourceUrl = $sourceUrl.Replace("https://", "")
$sourcePort = $sourceUrl.split(':')[1].Replace('/', '')
$sourceUrl = $sourceUrl.Replace("https://", "").split(':')[0]
Write-Output ''
$destinationUsesEmulator = (-not ((Read-Host -Prompt "Use Azure Cosmos Emulator for destination? [Y/n]") -like "y"))
if ($sourceUsesEmulator -ne $destinationUsesEmulator) {
if ($destinationUsesEmulator) {
$cosmosEmulatorPath = Get-Package | Where-Object{ $_.Name -like "Azure Cosmos DB Emulator" } | % { $_.Source }
if ([string]::IsNullOrEmpty($cosmosEmulatorPath)) {
Start "https://aka.ms/cosmosdb-emulator"
}
while ([string]::IsNullOrEmpty($cosmosEmulatorPath)) {
if ((Read-Host -Prompt "Has Azure Cosmos Emulator been downloaded and installed? [Press enter to check or y to skip]") -like "y") {
break
}
$cosmosEmulatorPath = Get-Package | Where-Object{ $_.Name -like "*Cosmos*" } | % { $_.Source }
}
Start -FilePath (Join-Path -Path $cosmosEmulatorPath -ChildPath "Microsoft.Azure.Cosmos.Emulator.exe")
Start "https://localhost:8081/_explorer/index.html"
Write-Output "CosmosDB should be open with the key showing"
}
if (-not $destinationUsesEmulator) {
Start "https://portal.azure.com/#blade/HubsExtension/BrowseResource/resourceType/Microsoft.DocumentDb%2FdatabaseAccounts"
Write-Output "Please navigate to the CosmosDB resource and open Keys under settings and select Read-only keys"
}
}
if ($sourceUsesEmulator -eq $destinationUsesEmulator) {
$destinationUrl = Read-Host -Prompt "Enter the destination Cosmos URL [leave blank to use source URL - $sourceUrl]"
if ([string]::IsNullOrEmpty($destinationUrl)) {
$destinationUrl = $sourceUrl
}
if ($sourceUrl -eq $destinationUrl) {
$destinationKey = Read-Host -Prompt "Enter the destination Cosmos key [leave blank to use source key - ********]" -AsSecureString
if ([string]::IsNullOrEmpty($destinationKey)) {
$destinationKey = $sourceKey
}
} else {
$destinationKey = Read-Host -Prompt "Enter the destination Cosmos key" -AsSecureString
}
} else {
$destinationUrl = Read-Host -Prompt "Enter the destination Cosmos URL"
$destinationKey = Read-Host -Prompt "Enter the destination Cosmos key" -AsSecureString
}
$destinationDatabaseName = Read-Host -Prompt "Enter the destination database name"
$destinationContainerName = Read-Host -Prompt "Enter the destination container name"
# Work-around
$destinationUrl = $destinationUrl.Replace("https://", "")
$destinationPort = $destinationUrl.split(':')[1].Replace('/', '')
$destinationUrl = $destinationUrl.Replace("https://", "").split(':')[0]
Write-Output ''
$overwritePartitionKey = Read-Host -Prompt "Should the partition keys in the destination collection be overwritten? [y/N]"
$overwritePartitionKeyIfMissing = $null
if ($overwritePartitionKey -ne 'y') {
$overwritePartitionKeyIfMissing = Read-Host -Prompt "Should the partition keys in the destination collection be written when missing? [y/N]"
}
$migrateMissingPartitionKeyAlgorithm = $null
if ($overwritePartitionKey -eq 'y' -Or $overwritePartitionKeyIfMissing -eq 'y') {
Write-Output 'Valid values include: '
Write-Output ' o $doc.fieldA - typical path that drills into objects'
Write-Output ''
Write-Output ' o $id - id of document (you normally dont want this)'
Write-Output ' o $sourcePartitionValue - represents the source tables partition key - value'
Write-Output ' o $destinationPartitionValue - represents the destination tables partition key - value'
Write-Output ''
Write-Output ' o [string]$doc.fieldA + "." + [string]$doc.fieldB - concatinates two objects with a period between them'
Write-Output ' o $doc.fieldA.fieldArrayB[5].fieldC - complex path getting value within object'
Write-Output ''
Write-Output 'Note: If the syntax fails, `@sourcePartitionKey + @destinationPartitionKey` will be used'
Write-Output ''
Write-Output 'Please list the fields (remember the $doc.var prefix) to be concatinated together'
$migrateMissingPartitionKeyAlgorithm = Read-Host -Prompt " for populating the partition key [leave blank to use @sourcePartitionKey]"
if ([string]::IsNullOrEmpty($migrateMissingPartionKeyAlgorithm)) {
$migrateMissingPartionKeyAlgorithmn = '@sourcePartitionKey'
}
}
Write-Output ''
$backoffPolicy = New-CosmosDbBackoffPolicy -MaxRetries 20
try {
$primarySourceKey = ConvertTo-SecureString -String $sourceKey.Trim() -AsPlainText -Force
} catch {
$primarySourceKey = $sourceKey
}
if ($sourceUsesEmulator -eq $true) {
$sourceCosmosDbContext = New-CosmosDbContext -Emulator -Database $sourceDatabaseName -URI $sourceUrl -PORT $sourcePort -Key $primarySourceKey -BackoffPolicy $backoffPolicy
} else {
$sourceCosmosDbContext = New-CosmosDbContext -Database $sourceDatabaseName -URI $sourceUrl -PORT $sourcePort -Key $primarySourceKey -BackoffPolicy $backoffPolicy
}
$sourceCollection = Get-CosmosDbCollection -Context $sourceCosmosDbContext -Id $sourceContainerName
$sourceCollectionId = Get-CosmosDbCollectionResourcePath -Database $sourceDatabaseName -Id $sourceContainerName
$sourcePartitionKey = ($sourceCollection | ConvertTo-Json | ConvertFrom-Json | % { $_.partitionKey.paths[0] }).TrimStart('/')
Write-Output ''
try {
$primaryDestinationKey = ConvertTo-SecureString -String $destinationKey.Trim() -AsPlainText -Force
} catch {
$primaryDestinationKey = $destinationKey
}
if ($destinationUsesEmulator -eq $true) {
$destinationCosmosDbContext = New-CosmosDbContext -Emulator -Database $destinationDatabaseName -URI $destinationUrl -PORT $destinationPort -Key $primaryDestinationKey -BackoffPolicy $backoffPolicy
} else {
$destinationCosmosDbContext = New-CosmosDbContext -Database $destinationDatabaseName -URI $destinationUrl -PORT $destinationPort -Key $primaryDestinationKey -BackoffPolicy $backoffPolicy
}
$destinationCollection = Get-CosmosDbCollection -Context $destinationCosmosDbContext -Id $destinationContainerName
$destinationCollectionId = Get-CosmosDbCollectionResourcePath -Database $destinationDatabaseName -Id $destinationContainerName
$destinationPartitionKey = ($destinationCollection | ConvertTo-Json | ConvertFrom-Json | % { $_.partitionKey.paths[0] }).TrimStart('/')
Write-Output ''
$testDestinationDocuments = Get-CosmosDbDocument -Context $sourceCosmosDbContext -CollectionId $sourceContainerName -MaxItemCount 3 -ResponseHeader ([ref] $ResponseHeader)
$isDestinationContainerEmpty = (-Not $documents -Or $documents.Length -lt 2)
$overrideWriteDestinationNonEmptyContainer = $false
if (-not $isDestinationContainerEmpty) {
Write-Output "Destination container is not empty!"
Write-Output " Writing to a non-empty container is forbidden..."
Write-Output ""
Write-Output "Note: You may override this by entering: Append_To_NonEmpty_$destinationContainerName"
Write-Output ""
Write-Output "If you continue, writing to the destination will not occur."
$watchLog = Read-Host -Prompt " Would you like to continue and watch console logging of the source documents? [y/N]"
if ($watchLog -like "Append_To_NonEmpty_$destinationContainerName") {
$overrideWriteDestinationNonEmptyContainer = $true
} elseif ($watchLog -like 'y') {
exit
}
}}
$documentsPerRequest = 20
$continuationToken = $null
$documents = $null
do {
$responseHeader = $null
$getCosmosDbDocumentParameters = @{
Context = $sourceCosmosDbContext
CollectionId = $sourceContainerName
MaxItemCount = $documentsPerRequest
ResponseHeader = ([ref] $responseHeader)
}
if ($continuationToken) {
$getCosmosDbDocumentParameters.ContinuationToken = $continuationToken
}
$documents = Get-CosmosDbDocument @getCosmosDbDocumentParameters
$continuationToken = Get-CosmosDbContinuationToken -ResponseHeader $responseHeader
$documents | Foreach-Object {
$docProperties = $_ | Get-Member -MemberType NoteProperty | ForEach-Object { $_.Name }
$doc = $_ | Select-Object -Property $docProperties | ConvertTo-Json | ConvertFrom-Json
$id = $doc.id
$sourcePartitionValue = Invoke-Expression "`$doc.$sourcePartitionKey"
$destinationPartitionValue = Invoke-Expression "`$doc.$destinationPartitionKey"
$newPartitonValue = $destinationPartitionValue
if ($overwritePartitionKey -eq 'y' -Or (-Not $partitonValue -And $overwritePartitionKeyIfMissing -eq 'y')) {
try {
$newPartitonValue = Invoke-Expression "$migrateMissingPartitionKeyAlgorithm"
} catch {
$newPartitonValue = [string]$sourcePartitionValue + [string]$destinationPartitionValue
Write-Output "'" + $migrateMissingPartitionKeyAlgorithm + "' did not work... Error: " + $_
}
if (!$doc.PSobject.Properties.Name.Contains($destinationPartitionKey)) {
$doc | Add-Member -MemberType NoteProperty -Name $destinationPartitionKey -Value $null
}
Invoke-Expression "`$doc.$destinationPartitionKey = `$newPartitonValue"
}
if ($isDestinationContainerEmpty) {
New-CosmosDbDocument -Context $destinationCosmosDbContext -CollectionId $destinationContainerName -DocumentBody ($doc | ConvertTo-Json) -PartitionKey $newPartitonValue
} elseif ($overrideWriteDestinationNonEmptyContainer) {
# upcert incase the document already exists and just needs updated
New-CosmosDbDocument -Context $destinationCosmosDbContext -CollectionId $destinationContainerName -DocumentBody ($doc | ConvertTo-Json) -PartitionKey $newPartitonValue -Upsert
} else {
Write-Output 'destination collection is not empty - no write performed'
}
Write-Output $doc
}
} while (-not [System.String]::IsNullOrEmpty($continuationToken))
Awesome stuff. Should probably create a samples folder and add these as .ps1 files. Can then link to them in the README.MD.
Can do. I will try to put both https://github.com/PlagueHO/CosmosDB/issues/275 and this into the same PR when I get the time. And the example could definitely use some cleaning up. Thank you for the approval!
Just an FYI that I am still planning on getting this done in a future weekend
$documents = Get-CosmosDbDocument @getCosmosDbDocumentParameters
$continuationToken = Get-CosmosDbContinuationToken -ResponseHeader $responseHeader
$documents | Foreach-Object {
$docProperties = $_ | Get-Member -MemberType NoteProperty | ForEach-Object { $_.Name }
$doc = $_ | Select-Object -Property $docProperties | ConvertTo-Json | ConvertFrom-Json
I finally found the missing piece! The key is to only consider NoteProperty type members, which filters methods, aliases, and internal fields. I couldn't figure out why extra fields were being appended to my JSON object. Powershell does not make this intuitive. But finally got it!
It is wise to note that ConvertTo-Json should also include Depth parameter, otherwise you can easily lose some nested properties that are deeper that 2 levels (right now in my sample doc I have 5).
