MSCEWI1020

CUSTOM UDF INSERTED.
Severity
Low
Summary
There are several User Defined Functions (UDF) provided by Mobilize.Net these UDFs are used to reproduce source language behaviors that are not supported by Snowflake, functionality and descriptions are detailed below.
UDFs Location
UDFs can be found in "UDF Helpers" folder created in output path after migration has occurred. 
DATE_TO_INT_UDF
​
When CAST function is used to cast a DATE type to a NUMERIC type. Because of this behavior is not supported in Snowflake, Mobilize.Net provides an UDF Helpers file, which is used to emulate this cast behaviors in the migrated code.
Code Example
Input Code:
CREATE TABLE SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR,
    CHAR_TYPE CHAR(11),
    INTEGER_TYPE INTEGER,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    TIME_TYPE TIME,
    PERIOD_TYPE PERIOD(DATE)
);
​
REPLACE VIEW SAMPLE_VIEW 
AS
SELECT
CAST(DATE_TYPE AS SMALLINT),
CAST(DATE_TYPE AS DECIMAL),
CAST(DATE_TYPE AS NUMBER),
CAST(DATE_TYPE AS FLOAT),
CAST(DATE_TYPE AS INTEGER)
FROM SAMPLE_TABLE;
Output Code:
CREATE TABLE PUBLIC.SAMPLE_TABLE
(
    VARCHAR_TYPE VARCHAR,
    CHAR_TYPE CHAR(11),
    INTEGER_TYPE INTEGER,
    DATE_TYPE DATE,
    TIMESTAMP_TYPE TIMESTAMP,
    TIME_TYPE TIME,
    PERIOD_TYPE VARCHAR(24) COMMENT 'PERIOD(DATE)' /*** MSC-WARNING - MSCEWI1036 - PERIOD DATA TYPE "PERIOD(DATE)" CONVERTED TO VARCHAR ***/
);
​
CREATE OR REPLACE VIEW PUBLIC.SAMPLE_VIEW
AS
SELECT
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE) /*** MSC-WARNING - MSCEWI2026 - CUSTOM UDF 'DATE_TO_INT_UDF' INSERTED. ***/,
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE) /*** MSC-WARNING - MSCEWI2026 - CUSTOM UDF 'DATE_TO_INT_UDF' INSERTED. ***/,
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE) /*** MSC-WARNING - MSCEWI2026 - CUSTOM UDF 'DATE_TO_INT_UDF' INSERTED. ***/,
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE) /*** MSC-WARNING - MSCEWI2026 - CUSTOM UDF 'DATE_TO_INT_UDF' INSERTED. ***/,
PUBLIC.DATE_TO_INT_UDF(DATE_TYPE) /*** MSC-WARNING - MSCEWI2026 - CUSTOM UDF 'DATE_TO_INT_UDF' INSERTED. ***/
FROM PUBLIC.SAMPLE_TABLE;
TIMESTAMP_DIFFERENCE UDF
​
1-Differences between Teradata and Snowflake date time subtraction
Teradata and Snowflake handle date time subtraction different, the syntax, returned type and precision are different.
Syntax: The syntax used to handle DATE, TIMESTAMP and TIME subtraction operation in Teradata is with minus sign and interval used to specify the precision and format of operation result. For further details of syntax visit the following link https://docs.teradata.com/r/w19R4KsuHIiEqyxz0WYfgA/7kLLsWrP0kHxbk3iida0mA. In Snowflake subtraction of DATE, TIMESTAMP and TIME are handled by three different functions, DATEDIFF, TIMESTAMPDIFF, and TIMEDIFF respectively, these operations takes 3 parameters, the dates, and the date part to be returned. Nevertheless, DATEDIFF function will work with TIMESTAMP and TIME types  The minus sign can be used if the operation is with DATE type, in this case, the default return type is the number of days.
Return Type: In general the returned data type of Teradata are a different kind of Intervals. Intervals reference can be found at https://www.docs.teradata.com/r/T5QsmcznbJo1bHmZT2KnFw/z~5iW7rYVstcmNYbd6Dsjg. The data type returned by Snowflake when one of the functions named above is called is Integer representing the number of units. https://docs.snowflake.com/en/sql-reference/functions/datediff.html​
Rounding: To see when DATEDIFF (used in some cases UDF) uses the entire date or if it disregards of other parts in Snowflake visit the following link https://docs.snowflake.com/en/sql-reference/functions/datediff.html#usage-notes. This point could give different output than Teradata's
2-Why is UDF used to date time subtraction?
Due to the differences mentioned above, it is necessary to use an UDF. When SnowConvert migrates a create table like the following 
create table ftable21( col1 interval day(4));
The output of the given input is
CREATE TABLE db1.PUBLIC.ftable21 (
col1 VARCHAR(20) COMMENT 'interval day(4)' /*** MSC-WARNING - MSCEWI1036 - INTERVAL DATA TYPE "interval day(4)" CONVERTED TO VARCHAR ***/);
 The UDF will return a VARCHAR equivalent to Teradata's interval.
Warning
Some operations may produce different day outputs with an error range of 1 unit. 
Code Sample 
Input Code:
select start_ts,end_ts, start_ts - end_ts second(4) from DB1.dummy_ts order by start_ts;
Output Code:
SELECT start_ts,
end_ts,
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TIMESTAMP_DIFFERENCE' INSERTED. ***/
PUBLIC.TIMESTAMP_DIFFERENCE( start_ts, end_ts, 'SECOND')
FROM DB1.PUBLIC.dummy_ts order by start_ts;
JSON_REGEX_QUERY UDF
This UDF is used to extract information of a snowflake variant object, just like in Teradata with JSON Entity Reference (dot notation), but with regular expression instead.
UDF Signature 
FUNCTION PUBLIC.JSON_REGEX_QUERY_UDF(JSON_DATA VARIANT, REGEX_PATH VARCHAR)
RETURNS VARIANT
JSON_DATA :  Variant data.
REGEX_PATH: Regular expression literal to filter the variant data.
RETURN: The filtered JSON data.
Code Sample
Input Code:
SELECT CAST(FORMULA AS JSON(2000))..name FROM variantTest;
​
SELECT js.jsonColumn..schools[0,name,location].*[0:20:5] from TABLEJ js
Output Code:
SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_REGEX_QUERY_UDF' INSERTED. **
PUBLIC.JSON_REGEX_QUERY_UDF( CAST(FORMULA AS VARIANT), '.*name')
FROM DATAWAREHOUSE.PUBLIC.variantTest;
​
SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_REGEX_QUERY_UDF' INSERTED. **
PUBLIC.JSON_REGEX_QUERY_UDF( jsonColumn, '.*schools(\\[(0)\\]|(name|location))(\\[\\d+\\]|\\.\\w+)\\[(0|5|10|15)\\]')
FROM DATAWAREHOUSE.PUBLIC.TABLEJ js;
Differences between Teradata JSON Entity Reference (dot notation ) and Snowflake JSON query method.
Teradata and Snowflake differ in the methods used for traverse JSON data. In this case Teradata use a way based on JavaScript that use dot notation, array indexing, and other especial operators like wildcard access or double dot. On the other hand, snowflake doesn't support these operators, and it only can access members and arrays. 
Why is this UDF used to extract JSON data?
Because Snowflake does not handle JSON dot notation, so to this UDF comes to simulate the Teradata JSON notation, that have some complex operators and array indexing for example the recursive descent operator.
General Recommendations
EXTRACT_FROM_INTERVAL UDF
This UDF is used to handle scenarios when there is an extract date-time part from an interval.
UDF Signature 
FUNCTION PUBLIC.EXTRACT_FROM_INTERVAL_UDF
(
  INTERVAL_DATA VARCHAR,
  INPUT_PART VARCHAR, 
  REQUEST_PART VARCHAR
 )
RETURNS NUMBER
INTERVAL_DATA:  Formatted varchar returned by TIMESTRAMP_DIFERENCE UDF.
INPUT_PART: Formatted varchar, is the original requested part (same as TIMESTAMP_DIFERENCE INPUT_PART) and must be one of these:
'DAY TO HOUR'
'DAT TO MINUTE'
'DAY TO SECOND'
'DAY TO MINUTE'
'HOUR TO MINUTE'
'HOUR TO SECOND'
'MINUTE TO SECOND'
REQUEST_PART: Value to be extracted, the request part should be contained in the input part interval, must be 'DAY', 'HOUR', 'MINUTE' or 'SECOND'.
RETURN: The extracted value of data type number.
Code Sample
Input Code:
select start_ts,end_ts, extract(day from ((start_ts - end_ts) day(4) to hour)) from DB1.dummy_ts order by start_ts;
​
select start_ts,end_ts, extract(MINUTE from ((start_ts - end_ts) day(4) to second)) from DB1.dummy_ts order by start_ts; 
​
SELECT EXTRACT (MINUTE FROM '23:50:17.3');
Output Code:
SELECT
start_ts,
end_ts,
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'EXTRACT_FROM_INTERVAL_UDF' INSERTED. ***/
PUBLIC.EXTRACT_FROM_INTERVAL_UDF( (/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TIMESTAMP_DIFFERENCE_UDF' INSERTED. ***/
PUBLIC.TIMESTAMP_DIFFERENCE_UDF(start_ts, end_ts, 'DAY TO HOUR')), 'DAY TO HOUR', 'DAY')
FROM DB1.PUBLIC.dummy_ts order by start_ts;
​
SELECT
start_ts,
end_ts,
/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'EXTRACT_FROM_INTERVAL_UDF' INSERTED. ***/
PUBLIC.EXTRACT_FROM_INTERVAL_UDF( (/*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'TIMESTAMP_DIFFERENCE_UDF' INSERTED. ***/
PUBLIC.TIMESTAMP_DIFFERENCE_UDF(start_ts, end_ts, 'DAY TO SECOND')), 'DAY TO SECOND', 'MINUTE')
FROM DB1.PUBLIC.dummy_ts order by start_ts;
​
SELECT
EXTRACT (MINUTE FROM '23:50:17.3');
Consider the warning described in the EWI warning when extracting day from the interval.
Differences between Teradata and Snowflake date-time extraction
Teradata and Snowflake differ in the parameters that each function respectively might receive and the return type.
Parameters: The main difference between Teradata and Snowflake extract function is that Snowflake does not handle intervals, it only supports dates and times. For further information on this, read Snowflake's EXTRACT function documentation and Teradata's EXTRACT function documentation.
Return type: Teradata EXTRACT function returns an integer or decimal(8, 2) value representing the part requested and Snowflake returns a number value representing a date-time of the part requested.
Teradata and Snowflake differ in the parameters that each function respectively might receive and the return type. 
Why is this UDF used to extract from interval?
Because Snowflake does not handle intervals, it only supports dates and times. So this UDF comes to complement the Snowflake function EXTRACT. 
General Recommendations
Check if the UDF Helpers folder is being created with files inside it.
For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.
MSCEWI1019
MSCEWI1021
Last modified 6mo ago