Cloud Adventures Part 3 – Staging Data Preparation – Logic App with Web API

There are many ways to take staging data and expand it for analysis.  For this blog entry, I opted to use a Logic App to expand the satellite dataset into component columns in another set of SQL Azure database tables and record the results.  To do so, I did a little research on what Logic Apps are best used for.  The list appears (to me) to be limited to repetitive events that are fairly simple.  A daily file download or email service seem to be good candidates. So, out of the box, I would not normally do a one-time load using a Logic App.  But, if the satellite dataset I generated in my earlier blog post was on-going, a Logic App might be a candidate if the dataset size was not to large.  So, I decided to move the existing data set into the new tables using a batch approach.

Github Web API code – https://github.com/ehelin/SatelliteLogicApi
NOTE:  Logic App mark up code is included in this solution

My initial thought was to use a code less Logic App and the Microsoft (MS) SQL Connector to call a stored procedure or T-SQL script to move the data over.  However, in creating the Logic App, it appeared to me that the MS SQL Connector was not available.  At the time, I tried adding a SQL connector, but it was not listed in the available control drop down in the Logic App designer.  After more digging, it seemed like this connector had been deprecated.   Given (at the time) that I thought the MS SQL Connector was no longer supported in Azure, I created a Web API application that could be called by the Logic App at a regular interval.  This Web API approach will be the focus of this blog post.  Later in the process, I learned that the MS SQL Connector may still supported.  I may explore that in a future blog post.

To get the staging data into expanded tables, I broke up the single records into the following tables based on date type (create scripts are at bottom of this post):
-SatelliteClientUpdate
-SatelliteStatusUpdate

Since this is an Web API application, we need to break our job into smaller batches.  So, to make these easier, I used this script to move the staging records into another table, but ordered by id.  This way, each time the logic application calls the Web API application, it will quickly pull the records in order in batches…we hope 🙂

truncate table dbo.updatesordered

insert into dbo.updatesordered
SELECT [id],
[type],
[data],
[created]
FROM [dbo].[UpdatesCloudTable]
order by id

Once this was done, I started running the Web API with Postman (an excellent Chrome HTTP tool – https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop?hl=en) with small batch/iteration counts to see what worked.  After confirming things worked as expected, I moved on to the Logic App.

In the Logic App Designer, I added a recurrence object and then connected it to an HTTP object.  The HTTP object is what calls my Web API application deployed as an Azure Web App.  I had hoped to run the Logic App every 20-30 seconds and had structured the batch/iteration counts to match.  However, the Logic App options open to me limited the number of calls I could make.  The options (given my budget of $100 per month on my MSDN subscription) out of the box are one call per hour.  So, I opted to have 30 iterations of 2000.  Given some independent testing I conducted with Postman and my application, this seemed to be the ultimate balance.

I fired off the Logic App and what happened is a little mind boggling.  The web application seemed to fire off multiple times before the web application shut itself off stating that I had exceeded my allotted amount of activity in that price tier.  300,000 records had been inserted.  I attempted to repeat this with Post man and the very same Azure hosted Web API application and I got the expected 60,000 expected records.  I can only conclude that the Logic apps are a little buggy 😦

So, to isolate what the issue was, I scaled back the batch/iteration counts to 20/30 and tried again. However, it appeared like my logic app was no longer functional as I tried to launch it and ‘Not Found’ appeared.  So I created a new Logic App, put in the old logic app code, and tried again with the scaled down web app and it worked as anticipated.  I redeployed the old application and tried again with Postman and the results were as expected. So, I tried the Logic App and got the same exact result.  Because the post is taking longer than it should, perhaps the Logic App is re-firing to see if it will successfully fire?  If so, it is firing before returning and not showing that in the Azure interface.  So, I made one call again with enough batch/iteration counts to get all of the data it.  The result was the same as before with multiple starts that shouldn’t occur.

Clearly Logic Apps are meant for simple and routine jobs of small size.  I had hoped to simulate the ultimate use case by firing the logic app every 15-20 seconds to complete a small portion of the load and track its effectiveness.  However, given that I can only fire the logic app once an hour without radically increasing the price tier, I am not going to be able to go further.  However, if you are willing to pay the highest tier which should give you the ability to call your Logic App at intervals up to 15 seconds (see reference #1), using a Logic App in this manner is a likely possibility.

My next post in the Logic App series will be to use a stored procedure to attempt the same load.  Stay tuned!

References
1) https://azure.microsoft.com/en-us/documentation/articles/app-service-logic-create-a-logic-app/
2) http://blogs.msdn.com/b/martinkearn/archive/2015/05/22/get-started-with-azure-logic-apps-and-your-own-api-app-in-5-minutes.aspx
3) https://github.com/domaindrivendev/Swashbuckle/issues/223 (helped to get swagger showing)
4) http://stackoverflow.com/questions/31802261/how-to-set-recurence-of-per-second-in-microsoft-azure-logic-app-using-office-365
5) https://azure.microsoft.com/en-us/pricing/details/app-service/plans/

CREATE TABLE [dbo].[SatelliteClientUpdatev3](
[id] [bigint] NULL,
[satellitename] [varchar](250) NULL,
[type] [varchar](1000) NULL,
[created] [datetime] NULL,
[onstation] [bit] NULL,
[solarpanelsdeployed] [bit] NULL,
[planetshift] [bit] NULL,
[destinationx] [decimal](18, 8) NULL,
[destinationy] [decimal](18, 8) NULL,
[inserted] [datetime] NULL
)

CREATE TABLE [dbo].[SatelliteStatusUpdatev3](
[id] [bigint] NULL,
[satellitename] [varchar](250) NULL,
[type] [varchar](1000) NULL,
[created] [datetime] NULL,
[onstation] [bit] NULL,
[solarpanelsdeployed] [bit] NULL,
[fuel] [decimal](18, 8) NULL,
[power] [decimal](18, 8) NULL,
[planetshift] [bit] NULL,
[satellitepositionx] [decimal](18, 8) NULL,
[satellitepositiony] [decimal](18, 8) NULL,
[sourcex] [decimal](18, 8) NULL,
[sourcey] [decimal](18, 8) NULL,
[destinationx] [decimal](18, 8) NULL,
[destinationy] [decimal](18, 8) NULL,
[ascentdirection] [varchar](50) NULL,
[inserted] [datetime] NULL
)

Advertisements

One thought on “Cloud Adventures Part 3 – Staging Data Preparation – Logic App with Web API

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s