Skip to main content

Send queries using REST API

Overview​

Teradata Query Service is a REST API for Vantage that you can use to run standard SQL statements without managing client-side drivers. Use Query Service if you are looking to query and access the Analytics Database through a REST API.

This how-to provides examples of common use cases to help you get started with Query Service API.

Prerequisites​

Before starting, make sure you have:

Note

If you need a test instance of Vantage, you can provision one for free at https://clearscape.teradata.com

  • Query Service hostname and system name
  • Authorization credentials to connect to the database

Having trouble with the prerequisites? Contact Teradata for setup information.

Query Service API examples​

When using the examples, please keep in mind that:

  • The examples in this document use Python, and you can use these to create examples in your language of choice.
  • The examples provided here are complete and ready for you to use, although most require a little customization.
    • The examples in this document use the URL https://<QS_HOSTNAME>:1443/.
    • Replace the following variables with your own value:
      • <QS_HOSTNAME>: Server where Query Service is installed
      • <SYSTEM_NAME>: Preconfigured alias of the system
Note

If your Vantage instance is provided through ClearScape Analytics Experience,<QS_HOSTNAME>, is the host URL of your ClearScape Analytics Experience environment, <SYSTEM_NAME> is 'local'.

Connect to your Query Service instance​

Provide valid credentials to access the target Analytics Database using HTTP Basic or JWT authentication.

HTTP Basic authentication​

The database username and password are combined into a string ("username : password") which is then encoded using Base64. The API response contains the authorization method and encoded credentials.

Request

Response

JWT authentication​

Prerequisites:

  • The user must already exist in the database.
  • The database must be JWT enabled.

Request

Response

Make a simple API request with basic options​

In the following example, the request includes:

  • SELECT * FROM DBC.DBCInfo: The query to the system with the alias <SYSTEM_NAME>.

  • 'format': 'OBJECT': The format for response. The formats supported are: JSON object, JSON array, and CSV.

    Note

    The JSON object format creates one JSON object per row where the column name is the field name, and the column value is the field value.

  • 'includeColumns': true: The request to include column metadata, such as column names and types, in the response.

  • 'rowLimit': 4: The number of rows to be returned from a query.

Request

Response

For response parameters, see Query Service Installation, Configuration, and Usage Guide.

Request a response in CSV format​

To return an API response in CSV format, set the *format* field in the request with the value *CSV*.

The CSV format contains only the query results and not response metadata. The response contains a line for each row, where each line contains the row columns separated by a comma. The following example returns the data as comma-separated values.

Request

Response

Use explicit session to submit a query​

Use explicit sessions when a transaction needs to span multiple requests or when using volatile tables. These sessions are only reused if you reference the sessions in a query request. The request is queued if a request references an explicit session already in use.

  1. Create a session Send a POST request to the /system/<SYSTEM_NAME>/sessions endpoint. The request creates a new database session and returns the session details as the response.

    In the following example, the request includes 'auto_commit': True - the request to commit the query upon completion.

    Request

    Response

  2. Use the session created in Step 1 to submit queries

    Send a POST request to the /system/<SYSTEM_NAME>/queries endpoint.

    The request submits queries to the target system and returns the release and version number of the target system.

    In the following example, the request includes:

    • SELECT * FROM DBC.DBCInfo: The query to the system with the alias <SYSTEM_NAME>.
    • 'format': 'OBJECT': The format for response.
    • 'Session' : <Session ID>: The session ID returned in Step 1 to create an explicit session.

    Request

    Response

Use asynchronous queries​

Use asynchronous queries when a system or network performance is affected by querying a large group of data or long running queries.

  1. Submit asynchronous queries to the target system and retrieve a Query ID Send a POST request to the /system/<SYSTEM_NAME>/queries endpoint. In the following example, the request includes:

    • SELECT * FROM DBC.DBCInfo: The query to the system with the alias <SYSTEM_NAME>.
    • 'format': 'OBJECT': The format for response.
    • 'spooled_result_set': True: The indication that the request is asynchronous.

    Request

    Response

  2. Get query details using the ID retrieved from Step 1

    Send a GET request to the /system/<SYSTEM_NAME>/queries/<queryID> endpoint, replacing <queryID> with the ID retrieved from Step 1.

    The request returns the details of the specific query, including queryState, queueOrder, queueDuration, and so on. For a complete list of the response fields and their descriptions, see Query Service Installation, Configuration, and Usage Guide.

    Request

    Response

  3. View resultset for asynchronous query

    Send a GET request to the /system/<SYSTEM_NAME>/queries/<queryID>/results endpoint, replacing <queryID> with the ID retrieved from Step 1.

    The request returns an array of the result sets and update counts produced by the submitted query.

    Request

    Response

Get a list of active or queued queries​

Send a GET request to the /system/<SYSTEM_NAME>/queries endpoint. The request returns the IDs of active queries.

Request

Response

Resources​

Note

If you have any questions or need further assistance, please visit our community forum where you can get support and interact with other community members.

Also of interest