Welcome and Happy New Year to all our readers! phData had an amazing 2022 and we are looking forward to what additional value we can drive for our customers in 2023.
We here at phData are taking this time to reflect. phData is Snowflake’s 2022 Partner of the Year award among, many other Snowflake awards offering several services across many different disciplines:
- Data Engineering
- Data Science and Machine Learning
- Reporting, Visualization, and Analytics Services
- Change Management, Enablement, and Learning
- Strategy Services
- Elastic Operations
Through our customers and projects that span across all of these offerings, we’ve identified common patterns, processes, and tasks that occur. phData is built on the foundation of bringing together software engineering principles with data. We’ve built the phData Toolkit (and continue to iterate on it) to accelerate our customers and ensure data remains scalable, reliable, and secure.
Let’s take a look at what’s new this month:
SQL Translation
SQLMorph, our SQL translation tool, has seen several updates. We’ve continued to iterate and focus on the Microsoft SQL to Snowflake translation. We are seeing several customers performing this migration and driving to automate and simplify that migration as much as possible.
Here’s the new functionality that’s been added:
- Translate the table type variable declaration to the CREATE TEMPORARY TABLE statement.
- Parse the ROLLBACK TRAN statement.
- Parse the TOP clause using a variable.
- Translate DATENAME to DATE_PART.
- Translate CHECKSUM_AGG to HASH_AGG.
- Translate CHECKSUM to HASH.
- Translate PARSENAME to SPLIT_PART.
- Parse the IDENTITY function.
- Parse the MERGE statement WITH.
- Parse the DECLARE CURSOR statement with multiple cursor options.
- Parse the SET statement with multiple setting options.
- Parse the RAISERROR statement with errorMesssage and errorSeverity as variables.
- Translate the “INSERT INTO” statement with Common Table Expression.
- Parse user-defined data type.
- Parse DEL, DATASOURCE, and OVERRIDE identifiers.
- Translate SYSNAME datatype to NVARCHAR(128)
- Implement the WAITFOR statement.
- Parse the SELECT statement with the OPENXML clause.
- Parse the CREATE TABLE statement with more than one column_definition.
- Parse the UPDATE statement with the table_hint clause.
- Parse the arguments in the RAISERROR statement.
- Parse the keywords as identifiers.
- Support multiple drops in a single statement.
- Translate the SET DATEFIRST statement.
As you can see, that’s a significant difference in functionality and support for the Microsoft SQL source. Our development team is focused on driving additional support and capabilities within this pairing.
Let’s take a look at these specific translations:
SET DateFirst
Source:
SET
DATEFIRST 7;
Target:
-- WARNING: line 1, character 0: This statement changes the current session setting.
ALTER SESSION
SET
WEEK_START = 7;
WAITFOR Statement
Source:
WAITFOR DELAY '02:05:01.05';
Target:
CALL SYSTEM $WAIT(7501005, 'MILLISECONDS');
IDENTITY Function
Source:
SELECT
IDENTITY(int, 1, 1) AS ID_Num INTO NewTable
FROM
OldTable;
Target:
-- WARNING: line 2, character 32: Snowflake does not support `INTO` clause in SELECT statement. Snowflake CTAS is a workaround for this.
CREATE TEMPORARY TABLE NewTable AS
SELECT
-- ERROR: line 2, character 2: No equivalent function for 'identity' exists in Snowflake
IDENTITY(int, 1, 1) AS ID_Num
FROM
OldTable;
Bringing It Together
One of the main benefits of leveraging the phData Toolkit (and working with phData) is the focus we have on automation and our ability to execute. We had a couple of MSSQL to Snowflake projects come across our sales team from customers and identified that we needed to prioritize building out support for this translation. Our team was quickly able to pivot and scale to deliver the functionality needed for those projects. If you’re thinking about performing a platform migration to Snowflake and are trying to figure out how to migrate your data definition language (DDL) and data markup language (DML), check out the phData Toolkit. You can always contact us if you need help, want to see additional functionality, or if you want to have us evaluate performing your migration for you!
Happy New Year everyone! We are so thankful for those who stay up-to-date with our blog and can’t wait to see what the New Year brings!
phData Toolkit
If you haven’t already explored the phData Toolkit, we highly recommend checking it out!
We encourage you to spend a few minutes browsing the apps and tools available in the phData Toolkit today to set yourself up for success in 2022.
Be sure to follow: this series for more updates on the phData Toolkit tools and features.
