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
Source:
from src
insert into target1(y,z) select src.a, src.b
insert into target2(y,z) select src.c, src.d;
Target:
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
Source:
EXPLAIN SELECT COUNT(*) FROM t1;
Target:
-- INFO: HiveFamilyStmtVisitor: line 1, character 0: Snowflake returns the logical execution plan for the specified SQL statement.
EXPLAIN USING TEXT SELECT COUNT(*) FROM t1;
SQLMorph will give you info, warnings, and errors based on the specific transpilation you’re performing to ensure you know the limitations.
Show Statement
Source:
show databases;
Target:
SHOW SCHEMAS;
Source:
show create table tb1;
Target:
-- INFO: HiveFamilyStmtVisitor: line 1, character 0: Snowflake equivalent of `show create table` statement is `get_ddl` function.
SELECT GET_DDL('TABLE', tb1)
Source:
show compactions;
Target:
-- ERROR: HiveFamilyStmtVisitor: line 1, character 0: Snowflake does not support `SHOW PARTITIONS/TABLE EXTENDED/TBLPROPERTIES/INDEX/CONF/COMPACTIONS`.
SHOW 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.
