Teradata provides built-in support for temporal data. Temporal data makes it easy to build and maintain applications where information changes over time. For example, consider a merchandise application that must store the price of an item along with the dates during which that price is valid. As shown below, traditional table design uses two date columns to store the beginning and end dates during which the price is valid.
CREATE TABLE price
(
Item_UPC BIGINT,
Price FLOAT,
Begin_date DATE,
End_Date DATE
)Item_UPC Price Begin_Date End_Date -------- ----- ---------- -------- 123456789012 $5 2010-01-05 9999-12-31
When the price of the item changes, the end date of the current price must be updated, and a new row must be inserted with the new price.
Item_UPC Price Begin_Date End_Date -------- ----- ---------- -------- 123456789012 $5 2010-01-05 2011-05-01 123456789012 $4 2011-05-01 9999-12-31
Teradata temporal support can simplify this process. You can use the PERIOD(DATE) data type to represent the time period during which the price is valid. Here is how you can create the table instead.
CREATE TABLE price
(
Item_UPC BIGINT,
Price FLOAT,
Price_validity PERIOD(DATE) NOT NULL AS VALIDTIME
)Item_UPC Price Price_validity -------- ----- -------------- 123456789012 $5 (2010-01-05, 9999-12-31)
For additional examples and information about features, see the Teradata temporal table support documentation.
SAS/ACCESS Interface to Teradata supports these temporal data types.
For true temporal support, you must specify the transaction-time and valid-time attributes on the PERIOD data type.
The transaction-time attribute on a PERIOD column makes the table a transaction-time table. Teradata automatically maintains tables with transaction-time columns. It tracks when a row is first made known to the table. When a row is inserted, it is considered to be an open row because it is currently in effect until the end of time. If the row is deleted, Teradata marks it as a closed row that is no longer in effect. However, the table can be queried to obtain rows that were open at a particular point in time even though the row is not currently valid. Similarly, when a row is modified, the current row is closed and a new row is opened and made effective.
A user cannot specify or modify a transaction-time column.
The valid-time attribute indicates the time period during which the information is in effect. If valid time is specified with the PERIOD data type, Teradata maintains how the time period is in effect if the row is updated or deleted. As in the example about prices, when a row is inserted with a new price, Teradata maintains the end date of the original row. The row with the old price is updated with an end date and the new row is inserted.
A row in a valid-time transaction table can be a history row, a current row, or a future row. The history row is no longer valid with respect to current time. Its end-time period is before the current time. A current row has a time period that straddles the current time.
To create a Teradata table with temporal data types from SAS, use the DBTYPE= data set option. SAS does not have an equivalent data type for PERIOD. The value is represented in SAS as a character string. In this example, when the character string that represents the period ID is inserted into Teradata, it is implicitly converted to a PERIOD data type.
data x.mytest(DBTYPE=(validity='PERIOD(DATE) VALIDTIME'));
i=1;
validity='(1973-02-03, 9999-12-31)';
output;
run;A Teradata PERIOD data-type
column can be read into SAS like any other column. It is represented
in SAS as a character string, such as '(1973-02-03, 9999-12-31)'.
Temporal tables contain
rows that can be current, history, or future in the valid-time dimension.
In the transaction-time dimension, rows can be open or closed. Temporal
qualifiers specify what data is needed. The
TEMPORAL_QUALIFIER=LIBNAME and data set options let you qualify queries in the valid-time or transaction-time
dimension. For example, to fetch rows that are
valid as of '2009-01-01' in a table, you
must specify TEMPORAL_QUALIFIER='VALIDTIME AS OF DATE '2009-01-01' ' as
a LIBNAME or data set option when you query temporal tables.
The option that you specify for TEMPORAL_QUALIFIER= is free-form text. Here are some examples.
TEMPORAL_QUALIFIER='CURRENT VALIDTIME'
TEMPORAL_QUALIFIER='VALIDTIME AS OF DATE '2009-01-01' '
TEMPORAL_QUALIFIER='NONSEQUENCED VALIDTIME'
TEMPORAL_QUALIFIER=' SEQUENCED VALIDTIME'
TEMPORAL_QUALIFIER='NONSEQUENCED VALIDTIME PERIOD '(2007-01-01, 2008-03-01)''
TEMPORAL_QUALIFIER=' SEQUENCED VALIDTIME PERIOD '(2007-01-01, 2008-03-01)''
TEMPORAL_QUALIFIER='CURRENT TRANSACTIONTIME'
TEMPORAL_QUALIFIER='TRANSACTIONTIME AS OF TIMESTAMP '2009-01-01 01:02:03.123456' 'If you specify the temporal
qualifier on the LIBNAME, it applies to the entire session because
it is implemented by issuing session commands at connect time. For
example, if you specify TEMPORAL_QUALIFIER='ASOF PERIOD '(1999-01-01,
2099-01-05)' ' on the LIBNAME, here is the Teradata SET
SESSION command that is issued at connect time. The SQL is submitted
as usual.
.SET SESSION ASOF PERIOD '(1999-01-01, 2099-01-05)'If you submit the above command, the temporal qualifier is added as a prefix, as shown below.
ASOF PERIOD '(1999-01-01, 2099-01-05)'
SELECT * from TEMPORAL_TABLE; Sample code
-----------
/* PERIOD data types require the Teradata V13 server. */
libname x teradata user=myusr1 pw=mypwd1 server=mysrv1;
/* Create a table with the PERIOD(DATE) data type.
Note: This is not a temporal table. */
data x.mytest(DBTYPE=(validity='PERIOD(DATE)'));
i=1; validity='(1973-02-03, 9999-12-31)'; output;
run;
/* Read from a table with a PERIOD data type? */
proc print data=x.mytest;
run;
/* Use FastLoad to load a table with a PERIOD data type. */
proc datasets library=x;
delete mytest;run;
data x.mytest(DBTYPE=(validity='PERIOD(TIMESTAMP)') FASTLOAD=YES TPT=NO);
i=1; validity='(1970-01-05 01:02:03.123, 1970-01-05 05:06:07.456)';
output;
run;
/* Tempral support starts in Teradata V13.10. */
libname x teradata user=myusr1 pw=mypwd1 server=mysrv1;
/* Create a table with the PERIOD(DATE) data type. */
data x.mytest(DBTYPE=(validity='PERIOD(DATE) VALIDTIME'));
i=1; validity='(1973-02-03, 1999-12-31)'; output;
i=2; validity='(2000-01-01, 2011-01-01)'; output;
i=3; validity='(2011-01-02, 9999-12-31)'; output;
run;
/* Can we read a PERIOD data type?
You must select the row with i=2. */
proc print data=x.mytest(TEMPORAL_QUALIFIER='CURRENT VALIDTIME');
run;
/* Consider data as of 1995-01-01. */
libname x teradata user=myusr1 pw=mypwd1 server=mysrv1
TEMPORAL_QUALIFIER='VALIDTIME AS OF DATE '1995-01-01' '
/* Row with i=1 is returned. */
proc print data=x.mytest;
run;