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.

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:

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>

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.

Anti-pattern: Do NOT load extra files for these

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

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)

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

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

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

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

Causal Analysis Templates

Related: playbooks.md, verified-queries.md, SKILL.md

Root-cause investigation patterns. Each template defines: trigger condition, hypothesis dimensions with weights, verification queries, and recommendations.

MCP Tip: Before running causal analysis queries, use check_access to verify permissions and get_project to check project configuration. Use list_tables to confirm the existence of relevant IS views.

Placeholder Filling Rules

These rules apply to all templates below:

• Date placeholders: Always use DATEADD expressions, not literal date strings (format may be wrong, and TASKS_HISTORY has ~5 min delay):
• <yesterday> → TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
• <today> → Avoid using (data may be incomplete due to delay); if required, use TO_CHAR(GETDATE(), 'yyyymmdd')
• <baseline_period> → TO_CHAR(DATEADD(GETDATE(), -8, 'dd'), 'yyyymmdd') through TO_CHAR(DATEADD(GETDATE(), -2, 'dd'), 'yyyymmdd')
• `{{primary_dimension}}`: Select the dimension with the highest (weight × percentage_change). Example: if owner_name (weight 0.45) changed 40% and task_type (weight 0.30) changed 20%, pick owner_name because 0.45 × 40 = 18 > 0.30 × 20 = 6.
• `ds` filter: Every TASKS_HISTORY/TUNNELS_HISTORY query MUST include a ds filter.

1. Cost Spike Analysis (cost_spike)

Trigger: CU-hour consumption increased by 20%+ compared to baseline.

Hypothesis Dimensions:

• owner_name (weight: 0.45) -- Which user submitted the most additional tasks?
• task_type (weight: 0.30) -- Is a specific task type driving the increase?
• task_catalog (weight: 0.25) -- Is the increase concentrated in one project?

Verification Queries:

-- By owner_name
SELECT owner_name, COUNT(*) AS task_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds IN (TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd'), TO_CHAR(GETDATE(), 'yyyymmdd'))
GROUP BY owner_name
ORDER BY cu_hours DESC
LIMIT 10;

-- By task_type
SELECT task_type, COUNT(*) AS task_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds IN (TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd'), TO_CHAR(GETDATE(), 'yyyymmdd'))
GROUP BY task_type
ORDER BY cu_hours DESC;

-- By task_catalog
SELECT task_catalog, COUNT(*) AS task_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds IN (TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd'), TO_CHAR(GETDATE(), 'yyyymmdd'))
GROUP BY task_catalog
ORDER BY cu_hours DESC
LIMIT 10;

Judgment Rules:

• Support hypothesis: If cost proportion change for a specific dimension value exceeds 20%
• Refute hypothesis: If all dimension values show stable cost distribution (change < 5%)

Recommended Actions:

• Check {{primary_dimension}} task queue for abnormal tasks
• Consider setting cost alert thresholds for {{primary_dimension}}

2. Storage Spike Analysis (storage_spike)

Trigger: Total storage increased by 30%+ compared to baseline.

Hypothesis Dimensions:

• table_name (weight: 0.50) -- Which table grew the most?
• owner_name (weight: 0.35) -- Which owner's tables grew?
• table_catalog (weight: 0.15) -- Is the growth in one project?

Verification Queries:

-- Top growing tables
SELECT table_name, owner_name, COALESCE(data_length, 0) / 1024 / 1024 / 1024 AS size_gb,
       lifecycle
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'MANAGED_TABLE'
ORDER BY COALESCE(data_length, 0) DESC
LIMIT 20;

-- Storage by owner
SELECT owner_name,
       COUNT(*) AS table_count,
       SUM(COALESCE(data_length, 0)) / 1024 / 1024 / 1024 AS total_tb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
GROUP BY owner_name
ORDER BY total_tb DESC;

Judgment Rules:

• Support hypothesis: If storage proportion change for a specific dimension value exceeds 30%
• Refute hypothesis: If all dimension values show stable storage distribution

Recommended Actions:

• Check {{primary_dimension}} data import task frequency
• Consider setting lifecycle policies to clean up expired data

3. Job Failure Spike Analysis (job_failure_spike)

Trigger: Task failure rate increased by 15%+ compared to baseline.

Hypothesis Dimensions:

• task_catalog (weight: 0.40) -- Is failure concentrated in one project?
• task_type (weight: 0.35) -- Is a specific task type failing?
• owner_name (weight: 0.25) -- Is one user's jobs failing?

Verification Queries:

-- Failure rate by project
SELECT task_catalog,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS failed,
       SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS failure_rate
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
GROUP BY task_catalog
ORDER BY failed DESC;

-- Failure details
SELECT task_name, task_type, owner_name, start_time, result
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
  AND status = 'Failed'
ORDER BY start_time DESC;

Recommended Actions:

• Check {{primary_dimension}} resource configuration for adequacy
• Consider increasing {{primary_dimension}} compute resource quota

4. Partition Growth Analysis (partition_growth)

Trigger: Partition count increased by 50%+ on a table.

Hypothesis Dimensions:

• table_name -- Which tables have the most partitions?
• partition_pattern -- Are partitions following expected naming patterns?

Verification Queries:

-- Tables with most partitions
SELECT table_name,
       COUNT(*) AS partition_count,
       SUM(data_length) / 1024 / 1024 AS size_mb,
       MIN(partition_name) AS oldest_partition,
       MAX(partition_name) AS newest_partition
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.PARTITIONS
GROUP BY table_name
HAVING COUNT(*) > 5000
ORDER BY partition_count DESC;

Recommendation Template:

• Set lifecycle on partitioned tables to auto-expire old partitions
• Consider partition-level merges for small partitions
• Review partition creation scripts for unintended patterns

5. Permission Change Analysis (permission_change)

Trigger: Grant count changed by 25%+ compared to baseline.

Hypothesis Dimensions:

• user_name -- Who received new permissions?
• privilege_type -- What type of permissions changed?
• table_name -- Which tables had permission changes?

Verification Queries:

-- Permission distribution (active grants only)
SELECT user_name, privilege_type, COUNT(*) AS grant_count
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE expired IS NULL OR expired > GETDATE()
GROUP BY user_name, privilege_type
ORDER BY grant_count DESC;

-- Tables with most grantees (active grants only)
SELECT table_name, COUNT(DISTINCT user_name) AS grantee_count
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE expired IS NULL OR expired > GETDATE()
GROUP BY table_name
ORDER BY grantee_count DESC
LIMIT 20;

Recommendation Template:

• Review recent grant activities for unexpected changes
• Ensure least-privilege principle is maintained
• Check for orphan users (users with permissions but no active roles)

Entities Reference

Related: views-reference.md, terminology.md, SKILL.md

Entity-to-table mappings for INFORMATION_SCHEMA views. All queries use SYSTEM_CATALOG.INFORMATION_SCHEMA.*.

Entity-Table Mapping

Join Paths Reference

Related: views-reference.md, verified-queries.md, SKILL.md

Trusted join relationships between INFORMATION_SCHEMA views. All views are in SYSTEM_CATALOG.INFORMATION_SCHEMA.

Important: Always include table_catalog in join conditions for robustness, especially for non-3-tier model projects where table_schema may be NULL.

MCP Tip: Before constructing multi-table JOINs, use list_tables to confirm tables exist and get_table_schema to verify column names. This prevents errors from misspelled table/column names in complex queries.

Join Paths

Common Multi-Table Queries

Table + Column + Privilege

SELECT t.table_catalog, t.table_name, t.owner_name, t.data_length,
       c.column_name, c.data_type, c.column_comment,
       p.user_name, p.privilege_type
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES t
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.COLUMNS c
  ON t.table_catalog = c.table_catalog AND t.table_name = c.table_name
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLE_PRIVILEGES p
  ON t.table_catalog = p.table_catalog AND t.table_name = p.table_name
WHERE t.table_name = 'my_table';

Table + Partition Count

SELECT t.table_catalog, t.table_name, t.owner_name, t.data_length,
       COUNT(DISTINCT p.partition_name) AS partition_count
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES t
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.PARTITIONS p
  ON t.table_catalog = p.table_catalog AND t.table_name = p.table_name
WHERE t.table_type = 'MANAGED_TABLE'
GROUP BY t.table_catalog, t.table_name, t.owner_name, t.data_length
ORDER BY t.data_length DESC
LIMIT 20;

User + Role + Permission Chain

SELECT ur.user_role_catalog, ur.user_name, ur.user_id,
       r.role_name, r.role_label
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.USER_ROLES ur
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.ROLES r
  ON ur.role_name = r.role_name AND ur.user_role_catalog = r.role_catalog
ORDER BY ur.user_role_catalog, ur.user_name;

Admin Role Users

SELECT user_role_catalog, role_name, user_name, user_id
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.USER_ROLES
WHERE role_name IN ('super_administrator', 'admin')
ORDER BY user_role_catalog, role_name;

Data Lineage: Upstream Tables

SELECT task_catalog, task_name, inst_id, input_tables, output_tables
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
  AND input_tables LIKE '%my_project.my_table%'
ORDER BY end_time DESC
LIMIT 50;

Data Lineage: Downstream Tables

SELECT task_catalog, task_name, inst_id, output_tables
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
  AND output_tables LIKE '%my_project.my_table%'
ORDER BY end_time DESC
LIMIT 50;

Project Settings Audit

SELECT catalog_name, region, status,
       get_json_object(json_parse(settings), '$."odps.timemachine.retention.days"') AS backup_days,
       get_json_object(json_parse(settings), '$."odps.security.ip.whitelist"') AS ip_whitelist,
       get_json_object(json_parse(settings), '$."odps.sql.metering.value.max"') AS sql_cost_limit
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS;

Storage Tier Distribution

SELECT storage_tier,
       COUNT(*) AS table_count,
       SUM(COALESCE(data_length, 0)) / 1024 / 1024 / 1024 AS storage_gb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'MANAGED_TABLE'
GROUP BY storage_tier
ORDER BY storage_gb DESC;

Permission Exposure Analysis

SELECT table_catalog, table_schema, table_name,
       COUNT(DISTINCT user_name) AS user_count,
       COLLECT_SET(privilege_type) AS privilege_types
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLE_PRIVILEGES
GROUP BY table_catalog, table_schema, table_name
ORDER BY user_count DESC
LIMIT 50;

MCP Tools Reference

Related: SKILL.md

Complete reference for maxcompute-catalog MCP tools used by odps_is skill. This document supplements SKILL.md with detailed MCP execution guidance.

MCP Availability Detection

If mcp__maxcompute-catalog__* tools are available in your tool list, use MCP as the primary channel. If an MCP tool call returns a connection error or auth failure, fall back to odpscmd for all subsequent queries.

Degradation rules: 1. Connection/auth error → mark MCP unavailable, use odpscmd for all subsequent queries 2. SQL execution error (syntax, permission) → do NOT degrade, this is a business-level error 3. Result truncation (exactly 1000 rows) → warn user, suggest odpscmd for complete data

Tool Overview

Metadata Exploration

list_projects / get_project

• list_projects: Lists all accessible project IDs (paginated via pageSize + token)
• get_project: Returns project details including schemaEnabled (critical for determining 2-level vs 3-level naming model)
• Fallback: SELECT * FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS

list_schemas / get_schema

• list_schemas: Lists schemas under a project (returns synthetic "default" for 2-level projects)
• get_schema: Returns schema details and metadata
• Fallback: SELECT * FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.SCHEMAS

list_tables

• Lists tables/views in a project
• Returns namingModel ("3-level" or "2-level") to guide SQL table references
• Supports filter parameter for table name prefix matching
• Fallback: odpscmd SQL

get_table_schema

• Returns column names, types, partition keys
• Critical: Returns sqlTableRef (exact table reference for SQL) and sqlExample
• Always call this before writing SQL to ensure correct table reference format
• Fallback: odpscmd SQL

get_partition_info

• Lists partitions with data size, create_time, last_modified_time
• Paginated (pageSize + token)
• Fallback: odpscmd SQL

SQL Execution

execute_sql

Execute SQL queries. Key parameters:

Namespace Flag (verified): Two working methods (choose one): 1. Via hints: hints={"odps.namespace.schema":"true"} (recommended) 2. Via SQL prefix: SET odps.namespace.schema=true; SELECT ... (also works, MCP uses script mode)

DQL Whitelist: SELECT, WITH, SHOW, DESC, DESCRIBE, EXPLAIN, VALUES, SET

DML Blacklist: INSERT, UPDATE, DELETE, MERGE, UPSERT, TRUNCATE, CREATE, DROP, ALTER, RENAME, GRANT, REVOKE, CALL, EXEC, EXECUTE, LOAD, UNLOAD, COPY, MSCK, REPAIR

Result Format:

• Sync mode (open_reader path): {"success":true, "columns":[...], "data":[...], "rowCount":N} — max 1000 rows
• Sync mode (no open_reader): {"success":true, "data":["line1","line2"], "rawOutput":"...", "rowCount":N} — for SHOW/DESC
• 1000-row truncation: if rowCount === 1000, warn user that results may be incomplete

cost_sql

• Estimates CU cost, input bytes, complexity without executing
• Supports both user tables and IS views (verified 2026-04): Returns real estimates including estimatedCU, inputBytes, complexity, udfCount
• Recommended before executing multi-day TASKS_HISTORY or TUNNELS_HISTORY queries to avoid unexpected costs

get_instance_status

• Poll async job: status, CU usage, progress, logView URL
• Returns: instanceId, status (RUNNING/TERMINATED/FAILED), isTerminated, isSuccessful

get_instance

• Retrieve completed async query results
• No row limit (unlike sync execute_sql)
• Result format varies:
• Has open_reader → structured JSON: {"columns":[...], "data":[...]}
• No open_reader → CSV string: "col1","col2"\n"val1","val2"
• Agent should handle both formats

Async Execution Pattern

When to use async=true (meet ANY condition): 1. Query involves TASKS_HISTORY/TUNNELS_HISTORY with ds range >= 7 days 2. Expected result rows > 500 3. Multi-table JOIN involving large tables (TABLES/PARTITIONS full scan) 4. Sync mode timeout (instanceId returned after timeout)

Pattern: 1. Submit: execute_sql(async=true, hints={"odps.namespace.schema":"true"}) → get instanceId 2. Poll: get_instance_status(instanceId) → check isTerminated 3. Retrieve: get_instance(instanceId) → get results

Search

search_meta_data

• Cross-project search for tables/resources/schemas
• Requires namespace_id configuration (config.json or MAXCOMPUTE_NAMESPACE_ID env var)
• Query syntax: name:foo,type=TABLE or description:bar,type=TABLE,project=proj
• Type is mandatory: TABLE, RESOURCE, or SCHEMA
• Fallback when unavailable: SQL LIKE query on IS views

Security

check_access

• Returns identity info (account type, masked AK, default project)
• Optional: include_grants=true to run SHOW GRANTS
• Use to verify MCP connection and permissions before complex operations

Table Design

create_table

• Creates tables using pyodps SDK compute.create_table() (NOT execute_sql)
• Supports columns, partitionColumns, lifecycle, comment
• Fallback: odpscmd DDL

insert_values

• Inserts rows via compute.run_sql() with SQL injection protection
• Backtick-escaped identifiers, quoted values
• Supports partitioned tables (groups by partition key)
• Fallback: odpscmd DML

IS Query Patterns via MCP

Pattern 1: Simple IS Query (sync)

execute_sql(
  sql="SELECT table_name, data_length FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES LIMIT 20",
  hints={"odps.namespace.schema":"true"},
  async=false
)

Pattern 2: Historical IS Query (async)

execute_sql(
  sql="SELECT ds, COUNT(*) AS task_count FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd') GROUP BY ds ORDER BY ds DESC",
  hints={"odps.namespace.schema":"true"},
  async=true
)
→ get_instance_status(instanceId) until isTerminated
→ get_instance(instanceId)

Pattern 3: Metadata First, Then SQL

1. list_tables(filter="my_table") → confirm table exists
2. get_table_schema(table="my_table") → get sqlTableRef
3. execute_sql(sql="SELECT ... FROM <sqlTableRef> ...", hints={"odps.namespace.schema":"true"})

Routing Decision Matrix

MCP Setup

1. Install maxcompute-catalog-mcp Python package 2. Create config file (e.g., config.json):

{
  "maxcompute": {
    "catalogapi_endpoint": "catalogapi.cn-hangzhou.maxcompute.aliyun.com",
    "maxcompute_endpoint": "http://service.odps.aliyun.com/api",
    "accessKeyId": "YOUR_AK",
    "accessKeySecret": "YOUR_SK",
    "defaultProject": "YOUR_PROJECT",
    "namespaceId": "YOUR_NAMESPACE_ID"
  }
}

Security warning: Never commit real credentials to source control. Use environment variables or a secrets manager. Add config.json to .gitignore.

Important: catalogapi_endpoint must NOT include https:// prefix.

3. Add to .mcp.json in your project root:

{
  "mcpServers": {
    "maxcompute-catalog": {
      "command": "/path/to/python3",
      "args": ["-m", "maxcompute_catalog_mcp", "--config", "/path/to/config.json"]
    }
  }
}

Configuration Keys

Verification

After configuration, verify MCP works: 1. check_access → Should return identity info 2. list_tables → Should return table list with namingModel 3. execute_sql with hints={"odps.namespace.schema":"true"} → Should return IS query results

Installation

QoderWork Skills Directory

cp -r odps_is ~/.qoderwork/skills/

Or place odps_is in your project's skills/ directory.

odpscmd Configuration

Edit scripts/odps_is_query.sh line 5 to set ODPS_CMD, or use environment variable:

export ODPS_CMD="/path/to/your/odpscmd"

CLI Usage

ODPS_CMD=/path/to/odpscmd ./scripts/odps_is_query.sh top-storage
ODPS_CMD=/path/to/odpscmd ./scripts/odps_is_query.sh failed-tasks -d 20240101
ODPS_CMD=/path/to/odpscmd ./scripts/odps_is_query.sh custom 'SELECT ...'

Supported types: tables, top-storage, columns, partitions, failed-tasks, cu-hours, cost-by-owner, cost-by-type, quota-usage, permissions, permission-audit, user-roles, comment-coverage, tunnel-daily, zombie-tables, smoke-test, custom.

MCP vs odpscmd Feature Matrix

Metrics Reference

Related: verified-queries.md, views-reference.md, SKILL.md

All metrics with SQL expressions. Query INFORMATION_SCHEMA views with SET odps.namespace.schema=true;.

Storage Metrics

Cost Metrics

Important notes:

• cost_cpu is DOUBLE type, unit = 100 core seconds. Convert to CU-hours: SUM(cost_cpu) / 100.0 / 3600
• cost_mem is DOUBLE type, unit = MB * seconds
• TASKS_HISTORY status values: Terminated (normal finish), Failed, Cancelled
• TABLES table_type values: MANAGED_TABLE, VIRTUAL_VIEW, EXTERNAL_TABLE, MATERIALIZED_VIEW, METADATA_TABLE, OBJECT_TABLE

Permission Metrics

Governance Metrics

Note: COLUMNS view has NO time field (last_modified_time exists in TABLES/PARTITIONS only). column_comment_coverage can only be computed as a static snapshot, not as a time-series metric.

Hotness Metrics

Tunnel Metrics

Quota Metrics

Object Count Metrics

Advanced Task Metrics

Storage Tier Metrics

Cluster Table Metrics

Alert Thresholds

Playbook Reference

Related: causal-templates.md, verified-queries.md, SKILL.md

Diagnostic scenarios for common operational concerns. Each playbook provides: intent signals, required tables, preferred metrics/dimensions, and suggested queries.

1. Storage Pressure (storage_pressure)

Intent signals: "存储满了", "storage full", "space pressure", "磁盘不够", "storage trend", "storage growth"

Required tables: TABLES, PARTITIONS

Key metrics: total_storage_tb, storage_bytes, partition_count, zombie_table_count, stale_table_count

MCP Quick Path: list_tables -> browse by size -> execute_sql + hints={"odps.namespace.schema":"true"} for aggregate queries

SQL Path / Check sequence: 1. Total storage: SELECT SUM(COALESCE(data_length, 0)) / 1024 / 1024 / 1024 / 1024 AS total_tb FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES; 2. Top tables by size: Top 20 tables by data_length 3. Zombie tables: Tables not modified in 90+ days 4. Partition explosion risk: Tables with >500 partitions 5. Stale tables: Tables not modified in 30+ days

Suggested prompt: "Analyze storage usage. Show top tables by size, identify zombie tables, and check for partition explosion risks."

2. Cost Pressure (cost_pressure)

Intent signals: "费用太高", "cost too high", "compute cost", "CU消耗大", "扫描成本", "资源消耗"

Required tables: TASKS_HISTORY

Key metrics: cu_hour_consumed, task_cost_proxy, task_count, task_failure_rate

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} (async recommended for 7+ day ds range)

SQL Path / Check sequence: 1. Daily CU-hour trend: Group by ds 2. Top cost tasks: By cost_cpu descending 3. Cost breakdown by owner: GROUP BY owner_name 4. Cost breakdown by task type: GROUP BY task_type 5. Cost breakdown by project: GROUP BY task_catalog

Suggested prompt: "Analyze compute costs. Show daily CU-hour trend, top cost consumers, and breakdown by owner and task type."

3. Task Failure Spike (task_failure_spike)

Intent signals: "任务失败", "job failed", "error rate", "执行失败", "失败率升高", "作业异常"

Required tables: TASKS_HISTORY

Key metrics: failed_task_count, task_failure_rate, task_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} -> check_access to verify permissions

SQL Path / Check sequence: 1. Daily failure count and rate: SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) 2. Failure breakdown by task_type 3. Failure breakdown by owner_name 4. Top failed tasks with error details (result field)

Suggested prompt: "Analyze task failures. Show failure rate trend, breakdown by type and owner, and list top failed tasks."

4. Permission Audit (permission_audit)

Intent signals: "权限审计", "who has access", "permission review", "安全审计", "高危授权", "越权风险"

Required tables: TABLE_PRIVILEGES, USERS, ROLES, USER_ROLES, COLUMNS, COLUMN_PRIVILEGES

Key metrics: grant_count, column_level_privilege_count, label_protected_table_count, admin_role_user_count

MCP Quick Path: check_access -> execute_sql + hints={"odps.namespace.schema":"true"} for privilege details

SQL Path / Check sequence: 1. Total grant count 2. Permission exposure per table: COUNT(DISTINCT user_name) per table 3. Column-level privileges 4. Admin role users: USER_ROLES WHERE role_name IN ('super_administrator', 'admin') 5. Users without roles (orphan users)

Suggested prompt: "Audit permissions. Show grant distribution, column-level privileges, and identify users without roles."

5. Hot Table Analysis (hot_table_analysis)

Intent signals: "热表", "most accessed table", "频繁访问的表", "热点表"

Required tables: TASKS_HISTORY, TABLES, TABLE_ACCESS_INFO

Key metrics: table_access_count, table_hotness_score

MCP Quick Path: list_tables -> execute_sql + hints={"odps.namespace.schema":"true"} for access pattern analysis (async recommended for 7+ day ds range)

SQL Path / Check sequence: 1. Tables by access frequency from TABLE_ACCESS_INFO 2. Top 10 most accessed tables from TASKS_HISTORY input_tables 3. Hot tables with storage sizes (join TABLES) 4. Zombie tables (not accessed in 90+ days)

Suggested prompt: "Find the most accessed tables. Show top 10 by query frequency and their storage sizes."

6. Metadata Governance Gap (metadata_governance_gap)

Intent signals: "注释覆盖率", "comment coverage", "governance gap", "元数据治理"

Required tables: TABLES, COLUMNS

Key metrics: table_comment_coverage, column_comment_coverage

MCP Quick Path: list_tables -> execute_sql + hints={"odps.namespace.schema":"true"} for coverage metrics

SQL Path / Check sequence: 1. Table comment coverage percentage 2. Column comment coverage percentage 3. Tables missing comments 4. Columns missing comments

Suggested prompt: "Check metadata governance. Show table and column comment coverage, list tables/columns missing comments."

7. Task Performance (task_performance)

Intent signals: "任务慢", "slow query", "performance", "执行时间长"

Required tables: TASKS_HISTORY

Key metrics: avg_task_duration, p99_task_duration, task_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} (async recommended for large ds range)

SQL Path / Check sequence: 1. Top 20 longest running tasks: ORDER BY DATEDIFF(end_time, start_time, 'ss') DESC 2. P99 duration by task type 3. Tasks with high cost_cpu but low input_bytes (inefficient)

Suggested prompt: "Analyze task performance. Show top 20 slowest tasks, P99 duration by type, and identify inefficient queries."

8. Data Transfer Audit (data_transfer_audit)

Intent signals: "下载审计", "tunnel audit", "data export", "数据传输", "公网下载"

Required tables: TUNNELS_HISTORY

Key metrics: daily_tunnel_volume (SUM(data_size))

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} -> async recommended for tunnel history queries

SQL Path / Check sequence: 1. Daily tunnel volume trend: SUM(data_size) GROUP BY ds 2. Top downloads by data_size: ORDER BY data_size DESC 3. Public IP download tracing: GROUP BY client_ip for DOWNLOAD operations 4. Failed tunnel transfers

Suggested prompt: "Audit data transfers. Show daily tunnel volume trend, top downloads, and trace public IP access."

9. User Role Audit (user_role_audit)

Intent signals: "用户角色", "user roles", "role matrix", "角色分配"

Required tables: USERS, ROLES, USER_ROLES

Key metrics: admin_role_user_count

MCP Quick Path: check_access -> execute_sql + hints={"odps.namespace.schema":"true"} for role matrix

SQL Path / Check sequence: 1. User-role matrix: JOIN USER_ROLES with ROLES 2. Users with multiple roles 3. Roles with no users (orphan roles) 4. Admin/super_administrator users

Suggested prompt: "Show user-role matrix. Identify users with excessive roles and orphan roles."

10. Partition Lifecycle (partition_lifecycle)

Intent signals: "分区太多", "partition explosion", "分区治理"

Required tables: PARTITIONS, TABLES

Key metrics: partition_count, avg_partition_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for partition queries -> list_tables for context

SQL Path / Check sequence: 1. Tables with most partitions: GROUP BY table_name HAVING COUNT > 500 2. Partition count by storage_tier 3. Small partitions (<1MB) 4. Partitions with lifecycle_enabled = false

Suggested prompt: "Analyze partition usage. Show tables with most partitions, identify small partitions, and check lifecycle settings."

11. Quota Resource Monitoring (quota_resource_monitoring)

Intent signals: "quota 使用", "资源使用率", "CPU利用率", "内存使用", "配额监控"

Required tables: QUOTA_USAGE

Key metrics: quota_cpu_usage_ratio, quota_mem_usage_ratio, quota_reserved_cpu_usage_ratio

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for quota usage queries

SQL Path / Check sequence: 1. Current elastic CPU and memory usage: cpu_elastic_quota_used / cpu_elastic_quota_max 2. Reserved CPU and memory usage: cpu_quota_used / cpu_quota_max 3. Quotas approaching limits (>80%) 4. Usage trend over time by region

Suggested prompt: "Monitor quota usage. Show current CPU and memory utilization by quota group."

12. Package Management (package_management)

Intent signals: "package 审计", "package audit", "包管理", "共享包"

Required tables: INSTALLED_PACKAGES, PACKAGE_OBJECTS, PACKAGE_PRIVILEGES

Key metrics: package_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for package inventory and privileges

SQL Path / Check sequence: 1. Installed packages by project 2. Package object types distribution 3. Package privilege audit

Suggested prompt: "Audit installed packages. Show package distribution by project and their permissions."

13. UDF Management (udf_management)

Intent signals: "UDF 审计", "UDF audit", "自定义函数", "函数管理"

Required tables: UDFS, UDF_RESOURCES, UDF_PRIVILEGES

Key metrics: udf_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for UDF inventory and dependencies

SQL Path / Check sequence: 1. UDF list by project 2. UDF resource dependencies 3. UDF privilege audit

Suggested prompt: "Audit UDFs. Show UDF list, resource dependencies, and permissions."

14. Resource Audit (resource_audit)

Intent signals: "资源审计", "resource audit", "资源管理"

Required tables: RESOURCES, RESOURCE_PRIVILEGES

Key metrics: resource_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for resource inventory and privileges

SQL Path / Check sequence: 1. Resource list by type (jar, archive, table, py) 2. Resource privilege audit 3. Temporary resources

Suggested prompt: "Audit resources. Show resource distribution by type and their permissions."

15. Catalog & Schema Overview (catalog_schema_overview)

Intent signals: "项目概览", "project overview", "schema 结构", "组织"

Required tables: CATALOGS, SCHEMAS

Key metrics: catalog_count, schema_count

MCP Quick Path: list_projects -> list_schemas -> execute_sql + hints={"odps.namespace.schema":"true"} for settings audit

SQL Path / Check sequence: 1. Project list with status and region 2. Schema organization per project 3. Project settings audit (backup, IP whitelist, cost limits)

Suggested prompt: "Show project and schema overview. Include project settings and status."

16. Data Security LABEL (data_security_label)

Intent signals: "安全标签", "label audit", "LABEL 管理", "数据安全"

Required tables: TABLE_LABELS, COLUMN_LABELS, TABLE_LABEL_GRANTS, COLUMN_LABEL_GRANTS

Key metrics: label_protected_table_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for LABEL inventory and grant queries

SQL Path / Check sequence: 1. Tables with LABEL protection 2. Columns with LABEL protection 3. LABEL grant distribution 4. Expired LABEL grants

Suggested prompt: "Audit LABEL security. Show protected tables/columns and grant distribution."

17. Access Pattern Analysis (access_pattern_analysis)

Intent signals: "访问模式", "access pattern", "访问频率", "热点分析"

Required tables: TABLE_ACCESS_INFO, PARTITION_ACCESS_INFO, TASKS_HISTORY

Key metrics: table_access_count, partition_access_count

MCP Quick Path: list_tables -> execute_sql + hints={"odps.namespace.schema":"true"} for access frequency analysis (async recommended for 7+ day ds range)

SQL Path / Check sequence: 1. Table access frequency by project 2. Partition access hotspots 3. Access pattern by time (daily trend)

Suggested prompt: "Analyze access patterns. Show table access frequency and partition hotspots."

18. Real-time Task Monitoring (realtime_task_monitoring)

Intent signals: "实时任务", "running tasks", "当前任务", "监控"

Required tables: TASKS (live view, Preview)

Key metrics: running_task_count

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for live task queries (TASKS view)

SQL Path / Check sequence: 1. Currently running tasks with CPU/memory usage 2. Tasks waiting in queue 3. Resource usage by quota

Suggested prompt: "Show currently running tasks. Include CPU and memory usage."

19. Data Lineage Tracking (data_lineage_tracking) [NEW]

Intent signals: "数据血缘", "lineage", "上下游依赖", "影响面", "表依赖"

Required tables: TASKS_HISTORY

Key metrics: table_hotness_score (based on input_tables/output_tables)

MCP Quick Path: execute_sql + hints={"odps.namespace.schema":"true"} for lineage queries on input_tables/output_tables (async recommended for large ds range)

SQL Path / Check sequence: 1. Upstream tables for a given table (appearing in input_tables) 2. Downstream tables for a given table (appearing in output_tables) 3. Full dependency chain for critical tables 4. Tables with most dependencies

Suggested prompt: "Trace data lineage for table X. Show upstream and downstream dependencies."

20. Storage Tier Analysis (storage_tier_analysis) [NEW]

Intent signals: "存储分层", "storage tier", "低频存储", "长期存储", "极限存储"

Required tables: TABLES, PARTITIONS

Key metrics: storage_bytes by storage_tier

MCP Quick Path: list_tables -> execute_sql + hints={"odps.namespace.schema":"true"} for storage tier distribution

SQL Path / Check sequence: 1. Data distribution by storage_tier (standard/lowfrequency/longterm) 2. Tables eligible for tier migration 3. Extreme storage tables (table_exstore_type) 4. Cost savings from tier optimization

Suggested prompt: "Analyze storage tier distribution. Show data by tier and migration opportunities."

21. Project Configuration Audit (project_config_audit) [NEW]

Intent signals: "项目配置", "project settings", "备份配置", "IP白名单", "安全配置"

Required tables: CATALOGS

Key metrics: catalog_count with settings

MCP Quick Path: list_projects -> execute_sql + hints={"odps.namespace.schema":"true"} for CATALOGS settings audit

SQL Path / Check sequence: 1. Backup retention: get_json_object(json_parse(settings), '$."odps.timemachine.retention.days"') 2. IP whitelist audit: get_json_object(json_parse(settings), '$."odps.security.ip.whitelist"') 3. SQL cost limits: get_json_object(json_parse(settings), '$."odps.sql.metering.value.max"') 4. Projects missing backup configuration

Suggested prompt: "Audit project configurations. Check backup settings, IP whitelists, and cost limits."

22. Extreme Storage Analysis (extreme_storage_analysis)

Intent signals: "极限存储", "extreme storage", "exstore", "极限存储表", "存储压缩"

Required tables: TABLES

Key metrics: extreme_storage_bytes, storage_bytes

MCP Quick Path: list_tables -> execute_sql + hints={"odps.namespace.schema":"true"} for EXSTORE table analysis

SQL Path / Check sequence: 1. Tables with extreme storage: WHERE table_exstore_type IN ('EXSTORE_TABLE_VIRTUAL', 'EXSTORE_TABLE_PHYSICAL') 2. Virtual vs physical table ratio 3. Storage savings from extreme storage compression 4. Projects using extreme storage

Suggested prompt: "Analyze extreme storage usage. Show tables using EXSTORE and their storage savings."

23. Cluster Table Analysis (cluster_table_analysis)

Intent signals: "聚簇表", "cluster table", "HASH聚簇", "RANGE聚簇", "分桶表", "bucket"

Required tables: TABLES, PARTITIONS

Key metrics: cluster_table_count, hash_cluster_count, range_cluster_count

MCP Quick Path: list_tables -> execute_sql + hints={"odps.namespace.schema":"true"} for cluster type distribution and bucket analysis

SQL Path / Check sequence: 1. Cluster type distribution: GROUP BY cluster_type (HASH/RANGE) 2. Tables with optimal bucket count: WHERE number_buckets > 0 3. Large tables without clustering: WHERE cluster_type IS NULL AND data_length > threshold 4. Bucket count distribution by cluster_type

Suggested prompt: "Analyze cluster table distribution. Show HASH vs RANGE clustering and identify large tables missing cluster optimization."

RAM 权限清单

Related: SKILL.md, mcp-tools-reference.md

required_permissions

本 Skill 查询 MaxCompute Information Schema 元数据视图所需的 RAM 权限：

odps:Describe — 查询 IS 元数据视图内容 odps:Select — 读取 IS 视图数据 odps:List — 列举 information_schema 下的对象

以上权限均为只读权限，无通配符，符合最小权限原则。

授权说明

IS 视图包含租户级数据。默认仅阿里云主账号可访问。 RAM 子账号需要通过租户级角色显式授权。

仅主账号或拥有 Super_Administrator/Admin 角色的账号可以进行授权。

授权步骤

1. 登录 MaxCompute 控制台，选择地域 2. 进入 管理配置 > 租户管理 3. 在 角色管理 标签页创建新角色，使用下方 Policy 模板 4. 在 用户管理 标签页添加成员并分配角色

Policy 模板

{
    "Statement":[
        {
            "Action":["odps:Describe", "odps:Select"],
            "Effect":"Allow",
            "Resource":["acs:odps:*:catalogs/system_catalog/schemas/information_schema/tables/*"]
        },
        {
            "Action":["odps:List"],
            "Effect":"Allow",
            "Resource":["acs:odps:*:catalogs/system_catalog/schemas/information_schema"]
        }
    ],
    "Version":"1"
}

注意事项

• Resource 目标为 catalogs/system_catalog/schemas/information_schema/tables/*，非 project 级资源
• 这是租户级角色绑定 — 在租户管理中配置，非 project 级
• SYSTEM_CATALOG project 为只读；查询必须从同一地域的普通 project 发起

Terminology Dictionary

Related: entities.md, metrics.md, SKILL.md

59 terms for NL2SQL intent recognition, organized by type: metric (22), dimension (6), and table (31).

Metric Terms

Dimension Terms

Table Terms

Troubleshooting Guide

Related: SKILL.md, ram-policies.md, mcp-tools-reference.md

Detailed error identification and recovery procedures for ODPS Information Schema queries.

T1: "Table not found" on IS Views

Error signal:

SemanticAnalysException: Table not found: SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES

Root cause: Missing odps.namespace.schema=true flag. Without it, MaxCompute cannot resolve the cross-project SYSTEM_CATALOG reference.

Fix — MCP:

execute_sql(
  sql="SELECT ... FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES ...",
  hints={"odps.namespace.schema": "true"}
)

Fix — odpscmd:

SET odps.namespace.schema=true;
SELECT ... FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES ...;

Verification: Run Q30 smoke test:

SET odps.namespace.schema=true;
SELECT COUNT(*) AS table_count FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES LIMIT 1;

If this returns a count, the flag is working.

---

T2: "Access denied" / "Permission denied" on IS Views

Error signal:

ODPS-0130131: Authorization failed -- user XXX does not have SELECT privilege on table SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES

Root cause: Current user lacks tenant-level IS access role. Only Alibaba Cloud primary account has access by default; RAM sub-accounts need explicit role assignment.

Fix: See ram-policies.md for the complete Policy template and authorization steps. Summary:

1. Log in to MaxCompute Console → 管理配置 > 租户管理 2. Create role with odps:Describe + odps:Select on information_schema/tables/* and odps:List on information_schema 3. Assign user to the role

---

T3: TASKS_HISTORY Query Slow or Expensive

Error signal: Query takes >60s, or cost_sql returns high CU estimate.

Root cause: Missing ds partition filter. TASKS_HISTORY is partitioned by ds (date string YYYYMMDD). Without it, the engine scans all 14 days of data.

Fix: Always add a ds filter:

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

Pre-check: Use cost_sql before executing (supports IS views, verified 2026-04):

cost_sql(
  sql="SELECT ... FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY WHERE ds >= '20260401' ...",
  hints={"odps.namespace.schema": "true"}
)

---

T4: MCP Returns Exactly 1000 Rows

Error signal: execute_sql returns rowCount: 1000 — this indicates sync mode truncation.

Root cause: MCP sync mode (async=false) has a 1000-row limit. The actual result may be larger.

Fix — Option A (recommended): Re-run with async mode:

instance_id = execute_sql(sql=..., hints={"odps.namespace.schema": "true"}, async=True)
# Poll until complete
get_instance_status(instance_id)
# Retrieve full results
get_instance(instance_id)

Fix — Option B: Add tighter WHERE/LIMIT:

WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
LIMIT 500

---

T5: "Column not found" Error

Error signal:

SemanticAnalysException: Column not found: size_bytes

Root cause: Used a non-existent column name. Common mistakes documented in SKILL.md Critical Column Reference.

Most common wrong columns and their correct replacements:

Fix: Replace the wrong column with the correct one from the table above.

---

T6: Async Query Timeout (>30s)

Error signal: Sync mode returns instanceId instead of results (timeout exceeded).

Root cause: Query involves large scan (e.g., TASKS_HISTORY with wide ds range, multi-table JOIN).

Fix: 1. Pre-check with `cost_sql`: Estimate CU before running 2. Add `ds` filter: Narrow the date range 3. Use async mode explicitly:

instance_id = execute_sql(sql=..., async=True, hints={"odps.namespace.schema": "true"})
# Poll with get_instance_status
# Retrieve with get_instance (no row limit in async)

4. Split the query: Break a 14-day scan into two 7-day queries

---

T7: IS View Shows No Recent Data

Error signal: Query returns 0 rows for today's data.

Root cause: IS views have inherent delays:

• History views (TASKS_HISTORY, TUNNELS_HISTORY): ~5 min delay
• Realtime views (TABLES, COLUMNS, etc.): ~3 hours delay
• TASKS (live snapshot): Seconds delay (Preview feature)

Fix:

• For yesterday's data: query after 06:00 to ensure completeness
• For today's data: expect ~5 min latency for history, ~3 hours for realtime
• Use TASKS (not TASKS_HISTORY) for live monitoring of running jobs

Verified Queries Reference

Related: metrics.md, views-reference.md, joins.md, SKILL.md

Query Index

Storage (Q1–Q4) Q1: Top 20 Tables by Storage · Q2: Storage Breakdown by Owner · Q3: Zombie Tables (Not Accessed in 90 Days) · Q4: Partition Explosion Risk

Cost (Q5–Q8) Q5: Daily CU-Hour Trend · Q6: Cost by Task Owner · Q7: Cost by Task Type · Q8: Top 20 Most Expensive Tasks

Failure (Q9–Q11) Q9: Failed Tasks Yesterday · Q10: Daily Failure Rate · Q11: Failure Breakdown by Type

Permission (Q12–Q14) Q12: Permission Exposure · Q13: Admin Role Users · Q14: User-Role Matrix

Governance (Q15–Q16) Q15: Table Comment Coverage · Q16: Column Comment Coverage

Tunnel (Q17–Q18) Q17: Daily Tunnel Volume · Q18: Top Tunnel Downloads

Object Count (Q19) Q19: Object Inventory

Performance (Q20) Q20: Top 20 Slowest Tasks

Hot Table (Q21–Q23) Q21: Top 20 Most Accessed Tables (Last 7 Days) · Q22: Top Tables by Data Volume Read (Last 7 Days) · Q23: User Resource Consumption by Table

Official Verified (Q24–Q29) Q24: TOP Storage Tables with COALESCE · Q25: Partition Explosion Risk (Simplified) · Q26: Daily Failure Trend · Q27: CU-Hour by Project and Hour · Q28: User Resource Consumption · Q29: Project Configuration Audit

Smoke Test (Q30) Q30: Namespace Flag Verification

Pre-validated SQL queries for common INFORMATION_SCHEMA analysis scenarios. All queries require SET odps.namespace.schema=true; prefix.

Storage Queries

Q1: Top 20 Tables by Storage

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT table_name, owner_name, data_length / 1024 / 1024 / 1024 AS size_gb,
       lifecycle, last_modified_time
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'MANAGED_TABLE'
  AND data_length IS NOT NULL
ORDER BY data_length DESC
LIMIT 20;

Q2: Storage Breakdown by Owner

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT owner_name,
       COUNT(*) AS table_count,
       SUM(data_length) / 1024 / 1024 / 1024 / 1024 AS total_tb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'MANAGED_TABLE'
  AND data_length IS NOT NULL
GROUP BY owner_name
ORDER BY total_tb DESC;

Q3: Zombie Tables (Not Accessed in 90 Days)

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT table_name, owner_name, data_length / 1024 / 1024 / 1024 AS size_gb,
       last_modified_time, lifecycle
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
WHERE COALESCE(last_access_time, last_modified_time) < DATEADD(GETDATE(), -90, 'dd')
  AND table_type = 'MANAGED_TABLE'
  AND data_length IS NOT NULL
ORDER BY data_length DESC;

Q4: Partition Explosion Risk

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT table_catalog, table_schema, table_name,
       COUNT(*) AS partition_count,
       SUM(data_length) / 1024 / 1024 / 1024 AS size_gb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.PARTITIONS
GROUP BY table_catalog, table_schema, table_name
HAVING COUNT(*) > 1000
ORDER BY partition_count DESC
LIMIT 20;

Cost Queries

Q5: Daily CU-Hour Trend

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync OK for ds ≤3 days; MUST use async=true for ds >3 days

SELECT ds,
       COUNT(*) AS task_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -14, 'dd'), 'yyyymmdd')
GROUP BY ds
ORDER BY ds DESC
LIMIT 30;

Q6: Cost by Task Owner

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT owner_name,
       COUNT(*) AS task_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours,
       SUM(cost_cpu) * 100.0 / SUM(SUM(cost_cpu)) OVER () AS cost_pct
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
GROUP BY owner_name
ORDER BY cu_hours DESC;

Q7: Cost by Task Type

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT task_type,
       COUNT(*) AS task_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
GROUP BY task_type
ORDER BY cu_hours DESC;

Q8: Top 20 Most Expensive Tasks

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT task_name, task_type, owner_name, start_time,
       cost_cpu / 100.0 / 3600 AS cu_hours,
       DATEDIFF(end_time, start_time, 'ss') AS run_seconds
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
ORDER BY cost_cpu DESC
LIMIT 20;

Failure Queries

Q9: Failed Tasks Yesterday

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT task_catalog, task_name, task_type, inst_id, owner_name, start_time, end_time,
       result,
       cost_cpu / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
  AND status = 'Failed'
ORDER BY start_time DESC;

Q10: Daily Failure Rate

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync OK for ds ≤3 days; MUST use async=true for ds >3 days

SELECT ds,
       COUNT(*) AS total_tasks,
       SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS failed_tasks,
       SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS failure_rate
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -14, 'dd'), 'yyyymmdd')
GROUP BY ds
ORDER BY ds DESC
LIMIT 14;

Q11: Failure Breakdown by Type

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT task_type,
       COUNT(*) AS total,
       SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS failed,
       SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS failure_rate
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
GROUP BY task_type
ORDER BY failed DESC;

Permission Queries

Q12: Permission Exposure

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT p.table_catalog, p.table_schema, p.table_name, p.user_name, p.privilege_type, t.owner_name
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLE_PRIVILEGES p
JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES t 
  ON p.table_catalog = t.table_catalog AND p.table_schema = t.table_schema AND p.table_name = t.table_name
WHERE p.user_name != t.owner_name
  AND (p.expired IS NULL OR p.expired > GETDATE())
ORDER BY p.table_name;

Q13: Admin Role Users

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT u.user_name, u.user_id, r.role_name, ur.user_role_catalog
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.USERS u
JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.USER_ROLES ur ON u.user_id = ur.user_id AND (u.user_catalog = ur.user_role_catalog OR (u.user_catalog IS NULL AND ur.user_role_catalog IS NULL))
-- NOTE: user_role_catalog and role_catalog may be NULL for tenant-level roles;
-- the OR condition handles both project-level and tenant-level role matching.
JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.ROLES r ON ur.role_name = r.role_name AND (ur.user_role_catalog = r.role_catalog OR (ur.user_role_catalog IS NULL AND r.role_catalog IS NULL))
WHERE LOWER(r.role_name) IN ('super_administrator', 'admin')
ORDER BY u.user_name;

Q14: User-Role Matrix

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT u.user_name, u.user_id, r.role_name, ur.user_role_catalog
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.USERS u
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.USER_ROLES ur ON u.user_id = ur.user_id AND (u.user_catalog = ur.user_role_catalog OR (u.user_catalog IS NULL AND ur.user_role_catalog IS NULL))
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.ROLES r ON ur.role_name = r.role_name AND (ur.user_role_catalog = r.role_catalog OR (ur.user_role_catalog IS NULL AND r.role_catalog IS NULL))
ORDER BY u.user_name, r.role_name;

Governance Queries

Q15: Table Comment Coverage

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT
    COUNT(CASE WHEN table_comment IS NOT NULL AND table_comment != '' THEN 1 END) AS commented,
    COUNT(*) AS total,
    COUNT(CASE WHEN table_comment IS NOT NULL AND table_comment != '' THEN 1 END) * 100.0 / COUNT(*) AS coverage_pct
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES;

Q16: Column Comment Coverage

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT
    COUNT(CASE WHEN column_comment IS NOT NULL AND column_comment != '' THEN 1 END) AS commented,
    COUNT(*) AS total,
    COUNT(CASE WHEN column_comment IS NOT NULL AND column_comment != '' THEN 1 END) * 100.0 / COUNT(*) AS coverage_pct
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.COLUMNS;

Tunnel Queries

Q17: Daily Tunnel Volume

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync OK for ds =1 day; MUST use async=true for ds >1 day (TUNNELS_HISTORY volume >> TASKS_HISTORY)

SELECT ds,
       COUNT(*) AS tunnel_count,
       SUM(data_size) / 1024 / 1024 / 1024 AS volume_gb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TUNNELS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
GROUP BY ds
ORDER BY ds DESC
LIMIT 7;

Tip: For upload/download breakdown, add operate_type to SELECT and GROUP BY: GROUP BY ds, operate_type

Q18: Top Tunnel Downloads

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT tunnel_catalog, object_name, operate_type,
       COUNT(*) AS tunnel_count,
       SUM(data_size) / 1024 / 1024 / 1024 AS volume_gb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TUNNELS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
  AND operate_type IN ('DOWNLOADLOG', 'DOWNLOADINSTANCELOG')
GROUP BY tunnel_catalog, object_name, operate_type
ORDER BY volume_gb DESC
LIMIT 20;

Object Count Queries

Q19: Object Inventory

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT 'Tables' AS object_type, COUNT(*) AS cnt FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES
UNION ALL
SELECT 'UDFs', COUNT(*) FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.UDFS
UNION ALL
SELECT 'Resources', COUNT(*) FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.RESOURCES
UNION ALL
SELECT 'Roles', COUNT(*) FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.ROLES
UNION ALL
SELECT 'Packages', COUNT(*) FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.INSTALLED_PACKAGES;

Performance Queries

Q20: Top 20 Slowest Tasks

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT task_catalog, task_name, task_type, owner_name, start_time,
       DATEDIFF(end_time, start_time, 'ss') AS run_seconds
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
  AND status = 'Terminated'
ORDER BY run_seconds DESC
LIMIT 20;

Hot Table Queries

Q21: Top 20 Most Accessed Tables (Last 7 Days)

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync may work for ds ≤3 days; MUST use async=true for ds >3 days (LATERAL VIEW EXPLODE causes row expansion)

SELECT table_name,
       COUNT(*) AS access_count,
       SUM(input_bytes) / 1024 / 1024 / 1024 AS total_read_gb
FROM (
    SELECT input_bytes,
           REGEXP_REPLACE(input_tables, '^\\[|\\]|"|\\s', '') AS table_list
    FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
    WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
      AND input_tables IS NOT NULL AND input_tables != ''
) t
LATERAL VIEW EXPLODE(SPLIT(table_list, ',')) tbl AS table_name
WHERE table_name != ''
GROUP BY table_name
ORDER BY access_count DESC
LIMIT 20;

Q22: Top Tables by Data Volume Read (Last 7 Days)

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync may work for ds ≤3 days; MUST use async=true for ds >3 days (LATERAL VIEW EXPLODE causes row expansion)

SELECT table_name,
       COUNT(*) AS access_count,
       SUM(input_bytes) / 1024 / 1024 / 1024 AS total_read_gb
FROM (
    SELECT input_bytes,
           REGEXP_REPLACE(input_tables, '^\\[|\\]|"|\\s', '') AS table_list
    FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
    WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
      AND input_tables IS NOT NULL AND input_tables != ''
) t
LATERAL VIEW EXPLODE(SPLIT(table_list, ',')) tbl AS table_name
WHERE table_name != ''
GROUP BY table_name
ORDER BY total_read_gb DESC
LIMIT 20;

Q23: User Resource Consumption by Table

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync may work for ds ≤3 days; MUST use async=true for ds >3 days (LATERAL VIEW EXPLODE causes row expansion)

SELECT owner_name, table_name,
       COUNT(*) AS access_count,
       SUM(input_bytes) / 1024 / 1024 / 1024 AS total_read_gb,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM (
    SELECT owner_name, input_bytes, cost_cpu,
           REGEXP_REPLACE(input_tables, '^\\[|\\]|"|\\s', '') AS table_list
    FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
    WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
      AND input_tables IS NOT NULL AND input_tables != ''
) t
LATERAL VIEW EXPLODE(SPLIT(table_list, ',')) tbl AS table_name
WHERE table_name != ''
GROUP BY owner_name, table_name
ORDER BY access_count DESC;

For column name pitfalls and corrections, see the Critical Column Name Reference in SKILL.md.

Official Verified Queries (Q24-Q29)

These queries use COALESCE for robustness across schema variations.

Q24: TOP Storage Tables with COALESCE

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

-- Cost warning: LEFT JOIN PARTITIONS may produce many rows for heavily partitioned tables.
-- Consider adding WHERE clause or using a subquery for partition_count instead.
SELECT
    t.table_catalog,
    t.table_schema,
    t.table_name,
    t.table_comment,
    COALESCE(t.data_length, 0) AS storage_bytes,
    CASE
        WHEN COALESCE(t.data_length, 0) >= 1099511627776
        THEN CONCAT(ROUND(COALESCE(t.data_length, 0) / 1099511627776.0, 2), ' TB')
        WHEN COALESCE(t.data_length, 0) >= 1073741824
        THEN CONCAT(ROUND(COALESCE(t.data_length, 0) / 1073741824.0, 2), ' GB')
        ELSE CONCAT(ROUND(COALESCE(t.data_length, 0) / 1048576.0, 2), ' MB')
    END AS storage_readable,
    t.owner_name,
    t.last_modified_time,
    COUNT(DISTINCT p.partition_name) AS partition_count
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES t
LEFT JOIN SYSTEM_CATALOG.INFORMATION_SCHEMA.PARTITIONS p
    ON t.table_catalog = p.table_catalog
    AND t.table_schema = p.table_schema
    AND t.table_name = p.table_name
GROUP BY t.table_catalog, t.table_schema, t.table_name,
         t.table_comment, t.data_length,
         t.owner_name, t.last_modified_time
ORDER BY storage_bytes DESC
LIMIT 20;

Q25: Partition Explosion Risk (Simplified)

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT
    table_catalog,
    table_schema,
    table_name,
    COUNT(DISTINCT partition_name) AS partition_count,
    MIN(create_time) AS earliest_partition,
    MAX(create_time) AS latest_partition
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.PARTITIONS
GROUP BY table_catalog, table_schema, table_name
HAVING COUNT(DISTINCT partition_name) > 500
ORDER BY partition_count DESC;

Q26: Daily Failure Trend

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync OK for ds ≤3 days; MUST use async=true for ds >3 days

SELECT
    ds AS stat_date,
    COUNT(*) AS task_count,
    SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) AS failed_count,
    ROUND(SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END)
          * 100.0 / COUNT(*), 2) AS failure_rate_pct
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
GROUP BY ds
ORDER BY stat_date;

Q27: CU-Hour by Project and Hour

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT task_catalog,
       DATEPART(end_time, 'hh') AS end_hour,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds = TO_CHAR(DATEADD(GETDATE(), -1, 'dd'), 'yyyymmdd')
GROUP BY task_catalog, DATEPART(end_time, 'hh')
ORDER BY cu_hours DESC;

Warning: Do NOT use TO_CHAR(end_time, 'hh') (12-hour format, AM/PM collapse) or TO_CHAR(end_time, 'hh24') (not supported in MaxCompute, outputs literal "24"). Use DATEPART(end_time, 'hh') or HOUR(end_time) which return 0-23.

Q28: User Resource Consumption

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL Execution: Sync OK for ds ≤3 days; MUST use async=true for ds >3 days

SELECT owner_name,
       COUNT(*) AS query_count,
       SUM(cost_cpu) / 100.0 / 3600 AS cu_hours,
       SUM(cost_mem) AS mem_mb_seconds,
       SUM(input_bytes) / 1024 / 1024 / 1024 AS input_gb,
       SUM(output_bytes) / 1024 / 1024 / 1024 AS output_gb
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TASKS_HISTORY
WHERE ds >= TO_CHAR(DATEADD(GETDATE(), -7, 'dd'), 'yyyymmdd')
GROUP BY owner_name
ORDER BY cu_hours DESC
LIMIT 100;

Q29: Project Configuration Audit

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT catalog_name, region, create_time,
       get_json_object(json_parse(settings), '$."odps.timemachine.retention.days"') AS backup_days,
       get_json_object(json_parse(settings), '$."odps.sql.metering.value.max"') AS sql_cost_limit
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.CATALOGS;

Smoke Test

Q30: Namespace Flag Verification

Purpose: Minimal query to verify odps.namespace.schema=true is set correctly. Run this first if IS queries return "Table not found".

MCP: execute_sql hints={"odps.namespace.schema":"true"} | odpscmd: SET odps.namespace.schema=true; + SQL

SELECT COUNT(*) AS table_count
FROM SYSTEM_CATALOG.INFORMATION_SCHEMA.TABLES;