Click me
Kickstart your modernization with Digital Transformation Starter Kit - Free Download
Announcing Mobilize.Net and Snowflake partner to migrate data warehouses to the cloud.

Converting SQL and Stored Procedures

by Brandon Carver, on Apr 28, 2020 6:39:45 AM

spock_imageLet’s talk about SQL and sProcs. And no, we are not talking sequels about Spock (though that does sound compelling, that has already been done), but something just as exciting: translating SQL and Stored Procedures (sProcs). Two weeks ago, Mobilize.Net and Snowflake announced a partnership to provide script translation tools for converting from a legacy, on-prem warehouse to Snowflake. We’ve been translating code since the days of Windows 95, but what’s there to translate with SQL? Isn’t SQL the same everywhere you go? And what does this have to do with Spock? (Again, it’s sProcs.)

You would think SQL was the same everywhere, but alas no. That would be way too logical. SQL has ANSI standards that most data warehouses adhere to, but everyone’s architecture is a bit different. That “bit” of difference creates huge problems when translating from one architecture to the next. When transforming from a legacy on-prem warehouse to the cloud, both systems are going to have “things” that appear in one that don’t appear in the other. For example, most Teradata SQL programmers I know always start creating a set table the same way:

CREATE SET TABLE DATABASE.TABLE
, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT   , DEFAULT MERGEBLOCKRATIO (

That’s quite the mouthful for creating a table (no one ever accused SQL of lacking verbosity). What’s actually happening here? Why can’t I just write ```CREATE TABLE DATABASE.TABLE (``` and let that be the end of it? It’s that architecture and those extensions of the ANSI SQL. The ```NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO``` statements are essentially telling Teradata how to store the table or different attributes about the table (i.e. not to use large quantities of space to create backups and save copies in order to recover the table).

In Snowflake, would we create the table the same way? Nope. Different architecture, different commands. In fact, the same table created in Snowflake would look like:

CREATE TABLE DATABASE.SCHEMA.TABLE (

That’s it. Snowflake optimizes the architecture and has an ever present recovery system in place that doesn’t need you to turn it on or off every time you create a table.

But what about those sProcs? Same story: different architecture. Stored Procedures written for a legacy system like Teradata are generally (not always) written in SQL. Here’s an example of a simple Stored Procedure:

REPLACE PROCEDURE DATABASE.FOR_STATEMENT()
BEGIN
FOR fUsgClass AS cUsgClass CURSOR FOR
     SELECT
           
COL0
          ,TRIM(COL1) AS COL1ALIAS
          ,TRIM(COL2)
          ,COL3
     FROM cust_knowledge_db.Some_Other_Table
DO
     SET lvSeqNo = lvSeqNo + 1;
     SET UPDATE1 = fUsgClass.COL1ALIAS + 1;
     SET UPDATE2 = fUsgClass.COL3 + 123;
END FOR;
END;

We have some simple commands like FOR and DO in this procedure along with a CURSOR statement. (This is a Stored Procedure, Teradata-style.) We’re using those to update the values in our columns. It’s relatively simple as far as sProcs go, and depending on what you are trying to do, quite useful. However, will this procedure work in Snowflake? You guessed it. Nope. How will we ever overcome this tragedy?

Snowflake uses JavaScript as its scripting language for stored procedures. If you tried to import this directly from Teradata into Snowflake, it would fail. You need to rewrite it to something that looks like this:

CREATE OR REPLACE PROCEDURE DATABASE.PUBLIC.FOR_STATEMENT ()
     RETURNS STRING
     LANGUAGE JAVASCRIPT
     EXECUTE AS CALLER
     AS
     $$
     var sql_stmt = `SELECT
                COL0

               ,TRIM(COL1) AS COL1ALIAS
               ,TRIM(COL2)
               ,COL3
          FROM cust_knowledge_db.PUBLIC.Some_Other_Table`;
     var stmt = snowflake.createStatement({
          sqlText : sql_stmt
     });
     var res = stmt.execute();
     while ( res.next() ) {
          var fUsgClass = {
               COL0 : res.getColumnValue(1),
               COL1ALIAS : res.getColumnValue(2),
               
COL3 : res.getColumnValue(4)
          };
          lvSeqNo = lvSeqNo + 1;
          UPDATE1 = fUsgClass.COL1ALIAS + 1;
          UPDATE2 = fUsgClass.COL3 + 123;
     }

          $$;

The same action is accomplished, and our sProc works just as well as it always has. Except that it’s in JavaScript now. Does this mean that we have to know SQL and JavaScript in order to write Stored Procedures? These extensions to the ANSI SQL are already costing time, money, and preventing a move to the cloud. Do we double down and get another competent Full Stack Developer to add to the team to deal with these problems related to JavaScript? I wonder how much time and money that might cost.

Luckily, Mobilize.Net is here to turn one set of SQL into another set of SQL, and all of your most precious and complex sProcs into JavaScript. We'll write some more about sProcs in the future, but for now: live long and prosper.

Topics:data warehouse migrationTeradata to Snowflake migrationSnowflakesql conversion

Comments

Mobilize.Net Blog

Mobilize.Net accelerates and simplifies the transformation of software applications to .NET, web, mobile, and cloud platforms.
More →

More...

Subscribe to Updates