alibabacloud-adb-smart-analyst

ADB MySQL intelligent analyst Skill

When to use

The user raises a data analysis question (such as "help me look at the sales data", "check the orders in Beijing", "the shipment volume of the last week")
The user wants to create, modify, or delete a semantic view
The user wants to explore the database table structure or metadata
The user mentions ADB MySQL, AnalyticDB, or semantic views

Prerequisites

ADB connection (required for all methods)

Python 3.6+, and pymysql package is installed
Install dependencies:uv pip install pymysql
Optional dependencies:cryptography(needed only in the following scenarios, install as needed uv pip install cryptography）：
- The server uses caching_sha2_password(MySQL 8.0 default) or sha256_password authentication plugin
- The connection uses TLS/SSL
- The default ADB MySQL mostly uses mysql_native_password, usually no installation is needed; if the connection reports RuntimeError: 'cryptography' package is required ..., then install it additionally
The following environment variables must be set (required regardless of which method is used, because the final SQL execution is all through the SQL connection):
- ADB_MYSQL_HOST - ADB MySQL cluster address
- ADB_MYSQL_PORT - ADB MySQL port (default: 3306)
- ADB_MYSQL_DATABASE - Default database name
- ADB_MYSQL_USER - Database username
- ADB_MYSQL_PASSWORD - Database password

Additional conditions for the OpenAPI method (when the semantic layer SQL method is unavailable)

When the semantic functionality of the SQL method is unavailable (such as missing semantic_views metadata table, SQL hint semantic operation failure), the semantic operation switches to the OpenAPI method. Data query execution is still completed through the above SQL connection.

aliyun CLI installed
adb CLI plugininstalled
aliyun CLI credentialsalready configured by the user. Can be done through aliyun configure to configure AK/SK, but it is not recommended for the Agent to proactively execute configuration commands or enter keys on behalf of the user; the Agent only detects and uses.
Must be set:
- ADB_CLUSTER_ID - ADB cluster ID (required, such as am-bp1xxxxxxxx）
- ADB_REGION - Region (optional, can be omitted if already included in the CLI profile, such as cn-hangzhou）
Credential detection preferentially uses aliyun configure list or lightweight OpenAPI probing; do not rely only on ALIBABA_CLOUD_* environment variables, because the default profile can also provide valid credentials.
RAM permission requirements (the RAM user or role corresponding to the current AK/SK needs to be authorized for the following Actions):
- adb:CreateSemanticView
- adb:DeleteSemanticView
- adb:ReplaceSemanticView
- adb:GenerateSqlBySemanticSql
- adb:RenameSemanticView
- adb:SearchSemanticViews
- adb:GetSemanticView

Architecture

方式 A（主方式 - 全 SQL）：
  用户 → Agent → Skill → PyMySQL → ADB Engine

方式 B（OpenAPI 语义 + SQL 执行）：
  语义操作：用户 → Agent → Skill → aliyun CLI → OpenAPI Gateway → ADB Engine
  数据执行：用户 → Agent → Skill → PyMySQL → ADB Engine

Both methods execute the final query through the SQL connection; the only difference is the implementation method of the semantic operations (SQL hint vs aliyun CLI). The Skill prioritizes the SQL method, and automatically switches to OpenAPI when the SQL semantic functionality is unavailable (see "Method Selection Strategy" below for details).

Under the OpenAPI method, alter_semantic_view --operation set_comment is unavailable (planned to be added). The Skill script automatically adds the SQL hint, so the Agent does not need to add a hint when generating SQL.

Method Selection Strategy

The Skill adopts the SQL priority + semantic layer automatic switching strategy, and the selection process is as follows:

Check SQL connection: Verify whether the ADB connection environment variables (ADB_MYSQL_HOST , etc.) are set and the connection is available
- Connection failed (missing environment variables, connection refused, authentication failed, timeout) → report an error prompting to check the ADB connection configuration,neither method is available
Try SQL-based semantic functionality: after the SQL connection is available, try to perform semantic operations via SQL hints
SQL semantic functionality works → use the SQL method (full functionality)
SQL semantic functionality failed(missing semantic_views metadata table, SQL hint semantic operation error) → check the prerequisites for the OpenAPI method:
- ADB_CLUSTER_ID environment variable exists? No → report an error prompting to set ADB_CLUSTER_ID
- CLI environment completeness check:
  - aliyun executable file exists? (which aliyun) No → report an error prompting to install the aliyun CLI
  - adb plugin available? (aliyun adb --api-version 2021-12-01 --help) No → report an error prompting to install the adb plugin (aliyun plugin install --names adb）
  - credentials valid? (aliyun configure list or a lightweight probe call for verification) No → prompt the user to run aliyun configure themselves to configure AK/SK
- all satisfied → switch to the OpenAPI method (semantic operations via OpenAPI, data execution and exploration via the SQL connection)
Method caching: the method selection result is cached at the Session level, and subsequent requests reuse it directly without repeating the detection. To switch methods (e.g., the user has newly configured the SQL semantic layer), a new Session is required

Two-phase data query workflow

Phase 1 - Intent grounding (semantic context awareness)

Extract keywords from the user's question
Call search_semantic_views --top-k 3 to perform vector similarity search (top-k is fixed at 3 and must not be adjusted)
User question decomposition: decompose the natural language question into "target metric terms" (what data the user wants to see) and "filter condition terms" (filter conditions)
Semantic object mapping: for each candidate view, identify dimensions/metrics/facts from the name、description、synonyms、expr fields in the YAML definition, and determine whether the user's metric terms and condition terms can be mapped
Decision:
- Only one view can complete the mapping(or only one candidate has score >= 0.5): decide on its own, output the mapping table
- Multiple views can map and one is significantly better than the others(covers >= 2 more semantic objects): choose the best, output the mapping table
- Multiple views have similar mapping coverage: show the candidates + their respective mapping relationships, and let the user choose
- No view can complete a meaningful mapping: ask the user to clarify, and show the closest candidates and their partial coverage
- All candidates have score < 0.5: ask the user to further describe the question or add keywords
- No results: ask the user to describe the question in another way or provide more context
Clarification-recheck loop: in all cases where the final semantic view cannot be determined (user selection, clarification, adding keywords), after the user replies, steps 1-5 must be re-executed (re-extract keywords → re- search_semantic_views → re-map → re-decide), rather than directly reusing the previous candidate results
Exploration mode downgrade conditions(downgrade if any one is met):
- The semantic view still cannot be determined after 3 rounds of clarification-recheck cycles
- search_semantic_views Call error (e.g., connection failure, semantic service unavailable) When downgrading, the reason for termination must be clearly communicated

Hard rules for view selection:

Deterministic reasoning must be provided: cite specific semantic object name/description/synonyms/expr in the form of a "mapping table" to prove the correspondence between user keywords and view fields
Must not fabricate metrics, dimensions, or facts that do not exist in the YAML definition
Must not arbitrarily select a view without sufficient mapping basis
Must not, without description or synonyms support, forcibly establish a synonymous relationship between user keywords and semantic object names
If the same user keyword can reasonably map to different semantic objects of multiple candidate views, do not decide on your own; you must present the candidates for the user to choose
If a user keyword cannot be mapped based on YAML fields, it must be marked as "not covered"
Mapping table format when deciding on your own:已选择视图 {schema}.{view_name}（score {x}）：「{关键词}」→ {对象类型} {name}
When presenting candidates, include a comparison of the mapping relationships of each view to help the user make a differentiated choice

Phase 2 - Deterministic execution

Generate semantic SQL based on the YAML definition of the selected view
Execute via execute_sql (default semantic_rewrite=true)
Present the results, including: data table, SQL used, analysis conclusions, visualization suggestions, directions for further analysis

Differences in Phase 2 under the OpenAPI approach:

Use the generate-sql-by-semantic-sql command to perform semantic SQL rewriting and obtain the rewritten physical SQL
Execute the rewritten physical SQL via execute_sql --no-semantic-rewrite to obtain query results
The presented content is consistent with the SQL approach: data table, semantic SQL + physical SQL, analysis conclusions, visualization suggestions, directions for further analysis

Downgrade strategy - Data exploration mode

When there is no matching semantic view, use progressive drill-down exploration:

Four-phase exploration process

Phase 1 - Schema discovery:

uv run scripts/adb_analyst.py list_databases

Phase 2 - Table discovery and scale assessment:

uv run scripts/adb_analyst.py explore_table_metadata --operation list_tables --database logistics
uv run scripts/adb_analyst.py explore_table_metadata --operation table_statistics --database logistics --table shipments

Phase 3 - Structural analysis:

uv run scripts/adb_analyst.py explore_table_metadata --operation describe_table --database logistics --table shipments
uv run scripts/adb_analyst.py explore_table_metadata --operation partition_info --database logistics --table shipments
uv run scripts/adb_analyst.py explore_table_metadata --operation show_create_table --database logistics --table shipments
uv run scripts/adb_analyst.py explore_table_metadata --operation index_info --database logistics --table shipments

Phase 4 - Safe sampling and querying:

uv run scripts/adb_analyst.py explore_table_metadata --operation safe_sample --database logistics --table shipments --columns shipment_id,ship_date,status --limit 5
uv run scripts/adb_analyst.py explore_table_metadata --operation explain --database logistics --table shipments --sql "SELECT COUNT(*) FROM logistics.shipments WHERE ship_date >= '2026-05-01'"
uv run scripts/adb_analyst.py execute_sql --sql "SELECT destination_city, COUNT(*) AS cnt FROM \`logistics\`.\`shipments\` WHERE ship_date >= '2026-05-10' GROUP BY destination_city LIMIT 20" --no-semantic-rewrite

Semantic SQL rules

When generating semantic SQL for the selected view, the following rules must be followed:

Rule	Description	Example
Metrics must use `AGG()`	Aggregate functions mapped by the semantic engine	`AGG(order_count)`
Dimensions in SELECT must appear in GROUP BY	Consistency requirement	`SELECT city ... GROUP BY city`
WHERE can only use dimensions or fact fields	Row-level filtering	`WHERE order_date >= '2025-01-01'`
HAVING can only use metrics	Post-aggregation filtering	`HAVING AGG(order_count) > 100`
Must include LIMIT	Prevent the result set from being too large	`LIMIT 100`
Field names come from the `name` field in YAML	Must not fabricate field names	Use the value of `name:` in YAML
Temporary metrics are allowed	Use aggregate functions on dimensions or fact fields	`COUNT(DISTINCT customer_name)`

Semantic SQL syntax:

SELECT [ DISTINCT ]
  {
    [<qualifiers>.]<dimension_or_fact> |
    AGG( [<qualifiers>.]<metric> ) |
    <aggregate_function>( [<qualifiers>.]<dimension_or_fact> )
  }
  [ , ... ]
FROM <semantic_view> [ AS <alias> ]
[ WHERE <expr_using_dimensions_or_facts> ]
[ GROUP BY <expr_using_dimensions_or_facts> [ , ... ] ]
[ HAVING <expr_using_metrics> ]
[ ORDER BY ... ]
LIMIT <n>

Security constraints

DML is prohibited（INSERT/UPDATE/DELETE）
Do not allow DROP DATABASE/TABLE
execute_sql Only SELECT statements are accepted
DDL operations (creating/modifying/deleting semantic views) require explicit user instructions
All SQL parameters use parameterized queries to prevent injection attacks
Exploration mode: all queries have LIMIT <= 100 and undergo SQL allowlist validation

Anti-full-table-scan rules (exploration mode)

ADB MySQL is a distributed columnar database, and full table scans are extremely costly.

Rule	Description
Check partition information before querying data	Before any data query, first call `partition_info`
Very large tables (>100 million rows) must have a time filter added	First use `table_statistics` to confirm the table size
SELECT * is prohibited for very wide tables (>50 columns)	Use column pruning and query only the required fields
Avoid GROUP BY on high-cardinality columns that are not distribution keys	Use `show_create_table` to view DISTRIBUTED BY information
Using functions on partition keys in WHERE is prohibited	`WHERE DATE_FORMAT(dt, '%Y-%m') = '2026-05'` will break partition pruning; instead use `WHERE dt BETWEEN '2026-05-01' AND '2026-05-31'`
Aggregation queries must include partition filter conditions	No partition filter = full table scan + shuffle
Run EXPLAIN before executing on large tables	Use the `explain` operation to check the execution plan
At most 2 levels of nested subqueries	Avoid unpredictable performance issues

Determine the default time range based on table size:

Table size (TABLE_ROWS estimate)	Default time range
< 1 million	No limit
1 million ~ 100 million	Last 1 month
> 100 million	Last 7 days

Tool call examples

SQL-style tool call examples

Search semantic views

uv run scripts/adb_analyst.py search_semantic_views --keywords "北京 销售" --top-k 3

Get semantic view definition

uv run scripts/adb_analyst.py get_semantic_view --schema sales_db --view-name SALES_ANALYSIS

Execute SQL (semantic mode - default)

uv run scripts/adb_analyst.py execute_sql --sql "SELECT city, AGG(revenue) FROM sales_db.SALES_ANALYSIS GROUP BY city LIMIT 100"

Execute SQL (direct connection mode, for exploration)

uv run scripts/adb_analyst.py execute_sql --sql "SELECT * FROM sales_db.orders WHERE dt >= '2026-05-01' LIMIT 10" --no-semantic-rewrite

Create semantic view

uv run scripts/adb_analyst.py create_semantic_view --schema logistics --view-name LOGISTICS_ANALYSIS --yaml-file /tmp/view.yaml

Modify semantic view

uv run scripts/adb_analyst.py alter_semantic_view --schema logistics --view-name LOGISTICS_ANALYSIS --operation rename --new-name SHIPPING_ANALYSIS
uv run scripts/adb_analyst.py alter_semantic_view --schema logistics --view-name LOGISTICS_ANALYSIS --operation set_comment --comment "Updated logistics view"

Delete semantic view

uv run scripts/adb_analyst.py drop_semantic_view --schema logistics --view-name LOGISTICS_ANALYSIS

List databases

uv run scripts/adb_analyst.py list_databases

Explore table metadata

# 列出数据库中的所有表
uv run scripts/adb_analyst.py explore_table_metadata --operation list_tables --database logistics

# 查看表的列结构
uv run scripts/adb_analyst.py explore_table_metadata --operation describe_table --database logistics --table shipments

# 表统计信息（行数、数据量）
uv run scripts/adb_analyst.py explore_table_metadata --operation table_statistics --database logistics --table shipments

# 分区信息（分区键 + 各分区统计）
uv run scripts/adb_analyst.py explore_table_metadata --operation partition_info --database logistics --table shipments

# 安全采样（感知分区，自动检测分区键）
uv run scripts/adb_analyst.py explore_table_metadata --operation safe_sample --database logistics --table shipments --columns shipment_id,ship_date,status --limit 5

# 查看查询执行计划
uv run scripts/adb_analyst.py explore_table_metadata --operation explain --database logistics --table shipments --sql "SELECT COUNT(*) FROM logistics.shipments WHERE ship_date >= '2026-05-01'"

# 索引信息
uv run scripts/adb_analyst.py explore_table_metadata --operation index_info --database logistics --table shipments

# 查看建表 DDL
uv run scripts/adb_analyst.py explore_table_metadata --operation show_create_table --database logistics --table shipments

OpenAPI-style tool call examples

Installation and usage conventions

# 安装 aliyun CLI
/bin/bash -c "$(curl -fsSL https://aliyuncli.alicdn.com/install.sh)"

# 安装 adb CLI 插件
aliyun plugin install --names adb

# 用户本地配置 AK/SK（不建议 Agent 代用户执行或录入密钥）
aliyun configure

Before the Agent uses the OpenAPI method, only check whether the aliyun、adb plugin, profile/credentials are available; if credentials are missing or invalid, prompt the user to configure them.
adb The product's default API version may not be the version where the semantic view resides; all semantic OpenAPI commands must explicitly specify --api-version 2021-12-01。
All semantic OpenAPI commands must specify --db-cluster-id; for cross-region or when the profile does not set a region, additionally specify --region。

Parameter name mapping(the parameter names differ between the SQL method and the OpenAPI method):

SQL method (adb_analyst.py) OpenAPI method (aliyun adb CLI)

--keywords --query-text

--top-k --topk

--schema --schema-name

--view-name --view-name

--yaml-file --definition

SQL method (adb_analyst.py)	OpenAPI method (aliyun adb CLI)
`--keywords`	`--query-text`
`--top-k`	`--topk`
`--schema`	`--schema-name`
`--view-name`	`--view-name`
`--yaml-file`	`--definition`

Search (search-semantic-views), get (get-semantic-view), create (create-semantic-view), delete (delete-semantic-view) use the SQL-style examples as reference for how to call them; simply replace the parameter names according to the mapping in the table above. Below only the OpenAPI-exclusive commands are listed:

Semantic SQL rewriting (generating physical SQL)

aliyun adb generate-sql-by-semantic-sql --api-version 2021-12-01 \
  --db-cluster-id am-bp1xxxxxxxx \
  --sql "SELECT city, AGG(revenue) FROM sales_db.SALES_ANALYSIS GROUP BY city LIMIT 100"

Replace semantic view

aliyun adb replace-semantic-view --api-version 2021-12-01 \
  --db-cluster-id am-bp1xxxxxxxx \
  --schema-name logistics \
  --view-name LOGISTICS_ANALYSIS \
  --definition "$(cat /tmp/view_v2.yaml)"

Rename semantic view

aliyun adb rename-semantic-view --api-version 2021-12-01 \
  --db-cluster-id am-bp1xxxxxxxx \
  --old-schema-name logistics \
  --old-view-name LOGISTICS_ANALYSIS \
  --new-schema-name logistics \
  --new-view-name SHIPPING_ANALYSIS

Result presentation specification

After the query results are returned, the following 5 items must be displayed (common to both SQL mode and OpenAPI mode):

Data table: Display the query results in Markdown table format
SQL used：
- Semantic mode / OpenAPI mode: Must display both the semantic SQL and the actual executed SQL (SQL mode extracts from rewrite_info.rewritten_sql ; OpenAPI mode extracts from the generate-sql-by-semantic-sql return value)
- Exploration mode（--no-semantic-rewrite): Display only the actually executed SQL
Text analysis: Key findings, trends, noteworthy patterns
Visualization suggestions: Time dimension + metric → line chart; category dimension + metric → bar chart/pie chart; two dimensions + metric → heatmap/grouped bar chart
Follow-up analysis directions: Suggest deeper analysis paths

Error handling

Error scenario	Agent behavior
SQL connection failure	Inform the user that both methods are unavailable, and suggest checking the ADB connection environment variables and network
No semantic search results	Guide into exploration mode
Semantic search relevance is extremely low (all candidate scores < 0.5)	Display the closest candidates and their partial coverage, ask the user to clarify or add keywords; if it still cannot be determined after 3 rounds of clarification, downgrade to exploration mode
Semantic SQL syntax error	Display the error and automatically correct and retry (up to 2 times)
Semantic engine validation failure	Re-read the YAML definition and correct the SQL
Query timeout	Suggest narrowing the time range or reducing the number of dimensions
Insufficient permissions	Inform the user that access permissions are insufficient
DDL failure (vectorization service unavailable)	Inform the user to retry later; DDL operations are atomic
aliyun CLI not installed	Prompt to install the aliyun CLI
adb plugin missing	Prompt to install the adb plugin (`aliyun plugin install --names adb`）
OpenAPI credentials invalid/expired	Prompt the user to execute `aliyun configure` to reconfigure
Insufficient OpenAPI permissions	Inform the user that RAM permissions are insufficient and list the required Actions
OpenAPI semantic rewrite failed but SQL connection is available	Display the error message and suggest the user check the semantic SQL syntax or view definition

OpenAPI retry strategy: Network timeout or rate limiting (Throttling) automatically retries up to 3 times (exponential backoff 1s/2s/4s); business errors are not retried. On failure, record RequestId for troubleshooting.

End-to-end example

Normal path: single-view self-decision

User: Help me check the sales situation in Beijing last year

Step 1 - Extract keywords and search:

uv run scripts/adb_analyst.py search_semantic_views --keywords "北京 销售" --top-k 3

Results: SALES_ANALYSIS (score=0.91), USER_PURCHASE (score=0.68).

Step 2 - Problem decomposition and semantic object mapping:

Problem decomposition:

Target metric term: "sales situation"
Filter condition terms: "Beijing" and "last year"

SALES_ANALYSIS view mapping verification:

"sales" → in metrics total_revenue(description: "total sales revenue"),order_count(description: "order count") ✓
"Beijing" → dimension city(description: "order city") ✓
"last year" → dimension order_year（expr: YEAR(order_date)）✓

Decision: SALES_ANALYSIS fully covers all keywords and is the only high-scoring candidate, so it is self-determined.

Step 3 - Output the mapping table and execute the semantic SQL:

Selected view sales_db.SALES_ANALYSIS (score 0.91): "Beijing" → dimension city "sales" → metrics total_revenue, order_count "last year" → dimension order_year = 2025

uv run scripts/adb_analyst.py execute_sql --sql "SELECT city, order_year, AGG(total_revenue), AGG(order_count) FROM sales_db.SALES_ANALYSIS WHERE city = '北京' AND order_year = 2025 GROUP BY city, order_year LIMIT 100"

Step 4 - Display results:

City Year Total revenue Order count

Beijing 2025 12,580,000.00 3,842

Semantic SQL:
SELECT city, order_year, AGG(total_revenue), AGG(order_count)
FROM sales_db.SALES_ANALYSIS
WHERE city = '北京' AND order_year = 2025
GROUP BY city, order_year LIMIT 100
Actual execution SQL:
SELECT o_city AS city, YEAR(o_orderdate) AS order_year,
       SUM(o_revenue) AS total_revenue, COUNT(*) AS order_count
FROM sales_db.orders
WHERE o_city = '北京' AND YEAR(o_orderdate) = 2025
GROUP BY o_city, YEAR(o_orderdate) LIMIT 100
Analysis: In 2025, the Beijing market's total revenue was 12.58 million yuan, with a total of 3,842 orders and an average order value of about 3,274 yuan.

Suggested further analysis: monthly trends (line chart), city comparison (bar chart), and share of contribution by customer group (pie chart).

City	Year	Total revenue	Order count
Beijing	2025	12,580,000.00	3,842

Ambiguous path: multiple view candidates → clarification-recheck loop

User: Help me look at the shipping situation in Beijing

Key differences from the normal path:

The search returns multiple candidates with similar coverage (e.g., SALES_ANALYSIS score=0.85, LOGISTICS_ANALYSIS score=0.83)
Both views can fully map "shipping" and "Beijing", but their scopes differ (sales scope vs logistics scope) → display the candidates + their respective mappings and let the user choose
After the user chooses, trigger theclarification-recheck loop(Step 6): incorporate the user's reply into the keywords and search again → remap → decide again → after determining the view, enter the second stage

Fallback path: degrade to exploration mode after clarification-recheck

User: Help me look at warehouse utilization