Teradata has a Period data type to represent a time interval between two specific dates. But what about Snowflake? Tragically, it doesn’t have an equivalent data type. So, how can we convert the old Teradata Code with columns and operations of Period data type to Snowflake without failing in the attempt? SnowConvert. That's how.
This blog post will help us dig a little deeper.
Teradata defines the Period data type as a complex type that has two elements within it; the first represents the beginning bound and the second the ending bound.
Both elements can be any time data type (like DATE, TIME, or TIMESTAMP). And it's also worth mentioning that the element type must be the same for both elements of a period. To illustrate this functionality, consider the following Teradata code that defines a dummy event table and a query that select all the events that happened on a specific date:
CREATE TABLE demo.event
(
[BC2] event_id INTEGER,
event_name VARCHAR(20),
duration PERIOD(DATE)
);
INSERT INTO demo.event VALUES (1,'Summer camp', PERIOD(DATE '2005-06-30', DATE '2005-07-15'));
INSERT INTO demo.event VALUES (2,'Science camp', PERIOD(DATE '2005-06-25', DATE '2005-07-05'));
SELECT event_name, duration
FROM demo.event
WHERE BEGIN(duration) <= DATE '2005-07-01' AND END(duration) >= DATE '2005-07-01'
Nice. But how do we transform this to Snowflake. Unfortunately, there is not an equivalent type with which you could directly transform it.
This can be done in SnowConvert for Teradata using two approaches.
The first is converting the period data type to a string. This is simply done by defining a format, transforming each datetime value to a string, and finally adding each value to the string with the format. You could even use a semi-structured data type and create a JSON object, although it will consume more space than a string with format. In the following example, you can see the conversion of the first example code using this approach:
CREATE TABLE demo.PUBLIC.event
(
event_id INTEGER,
event_name VARCHAR(20),
duration VARCHAR(24)
);
INSERT INTO demo.PUBLIC.event
VALUES (1,'Summer camp', PUBLIC.PERIOD_UDF(DATE '2005-06-30', DATE '2005-07-15'));
INSERT INTO demo.PUBLIC.event
VALUES (2,'Science camp', PUBLIC.PERIOD_UDF(DATE '2005-06-25', DATE '2005-07-05') );
SELECT
event_name,
duration
FROM
demo.PUBLIC.event
WHERE PUBLIC.PERIOD_BEGIN_UDF(duration) <= DATE '2005-07-01' AND
PUBLIC.PERIOD_END_UDF(duration) >= DATE '2005-07-01';
As shown above, the Period data type becomes a string and the constructor is replaced with a user-defined function (UDF) that creates the string with the two dates. Also, the built-in functions BEGIN and END were replaced with its equivalent UDF.
The second approach is by splitting the elements of the Period data type in two different fields. One for the beginning bound and the other for the ending bound. This way is harder than the string transformation because you must modify the structure of the tables and queries instead of only changing the type of the period fields. In the following sample code, you can see the conversion result by separating the period values:
CREATE TABLE demo.PUBLIC.event
(
event_id INTEGER,
event_name VARCHAR(20),
duration_begin DATE,
duration_end DATE
);
INSERT INTO demo.PUBLIC.event VALUES (1,'Summer camp',DATE '2005-06-30',DATE '2005-07-15');
INSERT INTO demo.PUBLIC.event VALUES (2,'Science camp', DATE '2005-06-25', DATE '2005-07-05');
SELECT
event_name,
duration_begin,
duration_end
FROM
demo.PUBLIC.event
WHERE duration_begin <= DATE '2005-07-01' AND
duration_end >= DATE '2005-07-01';
In the above case, the resulting code looks cleaner than the previous codeset because it was not necessary to use any UDF. Note that the structure of all the statements changes though with a new column inserted in the select query and in the table because an extra value is needed in both inserts. In addition, for both cases you must convert all the functions that Teradata provides to the user to operate with the Period data type values (for example, OVERLAPS or INTERSECT).
As mentioned before the second approach (about the conversion of period column as separated elements), we must modify the structure of almost all statements that reference a Period data type. In the third code example, the transformation was simple because there were just a few statements, but the real challenge begins when there are hundreds of statements that references period fields. Because the structure of all of them will change, if you skip some reference the output code will not work. In contrast, this does not happen when the period data type is transformed to a string, as only the period data type and its related functions are converted.
The conversion of Period data type to String is easier and safer than the other one, but is less efficient for two reasons: 1) more functions will be converted to UDFs (for example, the Period constructor), and 2) the UDFs need to parse the string to a time type and then transform it to string again to save the results. For those reasons, this approach will take more time to execute. However, the real performance difference will be appreciated if there are a lot of operations with millions of period columns.
In Snowflake, we can represent a period by using strings with format or two separated fields. One is cleaner and faster than the other, but changes the structure of the code. The other is easier, but the datetimes must be extracted from a string with a UDF for almost any operation. At the end, there is a trade-off choice and it depends on your needs.
In addition, no matters which approach you choose, you can utilize SnowConvert for Teradata and BlackDiamond Studio to convert the Period columns to either String data type or separated in two columns. You can try the above conversion out with the free trial of SnowConvert for Teradata that comes with by signing up for a free account in BlackDiamond Studio today.