This article configures Azure Automation for Analyzing Twitter sentiments using Azure Logic Apps and stores them into Azure SQL Database.
Introduction
As a technical blogger, I want to analyze the tweets for a specific hashtag, perform their sentiment analysis, and store them into Azure SQL Database to prepare visualization or further analysis. It is not feasible to store the Tweets manually in a database. Azure Logic Apps is an excellent tool for analyzing data for social platforms such as Twitter, LinkedIn, Facebook, etc. It allows automating design workflows using triggers, connectors.
Twitter is a social platform with allows businesses to reach a broad audience without any intermediaries. The sentiment analysis is a crucial element of social media networking, and it monitors emotions on social content. It involves the identification and classification of text such as positive, negative, or neutral.
For example, in the following screenshot, you can see a tweet sentiment as positive. Reference: MonekyLearn
Similarly, here, we have a negative sentiment text.
In the previous articles, we explored various practical usage of Azure Logic Apps. You can refer to those in TOC. This article introduces new tweets from the Twitter application and imports them into the SQL database with their sentiment score.
Create a logic app and connect it with a Twitter connector
We can use the Twitter connector for tasks such as post tweets, get tweets from the timeline, get a list of your followers. You can look tweets for a specific keyword or Text. Once the azure logic app detects a new tweet, it initiates a workflow based on your configuration.
Navigate to the Azure portal, go to logic apps and create a new logic app. Enter the resource group, logic app name along with the azure region.
Add a trigger for Twitter in Azure logic apps
In the logic app designer, you get a list of common triggers. Click on the trigger – When a new tweet is posted.
It opens logic app designer with Twitter configuration. Here, specify a connection name and authentication type – Use default Shared application.
Click on Sign in.
It asks for permissions for Microsoft Azure Logic apps to access your Twitter account.
Specify your Twitter credentials and click on Authorize App. You get a list of items you can perform using the logic app twitter connector.
- View Tweets from timeline, list and collections
- View and update Twitter profile, account settings
- Get a list of your followers, block
- Like, Share, Post, Delete Tweets
- Send a direct message and read, delete the direct messages
It connects with your Twitter account, and you see the connection name. For example, in my demo, it uses [rajendratwitter] connection name.
Click on Continue. Now, you need to define your search criteria. For example, specify the keyword, hashtag, or username in the search text box.
Similarly, specify the frequency of these checks. For example, you want to check for Tweets satisfying your criteria ( search Text) every 3 minutes, 10 minutes.
You can add additional configuration options from the add new parameter section. Here, I added the time zone and specified UTC+5.30, i.e. IST time zone. I specified #AUSvsIND a trending hashtag in the search text while writing this article for a demonstration purpose.
Perform sentiment analysis
As stated before, we can perform sentiment analysis using integration with the logic app. For this purpose, we use Azure Cognitive Services for text analysis.
Reference Image: Microsoft Industry Blogs
The Azure Cognitive Service uses text analytics API for natural language processing with the following components.
- Sentiment analysis
- Key phrase extraction
- Language detection
It uses a machine-learning algorithm for generating a sentiment score between 0 and 1.
- Score 1 indicates positive Sentiment. A score closer to 1 has positive Sentiment
- Score 0 indicates negative Sentiment. A score closer to 0 has negative Sentiment
For this purpose, we need to create a cognitive service account. In the Azure portal, search for cognitive services and create cognitive services.
It has a variety of services available, therefore, search for Text Analytics in the market place.
Click on Create. On the next page, select the subscription, resource group, instance region, name, and pricing tier. There are different pricing tiers available depending upon the number of transactions. For the demo purpose, I use Free F0 ( 5K transaction per 30 days). Refer to Microsoft documents for comparing these pricing tiers for Azure Cognitive Services.
It builds the resources and opens a quick start page.
We need an API key for authentication azure logic app with cognitive service. Click on the Keys and Endpoint from the left-hand menu and copy the keys from the page. It gives two keys in the console; however, we require only one key for the API call.
On the console page, you also get an endpoint URL referencing your cognitive service account.
Add Text Analytics in Azure Logic app
Now, go back to the azure logic app, click on the New step and search for Text Analytics.
In the Text Analytics, select an action – Detect Sentiment.
In the Text Analytics, enter a connection name and paste the account key, Site URL from the Keys and Endpoint page.
- Account key: Enter key1 that we copied from Azure Portal
- Site URL: Enter the cognitive service endpoint
It creates a connection for Azure cognitive service. In the detect Sentiment, click on Add new parameter and select Text.
For the Text, you can choose dynamic content. For example, here, we specify Tweet Text because we want to have sentiment analysis on tweets. However, you can modify it tweeted by, media URL items, location, etc.
Insert Tweets data into Azure SQL Database
In this step, we want the tweets data insertion in the Azure SQL Database. For this purpose, I created the following table in the [labazuresql] database. This table stores the following information.
- Tweet posting date
- Sentiment score
- Tweet author
- User Location
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Tweetsanalysisdata ( id int PRIMARY KEY IDENTITY, CreatedDate datetime, TweetText varchar(512), Sentiment Float NULL, TwittedBy varchar(512), Screenname varchar(100), Retweetcount int, WhoIsTheAuthor VARCHAR (200) , Location VARCHAR (100) , ); |
Now, click on Add a new step in the logic app and select SQL Server connector. In the actions, select Insert row ( V2).
In the Insert row(V2) component, select the server, database name, and destination table from the drop-down list.
We need to map input data and Azure SQL database table columns. Click on the new parameter and select the columns. Now, drag the dynamic content for the table columns as below.
- CreateDate: Created at
- TweetText: Tweet Text
- Sentiment: Score
- TwittedBy: Tweeted By
- Screenname: Original tweet user name
- Retweetcount: Retweet count
- WhoIsTheAuthor: TweetedBy
- Location: Location
Save the logic apps and click on Run. It detects a new tweet as per your configuration, calculates sentiment score using Azure Cognitive Service and inserts data into Azure SQL Database.
You can query the azure database table and perform data analysis. In the below screenshot, we sorted data in the descending order of retweet count.
Add a conditional operation in the logic app
Suppose you want an email notification whenever someone posts a new tweet with a sentiment score of more than 0.5. For this requirement, search for the Control connector.
In the actions, add conditional logic.
The conditional logic is equivalent to the IF ELSE block in a T-SQL. Specify your condition as below.
- If Sentiment is greater than 0.5:
- If True, send an email with the tweet text. For the email, configure a send an email(V2) task from outlook.com connector
- If false, no action
Rerun the logic app, and you start receiving positive sentiment emails, as shown below.
I got the following positive sentiment Tweet in my Inbox.
Conclusion
This article performed tweets sentiment analysis and stored them into Azure SQL Database using the Azure logic apps and Azure Cognitive Service. Similarly, you can automate tasks such as post a new tweet, retweet, tweet analysis using logic apps.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023