Unable to update Cosmos DB documents with Hierarchical partition keys using Azure Stream Analytics job

Question

I have document in Cosmos DB with Hierarchical partition keys.
I want to patch certain property value.

But while running the job I am getting this error👇

The streaming job failed: Stream Analytics job has validation errors: Error connecting to Cosmos DB Database: Error occurred when connecting to Cosmos DB account. Error message: This sub partitioned container has more than 1 partition key path please use PartitionKeyPaths.

This is my query 👇

SELECT
'1234' AS [id],
'org1' AS [organizationId],
'job1' AS [jobId],
1 AS [status]
INTO cosmosdb2
FROM
eventhub  

Here id, organizationId and jobId are the Hierarchical partition keys and I am updating the status property.

Answer 1

Your ASA query must include all parts of the hierarchical partition keys. In Cosmos DB, if you have hierarchical partition keys (for exq;ple ["/organizationId", "/jobId"])

Depending on the specifics of your setup, using the Cosmos DB v2 connector in Azure Stream Analytics is better for complex partitioning schemes. The v2 connector has improved capabilities around partitioning and might handle hierarchical partition keys more gracefully.

Qs q reco;;endqtion, If your update logic is complex consider using Cosmos DB stored procedures to handle updates and you can trigger them from Azure Functions or another component in your architecture that receives output from the Stream Analytics job.