For those new to SQLMorph, it is a free-to-use transpiler built to convert SQL statements from source to target.
By using this tool either through the UI or published Python script, it allows you to quickly translate your existing SQL statements into their destination. This lets you spend more time on complex topics such as establishing data governance practices.
Over the last month, our development team has been busy adding additional functionality and new features. As we continue to take feedback from customers and our data engineering team, the tool continues to grow more and more feature-rich.
Core Functionality Updates
SQLMorph’s user interface (UI) and command line interface (CLI) both share the same backend application interface (API). We will talk more specifically about individual updates to the CLI and UI later, but first, let’s talk about core updates to the platform.
Oracle to Snowflake
- Translate ‘COMMENT ON COLUMN’ stmt to column comment on ‘CREATE VIEW’ statement
- Add ‘OR REPLACE’ clause in Create_view statement
- Fix ‘Compute Statistics’ parse error in ‘using index’ clause
- Fix rewriter to rewrite ‘SYSDATE’ as ‘CURRENT_DATE()’
- Fix Trunc function for both Numeric and Date arguments
- PLSQL – Parse ‘Create or replace Package body’ syntax
- PLSQL – Add error msg for dbms_output stmt as it is not supported in Snowflake
- PLSQL – Add rewriters for looping stmts to rewrite StringConcatenation ‘||’ to ‘+’
- PLSQL – Fix ‘=’ operator in IfStmt and WhileLoop condition
Example:
Source:
PROCEDURE keepFunc (val1 NUMBER) IS
BEGIN
dbms_output.put_line( 'After loop: ' || val1 );
END;
Target:
create or replace procedure keepFunc ("val1" FLOAT)
returns string
language javascript
as
$$
//-- ERROR: PlSqlStmtVisitor: line 3, character 2: Snowflake does not support PLSQL `DBMS_OUTPUT` stmt
dbms_output.put_line('After loop: ' || val1);
$$
;
Microsoft SQL Server to Snowflake
- Implement GO command as ‘;’ (script separator) and Use_database statement
- Implement Set statement
- Implement Alter_table statement
Example:
Source:
ALTER TABLE T3 ADD C2 varchar(50) COLLATE Latin1_General_BIN;
ALTER TABLE dbo.doc_exc DROP my_constraint;
Target:
ALTER TABLE T3 ADDCOLUMN
-- ERROR: MSSqlTableColumnsVisitor: line 1, character 19: Snowflake does not support `COLLATE` in add_columns of Alter_table statement
C2 VARCHAR(50);
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint;
Impala to Snowflake
- Implement coalesce function
- Fix rewriter to support ANTI JOIN with multiple ON condition
Example:
Source:
SELECT * FROM employee e LEFT ANTI JOIN department d ON (
e.id = d.id and e.name = d.name
);
Target:
SELECT * FROM employee e
LEFT OUTER JOIN department d ON (e.id = d.id and e.name = d.name)
WHERE
e.id NOT IN (SELECT id FROM department)
AND e.name NOT IN (SELECT name FROM department);
CLI Updates
One of the main pieces of SQLMorph that’s seen an increase in usage and functionality is the Python CLI. This tool is built for power users who either have large amounts of SQL to translate or prefer a scripted approach.
Functionality Updates
There are two main ways to use the CLI: running against a set of files/folders, or by providing a connection to an Oracle database and schema. We’ve added new functionality to both use cases.
Previously, when integrating with Oracle, the tool would only crawl database tables in the given schema. We received feedback that consumers would also like to be able to crawl views in the provided schema as well. We’re happy to announce that we have added this functionality, and you now have the ability to crawl views in an Oracle database schema. Once run, the output directory will contain a directory with the source sql and translated sql. You can use this functionality by providing the –db-views flag.
Since SQLMorph uses the same API as the UI, the core functionality updates previously mentioned also apply to the CLI!
Documentation Updates
If you’re familiar with the SQLMorph UI, you may know that you can define configuration parameters for how you want your SQL to be transpiled:
- Should oracle virtual columns be translated to a view on top of the table physical columns
- Oracle DATE values can have time, what type should they be translated to
While users have been able to specify these configurations within the CLI for a while, it was an undocumented feature. We’ve now added this to our public documentation and CLI help output. This additional configuration allows for matching configuration functionality between the CLI and UI.
User Interface Updates
While our primary focus has been on the core functionality and CLI updates for the past month, we’ve also made some updates to the user interface.
Primarily, we recognized that the demo queries that we provide to showcase SQLMorph functionality were very basic and didn’t represent queries that typically exist for our customers. We have since updated the queries to be more complex, interesting, and representative of real-world scenarios.
We also fixed a couple of bugs within the user interface. There was an issue with sending feedback, which is primarily used for customers to report issues with queries or provide feedback on better translations for a given query.
Tidbits
- If you wish to view the change log in full by release, you can always navigate to the “Change Log” page within the user interface.
- Your phData account is reused between SQLMorph and Traverse with Single Sign On!
- When using the SQLMorph CLI, you will need your authentication token. This can be found by navigating to the “API” page within the user interface.
- SQLMorph works by parsing the source SQL dialect and creating an internal intermediate representation of the desired statement; it then writes this intermediate representation to the target dialect and uses it to render the final translated statement.
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.
