Experience intelligent SQL conversion with Migration on Pilot

Background

Traditional heterogeneous database migrations to the cloud often present the following challenges:

• Users are often unfamiliar with the target database's SQL syntax rules.

• SQL syntax differences require refactoring statements to ensure they function correctly on the target database.

• Traditional migration tools lack universal, 100% effective SQL conversion rules, preventing them from fully resolving these issues.

Solution overview

Offered in the ApsaraDB Solution Experience Hall, Migration on Pilot helps you overcome heterogeneous database migration challenges by combining a large language model (LLM) with rule-based SQL conversion.

• Rule-based intelligent conversion prompts: This feature builds on years of successful migration best practices from ADAM (Advanced Database & Application Migration). Based on SQL conversion rules, it parses source SQL into an Abstract Syntax Tree (AST) and then reconstructs the AST to generate syntax compatible with the target database.

• LLM-based interactive refinement: This feature uses the Qianwen large language model to understand your refactoring intent. By integrating an emotion and intent analysis model, it enables interactive SQL refinement through natural language.

Impact

This trial does not involve deployment in a production environment and will not affect your business.

Fees

Resources used in the trial are separate from your account, so you will not be charged.

Procedure

1. Log on to the ApsaraDB console.

2. Go to the MOP page. You can do this in one of two ways:

   • On the overview tab, click the Migration on Pilot card.

   • On the solutions tab, expand the DB+AI Experience collapsible panel, and then click the Free Trial button next to Migration on Pilot.

3. Enter the source SQL to convert. Currently, only Oracle SQL syntax is supported.

   You can also select an example from the Sample SQL drop-down list to test the conversion.

   The drop-down list next to Sample SQL provides the following types: table definition, function definition, sequence definition, stored procedure definition, synonym, and SQL. After you select an option, the editor automatically populates with the corresponding Oracle sample statement.

4. Select the target database type. Supported types include PostgreSQL databases such as ApsaraDB RDS for PostgreSQL, AnalyticDB for PostgreSQL, and PolarDB for PostgreSQL (Oracle-Compatible).

5. Click Convert Source SQL.

   The pane below details the specific modifications and recommendations, including syntax differences, incompatibilities between the source and target databases, and the applied changes.

   For example, you can select the following function definition SQL:

   CREATE OR REPLACE FUNCTION "SHOPTEST"."GET_TAB_PTF"(
                   p_rows IN NUMBER) RETURN t_tf_tab PIPELINED
               AS
               BEGIN
                   FOR i IN 1 .. p_rows
                   LOOP
                       PIPE ROW(t_tf_row(i, 'Description for ' || i));
                   END LOOP;
                   RETURN;
               END;

   When the target database is PolarDB for PostgreSQL (Oracle-Compatible), the converted SQL is as follows:

   CREATE OR REPLACE FUNCTION "SHOPTEST"."GET_TAB_PTF"(p_rows IN NUMBER)
   RETURN TABLE (t_tf_row(i INTEGER, Description VARCHAR2(200)))
   AS
   BEGIN
       FOR i IN 1 .. p_rows
       LOOP
           RETURN NEXT t_tf_row(i, 'Description for ' || i);
       END LOOP;
   END;
   /

   The details pane below shows that the primary modification addresses the PIPELINED keyword, which is not supported by PolarDB for PostgreSQL (Oracle-Compatible).

6. You can also enter natural language in the input box to refine your SQL refactoring requirements.

   For example, using the target SQL from the preceding step, you can enter Please convert this to a stored procedure and observe the refined result:

   CREATE OR REPLACE PROCEDURE SHOPTEST.GET_TAB_PTF(p_rows IN NUMBER)
   AS
   TYPE tab_t IS TABLE OF t_tf_row INDEX BY PLS_INTEGER;
   v_tab tab_t;
   BEGIN
       FOR i IN 1 .. p_rows
       LOOP
           v_tab(i) := t_tf_row(i, 'Description for ' || i);
       END LOOP;
       FOR i IN tab_t.FIRST..tab_t.LAST
       LOOP
           DBMS_OUTPUT.PUT_LINE(v_tab(i).i||'-'||v_tab(i).description);
       END LOOP;
   END;
   /

Related content

Target database compatibility assessment

Heterogeneous database migration

Detailed SQL conversion rules