Authentication and Database Queries Using Teradata AppCenter REST Services

Teradata has a new product that is included in its Teradata Vantage offering: Teradata AppCenter. I’ve been excited and playing with it for quite some time since it looks modern (and since some customers have started adopting it). It is also modern under the hood — it uses Angular, microservices, Docker, Kubernetes, and many other bells and whistles.

This article is different from my usual articles, I know. Bear with me. 🙂

AppCenter allows you to bundle your analytics use case into apps, deploy it, and share it with important stakeholders within your organisation. For example, if you have a set of SQL statements that processes some raw sales data, and in the end produces a report, you can turn it into an app in a few easy clicks with AppCenter. If you have a R Shiny dashboard, and you want to host and share it within the organisation, you can also do it on AppCenter.

Yes, AppCenter allows the creation, deployment, and management of simple apps made using SQL scripts, and more complex, custom, containerised apps made using any language. AppCenter also provides some REST services for authentication, sending queries, auditing, storing secrets, and some more. The shared services prevent developer from reinventing the wheel and provide convenience.

It also means that your app users only need to remember a single credentials — the one they use to log in to AppCenter.

I’ve learnt how to develop both kinds of apps, but I never tried going as far as using the shared services. And that’s what I tried to do recently. Once I knew where to look and whom to ask, it turned out to be relatively simple.

First Things First

In this article, I will discuss 3 essential things that a developer would do with AppCenter’s REST services:

1. Authenticating a user
2. Getting a list of registered databases (or in AppCenter’s term: System)
3. Sending a query to a system

Some notes before I start:

1. This article is written primarily for Angular developers.
2. The code shown here is simplified to ease understanding, and thus not recommended for complex applications.
3. This article assumes that SSO is not enabled for AppCenter.

The default configuration on AppCenter requires 2 levels of authentication. Firstly, a user needs to be authenticated against AppCenter. Secondly, a user then needs to be authenticated against the target database, or a System in AppCenter’s term.

Authenticating Against an App

This section discusses about the app-level authentication. The app-level authentication utilises the User Service, which is provided by the underlying Ecosystem Services. The only thing you need to do is to fire a request to https://${appCenterHost}/api/user/token. Replace the ${appCenterHost} with the URL or IP where AppCenter lives.

// TODO: Set the appCenterHost variable, or use an env variable
const url = `https://${appCenterHost}/api/user/token`;

The Request and Response

The request to be sent to User Service should contain the following JSON properties:

{
    username: string,
    password: string
}

If a user provides the wrong credentials, then the User Service will return a HTTP 401 error response. If a user is able to prove his/her identity, then the User Service will return a JSON payload with the following format:

{
  access_token: string,
  display_name: string,
  email: string,
  expires_at: Date,
  expires_in: number,
  refresh_token: string,
  token_in: string,
  token_type: string,
  username: string
}

The HTML

All right, now that we know what we need to send, and what we will get in return, let’s make a simple Angular form to do authentication. First, create a form which invokes login() function when submitted. Let’s name the form appAuthForm. Second, add username and password input text fields. Here’s the simplified HTML form code in Angular:

<form #appAuthForm="ngForm" (ngSubmit)="login(appAuthForm)">
    <label for="username">Username: </label>
    <input type="text" name="username" id="username" ngModel required />
    <label for="password">Password: </label>
    <input type="password" name="password" id="password" ngModel required />
    <button type="submit">Submit</button>
</form>

The Angular Code

Inside the Angular component code, write the login() function to handle the authentication process. In this example, I am using the default HttpClient module, which is injected to this._http property. I also import NgForm here to let TypeScript know the type of the function parameter.

import { NgForm } from '@angular/forms';
import { HttpClient } from '@angular/common/http';

const appCenterHost = ''; // TODO: Define the host name of AppCenter

// ... some other lines ...

login(form: NgForm) {
    const url = `https://${appCenterHost}/api/user/token`;

    // Only send the request if the form is valid
    if (form.valid) {
        this._http.post(url, form.value).subscribe(
            (response: AuthResponse) => {
              // Save the authentication response in a class property
              this.user = response;
              // Save the authentication response in a local storage
              localStorage.setItem('appcenter_user_auth', JSON.stringify(response));
              // TODO: Handle redirect?
            },
            // TODO: Handle error handling properly
            error => console.log(error)
        );
    }
}

The post() method of HttpClient returns an Observable, which can be subscribed usinng subscribe() method. In that method, create two anonymous functions to handle the positive and negative cases (error handling). You can always improve the authentication code above by using Services in Angular, or NgRx. Those two will come in handy when developing a larger, more complex, applications.

The AuthResponse used as the response type in the positive case is just a simple interface, so that IDE does not complain for the lack of typing. It also assists code completion. You can define the AuthResponse interface somewhere in the app code.

interface AuthResponse {
  access_token: string;
  display_name: string;
  email: string;
  expires_at: Date;
  expires_in: number;
  refresh_token: string;
  token_in: string;
  token_type: string;
  username: string;
}

The most important property here is the access_token property. The token will be embedded in the HTTP header in subsequent requests.

Getting a List of Systems

Assuming that we successfully authenticate the user against AppCenter, the next thing we need to do the systems our app can connect to. Quick refresher: systems are AppCenter’s term for databases / data stores. The root user of AppCenter manages the Systems, and the regular users can only consume them.

To get a list of Systems registered in AppCenter, we can use the System Service. System Service is located in the following URL:

const url = "https://${appCenterUrl}/api/system/systems";

To get the list of Systems, you need to fire a request to the URL above, while embedding the access token in the Authorization request header. We will also need to specify the system type we want to get, using the systemType property. The available value options are TERADATA, HIVE, and ASTER.

import { HttpClient } from '@angular/common/http';
// ... some other lines of code ...

// Get a list of systems
const url = "https://${appCenterUrl}/api/system/systems";
this._http.get(
    url,
    {
        headers: {
            Authorization: this.user.access_token
        },
        params: {
            systemType: 'TERADATA'
        }
    }
).subscribe(
    (systemResponse: [SystemResponse]) => {
      this.systems = systemResponse;
    }
);

If you successfully make a valid request, then the the System Service will return a JSON response with the following structure.

interface SystemResponse {
  id: string;
  nickname: string;
  host: string;
  port: number;
  system_type: string;
  platform_id: number;
  attributes: string;
  system_attributes: {
    attributes: {}
  };
}

The property in the data that we will need for sending a query request is the nickname. That property will need to be concatinated in the URL — we’ll discuss about it later.

Querying Data from a System

To send queries to a system, you will need to use the Query Service. Following is the URL that you can use in your app. You will need to populate variables of appCenterHost and systemName with some values. The systemName comes from the System Service.

const url = `https://${appCenterHost}/api/query/tdrest/systems/${systemName}/queries`;

Preparing the Authentication Header

The default database authentication mechanism is different from the app authentication. Instead of authenticating once before using the functionalities, in database authentication, a user always needs to send his/her database credentials for every submitted query. Yes, there is no one-time login under the hood. Aside from that, the user also needs to provide the access token that he/she get from the User Service. There is another difference: to do the database authentication we put the required credentials in the HTTP Header.

To make the request, we use the token that we get from the User Service in the X-AUTH-TOKEN header. The token, which is a long encoded string, needs to be preceded by Bearer string and a single space. We also need to supply Authorization header using basic HTTP authentication: encode the username and password to in base 64, and prefix the result with Basic string. In the current version of AppCenter, the Accept header for system authentication requests needs to be set to a certain value, too. The following snippet shows the whole header setup.

const header = {
      'X-AUTH-TOKEN': 'Bearer ' + access_token,
      Authorization: 'Basic ' + btoa(`${dbUsername}:${dbPassword}`),
      Accept: 'application/vnd.com.teradata.rest-v1.0+json, */*; q=0.01',
      'Content-Type': 'application/json'
};

The header above needs to be injected to every query request from your AppCenter containerized app. You’ll need to set the access_token, dbUsername, and dbPassword variables somewhere in your code.

Note: If your Teradata system has SSO set up and trusts the user service that issues the token, you can have query service use the JWT to log directly into the database without having to use a second set of credentials (the Basic authentication). See this page for the setup.

Building The Query Request Body

It’s extremely simple, you just need to supply a query property in the JSON payload. We’re going to supply a SELECT query here. I have not tried other kinds statements yet. That will be for next time.

const body = {
  query: `
    SELECT product_category_name as category,
      sum(sales_quantity) as qty
    FROM retail.sales_detail
    GROUP BY 1;
  `
};

Sending The Query Request

At this point, we have the url, the body, and the header populated. Now we can send the query request to the Query Service.

this._http.post(
  url,
  body,
  {
    headers: header
  }
).subscribe(
    // TODO: Process the response accordingly.
    (response: QueryResponse) => console.log(response),
    error => console.log(error)
);

The response contains data, and it is accesible in the results property. The structure of the results depends on your query, and data returned from the system.

interface QueryResponse {
  queryDuration: number;
  queueDuration: number;
  results: [QueryResult];
}

That’s it! It looks simple, right?