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 ?
CREATE SET TABLE queue_table1, QUEUE, FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL (
X TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
PRIMARY INDEX (Y, Z);
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.
SELECT databasename, tablename
WHERE queueflag = 'y';
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