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?
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
- A machine with Visual Studio (VS) with any supported version.
- An Azure Account to create Azure SQL Databases
- 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:
- Create an Azure SQL table with the feedback in different languages.
- Create an Azure Language Service.
- 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
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.
Select AI+Machine Learning and select Language Service and press Create.
In Select additional features select Custom text classification and 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.
You can search in the Azure search text box and look for it once it is created.
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.
You can copy the keys to the clipboard and there is a button to Show 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#.
In the Solution Explorer, right click on Packages and select Manage NuGet Packages to add packages.
Search for the Microsoft.Data.SqlClient and install it. This package allows you to connect to SQL Server or Azure SQL.
Also install Azure.AI.TextAnalytics to access the AI TextAnalytics service.
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.
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:
