SQLMorph June 2021 Update

Our development team has been hard at work over the last couple of weeks working on our free-to-use transpiler, built to convert SQL statements from source to target.  While SQLMorph already contains over 19,000 lines of ANTLR SQL grammar in nine different dialects, we have been continuously evaluating new opportunities to expand the tool.

Over the course of the last couple months, we have been working on adding support for Microsoft SQL Server (MSSQL) and are coming close to beta functionality.  When working with customers to migrate from SQL Server to Snowflake, it’s common for an enterprise to have thousands of SQL files, queries, and stored procedures that build reports, administer their instance, and perform create/read/update/delete (CRUD) operations for applications.

In order for customers to be successful in the migration process, it’s critical that more time is spent on design and development in the new technology, rather than comparing existing SQL syntax and functionality to the new system.  This mundane task should be automated as much as possible; this is where SQLMorph comes into play.

Microsoft SQL Server

While we’ve added a lot of functionality to our existing transpilation options, our primary focus has been on MSSQL.  We’ll cover the other transpilation options later.

While MSSQL support is not feature complete yet, we’d like to take some time to call out the functionality that we’ve been working on.  One of the most common ways of executing SQL within MSSQL environments is with stored procedures.  These stored procedures are often complex, lengthy, and even nested.  This creates a lot of complexity when trying to transpile queries.  Stored procedures often have temporary tables, lookup tables, and/or conditional logic that generates dynamic SQL.

Due to the amount of usage the stored procedures have within MSSQL, this has been our primary focus.  We have added support for options such as:

  • Multiple commands in a single stored procedure
  • Error messages for unsupported clauses
  • Conditional logic (if statements)
  • Declaring variables
  • Statements to execute before a stored procedure
  • Multiple conditions in where clauses
  • Cursors

While this unlocks a lot of functionality in a transpilation process, there is still a lot of work to be done.  Look forward to these updates as we continue to iterate.

Oracle

One of our more feature complete transpilation options is the Oracle SQL dialect.  Oracle is a system that our customers frequently use and therefore is a common source for existing SQL.

Over the last month, we have fixed a couple minor issues with existing transpilation functionality.  This includes:

  • Fixed OR PLSQL parse issue on type definition
  • Identifiers should not allow double quotes character
  • Fixed rewrite object of Select statement order by clause
  • Fixed parse issue of PLSQL starting with comments

Some of the functionality that we’ve added however is net-new!  This expands on the existing functionality that we’ve seen from our customers to allow for more migration paths.

  • Implemented Create Materialized View statement.
  • Improved procedure grammar to support schema.procedure and Editionable keyword
  • Added support for parameters in Open Cursor statement.

If your organization is working through an Oracle migration project to Snowflake, give the tool a shot!  If you have any feedback on transpilations or need a feature supported, use the feedback button in the top right corner of SQLMorph.

SQLMorph Transpilation Examples

This month we thought we’d spend some time showing off some of the more interesting and complicated transpilations that SQLMorph is capable of.  These don’t seek to show off every piece of functionality available.

Microsoft SQL Server to Snowflake

In this example, we have a few different things that need to be handled within the transpilation.  We have case statements, string literals, and functions that need to be mapped.  Some of the syntax and inputs for functions only exist in either the source or target, so we provide warnings for dropped parameters.

Source:
				
					select
  convert(varchar(7), jq.createdate, 126) createdate,
  jm.countryid,
  co.name countryname,
  sm.categoryid,
  cat.categoryname,
  jm.businessmodel,
  platform = case
    when ClientOs = 'iOS' then 'IOS'
    when ClientOs = 'Android' then 'Android'
    when DeviceCategory = 'Desktop' then 'Desktop'
    when (
      DeviceCategory = 'MobileWeb'
      and ClientOs ! = 'iOS'
    ) then 'MobileWeb'
    else 'Other'
  end,
  jq.jobquotesid,
  jm.jobid,
  jq.userid,
  Rev = case
    when jm.CountryId = 1 THEN ISNULL(lp.fiyati, 0) * (1 / 1.18)
    else ISNULL(
      (
        lp.Fiyati * ISNULL(
          (
            cer.[ Exchange Rate (Value In USD) ] / cerTRYUSD.[ Exchange Rate (Value In USD) ]
          ),
          1
        )
      ),
      0
    )
  END,
  ComRev = case
    when jm.CountryId = 1 THEN ISNULL(jd.commissionamount, 0) * (1 / 1.18)
    else ISNULL(
      (
        jd.commissionamount * ISNULL(
          (
            cer.[ Exchange Rate (Value In USD) ] / cerTRYUSD.[ Exchange Rate (Value In USD) ]
          ),
          1
        )
      ),
      0
    )
  END,
  jqs.ClientOs,
  jqs.DeviceCategory
from
  tbljobquotes as jq
  inner join tbljobmaster as jm on jq.jobid = jm.jobid
  inner join tbljobrevenues as jr on jq.jobid = jr.jobid
  left join TBLJOBLEADSPURCHASED lp on jq.jobquotesid = lp.jobquoteid
  left join tbljobdeal jd on jq.jobid = jd.jobid
  left join TBLSERVICEMASTER sm on sm.ServiceId = jm.ServiceId
  inner join tblCategories cat ON cat.CategoryId = sm.CategoryId
  inner join tblJobQuoteSource as jqs on jq.JobQuotesId = jqs.JobQuotesId
  inner join tblCountries co ON co.Id = jm.CountryId
  left join tblCurrencyExchangeRatesByMonth cer ON cer.Y = YEAR(lp.CreateDate)
  AND cer.M = MONTH(lp.CreateDate)
  AND cer.Currency = jr.CurrencyCode
  left join tblCurrencyExchangeRatesByMonth cerTRYUSD ON cerTRYUSD.Y = YEAR(lp.CreateDate)
  AND cerTRYUSD.M = MONTH(lp.CreateDate)
  AND cerTRYUSD.Currency = N 'TRY'
where
  convert(date, jq.createdate) >= '2020-01-01';
				
			

Target:

				
					SELECT
  -- WARNING: MSSqlExprVisitor: Snowflake does not support `style` argument for `convert` function
  CAST(jq.createdate AS VARCHAR(7)) createdate,
  jm.countryid,
  co.name countryname,
  sm.categoryid,
  cat.categoryname,
  jm.businessmodel,
  CASE
    WHEN ClientOs = 'iOS' THEN 'IOS'
    WHEN ClientOs = 'Android' THEN 'Android'
    WHEN DeviceCategory = 'Desktop' THEN 'Desktop'
    WHEN (
      DeviceCategory = 'MobileWeb'
      AND ClientOs = 'iOS'
    ) THEN 'MobileWeb'
    ELSE 'Other'
  END platform,
  jq.jobquotesid,
  jm.jobid,
  jq.userid,
  CASE
    WHEN jm.CountryId = 1 THEN IFNULL(lp.fiyati, 0) * (1 / 1.18)
    ELSE IFNULL(
      (
        lp.Fiyati * IFNULL(
          (
            cer." Exchange Rate (Value In USD) " / cerTRYUSD." Exchange Rate (Value In USD) "
          ),
          1
        )
      ),
      0
    )
  END Rev,
  CASE
    WHEN jm.CountryId = 1 THEN IFNULL(jd.commissionamount, 0) * (1 / 1.18)
    ELSE IFNULL(
      (
        jd.commissionamount * IFNULL(
          (
            cer." Exchange Rate (Value In USD) " / cerTRYUSD." Exchange Rate (Value In USD) "
          ),
          1
        )
      ),
      0
    )
  END ComRev,
  jqs.ClientOs,
  jqs.DeviceCategory
FROM
  tbljobquotes AS jq
  INNER JOIN tbljobmaster AS jm ON jq.jobid = jm.jobid
  INNER JOIN tbljobrevenues AS jr ON jq.jobid = jr.jobid
  LEFT JOIN TBLJOBLEADSPURCHASED lp ON jq.jobquotesid = lp.jobquoteid
  LEFT JOIN tbljobdeal jd ON jq.jobid = jd.jobid
  LEFT JOIN TBLSERVICEMASTER sm ON sm.ServiceId = jm.ServiceId
  INNER JOIN tblCategories cat ON cat.CategoryId = sm.CategoryId
  INNER JOIN tblJobQuoteSource AS jqs ON jq.JobQuotesId = jqs.JobQuotesId
  INNER JOIN tblCountries co ON co.Id = jm.CountryId
  LEFT JOIN tblCurrencyExchangeRatesByMonth cer ON cer.Y = YEAR(lp.CreateDate)
  AND cer.M = MONTH(lp.CreateDate)
  AND cer.Currency = jr.CurrencyCode
  LEFT JOIN tblCurrencyExchangeRatesByMonth cerTRYUSD ON cerTRYUSD.Y = YEAR(lp.CreateDate)
  AND cerTRYUSD.M = MONTH(lp.CreateDate)
  AND cerTRYUSD.Currency = 'TRY'
WHERE
  CAST(jq.createdate AS DATE) >= '2020-01-01';
				
			

Oracle To Snowflake

In this example, we have a very complex stored procedure transpiled from Oracle to Snowflake SQL dialects.  It includes rewriting the data definition language (DDL) for the create procedure statement, loops in SQL to javascript, cursors, local variables, comments, and much more.

Source:
				
					PROCEDURE for_loop_cursor_update_insert_example (GROUP_SEQ NUMBER, TRANS_SEQ NUMBER, ID NUMBER,TRANS_ID NUMBER, TRANS_SUM NUMBER, UNIT VARCHAR(6), DEBET NUMBER, CREDIT NUMBER) IS
BEGIN
PP := 1;
FOR v_rec in (SELECT unit from xxx)
LOOP
   FOR d_rec in (SELECT ID, TRANS_SUM, UNIT from yyy where unit = v_rec.unit )
   LOOP

     UPDATE YYY_SEQUENCE 
       SET SEQ_COUNT = SEQ_COUNT + 1
       WHERE SEQ_NAME = 'GROUP_SEQ'
       COMMIT;

      INSERT INTO AAA (ID, SEQ) VALUES (ID, GROUP_SEQ);
      PP := 1;
      WHILE PP <= 4
      LOOP
         PP := PP +1;

     UPDATE YYY_SEQUENCE 
       SET SEQ_COUNT = SEQ_COUNT + 1
       WHERE SEQ_NAME = 'GROUP_SEQ'
       COMMIT;

         IF (PP < 3) THEN
             DEBET := TRANS_SUM;
             CREDIT := 0;
         ELSE             
             DEBET := 0;
             CREDIT := TRANS_SUM;
         END IF;

         INSERT INTO BBB(ID, SEQ, UNIT, DEBET, CREDIT) VALUES (ID, TRANS_SEQ, UNIT, DEBET, CREDIT);
         COMMIT;
      END LOOP;
   END LOOP;
END LOOP;
-- from https://stackoverflow.com/a/61185793/14042808
END;
				
			

Target:

				
					-- INFO: Comments from translated statement. Future work will list them closer to their original location.
-- Comment(s) from line 38, character 0
-- from https://stackoverflow.com/a/61185793/14042808
create or replace procedure for_loop_cursor_update_insert_example ("GROUP_SEQ" FLOAT, "TRANS_SEQ" FLOAT, "ID" FLOAT, "TRANS_ID" FLOAT, "TRANS_SUM" FLOAT, "UNIT" VARCHAR(6), "DEBET" FLOAT, "CREDIT" FLOAT) 
returns string 
language javascript
as
$$
var PP = 1;

var cursor1 = snowflake.createStatement({
    sqlText: "SELECT unit FROM xxx;"
});
var cursor1Result = cursor1.execute();
while (cursor1Result.next()) {
    var cursor1 = snowflake.createStatement({
        sqlText: "SELECT ID, TRANS_SUM, UNIT FROM yyy WHERE unit = v_rec.unit;"
    });
    var cursor1Result = cursor1.execute();
    while (cursor1Result.next()) {
        var loopResult = snowflake.execute({
            sqlText: "UPDATE YYY_SEQUENCE SET SEQ_COUNT = SEQ_COUNT + 1 WHERE SEQ_NAME = 'GROUP_SEQ';"
        });
        COMMIT;
        var loopResult = snowflake.execute({
            sqlText: "INSERT INTO AAA (ID, SEQ) VALUES (ID, GROUP_SEQ);"
        });
        var PP = 1;
        while (PP <= 4) {
            var PP = PP + 1;
            var sql_stmt = snowflake.createStatement({
                sqlText: "UPDATE YYY_SEQUENCE SET SEQ_COUNT = SEQ_COUNT + 1 WHERE SEQ_NAME = 'GROUP_SEQ';"
            });
            var sql_result = sql_stmt.execute();
            var sql_stmt = snowflake.createStatement({
                sqlText: "COMMIT;"
            });
            var sql_result = sql_stmt.execute();
            if ((PP < 3)) {
                var DEBET = TRANS_SUM;
                var CREDIT = 0;
            } else {
                var DEBET = 0;
                var CREDIT = TRANS_SUM;
            }
            var sql_stmt = snowflake.createStatement({
                sqlText: "INSERT INTO BBB (ID, SEQ, UNIT, DEBET, CREDIT) VALUES (ID, TRANS_SEQ, UNIT, DEBET, CREDIT);"
            });
            var sql_result = sql_stmt.execute();
            var sql_stmt = snowflake.createStatement({
                sqlText: "COMMIT;"
            });
            var sql_result = sql_stmt.execute();
        }
    }
}
$$
;
				
			

Thanks for reading! Be sure to stay tuned for more exciting SQLMorph monthly updates. 

Share on linkedin
Share on twitter
Share on facebook
Share on email

Table of Contents

More to explore

Dependable data products, delivered faster.

Snowflake Onboarding Accelerator

Infrastructure-as-code Accelerator

Snowflake Account Visualization and Auditing

Operational Monitoring and Observability Accelerator

SaaS SQL Translator