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.
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.
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();
}
}
}
$$
;
SQLMorph is Now SQL Translation
Fantastic news! phData combined all of its tools and apps into a unified interface called the phData Toolkit. With the Toolkit, you’ll have a centralized place to utilize all of your favorite phData tools including SQLMorph, now called SQL Translation.
