Overview

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Overview

Message oriented technologies are an essential component of enterprise wide business operations. Messaging products allow heterogeneous applications to inter-operate across an enterprise by providing a form of loosely coupled distributed communication. Java Message Service (JMS) is an integral part of Java 2 Enterprise Edition (J2EE). JMS provides a standard interface for Java applications to create, send, receive, and read an enterprise messaging system’s messages.

Most enterprises build custom JMS applications to read messages from a JMS Queue and update a database table using information stored in the message. However, most applications are built for a specific table with specific types of updates such as insert, delete, and, update.

The initial request to develop this solution was to satisfy customer requirements to integrate JMS with Teradata. The requirement reflected a desire to load data sourced from JMS messages into Teradata tables. To satisfy this requirement, the Teradata JMS Universal Connector was developed.

Architecture Overview and Process Flow

Teradata JMS Universal Connector is a J2EE application that executes in a Web Server. The Loader component receives messages from a JMS Queue or JMS Topic and processes the message payload according to user specified message properties. Any JMS client application could propagate updates to Teradata by sending messages to any JMS Queue or Topic. JMS Universal Connector uses JDBC and standard SQL to achieve near-real time updates to the Data Warehouse. The Extractor component allows users to consume data from a Teradata Queue Table and publish the data to any JMS Queue or JMS Topic.

The diagram below depicts the Teradata JMS Universal Connector architecture.

Figure 1: Overview: JMS Universal Connector for Teradata integrates with JMS compliant Queues or Topics.

The Loader

  1. Retrieves messages from JMS Queues or JMS Topics.
  2. Parses the data in the message payload. The data must be in the proper format for the specified action. The action can be INSERT, DELETE, UPDATE, or UPSERT.
  3. Creates the appropriate SQL statements for the data in the payload for the user specified processing type.
  4. Process the data. For INSERT, the data in the payload is inserted into the table. For DELETE, rows are deleted from the table based on information in the data. For UPDATE, rows are updated in the table based on information in the data. For UPSERT, rows are updated in the table or inserted into the table based on information in the data.
  5. The loader module is multi-threaded to provide high performance.

The Extractor

  1. Retrieves data from Teradata Queue Tables.
  2. Composes data into an xml format and constructs a JMS message with the data as the message payload.
  3. Sends the JMS message to the target JMS Queue or JMS Topic.
  4. The extractor is multi-threaded to provide high performance.

Administration UI

  1. Provides the web user interface to define and manage loader and extractor services.
  2. Can start and stop loader or extractor services.
  3. All the service and environment information are stored in the Metadata Info database which is the system repository.

Key Points:

  • The Loader component can receive messages from both JMS Queues and Topics. The source data resides in the message payload. The source data can contain one or more rows of data for update in Teradata. The format of the data can be one of fixed length, delimiter separated, or XML. The action can be one of INSERT, DELETE, UPDATE, or UPSERT.
  • The Loader component uses JDBC with Batch Inserts for updating Teradata tables.
  • The Extractor component can send messages to both JMS Queues and Topics. The source data resides in a Teradata Queue Table. The source data is formatted into a message payload for sending to a JMS Queue or Topic. The data can contain one or more rows from the source Teradata Queue Table.
  • Users can configure the payload format using the Web Administration UI.