DocsICP FilingConsole
Documentation
Home Page AnalyticDB AnalyticDB for PostgreSQL User Guide Warehouse intelligence (public preview)

Warehouse intelligence (public preview)

Updated at:
Copy as MD
My Favorites

Use the PG_CATALOG.AI_GENERATE_TEXT(...) function in AnalyticDB for PostgreSQL to interact with large language model (LLM) services deployed on Alibaba Cloud's Platform for AI (PAI) Elastic Algorithm Service (EAS). This function enables you to perform tasks such as text inference, classification, induction, and summarization.

Background

Artificial intelligence generated content (AIGC) is a technology that uses artificial intelligence to automatically produce content. Based on machine learning and natural language processing (NLP), it can generate various types of content, including text, images, and audio.

AnalyticDB for PostgreSQL is an integrated platform for data analysis and lightweight AI. It helps small and medium-sized users create a closed-loop environment within their database that prioritizes data analysis, supplemented by AI applications to augment data analysis with AI capabilities.

The warehouse intelligence feature of AnalyticDB for PostgreSQL provides an AI text generation service. It uses an LLM service to generate, or infer, new text that is similar in style, tone, and content to the input data.

Prerequisites

  • Your AnalyticDB for PostgreSQL instance must meet the following minor version requirements. To view the minor version of your instance, see View the minor version.

    • For an instance in elastic storage mode on V6.0, the minor version must be v6.6.1.0 or later.

    • For an instance in elastic storage mode on V7.0, the minor version must be v7.0.4.0 or later.

    • For an instance in serverless mode, the minor version must be cn.v2.1.1.5 or later.

  • You have activated Elastic Algorithm Service (EAS) in the same region as your AnalyticDB for PostgreSQL instance, and you have deployed at least one LLM service, such as Tongyi Qianwen, ChatGLM, or Llama2. This topic uses the Tongyi Qianwen model as an example. For more information, see Quickly deploy Qwen in EAS.

    Note

The PG_CATALOG.AI_GENERATE_TEXT(...) function

Syntax

FUNCTION PG_CATALOG.AI_GENERATE_TEXT
(input_endpoint text,
input_token text,
input_prompt text,
input_additional_parms text)
RETURNS TEXT
......

Parameters

Parameter

Required

Description

input_endpoint

Yes

The endpoint for the LLM service deployed on PAI EAS.

To get the endpoint:

  1. Log on to the PAI console.

  2. In the left-side navigation pane, choose Model Deployment > Elastic Algorithm Service (EAS), and then click the service ID.

  3. On the Service Details tab, find the Basic Information card and click View Endpoint Information.

  4. In the Invocation Method dialog box, go to the VPC Endpoint tab to view the endpoint.

input_token

Yes

The token for the LLM service deployed on PAI EAS.

To get the token:

  1. Log on to the PAI console.

  2. In the left-side navigation pane, choose Model Deployment > Elastic Algorithm Service (EAS), and then click the service ID.

  3. On the Service Details tab, find the Basic Information card and click View Endpoint Information.

  4. In the Invocation Method dialog box, go to the VPC Endpoint tab to view the token.

input_prompt

Yes

The input text for inference.

input_additional_parms

No

You can set the parameter history. history provides context for the inference content. The context is encapsulated in a question-and-answer format using JSON syntax. For more information, see Best practices: Encapsulation function AI_GENERATE_TEXT.

Format: { "history": [ ["question_text",''answer_text"], ["question_text",''answer_text"] ... ] }

history is the keyword that identifies the context. It has the following components:

  • question_text: The historical question.

  • answer_text: The historical answer.

  • Both must be of the STRING or TEXT type.

If you do not set this parameter, use NULL when calling the PG_CATALOG.AI_GENERATE_TEXT(...) function.

Examples

  • Example 1: Use the input_prompt parameter to perform text inference.

    SELECT PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                   'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                   'Where is the capital of Zhejiang province?',
                                    NULL) AS answer;

    The output is similar to the following:

    -[RECOND 1]---------------
answer | The capital of Zhejiang province is Hangzhou.

  • Example 2: Use the input_additional_parms parameter to provide context for text inference.

    Adding relevant context can improve the accuracy of text inference. The context in this example is about provincial capitals, so the answer to the question "What about Jiangsu?" is correctly inferred as "The capital of Jiangsu province is Nanjing" instead of other general information about Jiangsu.

    SELECT PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                   'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                   'What about Jiangsu?',
                                    '{ "history" : [
                                                      ["Where is the capital of Zhejiang province?", "The capital of Zhejiang province is Hangzhou."],
                                                      ["Where is the capital of Liaoning province?", "The capital of Liaoning province is Shenyang."]
                                                    ]
                                    }') as answer;

    The output is similar to the following:

    -[RECOND 1]-------------
answer | The capital of Jiangsu province is Nanjing.

Best practice: Encapsulate AI_GENERATE_TEXT

Each time you use the AI_GENERATE_TEXT function, you must pass the lengthy input_endpoint and input_token parameters. This is cumbersome and exposes sensitive information in your application. We recommend that you encapsulate the AI_GENERATE_TEXT function within a custom function. This provides the following benefits:

  • Simplifies function calls and hides sensitive information.

  • Allows you to post-process the generated content.

The following example shows how to encapsulate the AI_GENERATE_TEXT function:

-- Define the encapsulation function:
CREATE OR REPLACE FUNCTION Wrapper_AI_GENERATE_TEXT(prompt text, context text) 
RETURNS TEXT AS
$$
DECLARE
   result text := ' ';
BEGIN
    SELECT PG_CATALOG.AI_GENERATE_TEXT ('<... use_your_endpoint ...>',
                                        '<... use_your_token ...>', 
                                        prompt, context) INTO result;
    IF result IS NOT NULL 
       AND result LIKE '%some_condition%' THEN 
      result := '<... your_expected_value ...> ';
    END IF; 
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Use the encapsulation function for simplified calls:
SELECT Wrapper_AI_GENERATE_TEXT('Where is the capital of Zhejiang province?', null) AS answer;

---------------------------
answer | The capital of Zhejiang province is Hangzhou.

Use cases

Summarize customer reviews from an e-commerce website and determine whether the feedback is positive or negative.

Run the following statements to create a table named feedback_collect to store customer reviews for purchased products.

-- Create the table.
CREATE TABLE feedback_collect
(
    id int not null,
    name varchar(10),
    age int,
    feedback text,
    AI_evaluation text
) 
distributed by (id);

-- Insert customer information and their product feedback.
INSERT INTO feedback_collect VALUES(1, 'John', 38, 'The iPhone 15 has a beautiful, lightweight design and powerful features. Its unique color and material, combined with a clear and smooth screen, make it a pleasure to use. The camera performance is excellent, with a great telephoto lens that meets a wide range of needs. It also includes a USB-C port for fast charging and data transfer. Overall, the iPhone 15 is a user-friendly phone that offers great value.');
INSERT INTO feedback_collect VALUES(2, 'Alice', 30, 'The ThinkPad P series laptop is stylish, thin, and powerful. It features a high-definition screen, smooth operation, and a comfortable keyboard, delivering excellent performance. It supports multiple connection types, including USB, HDMI, and Thunderbolt, along with fast charging and data transfer. The ThinkPad P series is user-friendly and offers great value.');
INSERT INTO feedback_collect VALUES(3, 'Mike', 40, 'This screen protector is terrible. First, the quality is poor, and it feels rough to use. Second, the adhesive is weak and wore off after just a few days, so it no longer protects the screen. Most importantly, it ruins the user experience. I am very dissatisfied and strongly recommend against buying this product to avoid wasting time and money.');
INSERT INTO feedback_collect VALUES(4, 'Sarah', 45, 'This electric toothbrush is awful. Its battery life is extremely short, lasting only a day or two per charge. The cleaning performance is also subpar, leaving a lot of plaque behind. On top of that, the quality is poor, and it frequently glitches or malfunctions. I am very disappointed and suggest that everyone think twice before buying it.');

  • Scenario 1: Automatically determine whether product feedback is positive or negative and store the result in the feedback_collect table.

    -- First, query the AI_evaluation column. As you can see, no evaluation results are present yet.
SELECT AI_evaluation FROM feedback_collect;

-- Automatically determine whether the feedback is positive or negative and save the result to the feedback_collect table.
UPDATE feedback_collect
SET AI_evaluation = PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                                'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                                'Determine whether the following feedback is positive or negative: '|| feedback,
                                                '{
                                                    "history" : [
                                                      ["This camera takes poor-quality photos and has bad battery life.", "Negative feedback."],
                                                      ["This refrigerator is energy-efficient, eco-friendly, and quiet.", "Positive feedback."]
                                                    ]
                                                 }');
-- Query the AI_evaluation column again to see the results.
SELECT AI_evaluation FROM feedback_collect;

     ai_evaluation      
------------------------
 This feedback is negative.
 Negative feedback.
 Positive feedback.
 Positive feedback.
(4 rows)

  • Scenario 2: Select and summarize negative product reviews.

    -- Summarize and return the content of negative reviews.
SELECT feedback AS original_feedback,
       AI_evaluation AS feedback_classification,
       PG_CATALOG.AI_GENERATE_TEXT('http://1648821****.vpc.cn-shanghai.pai-eas.aliyuncs.com/api/predict/test_pg',
                                    'OGZiOGVkNTcwNTRiNzA0ODM1MGY0MTZhZGIwNT****',
                                   'Summarize the following feedback in about 30 words: '|| feedback,
                           NULL) AS summary       
FROM feedback_collect
WHERE AI_evaluation LIKE '%Negative%';

-- The output is similar to the following:
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
original_feedback     | This screen protector is terrible. First, the quality is poor, and it feels rough to use. Second, the adhesive is weak and wore off after just a few days, so it no longer protects the screen. Most importantly, it ruins the user experience. I am very dissatisfied and strongly recommend against buying this product to avoid wasting time and money.
feedback_classification | This feedback is negative.
summary       | This screen protector has poor quality, weak adhesive, and negatively impacts the user experience. It is not recommended for purchase.
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
original_feedback     | This electric toothbrush is awful. Its battery life is extremely short, lasting only a day or two per charge. The cleaning performance is also subpar, leaving a lot of plaque behind. On top of that, the quality is poor, and it frequently glitches or malfunctions. I am very disappointed and suggest that everyone think twice before buying it.
feedback_classification | Negative feedback.
summary       | This electric toothbrush suffers from short battery life, poor cleaning performance, and low quality. Careful consideration is advised before purchasing.
Important

The inference results in these scenarios are for reference only. The output may vary depending on the large language model (LLM) you use, such as Tongyi Qianwen or ChatGLM. Even with the same LLM, running the same SQL statement multiple times might produce slightly different results.

Troubleshooting

Error messages

  • An error message is reported: AI_GENERATE_TEXT error: HTTP request service failure or AI_GENERATE_TEXT error: Incorrect endpoint URL is specified.

    When calling the PG_CATALOG.AI_GENERATE_TEXT function, ensure that the input_endpoint parameter is correct. You must use the VPC endpoint. To find the endpoint, see The PG_CATALOG.AI_GENERATE_TEXT(...) function.

  • You receive an error message such as AI_GENERATE_TEXT error: HTTP request service failure.

    When calling the PG_CATALOG.AI_GENERATE_TEXT function, ensure that the input_token parameter is correct. To find the token, see The PG_CATALOG.AI_GENERATE_TEXT(...) function.

  • You receive the error message AI_GENERATE_TEXT error: Invalid JSON syntax against the 4th parameter within UDF.

    When calling the PG_CATALOG.AI_GENERATE_TEXT function, ensure that the input_additional_parms parameter is correct. This error can be caused by the following reasons:

    • The JSON syntax for the input_additional_parms parameter is invalid.

    • The input_additional_parms parameter uses full-width (Chinese) characters for commas, single quotation marks, or double quotation marks, which are not valid in JSON.

  • You receive the error message function AI_GENERATE_TEXT (unknown, unknown, unknown, unknown) does not exist.

    • Ensure that your AnalyticDB for PostgreSQL instance meets the minor version requirements. To view the minor version of your instance, see View the minor version.

    • When calling the PG_CATALOG.AI_GENERATE_TEXT function, the schema PG_CATALOG was not specified, preventing the system from recognizing the function.

Extended response time for AI_GENERATE_TEXT

If a call to the PG_CATALOG.AI_GENERATE_TEXT function takes too long to return a result, investigate the following potential causes:

  • Ensure that your AnalyticDB for PostgreSQL instance is in the same region as the PAI EAS where the LLM service is deployed.

  • Ensure that the input_endpoint and input_token values used in the function call match the deployment information of the LLM service. For more details, see Parameters.

  • Check for performance issues with the model itself. Typically, the average response time for an LLM service to process a single request is around 2 to 3 seconds.

    Note

    The actual response time depends on several factors: the length of the content to analyze, the length of the generated inference, the model's processing performance, the hardware specifications of the deployment instance, and the number of model instances.

    For example, if you use a statement like SELECT pg_catalog.ai_generate_text (...) where column >= ... to perform text inference, you can try the following methods to improve processing speed.

    • Increase the number of instances for the LLM service.

      1. First, estimate the processing time.

        Calculate the total number of records to process by running SELECT COUNT(*) WHERE column >= .... Assume the result is 1,000. Sample a subset of the data to measure the LLM service's processing time. For example, run SELECT pg_catalog.ai_generate_text (...) LIMIT 100 to time the processing of 100 requests. Record timestamps in your application before and after executing the SELECT statement to measure the duration. Multiply this time by 10 to get a rough estimate for the total processing time.

      2. Based on the estimated time, increase the number of deployed instances for the LLM service. For recommendations on instance configurations, see Appendix: Recommended instance specifications for LLM services.

        Increase the number of LLM service instances in the following ways:

        • When you deploy the LLM service, select multiple node instances. For more information, see Deploy a custom inference service.

        • If the LLM service is already deployed, scale it out to increase the number of node instances. For more information, see Horizontal auto scaling.

        • Enable auto scaling to dynamically scale the number of node instances based on system load. For more information, see Scheduled auto scaling.

    • For long-running SQL queries, set the following parameters appropriately to prevent timeouts. Example:

      • SET idle_in_transaction_session_timeout = '5h'; sets the session timeout to 5 hours. Set a reasonable timeout based on your needs.

      • SET statement_timeout = 0; sets the statement timeout to never expire. Use this setting with caution.

Appendix: Recommended LLM instance specifications

The computing resources for LLM services are primarily provided by GPUs. Therefore, if you require faster request processing, select GPUs with higher specifications.

For the same total number of GPU cores, a larger number of service instances yields a greater improvement in processing speed.

For example, if you need a total of four GPU cores, deploying four instances with single-core GPUs will improve LLM request processing performance more than deploying one instance with a four-core GPU. The cost for both configurations is the same.

References

To learn more about Alibaba Cloud Platform for AI Elastic Algorithm Service (EAS), see EAS overview.

Previous：RDKitNext：Performance tests