December 27, 2021

SQLMorph December 2021 Update

By Nick Goble

Hello and welcome back to our monthly installment of our SQLMorph update blog series! You might know by now, but for those of you that are new to the series, SQLMorph is part of a set of tools that phData provides for customers.  

SQLMorph is a free-to-use transpiler built to convert SQL statements from source to target. This empowers our customers to migrate faster between SQL platforms more quickly and efficiently.

This month the team has added a wide variety of new features and functionality to SQLMorph. We’ll go into these in more detail in just a moment but for now, we want to briefly touch on the impact that SQLMorph has on customer migrations.

Why Is SQLMorph Important?

Migrating between database systems and providers is a complex task, especially if your company is a larger enterprise with lots of data pipelines and applications. You will need to do a variety of tasks such as:

  • Migrating Data Definition Language (DDL)
    • These define how your data is structured and modify resources
  • Migrating Data Markup Language (DML) 
    • These define how your data is created, read, updated, and deleted
  • Migrating business logic in both DDL/DML
    • These can include (sets of) stored procedures and orchestration of tasks

Where many companies should be spending the majority of their time during a migration is determining things like rearchitecting, data governance, and ensuring the list of entities targeted for migration is complete.  However, it’s common for businesses to have so much SQL that needs to be translated that it takes the focus away from these critical tasks. If a company can focus on improving their system at the same time as they’re migrating, it allows for setting themselves up for success.

This is where SQLMorph accelerates your migration process. 

SQLMorph can bulk transpile your DDL/DML statements either via command line interface (CLI) or via our user interface (UI).

So What’s New?

Now that we’ve covered what SQLMorph can do for your migration, let’s jump into the new functionality. We’ll go through some additions for a few of the source-target pairs available within SQLMorph.  If you want to keep up to date with all the updates, check out our change log.

Microsoft SQL Server to Snowflake

This month we’ve made a large number of updates to this transpilation pair. As we continue to move toward a full beta functionality for this pair, we’ve added new features and fixed some issues we’ve found along the way.

We’ve fixed parsing issues with:

  • Values expression
  • Using semi, label, days, time, and level as identifiers
  • Try Cast function
  • Create table, view, and use database statements
  • Parenthesis in the join of a select statement

New functionality includes:

  • Double type support
  • Error message for update statistics statement

As we continue to iterate on this compilation pair, it’s very helpful if any bugs found are reported.  This can be done via the feedback button on the top right corner of the SQLMorph UI.

Hive Family to Snowflake

The other major area of focus for the last month has been on this source-target pair. The Hive family (consisting of Hive, Impala, Databricks SparkSQL, and SparkSQL) covers a lot of different common systems used within data engineering and is frequently utilized within customer projects.

This month, we’ve fixed parsing issues with:

  • Create table statements with input/output formats
  • Alter table statements
  • Struct complex data type

We’ve also added new statement support for:

  • Setting the session timezone
  • Role grants
  • Revoke statements
  • Unsupported message for the compute stats statement and the create function statement
  • Cluster by clause in the create table statement
  • Create table/view with common table expressions (CTE)
  • Translate FROM_TIMESTAMP, FROM_UTC_TIMESTAMP, TO_UTC_TIMESTAMP, and FROM_UNIXTIME functions
  • Escaped apostrophes

Examples

				
					Source: 
CREATE TABLE t1(col1 int)         
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

Target: 
CREATE TABLE t1 (col1 INT);
				
			
				
					Source: 
SELECT 
FROM_TIMESTAMP('2006-05-12 18:27:28.123', 'MM-dd-yy'), 
FROM_TIMESTAMP(start_date, 'MM-dd-yy'), 
FROM_UTC_TIMESTAMP('2006-05-12 18:27:28.123', 'America/Los_Angeles');

Target: 
SELECT
TO_VARCHAR('2006-05-12 18:27:28.123': :timestamp, 'MM-dd-yy'),
TO_VARCHAR(start_date, 'MM-dd-yy'),
CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', '2006-05-12 18:27:28.123');
				
			

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.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit