Data analysis for web applications that serve various geographies is often a time-consuming and difficult task. This would necessitate a large team of multilingual experts. Furthermore, human translation is impractical to use in circumstances where the volume of content, the pace with which it is created, and the budget are limited. In such scenarios, Azure Cognitive Service can come to your aid. It provides Translator API which is a cloud-based machine translation service you can use to translate the text with a simple REST API call. Translator API can help you translate multiple languages at scale with high accuracy.

Process Flow

As shown in the below figure, we will create a Time Trigger Function that would get triggered at the scheduled time. The Function would read the content from a table in an MS SQL Server database and call Azure Cognitive Service – Translator API. The API would translate non-English text to English and return it as an output. This output will be updated in the table.

Prerequisites

1. An Azure Subscription
2. Non-English Text in a table in MS SQL Server

Provision Azure Cognitive Service – Translator

Provision Translator Service to invoke client APIs and translate the non-English text in the table

• Login to Azure Portal
• Search for “Translator” and Click on Translator under Marketplace

• Provide Name, Region and Pricing Tier and Click on Create

• Go to Resource > Keys and Endpoint. Copy Values of Key and Endpoint. These values would be configured for calling the APIs

Create Azure Function

Create a time trigger Azure Function. This trigger will get invoked at a scheduled time to read data from table for Text Translation.

• Search for Azure Functions Template

• Select the template and click on Next
• Provide Project Name and click on Create

• Select “Timer Trigger“. Configure Schedule by providing CRON expression. Click on Create.

Add Cognitive Service Nuget Package

• Right Click on Project > Manage Nuget Packages
• Search for “Newtonsoft.Json”. Select and Click on Install

Create Azure Cognitive Services Client

• Right Click on Project > Add > New Item…
• Select “Class“, provide name and click Add

• Update the TextTranslation.cs with below Code.

In this code, we have created a static DetectLanguage() method to detect the language and return output to TranslateText() as an input.

using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;
 
namespace DemoFuntionApp
{
    public static class TextTranslation
    {
        public static async Task<string> DetectLanguage(string subscriptionKey, string subscriptionRegion, string endpoint, string inputText)
        {
            string route = $"/detect?api-version=3.0";
            string textToTranslate = inputText;
            object[] body = new object[] { new { Text = textToTranslate } };
            var requestBody = JsonConvert.SerializeObject(body);

            using (var client = new HttpClient())
            using (var request = new HttpRequestMessage())
            {
                //Build the request.
                request.Method = HttpMethod.Post;
                request.RequestUri = new Uri(endpoint + route);
                request.Content = new StringContent(requestBody, Encoding.UTF8, "application/json");
                request.Headers.Add("Ocp-Apim-Subscription-Key", subscriptionKey);
                request.Headers.Add("Ocp-Apim-Subscription-Region", subscriptionRegion);

                // Send the request and get response.
                HttpResponseMessage response = await client.SendAsync(request).ConfigureAwait(false);
                // Read response as a string.
                string responseBody = await response.Content.ReadAsStringAsync();
                dynamic result = JsonConvert.DeserializeObject(responseBody);
                var text = result[0].language;
                return text;
            }
        }
    }
}

In this code, we have created a static TextTranslate() method with detected language and untranslated text as inputs. This method will translate text to English and return it as output.

    public static async Task<string> TranslateText(string subscriptionKey, string subscriptionRegion, string endpoint, string inputLanguage, string outputlanguage, string inputText)
        {
            string route = $"/translate?api-version=3.0&from={inputLanguage}&to={outputlanguage}";
            string textToTranslate = inputText;
            Console.WriteLine($"Input Language: {inputLanguage} \n Input:\n {textToTranslate}");
            object[] body = new object[] { new { Text = textToTranslate } };
            var requestBody = JsonConvert.SerializeObject(body);

            using (var client = new HttpClient())
            using (var request = new HttpRequestMessage())
            {
                //Build the request.
                request.Method = HttpMethod.Post;
                request.RequestUri = new Uri(endpoint + route);
                request.Content = new StringContent(requestBody, Encoding.UTF8, "application/json");
                request.Headers.Add("Ocp-Apim-Subscription-Key", subscriptionKey);
                request.Headers.Add("Ocp-Apim-Subscription-Region", subscriptionRegion);

                // Send the request and get response.
                HttpResponseMessage response = await client.SendAsync(request).ConfigureAwait(false);
                // Read response as a string.
                string responsetext = await response.Content.ReadAsStringAsync();
                var result = JsonConvert.DeserializeObject<List<Dictionary<string, List<Dictionary<string, string>>>>>(responsetext);
                var translation = result[0]["translations"][0]["text"];

                return translation;
            }

In this code, we have created a static Run() method which will get invoked from the Trigger function to detect the language, translate the text and return the translated text as output back to trigger.

public static string Run(string subscriptionKey, string subscriptionRegion, string endpoint, string outputlanguage, string inputText)
        {
            //Detect input language
            var inputLanguage = Detect.DetectLanguage(subscriptionKey, subscriptionRegion, endpoint, inputText).Result;
            var output = TranslateText(subscriptionKey, subscriptionRegion, endpoint, inputLanguage, outputlanguage, inputText).Result;
            return output;
        }

Update the Azure Function

using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using System.IO;
using System.Collections.Specialized;
using Microsoft.Extensions.Configuration;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc.Formatters.Internal;
using System.Text.RegularExpressions;
using System.Collections.Generic;
 
namespace DemoFuntionApp
{
    public static class TextTranslationFn
    {
        public static string Endpoint = "";
        public static string SubscriptionKey = "";
        public static string dbconnectionstr = "";

        [FunctionName("PIIDataMaskingFn")]
        public static void Run([TimerTrigger("0 */2 * * * *")]TimerInfo myTimer, ILogger log)
        {

DataSet dataset = new DataSet();


            using (SqlConnection conn = new SqlConnection(dbconnectionstr))
            {
                conn.Open();
                var queryString = "Select IDCOl1,COl2 from TestTable where IsTranslated = 0;";

                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(
                    queryString, conn);
                adapter.Fill(dataset);


                foreach (DataRow dr in dataset.Tables[0].Rows)
                {
                    string id = dr[0].ToString();
                    try
                    {
                        string translatedtext= string.Empty;
                        string untranslatedText= dr[1].ToString();
 
                        if (untranslatedText != "")
                        {
                            translatedtext = TextTranslation.Run(Endpoint, SubscriptionKey,untranslatedText , "en");
                        }

                        var updatequery = $"UPDATE [dbo].[TestTable] SET [Col2] = '{translatedtext}' ,[IsTranslated] = 1 WHERE IDCol1= {id}";

                        SqlCommand command = new SqlCommand(updatequery, conn);
                        command.ExecuteNonQuery();

                    }
                    catch(Exception ex)
                    { 
                    }
                }

            }
 
        }
    }
}

90 languages and dialects are supported by the translator. The translator may also be used in conjunction with Custom Translator to create neural translation systems for specific business and industry ecosystems.

Categories: Azure, Azure Cognitive Service, Azure Functions