alibabacloud-odps-information-schema

What this skill does

# 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 app