As a mini project I wanted to use Azure logic apps to pull tweets from my twitter account when people were tweeting about #Azure / #Microsoft. From here I used cognitive services – sentiment analysis API which returns a numeric score between 0 and 1. Scores close to 1 indicate a positive sentiment and scores close to 0 indicate a negative sentiment. Then I wanted to put that data into an Azure SQL Database table and link it to Power BI because I wanted to see where in the world tweets were coming from and with what score.
It’s a fun post but very useful if you want to track your companies twitter feed for positive / negative feedback.
Logic apps are great my friend Paul Andrew (https://mrpaulandrew.com/) introduced them to me. They help you build, schedule, and automate processes as workflows so you can integrate apps, data, systems, and services across enterprises or organizations. It is ever growing, more and more connectors are being deployed by Microsoft, I am still waiting for a LinkedIn data connector though. There is nothing complicated with my example, you will not see custom code.
This is what my workflow looks like when in designer mode.
For the twitter data I am checking for new items every 30 seconds (see below) where then I apply the sentiment API against the tweet text and finally move it into a table within an Azure SQL Database.
When you actually setup the sentiment analysis section you will need to get an API key from https://azure.microsoft.com/en-us/try/cognitive-services/ using the cognitive services language API as shown below.
Simply state what you want analysed.
For the insert into the table I am picking up the following example data. This is what is generated by the output of the logic app.
“location”: “Minneapolis, MN”,
“tweetdesc”: “Azure.Source #azureblogfeed #Azure #AzureStack #Cloud #CloudOps #Microsoft @Azure”
So you can see the author, location, sentiment score and the actual tweet. This gets moved into a table with the following structure, obviously I have made sure the column mappings are correct between JSON and the table.
CREATE TABLE [dbo].[TweetMe]( [id] [int] IDENTITY(1,1) NOT NULL, [createdDate] [datetime2] NULL, [tweetdesc] [varchar](512) NULL, [sentiment] [float] NULL, [author] [varchar](512) NULL, [location] [varchar](128) NULL, PRIMARY KEY CLUSTERED ( [id] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
I tend to manually run the app just to make sure that it works, hopefully you will see the green ticks too.
Just a note, this workflow would fail at the insert row step when the Allow access to Azure services was set to OFF on your Azure “logical” SQL Server. I don’t think you have a choice with this setting. Its not like you can map in an IP address range for this service.
When connecting to the Azure SQL Database and issuing basic queries you will see data incoming (I removed the author column).
SELECT * from [dbo].[TweetMe] ORDER BY createdDate desc
The analysis of the text is pretty good. For example, a tweet saying: “The more I use Azure Powershell/CLi to automate infrastructure and various tasks – the more I think AzureRM is becoming outdated. #microsoft #azure #unsure” was returning a score of 0.25 whereas “We have a great partnership with #Microsoft. Thanks #Azure #cloud” was returning 0.98.
I tested this using another logic app tracking a certain company and customer complaints were given very low scores whereas happy customers given 1 – for the majority of the cases.
I am not a report designer, far from it, but connecting to Power BI is very much possible for example you can see the tweets coming in from across the globe with a sentiment score associated with it (If you hover over the bubbles).
So while not exactly a true database related post hopefully still an enjoyable read.