AI Functions Overview

Supported functions

AI parameters

ApsaraDB for SelectDB uses resources to centrally manage AI API access, ensuring API key security and enabling permission controls.

Compatibility

This feature is available only for instances of version 5.0 and later.

Providers

The following providers are directly supported: OpenAI, Anthropic, Gemini, DeepSeek, Local, Moonshot, MiniMax, Zhipu, Qwen, and Baichuan.

If your provider is not listed but its API is compatible with OpenAI, Anthropic, or Gemini, select the corresponding type for the ai.provider_type parameter. This setting only affects the API request format that ApsaraDB for SelectDB constructs.

Quick start

1. Configure an AI resource.

   Example 1:

   CREATE RESOURCE 'openai_example'
   PROPERTIES (
       'type' = 'ai',
       'ai.provider_type' = 'openai',
       'ai.endpoint' = 'https://api.openai.com/v1/chat/completions',
       'ai.model_name' = 'gpt-4.1',
       'ai.api_key' = 'xxxxx'
   );

   Example 2:

   CREATE RESOURCE 'deepseek_example'
   PROPERTIES (
       'type'='ai',
       'ai.provider_type'='deepseek',
       'ai.endpoint'='https://api.deepseek.com/chat/completions',
       'ai.model_name' = 'deepseek-chat',
       'ai.api_key' = 'xxxxx'
   );

2. (Optional) Set the default resource.

   SET default_ai_resource='ai_resource_name';

3. Execute an SQL query.

   Case 1:

   Assume you have a table that stores document content related to databases:

   CREATE TABLE doc_pool (
       id  BIGINT,
       c   TEXT
   ) DUPLICATE KEY(id)
   DISTRIBUTED BY HASH(id) BUCKETS 10
   PROPERTIES (
       "replication_num" = "1"
   );

   To find the 10 records most relevant to SelectDB, run the following query:

   SELECT
       c,
       CAST(AI_GENERATE(CONCAT('Please score the relevance of the following document content to SelectDB, with a floating-point number from 0 to 10, output only the score. Document:', c)) AS DOUBLE) AS score
   FROM doc_pool ORDER BY score DESC LIMIT 10;

   This query uses AI to score each document's relevance to SelectDB and returns the top 10 results, ordered by the highest scores.

   +---------------------------------------------------------------------------------------------------------------+-------+
   | c                                                                                                             | score |
   +---------------------------------------------------------------------------------------------------------------+-------+
   | SelectDB is a lightning-fast MPP analytical database that supports sub-second multidimensional analytics. |   9.5 |
   | In SelectDB, materialized views can automatically route queries, saving significant compute resources.           |   9.2 |
   | SelectDB's vectorized execution engine boosts aggregation query performance by 5–10×.                            |   9.2 |
   | SelectDB Stream Load supports second-level real-time data ingestion.                                      |   9.2 |
   | SelectDB cost-based optimizer (CBO) generates better distributed execution plans.                                |   8.5 |
   | Enabling the SelectDB Pipeline execution engine noticeably improves CPU utilization.                             |   8.5 |
   | SelectDB supports Hive external tables for federated queries without moving data.                                |   8.5 |
   | SelectDB Light Schema Change lets you add or drop columns instantly.                                             |   8.5 |
   | SelectDB AUTO BUCKET automatically scales bucket count with data volume.                                         |   8.5 |
   | Using SelectDB inverted indexes enables second-level log searching.                                              |   8.5 |
   +---------------------------------------------------------------------------------------------------------------+-------+

   Case 2:

   The following tables simulate candidate resumes and job requirements in a recruiting scenario:

   CREATE TABLE candidate_profiles (
       candidate_id INT,
       name         VARCHAR(50),
       self_intro   VARCHAR(500)
   )
   DUPLICATE KEY(candidate_id)
   DISTRIBUTED BY HASH(candidate_id) BUCKETS 1
   PROPERTIES (
       "replication_num" = "1"
   );
   
   CREATE TABLE job_requirements (
       job_id   INT,
       title    VARCHAR(100),
       jd_text  VARCHAR(500)
   )
   DUPLICATE KEY(job_id)
   DISTRIBUTED BY HASH(job_id) BUCKETS 1
   PROPERTIES (
       "replication_num" = "1"
   );
   
   INSERT INTO candidate_profiles VALUES
   (1, 'Alice', 'I am a senior backend engineer with 7 years of experience in Java, Spring Cloud and high-concurrency systems.'),
   (2, 'Bob',   'Frontend developer focusing on React, TypeScript and performance optimization for e-commerce sites.'),
   (3, 'Cathy', 'Data scientist specializing in NLP, large language models and recommendation systems.');
   
   INSERT INTO job_requirements VALUES
   (101, 'Backend Engineer', 'Looking for a senior backend engineer with deep Java expertise and experience designing distributed systems.'),
   (102, 'ML Engineer',      'Seeking a data scientist or ML engineer familiar with NLP and large language models.');
   

   You can use AI_FILTER to perform a semantic match between job requirements and candidate introductions to find suitable candidates:

   SELECT
       c.candidate_id, c.name,
       j.job_id, j.title
   FROM candidate_profiles AS c
   JOIN job_requirements AS j
   WHERE AI_FILTER(CONCAT('Does the following candidate self-introduction match the job description?', 
                   'Job: ', j.jd_text, ' Candidate: ', c.self_intro));

   +--------------+-------+--------+------------------+
   | candidate_id | name  | job_id | title            |
   +--------------+-------+--------+------------------+
   |            3 | Cathy |    102 | ML Engineer      |
   |            1 | Alice |    101 | Backend Engineer |
   +--------------+-------+--------+------------------+

How it works