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

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

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

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
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 <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. Views marked ⚠️ are tenant-level only (not available in project-level IS).

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

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

• MaxCompute Tenant-level Information Schema