In-Database AI 库内模型服务

SELECT FEATURE_SELECTION(
    METHOD = 'iv',
    TARGET = 'is_default',
    OPTIONS (bins = 10, threshold = 0.02)
)
AS SELECT age, income, debt_ratio, credit_score, is_default FROM loan_data;

-- 返回:
-- +--------------+--------+----------------+
-- | feature      | iv     | recommendation |
-- +--------------+--------+----------------+
-- | credit_score | 0.8523 | 强预测力        |
-- | debt_ratio   | 0.3214 | 中等预测力      |
-- | income       | 0.1056 | 弱预测力        |
-- | age          | 0.0089 | 无预测力（建议剔除）|
-- +--------------+--------+----------------+

CREATE MODEL sales_predictor
ALGORITHM = 'lightgbm'
OPTIONS (n_estimators = 200, learning_rate = 0.05)
PREPROCESS (
    NORMALIZE(temperature, humidity, method = 'minmax'),
    FILL_NULL(promotion_flag, value = 0),
    ONEHOT(store_type, weekday),
    LABEL_ENCODE(region, order = ['east', 'south', 'west', 'north'])
)
AS SELECT temperature, humidity, promotion_flag, store_type, weekday, region, sales
FROM store_sales
WHERE year >= 2024;

CREATE MODEL <model_name>
ALGORITHM = '<algorithm_name>'
[ OPTIONS ( <key> = <value> [, ...] ) ]
[ PREPROCESS ( <preprocess_expr> [, ...] ) ]
AS <select_statement>
[ ASYNC ];

CREATE MODEL house_price_lgb
ALGORITHM = 'lightgbm'
OPTIONS (n_estimators = 100, learning_rate = 0.1)
AS SELECT area, rooms, age, price FROM house_data;

CREATE MODEL my_model
ALGORITHM = 'lightgbm'
AS SELECT * FROM training_data;

-- 返回:
-- +------------+--------+-----------+------------+
-- | model_name | status | algorithm | duration_s |
-- +------------+--------+-----------+------------+
-- | my_model   | READY  | lightgbm  | 12.3       |
-- +------------+--------+-----------+------------+

CREATE MODEL my_model
ALGORITHM = 'lightgbm'
AS SELECT * FROM training_data
ASYNC;

-- 返回:
-- +---------+----------+
-- | task_id | status   |
-- +---------+----------+
-- | t_a1b2c | CREATING |
-- +---------+----------+

SHOW TRAIN JOB 't_a1b2c';

-- 返回:
-- +---------+--------+----------+------------+
-- | task_id | status | progress | duration_s |
-- +---------+--------+----------+------------+
-- | t_a1b2c | READY  | 100%     | 45.2       |
-- +---------+--------+----------+------------+

IMPORT MODEL <model_name>
FROM '<cos_path>'
INPUT ( <column_name> <data_type> [, ...] )
OUTPUT ( <column_name> <data_type> [, ...] )
[ PREPROCESS ( <preprocess_expr> [, ...] ) ];

IMPORT MODEL external_price_model
FROM 'cos://ml-models/production/price_v3.onnx'
INPUT (
    area        FLOAT,
    rooms       INT,
    floor       INT,
    age         FLOAT,
    has_parking INT
)
OUTPUT (
    predicted_price FLOAT
);

-- 返回:
-- +------------------------+--------+-----------+
-- | model_name             | status | source    |
-- +------------------------+--------+-----------+
-- | external_price_model   | READY  | cos_import|
-- +------------------------+--------+-----------+

IMPORT MODEL external_score_model
FROM 'cos://ml-models/production/score_v2.onnx'
INPUT (
    age         FLOAT,
    income      FLOAT,
    city        STRING
)
OUTPUT (
    risk_score  FLOAT
)
PREPROCESS (
    NORMALIZE(age, income, method = 'minmax'),
    ONEHOT(city)
);

EVALUATE MODEL <model_name>
[ METRICS ( <metric_name> [, ...] ) ]
AS <select_statement>;

EVALUATE MODEL house_price_lgb
METRICS (rmse, r2, mae)
AS SELECT area, rooms, age, price FROM test_data;

-- 返回:
-- +------------------+-------+------+--------+
-- | model_name       | rmse  | r2   | mae    |
-- +------------------+-------+------+--------+
-- | house_price_lgb  | 12.45 | 0.93 | 8.72   |
-- +------------------+-------+------+--------+

SELECT PREDICT(<model_name>, <col1>, <col2>, ...) AS <alias>
[, <other_columns>]
FROM <table>
[ WHERE <condition> ];

-- 对单条记录进行推理
SELECT id, PREDICT(house_price_lgb, area, rooms, age) AS predicted_price
FROM house_data
WHERE id = 1001;

-- 返回:
-- +------+-----------------+
-- | id   | predicted_price |
-- +------+-----------------+
-- | 1001 | 356.8           |
-- +------+-----------------+

-- 对小批量数据进行同步推理
SELECT id, PREDICT(house_price_lgb, area, rooms, age) AS predicted_price
FROM house_data
WHERE city = 'shanghai'
LIMIT 100;

CREATE PREDICT JOB
USING MODEL <model_name>
INPUT ( <select_statement> )
OUTPUT TO '<target>';

-- 结果写入 COS
CREATE PREDICT JOB
USING MODEL house_price_lgb
INPUT (SELECT area, rooms, age FROM house_data WHERE city = 'beijing')
OUTPUT TO 'cos://ml-results/batch_predict_20260520.csv';

-- 返回:
-- +---------+----------+
-- | task_id | status   |
-- +---------+----------+
-- | t_x7y8z | RUNNING  |
-- +---------+----------+

-- 结果写入数据库表
CREATE PREDICT JOB
USING MODEL house_price_lgb
INPUT (SELECT id, area, rooms, age FROM house_data)
OUTPUT TO 'mydb.prediction_results';

SHOW PREDICT JOB '<task_id>';

-- 返回:
-- +---------+-----------+----------+------------+----------------------------------+
-- | task_id | status    | progress | duration_s | output_location                  |
-- +---------+-----------+----------+------------+----------------------------------+
-- | t_x7y8z | COMPLETED | 100%     | 128.5      | cos://ml-results/batch_...csv    |
-- +---------+-----------+----------+------------+----------------------------------+

CREATE LLM FUNCTION <function_name>
ENDPOINT = '<service_url>'
OPTIONS (
    api_key    = '<key>',
    model      = '<model_name>',
    timeout_ms = 30000,
    max_tokens = 1024
);

-- 文本摘要
SELECT id, LLM_INVOKE(my_llm, concat('请总结以下内容：', content)) AS summary
FROM articles
WHERE id = 1001;

-- 情感分析
SELECT id, title, LLM_INVOKE(my_llm, concat('判断以下评论的情感倾向（正面/负面/中性）：', comment)) AS sentiment
FROM user_reviews
LIMIT 50;

-- 使用 PROMPT 模板
SELECT id, LLM_INVOKE(my_llm, PROMPT('extract_entities', content)) AS entities
FROM documents;

-- 查看已注册的 LLM 函数
SHOW LLM FUNCTIONS;

-- 查看函数详情
DESCRIBE LLM FUNCTION my_llm;

-- 删除 LLM 函数
DROP LLM FUNCTION my_llm;

SHOW MODELS;

-- 返回:
-- +------------------------+-----------+------------------+---------------------+--------+
-- | model_name             | algorithm | source           | created_at          | status |
-- +------------------------+-----------+------------------+---------------------+--------+
-- | house_price_lgb        | lightgbm  | trained          | 2026-05-20 10:30:00 | READY  |
-- | house_price_lr         | linear_reg| trained          | 2026-05-19 14:00:00 | READY  |
-- | external_price_model   | onnx      | cos_import       | 2026-05-18 09:15:00 | READY  |
-- +------------------------+-----------+------------------+---------------------+--------+

DESCRIBE MODEL house_price_lgb;

-- 返回:
-- +----------------+----------------------------------------------------+
-- | property       | value                                              |
-- +----------------+----------------------------------------------------+
-- | model_name     | house_price_lgb                                    |
-- | algorithm      | lightgbm                                           |
-- | status         | READY                                              |
-- | source         | trained                                            |
-- | created_at     | 2026-05-20 10:30:00                                |
-- | features       | area (FLOAT), rooms (INT), age (FLOAT)             |
-- | label          | price (FLOAT)                                      |
-- | options        | n_estimators=100, learning_rate=0.1                 |
-- | preprocess     | NORMALIZE(area, age, method='minmax')               |
-- | model_size     | 2.3 MB                                             |
-- | training_rows  | 50000                                              |
-- | train_duration | 12.3s                                              |
-- +----------------+----------------------------------------------------+

SHOW JOBS;

-- 返回:
-- +---------+----------+------------------+-----------+---------------------+
-- | task_id | type     | model_name       | status    | created_at          |
-- +---------+----------+------------------+-----------+---------------------+
-- | t_a1b2c | TRAIN    | my_model         | COMPLETED | 2026-05-20 10:00:00 |
-- | t_x7y8z | PREDICT  | house_price_lgb  | RUNNING   | 2026-05-20 11:30:00 |
-- +---------+----------+------------------+-----------+---------------------+

DROP MODEL <model_name>;

DROP MODEL house_price_lr;

-- 返回:
-- +----------------+--------+
-- | model_name     | status |
-- +----------------+--------+
-- | house_price_lr | DROPPED|
-- +----------------+--------+