Concept illustration of web technology interface

Tech2Tech

Hands On

Driverless Connectivity

REST API enables easy access to the Teradata® Database from Web pages, mobile devices and scripting languages.

Have you ever wanted to display or visualize SQL results on a Web page or mobile device? Or use a high-productivity scripting language to implement recurring business logic and deploy it without worrying about installing or maintaining a database driver? Implementing these use cases has not been easy since doing so requires building and supporting a middle-tier application to act as the translation layer between your application and the database.

Imagine, however, if you could use HTTP to query your database and get the results back in a standard format such as JSON. Well, you can. The REST API for Teradata® Database is a middle-tier Web service that accepts SQL requests in JSON through HTTP and forwards them to the Teradata Database using Java database connectivity (JDBC). (See figure.) When the SQL requests are complete, the results are converted into JSON (or alternatively, comma-separated values known as CSV) and streamed back to the requestor in the HTTP response.

Submit a Query

Before you can submit your first SQL statement, you need to register the target Teradata system. This can be done with the Service Configuration user interface or the REST API itself. When registering a system, you specify information including:

  • Teradata Database hostname
  • Database port number
  • List of database users allowed to access the service
  • Default session settings
  • Session limits
  • Alias used to refer to the system in future API calls

Once a system is registered, you can start submitting SQL statements. To do this, you send an HTTP request using the POST method on the /tdrest/systems/{name}/queries resource, replacing {name} with the alias of the system. HTTP requests are comprised of a request line, a series of request headers, an empty line and the message body:

POST /tdrest/systems/mysystem/queries HTTP/1.1
Accept: application/vnd.com.teradata.rest-v1.0+json
Authorization: Basic ZGJjOmRiYw==
{
	"query":"SELECT * FROM Customers",
	"format":"object"
}

In this example, you are submitting a "SELECT * FROM Customers" query. The first line is the request line and instructs the service that you want to submit a new query to the system named "mysystem". The next two lines are HTTP headers. The Accept header is mandatory since it tells the REST API what version of the API you want to use. In this case, it’s version 1.0. This is important because if the API changes in the future, you can continue to work with that version.

The Authorization header is also mandatory because it specifies the credentials that are used to access the Teradata Database. The REST API leverages HTTP Basic authentication for passing credentials.

The remaining part of the request is the message body. The message body is a JSON object that contains the mandatory "query" field with the SQL that will be executed. It can also contain other optional fields such as the ID of the session to use to execute the query, query bands to associate with the query, the desired result set format, and timeout and row limit options.

Of course you won’t have to craft HTTP requests by hand since HTTP APIs are available to do it for you. This is an example of a query submitted from a Web page using JQuery, a popular javascript library:

$.ajax({
	type: "POST",
	url: "https://myhost:1080/tdrest/systems/mysystem/queries",
	contentType: "application/json",
	headers: {
		'Accept': "application/vnd.com.teradata.rest-v1.0+json",
		'Authorization': 'Basic ' + btoa(username + ':' + password)
	},
	data: JSON.stringify({
		query: "SELECT * FROM Customers",
		format: 'object'
	})
});

Get the Results

Query results for the original request are returned in the HTTP Response. The "format" field in the request determines how they are returned. Three formats are supported:

  • "object" is the default format and causes results to be returned as a JSON object per row in which the column name is the field name and the column value is the field value.
  • "array" has results returned as a JSON array per row where each column value is an element in the array.
  • "CSV" is for a comma-separated values option in which results are returned as a separate line for each row with commas between the column values.

(See the "Object" Format Query" sidebar for an example of a response to the query in "object" format using data from the “Customer Information” table.)

The information returned from the query can be found in the "results" field. This field contains an array of results for each query submitted. Since only a single SELECT query was submitted, the "results" array contains only one element. The "data" field contains your query’s results and is an array of JSON objects since the "object" format was requested. If the "array" format had been selected, the data field would contain the following JSON array instead:

"data":[[1,"Alice","10795 Via Del Campo","San Diego","USA"],
	[2,"Bob",​"221B Baker Street",​"London","UK"]]

Development and Deployment Made Easy

The REST API for Teradata Database simplifies developing new types of applications that previously may not have been possible. Creating Web and mobile displays can be streamlined, with no time needed for building a middle-tier Web service. Users specializing in front-end Web development can build entire applications without any dependency on server-side development, reducing both the skills and the number of people required to build an application.

The deployment of applications can also be simplified. Since support for HTTP and JSON in programming languages is practically ubiquitous, applications can be written that no longer depend on a database driver, allowing the applications to be deployed anywhere easily. When it comes time for the next database upgrade, there’s no need to worry about having to upgrade database drivers either. You benefit now, and you’ll benefit later, too. 

Eric Scheie joined Teradata in 2000 and is the chief architect responsible for the REST API for Teradata Database.


Your Comment:
  
Your Rating:

Comments
Fuzzy Logix