Query Teradata Vantage from a Mule service

Overview

This example is a clone of the Mulesoft MySQL sample project. It demonstrates how to query a Teradata database and expose results over REST API.

Prerequisites

Mulesoft Anypoint Studio. You can download a 30-day trial from https://www.mulesoft.com/platform/studio.
Access to a Teradata Vantage instance.

If you need a test instance of Vantage, you can provision one for free at https://clearscape.teradata.com.
Java JDK. See installation instructions for OpenJDK: https://openjdk.java.net/install/.
Apache Maven. See installation instructions: https://maven.apache.org/install.html.

Example service

This example Mule service takes an HTTP request, queries the Teradata Vantage database and returns results in JSON format.

The Mule HTTP connector listens for HTTP GET requests with the form: http://<host>:8081/?lastname=<parameter>;. The HTTP connector passes the value of <parameter> as one of the message properties to a database connector. The database connector is configured to extract this value and use it in this SQL query:

SELECT * FROM hr.employees WHERE LastName = :lastName

As you can see, we are using parameterized query with reference to the value of the parameter passed to the HTTP connector. So if the HTTP connector receives http://localhost:8081/?lastname=Smith, the SQL query will be:

SELECT * FROM employees WHERE last_name = Smith

The database connector instructs the database server to run the SQL query, retrieves the result of the query, and passes it to the Transform message processor which converts the result to JSON. Since the HTTP connector is configured as request-response, the result is returned to the originating HTTP client.

Setup

Clone Teradata/mule-jdbc-example repository:

 git clone git@github.com:Teradata/mule-jdbc-example.git

Edit src/main/mule/querying-a-teradata-database.xml, find the Teradata connection string jdbc:teradata://localhost/user=dbc,password=dbc and replace Teradata connection parameters to match your environment.

Create a sample database in your Vantage instance. Populate it with sample data.

 -- create database
 CREATE DATABASE HR
   AS PERMANENT = 60e6, SPOOL = 120e6;

 -- create table
 CREATE SET TABLE HR.Employees (
   GlobalID INTEGER,
   FirstName VARCHAR(30),
   LastName VARCHAR(30),
   DateOfBirth DATE FORMAT 'YYYY-MM-DD',
   JoinedDate DATE FORMAT 'YYYY-MM-DD',
   DepartmentCode BYTEINT
 )
 UNIQUE PRIMARY INDEX ( GlobalID );

 -- insert a record
 INSERT INTO HR.Employees (
   GlobalID,
   FirstName,
   LastName,
   DateOfBirth,
   JoinedDate,
   DepartmentCode
 ) VALUES (
   101,
   'Test',
   'Testowsky',
   '1980-01-05',
   '2004-08-01',
   01
 );

Download the Teradata JDBC driver and unzip it:

 wget https://downloads.teradata.com/download/cdn/connectivity/jdbc/17.10.00.24/TeraJDBC__indep_indep.17.10.00.24.zip
 unzip TeraJDBC__indep_indep.17.10.00.24.zip

Import the JDBC driver to your local Maven repository:

 mvn install:install-file \
 -Dfile=terajdbc4.jar \
 -DgroupId=com.teradata \
 -DartifactId=terajdbc4 \
 -Dversion=17.10.00.24 \
 -Dpackaging=jar \
 -DgeneratePom=true

Open the project in Anypoint Studio.
- Once in Anypoint Studio, click on Import projects..:
- Select Anypoint Studio project from File System:
- Use the directory where you cloned the git repository as the Project Root. Leave all other settings at their default values.

Run

Run the example application in Anypoint Studio using the Run menu. The project will now build and run. It will take a minute.

Go to your web browser and send the following request: http://localhost:8081/?lastname=Testowsky.

You should get the following JSON response:

[
  {
    "JoinedDate": "2004-08-01T00:00:00",
    "DateOfBirth": "1980-01-05T00:00:00",
    "FirstName": "Test",
    "GlobalID": 101,
    "DepartmentCode": 1,
    "LastName": "Testowsky"
  }
]

Query Teradata Vantage from a Mule service

Overview

Prerequisites

Example service

Setup

Run

Further reading