Macros

Translation reference to convert Teradata MACRO statement to Snowflake

Description

Teradata's MACRO statement is translated to Snowflake MACRO syntax.

For more information on Teradata MACRO, check here.

Sample Source Patterns

Create Macro Transformation

Teradata

Macro

CREATE MACRO new_table (col1 INTEGER, col2 VARCHAR(12))
AS
(
	insert into table1 (col1, col2) values (:col1, :col2);
	select * from table1 where col1 = :col1;
);

Snowflake

JavaScript Macro

CREATE OR REPLACE PROCEDURE PUBLIC.PUBLIC.new_table (COL1 FLOAT, COL2 VARCHAR(12))
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
 	// REGION SnowConvert Helpers Code
	var HANDLE_NOTFOUND;
	var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, ACTIVITY_COUNT = 0, INTO, _OUTQUERIES = [], DYNAMIC_RESULTS = -1;
	var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
	var fixBind = function (arg) {
	   arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
	   return arg;
	};
	var EXEC = function (stmt,binds,noCatch,catchFunction,opts) {
	   try {
	      binds = binds ? binds.map(fixBind) : binds;
	      _RS = snowflake.createStatement({
	            sqlText : stmt,
	            binds : binds
	         });
	      _ROWS = _RS.execute();
	      ROW_COUNT = _RS.getRowCount();
	      ACTIVITY_COUNT = _RS.getNumRowsAffected();
	      HANDLE_NOTFOUND && HANDLE_NOTFOUND(_RS);
	      if (INTO) return {
	         INTO : function () {
	            return INTO();
	         }
	      };
	      if (_OUTQUERIES.length < DYNAMIC_RESULTS) _OUTQUERIES.push(_ROWS.getQueryId());
	      if (opts && opts.temp) return _ROWS.getQueryId();
	   } catch(error) {
	      MESSAGE_TEXT = error.message;
	      SQLCODE = error.code;
	      SQLSTATE = error.state;
	      var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;
	      if (catchFunction) catchFunction(error);
	      if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error); else throw new Error(msg);
	   }
	};
	var procname = `PUBLIC.new_table`;
	var temptable_prefix, tablelist = [];
	var INSERT_TEMP = function (query,parameters) {
	   if (!temptable_prefix) {
	      var sql_stmt = `select current_session() || '_' || to_varchar(current_timestamp, 'yyyymmddhh24missss')`;
	      var rs = snowflake.createStatement({
	         sqlText : sql_stmt,
	         binds : []
	      }).execute();
	      temptable_prefix = rs.next() && (procname + '_TEMP_' + rs.getColumnValue(1) + '_');
	   }
	   var tablename = temptable_prefix + tablelist.length;
	   tablelist.push(tablename);
	   var sql_stmt = `CREATE OR REPLACE TEMPORARY TABLE ${tablename} AS ${query}`;
	   snowflake.execute({
	      sqlText : sql_stmt,
	      binds : parameters
	   });
	   return tablename;
	};
	// END REGION
	
	// ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
	EXEC(`INSERT INTO PUBLIC.table1 (col1, col2) VALUES (:1, :2)`,[COL1,COL2]);
	INSERT_TEMP(`SELECT
	*
	FROM PUBLIC.table1 where col1 = :1`,[COL1]);
	return tablelist;
 
$$;

Known Issues

1. Macro transform to store procedure

The Teradata Macro is transformed to a stored procedure since Snowflake does not support Macros.

Related EWIs

No related EWIs.