none
Enable upsert in CosmosDB

    Question

  • Hi there,

    I read a few things about UpsertDocumentAsync to upsert in CosmosDB doc.

    Is is possible to enable Upsert for a collection in CosmosDB. Means whenever I try to insert and if the id already exists then replace the doc with new doc.

    If this is not possible then please help me how can I achieve the following thing.

    I want to create copy task which reads parquet file from ADLS and writes it to CosmosDB. How can I achieve upsert here?Currently it gives me error - "Resource with specified id or name already exists" when I rerun the pipeline, obviously because most of the data already exists

    Since I am trying to import dimensions, I want to reload them periodically

    Thanks

    Monday, July 2, 2018 11:59 PM

All replies

  • Hi,

    Thanks for your feedback.

    We're currently working on adding 'Upsert' feature of cosmosdb into ADFv2, please have a try next week.

    Thanks for your patience.

    Tuesday, July 3, 2018 6:33 AM
  • Any update on this. How can I achieve this now
    Tuesday, July 10, 2018 9:16 PM
  • Hi,

    Our backend is in deployment stage, I'll inform you once it's available in prod.

    Thanks.

    Wednesday, July 11, 2018 2:42 AM
  • Hi,

    The 'upsert' in cosmosDB is deployed into ADFv2 now, please go to try it.

    Thanks.

    Saturday, July 21, 2018 2:00 AM
  • Hi,

    Today i try to use 'Upsert' write behavior option for CosmosDB Sink Dataset in ADF v2 and receive next error.


    Activity Result to CosmoDB failed: 'Type=Microsoft.Azure.Documents.DocumentClientException,Message=Partition range id 0 | Failed to import mini-batch. Exception was Message: {"Errors":["Conflicting request to resource has been attempted. Retry to avoid conflicts."]}
    ActivityId: b3469a6a-f595-45fc-84c1-d5aacc21c9a0, Request URI: /apps/ab34b0f8-325c-4d91-b2e6-59ed02cb1776/services/3b7ed180-834e-4438-9f0a-956883f509cc/partitions/16661145-2e77-4e6c-a687-d515b7783f4b/replicas/131770700672270468p/, RequestStats: , SDK: documentdb-dotnet-sdk/1.21.1 Host/64-bit MicrosoftWindowsNT/6.2.9200.0. Status code was 449
    ActivityId: 7090663d-56d5-41b5-b6e0-2c45ecaa3570, documentdb-dotnet-sdk/1.21.1 Host/64-bit MicrosoftWindowsNT/6.2.9200.0,Source=Microsoft.Azure.CosmosDB.BulkImport,''Type=Microsoft.Azure.Documents.RetryWithException,Message=Message: {"Errors":["Conflicting request to resource has been attempted. Retry to avoid conflicts."]}
    ActivityId: b3469a6a-f595-45fc-84c1-d5aacc21c9a0, Request URI: /apps/ab34b0f8-325c-4d91-b2e6-59ed02cb1776/services/3b7ed180-834e-4438-9f0a-956883f509cc/partitions/16661145-2e77-4e6c-a687-d515b7783f4b/replicas/131770700672270468p/, RequestStats: , SDK: documentdb-dotnet-sdk/1.21.1 Host/64-bit MicrosoftWindowsNT/6.2.9200.0,Source=Microsoft.Azure.Documents.Client,'


    Info about my workflow.

    1) I was create new collection in CosmosDB with unique key '/key1,/key2'

    2) Run Pipeline that copy data from Azure Data Lake Gen1 to new CosmosDB collection.

    3) First copy operation was successful.

    4) I was edit some data in source file to see upsert in work.

    5) Re run Pipeline and receive error from above.

    6) If i remove all data from source file and add new one then Pipeline works fine for first run. After second run i receve same error.

    Thursday, July 26, 2018 11:16 AM
  • Hi,

    This error indicates that:

    1. you have a "unique key" setting on your collection, which is not the "id" property

    2. there are 2 documents with different Ids but same unique key 

    Base on you description, I guess you don't set the "id" column when importing data from ADLS to COSMOSDB so the "id" is auto generated, so when you reimport the document a new id is generated and so was consider to be a new document but the unique key is conflicted.

    Thanks,

    Eva

    Friday, July 27, 2018 2:37 AM
  • Hi,

    This error indicates that:

    1. you have a "unique key" setting on your collection, which is not the "id" property

    2. there are 2 documents with different Ids but same unique key 

    Base on you description, I guess you don't set the "id" column when importing data from ADLS to COSMOSDB so the "id" is auto generated, so when you reimport the document a new id is generated and so was consider to be a new document but the unique key is conflicted.

    Thanks,

    Eva

    Hello,

    You are absolutely right! Thank you.
    I mark Wang Zhang answer as solution.


    Friday, July 27, 2018 11:23 AM
  • Hi Roman,

    Eva is our expert on CosmosDB, please mark Eva's reply: 

    Hi,

    This error indicates that:

    1. you have a "unique key" setting on your collection, which is not the "id" property

    2. there are 2 documents with different Ids but same unique key 

    Base on you description, I guess you don't set the "id" column when importing data from ADLS to COSMOSDB so the "id" is auto generated, so when you reimport the document a new id is generated and so was consider to be a new document but the unique key is conflicted.

    Thanks,

    Eva

    as the answer.

    Thanks. :)

    Monday, July 30, 2018 4:38 AM