| name | alibabacloud-odps-information-schema |
| description | 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. |
| license | Apache-2.0 |
| compatibility | Requires MaxCompute tenant-level Information Schema access. Execution channels: MCP preferred (maxcompute-catalog MCP server >= 0.1.0), odpscmd >= 0.35.0 as fallback. |
| metadata | {"domain":"aiops","owner":"MaxCompute","contact":"jiexian.hc@alibaba-inc.com, zhipeng.gzp@alibaba-inc.com","version":27,"when_to_use":"User asks about metadata analysis using Information Schema views — e.g. table storage stats, query history, permission audit, cost tracking, governance diagnostics, zombie tables, who has access to table X, CU consumption trend, failed tasks analysis, table access patterns, tunnel audit. Do NOT use for: show me my tables (use list_tables), create a table (DDL), run this SQL (execution), general ODPS questions without IS context."} |
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 |
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 |
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 |
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
Official Documentation
