Translation reference to convert Teradata Expand On functionality to Snowflake
Description
The Expand On clause expands a column having a period data type, creating a regular time series of rows based on the period value in the input row. For more information about Expand On clause, see the Teradata documentation.
Sample Source Patterns
Some parts in the output code are omitted for clarity reasons.
Sample data
IN -> Teradata_01.sql
CREATE TABLE table1 (id INTEGER, pd PERIOD (TIMESTAMP));
INSERT INTO
table1
VALUES
(
1,
PERIOD(
TIMESTAMP '2022-05-23 10:15:20.00009',
TIMESTAMP '2022-05-23 10:15:25.000012'
)
);
OUT -> Teradata_01.sql
CREATE OR REPLACE TABLE table1 (
id INTEGER,
pd VARCHAR(58) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;
INSERT INTO table1
VALUES (
1, PUBLIC.PERIOD_UDF(
TIMESTAMP '2022-05-23 10:15:20.00009',
TIMESTAMP '2022-05-23 10:15:25.000012'
) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!);
Expand On Clause
Suppose you want to expand the period column by seconds, for this Expand On clause has anchor period expansion and interval literal expansion.
Anchor Period Expansion
IN -> Teradata_02.sql
SELECT
id,
BEGIN(bg)
FROM
table1 EXPAND ON pd AS bg BY ANCHOR ANCHOR_SECOND;
id
BEGIN (bg)
1
2022-05-23 10:15:21.0000
1
2022-05-23 10:15:22.0000
1
2022-05-23 10:15:23.0000
1
2022-05-23 10:15:24.0000
1
2022-05-23 10:15:25.0000
Snowflake doesn't support Expand On clause. To reproduce the same results and functionality, the Teradata SQL code will be contained in a CTE block, with an EXPAND_ON_UDF and TABLE function, using FLATTEN function to return multiple rows, ROW_COUNT_UDF and DIFF_TTIME_PERIOD_UDF to indicate how many rows are needed and returning VALUE to help the EXPAND_ON_UDF to calculate the different regular time series. This CTE block returns the same expand columns alias as in the Expand On clause, so the result can be used in any usage of period datatype.
OUT -> Teradata_02.sql
WITH ExpandOnCTE AS
(
SELECT
PUBLIC.EXPAND_ON_UDF('ANCHOR_SECOND', VALUE, pd) bg
FROM
table1,
TABLE(FLATTEN(PUBLIC.ROW_COUNT_UDF(PUBLIC.DIFF_TIME_PERIOD_UDF('ANCHOR_SECOND', pd))))
)
SELECT
id,
PUBLIC.PERIOD_BEGIN_UDF(bg) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
FROM
table1,
ExpandOnCTE;
id
PERIOD_BEGIN_UDF(bg)
1
2022-05-23 10:15:21.0000
1
2022-05-23 10:15:22.0000
1
2022-05-23 10:15:23.0000
1
2022-05-23 10:15:24.0000
1
2022-05-23 10:15:25.0000
Known Issues
The Expand On clause can use interval literal expansion, for this case, SnowConvert will add an error that this translation is planned.
Interval literal expansion
IN -> Teradata_03.sql
SELECT
id,
BEGIN(bg)
FROM
table1 EXPAND ON pd AS bg BY INTERVAL '1' SECOND;
id
BEGIN(bg)
1
2022-05-23 10:15:20.0000
1
2022-05-23 10:15:21.0000
1
2022-05-23 10:15:22.0000
1
2022-05-23 10:15:23.0000
1
2022-05-23 10:15:24.0000
OUT -> Teradata_03.sql
SELECT
id,
PUBLIC.PERIOD_BEGIN_UDF(bg) !!!RESOLVE EWI!!! /*** SSC-EWI-TD0053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/!!!
FROM
table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'EXPAND ON' NODE ***/!!!
EXPAND ON pd AS bg BY INTERVAL '1' SECOND;