SnowConvert AI - Teradata - Built-in Functions¶

This page provides a description of the translation for the built-in functions in Teradata to Snowflake

Aggregate Functions¶

See Aggregate functions​

Arithmetic, Trigonometric, Hyperbolic Operators/Functions¶

See Arithmetic, Trigonometric, Hyperbolic Operators/Functions​

Attribute Functions¶

See Attribute functions

Bit/Byte Manipulation Functions¶

See Bit/Byte functions

Built-In (System Functions)¶

See Built-In Functions

Business Calendars¶

See Business Calendars

Calendar Functions¶

See Calendar Functions

Case Functions¶

See case functions

Comparison Functions¶

See comparison functions

Data type conversions¶

See Data Type Conversions

Data Type Conversion Functions¶

See Data Type Conversion Functions

DateTime and Interval functions¶

See DateTime and Interval Functions and Expressions

Hash functions¶

See Hash functions

JSON functions¶

See JSON documentation

Null-Handling functions¶

See Null-Handling functions

Ordered Analytical/Window Aggregate functions¶

See Window functions

Period functions and operators¶

See Period Functions and Operators

Query band functions¶

See Query band functions

Regex functions¶

See Regex functions

String operators and functions¶

See String operators and functions​​​

St_Point functions¶

See St_Point functions

Table operators¶

See Table Operators

XML functions¶

See XML functions

Extensibility UDFs¶

This section contains UDFs and other extensibility functions that are not offered as system built-in functions by Teradata but are transformed by SnowConvert AI

Notes¶

Architecture differences between Teradata and Snowflake¶

Teradata has a shared-nothing architecture with Access Module Processors (AMP) where each AMP manages their own share of disk storage and is accessed through hashing when doing queries. To take advantage of parallelism the stored information should be evenly distributed among AMPs and to do this Teradata offers a group of hash-related functions that can be used to determine how good the actual primary indexes are.

On the other hand, Snowflake architecture is different, and it manages how the data is stored on its own, meaning users do not need to worry about optimizing their data distribution.

Ending bound constants (UNTIL_CHANGED and UNTIL_CLOSED)¶

Both UNTIL_CHANGED and UNTIL_CLOSED are Teradata constants that represent an undefined ending bound for periods. Internally, these constants are represented as the maximum value a timestamp can have i.e ’9999-12-31 23:59:59.999999’. During the migration of the PERIOD function, the ending bound is checked if present to determine if it is one of these constants and to replace it with varchar of value ’9999-12-31 23:59:59.999999’ in case it is, Snowflake then casts the varchar to date or timestamp depending on the type of the beginning bound when calling PERIOD___UDF.

Implicit conversion¶

Some Teradata string functions like INDEX or POSITION accept non-string data types and implicitly convert them to string, this can cause inconsistencies in the results of those functions between Teradata and Snowflake. For example, the following Teradata code:

 SELECT INDEX(35, '5');

Returns 4, while the CHARINDEX equivalent in Snowflake:

 SELECT CHARINDEX('5', 35);

Returns 2, this happens because Teradata has its own default formats which are used during implicit conversion. In the above example, Teradata interprets the numeric constant 35 as BYTEINT and uses BYTEINT default format'-999' for the implicit conversion to string, causing the converted value to be ' 35'. On the other hand, Snowflake uses its own default formats, creating inconsistencies in the result.

To solve this, the following changes are done to those function parameters:

• If the parameter does not have a cast with format, then a Snowflake TO_VARCHAR function with the default Teradata format equivalent in Snowflake is added instead.
• If the parameter does have a cast with format, then the format is converted to its Snowflake equivalent and theTO_VARCHARfunction is added.
  • As a side note, Teradata ignores the sign of a number if it is not explicitly put inside a format, while Snowflake always adds spaces to insert the sign even when not specified, for those cases a check is done to see if the sign was specified and to remove it from the Snowflake string in case it was not.

After these changes, the resulting code would be:

 SELECT CHARINDEX( '5', TO_VARCHAR(35, 'MI999'));

Which returns 4, the same as the Teradata code.

Known Issues ¶

No issues were found.

Related EWIs ¶

No related EWIs.

COALESCE¶

Description¶

The coalesce function is used to return the first non-null element in a list. For more information check COALESCE.

COALESCE(element_1, element_2 [, element_3, ..., element_n])

Both Teradata and Snowflake COALESCE functions allow mixing numeric with string and date with timestamp parameters. However, they handle these two cases differently:

• Numeric along with string parameters: Teradata converts all numeric parameters to varchar while Snowflake does the opposite
• Timestamp along with date parameters: Teradata converts all timestamps to date while Snowflake does the opposite

To ensure functional equivalence in the first case, all numeric parameters are cast tostringusingto_varcharfunction, this takes the format of the numbers into account. In the second case, all timestamps are casted to date using to_date, Teradata ignores the format of timestamps when casting them so it is removed during transformation.

Sample Source Patterns¶

Numeric mixed with string parameters¶

Teradata¶

Query

 SELECT COALESCE(125, 'hello', cast(850 as format '-999'));

Result

COLUMN1|
-------+
125    |

Snowflake¶

Query

SELECT
 COALESCE(TO_VARCHAR(125), 'hello', TO_VARCHAR(850, '9000'));

Result

COLUMN1|
-------+
125    |

Timestamp mixed with date parameters¶

Teradata¶

Query

SELECT COALESCE(cast(TIMESTAMP '2021-09-14 10:14:59' as format 'HH:MI:SSBDD-MM-YYYY'), current_date);

Result

COLUMN1    |
-----------+
2021-09-14 |

Snowflake¶

Query

SELECT
 COALESCE(TO_DATE(TIMESTAMP '2021-09-14 10:14:59' !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'HH:MI:SSBDD-MM-YYYY' NOT SUPPORTED. ***/!!!), CURRENT_DATE());

Result

COLUMN1    |
-----------+
2021-09-14 |

Known Issues¶

No known issues_._

Related EWIs¶

• SSC-EWI-TD0025: Output format not supported.

CURRENT_TIMESTAMP¶

Severity¶

Low

Description¶

Fractional seconds are only displayed if it is explicitly set in the TIME_OUTPUT_FORMAT session parameter.

Input code:¶

SELECT current_timestamp(4) at local;

Output code:¶

SELECT
CURRENT_TIMESTAMP(4);

Recommendations¶

• Check if the TIME_OUTPUT___FORMAT session parameter is set to get the behavior that you want.
• If you need more support, you can email us at snowconvert-support@snowflake.com

Known Issues ¶

No issues were found.

Related EWIs ¶

No related EWIs.

DAYNUMBER_OF_MONTH¶

Description¶

Returns the number of days elapsed from the beginning of the month to the given date. For more information check DAYNUMBER_OF_MONTH.

DAYNUMBER_OF_MONTH(expression [, calendar_name])

Both Teradata and Snowflake handle the DAYNUMBER_OF_MONTH function in the same way, except in one case:

• The ISO calendar: An ISO month has 4 or 5 complete weeks. For more information check About ISO Computation.

To ensure functional equivalence, a user-defined function (UDF) is added for the ISO calendar case.

Sample Source Patterns¶

Teradata¶

Query

SELECT
    DAYNUMBER_OF_MONTH (DATE'2022-12-22'),
    DAYNUMBER_OF_MONTH (DATE'2022-12-22', NULL),
    DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'Teradata'),
    DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'COMPATIBLE');

Result

COLUMN1|COLUMN2|COLUMN3|COLUMN4|
-------+-------+-------+-------+
22     |22     |22     |22     |

Snowflake¶

Query

SELECT
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22');

Result

COLUMN1|COLUMN2|COLUMN3|COLUMN4|
-------+-------+-------+-------+
22     |22     |22     |22     |

ISO calendar¶

Teradata¶

Query

SELECT DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'ISO');

Result

COLUMN1|
-------+
25     |

Snowflake¶

Query

SELECT
PUBLIC.DAYNUMBER_OF_MONTH_UDF(DATE'2022-12-22');

Result

COLUMN1|
-------+
25     |

Known Issues ¶

No issues were found.

Related EWIs ¶

No related EWIs.

FROM_BYTES¶

Translation specification for transforming the TO_CHAR function into an equivalent function concatenation in Snowflake

Description¶

The FROM_BYTES function encodes a sequence of bits into a sequence of characters representing its encoding. For more information check FROM_BYTES(Encoding).

Snowflake does not have support for FROM_BYTES function, however, some workarounds can be done for the most common occurrences of this function.

Sample Source Patterns¶

Teradata¶

Query¶

 SELECT
FROM_BYTES('5A1B'XB, 'base10'), --returns '23067'
FROM_BYTES('5A3F'XB, 'ASCII'), --returns 'Z\ESC '
FROM_BYTES('5A1B'XB, 'base16'); -- returns '5A1B'

Result¶

COLUMN1    | COLUMN2    | COLUMN3 |
-----------+------------+---------+
23067      |  Z\ESC     | 5A1B    |

Snowflake¶

Query¶

 SELECT
--returns '23067'
TO_NUMBER('5A1B', 'XXXX'),
--returns 'Z\ESC '
!!!RESOLVE EWI!!! /*** SSC-EWI-0031 - FROM_BYTES FUNCTION NOT SUPPORTED ***/!!!
FROM_BYTES(TO_BINARY('5A3F'), 'ASCII'),
TO_BINARY('5A1B', 'HEX'); -- returns '5A1B'

Result¶

COLUMN1    | COLUMN2    | COLUMN3 |
-----------+------------+---------+
23067      |  Z\ESC     | 5A1B    |

Known Issues¶

1. TO_NUMBER format parameter must match with the digits on the input string.
2. There is no functional equivalent built-in function for FROM_BYTES when encoding to ANSI

Related EWIs¶

1. SSC-EWI-0031: FUNCTION NOT SUPPORTED

GETQUERYBANDVALUE¶

Translation specification for the transformation of GetQueryBandValue to Snowflake

Description¶

The GetQueryBandValue function searches a name key inside of the query band and returns its associated value if present. It can be used to search inside the transaction, session, profile, or any of the key-value pairs of the query band.

For more information on this function check GetQueryBandValue in the Teradata documentation.

[SYSLIB.]GetQueryBandValue([QueryBandIn,] SearchType, Name);

Sample Source Patterns¶

Setup data¶

Teradata¶

Query¶

 SET QUERY_BAND = 'hola=hello;adios=bye;' FOR SESSION;

Snowflake¶

Query¶

 ALTER SESSION SET QUERY_TAG = 'hola=hello;adios=bye;';

GetQueryBandValue with QueryBandIn parameter¶

Teradata¶

Query¶

 SELECT
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'account') as Example1,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'account') as Example2,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 2, 'account') as Example3,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 3, 'account') as Example4,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'role') as Example5,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'role') as Example6;

Result¶

+----------+----------+----------+----------+----------+----------+
| EXAMPLE1 | EXAMPLE2 | EXAMPLE3 | EXAMPLE4 | EXAMPLE5 | EXAMPLE6 |
+----------+----------+----------+----------+----------+----------+
| Mark200  | Mark200  | SaraDB   | Peter3   | DbAdmin  |          |
+----------+----------+----------+----------+----------+----------+

Snowflake¶

Query¶

 SELECT
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'account') as Example1,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'account') as Example2,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 2, 'account') as Example3,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 3, 'account') as Example4,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'role') as Example5,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'role') as Example6;

Result¶

+----------+----------+----------+----------+----------+----------+
| EXAMPLE1 | EXAMPLE2 | EXAMPLE3 | EXAMPLE4 | EXAMPLE5 | EXAMPLE6 |
+----------+----------+----------+----------+----------+----------+
| Mark200  | Mark200  | SaraDB   | Peter3   | DbAdmin  |          |
+----------+----------+----------+----------+----------+----------+

GetQueryBandValue without QueryBandIn parameter¶

Teradata¶

Query¶

 SELECT
GETQUERYBANDVALUE(2, 'hola') as Example1,
GETQUERYBANDVALUE(2, 'adios') as Example2;

Result¶

+----------+----------+
| EXAMPLE1 | EXAMPLE2 |
+----------+----------+
| hello    | bye      |
+----------+----------+

Snowflake¶

Query¶

 SELECT
GETQUERYBANDVALUE_UDF('hola') as Example1,
GETQUERYBANDVALUE_UDF('adios') as Example2;

Result¶

+----------+----------+
| EXAMPLE1 | EXAMPLE2 |
+----------+----------+
| hello    | bye      |
+----------+----------+

Known Issues¶

1. GetQueryBandValue without QueryBandIn parameter only supported for session

Teradata allows defining query bands at transaction, session or profile levels. If GetQueryBandValue is called without specifying an input query band Teradata will automatically check the transaction, session or profile query bands depending on the value of the SearchType parameter.

In Snowflake the closest equivalent to query bands are query tags, which can be specified for session, user and account.

Due to these differences, the implementation of GetQueryBandValue without QueryBandIn parameter only considers the session query tag and may not work as expected for other search types.

Related EWIs¶

No related EWIs.

HASHBUCKET¶

Description¶

HASHBUCKET is a Teradata system function used to determine the hash bucket number for data distribution across Access Module Processors (AMPs). It is commonly used in combination with HASHROW to evaluate how evenly data is distributed across AMPs based on the primary index. For more information, see HASHBUCKET.

HASHBUCKET(HASHROW(column1 [, column2, ...]))
HASHBUCKET()
HASHBUCKET(byte_expression)

HASHBUCKET is not translated to Snowflake because it is specific to Teradata’s shared-nothing AMP architecture. While Snowflake provides a HASH function, the two are not functionally equivalent:

• Different algorithms: Teradata and Snowflake use completely different hashing algorithms.
• Different value ranges: HASHBUCKET returns values in the range 0–1,048,575, while Snowflake HASH returns signed 64-bit integers (a far larger and different value range).
• Different NULL handling: HASHBUCKET(HASHROW(NULL)) returns 0, while Snowflake HASH(NULL) returns a non-NULL hash value.

Sample Source Patterns¶

HASHBUCKET with HASHROW (single column)¶

Teradata¶

 SELECT HASHBUCKET(HASHROW(col1)) FROM my_table;

Snowflake¶

 SELECT
   !!!RESOLVE EWI!!! /*** SSC-EWI-0031 - HASHBUCKET FUNCTION NOT SUPPORTED ***/!!!
   HASHBUCKET(
              !!!RESOLVE EWI!!! /*** SSC-EWI-0031 - HASHROW FUNCTION NOT SUPPORTED ***/!!!
              HASHROW(col1))
 FROM
   my_table;

HASHBUCKET with HASHROW (multiple columns)¶

Teradata¶

 SELECT HASHBUCKET(HASHROW(a, b, c)) FROM my_table;

Snowflake¶

 SELECT
   !!!RESOLVE EWI!!! /*** SSC-EWI-0031 - HASHBUCKET FUNCTION NOT SUPPORTED ***/!!!
   HASHBUCKET(
              !!!RESOLVE EWI!!! /*** SSC-EWI-0031 - HASHROW FUNCTION NOT SUPPORTED ***/!!!
              HASHROW(a, b, c))
 FROM
   my_table;

HASHBUCKET with no arguments¶

Teradata¶

 SELECT HASHBUCKET() + 1 FROM my_table;

Snowflake¶

 SELECT
   !!!RESOLVE EWI!!! /*** SSC-EWI-0031 - HASHBUCKET FUNCTION NOT SUPPORTED ***/!!!
   HASHBUCKET() + 1
 FROM
   my_table;

Recommendations¶

The appropriate replacement depends on how HASHBUCKET is used in the source code:

• Data distribution or skew analysis — Teradata uses HASHBUCKET(HASHROW(col)) to evaluate how evenly data is distributed across AMPs. In Snowflake, data distribution is managed automatically through micro-partitioning. To analyze clustering quality, use SYSTEM$CLUSTERING_INFORMATION instead.

• Custom bucketing or partitioning logic — If the source code uses hash bucket numbers to route or partition data, consider using Snowflake’s HASH function with MOD() to distribute values across a fixed number of buckets: ABS(MOD(HASH(col), num_buckets)). Note that this will not produce the same bucket assignments as Teradata.

• Max bucket number constant — HASHBUCKET() with no arguments returns the maximum hash bucket number (1,048,575) in Teradata. If this value is used as a constant in calculations, replace it with the literal value 1048575.

• Code that needs any hash value — If the specific bucket numbers do not matter and only the distribution property is needed (e.g., sampling, random assignment), Snowflake’s HASH function can be used directly.

• Code that depends on exact Teradata bucket values — If the logic relies on specific bucket numbers (e.g., hardcoded ranges, lookups against pre-computed buckets), there is no automated migration path. The hashing algorithm is proprietary to Teradata and cannot be reproduced in Snowflake. This code requires manual redesign.

Known Issues¶

No known issues.

Related EWIs¶

• SSC-EWI-0031: Function not supported.

HASHROW¶

Description¶

HASHROW is a Teradata system function that generates a hash value for one or more columns. It is typically used as the argument to HASHBUCKET for data distribution analysis. Like HASHBUCKET, HASHROW is tied to Teradata’s AMP architecture and has no Snowflake equivalent. For more information, see Hash functions.

See HASHBUCKET for conversion examples and recommendations.

Related EWIs¶

• SSC-EWI-0031: Function not supported.

HASHAMP¶

Description¶

HASHAMP is a Teradata system function that returns the AMP number associated with a hash bucket value. Like HASHBUCKET and HASHROW, it is tied to Teradata’s AMP architecture and has no Snowflake equivalent. For more information, see Hash functions.

See HASHBUCKET for conversion examples and recommendations.

Related EWIs¶

• SSC-EWI-0031: Function not supported.

HASHBAKAMP¶

Description¶

HASHBAKAMP is a Teradata system function that returns the fallback AMP number for a given hash bucket value. Like HASHBUCKET and HASHROW, it is tied to Teradata’s AMP architecture and has no Snowflake equivalent. For more information, see Hash functions.

See HASHBUCKET for conversion examples and recommendations.

Related EWIs¶

• SSC-EWI-0031: Function not supported.

JSON_CHECK¶

Description¶

The JSON_CHECK function checks a string for valid JSON.

For more information, see the Teradata JSON_CHECK documentation.

[TD_SYSFNLIB.]JSON_CHECK(string_expr);

Sample Source Pattern¶

Basic Source Pattern¶

Teradata¶

Query

SELECT JSON_CHECK('{"key": "value"}');

Snowflake Scripting¶

Query

SELECT
IFNULL(CHECK_JSON('{"key": "value"}'), 'OK');

JSON_CHECK inside CASE transformation¶

Teradata¶

Query

SELECT CASE WHEN JSON_CHECK('{}') = 'OK' then 'OKK' ELSE 'NOT OK' END;

Snowflake Scripting¶

Query

SELECT
CASE
WHEN UPPER(RTRIM(IFNULL(CHECK_JSON('{}'), 'OK'))) = UPPER(RTRIM('OK'))
THEN 'OKK' ELSE 'NOT OK'
END;

Known Issues ¶

No issues were found.

Related EWIs ¶

No related EWIs.

JSON_EXTRACT¶

Translation reference to convert the Teradata functions JSONExtractValue, JSONExtractLargeValue and JSONExtract to Snowflake Scripting.

Description¶

As per Teradata’s documentation, these functions use the JSONPath Query Syntax to request information about a portion of a JSON instance. The entity desired can be any portion of a JSON instance, such as a name/value pair, an object, an array, an array element, or a value.

For more information, see the Teradata JSONExtract function comparison.

JSON_expr.JSONExtractValue(JSONPath_expr);

JSON_expr.JSONExtractLargeValue(JSONPath_expr);

JSON_expr.JSONExtract(JSONPath_expr);

The JSON_EXTRACT_UDF is a Snowflake implementation of the JSONPath specification that uses a modified version of the original JavaScript implementation developed by Stefan Goessner.

Sample Source Pattern¶

Teradata¶

Query¶

 SELECT
    Store.JSONExtract('$..author') as AllAuthors,
    Store.JSONExtractValue('$..book[2].title') as ThirdBookTitle,
    Store.JSONExtractLargeValue('$..book[2].price') as ThirdBookPrice
FROM BookStores;

Snowflake Scripting¶

Query¶

 SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors,
    JSON_EXTRACT_UDF(Store, '$..book[2].title', TRUE) as ThirdBookTitle,
    JSON_EXTRACT_UDF(Store, '$..book[2].price', TRUE) as ThirdBookPrice
    FROM
    BookStores;

Known Issues¶

1. Elements inside JSONs may not retain their original order.¶

Elements inside a JSON are ordered by their keys when inserted in a table. Thus, the query results might differ. However, this does not affect the order of arrays inside the JSON.

For example, if the original JSON is:

{
  "firstName": "Peter",
  "lastName": "Andre",
  "age": 31,
  "cities": ["Los Angeles", "Lima", "Buenos Aires"]
}

Using the Snowflake PARSE_JSON() that interprets an input string as a JSON document, producing a VARIANT value. The inserted JSON will be:

{
  "age": 31,
  "cities": ["Los Angeles", "Lima", "Buenos Aires"],
  "firstName": "Peter",
  "lastName": "Andre"
}

Note how “age” is now the first element. However, the array of “cities” maintains its original order.

Related EWIs¶

No related EWIs.

JSON_TABLE¶

Translation specification for the transformation of JSON_TABLE into a equivalent query in Snowflake

Description¶

Creates a table based on the contents of a JSON document. See JSON_TABLE documentation.

[TD_SYSFNLIB.]JSON_TABLE(
  ON (json_documents_retrieving_expr)
  USING
      ROWEXPR (row_expr_literal)
      COLEXPR (column_expr_literal)
  [AS] correlation_name [(column_name [,...])]
)

The conversion of JSON_TABLE has the considerations shown below:

• ROW_NUMBER() is an equivalent of ordinal columns in Snowflake.
• In Teradata, the second column of JSON_TABLE must be JSON type because the generated columns replace the second column, for that reason, SnowConvert AI assumes that the column has the right type, and uses it for the transformation.

Sample Source Patterns¶

Setup data¶

Teradata¶

Query¶

 create table myJsonTable(
 col1 integer,
 col2 JSON(1000)
 );


insert into myJsonTable values(1,
new json('{
"name": "Matt",
"age" : 30,
"songs" : [
	{"name" : "Late night", "genre" : "Jazz"},
	{"name" : "Wake up", "genre" : "Rock"},
	{"name" : "Who am I", "genre" : "Rock"},
	{"name" : "Raining", "genre" : "Blues"}
]
}'));

Snowflake¶

Query¶

 CREATE OR REPLACE TABLE myJsonTable (
 col1 integer,
 col2 VARIANT
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO myJsonTable
VALUES (1, TO_JSON(PARSE_JSON('{
"name": "Matt",
"age" : 30,
"songs" : [
	{"name" : "Late night", "genre" : "Jazz"},
	{"name" : "Wake up", "genre" : "Rock"},
	{"name" : "Who am I", "genre" : "Rock"},
	{"name" : "Raining", "genre" : "Blues"}
]
}')));

Pattern code 1¶

Teradata¶

Query¶

 SELECT * FROM
JSON_TABLE(ON (SELECT COL1, COL2 FROM myJsonTable WHERE col1 = 1)
USING rowexpr('$.songs[*]')
colexpr('[ {"jsonpath" : "$.name",
            "type" : "CHAR(20)"},
            {"jsonpath" : "$.genre",
             "type" : "VARCHAR(20)"}]')) AS JT(ID, "Song name", Genre);

Result¶

ID | Song name  | Genre |
---+------------+-------+
1  | Late night | Jazz  |
---+------------+-------+
1  | Wake up    | Rock  |
---+------------+-------+
1  | Who am I   | Rock  |
---+------------+-------+
1  | Raining    | Blues |

Snowflake¶

Query¶

 SELECT
* FROM
(
SELECT
COL1 AS ID,
rowexpr.value:name :: CHAR(20) AS "Song name",
rowexpr.value:genre :: VARCHAR(20) AS Genre
FROM
myJsonTable,
TABLE(FLATTEN(INPUT => COL2:songs)) rowexpr
WHERE col1 = 1
) JT;

Result¶

ID | Song name  | Genre |
---+------------+-------+
1  | Late night | Jazz  |
---+------------+-------+
1  | Wake up    | Rock  |
---+------------+-------+
1  | Who am I   | Rock  |
---+------------+-------+
1  | Raining    | Blues |

Known Issues¶

1. The JSON path in COLEXPR can not have multiple asterisk accesses

The columns JSON path cannot have multiple lists with asterisk access, for example: $.Names[*].FullNames[*]. On the other hand, the JSON path of ROWEXP can have it.

2. JSON structure defined in the COLEXPR literal must be a valid JSON

When it is not the case the user will be warned about the JSON being badly formed.

Related EWIs¶

No related EWIs.

NEW JSON¶

Description¶

Allocates a new instance of a JSON datatype. For more information check NEW JSON Constructor Expression.

NEW JSON ( [ JSON_string_spec | JSON_binary_data_spec ] )

JSON_string_spec := JSON_String_literal [, { LATIN | UNICODE | BSON | UBJSON } ]

JSON_binary_data_spec := JSON_binary_literal [, { BSON | UBJSON } ]

The second parameter of the NEW JSON function is always omitted by SnowConvert AI since Snowflake works only with UTF-8.

Sample Source Patterns¶

NEW JSON with string data¶

Teradata¶

Query

SELECT NEW JSON ('{"name" : "cameron", "age" : 24}'),
NEW JSON ('{"name" : "cameron", "age" : 24}', LATIN);

Result

Snowflake¶

Query

SELECT
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}')),
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0039 - INPUT FORMAT 'LATIN' NOT SUPPORTED ***/!!!
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}'));

Result

Known Issues¶

1. The second parameter is not supported

The second parameter of the function used to specify the format of the resulting JSON is not supported because Snowflake only supports UTF-8, this may result in functional differences for some uses of the function.

2. JSON with BINARY data is not supported

Snowflake does not support parsing binary data to create a JSON value, the user will be warned when SnowConvert AI finds a NEW JSON with binary data.

Related EWIs¶

1. SSC-EWI-TD0039: Input format not supported.

NVP¶

Description¶

Extracts the value of the key-value pair where the key matches the nth occurrence of the specified name to search. See NVP.

[TD_SYSFNLIB.] NVP (
in_string,
name_to_search
[, name_delimiters ]
[, value_delimiters ]
[, occurrence ]
)

Sample Source Patterns¶

NVP basic case¶

Teradata¶

Query

SELECT
NVP('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1),
NVP('Hello=bye|name=Lucas|Hello=world!', 'Hello', '|', '=', 2),
NVP('Player=Mario$Game&Tenis%Player/Susana$Game=Chess', 'Player', '% $', '= & /', 2);

Result

COLUMN1        | COLUMN2 | COLUMN3 |
---------------+---------+---------+
orange chicken | world!  | Susana  |

Snowflake¶

Query

SELECT
PUBLIC.NVP_UDF('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1),
PUBLIC.NVP_UDF('Hello=bye|name=Lucas|Hello=world!', 'Hello', '|', '=', 2),
PUBLIC.NVP_UDF('Player=Mario$Game&Tenis%Player/Susana$Game=Chess', 'Player', '% $', '= & /', 2);

Result

COLUMN1        | COLUMN2 | COLUMN3 |
---------------+---------+---------+
orange chicken | world!  | Susana  |

NVP with optional parameters ignored¶

Teradata¶

Query

SELECT
NVP('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color'),
NVP('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', 2),
NVP('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=');

Result

COLUMN1 | COLUMN2 | COLUMN3     |
--------+---------+-------------+
Green   | Blue    | Los Angeles |

Snowflake¶

Query

SELECT
    PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 1),
    PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 2),
    PUBLIC.NVP_UDF('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=', 1);

Result

COLUMN1 | COLUMN2 | COLUMN3     |
--------+---------+-------------+
Green   | Blue    | Los Angeles |

NVP with spaces in delimiters¶

Teradata¶

Query

SELECT
NVP('store = whole foods&&store: ?Bristol farms','store', '&&', '\ =\  :\ ?', 2),
NVP('Hello = bye|name = Lucas|Hello = world!', 'Hello', '|', '\ =\ ', 2);

Result

COLUMN1       | COLUMN2 |
--------------+---------+
Bristol farms | world!  |

Snowflake¶

Query

SELECT
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms', 'store', '&&', '\\ =\\  :\\ ?', 2),
PUBLIC.NVP_UDF('Hello = bye|name = Lucas|Hello = world!', 'Hello', '|', '\\ =\\ ', 2);

Result

COLUMN1       | COLUMN2 |
--------------+---------+
Bristol farms | world!  |

NVP with non-literal delimiters¶

Teradata¶

Query

SELECT NVP('store = whole foods&&store: ?Bristol farms','store', '&&', valueDelimiter, 2);

Snowflake¶

Query

SELECT
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms', 'store', '&&', valueDelimiter, 2) /*** SSC-FDM-TD0008 - WHEN NVP_UDF FOURTH PARAMETER IS NON-LITERAL AND IT CONTAINS A BACKSLASH, THAT BACKSLASH NEEDS TO BE ESCAPED ***/;

Known Issues¶

1. Delimiters with spaces (\ ) need to have the backslash escaped in Snowflake

In Teradata, delimiters including space specify them using “\ “ (see NVP with spaces in delimiters), as shown in the examples, in Teradata it is not necessary to escape the backslash, however, it is necessary in Snowflake. Escaping the backslashes in the delimiter can be done automatically by SnowConvert AI but only if the delimiter values are literal strings, otherwise the user will be warned that the backslashes could not be escaped and that it may cause different results in Snowflake.

Related EWIs¶

1. SSC-FDM-TD0008: Non-literal delimiters with spaces need their backslash escaped in Snowflake.

OVERLAPS¶

Description¶

According to Teradata’s documentation, the OVERLAPS operator compares two or more period expressions. If they overlap, it returns true.

For more information, see the Teradata OVERLAPS documentation.

period_expression
OVERLAPS
period_expression

The PERIOD_OVERLAPS_UDF is a Snowflake implementation of the OVERLAPS operator in Teradata.

Sample Source Pattern¶

Teradata¶

Query

SELECT
    PERIOD(DATE '2009-01-01', DATE '2010-09-24')
    OVERLAPS
    PERIOD(DATE '2009-02-01', DATE '2009-06-24');

Snowflake Scripting¶

Query

SELECT
    PUBLIC.PERIOD_OVERLAPS_UDF(ARRAY_CONSTRUCT(PUBLIC.PERIOD_UDF(DATE '2009-01-01', DATE '2010-09-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!, PUBLIC.PERIOD_UDF(DATE '2009-02-01', DATE '2009-06-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!)) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!;

Known Issues¶

1. Unsupported Period Expressions¶

The PERIOD(TIME WITH TIME ZONE) and PERIOD(TIMESTAMP WITH TIME ZONE) expressions are not supported yet.

Related EWIs¶

1. SSC-EWI-TD0053: Snowflake does not support the period datatype, all periods are handled as varchar instead

P_INTERSECT¶

Description¶

According to Teradata’s documentation, the P_INTERSECT operator compares two or more period expressions. If they overlap, it returns the common portion of the period expressions.

For more information, see the Teradata P_INTERSECT documentation.

period_expression
P_INTERSECT
period_expression

The PERIOD_INTERSECT_UDF is a Snowflake implementation of the P_INTERSECT operator in Teradata.

Sample Source Pattern¶

Teradata¶

Query

SELECT
    PERIOD(DATE '2009-01-01', DATE '2010-09-24')
    P_INTERSECT
    PERIOD(DATE '2009-02-01', DATE '2009-06-24');

Snowflake Scripting¶

Query

SELECT
    PUBLIC.PERIOD_INTERSECT_UDF(ARRAY_CONSTRUCT(PUBLIC.PERIOD_UDF(DATE '2009-01-01', DATE '2010-09-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!, PUBLIC.PERIOD_UDF(DATE '2009-02-01', DATE '2009-06-24') !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!)) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!;

Known Issues¶

1. Unsupported Period Expressions¶

The PERIOD(TIME WITH TIME ZONE) and PERIOD(TIMESTAMP WITH TIME ZONE) expressions are not supported yet.

Related EWIs¶

1. SSC-EWI-TD0053: Snowflake does not support the period datatype, all periods are handled as varchar instead

PIVOT¶

Translation specification for the PIVOT function from Teradata to Snowflake

Description¶

The pivot function is used to transform rows of a table into columns. For more information check the PIVOT Teradata documentation.

PIVOT ( pivot_spec )
  [ WITH with_spec [,...] ]
  [AS] derived_table_name [ ( cname [,...] ) ]

pivot_spec := aggr_fn_spec [,...] FOR for_spec

aggr_fn_spec := aggr_fn ( cname ) [ [AS] pvt_aggr_alias ]

for_spec := { cname IN ( expr_spec_1 [,...] ) |
( cname [,...] ) IN ( expr_spec_2 [,...] ) |
cname IN ( subquery )
}

expr_spec_1 := expr [ [AS] expr_alias_name ]

expr_spec_2 := ( expr [,...] ) [ [AS] expr_alias_name ]

with_spec := aggr_fn ( { cname [,...] | * } ) [AS] aggr_alias

Sample Source Patterns¶

Setup data¶

Teradata¶

Query¶

 CREATE TABLE star1(
	country VARCHAR(20),
	state VARCHAR(10),
	yr INTEGER,
	qtr VARCHAR(3),
	sales INTEGER,
	cogs INTEGER
);

insert into star1 values ('USA', 'CA', 2001, 'Q1', 30, 15);
insert into star1 values ('Canada', 'ON', 2001, 'Q2', 10, 0);
insert into star1 values ('Canada', 'BC', 2001, 'Q3', 10, 0);
insert into star1 values ('USA', 'NY', 2001, 'Q1', 45, 25);
insert into star1 values ('USA', 'CA', 2001, 'Q2', 50, 20);

Snowflake¶

Query¶

 CREATE OR REPLACE TABLE star1 (
	country VARCHAR(20),
	state VARCHAR(10),
	yr INTEGER,
	qtr VARCHAR(3),
	sales INTEGER,
	cogs INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO star1
VALUES ('USA', 'CA', 2001, 'Q1', 30, 15);

INSERT INTO star1
VALUES ('Canada', 'ON', 2001, 'Q2', 10, 0);

INSERT INTO star1
VALUES ('Canada', 'BC', 2001, 'Q3', 10, 0);

INSERT INTO star1
VALUES ('USA', 'NY', 2001, 'Q1', 45, 25);

INSERT INTO star1
VALUES ('USA', 'CA', 2001, 'Q2', 50, 20);