Inserting lots of rows into SQL with a Logic App and a Stored Procedure

Inserting lots of rows into SQL with a Logic App and a Stored Procedure

When you are working with API’s and Logic Apps and there is lots of rows of data involved you will sometimes come up with the following problems:

  1. An API often pages the data once you go beyond a certain number of records
  2. When you want to insert lots of rows with a Logic App into SQL you will usually have a loop which iterates over a dataset and does inserts
    1. This takes a long time to execute
    2. There is a cost implication to your implementation when you pay for each action

I recently had a scenario in this space and used quite a cool approach to solve the problem which I wanted to share.

Scenario

The scenario I had started in Shopify. When I add products & collections to my online store in Shopify I wanted to have a daily extract from Shopify to synchronise these new product/collections to my Azure SQL database which I use for reporting with Power BI.

To achieve this I would have a Logic App with a nightly trigger which would take the following actions:

  • Clean the table of which product is in which collection
  • Extract all products in collections via the Shopify API
  • Insert them all into the SQL table

The end result is I have a table which has all of the products in each collection listed for my analysis.

At a high level the scenario looks like the below diagram:

Implementation

As I mentioned above the problem is two folded here, when we query Shopify there many be thousands of products so we need to use a paging approach to query their API, secondly I want to insert into SQL in batches to try to minimise the number of action calls on SQL to improve performance and reduce cost.

Lets look at how I did this.

Paging API calls to Shopify

When it comes to the Shopify API you are able to execute a GET operation against the collection and it will return the products within it. If you have lots of products you can get them in pages. I chose to get 250 per time and you need to pass a page index to the API as a query parameter. The below picture shows you what a call to Shopify would look like with the paging parameters set.

Once I can make this single call I can then use a loop around the call to Shopify, but before I do this I need to know how many pages there are. I can do this by executing a GET against the collections API with the count extension on the url. This will return me the number of products in collections. You can see this below.

From the response I can parse the count and then I would set a variable which is the number of pages which I will work out with a calculation of dividing the number of products by the number of products I will get per page. I will also add 1 to this so I get 1 more page than the count incase the division is not a whole number. The calculation is shown below.

add(div(body(‘Parse_JSON_-_Count’)?[‘count’], 250),1)

Now I know the number of pages I can implement the loop where I will increment the page index each time until we have matched the number of pages. Within the loop we will get the next page of data from the API as shown in the picture below.

SQL Json Insert

It would be possible to just call the insert action for SQL in the logic app but if there are say 10000 products then the loop will do 10000 iterations which will take quite a while to run and also there is a cost associated with that. I wanted to look at options for inserting the data in batches. If I could insert the entire page returned from the API as a batch then with my 250 records at a time I could reduce the 10000 iterations down to 40. That should be a lot less time and a much lower cost.

To do this I developed a stored procedure where I passed the entire JSON string from the API response to the stored procedure as an NVARCHAR(max) parameter. In the stored procedure I was fortunate that the format of the json in this case was very table/row like making it easy to do this insert. I used SQL’s OPENJSON feature and was able to insert the entire page of data from the API in a simple insert statement as you can see in the SQL below.

Summary

Once it was all put together I was able to run my Logic App to refresh my SQL database each night and the process took 10 seconds to copy across 2500 records. This took 10 iterations of the loop.

That’s a nice and easy to support and run Logic App which does a nice job in this case.

A lap around Azure Functions, go serverless!

A lap around Azure Functions, go serverless!

Serverless is hot and happening. Hence, it is not a buzzword, but a new interesting part of Computer Science, which is amazing and also a driver of the second machine age, which we are currently experiencing. I read two books sequentially recently: Computer Science Distilled and the Second Machine Age.

The first book dealt with the concepts of Computer Science. And few aspects in it caught my attention like breaking a problem into smaller pieces. Hence, in Azure I could use functions to solve partial of a complete problem or process parts of a large workload. The second book discusses the second machine age around automation, robotics, artificial intelligence and so on. And little repetitive tasks can be build using Functions. Azure Functions to be precise that can automate those little tasks. Thus, why not consolidate my little research of the current state of Azure Functions into a blog post with the context of both books in the back of my mind.

Serverless

Serverless computing is a reality and Microsoft Azure provides several platform services that can be provisioned dynamically. Resources are allocated without you worrying about scale, availability and security. And the beauty of it all is you only pay what you use.

Azure Functions is one of Microsoft’s serverless capabilities in Azure. Functions enable you to run pieces of code in Azure. Cool eh! And can be run independently, in orchestration or flow (durable functions), or as a part of a Logic App definition or Microsoft Flow.

You provision a Function App, which acts as a container for one or more functions. Subsequently, either attach a price plan to it, when you want share resources with other services like web app or you choose a consumption plan (pay as you go).

Finally, you have the function app available and you can start adding functions to them. Either using Visual Studio that has templates for building a function or you use the Azure Portal (Browser). Both provide features to build and test your function. However, Visual Studio will deliver intellisense and debugging features to you.

Function Types

Functions can be build using your language of choice like C#, F#, JavaScript, or Node.js. Furthermore, there are several types of functions you can build such as a WebHook + API function or a trigger based function. The latter can be used to integrate with the following Azure Services and SaaS solutions :

  • Cosmos DB
  • Event Hubs
  • Mobile Apps (tables)
  • Notification Hubs
  • Service Bus (queues and topics)
  • Storage (blob, queues, and tables)
  • GitHub (webhooks)
  • On-premises (using Service Bus)
  • Twilio (SMS messages)

The integration is based upon a binding and trigger, key concepts with Azure Functions. Bindings provide a way to connect to in- and outputs of earlier mentioned services and solutions, see Azure Functions triggers and bindings concepts.

WebHook + API function

A popular quick start template for Azure Functions is WebHook + API function. This type of function is supported through the HTTP/WebHook binding and enables you to build autonomous functions that can be (re)used is various types of applications like a Logic App.

After provisioning a Function App you can add a function easily. As shown below you can select a premade function, choose CSharp and click Create this function.

A function named HttpTriggerCSharp1 will be made available to you. The sample is easy to experiment with. I changed the given function to something new like the screenshot below.

And now it gets interesting. You can click Get Function URL as the function is publically accessible that is if you know the function key. By clicking the Get Function URL you’ll receive an URL that looks like this:

https://myfunctioncollection.azurewebsites.net/api/HttpTriggerCSharp1?code=iaMsbyhujlIjQhR4elcJKcCDnlYoyYUZv4QP9Odbs4nEZQsBtgzN7Q==

And the code resembles the default function key, which you can change through the Manage pane in the Function App blade.

Since your function is accessible you can call it using for instance postman.

The screenshot above shows an example of a call to the function endpoint . The request includes the function key (code). However, a call like above might not be as secure as you need. Hence, you can secure the function endpoint by using API Management Service in Azure. See Using API Management to protect Azure Functions (Middleware Friday) blog post. The post explains how to do that and it’s more secure!

Integrate and Monitor

You can bind Azure Storage as an extra output channel for a function. Through the Integrate pane I can add an extra output to the function. Configure the new output by choosing Azure Blob Storage, set Storage Account Connection and specify the path.

Next you have to update the Function signature with outputBlob parameter and implement the outputBlob.

Finally, you can monitor your functions through the Monitor pane, which provides you some basic insights (logs). For a more richer monitoring experience, including live metrics and custom queries, Microsoft recommends using Azure Application Insights. See also Monitoring Azure Functions.

Visual Studio Experience

Azure Functions can be build with Visual Studio. Now the templates are now available after a default installation of Visual Studio. You need download them.  Visual Studio 2017 the templates for Azure Functions are available on the marketplace. For Visual Studio 2015 read this blog post, which includes the steps I did for my Visual Studio 2015 installation.

Once the templates are available in your Visual Studio version (2015 or 2017) you can create a FunctionApp project. Within the created FunctionApp project you can add functions. Right click the project and select Add –> New Azure Function. Now you can choose what type of function you can build. You will have a similar experience as with the portal.

For instance you can create a ServiceBusTrigger Function (WindSpeedToBeaufort), which will be triggered once a message arrives on a queue (myqueue).

As a result you will see the following code once you hit Create:

Now let’s work on the function so it will resemble the diagram below:

To modify the function that does the above the necessary code is shown below:

And the json.setting needs to be renamed to local.settings.json, the function.json needs modification to:

The connection string is moved to the local.settings.json as depicted below:

Most of all this change is important, otherwise you will run into errors.

Debugging with Visual Studio

Visual Studio provides the capability to debug your custom function. Compile and start a debug instance. A command line dialog box will appear and your function is running (i.e. hosted and running).

To debug our function in this blog a message is sent to myqueue using the ServiceBus360 service.

Once the message arrives at the queue it will trigger the function. Hence, the debugging can start on the position in the code, where a breakpoint has been set.

And the result of execution will be visible in the command line dialog box:

In conclusion this is the debugger experience you will have with Visual Studio. Combined with having intellisense while developing your function.

Deployment

You have build and tested your function to your satisfaction in Visual Studio. Now it’s time to deploy it to Azure, therefore you right click the project and choose publish. A dialog will appear and you can choose AppService. Subsequently, if you are logged in with your Azure Credentials you will see based on the subscription one or more resource groups.

You can click OK and proceed with next steps to publish your function to the desired resource group –> function app. However, this will in the end not work!

As a result you will need a workaround as explained in Publishing a .NET class library as a Function App at least that’s what I found online. However, I as able to deploy it. However, I stumbled on another error in the portal:

Error:

Function ($WindSpeedToBeaufort) Error: Microsoft.Azure.WebJobs.Host: Error indexing method ‘Functions.WindSpeedToBeaufort’. Microsoft.Azure.WebJobs.ServiceBus: Microsoft Azure WebJobs SDK ServiceBus connection string ‘AzureWebJobsconnection‘ is missing or empty.

Hence, not a truly positive experience. In the end it’s missing a setting i.e. application setting of the Function App.

Anyways, another walkaround is to create add a new function to existing function app. Choose ServiceBusTrigger template, create it and finally copy the code from the local project into the template over the existing code. In conclusion this works as now you see a setting for the Service Bus connection string in the application setting and the reference in the function.json file.

Considerations

There are some considerations around Azure Function you need to be aware of. First of all the cost of execution, which determines whether you will choose a consumption or app plan. See Function Pricing and use the calculator to have a better indication of costs. Also consider some of the best practices around functions. These practices are:

  • Azure Functions should do just one task,
  • finish as quickly as possible,
  • be stateless
  • and be idempotent.

See also Optimize the performance and reliability of Azure Functions.

Finally, be aware of the fact that some features of Azure Functions are still preview like Proxies, Slots and the Visual Studio Tools.

Resources

This blog contains several links to some resources you might like to explore. An excellent starting point for a researching Azure functions is https://github.com/Azure/Azure-Functions. And if you are interested how Functions can play a role in Logic Apps have a look at this blog post: Building sentiment analysis solution with Logic Apps.

Explore Azure Functions, go serverless!

Cheers,

Steef-Jan

Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, health care, agriculture, (local) government, bio-sciences, retail, travel and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 7 years. View all posts by Steef-Jan Wiggers

TUGAIT 2017: Integration and Logic Apps

TUGAIT 2017: Integration and Logic Apps

Two more weeks and I will, once again, return to Lisbon for the TUGAIT event. Last year during the 2016 edition Azure MVP’s Sandro, Nino and myself (the three integration animals, see picture below) did a workshop and several sessions on integration i.e. BizTalk, Open Source connectivity (GrabCaster), and Hybrid  And this was no doubt a successful event and the debut of the integration track. Hence this year the track will active again!

TUGAIT 2017

The May 18th until the 20th, a variety of speakers will present on a myriad set of technologies like Xamarin, Angular, DataScience, Agile, Scrum, DevOps, Integration, DotNet, SQL Server, SharePoint, Office365, Azure and IoT.

The integration track on Saturday the 2oth will be packed with session by Azure MVP’s Sandro, Nino, Eldert, Riccardo, Tomasso and myself.

Integration in 2017: Logic Apps

Microsoft has made a leap forward with several of there Azure Services including Logic Apps. A Service that went GA end of July 2016 and evolved rapidly to maturity. Already we see a steady growing adoption of this service within enterprises. Logic Apps is not the replacement of BizTalk, its Microsoft answer to solve integration challenges in the Cloud. The Logic App connectors provide connectivity to other Azure Services and several SaaS solutions like MailChip, SalesForce and CRM online. At TUGAIT 2017 in the Integration Track you will learn more about Logic Apps.

Why attend?

“Nice there’s an integration track, but what if I like to learn about other technologies (too)?

Well you are at the right place as on the 18th there’s a full day of workshops you can choose from. On Friday there are 5 parallel tracks from which you can pick and choose. The same accounts for Saturday, including the integration track!

The event is located in one of the most beautiful, cultural cities of Portugal. It’s three days packed with content, stellar speakers and community leaders you can listen to and grab to ask questions.

Registration

Registration is a few euro’s or even free if you do require lunch (the fee is there to reduce waste and prevent having an abundance of food).

You can register here and I will see you there in Lisbon!

Cheers,

Steef-Jan

Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, health care, agriculture, (local) government, bio-sciences, retail, travel and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 6 years. View all posts by Steef-Jan Wiggers

TUGAIT 2017: Integration and Logic Apps

TUGAIT 2017: Integration and Logic Apps

Two more weeks and I will, once again, return to Lisbon for the TUGAIT event. Last year during the 2016 edition Azure MVP’s Sandro, Nino and myself (the three integration animals, see picture below) did a workshop and several sessions on integration i.e. BizTalk, Open Source connectivity (GrabCaster), and Hybrid  And this was no doubt a successful event and the debut of the integration track. Hence this year the track will active again!

TUGAIT 2017

The May 18th until the 20th, a variety of speakers will present on a myriad set of technologies like Xamarin, Angular, DataScience, Agile, Scrum, DevOps, Integration, DotNet, SQL Server, SharePoint, Office365, Azure and IoT.

The integration track on Saturday the 2oth will be packed with session by Azure MVP’s Sandro, Nino, Eldert, Riccardo, Tomasso and myself.

Integration in 2017: Logic Apps

Microsoft has made a leap forward with several of there Azure Services including Logic Apps. A Service that went GA end of July 2016 and evolved rapidly to maturity. Already we see a steady growing adoption of this service within enterprises. Logic Apps is not the replacement of BizTalk, its Microsoft answer to solve integration challenges in the Cloud. The Logic App connectors provide connectivity to other Azure Services and several SaaS solutions like MailChip, SalesForce and CRM online. At TUGAIT 2017 in the Integration Track you will learn more about Logic Apps.

Why attend?

“Nice there’s an integration track, but what if I like to learn about other technologies (too)?

Well you are at the right place as on the 18th there’s a full day of workshops you can choose from. On Friday there are 5 parallel tracks from which you can pick and choose. The same accounts for Saturday, including the integration track!

The event is located in one of the most beautiful, cultural cities of Portugal. It’s three days packed with content, stellar speakers and community leaders you can listen to and grab to ask questions.

Registration

Registration is a few euro’s or even free if you do require lunch (the fee is there to reduce waste and prevent having an abundance of food).

You can register here and I will see you there in Lisbon!

Cheers,

Steef-Jan

Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, health care, agriculture, (local) government, bio-sciences, retail, travel and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 6 years. View all posts by Steef-Jan Wiggers