This skill has been validated on the Qoder / QoderWork platform. It is a pure content-generation skill that does not invoke external tools. It can also be used on other Agent platforms, though trigger matching behavior may vary.
Never embed real customer database/table/column names in conversion examples or output (replace with tbl_demo, col_x)
Never embed real business data (phone numbers, IDs, account numbers, etc.) in example SQL; use placeholders where needed
Conversion output is generated content — for reference only; human review before executing in production is recommended
Never store any AK/SK, passwords, or plaintext connection strings in SKILL.md or references files
MCP Tool Inventory
This skill is pure content generation and does not depend on external MCP tools or CLI. All conversions are driven by built-in rule documents and mapping tables.
1. Overview
Provides SQL syntax conversion capabilities across big-data engines (DML / DDL / stored procedures / functions, etc.). This skill uses a "multi-directional language-pair + extensible conversion-rules" architecture, following a unified 5-step main process to complete each conversion, with implementations for additional engine pairs added incrementally.
Important: For language pairs marked 🟡 TODO in the table above, the corresponding references/<src>_to_<dst>.md files have not yet been created. When a user requests an unimplemented language pair, the Agent must explicitly inform the user that the pair is not yet implemented; fabricating conversion results based on speculation is strictly prohibited.
Rejection Policy for Unimplemented Language Pairs (Hard Constraint)
When a user's conversion request involves a 🟡 TODO language pair (especially Hive → Hologres), the Agent must perform the following actions:
Explicitly inform: "Hive → Hologres language pair is not yet implemented (TODO); no conversion rules are currently available."
Suggest alternatives (the reply must include specific, actionable recommendations):
Manually convert by referencing the Synapse → Hologres rule structure in this document (identifiers / data types / functions / query syntax / DDL)
Consult the data-type mapping table and function mapping table in reference.md as a general reference
Consult the official Alibaba Cloud Hologres documentation for SQL syntax details
Absolutely prohibited:
❌ Fabricating or extrapolating conversion results
❌ Outputting pseudo-conversions with Status: PASS or -- TODO: verify markers
❌ Pretending rules exist when the rewrite is actually a guess
Output delivery (hard requirement):
The rejection notice and alternative suggestions must be presented directly in the conversation reply, not merely declared as "saved to a file"
If a file needs to be written (e.g. outputs/refusal_notice.md), the file write must actually be executed; claiming a write was performed without doing so is prohibited
Hallucinated writes are prohibited: any file mentioned in logs or replies as "saved / written / generated" must actually exist in the file system with complete content
Recommended practice: reply directly in the conversation with the rejection notice and alternative suggestions — no extra file needed
Example rejection reply (output directly in conversation, not written to a file):
"SQL conversion rules for Hive → Hologres are not yet implemented (only Synapse → Hologres is currently supported). Recommendations: ① Reference the Synapse → Hologres conversion rule structure in this skill and manually rewrite against Hive syntax; ② Consult the official Hologres documentation for PostgreSQL-compatible functions and data types; ③ Re-run automatic conversion once the references/hive_to_hologres.md rule file is added."
Extension method: To add a new language pair, simply create references/<src>_to_<dst>.md using the same five-section template (identifiers / data types / functions / query syntax / DDL), and supplement the detailed mapping tables in reference.md. At runtime, the Agent loads the corresponding file based on the source/target pair.
4. Iron Rules (Violating Any Rule = Conversion Failure)
#
Prohibition
Explanation
0
No conversion for unimplemented language pairs
Language pairs marked 🟡 TODO in the support matrix (e.g. Hive → Hologres) must be rejected with a notice to the user. Suggest manual conversion using the implemented Synapse → Hologres rules or official documentation. Never output speculative conversion results
1
No speculative SQL rewrites
Every change must have a documented rule basis (this document / references / engines files) or an authoritative source. Rule not covered → keep original and annotate -- TODO: verify
2
No skipping validation
All DML/DQL must pass the "Validation Checklist" item by item. "Too complex" or "should be fine" are not valid reasons to skip
3
Validation fails → no output
When the validation checklist has unresolved items, never deliver results to the user; iterate until fixed
4
No structural loss
Conversion must not drop or merge subqueries, reduce SELECT column count, or remove WHERE/GROUP BY/HAVING clauses
5
TRUNCATE must be preserved
If the source SQL contains TRUNCATE, the conversion must preserve it
6
Each file runs the full pipeline independently
Parse → Load Rules → Transform → Validate → Output. Never reuse a template from a previous file or skip steps
7
No hallucinated writes
Never claim in logs or replies that a file has been "saved / written / generated" when no actual write was performed. Any file mentioned must exist in the file system with complete content
5. Main Process (Universal 5-Step Skeleton)
All language pairs share the same conversion process:
flowchart LR
A[Step 1<br>Parse] --> B[Step 2<br>Load Rules]
B --> C[Step 3<br>Transform]
C --> D[Step 4<br>Validate]
D --> E[Step 5<br>Output]
D -->|Fail| C
Step
Name
Description
1
Parse
Identify the scope of the input SQL: DML / DDL / stored procedure / hybrid; decompose complex SQL (see "Complex SQL Decomposition Strategy")
2
Load Rules
Based on the "source → target" pair, load the rule set from the corresponding references/<src>_to_<dst>.md (identifiers / data types / functions / query syntax / DDL / procedural code)
3
Transform
Apply rules in order: identifiers → data types → functions → query syntax → DDL → procedural. Only change what the rules cover; leave everything else untouched
4
Validate
Run the "Validation Checklist" item by item; if any item fails, return to Step 3 for iterative repair (max 3 rounds)
5
Output
Emit the converted SQL; add inline comments noting semantic differences and assumptions; mark uncertain mappings with -- TODO: verify
Complex SQL Decomposition Strategy
When the input SQL contains deeply nested subqueries (≥ 3 levels) or multiple CTEs combined with complex JOINs, apply a "transform from innermost layer outward" strategy:
Hologres folds unquoted identifiers to lowercase; if tables/columns are stored lowercase, do NOT add ""
Reserved words
If a column name is a PostgreSQL reserved word (e.g. level, name, user, order, table, type, comment), wrap in ""
Mixed-case needed
Only use "" when the Hologres object was created with "" and stores mixed case
Schema mapping
dbo.xxx → verify target schema; Hologres default schema is public, not dbo
Decision logic: Ask the user or infer from context whether their Hologres tables use lowercase (default) or mixed case. When unsure, default to no quotes and add a comment noting the assumption.
-- SynapseCREATE TABLE dbo.orders (
order_date DATE,
amount DECIMAL(18,2)
)
WITH (
PARTITION (order_date RANGERIGHTFORVALUES
('2024-01-01','2024-04-01','2024-07-01','2024-10-01'))
);
-- HologresBEGIN;
CREATE TABLE public.orders (
order_date DATE,
amount DECIMAL(18,2)
) PARTITIONBY LIST (order_date);
-- Or use PARTITION BY RANGE if Hologres version supports itCOMMIT;
6. Stored Procedures → PL/pgSQL Functions
-- SynapseCREATEPROCEDURE dbo.update_status @idINT, @statusVARCHAR(20)
ASBEGINUPDATE dbo.orders SET status =@statusWHERE order_id =@id;
SELECT @@ROWCOUNTAS affected;
END;
-- HologresCREATEOR REPLACE FUNCTION public.update_status(p_id INT, p_status VARCHAR(20))
RETURNSTABLE(affected BIGINT) AS $$
DECLARE
row_cnt BIGINT;
BEGINUPDATE public.orders SET status = p_status WHERE order_id = p_id;
GET DIAGNOSTICS row_cnt = ROW_COUNT;
RETURN QUERY SELECT row_cnt;
END;
$$ LANGUAGE plpgsql;
7. External Tables
Synapse external tables (PolyBase / CETAS) need rewriting to Hologres foreign tables or federated queries. These are highly environment-specific — flag them and ask the user about the target data source.
7. Validation Checklist
After conversion, verify:
No remaining [] brackets
No GETDATE, ISNULL, LEN, CHARINDEX, IIF, STUFF etc.
No DATEDIFF, DATEADD, DATEPART, DATENAME
No TOP n (should be LIMIT n)
No WITH (NOLOCK) or query hints
No #temp or @table variables outside PL/pgSQL
No T-SQL data types (NVARCHAR, DATETIME2, BIT, MONEY, etc.)
CONCAT() preserved (not replaced with ||) for NULL safety
Statements end with ;
Schema references match Hologres target schemas
Identifier quoting matches Hologres conventions
SELECT column count matches the original SQL (no columns dropped)
FROM/JOIN structure preserved (no JOINs merged or split)
WHERE/GROUP BY/HAVING/ORDER BY clauses fully preserved
NULL handling semantics are correct (COALESCE, not ||)
8. Conversion Quality Requirements
Rules Must Be Compatible with All Data Values
The target expression of every conversion rule must be a generic expression compatible with all possible data values in that scenario — NULL, empty string, zero, normal values. Never assume "this field will never be NULL" or "this array will never be empty".
No NULL-Introducing Conversions
If a source SQL expression does not return NULL for non-NULL inputs, the converted expression must not introduce additional NULL risk either. Typical anti-pattern: replacing ISNULL(a,b) with a || b (which yields NULL when a is NULL).
Distance Validation Principle
The conversion result should minimize the "structural distance" from the original SQL:
Check Item
Requirement
SELECT column count
Identical
FROM/JOIN count
Unchanged
Subquery depth
Unchanged (no merging or splitting)
WHERE/GROUP BY clauses
Fully preserved
Distance validation fails → fall back to the original SQL and apply only the minimum changes needed to fix syntax differences.
Never omit TODO markers (uncertain mappings must be flagged)
Never output empty files or files containing only comments
Never merge multiple source files into a single output
Never include verbatim source-engine syntax keywords in output files (including SQL comments and conversion logs). Specific rules:
❌ Do not reference source-engine syntax verbatim in comments, e.g. MERGE, WHEN MATCHED, WHEN NOT MATCHED, [dbo]., GETDATE(), ISNULL, TOP n, SET NOCOUNT ON, @variable_name, etc.
❌ Do not write comments like -- NOTE: MERGE...WHEN MATCHED/NOT MATCHED → INSERT...ON CONFLICT
❌ Do not reference source-engine originals in conversion logs as "explanations of transformations", e.g. @ReportMonth → p_report_month, SET NOCOUNT ON → removed
✅ Comments should only describe target-engine semantics, e.g. -- upsert: update quantity and last_updated on conflict
✅ When indicating transformation origin, use abstract descriptions rather than source syntax, e.g. -- original upsert logic → INSERT ON CONFLICT (do not write the MERGE keyword)
✅ In logs, describe parameter renaming as: parameter renamed to p_report_month (do not write the original @-prefixed variable name)
✅ In logs, describe statement removal as: removed row-count control statement (not needed on target engine) (do not write SET NOCOUNT ON)
ran_scripts/conversion_log.md and other log files are equally subject to this rule, with no exceptions — "documenting transformations" is not a justification for using source-engine keywords verbatim
Update the corresponding row in the "Support Matrix" table above from 🟡 TODO to ✅
Conversion Rule Generalization Recommendations
The following rules are broadly reusable across most "T-SQL / traditional data-warehouse → PostgreSQL-family / MaxCompute" scenarios and can serve as starting points when adding new language pairs:
