Teradata Query Service (@projectVersion@)

To submit an SQL request to a Teradata Database using this web service, you send a POST request to the /system/[systemName]/queries API endpoint, replacing [systemName] with the nickname of a system that has been defined by an administrator using the System Service.

Result Set Formats

The format of the response to an SQL request depends on the requested format. Three formats are supported: object, array, and csv. Both object and array options generate JSON responses, while the csv option generates a comma separated value response.

JSON Object

JSON Object is the default result format. This format creates a JSON object per row with the column names as the field names and the column values as the field values.

curl --insecure -X POST \
"$HOST:1443/api/query/systems/prod/queries" \
-H 'Accept: application/vnd.com.teradata.rest-v1.0+json, */*; q=0.01' \
-H 'Content-Type: application/json' \
-H "Authorization: Basic ZGJjOmRiYw==' \
-d '{"query": "SELECT * FROM dbc.dbcinfo"}'

In the example above, we are submitting a SELECT * FROM DBC.DBCInfo query to the system nicknamed "prod" and using TD2 authentication with the username and password "dbc" ("ZGJjOmRiYw==" is "dbc:dbc" Base64 encoded). The results will be returned in the default JSON Object format:

{
  "queryDuration": 45,
  "queueDuration": 3,
  "results": [
  {
    "data": [
    {
      "InfoData": "16.20.00.00",
      "InfoKey": "VERSION"
    },
    {
      "InfoData": "Japanese",
      "InfoKey": "LANGUAGE SUPPORT MODE"
    },
    {
      "InfoData": "16.20.00.00",
      "InfoKey": "RELEASE"
    }
    ],
    "resultSet": true,
    "rowCount": 3,
    "rowLimitExceeded": false
  }
  ]
}

The JSON object response contains the following fields:

• queueDuration How long the request was queued in milliseconds.

• queryDuration How long the request ran once submitted in milliseconds.

• results An array of the result sets, update counts produced by the submitted SQL. The array will have more than one element if the submitted SQL contains more than one statement or if a stored procedure was called that returns more than one result set. The following fields may be present inside of a result array element.

  • resultSet - Indicates if the result is a result set (true) or an update count (false).
  • columns - Contains an array of the columns comprising the result set. Each column contains a name and type field containing the column's name and SQL type respectively (only present if resultSet is true and include_columns was true when the request was submitted).
  • outParams - An object of key value pairs representing the output parameters from a stored procedure.
  • data - Contains the data produced by the query. The format depends on the value of the format attribute specified with the request (e.g. an array of arrays, or an array of objects). The data field is only present when resultSet is true.
  • rowCount - If a result set, the number of rows returned up to the row limit if set, else the update count.
  • rowLimitExceeded - Flags if the number of rows in the result exceeded the number of rows specified in the rowLimit.

• responseError This field will typically not be present. It is only present if an error occurs while the query is in the RESPONDING state. In this case, a successful status would have already been sent to the client, which is why any responseErrors are included as the last field in the JSON response.

JSON Array

The JSON Array format is similar to JSON object format except instead of a JSON object per row, there is a JSON array per row where each column value is an element in the array.

curl --insecure -X POST \
"$HOST:1443/api/query/systems/prod/queries" \
-H 'Accept: application/vnd.com.teradata.rest-v1.0+json, */*; q=0.01' \
-H 'Content-Type: application/json' \
-H "Authorization: Basic ZGJjOmRiYw==' \
-d '{"query": "SELECT * FROM dbc.dbcinfo", "format": "array", "include_columns": true }'

The request above demonstrates several availble options:

• The response will be in JSON array format ("format": "array") .
• The response will include column information ("include_columns": true).

Here are sample results:

{
  "queryDuration": 11,
  "queueDuration": 3,
  "results": [
  {
    "columns": [
    {
      "name": "InfoKey",
      "type": "VARCHAR"
    },
    {
      "name": "InfoData",
      "type": "VARCHAR"
    }
    ],
    "data": [
      [
      "VERSION",
      "16.20.00.00"
      ],
      [
        "LANGUAGE SUPPORT MODE",
        "Japanese"
        ],
      [
        "RELEASE",
        "16.20.00.00"
        ]
      ],
    "resultSet": true,
    "rowCount": 3,
    "rowLimitExceeded": false
  }
  ]
}

Comma Separated Value (CSV)

CSV format does not contain any meta data about the response and simply contains the query results. The response contains a line for each row where each line contains the row's column values separated by a comma.

curl --insecure -X POST \
"$HOST:1443/systems/prod/queries" \
-H 'Accept: application/vnd.com.teradata.rest-v1.0+json, */*; q=0.01' \
-H 'Content-Type: application/json' \
-H "Authorization: Basic ZGJjOmRiYw==' \
-d '{"query": "SELECT * FROM dbc.dbcinfo", "format": "csv", "include_columns": true}'

The output for CSV format will look like this:

InfoKey,InfoData
VERSION,16.20.00.00
LANGUAGE SUPPORT MODE,Japanese
RELEASE,16.20.00.00

The first row contains the column names (because we requested include_columns).

Managing Database Sessions

There are two ways that database sessions are created by the Query Service. The first way is when a client submits a query without referencing a session ID. If an idle session does not already exist for the specified credentials, a new session is created based on the default settings configured for the target system. This type of session is called an implicit session. The second way a session is created is if a client calls POST /system/[systemName]/sessions to open a session. This type of session is called an explicit session.

Each session remains open until the session is idle for the configured maxIdleTime or until closed by calling DELETE /system/[systemName]/sessions/[sessionId]. Implicit sessions are reused if they are idle and if the credentials specified by the client are the same as when the session was created. If there are no sessions that match that criteria, then a new implicit session can be created, up to the maximum number of implicit sessions allowed per user. If the maximum number of implicit sessions are reached and none are idle, then the request will be queued.

Explicit sessions are only used if a client references them in a query request. If a request references an explicit session that is already in use, the request will be queued. Explicit sessions should be used when a transaction needs to span multiple requests or when using volatile tables

API's for fetching GeneralConfig of Teradata database configuration.

API's for admin users.

API's to install/update/delete certiicates.

API's for fetching metadata about databases, tables, macros, etc.

API's for submitting and managing queries.

API's for managing explict sessions. Explicit sessions are an optional feature that give you complete control over the creation, usage, and removal of database sessions. You would want to use excplicit sessions if you are using session specific features such as temporary tables or transactions that span multiple statements.