MaxCompute:AI_EXTRACT

Syntax

STRING AI_EXTRACT(
  STRING <model_name>,
  STRING <version_name>,
  STRING <input>,
  ARRAY<STRING> <labels>
  [, STRING <model_parameters>]
);

Parameters

• model_name: Required. STRING. The model name. For more information, see SQL AI functions.

• version_name: Required. STRING. The model version. Specify DEFAULT_VERSION to use the default version.

• input: Required. STRING. The source text for extraction.

• labels: Required. ARRAY<STRING>. The labels to extract. Must contain 1 to 20 labels.

• model_parameters: Optional. A string specifying model parameters in JSON format, such as max_tokens, temperature, and top_p. For example:

  '{"max_tokens": 500, "temperature": 0.6, "top_p": 0.95}'.

  • max_tokens: The maximum number of tokens to generate in a single model call. The default for MaxCompute public models is 4096.

  • temperature: A value between 0 and 1 that controls the randomness of the model's output. A higher value results in more creative and diverse output, while a lower value makes the output more deterministic and conservative.

  • top_p: A value between 0 and 1 that limits the range of candidate labels the model considers. A higher value results in a wider range and more diversity, while a lower value results in a narrower range and more focused output.

Return value

Returns a JSON-formatted STRING containing all extracted labels and their values.

• Returns an error if input is not a STRING or labels is not an ARRAY<STRING>.

• Returns an error if labels is a constant with more than 20 items.

• Returns NULL if input or labels is NULL, or if input is an empty string ("").

• If a label has no match in the input text, its value is NULL in the returned JSON.

Examples

-- Use the model computing service in SQL jobs
SET odps.task.major.version=sql_modelstudio;
SET odps.namespace.schema=true;

SELECT AI_EXTRACT(
    bigdata_public_modelset.default.`qwen3-max`,
    DEFAULT_VERSION,
    'John Smith is a 35-year-old software engineer working at Alibaba Cloud in Hangzhou. He joined the company in 2020 and specializes in distributed computing.',
    ARRAY('name', 'age', 'occupation', 'company', 'city', 'year_joined')
) AS extracted_info;
 
-- Result:
+------------------------------------------------------------------------------------------------------------------------------------+
| extracted_info                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------+
| {"name":"John Smith","age":"35","occupation":"software engineer","company":"Alibaba Cloud","city":"Hangzhou","year_joined":"2020"} |
+------------------------------------------------------------------------------------------------------------------------------------+

-- Sample data
CREATE TABLE customer_reviews (
    review STRING
);

INSERT INTO customer_reviews VALUES
    ('The new laptop has excellent battery life but the keyboard feels cheap.'),
    ('Delivery was fast. The headphones sound quality is amazing for the price.'),
    ('Returned the monitor due to dead pixels. Customer service was helpful though.');

-- Extract structured information from the customer reviews in the table
SET odps.sql.ai.treat.as.common.model=true;
SET odps.namespace.schema=true;

SELECT
    review,
    AI_EXTRACT(
        bigdata_public_modelset.default.`Qwen3-4B-GGUF`,
        DEFAULT_VERSION,
        review,
        ARRAY('product', 'issue', 'sentiment')
    ) AS extracted_info
FROM customer_reviews;

-- Result:
+-----------------------------------------------------------------------------+-------------------------------------------------------------------------+
| review                                                                      | extracted_info                                                          |
+-----------------------------------------------------------------------------+-------------------------------------------------------------------------+
| The new laptop has excellent battery life but the keyboard feels cheap.     | {"product":"laptop","issue":"keyboard feels cheap","sentiment":"mixed"}   |
| Delivery was fast. The headphones sound quality is amazing for the price.   | {"product":"headphones","issue":null,"sentiment":"positive"}              |
| Returned the monitor due to dead pixels. Customer service was helpful though. | {"product":"monitor","issue":"dead pixels","sentiment":"negative"}        |
+-----------------------------------------------------------------------------+-------------------------------------------------------------------------+