phData Toolkit Enhances SQL Translation with Redshift to Snowflake Support

We are excited to announce that the phData Toolkit SQL Translation Tool now offers translation support from Amazon Redshift to Snowflake. This new functionality allows users to effortlessly convert Redshift SQL statements to their equivalent Snowflake SQL statements.

This blog will explore how the SQL Translation Tool performs the Redshift-to-Snowflake translation and which statements are supported.

Quick Info on The SQL Translation Tool

The SQL Translation Tool in the phData Toolkit simplifies the translation overhead of SQL statements during database migration. It translates SQL statements from various dialects to their equivalent in Snowflake, along with detailed messages, which helps users better understand the translation changes made. The Toolkit is free for limited use to anyone with a Toolkit account. phData customers receive a permanent pro license, which allows unlimited use of the entire Toolkit.

SQL Translation supports both a user-friendly UI for single conversions and a powerful CLI for large batch conversions. The CLI also generates a report depicting the percentage of translation coverage on both the files and statements level. 

The Supported dialects include Oracle, Microsoft SQL Server, Hive, Impala, SparkSQL, Databricks SparkSQL, Hana, Teradata, and Netezza. And now, Amazon Redshift has joined the list. 

Though today is the formal launch of production-ready support, we have built the translation coverage out over many months and provided over 97% translation coverage for the SQL of a pilot customer. We expect initial translation coverage to be well over 85% for typical customer SQL going forward. We will continue to build out coverage in collaboration with our services at no cost to our customers.

For more information on the SQL Translation Tool, please visit our documentation.

Getting Started with the UI

Step 1: Sign in to phData’s Toolkit.

Step 2: Navigate to SQL Translation from the tools list in the left pane.

Step 3: Select the source dialect as Redshift, and the target dialect will automatically be set to Snowflake.

Step 4: Paste/upload your input SQL statement/file and hit the Translate button. The relevant Snowflake statement will appear on the right side.

Getting Started with the CLI

Step 1: Install the Toolkit cli and initialize the Toolkit project.

Step 2: Generate and set the Authentication token.

Step 3: Translate using toolkit sql translate command, which will also generate a report. 

					toolkit sql translate redshift snowflake [--input <input file/directory>] [--output <output file/directory] [--query <query>]> [--extensions <ext1> <ext2> ...] [--encoding <encoding>]
				

In the CLI, you can perform both single-file and batch conversions. For more instructions, refer to our CLI tutorial.

Supported Statements of Redshift-to-Snowflake Translation

Note: This list is constantly expanding as a part of our weekly Toolkit releases.

Example SQL Translations

Ex 1: Create database, Create schema, and Create Table Statements

Input

					create database db1;
create schema if not exists us_sales;
create table if not exists cities(
    cityid integer not null,
    city varchar(40) not null,
    citygroup varchar(10) default 'Special',
    state char(20) not null,
    stateid integer not null,
    primary key(cityid),
    foreign key(CustomerID) references Customers(stateid)
) encode auto; 

				

Ex 2: Insert Statement

Input

					insert into category_stage values
    (12, 'Concerts', 'Comedy', 'Stand-up comedy'),
    (13, 'Concerts', 'Other', default);
insert into category_stage
    (select * from category);

				

Ex 3: Create Procedure

Input

					CREATE PROCEDURE proc2() AS 
$$
DECLARE
  total_price FLOAT;
  reports RECORD;
BEGIN
  FOR reports IN SELECT price FROM invoices LOOP
    total_price := total_price + reports.price;
  END LOOP;
  RETURN total_price;
END;
$$ LANGUAGE plpgsql;

Call proc2();

				

Unique Solutions

A few clauses, data types, and statements are not directly supported in Snowflake. They are specially translated to retain their expected functionality.

Statement 1 shows how a SELECT INTO statement is translated as a CREATE TABLE statement.

Statement 2 shows how we implemented the Update statement with Common Table Expressions (CTE).

Statement 3 shows a few data type mappings along with max length.

Statement 4 Shows how to drop multiple tables and views.

Next Steps

We encourage you to try your own Redshift-to-Snowflake SQL translations in the Toolkit. While you are there, explore the other powerful tools in the phData Toolkit too. Finally, you can share feedback or suggestions using the support form. Our Updates page will keep you informed of the latest features and fixes.

Start Using SQL Translation Today!

The post phData Toolkit Enhances SQL Translation with Redshift to Snowflake Support appeared first

Source: View source