This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

The azure_ai extension on Azure Database for PostgreSQL now has text translation capabilities, in preview, using Azure AI translator.

With the text translation APIs, you can translate text in more than 100 languages from right within SQL, which facilitates building intelligent multilingual applications on Azure Database for PostgreSQL. The translation is done in real time and translated text can be stored in a Postgres column for future references or can be used on the fly as a piece of data involved in the execution of a query. The extension also supports automatically detecting the language of the input text and filtering profanities.

How to use it

To use the translation feature:

1. Allow list the azure_ai extension.
2. Create the extension in the database.

   CREATE EXTENSION azure_ai;

3. Configure the extension to use Azure AI Language services.
4. Invoke the translate function from within SQL.

Example

In its simplest form, real-time translation is as simple as passing the text to be translated, passing the target language, and obtaining back an array of translations.

-- returns an array of translations
-- output: https://learn.microsoft.com/en-us/azure/ai-services/translator/reference/v3-0-translate#response-body
-- note: languages chosen in the examples below correspond to the following:
--        'pt': Brazilian-Portuguese
--        'es': Spanish
--        'zh-Hans': Chinese Simplified
SELECT a.*
FROM azure_cognitive.translate('Language Translation in real time in multiple languages is quite cool', 'pt') a;

-- same as the previous example, but expanding the returned array to a set of rows.
-- the unnest function used below expands an array passed as an argument to a set of rows. It is one of many PostgreSQL built-in functions provided to manipulate array types.
SELECT (unnest(a.translations)).*
FROM azure_cognitive.translate('Language Translation in real time in multiple languages is quite cool', 'pt') a;

-- returns the same text translated to multiple languages
SELECT (unnest(a.translations)).*
FROM azure_cognitive.translate('Language Translation in real time in multiple languages is quite cool', array['es', 'pt', 'zh-Hans']) a;

In addition, you can use the profanity filters to choose how to deal with profanities during translation.

-- returns an array of translations with profanities marked with asterisks.
-- profanity_action defaults to 'NoAction', and can be also set to 'Marked' or 'Deleted'
-- profanity_marker defaults to 'Asterisk', and can be also set to 'Tag'. The former replaces profanities with ***, the latter surrounds profane words with  tags.
SELECT (unnest(a.translations)).TEXT
FROM azure_cognitive.translate('Language Translation in real time in multiple languages is quite cool you idiot', 'es'
            ,profanity_action=> 'Marked', profanity_marker=> 'Asterisk') a;

All the Azure AI language functions can be used together. An example of this would be summarization of a document, and then translating the summary into another language.

-- summarize text in Englsih and then translate into Portuguese.
SELECT
    bill_id,
(unnest((azure_cognitive.translate(unnest(azure_cognitive.summarize_abstractive(bill_text, 'en')),'pt')).translations)).TEXT translated_abstractive_summary
    , bill_text
FROM bill_summaries
WHERE bill_id = '114_hr2499';

Real-time translation makes it easy to localize error messages, or product descriptions in applications that serve a multi-lingual audience. For example, an application could invoke the function to translate reviews in real-time based on a user's locale.

CREATE OR REPLACE FUNCTION translated_reviews (IN review_listing_id INT, IN target_language varchar(2), IN topn INT DEFAULT 10)
RETURNS TABLE(
            listing_id int,
            translated_comments text,
            target_language varchar(2)
            )
AS $$
    SELECT a.listing_id,(unnest(b.translations)).text,target_language
    FROM reviews a, azure_cognitive.translate(comments,target_language) b
    WHERE listing_id = review_listing_id
    LIMIT topn;
$$ LANGUAGE sql;

-- Call the function
SELECT * FROM translated_reviews(7202016, 'pt', 5); -- pt=Portuguese
SELECT * FROM translated_reviews(7202016, 'fr', 5); -- fr=French
SELECT * FROM translated_reviews(7202016, 'ko', 5); -- ko = korean

In another example, the code snippet below shows inserting an error message and having it translated into multiple languages, storing those in the database for use later.

-- table in which the original product names and descriptions are kept.
CREATE TABLE products
(
    product_id INT GENERATED ALWAYS  AS IDENTITY PRIMARY KEY,
    product_name TEXT,
    product_description TEXT
);

-- table in which localized versions of product descriptions are kept.
CREATE TABLE products_localized
(
    product_id INT,
    product_description TEXT,
    language_code VARCHAR(2)
);

-- procedure to add a new product to the product catalog.
-- it automatically translates the product description into the list of languages provided and stores them in the products_localized table for future reference.
CREATE OR REPLACE PROCEDURE add_product(product_name TEXT, product_description TEXT, target_languages  VARCHAR(2)[])
LANGUAGE plpgsql
AS $$
DECLARE
product_code INT;
BEGIN
    INSERT INTO products (product_name, product_description)
        values (product_name, product_description) 
        RETURNING product_id INTO product_code;

    INSERT INTO products_localized(product_id,product_description, language_code)
        SELECT product_code, (unnest(a.translations)).text AS product_description
                , (unnest(a.translations)).target_language ASmlanguage_code
        FROM azure_cognitive.translate(product_description, target_languages) a;
END;
$$ ;

-- add one 'Mountanin Bike' product to the catalog, together with its description, and request the description to be translated into French, Brazilian-Portuguese, and Japanese.
CALL add_product('Mountain Bike', 'This outdoor tough mountain bike has a stunning 5 year warranty', array['fr','pt','ja']);

-- check that the product has been added to the catalog, and the original description has been translated into the three languages requested.
SELECT * FROM products;
SELECT * FROM products_localized;

Results:

Getting Started

To learn more about the azure_ai extension, translations and how it can simplify building applications on Azure Database for PostgreSQL, visit our documentation below:

• Azure AI Extension.
• Azure AI Text Translation
• To learn even more about our Flexible Server managed service, see the Azure Database for PostgreSQL Flexible Server.
• You can always find the latest features added to Flexible server in this release notes page.

If you have any questions or need guidance, don't hesitate to reach out to us at Ask Azure DB for PostgreSQL.