November 22, 2021

SQLMorph November 2021 Update

By Nick Goble

As customers are aiming to migrate between different database and data warehouse solutions, we continue to listen to pain points and look for opportunities to automate the transition.  One of the frequent pain points is converting existing processes into the new system.  Customers have years of SQL written to perform DDL/DML operations, and the SQL dialects between systems often vary. This is where SQLMorph comes in.

SQLMorph, part of a set of tools that phData provides for customers, 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.

Over the last month, we’ve been focusing on additional functionality for the Hive family of systems (Hive, Impala, Databricks SparkSQL, and SparkSQL). Since these all mostly use the same variant of SQL syntax, it allows us to enable more of our users to perform transpilations.

Let’s take a look at what new functionality has been added.

Hive Family to Snowflake

The Hive family (Hive, Impala, Databricks SparkSQL, and SparkSQL) is a group of SQL dialects that are all identical or nearly identical in their dialect. This allows us to target a wide range of technologies with one set of transpilations.

Over the course of the last month, we’ve targeted a few different kinds of statements for transpilation.  These include statements like :

  • Materialized views
    • Create
    • Delete
    • Update
  • Utility Functions
    • Show
    • Describe
    • Explain
  • Math Functions
    • Hash
    • Murmur Hash
  • Collection Functions
    • Set
    • Union
    • Multi Insert

As you can see, that’s a lot of functionality and coverage for one month of changes!  Let’s take a look at some of the specific examples of these transpilations.

Multiple Inserts

from src 
  insert into target1(y,z) select src.a, src.b 
  insert into target2(y,z) select src.c, src.d;
INSERT INTO target1 (y, z) SELECT src.a, src.b FROM src;
INSERT INTO target2 (y, z) SELECT src.c, src.d FROM src;

Explain Statement

-- INFO: HiveFamilyStmtVisitor: line 1, character 0: Snowflake returns the logical execution plan for the specified SQL statement.

SQLMorph will give you info, warnings, and errors based on the specific transpilation you’re performing to ensure you know the limitations.

Show Statement

show databases;

show create table tb1;
-- INFO: HiveFamilyStmtVisitor: line 1, character 0: Snowflake equivalent of `show create table` statement is `get_ddl` function.
show compactions;
-- ERROR: HiveFamilyStmtVisitor: line 1, character 0: Snowflake does not support `SHOW PARTITIONS/TABLE EXTENDED/TBLPROPERTIES/INDEX/CONF/COMPACTIONS`.

The SQLMorph UI will show errors in red to alert you to a failed transpilation.

Other Updates

While we have mostly been focused on the Hive family to Snowflake transpilations, we’ve also made a couple of minor updates to our other source/target pairs. These include:

  • Fixed parsing of SAMPLE for Microsoft SQL Server
  • Fixed parsing of MONITORING for Oracle
  • Fixed parsing of LIMIT for Microsoft SQL Server when using an alias

Getting Started With SQLMorph

Now that we’ve covered what’s new with SQLMorph, we’re sure you’re just as excited as us to get your hands on the tool. SQLMorph is free-to-use and can be accessed a couple of different ways.  

You can visit our SQLMorph webpage, create an account, and quickly get started by going to the translate tab in the navigation bar.  Alternatively, you can use the SQLMorph python script that hits the same API as our website. If you are looking for more information on SQLMorph, you can view our docs and check out our full blog on SQLMorph.

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