fbpx

Teradata Temporal Query Processing Review

By Roland Wenzlofsky

October 18, 2014


It is assumed that you are familiar with the basic concepts of historization to get the most out of this article.

Temporal data management is a crucial task in data warehousing. A few years ago, we had to write our temporal data management functionality to historize our data and keep different historical timelines.

Starting with release 13.10, Teradata, like many other data warehouse vendors, added temporal data management functionality, based on the TSQL2 specification, a temporal extension to the SQL-92 language standard.

The Teradata temporal feature incorporates temporal table definitions (valid time and transaction time), temporal qualifiers, temporal constraints, and comparison operators.

There are two different strategies for temporal data management implemented by database vendors.

Teradata’s plan is a query rewrite approach: The temporal query is rewritten into a non-temporal query by adding the historical constraints to the non-temporal query.

Other database vendors have chosen to directly build temporal features into the RDBMS without the indirect way mentioned above, resulting in better performance. Still, the rewrite approach has no impact on how a query is executed, and almost no impact on the design of the Teradata Optimizer. Hence, it was a more straightforward approach to introducing temporal data management into an existing RDBMS.

Here is an example of a temporal query, showing the applied rewrite step:

CURRENT VALIDTIME
SELECT *
FROM <TABLE>
WHERE <COLUMN1> = 10;

The rewritten query looks like this:

SELECT *
FROM <TABLE>
WHERE BEGIN(MyPeriod) <= DATE ‘2014-04-01’
AND END(MyPeriod) > DATE ‘2014-04-01’
AND <COLUMN1> = 10;

In the above example, it’s assumed that the current valid time equals 2014-04-01.

We can implement single and double historizations; double historization means keeping a separate, second-level historization timeline:

Valid Time Temporal Tables
Transaction Time Temporal Tables
Bi-Temporal Tables (containing Valid Time and Transaction Time, i.e., a double historization)

Here is one example for creating a temporal table (see below a bi-temporal table):

CREATEMULTISET TABLE
(
PK INTEGER NOT NULL,
MY_VALID_TIME PERIOD(DATE) NOT NULL AS VALIDTIME,
MY_TRANSACTION_TIME PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME
) PRIMARY INDEX (PK);

The data type PERIOD is a pair of dates or timestamps: {‘2014-01-31′,’9999-12-31’}

While VALIDTIME can be a pair of dates or timestamps, TRANSACTIONTIME always has to be a pair of timestamps.

Usually, VALIDTIME would be used to define the validity timeframe of each record belonging to an object, and TRANSACTIONTIME would keep versions of the different historical timelines specified by VALIDTIME.

Teradata automatically manages temporal tables.

In case you insert, update or delete a row, a historical row will be written. The historization works in an entirely automatic mode.

In case you run the following statement on a temporal table, the most current timeline with the currently valid record in this timeline will be automatically retrieved:

SELECT * FROM <TABLE>;

If you want to retrieve the whole history, add the following first line:

NONSEQUENCED VALIDTIME
SELECT * FROM <TABLE>;

If you want to retrieve the valid record for a particular date/time, another first line is needed:

VALIDTIME AS OF DATE ‘2014-04-01’
SELECT * FROM <TABLE>;

Finally, if you want to retrieve all historical timelines and all history rows per timeline:

NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
SELECT * FROM <TABLE>;

Although this was just a short introduction to Teradata temporal data management, you probably recognized that it could easily replace your handwritten historization logic.

It would be interesting to hear from professionals working with the Teradata temporal logic about query performance, especially when comparing Teradata temporals with handwritten historization solutions.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

  • Hi

    Having worked on data management in the context of time for some years, I have developed an approach that uses standard RDBMS facilities and DB modeling. http://www.youtube.com/watch?v=V1EcsuJxUno is one example of implementation against a requirement that involved multiple entities and the demanding business reporting requirements of an investment bank. ie the temporal challenge can be addressed without special capabilities.

  • Hi Roland,

    we had a session with Roy E. who is in charge of Teradata’s Global Control Framework (GCFR). And he mentioned that temporal tables are not yet supported in their framework. But that is not because it does not reliably work, but because they want to be able to sell GCFR to customers which do not have the Teradata Enterprise license. So not every Teradata system has this feature. It costs extra…

    -Timm

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >