How to get key phrases from Azure SQL using Artificial Intelligence

By:   |   Updated: 2022-02-15   |   Comments   |   Related: > Azure


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

Problem

I have several places where we collect and store text data in Azure SQL DB tables. How can we detect and extract key phrases from the collected data? Is there an Azure service to assist with this task?

Solution

In a previous article we looked at how to Detect the Language with Azure AI Language Service. In this article we are going to create an application that can extract key phrases from Azure SQL tables.

Here is an application I wrote to detect key phrases using AI. If you click on the phrase, it will show a list of key phrases. We will show how this can be done in this article.

Azure SQL

Requirements

  1. A machine with Visual Studio (VS).
  2. An Azure Account to create an Azure SQL Database
  3. A database in Azure SQL. If you do not have one, you can check the C# to Azure SQL related article.

Getting Started

The tip will have 3 parts:

  1. Create an Azure SQL table
  2. Create an Azure AI Text Analysis Service
  3. Create a Windows Form to get the Azure SQL data and extract key phrases

Create an Azure SQL table

First, we need to create an Azure SQL Database. In this database, we will create a table named phrase with the columns ID and phrases.

Our article C# to Azure SQL explains how to create an Azure SQL database. Note that the Server admin is admindaniel. If you do not remember the password of the Server admin, you can reset the password. Also, the server’s name will be useful to connect to the database later.

Azure SQL Server

The database name in this example is mymssqltips. This information will be used later. Press the Query editor to create a table with data.

Azure SQL database

Run the following T-SQL commands to create the dbo.phrase table and insert some rows of data.

CREATE TABLE [dbo].[phrase](
   [id] [smallint] NULL,
   [phrases] [nchar](4000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[phrase] ([id], [phrases]) VALUES (1, N'Obama was born in Honolulu, Hawaii. After graduating from Columbia University in 1983, he worked as a community organizer in Chicago. In 1988, he enrolled in Harvard Law School, where he was the first black president of the Harvard Law Review.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ')
INSERT [dbo].[phrase] ([id], [phrases]) VALUES (2, N'Barack Hussein Obama  (About this soundlisten) bə-RAHK hoo-SAYN oh-BAH-mə;[1] born August 4, 1961) is an American politician, lawyer, and author who served as the 44th president of the United States from 2009 to 2017. A member of the Democratic Party, Obama was the first African-American president of the United States.[                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ')
INSERT [dbo].[phrase] ([id], [phrases]) VALUES (3, N'Obama signed many landmark bills into law during his first two years in office.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 ')
GO
Create table in Azure SQL

Create an Azure AI Text Analysis Service

Azure provides services with AI to do different things like detect key phrases, detect the language used, answer questions and more.

In this example, we will use the text analysis to read text and extract key phrases. To do that, we need to create an endpoint in the Azure Portal and get the endpoint’s URL and the key.

In the Azure Portal, create a resource AI+Machine Learning and select Language Service and press Create.

Create Azure Language Service

For more information about creating a Language service, please refer to our Detect the language with AI for more details.

Once created, we need 2 things: the Endpoint’s URL and the key. Click the option to manage keys.

Azure Language service Endpoint

You can copy the key to the clipboard. That information will be used to connect to the Azure Service from the application.

Administer Azure Endpoint keys

Create a Windows Form to get Azure SQL Data and extract key phrases

In the previous sections, we created an Azure SQL table with some phrases. We then created an Azure Endpoint to provide AI text analysis. Now, we will create a C# application to get the data and extract the information.

In VS, go to File > New Project and select Windows Forms App.

Create c# Windows forms project

In the Solution Explorer, right click on Packages and select Manage NuGet Packages to add packages.

Update Packages

Search for the Microsoft.Data.SqlClient and install it. This package allows us to connect to SQL Server or Azure SQL.

Microsoft Data Sqlclient

Also, install the Azure.AI.TextAnalytics to access the AI TextAnalytics service. This is the library to connect to the Azure AI Service for text analysis.

Azure.AI.TextAnalytics

In the Windows form, drag and drop a DataGridView, a ListBox and a label.

Visual Studio ListBox and Datagridview

The form should look like this:

Windows forms app

Now add this code to the form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using Azure;
using Azure.AI.TextAnalytics;
 
namespace keyphrase
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        //Endpoint url and credentials
        private static readonly AzureKeyCredential credentials = new AzureKeyCredential("f3d03934563b9d3987f5f678e88717");
        private static readonly Uri endpoint = new Uri("https://textanalysisms.cognitiveservices.azure.com/");
 
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                //Clear the listbox
                listBox1.Items.Clear();
                DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
                //Connect using the credentials
                var client = new TextAnalyticsClient(endpoint, credentials);
                //Extract the key phrases of the cell selected
                var response = client.ExtractKeyPhrases(row.Cells[1].Value.ToString());
                
 
                foreach (string keyphrase in response.Value)
                {
                    
                    //Add the keypharase to the listbox
                    listBox1.Items.Add(keyphrase);
 
                }
                
 
            }
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
 
            //Create a string connection
            string myconn = "Server=mssqltipsserver.database.windows.net;Database=mymssqltips;User ID=admindaniel;Password=thipwd2334!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
            //create a query to Azure
            string Azurequery = "SELECT * FROM dbo.phrase";
            SqlConnection myconnection = new SqlConnection(myconn);
            //Create a sql data adapter
            SqlDataAdapter mydapter = new SqlDataAdapter(Azurequery, myconnection);
            //Create a new dataset
            DataSet mydataset = new DataSet();
            //Open the connection
            myconnection.Open();
            //Fill Data Adapter with the information from phrase table
            mydapter.Fill(mydataset, "phrase");
            //close connection
            myconnection.Close();
            //Bind the datagridview1 with the data set mydataset
            dataGridView1.DataSource = mydataset;
            dataGridView1.DataMember = "phrase";
            //Increase the size of the column
            DataGridViewColumn column = dataGridView1.Columns[1];
            column.Width = 350;
        }
 
    }
}
 

Let’s explain the code.

First, we invoke the namespaces.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using Azure;
using Azure.AI.TextAnalytics;

They are all defaults, except these ones:

  • System.Data.SqlClient is used to connect to the Azure SQL databases.
  • Azure.AI.TextAnalytics is used to connect to the Azure AI text service.

Then we provide the endpoint URL and the keys. This information is from the "Create an Azure AI Text Analysis Service" section above. You need the Azure key and the endpoint URL.

//Endpoint url and credentials
        private static readonly AzureKeyCredential credentials = new AzureKeyCredential("f3d03934563b9d3987f5f678e88717");
        private static readonly Uri endpoint = new Uri("https://textanalysisms.cognitiveservices.azure.com/");

When we load the form, we load with the Azure SQL information from the dbo.phrase table.

        private void Form1_Load(object sender, EventArgs e)
        {

The string connection, stores the following information.

  • The Azure Server name (mssqltipsserver.database.windows.net)
  • The Azure database name (mymssqltips).
  • The Azure administrator for the Azure Server (admindaniel)
  • The Azure administrator password
 
            //Create a string connection
            string myconn = "Server=mssqltipsserver.database.windows.net;Database=mymssqltips;User ID=admindaniel;Password=thipwd2334!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
 

We create a query to the dbo.phrase Azure SQL table. Creating a SqlDataAdapter.

            //create a query to Azure
            string Azurequery = "SELECT * FROM dbo.phrase";
            SqlConnection myconnection = new SqlConnection(myconn);
            //Create a sql data adapter
            SqlDataAdapter mydapter = new SqlDataAdapter(Azurequery, myconnection);
            

We will create a DataSet, fill the Adapter with the dataset and send the information to the dataGridView1 to show the data.

//Create a new dataset
            DataSet mydataset = new DataSet();
            //Open the connection
            myconnection.Open();
            //Fill Data Adapter with the information from phrase table
            mydapter.Fill(mydataset, "phrase");
            //close connection
            myconnection.Close();
            //Bind the datagridview1 with the data set mydataset
            dataGridView1.DataSource = mydataset;
            dataGridView1.DataMember = "phrase";
 
 
            //Increase the size of the column
            DataGridViewColumn column = dataGridView1.Columns[1];
            column.Width = 350;
        }
 
 
    }
}

Then in the dataGridView1, we will create an event when clicking on the cell to get the key phrases of the selected cell. The code will detect the cell selected and add the key phrases to the listbox1.

The listBox1 will be cleared each time a new cell is selected.

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                //Clear the listbox
                listBox1.Items.Clear();
                DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
                //Connect using the credentials
                var client = new TextAnalyticsClient(endpoint, credentials);
                //Extract the key phrases of the cell selected
                var response = client.ExtractKeyPhrases(row.Cells[1].Value.ToString());
                
 
                foreach (string keyphrase in response.Value)
                {
                    
                    //Add the keypharase to the listbox
                    listBox1.Items.Add(keyphrase);
 
                }
                
 
            }
        }

If everything is setup correctly, you will be able to get the phrases from Azure SQL in the Datagridview and get the key phrases in the listbox.

Select datagridview cell

Conclusion

In this article, we learned how to create an Azure Language Service and how to get key phrases using the Azure AI services. We used data from Azure SQL and then extract the key phrases from the data.

Next Steps

For more information refer to the following links:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips


Article Last Updated: 2022-02-15

Comments For This Article





download














get free sql tips
agree to terms