Every Computer Science student takes a course on compilers, but it’s rare that you get to build one yourself. Therefore the phData Internal Engineering team jumped at the chance to build a transpiler, a special type of compiler so that our customers and engineers can get out of the business of manual SQL Translation.
The benefit of a SQL to SQL translator, transpiler or converter is clear. When migrating from one analytical database to another you have hundreds to thousands of SQL scripts that need to be converted. These come in the form of Data Definition Language (DDL) and data manipulation language (DML) scripts which are used for everything from defining tables to performing the transformation in ETL, to generating reports, to preparing data for Machine Learning.
There are commercial products on the market to convert between SQL dialects, but there are several limitations of those products:
- Cost – they are expensive, often charging per object or per line of code.
- Availability – there is a “long tail” of SQL to translate, but given these tools are expensive, only engineers on a project have access to them.
- Features – many tools in this market are based on regular expression matching, which can only perform simple function name replacements.
Get the Best SQL Translator for Free
phData customers get free access to SQLMorph. However, for a limited time, we are offering it to everyone for free. Sign up for free today!
This includes both the user-friendly UI and the command line interface for batch conversions. There is no charge regardless of the number of lines of code or objects you translate.
Who is This Available to?
SQLMorph is available to an unlimited number of users. This means not only do a few engineers executing the migration have access, the long tail of business intelligence developers, data scientists, data engineers, and others who currently use the legacy database also have access. Anyone in your organization can sign up and use the tool.
Features
SQLMorph is a true transpiler, which means SQLMorph parses the input query using a formal grammar-based lexer and parser (ANTLR). SQLMorph then translates that to an intermediate representation. This allows us to make simple “find and replace” changes as well as more complex changes.
For example, when translating SQL from Oracle to Snowflake, here is a sample of the translations which might occur:
- The function
systimestampis simply renamed tolocaltimestamp.
- More complex transformations like translating:
json_array(1, null, 3 null on null)
To:array_construct_compact(1, parse_json('NULL'), 3)
- Even more complex translations like “virtual columns”:
create table t1 (A number, B number as (ROUND(A)) virtual);
In this case, you can choose to have a view generated which includes the virtual columns:
create table t1 (A NUMBER);create view t1_view AS SELECT A, (ROUND(A)) AS B FROM t1;
Or you can have the virtual columns converted to physical columns which you’d have to populate on ingest:
create table t1 (A number, B number);
- The function
scn_to_timestamphas no translation and therefore SQLMorph flags it as an error:
-- ERROR: FunctionRewriter: line 1, character 7: No equivalent function for 'scn_to_timestamp' exists in Snowflake
- Virtual columns, which are like a combination of a table and view, can be translated either as a view on top of a table or as physical columns
Â
- In Snowflake, currently, you cannot add a column comment to a view column post view creation, therefore those DDL statements are translated into the view definition where possible.
While it’s possible to use Regular Expression based tools to do some of these translations, that approach quickly falls down. In order to translate the semantic meaning of the SQL statement, a transpiler is required.
Our transpiler has four phases. The first phase is parsing the source language using a grammar we’ve written for each source language. Then the result of the parser is translated into an intermediate representation. Then the rewriters perform target specific translations, and finally, the result is rendered in the target language.
Watch the video below to see SQLMorph in action:
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.
