SkillHub

odps-sql

v1.0.1

Use this skill when the user wants to query, analyze, or explore data in Alibaba Cloud ODPS (MaxCompute / 阿里云大数据计算服务). This skill executes SQL queries, lists tables, and inspects table schemas by running the odps_helper.py command-line script. Trigger this skill for requests like: querying ODPS data...

Sourced from ClawHub, Authored by GuilongZh

Installation

Please help me install the skill `odps-sql` from SkillHub official store. npx skills add GuilongZh/odps-sql

Setup (First-time only)

  1. Copy the credential template and fill in your values: bash cd mcp-odps/ cp config.example.env .env # Edit .env with your Alibaba Cloud credentials

  2. Activate your Python environment and install dependency: ```bash # conda users: conda activate # venv users: source .venv/bin/activate

pip install pyodps ```

Executing Commands

Activate your Python environment first, then run all commands from the project root with:

SCRIPT=mcp-odps/scripts/odps_helper.py

List tables

python $SCRIPT --list-tables

Filter by name:

python $SCRIPT --list-tables --pattern <keyword>

Get table schema

python $SCRIPT --describe <table_name>

Execute SQL query

python $SCRIPT --query "<SQL statement>" [--limit <n>]

Default limit is 100 rows.

Workflow for Data Tasks

Follow this pattern when the user asks about ODPS data:

  1. Discover — If the table name is unknown, run --list-tables --pattern <keyword> to find it.
  2. Inspect — Run --describe <table> to understand columns, types, and partition structure.
  3. Query — Construct the SQL and run --query. Always add a partition filter (WHERE dt = '...') for partitioned tables to avoid full scans.
  4. Present — Summarize the results clearly for the user.

ODPS SQL Key Differences from Standard SQL

Feature Standard SQL ODPS SQL
String concat a || b CONCAT(a, b)
Current time NOW() GETDATE()
Null coalesce IFNULL(x,y) NVL(x, y)
Regex match REGEXP RLIKE
Date literal '2024-01-01' TO_DATE('2024-01-01','yyyy-mm-dd')

Partition filter is required for partitioned tables (partition column is usually dt):

SELECT * FROM table_name WHERE dt = '2024-01-01' LIMIT 100

See mcp-odps/references/odps_sql_guide.md for a full SQL reference.

Error Handling

  • pyodps not found → Run install command in Setup step above
  • Missing credentials → Check that mcp-odps/.env exists and all four fields are filled in
  • Table not found → Use --list-tables --pattern to find the correct name
  • SQL syntax error → Check the ODPS SQL differences table above; avoid MySQL/PostgreSQL-specific syntax