fbpx

Teradata Queue Tables for Active Data Warehousing

By stelvio.sanfilippo

February 22, 2017


Introduction

In today’s business, it is critical to respond as fast as possible to opportunities and problems as they happen.

These business events can be detected and collected for immediate processing resulting in an alert to a front-line user or an update message to an operational system.

Teradata parallel database triggers manage exactly this kind of live event.

Typically, every X minutes, a dashboard function checks the queue table for significant events, filters out those that aren’t important, and finds one serious shipper delay that will affect profitability.
All this is possible with Teradata triggers, stored procedures, and queue tables that are easy to program and link to production applications.

How does a particular Queue table work?

It’s similar to ordinary base tables, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.

When you create a queue table, you must define a TIMESTAMP column QITS (Queue Insertion TimeStamp) with a CURRENT_TIMESTAMP default value.

The column values indicate the time the rows were inserted into the queue table unless different, user-supplied values are inserted:

Teradata Queue Tables for Active Data Warehousing 1

You can use an INSERT statement, which operates as a FIFO push

Teradata Queue Tables for Active Data Warehousing 2

You can use a SELECT statement, which works like a FIFO peek

Teradata Queue Tables for Active Data Warehousing 3Teradata Queue Tables for Active Data Warehousing 4

You can then use a SELECT AND CONSUME statement, which operates as a FIFO pop:

Data is returned from the row with the oldest timestamp in the specified queue table.
The row is deleted from the queue table, guaranteeing that the row is processed only once.

Teradata Queue Tables for Active Data Warehousing 5Teradata Queue Tables for Active Data Warehousing 6

We perform another peek operation to check out the previous pop operation. The result is correct: the first record was correctly consumed.

Teradata Queue Tables for Active Data Warehousing 7

If no rows are available, the transaction enters a delay state until one of the following actions occur:
• A row is inserted into the queue table
• The transaction aborts, either as a result of direct user intervention, such as the ABORT statement, or indirect user intervention, such as a DROP TABLE statement on the queue table.

Queue Tables and Performance

For Queue tables, we have to stay aware on:

1. Each time the system performs a DELETE, MERGE, or UPDATE operation on a queue table, the FIFO cache for that table is spoiled. The next INSERT or SELECT AND CONSUME request performed on the table initiates a full‑table scan to rebuild the FIFO cache, which impacts performance. So, you should code DELETE, MERGE, and UPDATE operations only sparingly, and these should never be frequently performed operations.
2. The queue table FIFO cache on each Parsing Engine (PE) supports 100 queue tables. When the number of active queue tables in the cache exceeds 100, the system performs full table scans. The system performs full table scans on all the tables in the cache and initiates a purge of the cache by taking one of the following actions:

– Swap out a queue table that has been spoiled. For example, if a queue table has had a delete operation performed, it is a purge candidate from the FIFO cache.

– Purge an inactive queue table from the FIFO cache.
3. To optimize the distribution of your queue tables across the PEs, consider creating them all simultaneously.

Limitations on Queue Tables

Creation or altering may not:

have Permanent Journals
Remember that the purpose of a permanent journal is to maintain a sequential history of all changes made to the rows of one or more tables, and
protect user data when users commit, un-commit, or abort transactions.
See article: Teradata Permanent Journal
contain any Large Object(LOB) data
contain References or Foreign Keys
Questions?
If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.
__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

stelvio.sanfilippo

Stelvio Sanfilippo is a graduated computer scientist, Data Warehouse and Business Intelligence professional, working with the Teradata database system for more than 7 years.He is experienced in the fields of banking and telecommunication with a strong focus on BI/DWH solutions and architectures.

  • Stelvio Sanfilippo
    Stelvio Sanfilippo says:

    Hi Jiri, you’re welcome, thank you for the question!
    Teradata uses QITS column to maintain the FIFO ordering of rows in the queue table.
    The queue table FIFO cache row entry is in fact a pair of QITS and rowID values for each row to be consumed from the queue, sorted in QITS value order. So, yes, for instance when you execute the query “Select and consume top 1 from queue_table” the system is able to retrieve the queue table row at the top of the FIFO queue from a single AMP.

  • Hi Stelvio, thank you very much for your reply! I’ve got a follow-up question, though. In which way does the FIFO cache affect the query performance of queue tables? Does it contain, for example, statistic-like information on QTIS column so the Teradata could perform a single-AMP data retrieval?

  • Stelvio Sanfilippo
    Stelvio Sanfilippo says:

    Thank you very much indeed Jiri.
    Basically the Queue FIFO cache purpose is improve the performance for consuming rows from queue tables. The FIFO caches are created by the system during startup, and hold row information for a number of non‑consumed rows for each queue table. The Dipsatcher is responsible for maintaining this cache.

  • Hi Stelvio, great article! Just a question regarding the FIFO cache. What is its purpose and what does it contain? Thanks! 🙂

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

    You might also like

    >