ODPS Information Schema

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.

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

mcp__maxcompute-catalog__*

execute_sql

hints={"odps.namespace.schema":"true"}

cost_sql

execute_sql

hints={}

Information_Schema.*

SET odps.namespace.schema=true;

Information_Schema.*

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>

SYSTEM_CATALOG.INFORMATION_SCHEMA.*

SYSTEM_CATALOG.INFORMATION_SCHEMA.

Information_Schema.

hints={"odps.namespace.schema":"true"}

hints={}

SET odps.namespace.schema=true;

task_schema

table_catalog

odps

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

Table not found

Critical Column Name Reference <a name="column-reference"></a>

data_length

inst_id

owner_name

task_catalog

result

DATEDIFF(end_time, start_time, 'ss')

status

input_bytes

table_comment

column_comment

user_name

user_id

expired

size

session_id

data_size

identity_provider

DATETIME

last_modified_time

DOUBLE

For verified query examples using these columns, see references/verified-queries.md.

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

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

Anti-pattern: Do NOT load extra files for these

Inline Join Conditions (for 2+ view JOINs)

table_catalog

table_schema

table_name

t.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_name

t.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_name

t.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_name

t.table_catalog = a.table_catalog AND t.table_schema = a.table_schema AND t.table_name = a.table_name

t.table_catalog = l.table_catalog AND t.table_schema = l.table_schema AND t.table_name = l.table_name

u.user_id = ur.user_id

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

Inline Terminology Mapping (common non-English terms)

cost_cpu

DATEDIFF(end_time, start_time, 'ss')

data_length

SUM(cost_cpu) / 100.0 / 3600

status='Failed'

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

Table not found

SET odps.namespace.schema=true;

hints={"odps.namespace.schema":"true"}

Access denied

Permission denied

check_access(include_grants=true)

Table not found

SYSTEM_CATALOG.INFORMATION_SCHEMA.*

Information_Schema.*

Information_Schema not found

Package not installed

install package Information_Schema.systables

Information_Schema.view_name

Object 'Information_Schema' not found

SYSTEM_CATALOG.INFORMATION_SCHEMA.*

ds

WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -14, 'dd'), 'yyyymmdd')

async=true

Column not found

size_bytes

data_length

task_status

status

async=true

get_instance

cost_sql

ds

odps_is_query.sh -t <seconds>

ds

Table not found

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

TABLES

COLUMNS

PARTITIONS

TASKS

TASKS_HISTORY

TUNNELS_HISTORY

TABLE_PRIVILEGES

TABLE_ACCESS_INFO

QUOTA_USAGE

USERS

USER_ROLES

CATALOGS

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