Alibabacloud Migration Sdm SQL Trans

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.

Cross-Engine SQL Syntax Translation (migration-sdm-sql-trans)

Security Red Lines

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

2. When to Use / When NOT to Use

3. Support Matrix

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:

1. Explicitly inform: "Hive → Hologres language pair is not yet implemented (TODO); no conversion rules are currently available."

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

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

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

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

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:

1. Identify subquery levels (floor 0 = outermost, floor N = innermost)
2. Start transforming from the highest-floor leaf nodes; sibling nodes at the same floor can be processed in parallel
3. After a subquery is transformed, substitute the result back into the corresponding position in the parent query
4. The parent query then re-enters the Step 2–4 transform loop
5. Proceed layer by layer upward until the root query (floor 0) is completed

Iterative Repair Process (Step 3 ↔ 4 Loop)

Repair strategy when validation fails:

6. Scenario Implementation: Synapse → Hologres (Complete)

Core Rules

1. Identifiers

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.

2. Data Types

See reference.md → Data Type Mapping for the complete table. Key conversions:

• NVARCHAR/VARCHAR(MAX) → TEXT
• DATETIME/DATETIME2/SMALLDATETIME → TIMESTAMP
• DATETIMEOFFSET → TIMESTAMPTZ
• BIT → BOOLEAN
• TINYINT → SMALLINT
• MONEY/SMALLMONEY → NUMERIC(19,4) / NUMERIC(10,4)
• UNIQUEIDENTIFIER → UUID
• VARBINARY/IMAGE → BYTEA
• IDENTITY(seed,inc) → SERIAL / BIGSERIAL or GENERATED ALWAYS AS IDENTITY

3. Functions

See reference.md → Function Mapping for the full reference. Critical conversions:

4. Query Syntax

5. DDL Conversion

Table Creation

-- Synapse
CREATE TABLE dbo.sales (
    id INT IDENTITY(1,1),
    name NVARCHAR(100),
    amount MONEY,
    created DATETIME2
)
WITH (
    DISTRIBUTION = HASH(id),
    CLUSTERED COLUMNSTORE INDEX
);

-- Hologres
BEGIN;
CREATE TABLE public.sales (
    id BIGSERIAL,
    name TEXT,
    amount NUMERIC(19,4),
    created TIMESTAMP
);
CALL set_table_property('public.sales', 'distribution_key', 'id');
CALL set_table_property('public.sales', 'orientation', 'column');
COMMIT;

Distribution Strategy

Index / Storage

Partitioning

-- Synapse
CREATE TABLE dbo.orders (
    order_date DATE,
    amount DECIMAL(18,2)
)
WITH (
    PARTITION (order_date RANGE RIGHT FOR VALUES
        ('2024-01-01','2024-04-01','2024-07-01','2024-10-01'))
);

-- Hologres
BEGIN;
CREATE TABLE public.orders (
    order_date DATE,
    amount DECIMAL(18,2)
) PARTITION BY LIST (order_date);
-- Or use PARTITION BY RANGE if Hologres version supports it
COMMIT;

6. Stored Procedures → PL/pgSQL Functions

-- Synapse
CREATE PROCEDURE dbo.update_status @id INT, @status VARCHAR(20)
AS
BEGIN
    UPDATE dbo.orders SET status = @status WHERE order_id = @id;
    SELECT @@ROWCOUNT AS affected;
END;

-- Hologres
CREATE OR REPLACE FUNCTION public.update_status(p_id INT, p_status VARCHAR(20))
RETURNS TABLE(affected BIGINT) AS $$
DECLARE
    row_cnt BIGINT;
BEGIN
    UPDATE 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:

Distance validation fails → fall back to the original SQL and apply only the minimum changes needed to fix syntax differences.

9. Output Format Specification

Conversion results must follow this format:

-- ============================================================
-- Source: <source file name>
-- Engine: <source engine> → <target engine>
-- Status: PASS | PASS_WITH_TODO | FAIL
-- ============================================================

-- Converted SQL content
-- Add inline comments for semantic differences (using target-engine terminology only), e.g.:
-- NOTE: date-difference calculation → (b::date - a::date), returns integer days
SELECT ...;

-- TODO: verify - no corresponding rule for this construct; manual review required

Prohibited output content (negative constraints):

• Never output unvalidated SQL
• 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

10. Additional Resources

• Complete function and type mappings: reference.md
• Before/after conversion examples: examples.md
• DryRun errors and repair strategies: error-patterns.md

11. Extending to Other Engine Pairs (Four-Step Method)

This skill reserves extension slots for multiple engine pairs. To add a new language pair (e.g. Hive → MaxCompute):

1. Create references/<src>_to_<dst>.md using the same section structure as this document:
   • 1. Identifiers / 2. Data Types / 3. Functions / 4. Query Syntax / 5. DDL Conversion / 6. Procedural / 7. External Tables
2. Add a " → Reference" section in reference.md with complete data-type and function mapping tables
3. Add before/after examples in examples.md
4. 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:

When adding a new language pair, start with reserved-word conflicts + data types — these are the most error-prone areas in cross-engine migration.

12. Conversion Quality Evaluation and Reflection

For batch conversions, the following evaluation framework is recommended:

Health Metrics

Root-Cause Attribution (for failing / high-iteration cases)

Reflection Output

After each batch conversion, a reflection report is recommended, including:

• Health metrics
• Discovered failure patterns and their attribution
• List of rules updated
• Focus areas for the next evaluation round