Links

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.

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.