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:
- Access to a VantageCloud system where Query Service is provisioned, or a VantageCore with Query Service enabled connectivity. If you are an admin and need to install Query Service, see Query Service Installation, Configuration, and Usage Guide.
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
- The examples in this document use the URL
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.HinweisThe 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.
-
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
-
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.
-
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
-
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
-
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
- Features, examples, and references: Query Service Installation, Configuration, and Usage Guide
- Query Service API OpenAPI Specification
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.