Detect the Language with Azure AI Language Service

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


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

Problem

We have a table in Azure SQL with some comments. Some are in English, but some are in other languages. Is there a way to detect the language of the comment?

Azure SQL table with comments in different languages
Solution

To help detect the language of the comment, we are going to use some AI. We will use Azure Artificial Intelligence to detect the language of text stored in an Azure SQL table.

Requirements

  1. A machine with Visual Studio (VS) with any supported version.
  2. An Azure Account to create Azure SQL Databases
  3. A database in Azure SQL. If you do not have one, you can check the C# to Azure SQL article for more info.

Getting Started

This tutorial includes 3 parts:

  1. Create an Azure SQL table with the feedback in different languages.
  2. Create an Azure Language Service.
  3. Create an application to retrieve data from Azure SQL and detect the language in VS.

1 - Create an Azure SQL table with the feedback in different languages

I am assuming that you already have an Azure SQL database.

In your Azure SQL database, create the following table and insert data using the following T-SQL statements:

CREATE TABLE [dbo].[feedback](
   [id] [smallint] NULL,
   [comment] [nchar](4000) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[feedback] ([id], [comment]) VALUES (1, N'Hello, I really like your product it is pretty cool                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ')
INSERT [dbo].[feedback] ([id], [comment]) VALUES (2, N'Bonjour. Je suis vraiment heureux d''acheter ce produit.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         ')
INSERT [dbo].[feedback] ([id], [comment]) VALUES (3, N'Estoy facinado por el producto. Los felicito por el trabajo.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ')
GO
Azure SQL Table with data

2 - Create an Azure Language Service

The Azure language service offers different language services. You can get key phrases from text, detect Personally Identifiable Information (PII), Detect the language, analyze sentiments from text, question answering and more. In this article, we will check the text from an Azure SQL column and detect the language.

In the Azure Portal, Create a resource.

Azure Create a Resource

Select AI+Machine Learning and select Language Service and press Create.

Azure Language Service

In Select additional features select Custom text classification and extraction.

Custom test classification & extraction

You may need to create a resource group. This is a group in Azure that holds different resources. If you do not have one, you can create a new one with the Create new link.

You then need to select a region, it is recommended to select the region closest to your location, a name which should not be repeated and the Pricing tier which depends on your demand and usage. You will need to accept the legal terms and the Responsible AI Notice.

Azure text service properties

You can search in the Azure search text box and look for it once it is created.

Search text analysis service in Azure

It is an API that you can use with API tools like postman, use Power BI to get results or you can use a programing language of your preference to invoke the API.

You will be able to see the URL of the endpoint that provides the AI service. If you want to see the keys, press Click here to manage keys link.

Endpoint Azure manage keys

You can copy the keys to the clipboard and there is a button to Show Keys.

Administer Azure Endpoint keys

3 - Create an application to retrieve data from Azure SQL and detect the language in VS

In Visual Studio, we are going to invoke the Azure Service with AI to detect the language of our database. We will use C# to invoke the Azure AI Service. In VS go to the menu and select File > New Project.

Then select Console Application and C#.

Visual Studio Create Console Application 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 you to connect to SQL Server or Azure SQL.

Microsoft Data Sqlclient

Also install Azure.AI.TextAnalytics to access the AI TextAnalytics service.

Azure.AI.TextAnalytics

Finally, we can use the following c# code or similar for testing:

using Azure;
using System;
using Azure.AI.TextAnalytics;
using System.Data.SqlClient;
 
namespace LanguageDetectionExample
{
    class Program
    {
        private static readonly AzureKeyCredential credentials = new AzureKeyCredential("f3d0399asd33347423b9d3987f5f8e88717");
        private static readonly Uri endpoint = new Uri("https://textanalysisms.cognitiveservices.azure.com/");
 
        static void Main(string[] args)
        {
            try
            {
                var client = new TextAnalyticsClient(endpoint, credentials);
                
                SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
                //Azure SQL Server Name 
                conn.DataSource = "mssqltipsserver.database.windows.net";
                //User to connect to Azure
                conn.UserID = "admindaniel";
                //Password used in Azure
                conn.Password = "mySuperSecretPwd2333@";
                //Azure database name
                conn.InitialCatalog = "mymssqltips";
 
                using (SqlConnection connection = new SqlConnection(conn.ConnectionString))
                {
 
                    //Query used in the code
                    String sql = "SELECT id,comment from dbo.feedback";
                    //Connect to Azure SQL using the connection
                    using (SqlCommand sqlcommand = new SqlCommand(sql, connection))
                    {
                        //Open the connection
                        connection.Open();
                        //Execute the reader function to read the information
                        using (SqlDataReader reader = sqlcommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                DetectedLanguage detectedLanguage = client.DetectLanguage(reader.GetString(1));
 
 
                                Console.WriteLine($"\t{detectedLanguage.Name},\tISO-6391: {detectedLanguage.Iso6391Name}");
                                Console.WriteLine("\t{0}\t{1}\n", reader.GetInt16(0), reader.GetString(1).Substring(0,30));
                            }
                        }
                    }
                }
            }
            //If it fails write the error message exception
            catch (SqlException e)
            {
 
                //Write the error message
                Console.WriteLine(e.ToString());
            }
            Console.ReadLine();
        }
    }
} 

Let’s explain the code.

In the first part we are invoking the packages. We use the Azure libraries, the Sqlclient to connect to Azure SQL and the Azure.AI.TextAnalytics to connect to our language detector service.

using Azure;
using System;
using Azure.AI.TextAnalytics;
using System.Data.SqlClient;

We then provide the Keys and the Endpoint. This information is in Azure in the Language service.

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

After that, we provide the Azure SQL credentials.

SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder();
//Azure SQL Server Name 
conn.DataSource = "mssqltipsserver.database.windows.net";
//User to connect to Azure
conn.UserID = "admindaniel";
//Password used in Azure
conn.Password = "mySuperSecretPwd2333@";
//Azure database name
conn.InitialCatalog = "mymssqltips";

Finally, we retrieve the columns from the dbo.feedback table and detect the language used.

String sql = "SELECT id,comment from dbo.feedback";
//Connect to Azure SQL using the connection
using (SqlCommand sqlcommand = new SqlCommand(sql, connection))
{
    //Open the connection
    connection.Open();
    //Execute the reader function to read the information
    using (SqlDataReader reader = sqlcommand.ExecuteReader())
    {
        while (reader.Read())
        {
            DetectedLanguage detectedLanguage = client.DetectLanguage(reader.GetString(1));

            Console.WriteLine($"\t{detectedLanguage.Name},\tISO-6391: {detectedLanguage.Iso6391Name}");
            Console.WriteLine("\t{0}\t{1}\n", reader.GetInt16(0), reader.GetString(1).Substring(0,30));
        }

If everything is setup correctly, when we run the code we can see the following output that shows the language and then the comment that we are checking.

Language results

The first comment is in English, the second in French and the last in Spanish. As you can see, our code can detect the language of the comments used in Azure SQL.

Conclusion

In this article, we learned how to create an Azure Language Service with AI and how to create code in C# to connect to the service and detect the language of some data in an Azure SQL table.

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-03

Comments For This Article





download














get free sql tips
agree to terms