banner



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

tweet sentiment

Similarly, here, we have a negative sentiment text.

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.

Create a logic app

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.

Add a trigger for Twitter

It opens logic app designer with Twitter configuration. Here, specify a connection name and authentication type – Use default Shared application.

Click on Sign in.

Twitter configuration

It asks for permissions for Microsoft Azure Logic apps to access your Twitter account.

Authorize Twitter app

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.

Authorize App

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.

When a new tweet is posted

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.

trending hashtag

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.

A diagram showing one potential way of analysing social media - using logic apps, sql database and cognitive services to analyse Twitter, and display results in Power BI.

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.

Perform sentiment analysis

It has a variety of services available, therefore, search for Text Analytics in the market place.

Text Analytics

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.

different pricing tiers

Review the config

It builds the resources and opens a quick start page.

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.

Keys and Endpoint

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.

Add Text Analytics in Azure Logic app

In the Text Analytics, select an action – Detect Sentiment.

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

account key, Site URL

It creates a connection for Azure cognitive service. In the detect Sentiment, click on Add new parameter and select Text.

azure cognitive service

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.

Add new parameter and dynamic parameter

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

Insert Tweets data

In the Insert row(V2) component, select the server, database name, and destination table from the drop-down list.

Insert row(V2)

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

Map table columns

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.

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.

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.

Add a conditional operation

In the actions, add conditional logic.

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

IF ELSE block

Rerun the logic app, and you start receiving positive sentiment emails, as shown below.

Rerun the logic app

I got the following positive sentiment Tweet in my Inbox.

positive sentiment Tweet

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

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

0 Response to "Logic Apps Designer String Contains Multiple Values"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel