# rds-ml

Part of **RDS**

# ApsaraDB RDS Advanced Database Features

## Capabilities Overview

| Sub-capability | Models | Calling Mode | Description |
|----------------|--------|--------------|-------------|
| Train XGBoost Model | xgboost.gbtree | Synchronous | Train XGBoost machine learning models using RDS APIs. |
| Manage SQLFlow Operations | — | Synchronous | Perform various SQLFlow operations including training, prediction, and model management. |
| Extend Varbit Functionality | — | Synchronous | Use the varbitx extension to enhance PostgreSQL varbit operations. |
| Invoke AI Models via rds_ai | qwen-plus, qwen-max, qwen-turbo, +4 more | Synchronous | Use the rds_ai extension to call LLMs for text generation, embeddings, reranking, and RAG question answering. |
| Manage pg_hba.conf Configuration | — | Synchronous | Modify, query, and view modification history of the pg_hba.conf file for PostgreSQL instances. |

## Model Selection Guide

### Train XGBoost Model

| Model ID | Calling Mode |
|----------|--------------|
| xgboost.gbtree | Synchronous |

### Invoke AI Models via rds_ai

| Model ID | Calling Mode |
|----------|--------------|
| qwen-plus | Synchronous |
| qwen-max | Synchronous |
| qwen-turbo | Synchronous |
| text-embedding-v3 | Synchronous |
| gte-rerank | Synchronous |
| text-embedding-v2 | Synchronous |
| deepseek-r1 | Synchronous |

## API Calling Patterns

### Authentication
The primary authentication method is **Bearer Token** via the `Authorization` header.

- Header format: `Authorization: Bearer <your_api_key>`
- Environment variable: `DASHSCOPE_API_KEY`
- Note: Some RDS management APIs (e.g., pg_hba.conf) may use Alibaba Cloud AccessKey-based authentication, but for AI/ML features (`rds_ai`, SQLFlow), the Bearer token with a DashScope API key is required.

### Service Endpoint
APIs use region-specific endpoints:

- Base pattern: `https://{service}.{region}.aliyuncs.com`
- Common regions: `cn-hangzhou`, `cn-shanghai`, `cn-beijing`
- For AI model APIs (via `rds_ai`):
  - China: `https://dashscope.aliyuncs.com`
  - International: `https://dashscope-intl.aliyuncs.com`
- For RDS instance management (e.g., pg_hba.conf): `https://rds.aliyuncs.com`

### Synchronous Pattern
All operations in this domain are synchronous:
1. Send a single request (SQL statement or HTTP API call)
2. Wait for immediate JSON or tabular response
3. No polling or async task ID handling is required

For SQL-based operations (SQLFlow, `rds_ai`, `varbitx`), execute standard SQL queries via your PostgreSQL client. The RDS instance handles the underlying API calls internally.

For HTTP-based RDS management APIs (e.g., `ModifyPGHbaConfig`), send a GET/POST request to the RDS endpoint with required parameters.

## Parameter Reference

### Train XGBoost Model

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|------------|
| objective | string | false | null | one of: reg:squarederror, reg:logistic, binary:logistic, multi:softmax, multi:softprob | Specifies the learning task and the corresponding loss function. For regression tasks, use 'reg:squarederror'. |
| train.num_boost_round | integer | false | 100 | range 1-1000 | The number of boosting rounds (trees) to train. Higher values may improve accuracy but increase training time. |
| COLUMN | array | true | null | max 100 columns | List of feature columns used for training the model. |
| LABEL | string | true | null | — | The target column to predict. |
| INTO | string | true | null | valid database schema and table name | The destination table or path where the trained model will be stored. |

### Invoke AI Models via rds_ai

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|------------|
| model_name | text | false | null | — | The name of the model to invoke. If omitted, the default model is used. |
| content | text | true | null | — | The input text to embed or the prompt to send to the model. |
| args | jsonb | false | {} | — | Additional parameters for the model, such as temperature, top_p, etc. |
| question | text | true | null | — | The input text for the similarity search. |
| source_schema | text | true | null | — | The schema of the table to search. |
| source_table | text | true | null | — | The name of the table to search. |
| chunk_col | text | true | null | — | The name of the column that contains the original text chunks. |
| vector_col | text | true | null | — | The name of the vector column to search. |
| topn | int | false | 10 | range 1-100 | The number of nearest neighbors to return. |
| embed_args | jsonb | false | {} | — | Specifies parameters for the text-to-vector conversion. |
| distance_type | text | false | L2 | one of: L1, L2, cosine, inner product | The distance metric for calculating vector similarity. |
| prompt_model | text | false | null | — | The prompt model to use for generating answers in RAG. |
| prompt_args | jsonb | false | {} | — | Parameters for prompt execution. |

### Manage pg_hba.conf Configuration

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|------------|
| Action | String | true | null | — | The operation that you want to perform. Set the value to ModifyPGHbaConfig / DescribePGHbaConfig / DescribeModifyPGHbaConfigLog. |
| DBInstanceId | String | true | null | — | The ID of the instance. |
| OpsType | String | true | null | one of: add\|delete\|modify\|update | The method that you use to modify the pg_hba.conf file. |
| HbaItem.N.Type | String | true | null | one of: host\|hostssl\|hostnossl | The connection type of record N. |
| HbaItem.N.Database | String | true | all | — | The name of the database for record N. |
| HbaItem.N.PriorityId | Integer | true | null | range 0-10000 | The priority of record N. |
| HbaItem.N.Address | String | true | 0.0.0.0/0 | — | The IP addresses from which the specified users can access the specified databases. |
| HbaItem.N.User | String | true | null | — | The user who is allowed to access the specified databases. |
| HbaItem.N.Method | String | true | null | one of: trust\|reject\|scram-sha-256\|md5\|password\|gss\|sspi\|ldap\|radius\|cert\|pam | The authentication method. |
| HbaItem.N.Mask | String | false | 0 | — | The mask for record N. |
| HbaItem.N.Option | String | false | null | — | Optional. The value varies based on the authentication method. |

## Code Examples

### Train XGBoost Regression Model - SQL - all

```sql
SELECT * FROM boston.train
TO TRAIN xgboost.gbtree
WITH
    objective="reg:squarederror",
    train.num_boost_round = 30
COLUMN crim, zn, indus, chas, nox, rm, age, dis, rad, tax, ptratio, b, lstat
LABEL medv
INTO sqlflow_models.my_xgb_regression_model;
```

### Generate Text with Default LLM - SQL - all

```sql
SELECT rds_ai.prompt('Give me a recipe for a dish made with carrots, potatoes, and eggplants.');
```

### Retrieve Similar Documents for RAG - SQL - all

```sql
SELECT * FROM rds_ai.retrieve('Why is PostgreSQL considered the most advanced open source database', 'public', 'test_rag', 'chunk', 'embedding');
```

### Add LDAP Authentication Rule to pg_hba.conf - Bash - all

```bash
http(s)://rds.aliyuncs.com/?Action=ModifyPGHbaConfig
&DBInstanceId=pgm-bp1lymyn1v3ifjyj
&OpsType=add
&HbaItem.1.Address=0.0.0.0/0
&HbaItem.1.Database=all
&HbaItem.1.Method=ldap
&HbaItem.1.PriorityId=2
&HbaItem.1.Type=host
&HbaItem.1.User=ldapuser
&HbaItem.1.Option=ldapserver=172.16.XX.XX ldapbasedn="CN=Users,DC=pgsqldomain,DC=net" ldapbinddn="CN=pgadmin,CN=Users,DC=pgsqldomain,DC=net" ldapbindpasswd="test_123456" ldapsearchattribute="sAMAccountName"
```

### Use Varbit Extension for Bit Counting - SQL - all

```sql
SELECT bit_count('1111000011110000', 1, 5, 4);
```

### Evaluate a Trained Model - SQL - all

```sql
SELECT * FROM iris.test 
TO EVALUATE sqlflow_models.my_dnn_model 
WITH validation.metrics = Accuracy 
LABEL class  
INTO sqlflow_models.evaluate_result_table;
```

## Response Format

```json
{"choices":[{"message":{"content":"Hello! I'm Qwen, a large language model developed by Alibaba Cloud."}}],"usage":{"prompt_tokens":15,"completion_tokens":14,"total_tokens":29}}
```

**Key Fields**:
- `choices[].message.content` — The generated text or answer from the model
- `usage.prompt_tokens` — Number of tokens in the input prompt
- `usage.completion_tokens` — Number of tokens in the generated output
- `usage.total_tokens` — Total tokens consumed (prompt + completion)

## Error Handling

| Error Code | Description | Recommended Action |
|-----------|-------------|-------------------|
| 400 | Invalid request format. Check SQL syntax and required parameters. | Validate SQL syntax, ensure all required parameters are provided. |
| 401 | Unauthorized – Invalid or missing API key. | Verify that `DASHSCOPE_API_KEY` is set and valid. |
| 403 | Forbidden – Insufficient permissions to access the model or resource. | Ensure your account has permissions to use the model or modify the RDS instance. |
| 404 | Dataset or model not found. Verify table names and paths. | Check that the referenced tables, models, or instance IDs exist. |
| 429 | Too Many Requests – Rate limit exceeded. Wait before retrying. | Implement exponential backoff; reduce request frequency. |
| 500 | Internal Server Error – An unexpected error occurred on the server side. | Retry the request; if persistent, contact support. |
| 502 | Bad Gateway – The upstream service failed to respond. | Retry after a short delay; check DashScope service status. |
| 503 | Service Unavailable – The service is temporarily down or overloaded. | Wait and retry later. |

### Rate Limits & Retry
- **AI Models (`rds_ai`)**: 100 QPS per model, max 8192 tokens per request
- **XGBoost Training**: 10 concurrent model training jobs per user
- **pg_hba.conf APIs**: 100 QPS
- **Retry Strategy**: Use exponential backoff (e.g., 1s, 2s, 4s, 8s) for 429/5xx errors. Respect `Retry-After` header if present.

## Requirements

- **DashScope API Key**: Required for `rds_ai` functions. Set via `export DASHSCOPE_API_KEY=your_key`
- **RDS Instance Requirements**:
  - For `rds_ai`: PostgreSQL 16 (minor version ≥ 20241230) or PostgreSQL 14/15/17 (≥ 20250430) or PostgreSQL 18 (≥ 20251130)
  - For `varbitx`: PostgreSQL 10 or 11
- **Extensions**: Install `rds_ai`, `varbitx`, or `pgvector` via RDS console before use

## FAQ

Q: How do I authenticate when using the `rds_ai` extension?
A: You must configure a DashScope API key in your RDS instance settings. The extension uses this key internally—no authentication is needed in your SQL queries.

Q: Can I use custom models with `rds_ai`?
A: Yes. You can specify a custom model name in the `model_name` parameter if it's deployed in Alibaba Cloud Model Studio and accessible via DashScope.

Q: Why am I getting a "current database minor version does not support the operation" error?
A: Your RDS PostgreSQL instance version is too old. Upgrade to a supported minor version (check prerequisites for each feature).

Q: Are SQLFlow operations charged separately from regular SQL queries?
A: Yes. SQLFlow training and prediction are billed per request, even though they use SQL syntax.

Q: How do I view my current pg_hba.conf rules?
A: Use the `DescribePGHbaConfig` API or run `SHOW hba_file;` in psql (note: direct file access may be restricted in RDS).

## Pricing & Billing

### Billing Model
- **Per-request pricing** for AI models, SQLFlow, and pg_hba.conf APIs
- **Free** for `varbitx` extension usage

### Price Reference

| Model/Specification | Input Price | Output Price |
|---------------------|------------|-------------|
| qwen-plus | 0.002 /tokens | 0.004 /tokens |
| qwen-max | 0.004 /tokens | 0.008 /tokens |
| qwen-turbo | 0.001 /tokens | 0.002 /tokens |
| text-embedding-v3 | 0.0001 /tokens | — |
| gte-rerank | 0.0002 /tokens | — |
| xgboost.gbtree | 0.0002 / | 0.0001 / |

### Free Tier
- **AI Models**: 100,000 tokens/month free for new users
- **XGBoost**: 100 free training and prediction requests per month
- **pg_hba.conf APIs**: 100 free calls per month

### Usage Limits
- **AI Models**: 100 QPS, max 8192 tokens per request
- **XGBoost**: 10 concurrent jobs, max 10,000 rows per request
- **pg_hba.conf APIs**: 100 QPS

### Billing Notes
- Async tasks are billed on completion; minimum 1-hour charge for long-running operations (not applicable to current synchronous APIs)
- Model training and prediction are billed separately
- API calls are counted per request, regardless of result size