Azure Synapse Pathway or how to easily migrate your Data Warehouse

Recently, I wrote about a nice set of new features for Azure Synapse, here. Although, the party continues and today I present you a preview solution that comes to make life easier for organizations that want to modernize their data warehouses by moving them to the Microsoft Cloud and above all, providing them with Synapse’s extraordinary analytical capabilities. This is Azure Synapse Pathway.

Azure Synapse Pathway is currently under development, although the business idea is very good, as it will speed up the migration of analytics solutions by translating millions of lines of SQL code into TSQL compatible with Azure Synapse. Something that will undoubtedly alleviate the headaches of many CDOs.

Currently, this service is available for the following components both on premise and in the cloud:

• Amazon RedShift
• Google Big Query
• Teradata
• Snowflake
• IBM Netezza
• Microsoft SQLServer

The process is based on three steps and is intended to be able to maintain the functionality of the original code. For this, even the use of metadata is taken into account in order to offer the maximum quality of the translation.

In the first phase, a SQL code tokenization process is executed , something that is not new since there are many third-party services that execute this process. The idea is to extract the different logical pieces, understand them and finally plan their execution in order to guarantee that the result is successful. This corresponds to the second phase, where all the tokens are grouped giving rise to a skeleton of instructions that must be translated, which is the last of the phases included in the process. In it, our Pathway knows Synapse’s own TSQL code syntax, so the process is based on converting the code fragments into readable instructions.

You can download Azure Synapse Pathway from here .

I am going to carry out a first test for Microsoft SQ Server, for this, in addition to downloading Azure Synapse Pathway, I must verify that I have .NET 5.0 Desktop Runtime installed on my machine.

For this initial demo, I create a simple CREATE TABLE example in the ‘Pathway Input’ folder and set the translation output to the ‘Pathway Output’ folder, as seen in the image below.

Then I click on the “Translate” button and I get this summary

source code

CREATE TABLE [dbo].[Ward]
(
        Id	                INT IDENTITY(1,1) PRIMARY KEY,
	BPID 	                NVARCHAR(100) NULL,
	Name 		        NVARCHAR(100) NULL,
	IsActive		BIT NOT NULL,
	PhoneNumber 	        NVARCHAR(100) NULL,
	FaxNumber 		NVARCHAR(100) NULL,
	NHBPID 	                NVARCHAR(100) NULL,
	SapNoOfBeds 		INT NULL,
	SapNoOfIncoBeds 	INT NULL,
	Name2            	NVARCHAR(100) NULL,
	Name4  	                NVARCHAR(100) NULL
	);

translated code

CREATE TABLE [dbo].[Ward]
(
  [Id] INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED NOT ENFORCED
, [BPID] NVARCHAR(100) NULL
, [Name] NVARCHAR(100) NULL
, [IsActive] BIT NOT NULL
, [PhoneNumber] NVARCHAR(100) NULL
, [FaxNumber] NVARCHAR(100) NULL
, [NHBPID] NVARCHAR(100) NULL
, [SapNoOfBeds] INT NULL
, [SapNoOfIncoBeds] INT NULL
, [Name2] NVARCHAR(100) NULL
, [Name4] NVARCHAR(100) NULL
    );

CONCLUSION
In the process, I had to eliminate the CONTRAINTS for some of the fields in the source table, since it returned an error. So you must, yes or yes, continue to improve. However, as I indicated at the beginning, it will be a great tool if Microsoft finally manages to cover a wide range of solutions, instructions, and functions. So let’s keep a close eye on the Azure Synapse Pathway.

Cover photo thanks to  Karolina Grabowska  at  Pexels