# Alibabacloud Odps Information Schema

> 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.

- Skill: `aliyun/alibabacloud-odps-information-schema` (Agent Skill, multi-file: 13 files)
- Install (CLI): `npx skillmds@latest add aliyun/alibabacloud-odps-information-schema`
- Raw SKILL.md: https://api.skillmd.com/api/skills/aliyun/alibabacloud-odps-information-schema/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: Apache-2.0
- Author: aliyun (https://skillmd.com/u/aliyun)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/aliyun/alibabacloud-odps-information-schema

---


# 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 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 <a name="column-reference"></a>

| 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](references/verified-queries.md).

## Routing Index <a name="routing"></a>

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](references/joins.md) |
| **Named metric/template** | "comment coverage", "CU trend", "zombie table detection" | [references/verified-queries.md](references/verified-queries.md) + [references/metrics.md](references/metrics.md) |
| **Multi-step diagnosis** | "Why did CU spike?", root-cause analysis | [references/playbooks.md](references/playbooks.md) + [references/causal-templates.md](references/causal-templates.md) |
| **Non-English synonyms** | "cpu时间", "作业时长", "存储占用", or any CJK/localized terms | [references/terminology.md](references/terminology.md) (or use inline mapping below) |
| **Schema/field lookup** | "What columns does X have?" | [references/views-reference.md](references/views-reference.md) |
| **Access denied error** | Permission denied on IS view | [references/ram-policies.md](references/ram-policies.md) |
| **Troubleshooting** | Table not found, timeout, etc. | [references/TROUBLESHOOTING.md](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 |

<!-- SYNC: derived from references/joins.md paths #1 #2 #3 #4 #5 #8 #10 -->
### 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](references/joins.md). The 7 most common paths are inlined below.

<!-- 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 -->
### 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](references/terminology.md).

## Error Recovery <a name="error-recovery"></a>

| 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](references/verified-queries.md#q30-namespace-flag-verification) |
| `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 |
| `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 |
| `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](references/TROUBLESHOOTING.md) for T1–T7 scenarios |

## Core Views <a name="core-views"></a>

| 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](references/views-reference.md). Views marked ⚠️ are **tenant-level only** (not available in project-level IS).

## Additional Resources <a name="resources"></a>

- [references/views-reference.md](references/views-reference.md) — Complete field definitions for all 31 IS views
- [references/verified-queries.md](references/verified-queries.md) — 30 pre-validated SQL query templates (including smoke test)
- [references/entities.md](references/entities.md) — Entity-to-table mapping
- [references/metrics.md](references/metrics.md) — Metric definitions with SQL expressions
- [references/joins.md](references/joins.md) — Join paths between views
- [references/playbooks.md](references/playbooks.md) — 23 diagnostic scenario playbooks
- [references/causal-templates.md](references/causal-templates.md) — Root-cause analysis templates
- [references/terminology.md](references/terminology.md) — 59-term synonym dictionary for NL2SQL
- [references/ram-policies.md](references/ram-policies.md) — Tenant permission setup and Policy template
- [references/mcp-tools-reference.md](references/mcp-tools-reference.md) — 15 MCP tools with routing guide + MCP setup + installation
- [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).
- [references/TROUBLESHOOTING.md](references/TROUBLESHOOTING.md) — 7 error scenarios with fix templates (T1–T7)

## Official Documentation <a name="docs"></a>

- [MaxCompute Tenant-level Information Schema](https://help.aliyun.com/zh/maxcompute/user-guide/tenant-level-information-schema)