ODPS Information Schema
This skill is for Information Schema (IS) metadata queries ONLY. If the user's question is about DDL/DML, listing tables, or general MaxCompute usage (not IS views), do NOT use this skill — use MCP tools (list_tables, get_table_schema) or odpscmd instead.
Query MaxCompute metadata through INFORMATION_SCHEMA views for storage, cost, permission, task, and governance analysis.
Prerequisites
MANDATORY: Every IS query MUST set namespace flag. Without it, ALL queries fail with "Table not found".
- MCP:
hints={"odps.namespace.schema":"true"} in execute_sql
- odpscmd:
SET odps.namespace.schema=true; before each query
- No exceptions. Applies to ALL
SYSTEM_CATALOG.INFORMATION_SCHEMA.* queries.
IS views require tenant-level permission. If you get access errors, the user needs tenant-level role — see references/ram-policies.md for Policy template.
Data freshness: History views (TASKS_HISTORY, TUNNELS_HISTORY) have ~5 min delay, realtime views ~3 hours. For yesterday's data, query after 06:00 to ensure completeness.
Tenant-level vs Project-level IS: MaxCompute has two IS levels. Tenant-level (SYSTEM_CATALOG.INFORMATION_SCHEMA.*) is the default — it covers all projects under the same metadata center and is recommended. Project-level (Information_Schema.*) is per-project only, requires install package Information_Schema.systables, and is being deprecated (since 2024-03 new projects no longer auto-install). Key differences: (1) project-level has fewer views (no CATALOGS, VOLUMES, FOREIGN_SERVERS, SCHEMAS, PARTITION_ACCESS_INFO, TABLE_ACCESS_INFO, QUOTA_USAGE; has SCHEMA_PRIVILEGES which tenant lacks); (2) project-level TASKS_HISTORY has task_schema while tenant-level does NOT; (3) project-level table_catalog is always odps while tenant-level is the actual project name. See Project-level IS Adaptation for transformation rules.
For MCP configuration, see references/mcp-tools-reference.md.
Execution Channels
MCP preferred when mcp__maxcompute-catalog__* tools are available. Fall back to odpscmd on connection/auth errors.
| Channel |
Use For |
Key Detail |
| MCP (tenant-level) |
DQL, metadata, search |
execute_sql + hints={"odps.namespace.schema":"true"}; sync limit 1000 rows; cost_sql supports IS views (verified 2026-04) |
| MCP (project-level) |
DQL, metadata, search |
execute_sql + hints={} (no namespace flag); view prefix: Information_Schema.* |
| odpscmd (tenant-level) |
DDL/DML, large results, MCP unavailable |
SET odps.namespace.schema=true; prefix required |
| odpscmd (project-level) |
DDL/DML, large results, MCP unavailable |
No namespace flag; view prefix: Information_Schema.* |
See references/mcp-tools-reference.md for 15 MCP tools with routing guide.
Important Rules
- Always set namespace flag — every tenant-level IS query, no exceptions. Project-level IS queries do NOT need this flag
- Filter by
ds — TASKS_HISTORY / TUNNELS_HISTORY are partitioned; always add ds filter to avoid full scan
- No SELECT * — use explicit column names
- Cross-metadata-center NOT supported — each region is independent
- last_access_time is NULL for partitioned tables — use
COALESCE(last_access_time, last_modified_time) or check PARTITIONS view. Also: not collected for ALGO jobs or Hologres direct reads; up to 24h delay from actual access.
- status values — TASKS_HISTORY:
Terminated (normal), Failed, Cancelled (rare). Never count Terminated as failure.
- operate_type values — TUNNELS_HISTORY:
UPLOADLOG, DOWNLOADLOG, DOWNLOADINSTANCELOG, STORAGEAPIREAD, STORAGEAPIWRITE
- Views without time fields — COLUMNS has no time column. TABLE_PRIVILEGES/COLUMN_PRIVILEGES have no time column, only
expired. These views support static snapshot only, not time-series.
- cost_cpu / cost_mem are DOUBLE — unit: 100×core×seconds / MB×seconds. Convert to CU-hours:
cost_cpu / 100 / 3600
- Duration — use
DATEDIFF(end_time, start_time, 'ss') (seconds). No duration_ms column exists.
- Non-existent fields trap — see Critical Column Reference below
- JOIN IS views requires 3-field key — when joining any two IS views, the ON condition MUST include
table_catalog, table_schema, AND table_name. Missing any one causes incorrect results in multi-catalog environments
Project-level IS Adaptation
All SQL templates in this skill default to tenant-level syntax (SYSTEM_CATALOG.INFORMATION_SCHEMA.* + namespace flag). If the environment only supports project-level IS, apply these mechanical transformations to every generated SQL:
| Transform |
Tenant-level (default) |
Project-level |
| View prefix |
SYSTEM_CATALOG.INFORMATION_SCHEMA. |
Information_Schema. |
| Namespace flag (MCP) |
hints={"odps.namespace.schema":"true"} |
hints={} (remove flag) |
| Namespace flag (odpscmd) |
SET odps.namespace.schema=true; |
Remove entirely |
| Scope |
All projects in metadata center |
Current project only |
| Views unavailable |
— |
CATALOGS, VOLUMES, FOREIGN_SERVERS, SCHEMAS, PARTITION_ACCESS_INFO, TABLE_ACCESS_INFO, QUOTA_USAGE |
| View exclusive to this level |
— |
SCHEMA_PRIVILEGES |
| TASKS_HISTORY extra column |
— |
task_schema (project name; tenant-level lacks this) |
table_catalog value |
Actual project name |
Fixed odps |
Example transformation:
-- Tenant-level (default):
SET odps.namespace.schema=true;
SELECT table_name, data_length FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES WHERE ...
-- Project-level (after transformation):
SELECT table_name, data_length FROM Information_Schema.tables WHERE ...
When to switch: If a tenant-level query fails with Table not found (and namespace flag is correctly set), or if the user explicitly says they only have project-level IS, apply the transformation rules above to all subsequent queries.
Critical Column Name Reference
| Concept |
Correct |
Wrong |
| Table size |
data_length |
size_bytes, size |
| Task instance |
inst_id |
task_id |
| Task submitter |
owner_name |
task_owner |
| Task project |
task_catalog (tenant-level) |
project_name, task_schema (project-level IS only) |
| Task error |
result |
error_message |
| Task duration |
DATEDIFF(end_time, start_time, 'ss') |
duration_ms |
| Task status |
status |
task_status |
| Task input size |
input_bytes |
scan_bytes, processed_bytes |
| Table comment |
table_comment |
comment |
| Column comment |
column_comment |
comment |
| Privilege grantee |
user_name, user_id |
grantee |
| Privilege time |
expired |
grant_time |
| Resource size |
size |
size_bytes |
| Tunnel session |
session_id |
tunnel_id |
| Tunnel data size |
data_size |
size_bytes |
| User identity |
identity_provider |
— |
| Timestamp type |
DATETIME |
TIMESTAMP |
| Table modified |
last_modified_time |
last_ddl_time |
| cost_cpu type |
DOUBLE |
BIGINT |
For verified query examples using these columns, see references/verified-queries.md.
Routing Index
SKILL.md contains critical column names and namespace rules. Load sub-files only when needed:
- If multiple rows match, load ALL matched files. E.g., a non-English term causal query needs both terminology.md and playbooks+causal-templates.
- If SKILL.md inline info (tables below) is sufficient, do NOT load extra files.
- NOT about IS views? → This skill is not applicable. Use MCP tools (list_tables, get_table_schema, execute_sql) or odpscmd for DDL/DML/general queries.
| Query Type |
When |
Load Extra File |
| NOT an IS query |
DDL/DML, list tables, run SQL, general ODPS |
None — use MCP tools or odpscmd instead |
| Single-view query |
One IS view, no JOIN |
None — SKILL.md only |
| Live monitoring |
TASKS / QUOTA_USAGE |
None — SKILL.md only |
| 2+ IS view JOIN |
Combining views |
references/joins.md |
| Named metric/template |
"comment coverage", "CU trend", "zombie table detection" |
references/verified-queries.md + references/metrics.md |
| Multi-step diagnosis |
"Why did CU spike?", root-cause analysis |
references/playbooks.md + references/causal-templates.md |
| Non-English synonyms |
"cpu时间", "作业时长", "存储占用", or any CJK/localized terms |
references/terminology.md (or use inline mapping below) |
| Schema/field lookup |
"What columns does X have?" |
references/views-reference.md |
| Access denied error |
Permission denied on IS view |
references/ram-policies.md |
| Troubleshooting |
Table not found, timeout, etc. |
references/TROUBLESHOOTING.md |
Anti-pattern: Do NOT load extra files for these
| User says |
Looks like |
Actually is |
Load |
| "storage pressure, list top 20 tables" |
Diagnostics |
Single-view |
SKILL.md only |
| "permission audit, who has SELECT on X" |
Playbook |
Single-view |
SKILL.md only |
| "cost attribution by owner" |
Causal |
Single-view |
SKILL.md only |
Inline Join Conditions (for 2+ view JOINs)
When joining IS views, you MUST include table_catalog, table_schema, AND table_name in join conditions. Common join paths:
| Left |
Right |
Join Condition |
| TABLES |
COLUMNS |
t.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_name |
| TABLES |
PARTITIONS |
t.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_name |
| TABLES |
TABLE_PRIVILEGES |
t.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_name |
| TABLES |
TABLE_ACCESS_INFO |
t.table_catalog = a.table_catalog AND t.table_schema = a.table_schema AND t.table_name = a.table_name |
| TABLES |
TABLE_LABELS |
t.table_catalog = l.table_catalog AND t.table_schema = l.table_schema AND t.table_name = l.table_name |
| USERS |
USER_ROLES |
u.user_id = ur.user_id |
| COLUMNS |
COLUMN_LABELS |
c.table_catalog = l.table_catalog AND c.table_schema = l.table_schema AND c.table_name = l.table_name AND c.column_name = l.column_name |
For all 16 join paths, see references/joins.md. The 7 most common paths are inlined below.
Inline Terminology Mapping (common non-English terms)
| Non-English term |
English equivalent |
Correct column/source |
Common mistake |
| cpu时间 / CPU消耗 |
CPU time / CPU consumption |
cost_cpu (DOUBLE), ÷100÷3600 = CU·hour |
cpu_time |
| 作业时长 / 任务耗时 |
Task duration / task elapsed time |
DATEDIFF(end_time, start_time, 'ss') |
duration_ms |
| 存储占用 / 表大小 |
Storage usage / table size |
data_length (÷1073741824 = GB) |
size_bytes |
| 僵尸表 |
Zombie table |
TABLES + TABLE_ACCESS_INFO |
— |
| 排队时间 |
Queue wait time |
NOT available in IS views |
— |
| CU时 / CU消耗 |
CU-hours / CU consumption |
SUM(cost_cpu) / 100.0 / 3600 |
— |
| 任务失败率 |
Task failure rate |
status='Failed' ratio in TASKS_HISTORY |
— |
For all 59 terms, see references/terminology.md.
Error Recovery
| Error Signal |
Root Cause |
Fix |
Table not found on IS view |
Missing namespace flag |
Add SET odps.namespace.schema=true; / hints={"odps.namespace.schema":"true"}. Verify with Q30 smoke test |
Access denied / Permission denied on IS view |
Missing tenant-level role |
Verify access with check_access(include_grants=true). User needs tenant-level role — load references/ram-policies.md for Policy template |
Table not found on SYSTEM_CATALOG.INFORMATION_SCHEMA.* (namespace flag correctly set) |
Environment only supports project-level IS |
Apply Project-level IS Adaptation transformation rules to all subsequent queries: switch prefix to Information_Schema.*, remove namespace flag |
Information_Schema not found / Package not installed |
Project-level IS not installed in this project |
User must run install package Information_Schema.systables as project owner or Super_Administrator. After install, query as Information_Schema.view_name (no namespace flag). Note: project-level IS is being deprecated — prefer tenant-level |
Object 'Information_Schema' not found on new project |
New projects (since 2024-03) don't auto-install project-level IS |
Switch to tenant-level IS (SYSTEM_CATALOG.INFORMATION_SCHEMA.*) or manually install package |
| TASKS_HISTORY query slow/expensive |
No ds filter |
Add WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -14, 'dd'), 'yyyymmdd') |
| MCP returns exactly 1000 rows |
Sync limit truncation |
Re-run with async=true, or add tighter WHERE/LIMIT |
Column not found |
Used non-existent column |
Check Critical Column Reference above — common: size_bytes→data_length, task_status→status |
| TUNNELS_HISTORY sync timeout (>30s) |
Tunnel record volume much larger than TASKS_HISTORY |
Use async=true + get_instance, or reduce ds to 1 day |
| Async timeout (>30s) |
Large scan |
Use cost_sql first; add ds filter; split query |
| IS view shows no recent data |
~5 min delay for history views |
Query yesterday's data after 06:00 |
| odpscmd query hangs |
Large result set or full-table scan |
Use odps_is_query.sh -t <seconds> to set timeout (default 300s); add ds filter |
Namespace flag set but still Table not found |
Other causes (wrong project, typo, schema issue) |
Load references/TROUBLESHOOTING.md for T1–T7 scenarios |
Core Views
| View |
Purpose |
Key Columns |
TABLES |
Table metadata |
table_name, owner_name, data_length, table_type, lifecycle, last_modified_time |
COLUMNS |
Column metadata |
column_name, data_type, column_comment, is_partition_key |
PARTITIONS |
Partition metadata |
partition_name, data_length, create_time, last_modified_time |
TASKS |
Running jobs (live, seconds delay) |
inst_id, task_name, owner_name, status, cpu_usage (core×100), mem_usage (MB) |
TASKS_HISTORY |
Query history |
inst_id, task_name, owner_name, status, task_type, start_time, end_time, result, cost_cpu, input_bytes, ds |
TUNNELS_HISTORY |
Tunnel history |
session_id, object_name, operate_type, data_size, owner_name, ds |
TABLE_PRIVILEGES |
Table permissions |
table_name, user_name, privilege_type, expired |
TABLE_ACCESS_INFO ⚠️ |
Table access stats |
table_name, access_count, access_bytes, last_access_time |
QUOTA_USAGE |
Subscription quota monitoring |
name, cpu_elastic_quota_max, cpu_elastic_quota_used, mem_elastic_quota_max, mem_elastic_quota_used |
USERS |
Project users |
user_name, user_id, identity_provider |
USER_ROLES |
User-role mapping |
user_name, role_name, user_role_catalog |
CATALOGS ⚠️ |
Project list |
catalog_name, status, owner_name, region |
For all 31 views with complete field definitions, see references/views-reference.md. Views marked ⚠️ are tenant-level only (not available in project-level IS).
Additional Resources
- references/views-reference.md — Complete field definitions for all 31 IS views
- references/verified-queries.md — 30 pre-validated SQL query templates (including smoke test)
- references/entities.md — Entity-to-table mapping
- references/metrics.md — Metric definitions with SQL expressions
- references/joins.md — Join paths between views
- references/playbooks.md — 23 diagnostic scenario playbooks
- references/causal-templates.md — Root-cause analysis templates
- references/terminology.md — 59-term synonym dictionary for NL2SQL
- references/ram-policies.md — Tenant permission setup and Policy template
- references/mcp-tools-reference.md — 15 MCP tools with routing guide + MCP setup + installation
- scripts/odps_is_query.sh — CLI query tool (16 query types + custom, including smoke-test). Supports
-t <seconds> for timeout (default 300s), -d YYYYMMDD for date, -p for project. Custom mode only allows SELECT (DDL/DML rejected).
- references/TROUBLESHOOTING.md — 7 error scenarios with fix templates (T1–T7)
Official Documentation
1---2name: alibabacloud-odps-information-schema3description: Query MaxCompute (ODPS) Information Schema metadata views. Tenant-level (SYSTEM_CATALOG.INFORMATION_SCHEMA.*, recommended) or project-level (Information_Schema.*, deprecated). NL→SQL for IS views: tables, columns, partitions, tasks_history, tunnels_history, table_privileges, users, user_roles, quota_usage, etc. NOT for: DDL/DML, listing tables via MCP, running ad-hoc SQL, general MaxCompute questions.4license: Apache-2.05---6
7# ODPS Information Schema
8
9**This skill is for Information Schema (IS) metadata queries ONLY.** If the user's question is about DDL/DML, listing tables, or general MaxCompute usage (not IS views), do NOT use this skill — use MCP tools (list_tables, get_table_schema) or odpscmd instead.
10
11Query MaxCompute metadata through INFORMATION_SCHEMA views for storage, cost, permission, task, and governance analysis.
12
13## Prerequisites <a name="prerequisites"></a>
14
15> **MANDATORY: Every IS query MUST set namespace flag.** Without it, ALL queries fail with "Table not found".
16> - **MCP**: `hints={"odps.namespace.schema":"true"}` in `execute_sql`
17> - **odpscmd**: `SET odps.namespace.schema=true;` before each query
18> - No exceptions. Applies to ALL `SYSTEM_CATALOG.INFORMATION_SCHEMA.*` queries.
19
20> IS views require tenant-level permission. If you get access errors, the user needs tenant-level role — see [references/ram-policies.md](references/ram-policies.md) for Policy template.
21
22> **Data freshness**: History views (TASKS_HISTORY, TUNNELS_HISTORY) have ~5 min delay, realtime views ~3 hours. For yesterday's data, query after 06:00 to ensure completeness.
23
24> **Tenant-level vs Project-level IS**: MaxCompute has two IS levels. **Tenant-level** (`SYSTEM_CATALOG.INFORMATION_SCHEMA.*`) is the default — it covers all projects under the same metadata center and is **recommended**. **Project-level** (`Information_Schema.*`) is per-project only, requires `install package Information_Schema.systables`, and is **being deprecated** (since 2024-03 new projects no longer auto-install). Key differences: (1) project-level has fewer views (no CATALOGS, VOLUMES, FOREIGN_SERVERS, SCHEMAS, PARTITION_ACCESS_INFO, TABLE_ACCESS_INFO, QUOTA_USAGE; has SCHEMA_PRIVILEGES which tenant lacks); (2) project-level TASKS_HISTORY has `task_schema` while tenant-level does NOT; (3) project-level `table_catalog` is always `odps` while tenant-level is the actual project name. See [Project-level IS Adaptation](#project-level-adaptation) for transformation rules.
25
26For MCP configuration, see [references/mcp-tools-reference.md](references/mcp-tools-reference.md).
27
28## Execution Channels <a name="channels"></a>
29
30**MCP preferred** when `mcp__maxcompute-catalog__*` tools are available. Fall back to odpscmd on connection/auth errors.
31
32| Channel | Use For | Key Detail |
33|---|---|---|
34| **MCP (tenant-level)** | DQL, metadata, search | `execute_sql` + `hints={"odps.namespace.schema":"true"}`; sync limit 1000 rows; `cost_sql` supports IS views (verified 2026-04) |
35| **MCP (project-level)** | DQL, metadata, search | `execute_sql` + `hints={}` (no namespace flag); view prefix: `Information_Schema.*` |
36| **odpscmd (tenant-level)** | DDL/DML, large results, MCP unavailable | `SET odps.namespace.schema=true;` prefix required |
37| **odpscmd (project-level)** | DDL/DML, large results, MCP unavailable | No namespace flag; view prefix: `Information_Schema.*` |
38
39See [references/mcp-tools-reference.md](references/mcp-tools-reference.md) for 15 MCP tools with routing guide.
40
41## Important Rules <a name="rules"></a>
42
431. **Always set namespace flag** — every tenant-level IS query, no exceptions. Project-level IS queries do NOT need this flag
442. **Filter by `ds`** — TASKS_HISTORY / TUNNELS_HISTORY are partitioned; always add `ds` filter to avoid full scan
453. **No SELECT \*** — use explicit column names
464. **Cross-metadata-center NOT supported** — each region is independent
475. **last_access_time is NULL for partitioned tables** — use `COALESCE(last_access_time, last_modified_time)` or check PARTITIONS view. Also: not collected for ALGO jobs or Hologres direct reads; up to 24h delay from actual access.
486. **status values** — TASKS_HISTORY: `Terminated` (normal), `Failed`, `Cancelled` (rare). Never count Terminated as failure.
497. **operate_type values** — TUNNELS_HISTORY: `UPLOADLOG`, `DOWNLOADLOG`, `DOWNLOADINSTANCELOG`, `STORAGEAPIREAD`, `STORAGEAPIWRITE`
508. **Views without time fields** — COLUMNS has no time column. TABLE_PRIVILEGES/COLUMN_PRIVILEGES have no time column, only `expired`. These views support static snapshot only, not time-series.
519. **cost_cpu / cost_mem are DOUBLE** — unit: 100×core×seconds / MB×seconds. Convert to CU-hours: `cost_cpu / 100 / 3600`
5210. **Duration** — use `DATEDIFF(end_time, start_time, 'ss')` (seconds). No `duration_ms` column exists.
5311. **Non-existent fields trap** — see Critical Column Reference below
5412. **JOIN IS views requires 3-field key** — when joining any two IS views, the ON condition MUST include `table_catalog`, `table_schema`, AND `table_name`. Missing any one causes incorrect results in multi-catalog environments
55
56## Project-level IS Adaptation <a name="project-level-adaptation"></a>
57
58All SQL templates in this skill default to **tenant-level** syntax (`SYSTEM_CATALOG.INFORMATION_SCHEMA.*` + namespace flag). If the environment only supports **project-level** IS, apply these mechanical transformations to every generated SQL:
59
60| Transform | Tenant-level (default) | Project-level |
61|-----------|----------------------|---------------|
62| View prefix | `SYSTEM_CATALOG.INFORMATION_SCHEMA.` | `Information_Schema.` |
63| Namespace flag (MCP) | `hints={"odps.namespace.schema":"true"}` | `hints={}` (remove flag) |
64| Namespace flag (odpscmd) | `SET odps.namespace.schema=true;` | Remove entirely |
65| Scope | All projects in metadata center | Current project only |
66| Views unavailable | — | CATALOGS, VOLUMES, FOREIGN_SERVERS, SCHEMAS, PARTITION_ACCESS_INFO, TABLE_ACCESS_INFO, QUOTA_USAGE |
67| View exclusive to this level | — | SCHEMA_PRIVILEGES |
68| TASKS_HISTORY extra column | — | `task_schema` (project name; tenant-level lacks this) |
69| `table_catalog` value | Actual project name | Fixed `odps` |
70
71**Example transformation:**
72```
73-- Tenant-level (default):
74SET odps.namespace.schema=true;
75SELECT table_name, data_length FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES WHERE ...
76
77-- Project-level (after transformation):
78SELECT table_name, data_length FROM Information_Schema.tables WHERE ...
79```
80
81**When to switch**: If a tenant-level query fails with `Table not found` (and namespace flag is correctly set), or if the user explicitly says they only have project-level IS, apply the transformation rules above to all subsequent queries.
82
83## Critical Column Name Reference <a name="column-reference"></a>
84
85| Concept | Correct | Wrong |
86|---|---|---|
87| Table size | `data_length` | ~~size_bytes~~, ~~size~~ |
88| Task instance | `inst_id` | ~~task_id~~ |
89| Task submitter | `owner_name` | ~~task_owner~~ |
90| Task project | `task_catalog` (tenant-level) | ~~project_name~~, ~~task_schema~~ (project-level IS only) |
91| Task error | `result` | ~~error_message~~ |
92| Task duration | `DATEDIFF(end_time, start_time, 'ss')` | ~~duration_ms~~ |
93| Task status | `status` | ~~task_status~~ |
94| Task input size | `input_bytes` | ~~scan_bytes~~, ~~processed_bytes~~ |
95| Table comment | `table_comment` | ~~comment~~ |
96| Column comment | `column_comment` | ~~comment~~ |
97| Privilege grantee | `user_name`, `user_id` | ~~grantee~~ |
98| Privilege time | `expired` | ~~grant_time~~ |
99| Resource size | `size` | ~~size_bytes~~ |
100| Tunnel session | `session_id` | ~~tunnel_id~~ |
101| Tunnel data size | `data_size` | ~~size_bytes~~ |
102| User identity | `identity_provider` | — |
103| Timestamp type | `DATETIME` | ~~TIMESTAMP~~ |
104| Table modified | `last_modified_time` | ~~last_ddl_time~~ |
105| cost_cpu type | `DOUBLE` | ~~BIGINT~~ |
106
107> For verified query examples using these columns, see [references/verified-queries.md](references/verified-queries.md).
108
109## Routing Index <a name="routing"></a>
110
111SKILL.md contains critical column names and namespace rules. Load sub-files only when needed:
112
113- **If multiple rows match, load ALL matched files.** E.g., a non-English term causal query needs both terminology.md and playbooks+causal-templates.
114- **If SKILL.md inline info (tables below) is sufficient, do NOT load extra files.**
115- **NOT about IS views?** → This skill is not applicable. Use MCP tools (list_tables, get_table_schema, execute_sql) or odpscmd for DDL/DML/general queries.
116
117| Query Type | When | Load Extra File |
118|---|---|---|
119| **NOT an IS query** | DDL/DML, list tables, run SQL, general ODPS | None — use MCP tools or odpscmd instead |
120| **Single-view query** | One IS view, no JOIN | None — SKILL.md only |
121| **Live monitoring** | TASKS / QUOTA_USAGE | None — SKILL.md only |
122| **2+ IS view JOIN** | Combining views | [references/joins.md](references/joins.md) |
123| **Named metric/template** | "comment coverage", "CU trend", "zombie table detection" | [references/verified-queries.md](references/verified-queries.md) + [references/metrics.md](references/metrics.md) |
124| **Multi-step diagnosis** | "Why did CU spike?", root-cause analysis | [references/playbooks.md](references/playbooks.md) + [references/causal-templates.md](references/causal-templates.md) |
125| **Non-English synonyms** | "cpu时间", "作业时长", "存储占用", or any CJK/localized terms | [references/terminology.md](references/terminology.md) (or use inline mapping below) |
126| **Schema/field lookup** | "What columns does X have?" | [references/views-reference.md](references/views-reference.md) |
127| **Access denied error** | Permission denied on IS view | [references/ram-policies.md](references/ram-policies.md) |
128| **Troubleshooting** | Table not found, timeout, etc. | [references/TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) |
129
130### Anti-pattern: Do NOT load extra files for these
131
132| User says | Looks like | Actually is | Load |
133|---|---|---|---|
134| "storage pressure, list top 20 tables" | Diagnostics | Single-view | SKILL.md only |
135| "permission audit, who has SELECT on X" | Playbook | Single-view | SKILL.md only |
136| "cost attribution by owner" | Causal | Single-view | SKILL.md only |
137
138<!-- SYNC: derived from references/joins.md paths #1 #2 #3 #4 #5 #8 #10 -->
139### Inline Join Conditions (for 2+ view JOINs)
140
141When joining IS views, you MUST include `table_catalog`, `table_schema`, AND `table_name` in join conditions. Common join paths:
142
143| Left | Right | Join Condition |
144|---|---|---|
145| TABLES | COLUMNS | `t.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_name` |
146| TABLES | PARTITIONS | `t.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_name` |
147| TABLES | TABLE_PRIVILEGES | `t.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_name` |
148| TABLES | TABLE_ACCESS_INFO | `t.table_catalog = a.table_catalog AND t.table_schema = a.table_schema AND t.table_name = a.table_name` |
149| TABLES | TABLE_LABELS | `t.table_catalog = l.table_catalog AND t.table_schema = l.table_schema AND t.table_name = l.table_name` |
150| USERS | USER_ROLES | `u.user_id = ur.user_id` |
151| COLUMNS | COLUMN_LABELS | `c.table_catalog = l.table_catalog AND c.table_schema = l.table_schema AND c.table_name = l.table_name AND c.column_name = l.column_name` |
152
153For all 16 join paths, see [references/joins.md](references/joins.md). The 7 most common paths are inlined below.
154
155<!-- SYNC: derived from references/terminology.md — metric terms: storage usage, task CPU consumption, task execution duration, CU-hours, queue wait; dimension terms: zombie tables; metric: task failure rate -->
156### Inline Terminology Mapping (common non-English terms)
157
158| Non-English term | English equivalent | Correct column/source | Common mistake |
159|---|---|---|---|
160| cpu时间 / CPU消耗 | CPU time / CPU consumption | `cost_cpu` (DOUBLE), ÷100÷3600 = CU·hour | ~~cpu_time~~ |
161| 作业时长 / 任务耗时 | Task duration / task elapsed time | `DATEDIFF(end_time, start_time, 'ss')` | ~~duration_ms~~ |
162| 存储占用 / 表大小 | Storage usage / table size | `data_length` (÷1073741824 = GB) | ~~size_bytes~~ |
163| 僵尸表 | Zombie table | TABLES + TABLE_ACCESS_INFO | — |
164| 排队时间 | Queue wait time | NOT available in IS views | — |
165| CU时 / CU消耗 | CU-hours / CU consumption | `SUM(cost_cpu) / 100.0 / 3600` | — |
166| 任务失败率 | Task failure rate | `status='Failed'` ratio in TASKS_HISTORY | — |
167
168For all 59 terms, see [references/terminology.md](references/terminology.md).
169
170## Error Recovery <a name="error-recovery"></a>
171
172| Error Signal | Root Cause | Fix |
173|---|---|---|
174| `Table not found` on IS view | Missing namespace flag | Add `SET odps.namespace.schema=true;` / `hints={"odps.namespace.schema":"true"}`. Verify with [Q30 smoke test](references/verified-queries.md#q30-namespace-flag-verification) |
175| `Access denied` / `Permission denied` on IS view | Missing tenant-level role | Verify access with `check_access(include_grants=true)`. User needs tenant-level role — load [references/ram-policies.md](references/ram-policies.md) for Policy template |
176| `Table not found` on `SYSTEM_CATALOG.INFORMATION_SCHEMA.*` (namespace flag correctly set) | Environment only supports project-level IS | Apply [Project-level IS Adaptation](#project-level-adaptation) transformation rules to all subsequent queries: switch prefix to `Information_Schema.*`, remove namespace flag |
177| `Information_Schema not found` / `Package not installed` | Project-level IS not installed in this project | User must run `install package Information_Schema.systables` as project owner or Super_Administrator. After install, query as `Information_Schema.view_name` (no namespace flag). Note: project-level IS is being deprecated — prefer tenant-level |
178| `Object 'Information_Schema' not found` on new project | New projects (since 2024-03) don't auto-install project-level IS | Switch to tenant-level IS (`SYSTEM_CATALOG.INFORMATION_SCHEMA.*`) or manually install package |
179| TASKS_HISTORY query slow/expensive | No `ds` filter | Add `WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -14, 'dd'), 'yyyymmdd')` |
180| MCP returns exactly 1000 rows | Sync limit truncation | Re-run with `async=true`, or add tighter WHERE/LIMIT |
181| `Column not found` | Used non-existent column | Check Critical Column Reference above — common: `size_bytes`→`data_length`, `task_status`→`status` |
182| TUNNELS_HISTORY sync timeout (>30s) | Tunnel record volume much larger than TASKS_HISTORY | Use `async=true` + `get_instance`, or reduce ds to 1 day |
183| Async timeout (>30s) | Large scan | Use `cost_sql` first; add `ds` filter; split query |
184| IS view shows no recent data | ~5 min delay for history views | Query yesterday's data after 06:00 |
185| odpscmd query hangs | Large result set or full-table scan | Use `odps_is_query.sh -t <seconds>` to set timeout (default 300s); add `ds` filter |
186| Namespace flag set but still `Table not found` | Other causes (wrong project, typo, schema issue) | Load [references/TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) for T1–T7 scenarios |
187
188## Core Views <a name="core-views"></a>
189
190| View | Purpose | Key Columns |
191|---|---|---|
192| `TABLES` | Table metadata | table_name, owner_name, data_length, table_type, lifecycle, last_modified_time |
193| `COLUMNS` | Column metadata | column_name, data_type, column_comment, is_partition_key |
194| `PARTITIONS` | Partition metadata | partition_name, data_length, create_time, last_modified_time |
195| `TASKS` | Running jobs (live, seconds delay) | inst_id, task_name, owner_name, status, cpu_usage (core×100), mem_usage (MB) |
196| `TASKS_HISTORY` | Query history | inst_id, task_name, owner_name, status, task_type, start_time, end_time, result, cost_cpu, input_bytes, ds |
197| `TUNNELS_HISTORY` | Tunnel history | session_id, object_name, operate_type, data_size, owner_name, ds |
198| `TABLE_PRIVILEGES` | Table permissions | table_name, user_name, privilege_type, expired |
199| `TABLE_ACCESS_INFO` ⚠️ | Table access stats | table_name, access_count, access_bytes, last_access_time |
200| `QUOTA_USAGE` | Subscription quota monitoring | name, cpu_elastic_quota_max, cpu_elastic_quota_used, mem_elastic_quota_max, mem_elastic_quota_used |
201| `USERS` | Project users | user_name, user_id, identity_provider |
202| `USER_ROLES` | User-role mapping | user_name, role_name, user_role_catalog |
203| `CATALOGS` ⚠️ | Project list | catalog_name, status, owner_name, region |
204
205For all 31 views with complete field definitions, see [references/views-reference.md](references/views-reference.md). Views marked ⚠️ are **tenant-level only** (not available in project-level IS).
206
207## Additional Resources <a name="resources"></a>
208
209- [references/views-reference.md](references/views-reference.md) — Complete field definitions for all 31 IS views
210- [references/verified-queries.md](references/verified-queries.md) — 30 pre-validated SQL query templates (including smoke test)
211- [references/entities.md](references/entities.md) — Entity-to-table mapping
212- [references/metrics.md](references/metrics.md) — Metric definitions with SQL expressions
213- [references/joins.md](references/joins.md) — Join paths between views
214- [references/playbooks.md](references/playbooks.md) — 23 diagnostic scenario playbooks
215- [references/causal-templates.md](references/causal-templates.md) — Root-cause analysis templates
216- [references/terminology.md](references/terminology.md) — 59-term synonym dictionary for NL2SQL
217- [references/ram-policies.md](references/ram-policies.md) — Tenant permission setup and Policy template
218- [references/mcp-tools-reference.md](references/mcp-tools-reference.md) — 15 MCP tools with routing guide + MCP setup + installation
219- [scripts/odps_is_query.sh](scripts/odps_is_query.sh) — CLI query tool (16 query types + custom, including smoke-test). Supports `-t <seconds>` for timeout (default 300s), `-d YYYYMMDD` for date, `-p` for project. Custom mode only allows SELECT (DDL/DML rejected).
220- [references/TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) — 7 error scenarios with fix templates (T1–T7)
221
222## Official Documentation <a name="docs"></a>
223
224- [MaxCompute Tenant-level Information Schema](https://help.aliyun.com/zh/maxcompute/user-guide/tenant-level-information-schema)
aliyun