Whether you’re new to phData, SQLMorph, or this blog series, we’ve got a lot in store for you today! Welcome once again to our monthly update on SQLMorph, our free-to-use transpiler built to convert SQL statements from source to target.
You may be new and wondering, “What is this series all about? Why should I care?” Well, kind reader, do we have plenty of information for you! Please refer back to our previous blog posts in the series as well as our SQLMorph overview for more granular information!
If you’ve been keeping up to date with this series, then you know we have a lot of content in store for you. We continue to iterate on our design, implementation, and take in feedback from consumers like you. As always, please feel free to reach out or use the feedback button within our applications.
To The Good Stuff
While we regularly make updates to all of our available transpilations, this last month has been heavily focused on Microsoft SQL Server to Snowflake. We previously talked about this new transpilation option in our June update and the beta functionality we’ve been working on thus far. We are quickly approaching a full beta version of this transpilation (more on this later).
We’ve also introduced additional functionality for Teradata.
Let’s get into the details.
SQL Server to Snowflake
While there have been many changes to this transpilation over the last month, we’ll focus on a couple specific features here.
Merging Data
One of the most frequent operations that happens within a database or data warehouse is inserting and updating data. Different systems implement this differently, however, the easiest way to do this within SQL Server and Snowflake is to use a merge statement. This allows you to match specific criteria and specify what you want to happen if the record is new or already exists.
Let’s look at an example.
Source:
MERGE t1 USING (SELECT @c1, @c2) as t2 ON (t1.c1 = t2.c1)
WHEN MATCHED THEN UPDATE SET col1 = t2.c1
WHEN NOT MATCHED THEN INSERT (t1.c1)
Target:
MERGE INTO t1 USING (SELECT c1, c2) ON (t1.c1 = t2.c1)
WHEN MATCHED THEN UPDATE SET col1 = t2.c1;
While this is a simple example that happens to be nearly identical in syntax, these merge statements can get very complex and often have multiple conditions such as the following:
Source:
MERGE Production.ProductInventory AS pi USING (
SELECT
ProductID,
SUM(OrderQty)
FROM
Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate BETWEEN '20030701'
AND '20030731'
GROUP BY
ProductID
) AS src (ProductID, OrderQty) ON pi.ProductID = src.ProductID
WHEN MATCHED
AND pi.Quantity - src.OrderQty >= 0 THEN
UPDATE
SET
pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED
AND pi.Quantity - src.OrderQty <= 0 THEN DELETE OUTPUT $ action,
Inserted.ProductID,
Inserted.LocationID,
Inserted.Quantity;
Target:
-- ERROR: MSSqlStmtVisitor: line 1, character 0: Snowflake does not support `output_clause` in merge statement.
MERGE INTO Production.ProductInventory pi USING (
SELECT
ProductID,
SUM(OrderQty)
FROM
Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate BETWEEN '20030701'
AND '20030731'
GROUP BY
ProductID
) ON pi.ProductID = src.ProductID
WHEN MATCHED
AND pi.Quantity - src.OrderQty >= 0 THEN
UPDATE
SET
pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED
AND pi.Quantity - src.OrderQty <= 0 THEN DELETE;
This is where the power of SQLMorph really shines. Instead of a developer needing to know the specific merge syntax of both providers, and manually making the edits, you’re able to leverage our transpiler!
SQLMorph will give you errors or warnings if the SQL you’re transpiling doesn’t translate 1 to 1. For example, in the above query, Snowflake doesn’t support having an output clause in the merge statement. This saves you critical development time and warns you of gaps in your existing process.
Try Catch
A common operation within software engineering is a try catch block. This is used to encapsulate a set of code (the try) that might have exceptions thrown that we want control over (the catch).
For example, if we wanted to withdraw money from someone’s bank account, we could put a try around the code to withdraw money. If the user doesn’t have enough money, an exception is thrown by the code within the try block. In the catch, we may want to log that the user tried to withdraw more money than they have, alert an agent, and then notify the customer that the transaction failed gracefully. We wouldn’t want to show the user a 500 internal server error.
This same usage applies within SQL dialects as well. We may want to handle things like selecting from a table or schema that may not exist. Note that Snowflake only supports try/catch statements within stored procedures at the moment.
Source:
BEGIN TRY
SELECT *
FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
END CATCH
Target:
-- ERROR: StmtRewriter: Snowflake does not support `try-catch` block. So SQLMorph has included only the statements of try - block here.
SELECT * FROM NonexistentTable;
Source:
CREATE PROCEDURE proc1 AS
BEGIN TRY
SELECT *
FROM NonexistentTable;
END TRY
BEGIN CATCH
SELECT *
FROM TableCopy;
END CATCH
Target:
create or replace procedure proc1 ()
returns string
language javascript
as
$$
try {
var sql_stmt = snowflake.createStatement({sqlText: "SELECT * FROM NonexistentTable;"});
var sql_result = sql_stmt.execute();
} catch (err) {
var sql_stmt = snowflake.createStatement({sqlText: "SELECT * FROM TableCopy;"});
var sql_result = sql_stmt.execute();
}
$$;
Oracle to Snowflake
We’ve also added some additional functionality to our Oracle to Snowflake transpilation. One of the biggest pain points for developers, whether building applications or migrating data, is dates and times. It’s easy to say an event happened at 12 PM, but 12 PM relative to what? What time zone are you in? Does the country that data came from do daylight savings time?
It’s best practice to store this information in UTC along with the timezone offset the data was entered from.
Why are we talking about timezones? Well, we’ve now added support for the timestamp with time zone data type! This looks like the following:
Source:
CREATE TABLE t1 (c1 TIMESTAMP(7) WITH TIME ZONE);
Target:
CREATE TABLE t1 (c1 TIMESTAMP_TZ(7));
We’ve also fixed some parsing issues around special characters.
Source:
CREATE TABLE t1 (col1 NUMBER(*, 0))
Target:
CREATE TABLE t1 (col1 NUMBER(38, 0));
Source:
select * from ${db:empid}._DATA;
Target:
SET var0 = $empid || '.DATA';
SELECT * FROM identifier($var0);
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.
