Translation reference to convert Teradata CREATE MACRO to Snowflake Scripting
Description
The Teradata CREATE MACRO defines one or more statements that are commonly used or that perform a complex operation, thus avoiding writing the same sequence of statements multiple times. The macro is executed when it is called by the EXECUTE statement.
For more information about CREATE MACRO click here.
The following code is necessary to execute the sample patterns present in this section.
IN -> Teradata_01.sql
CREATETABLEDEPOSIT( ACCOUNTNO NUMBER, ACCOUNTNAME VARCHAR(100));INSERT INTO DEPOSIT VALUES (1, 'Account 1');INSERT INTO DEPOSIT VALUES (2, 'Account 2');INSERT INTO DEPOSIT VALUES (3, 'Account 3');INSERT INTO DEPOSIT VALUES (4, 'Account 4');
OUT -> Teradata_01.sql
CREATETABLEDEPOSIT( ACCOUNTNO NUMBER(38, 18), ACCOUNTNAME VARCHAR(100))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;INSERT INTO DEPOSITVALUES (1, 'Account 1');INSERT INTO DEPOSITVALUES (2, 'Account 2');INSERT INTO DEPOSITVALUES (3, 'Account 3');INSERT INTO DEPOSITVALUES (4, 'Account 4');
Basic Macro
Since there is no macro object in Snowflake, the conversion tool transforms Teradata macros into Snowflake Scripting stored procedures. Besides, to replicate the functionality of the returned result set, in Snowflake Scripting, the query that is supposed to return a data set from a macro is assigned to a RESULTSET variable which will then be returned.
Teradata
IN -> Teradata_02.sql
REPLACE MACRO DEPOSITID (ID INT)AS(SELECT*FROM DEPOSIT WHERE ACCOUNTNO=:ID;);EXECUTE DEPOSITID(2);
CREATEORREPLACEPROCEDURE DEPOSITID (ID FLOAT)RETURNSTABLE ()LANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTEASCALLERAS$$BEGIN LET res RESULTSET := (SELECT*FROM DEPOSIT WHERE ACCOUNTNO=:ID);RETURNTABLE(res);END;$$;CALL DEPOSITID(2);
In Teradata, macros can return more than one result set from a single macro.
Snowflake Scripting procedures only allow one result set to be returned per procedure. To replicate Teradata behavior, when there are two or more result sets to return, they are stored in temporary tables. The Snowflake Scripting procedure will return an array containing the name of the temporary tables.
Teradata
IN -> Teradata_05.sql
REPLACE MACRO DEPOSITID (ID INT)AS(SELECT*FROM DEPOSIT WHERE ACCOUNTNO=4;SELECT*FROM DEPOSIT WHERE ACCOUNTNO=:ID;);EXECUTE DEPOSITID(2);