SQLMorph December 2021 Update

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');
				
			

How Do I Stay Updated on SQLMorph?

There are a number of ways to stay updated on the development of SQLMorph.  This blog series will continue to give you a taste of what we’ve been up to every month. However, if you’re currently working with the tool and need to see changes faster, check out the change log within the SQLMorph UI. 

You can also get in contact with us either using the feedback button in the SQLMorph UI (for SQLMorph specific bugs/feature requests) or via our contact us page. We regularly check this feedback to prioritize what we work on next and resolve any issues that customers face.

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

Table of Contents

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

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