Welcome to the SnowConvert for Teradata Lab 3. The main purpose of this lab is to guide you on the related tasks to take the Functions migrated using the SnowConvert Teradata tool, how to deploy them and some general guidance on how to finish any issues that required some manual revision.

What You'll Learn

Prerequisites

A function is a subprogram that is used to return a single value.

They can be simple expressions like:

create or replace function adder(n1 in number, n2 in number)    
return number    
is     
n3 number(8);    
begin    
n3 :=n1+n2;    
return n3;    
end;

or leverage some SQL

CREATE OR REPLACE FUNCTION totalCustomers  
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM customers;  
    RETURN total;  
END;

UDFs in Snowflake

Snowflake allows you to create UDFs. In snowflake there are two types of UDFs

For more details check Snowflake Doc

Snowflake SQL UDFS

A SQL UDF evaluates an arbitrary SQL expression and returns the results of the expression. The function definition can be a SQL expression that returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows. The expression can be a query expression, though for non-tabular functions, the query expression must be guaranteed to return at most one row, containing a single column. The expression defining a UDF can refer to the input arguments of the function, and to database objects such as tables, views, and sequences. The UDF owner must have appropriate privileges on any database objects that the UDF accesses. A SQL UDF's defining expression can refer to other user-defined functions, though it cannot refer recursively to itself, either directly or through another function calling back to it.

Snowflake JavaScript UDFs

JavaScript UDFs allow you to manipulate data using the JavaScript programming language and runtime environment. JavaScript UDFs are created in the same way as SQL UDFs, but with the LANGUAGE parameter set to JAVASCRIPT.

Similar to SQL UDFs, JavaScript UDFs can return either a scalar or a tabular result, depending on how the UDF is defined.

A JavaScript UDF's defining expression can refer recursively to itself, but cannot refer to other user-defined functions.

Create the table and data to use:

create table purchases (number_sold integer, 
wholesale_price number(7,2), retail_price number(7,2));
insert into purchases (number_sold, wholesale_price, retail_price) values 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

Create the UDF:

create function profit()
  returns numeric(11, 2)
  as
  $$
    select sum((retail_price - wholesale_price) * number_sold) from purchases
  $$;

Call the UDF in a query:

select profit();

Output:

+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

Let's try a very quick an simple example:

CREATE OR REPLACE FUNCTION VALIDATE_ID(ID float) returns string
  LANGUAGE JAVASCRIPT
AS
$$
  if (ID < 0) {
    return 'Invalid ID';
  }
  return 'Valid ID';
$$
;
--- Create a table with valid and invalid values

create table students(id integer);
insert into students(id) values (44),(-44);

select ID, validate_id(ID) from students order by ID;

What libraries are available in my Snowflake Javascript UDF

--- this will list all the ECMAScript objects available to your Snowflake Functions
CREATE OR REPLACE FUNCTION all_props() returns string
  LANGUAGE JAVASCRIPT
AS
$$
  return Object.getOwnPropertyNames(this);
$$
;

select all_props();

Recomendations

Sometimes using Javascript to solve some tasks can be challenging. For programmers with experience with nodejs it will probably easier.

We usually recommend that you start a JS terminal and try your code there. It could be with nodejs or just using your browser.

For example open a terminal and type:

node

That will start the command line REPL. At that termina you can try some JS code. Once it works there. You can then copy and paste that code in your Snowflake Function.

Interesting snippets you can try:

RGB Encoding into HEX

// Converts the values of RGB components to a hexadecimal color code.

/*Convert given RGB parameters to hexadecimal string using bitwise left-shift operator (<<) and Number.prototype.toString(16).
Use String.prototype.padStart(6, '0') to get a 6-digit hexadecimal value.*/

const RGBToHex = (r, g, b) =>
  ((r << 16) + (g << 8) + b).toString(16).padStart(6, '0');

// And to run it
RGBToHex(255, 165, 1); // 'ffa501'

Array Operations

// Creates an array of partial sums.

/*Use Array.prototype.reduce(), initialized with an empty array accumulator to iterate over nums.
Use Array.prototype.slice(-1), the spread operator (...) and the unary + operator to add each value to the accumulator array containing the previous sums.*/
const accumulate = (...nums) =>
  nums.reduce((acc, n) => [...acc, n + +acc.slice(-1)], []);
accumulate(1, 2, 3, 4); // [1, 3, 6, 10]
accumulate(...[1, 2, 3, 4]); // [1, 3, 6, 10]