Information from this blog comes from the Snowflake Architecture and SQL book by Tom Coffing and David Cook.
Are you ready to learn stored procedures from the best technical trainer the world has ever seen? Tom Coffing, AKA Tera-Tom, makes learning fun, exciting, and easy.
Tom became famous as a teacher because, for over 30 years, he memorized every student's name when he greeted them. Tom has taught over 1,000 classes and never missed a single name. Ask anyone who attended a class taught by Tom Coffing.
Tom has also led a team of developers for almost 20 years to create the Nexus, which queries, migrates, and joins data across all systems. The chart below shows which systems users can query, migrate, and join data automatically with Nexus. Many large customers moving from legacy systems to Snowflake use the Nexus Server from Coffing Data Warehousing.
You can download a free trial of Nexus at CoffingDW.com. You can also see some of the great features of Nexus right here.
You can also see a video of the Nexus migrating tables from Teradata to Snowflake here:
Snowflake Stored Procedures
Snowflake gives you two options for stored procedures: Javascript and SQL. This blog post will start you out with the fundamentals for both.
In the picture below, we create a procedure named input_arguments. Notice right after the procedure name, we have an input variable named abc_arg1 (in lower case). You will always have parentheses after the procedure name, but they will not always have a parameter.
Notice that the language for this procedure is javascript. We will start the procedure's body with $$ in this example. We also end the procedure's body with $$.
The most important thing to notice is the return ABC_ARG1, which is the input parameter but is always capitalized in the procedure's body, or an error occurs.
Once the stored procedure is created, we CALL the stored procedure, and since it is expecting the input parameter (named abc_arg1), we must include a parameter, and we have chosen 'Hello World.'
You SELECT from a table or view but always call a stored procedure.
The next example below is another javascript procedure with an input parameter. We name the procedure input_arg_backticks because we return the information in the call using backticks and a dollar sign. You will see multiple ways to write Snowflake stored procedures in Javascript, so I want you to learn both ways.
Our next example creates a stored procedure using LANGUAGE SQL. Notice that the name is CUST_INSERTS_XYZ and that there are no parameters, only an empty set of parenthesis.
We have our $$ signs of beginning and ending the body, but we also have a BEGIN and END statement, which defines a body of work. You could have multiple BEGIN and END statements.
Notice we do a DROP TABLE IF EXISTS statement. The IF EXISTS is important because if we just tried to do a DROP TABLE, but the table did not exist, we would get an error, and the procedure would stop. However, because we specifically state DROP TABLE IF EXISTS, the procedure will not stop if the table does not exist. If the table does exist, it will do a DROP.
We then CREATE a table named MY_CUSTOMER_TABLE_XYZ from another table (CUSTOMER_TABLE), so the new table will exist with data.
Finally, we INSERT into the new table a new row containing 'Puppies by Phil' as a new customer. The example below is not the best way to insert data using a stored procedure, but the next example will be better because we will have input parameters.
The example below shows how to use input parameters in a stored procedure. We have three input parameters named IN_CUST, IN_NAME, and IN_PHONE. When we call the stored procedure, we pass the arguments and data inserts.
The stored procedure below is so good because you can run it a thousand times and pass it a different set of parameters each time. The colons in the VALUES clause represent input parameter data being passed to the stored procedure during the CALL.
Below is another example of an SQL stored procedure in Snowflake with two input parameters that we use to perform an update of a table.
The picture below inserts data into a table using a Snowflake Javascript stored procedure. Notice again that all input parameters must always be capitalized in the body. Also, notice how Javascript has a var command, a var stmt, which utilizes a Javascript function called the snowflake.createStatement. The stored procedure finally needs to execute the stmt.
Here is an example of a Snowflake Javascript procedure doing an update.
The picture below is Tom and his family in 1988 when Tom was a programmer for NCR corporation. Tom has been married for 41 years.
Comentários