QUEUE TABLES

Share via:
QUEUE TABLES

Dear Readers,

In this article you will get to know the QUEUE TABLES

What is a Queue table ?

It is a Teradata extension to ANSI SQL at the database/ table level.

It is used to handle queue-oriented data, such as event processing and asynchronous data loading applications, with subsequent complex processing of the buffered data load.

Teradata Database queue tables support asynchronous first-in-first-out (FIFO) push, pop, and peek queue operations:

  • The FIFO push operation is supported by SQL INSERT operation to store rows into a queue table.
  • The FIFO peek operation is supported by SQL SELECT operation to retrieve rows from a queue table without                deleting them. This is also referred to as browse mode.
  • The FIFO pop operation is supported by SQL SELECT AND CONSUME operation to retrieve a row from a queue          table and delete that selected row upon completion of the read.

This is also referred to as consume mode.Consumed rows are rolled back when a transaction containing a SELECT AND CONSUME does not complete successfully.PERCENT or WITH TIES options cannot be used with consume mode SELECT operations on Queue tables.

An ideal queue table has the following characteristics :

  • Low cardinality (implying that its rows are consumed at roughly the same rate as they are inserted).
  • Infrequent UPDATE operations to its rows.
  • Infrequent DELETE operations on its rows.

How to create a Queue table ?

Syntax :

Points to remember while creating a queue table

  • QUEUE is a mandatory keyword to indicate that the table being defined is a queue table.
  • You can create queue tables with inline JSON, ST_GEOMETRY, or XML Columns.
  • The first column defined for the table should be the Query Insertion Timestamp (QITS) column. This is mandatory          for all the queue tables.
  • The QITS column contains the time the row was inserted into the queue table as the means for approximate FIFO          ordering
  • The QITS column must be defined with a timestamp data type with exactly the same precision and default value as        specified in the example.
  • You do not need to specify a precision for the TIMESTAMP data type specification because it defaults to 6 when            no precision is specified.
  • You cannot explicitly specify a precision other than 6 for the timestamp. This is mandatory for queue tables
  • The QITS column is not the (non unique) primary index for the table.You cannot create a USI on the QITS column          of a queue table.
  • Queue tables can be created as Non-partitioned NoPI tables.

 

Rules and Restrictions for Modifying Queue Tables

  • You cannot change a queue table into a non-queue table.
  • You cannot modify the first column defined for a queue table to be something other than a user-defined QITS                column.
  • You cannot drop the QITS column from a queue table.
  • You cannot modify the QITS column by adding either of the following attributes.

1. UNIQUE, 2. PRIMARY KEY

  • Because of this restriction, you cannot add a simple UPI on the QITS column.
  • Similarly, you cannot modify the QITS column to make it a simple UPI.
  • You cannot add either of the following constraints to a queue table definition.

1. FOREIGN KEY … REFERENCES, 2. REFERENCES

  • You cannot modify any column of a queue table to have a LOB data type
  • You cannot add a permanent journal to the definition for a queue table.
  • You cannot add a reference to queue table columns in a REFERENCES clause for any table.
  • Otherwise, all column- and table-level constraint clauses are valid within queue table definitions with the exception        of UNIQUE and PRIMARY KEY constraints not being valid attributes for the QITS column.
  • You cannot modify a queue table definition to have a partitioned primary index.
  • Queue tables cannot be column-partitioned tables.

 

What if we do SELECT -CONSUME and table contains no data?

  • A consume mode request goes into a delayed state when a SELECT AND CONSUME finds no rows in the queue          table.
  • SELECT AND CONSUME request will wait for the table to be populated.
  • The request remains idle until an INSERT to that queue table awakens the request; that row is returned, and then it        is deleted.

 

How to locate queue tables in Teradata Database ?

Using DBC.TablesV, we can know the queue tables in Teradata database.

How to change the default FIFO ordering ?

  • The default FIFO ordering of a queue table may be altered before consumption by altering the QITS.
  • This is done with the SQL UPDATE, UPSERT form of UPDATE, or MERGE statements.

 

How to populate a Queue table ?

  • With INSERT statements
  • With INSERT…SELECT statements (from queue or a non-queue table)
  • Using Load utility like Teradata Parallel Data Pump or Teradata Parallel Transporter (using the STREAM and                    INSERT operators) that generates those statements
  • Using INSERT … SELECT AND CONSUME statement to populate a queue table with rows from another queue table

 

How to delete data from Queue tables ?

  • Using SELECT..CONSUME
  • SQL DELETE

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment