MSCEWI1020

CUSTOM UDF INSERTED.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Summary

There are several User-Defined Functions (UDF) provided by SnowConvert 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.

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.

IS_FOUND_UDF

This UDF is used to indicate if the last ran query returned any rows, if the last executed query returned 0 rows returns FALSE, if the last executed query returned 1 or more rows returns TRUE.

UDF Signature

CREATE OR REPLACE FUNCTION IS_FOUND_UDF() 
RETURNS BOOLEAN

• RETURN: Boolean value indicating if the last query ran returned any rows.

Input Code:

IF FOUND THEN

Output Code:

--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'IS_FOUND_UDF' INSERTED. **
IF (IS_FOUND_UDF()) THEN

Recommendations

• Check if the UDF Helpers folder is being created with files inside it.

• If you need more support, you can email us at [email protected]