MSCEWI1020
CUSTOM UDF INSERTED.
Last updated
CUSTOM UDF INSERTED.
Last updated
This is a deprecated version of the SnowConvert documentation, please visit the official site .
Low
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 can be found in "UDF Helpers" folder created in output path after migration has occurred.
When 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.
Teradata and Snowflake handle date time subtraction different, the syntax, returned type and precision are different.
Due to the differences mentioned above, it is necessary to use an UDF. When SnowConvert migrates a create table like the following
The output of the given input is
The UDF will return a VARCHAR equivalent to Teradata's interval.
Some operations may produce different day outputs with an error range of 1 unit.
JSON_DATA : Variant data.
REGEX_PATH: Regular expression literal to filter the variant data.
RETURN: The filtered JSON data.
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.
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.
This UDF is used to handle scenarios when there is an extract date-time part from an interval.
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.
Teradata and Snowflake differ in the parameters that each function respectively might receive and the return type.
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.
Because Snowflake does not handle intervals, it only supports dates and times. So this UDF comes to complement the Snowflake function EXTRACT.
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.
RETURN: Boolean value indicating if the last query ran returned any rows.
Check if the UDF Helpers folder is being created with files inside it.
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 . 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 . The data type returned by Snowflake when one of the functions named above is called is Integer representing the number of units.
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 . This point could give different output than Teradata's
This UDF is used to extract information of a snowflake variant object, just like in Teradata with (dot notation), but with regular expression instead.
Consider the warning described in the when extracting day from the interval.
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 and .
If you need more support, you can email us at