Getting my Twitter Data via Azure Logic Apps

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.

designermode

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.

newtweet

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.

APIkey

Simply state what you want analysed.

detectAPI

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.

{
“author”: “Test”,
“location”: “Minneapolis, MN”,
“sentiment”: 0.5,
“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.

GreenTciks

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

tableoutput

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).

powerbimap

So while not exactly a true database related post hopefully still an enjoyable read.

4 thoughts on “Getting my Twitter Data via Azure Logic Apps

  1. Pingback: Dew Drop - May 3, 2018 (#2717) - Morning Dew

  2. Hello. This is a nice post. However, I have a query. In the above template, you have manually typed the value “#Azure, #Microsoft” for Search Text when a new tweet is posted. It works well. But I want to know if it is possible to add the value for search text dynamically.
    I have an asp.net web form, the user will enter search text of his choice, and when he clicks on Add button, the new workflow is created.
    Is it possible by any chance?
    Any help would be appreciated. Thanks in advance!

    Liked by 1 person

    • How about you write the search terms (hashtags etc) into a SQL table . then get the handles from that table?

      public async Task ParseTweet(string entireTweet)
      {
      // Convert JSON to dynamic C# object
      tweetObj = JObject.Parse(entireTweet);
      tweet = tweetObj;

      //Connect to Azure SQL Database & bring in Twitter Handles & IDs
      string twitterHandles =
      ExecuteSqlQuery(“select value FROM pbist_twitter.configuration where name = \’twitterHandle\'”, “value”);
      string twitterHandleId =
      ExecuteSqlQuery(“select value FROM pbist_twitter.configuration where name = \’twitterHandleId\'”,
      “value”);

      // Split out all the handles & create dictionary
      String[] handle = null;
      String[] handleId = null;
      var dictionary = new Dictionary();

      Liked by 1 person

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 )

Connecting to %s