Invoking a Stored Procedure from a partitioned CosmosDB collection from Logic Apps


I struggled a little to make that work, so I thought I would share the learning in order to accelerate your future endeavour.

I was looking at a way to populate a CosmosDB quickly with random data.

Stored Procedures came to mind since they would skip client-server latency.  We can call a stored procedure creating hundreds of documents with random data.

Each Stored Procedure runs in a partition, so we need something external to the stored procedure to loop and decide of the partition key.

Enter Logic Apps:  cheap to run and quick to setup.

Stored Procedure

Something important to realize is that some portal features aren’t supported when we deal with a partitioned collection.

One of them is to update the content of a stored procedure (same thing for triggers).  We therefore need to delete it and re-create it.

Here is the stored procedure we used:

function createRecords(recordCount) {
    var context = getContext();
    var collection = context.getCollection();
    var createdIds = [];

    for (i = 0; i < recordCount; i++) {
        var documentToCreate = { part: "abc", name: "sample" + i };
        var accepted = collection.createDocument(
            collection.getSelfLink(),
            documentToCreate,
            function (err, documentCreated) {
                if (err) {
                    throw new Error('Error' + err.message);
                }
                else {
                    createdIds.push(documentCreated.id);
                }
            });

        if (!accepted)
            return;
    }

    context.getResponse().setBody(createdIds)
}

We take the number of documents to create in parameter, loop & create documents.  We return the document IDs in a list in the output.

The documents we create are trivial:  no random data.

Logic App

On the canvas, let’s type Cosmos in the search box for actions.

image

Let’s choose Execute stored procedure.

We are prompted to create a new Cosmos DB connection.  We need to:

  • Type a name for the connection (purely for readability, can be anything)
  • Select an existing Cosmos DB collection

We can then pick the database ID, the collection ID & the stored procedure ID.

image

Stored Procedure parameters are expressed as a JSON array.  For instance here, we want to pass 1000 as the recordCount parameter, so we type [1000]:  no parameter name and always square brackets.

If we would run the app now we would get an error stating the operation requires the partition key.

In order to set the partition key, we need to Show advanced options.

image

In order to specify the partition key value, we simply type its value:  no square bracket, no quotes.

Now we can run the Logic App and it should execute the stored procedure and get its output in the action’s output.

Summary

Invoking a Cosmos DB stored procedure from Logic App isn’t rocket science but there are a few items to get straight in order for it to work properly.

Advertisements

One thought on “Invoking a Stored Procedure from a partitioned CosmosDB collection from Logic Apps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s