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#

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#

MCP preferred when mcp__maxcompute-catalog__* tools are available. Fall back to odpscmd on connection/auth errors.

ChannelUse ForKey Detail**MCP (tenant-level)**DQL, metadata, searchexecute_sql + hints={"odps.namespace.schema":"true"}; sync limit 1000 rows; cost_sql supports IS views (verified 2026-04)**MCP (project-level)**DQL, metadata, searchexecute_sql + hints={} (no namespace flag); view prefix: Information_Schema.***odpscmd (tenant-level)DDL/DML, large results, MCP unavailableSET odps.namespace.schema=true; prefix requiredodpscmd (project-level)**DDL/DML, large results, MCP unavailableNo namespace flag; view prefix: Information_Schema.*

See references/mcp-tools-reference.md for 15 MCP tools with routing guide.

Important Rules#

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#

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:

TransformTenant-level (default)Project-levelView prefixSYSTEM_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 entirelyScopeAll projects in metadata centerCurrent project onlyViews unavailable—CATALOGS, VOLUMES, FOREIGN_SERVERS, SCHEMAS, PARTITION_ACCESS_INFO, TABLE_ACCESS_INFO, QUOTA_USAGEView exclusive to this level—SCHEMA_PRIVILEGESTASKS_HISTORY extra column—task_schema (project name; tenant-level lacks this)table_catalog valueActual project nameFixed odps

Example transformation:

text

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

ConceptCorrectWrongTable sizedata_lengthsize_bytes, sizeTask instanceinst_idtask_idTask submitterowner_nametask_ownerTask projecttask_catalog (tenant-level)project_name, task_schema (project-level IS only)Task errorresulterror_messageTask durationDATEDIFF(end_time, start_time, 'ss')duration_msTask statusstatustask_statusTask input sizeinput_bytesscan_bytes, processed_bytesTable commenttable_commentcommentColumn commentcolumn_commentcommentPrivilege granteeuser_name, user_idgranteePrivilege timeexpiredgrant_timeResource sizesizesize_bytesTunnel sessionsession_idtunnel_idTunnel data sizedata_sizesize_bytesUser identityidentity_provider—Timestamp typeDATETIMETIMESTAMPTable modifiedlast_modified_timelast_ddl_timecost_cpu typeDOUBLEBIGINT

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

Routing Index#

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 TypeWhenLoad Extra FileNOT an IS queryDDL/DML, list tables, run SQL, general ODPSNone — use MCP tools or odpscmd insteadSingle-view queryOne IS view, no JOINNone — SKILL.md onlyLive monitoringTASKS / QUOTA_USAGENone — SKILL.md only2+ IS view JOINCombining viewsreferences/joins.mdNamed metric/template"comment coverage", "CU trend", "zombie table detection"references/verified-queries.md + references/metrics.mdMulti-step diagnosis"Why did CU spike?", root-cause analysisreferences/playbooks.md + references/causal-templates.mdNon-English synonyms"cpu时间", "作业时长", "存储占用", or any CJK/localized termsreferences/terminology.md (or use inline mapping below)Schema/field lookup"What columns does X have?"references/views-reference.mdAccess denied errorPermission denied on IS viewreferences/ram-policies.mdTroubleshootingTable not found, timeout, etc.references/TROUBLESHOOTING.md

Anti-pattern: Do NOT load extra files for these#

User saysLooks likeActually isLoad"storage pressure, list top 20 tables"DiagnosticsSingle-viewSKILL.md only"permission audit, who has SELECT on X"PlaybookSingle-viewSKILL.md only"cost attribution by owner"CausalSingle-viewSKILL.md only

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:

LeftRightJoin ConditionTABLESCOLUMNSt.table_catalog = c.table_catalog AND t.table_schema = c.table_schema AND t.table_name = c.table_nameTABLESPARTITIONSt.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_nameTABLESTABLE_PRIVILEGESt.table_catalog = p.table_catalog AND t.table_schema = p.table_schema AND t.table_name = p.table_nameTABLESTABLE_ACCESS_INFOt.table_catalog = a.table_catalog AND t.table_schema = a.table_schema AND t.table_name = a.table_nameTABLESTABLE_LABELSt.table_catalog = l.table_catalog AND t.table_schema = l.table_schema AND t.table_name = l.table_nameUSERSUSER_ROLESu.user_id = ur.user_idCOLUMNSCOLUMN_LABELSc.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.

Inline Terminology Mapping (common non-English terms)#

Non-English termEnglish equivalentCorrect column/sourceCommon mistakecpu时间 / CPU消耗CPU time / CPU consumptioncost_cpu (DOUBLE), ÷100÷3600 = CU·hourcpu_time作业时长 / 任务耗时Task duration / task elapsed timeDATEDIFF(end_time, start_time, 'ss')duration_ms存储占用 / 表大小Storage usage / table sizedata_length (÷1073741824 = GB)size_bytes僵尸表Zombie tableTABLES + TABLE_ACCESS_INFO—排队时间Queue wait timeNOT available in IS views—CU时 / CU消耗CU-hours / CU consumptionSUM(cost_cpu) / 100.0 / 3600—任务失败率Task failure ratestatus='Failed' ratio in TASKS_HISTORY—

For all 59 terms, see references/terminology.md.

Error Recovery#

Error SignalRoot CauseFixTable not found on IS viewMissing namespace flagAdd SET odps.namespace.schema=true; / hints={"odps.namespace.schema":"true"}. Verify with Q30 smoke testAccess denied / Permission denied on IS viewMissing tenant-level roleVerify access with check_access(include_grants=true). User needs tenant-level role — load references/ram-policies.md for Policy templateTable not found on SYSTEM_CATALOG.INFORMATION_SCHEMA.* (namespace flag correctly set)Environment only supports project-level ISApply Project-level IS Adaptation transformation rules to all subsequent queries: switch prefix to Information_Schema.*, remove namespace flagInformation_Schema not found / Package not installedProject-level IS not installed in this projectUser 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-levelObject 'Information_Schema' not found on new projectNew projects (since 2024-03) don't auto-install project-level ISSwitch to tenant-level IS ( SYSTEM_CATALOG.INFORMATION_SCHEMA.*) or manually install packageTASKS_HISTORY query slow/expensiveNo ds filterAdd WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -14, 'dd'), 'yyyymmdd')MCP returns exactly 1000 rowsSync limit truncationRe-run with async=true, or add tighter WHERE/LIMITColumn not foundUsed non-existent columnCheck Critical Column Reference above — common: size_bytes → data_length, task_status → statusTUNNELS_HISTORY sync timeout (>30s)Tunnel record volume much larger than TASKS_HISTORYUse async=true + get_instance, or reduce ds to 1 dayAsync timeout (>30s)Large scanUse cost_sql first; add ds filter; split queryIS view shows no recent data~5 min delay for history viewsQuery yesterday's data after 06:00odpscmd query hangsLarge result set or full-table scanUse odps_is_query.sh -t <seconds> to set timeout (default 300s); add ds filterNamespace flag set but still Table not foundOther causes (wrong project, typo, schema issue)Load references/TROUBLESHOOTING.md for T1–T7 scenarios

Core Views#

ViewPurposeKey ColumnsTABLESTable metadatatable_name, owner_name, data_length, table_type, lifecycle, last_modified_timeCOLUMNSColumn metadatacolumn_name, data_type, column_comment, is_partition_keyPARTITIONSPartition metadatapartition_name, data_length, create_time, last_modified_timeTASKSRunning jobs (live, seconds delay)inst_id, task_name, owner_name, status, cpu_usage (core×100), mem_usage (MB)TASKS_HISTORYQuery historyinst_id, task_name, owner_name, status, task_type, start_time, end_time, result, cost_cpu, input_bytes, dsTUNNELS_HISTORYTunnel historysession_id, object_name, operate_type, data_size, owner_name, dsTABLE_PRIVILEGESTable permissionstable_name, user_name, privilege_type, expiredTABLE_ACCESS_INFO ⚠️Table access statstable_name, access_count, access_bytes, last_access_timeQUOTA_USAGESubscription quota monitoringname, cpu_elastic_quota_max, cpu_elastic_quota_used, mem_elastic_quota_max, mem_elastic_quota_usedUSERSProject usersuser_name, user_id, identity_providerUSER_ROLESUser-role mappinguser_name, role_name, user_role_catalogCATALOGS ⚠️Project listcatalog_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#

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

• MaxCompute Tenant-level Information Schema