Decisions. Whether it's paper or plastic, coffee or tea, gas or electric... there's always another decision waiting around the corner. And if you landed on this page, your next decision might just be as important as those previously listed: Snowflake Scripting or JavaScript. For many developers and data engineers, it might be the choice between a familiar road that doesn't quite get you to your destination and an unfamiliar road that gets you all the way there. Regardless, this is a decision you don't have to make alone.
Mobilize.Net has been converting SQL scripting languages like PL/SQL, T-SQL, and stored procedures in Teradata to JavaScript embedded in Snowflake SQL for several years now. (In fact, we've blogged about it in the past.) But a couple of months ago, Snowflake published that Snowflake Scripting is now fully available to any Snowflake users. (And we've blogged about a few things you can do with Snowflake Scripting in the past as well.) We've partnered with Snowflake to make it the best that it can be (and will continue to do so...), and that it continues to develop and evolve as more and more users take advantage of writing procedural code using only one language, SQL.
But what about migration? You might know that Mobilize.Net and Snowflake have partnered together to develop the best accelerator on the market to Snowflake, SnowConvert. Well, SnowConvert has been automating the incredibly complex conversion of bteq, PL/SQL, and T-SQL to JavaScript embedded in Snowflake SQL for several years now. If SnowConvert can go from sprocs to JavaScript, will it be able to go from sprocs to Snowflake Scripting Procedures? (Dare I say... snowsprocs...? Not to be confused with the incredibly offensive snowcrocs.) Of course. As you may expect, the conversion is much more straightforward.
Fear not though, we don't play favorites. With all the flavors of SnowConvert, you will still be able to choose whether you want to output to JavaScript or Snowflake Scripting.
Let's take a look at an example that showcases a single element of a conversion that is functionally the same in JavaScript created by SnowConvert and Snowflake Scripting created by SnowConvert, but with a potentially simpler output. I mean, if you're used to writing SQL, the switch to JavaScript could be jarring and often requires a completely different skillset.
When Mobilize.net SnowConvert evaluates a codebase, the automation tool's goal is to create functional equivalence, not to find the nearest equivalent in the target and give up if there is no near equivalent. As a result, when converting procedures to JavaScript, SnowConvert creates helper classes to create functionality that is not present in the target platform. This can be done by utilizing the full implementation of JavaScript that Snowflake supports.
However, when converting SQL procedures in a source platform to SQL procedures in Snowflake (Snowflake Scripting), the conversions are much more straightforward. Let's take a look at what this looks like with a very simple example in Oracle PL/SQL.
Let's take a sample procedure with a RAISE statement as an example. (Note that you can use this code to try this out.) Here's what that could look like in Oracle.
-- Oracle test procedure for RAISE conversion. CREATE OR REPLACE PROCEDURE simple_exception_throw_handle(param1 INTEGER) IS my_exception EXCEPTION; my_other_exception EXCEPTION; BEGIN IF param1 > 0 THEN RAISE my_exception; END IF; EXCEPTION WHEN my_exception THEN IF param1 = 1 THEN RAISE; END IF; RAISE my_other_exception; END; --Completes without issue CALL simple_exception_throw_handle(0); --Throws my_exception CALL simple_exception_throw_handle(1); --Throws my_exception, catches then raises second my_other_exception CALL simple_exception_throw_handle(2);
Pretty straightforward, right? We have two exceptions (my_exception and my_other_exception), and based on a certain condition, one of those exceptions may be called. What would this look like in Snowflake Scripting and Javascript? (We're... so glad you asked.)
In Snowflake Scripting, the same procedure would look like the following:
-- Snowflake Scripting test procedure for RAISE conversion. CREATE OR REPLACE PROCEDURE PUBLIC.simple_exception_throw_handle (param1 INTEGER) RETURNS VARCHAR LANGUAGE SQL -- Using Snowflake Scripting. EXECUTE AS CALLER AS $$ DECLARE my_exception EXCEPTION; my_other_exception EXCEPTION; BEGIN IF (:param1 > 0) THEN RAISE my_exception; END IF; EXCEPTION WHEN my_exception THEN IF (param1 = 1) THEN RAISE; END IF; RAISE my_other_exception; END; $$; -- Test it out to see what happens! CALL PUBLIC.simple_exception_throw_handle(0); CALL PUBLIC.simple_exception_throw_handle(1); CALL PUBLIC.simple_exception_throw_handle(2);
Looks... pretty similar to the first one, right? That's because it is. The RAISE function implemented in Snowflake with Snowflake Scripting is essentially the same as the function the RAISE function in Oracle. That's by design. SQL has been around longer than Snowflake, and now that Snowflake Scripting is here, Snowflake's implementation of procedures written in SQL will look similar to what's already out there. The output code in Snowflake Scripting is easy to understand and easy to manipulate for developers familiar with SQL.
Now that we've seen the straightforward conversion to Snowflake Scripting, what about JavaScript? That will ultimately look similar, but only because Mobilize.Net SnowConvert recreates the functionality of the RAISE function in Oracle PL/SQL with a helper function written in JavaScript for Snowflake. Let's take a look at what that could look like. (Note that you won't be able to run this directly in Snowflake because a large quantity of lines of code have been removed from the helper class for the brevity of this code.)
-- Snowflake JavaScript test procedure for RAISE conversion. CREATE OR REPLACE PROCEDURE PUBLIC.simple_exception_throw_handle (param1 FLOAT) RETURNS STRING LANGUAGE JAVASCRIPT -- Using JavaScript. EXECUTE AS CALLER AS $$ // REGION SnowConvert Helpers Code // Other helper functions would appear here // The helper class we care about appears here. var RAISE = function (code,name,message) { message === undefined && ([name,message] = [message,name]) var error = new Error(message); error.name = name SQLERRM = `${(SQLCODE = (error.code = code))}: ${message}` throw error; }; // END REGION /* ** MSC-WARNING - MSCEWI3052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */ /* my_exception EXCEPTION */ ; /* ** MSC-WARNING - MSCEWI3052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */ /* my_other_exception EXCEPTION */ ; try { if (PARAM1 > 0) { RAISE(-6512,`MY_EXCEPTION`,`MY_EXCEPTION`); } } catch(error) { switch(error.name) { case `MY_EXCEPTION`: { if (PARAM1 == 1) { throw error; } RAISE(-6512,`MY_OTHER_EXCEPTION`,`MY_OTHER_EXCEPTION`); break; } default: { throw error; break; } } } $$; CALL PUBLIC.simple_exception_throw_handle(0); CALL PUBLIC.simple_exception_throw_handle(1); CALL PUBLIC.simple_exception_throw_handle(2);
In this output, SnowConvert for Oracle creates a JavaScript helper function called raise that replicates the functionality of the SQL RAISE function. The functionality of the rest of the procedure is recreated in the try statement (with a few unnecessary elements removed with a comment from SnowConvert letting you know something has changed) utilizing the helper raise function created above. The output is functionally equivalent to the other two procedures (Oracle and Snowflake Scripting) given above.
Given this example, isn't Snowflake Scripting the better alternative when writing procedural code in Snowflake? It might appear to be obvious, but as is so often the case, it depends. The above procedure is pretty simple. It's raising an exception. It's not performing a series of operations on or across databases, which are often the objective of procedural code. To the extent that Snowflake Scripting supports the functionality that exists in your SQL procedures, then yes. It's likely that Snowflake Scripting will be a much simpler and straightforward conversion from the source platform you're used to. However, if that functionality does not yet exist (and we do mean "yet", as Snowflake is constantly updating the functionality [and subsequent documentation] present in Snowflake Scripting), then Snowflake's implementation of JavaScript allows SnowConvert to recreate that functionality, and the conversion to JavaScript will likely lead to a higher level of functional equivalence. Of course, the obvious downside is that you will now have more than one language to support (SQL and JavaScript).
But as we mentioned at the top, you don't have to make these decisions alone. You can use SnowConvert to get an assessment of the code you have and get an idea of the level of automated conversion you could expect to either Snowflake Scripting or JavaScript. You can also find more of these examples by requesting access to our translation reference documentation (our public documentation site for SnowConvert has great support, but there's an even deeper library we can make available). Let us know how we can help and we will.
Unless it's about that whole coffee or tea thing. That debate continues to rage around the mahogany halls of the Mobilize.Net offices and shows no end in sight.