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
- 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
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 );
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 );
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
About Sogeti Labs
SogetiLabs gathers distinguished technology leaders from around the Sogeti world. It is an initiative explaining not how IT works, but what IT means for business.