Welcome back to our monthly update on SQLMorph, our free-to-use transpiler built to convert SQL statements from source to target. Whether you’re new to the series or a long-time subscriber, we’ve got you covered! We aim to give you updates not only on the latest and greatest functionality but also to clue you in on features you might not have known about before.
It’s important to understand the complexity that SQLMorph aims to solve. You’re likely familiar with some variant of SQL syntax, but did you know that SQL Server, Oracle, MySQL, PostgreSQL, Snowflake, and many more databases have their own SQL dialects? This creates a lot of complexity and headache when migrating between them. Things as simple as trying to get the current date/time can be dramatically different, and large enterprises often have hundreds of thousands of lines of SQL! Trying to convert that manually would take a long time, and it’s frequently not as simple as a find and replace.
This is why we’ve created SQLMorph: to automate the complexity of SQL migration and allow developers to focus on other tasks.
So What’s New?
Well, a lot of things. There are many different combinations of SQL dialects that SQLMorph can support, there’s a user interface, and there’s also a command line interface. Historically we’ve bundled all these changes together when publishing updates to SQLMorph, but now we’ve recently separated our transpilation module from the SQLMorph API. This not only allows us to use these transpilations in other projects but also enables us to release functionality quicker.
Let’s dig into some of these updates to each piece of the platform.
SQL Server to Snowflake
One of the main areas of focus lately for our engineers is adding support for Microsoft SQL Server. We continue to identify customers who are migrating from this platform to tools like Snowflake and need the capability to transpile their existing SQL. In the last month, we’ve implemented the following:
- DML/DDL
- Update statements
- Merge statements
- T-SQL
- Nested if/else blocks with support for in_expr and in_stmt
- Convert function
- Patindex function
- Escaping single quotes (triple single quote)
- Multi-line strings with back-ticks
- Format date/time
Example 1:
Source: CREATE PROCEDURE proc1
As
If @num <> 0 BEGIN
If @num IN (0,6) BEGIN
Set @value1 = 10 END
ELSE IF @num = 1 BEGIN
Set @value2 = 5 END
END
Target: create or replace procedure proc1 ()
returns string
language javascript
as
$$
if (num != 0) {
if (num == 0 || num == 6) {
var value1 = 10;
} else if (num == 1) {
var value2 = 5;
}
}
$$
;
Example 2:
Source: CREATE PROCEDURE proc1
@OrgCd NVARCHAR(50)
AS
set @SQLSelect = CONVERT(datetime, @OrgCd)
Target: create or replace procedure proc1 ("OrgCd" NVARCHAR(50))
returns string
language javascript
as
$$
var SQLSelect = format_date(OrgCd);
// 'appendLeadingZeroes' and 'format_date' functions are added by SQLMorph to format the date in Snowflake format. These functions use UTC time.
function appendLeadingZeroes(n) {
if (n <= 9) {
return "0" + n;
}
return n;
}
function format_date(dateVal) {
if (!dateVal) return null;
var dt = new Date(dateVal);
return "'" + dt.getUTCFullYear() + "-" + appendLeadingZeroes(dt.getUTCMonth() + 1) + "-" + appendLeadingZeroes(dt.getUTCDate()) + " " +
appendLeadingZeroes(dt.getUTCHours()) + ":" + appendLeadingZeroes(dt.getUTCMinutes()) + ":" + appendLeadingZeroes(dt.getUTCSeconds()) +
"." + appendLeadingZeroes(dt.getUTCMilliseconds()) + "'";
}
$$
;
HiveFamily (Hive, Impala, Databricks, and SparkSQL) to Snowflake
Another common migration path that we see customers taking is moving from the Hadoop/Cloudera ecosystem to Snowflake. This covers a wide array of products, but they all follow the same SQL dialects. Within these data platforms, it’s very common to have semi-structured data (may contain things like arrays/objects) within your dataset that you want to store as one chunk of data. We’ve now implemented this for the Hive family transpilation to Snowflake for both data definitions (DDL) and data manipulations (DML).
Example 1:
Source: create table t1(
c1 Array<String>,
c2 Array<float>,
c3 map <String, float>,
c4 struct<a :float, b :float>,
c5 uniontype <int, double, Array<String>>)
Target: CREATE TABLE t1 (c1 ARRAY, c2 ARRAY, c3 VARIANT, c4 VARIANT, c5 VARIANT);
Example 2:
Source: SELECT reportee [0], card ["Gobelin"] FROM t1;
Target: SELECT GET(reportee, 0), GET(card, 'Gobelin') FROM t1;
Example 3:
Source: create table t1 (a int) partitioned by (b string);
Target: -- ERROR: HiveFamilyStmtVisitor: line 1, character 24: Snowflake does not support `partition_by` clause. SQLMorph added Hive partition columns to the list of columns. You will need to either delete this column or add the column to the underlying data file.
CREATE TABLE t1 (a INT, b STRING);
User Interface Changes
While we tend to spend the majority of our time these days adding support for a variety of statements, we also actively update our user interface. This custom-built interface serves as the primary entry point for a lot of our customers to work on their transpilations. You can also find our change log within the application if you want to see updates more frequently from us!
In the last few months, we’ve made a few minor tweaks to the UI. This includes things like:
- Theming the UI to match Traverse
- Fixing horizontal overflow within the change log messages
- Bug fixes for the source/target dropdown
- Removing duplicate scroll bar on the translation page
These changes aim to improve the quality of life and consistency across our applications.
If you ever want to view the phData applications that you have access to, check out the menu in the top right corner of the navigation bar within SQLMorph!
Tidbits
Interested in learning more about existing functionality within SQLMorph that you might not have been aware of? Check this out:
- SQLMorph contains a demo page where you can view example transpilations between various source/target combinations. This is great for exploring SQLMorph functionality.
- Speaking of pages, we also have a supported statements page that allows you to identify what functionality is supported for transpilation. We call out whether the functionality is GA, Beta, or Alpha support based on the amount of coverage we have of that statement/functionality.
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.
