/alibabacloud-odps-information-schema

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 <a name="prerequisites"></a>

> **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](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](#project-level-adaptation) for transformation rules.

For MCP configuration, see [references/mcp-tools-reference.md](references/mcp-tools-reference.md).

Execution Channels <a name="channels"></a>

**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](references/mcp-tools-reference.md) for 15 MCP tools with routing guide.

Important Rules <a name="rules"></a>

1. **Always set namespace flag** — every tenant-level IS query, no exceptions. Project-level IS queries do NOT need this flag 2. **Filter by `ds`** — TASKS_HISTORY / TUNNELS_HISTORY are partitioned; always add `ds` filter to avoid full scan 3. **No SELECT \*** — use explicit column names 4. **Cross-metadata-center NOT supported** — each region is independent 5. **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. 6. **status values** — TASKS_HISTORY: `Terminated` (normal), `Failed`, `Cancelled` (rare). Never count Terminated as failure. 7. **operate_type values** — TUNNELS_HISTORY: `UPLOADLOG`, `DOWNLOADLOG`, `DOWNLOADINSTANCELOG`, `STORAGEAPIREAD`, `STORAGEAPIWRITE` 8. **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. 9. **cost_cpu / cost_mem are DOUBLE** — unit: 100×core×seconds / MB×seconds. Convert to CU-hours: `cost_cpu / 100 / 3600` 10. **Duration** — use `DATEDIFF(end_time, start_time, 'ss')` (seconds). No `duration_ms` column exists. 11. **Non-existent fields trap** — see Critical Column Reference below 12. **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 <a name="project-level-adaptation"></a>

All SQL temp