Azure Cosmos DB asking for partition key for stored procedure

7
votes

I am using GUID Id as my partition key and I am facing problem when I am trying to run a stored procedure. To run a store procedure I need to provide partition key ans I am not sure what value should I provide in this case? Please assist.

3
azureazure-cosmosdb

3 Answers

4
votes

If the collection the stored procedure is registered against is a single-partition collection, then the transaction is scoped to all the documents within the collection. If the collection is partitioned, then stored procedures are executed in the transaction scope of a single partition key. Each stored procedure execution must then include a partition key value corresponding to the scope the transaction must run under.

You could refer to the description above which mentioned here.

As @Rafat Sarosh said, GUID Id is not an appropriate partitioning key. Based on your situation , city may be more appropriate.You may need to adjust your database partitioning scheme because the partitioning key can not be deleted or modified after you have defined it.

I suggest you exporting your data to json file then import to a new collection which is partitioned by city via Azure Cosmos DB Data migration tool.

Hope it helps you.

Just for summary:

Issue:

Unable to provide specific partition key value when executing sql to query documents.

Solution:

1.Set EnableCrossPartitionQuery to true when executing query sql.(has performance bottleneck)

2.Consider setting a frequently queried field as a partitioning key.

0
votes

Example your partition key is /id

and your cosmos document is

{
  "id" : abcde
}

When store procedure run, you need to paste: abcde value

So if you want your store procedure running cross partition, it can't Answer from cosmos team https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/33550159-support-stored-procedure-execution-over-all-partit