Logic Apps Designer String Contains Multiple Values

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

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

Azure Automation: Export Azure SQL Database to Blob Storage in a BACPAC file

Azure Automation: Create database copies of Azure SQL Database

Azure Automation: Automate Azure SQL Database indexes and statistics maintenance

Azure Automation: Automate Pause and Resume of Azure Analysis Services

Azure Automation: Automate data loading from email attachments using Azure Logic Apps

Azure Automation: Building approval-based automated workflows using Azure Logic Apps

Azure Automation: Auto-scaling Azure SQL database with Azure Logic Apps

Azure Automation: Analyzing Twitter sentiments using Azure Logic Apps

Azure Automation: Use Azure Logic Apps to import data into Azure SQL Database from Azure Blob Storage

Azure Automation: Publish LinkedIn posts and tweets automatically using Azure Logic Apps

Azure Automation: Export Azure SQL database data to CSV files using Azure Logic Apps

Azure Automation: Translate documents into different languages using Cognitive Services with Azure Logic Apps

Azure Automation: Azure Logic Apps for face recognition and insert its data into Azure SQL Database

Azure Automation: Automatic scaling Azure SQL databases based on CPU usage threshold

• Author
• Recent Posts

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date.

He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious "Best author of the year" continuously in 2020 and 2021 at SQLShack.

Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Logic Apps Designer String Contains Multiple Values

Source: https://www.sqlshack.com/azure-automation-analyzing-twitter-sentiments-using-azure-logic-apps/

Posted by: mcnamaragulay1979.blogspot.com

Share this post