Introduction

IBM Cloud SQL Query is a cloud-native service that lets you use ANSI SQL to analyze rectangular data stored in IBM Cloud Object Storage. It is hosted on IBM Cloud and includes a publicly accessible REST API. The API documentation intends to help you get going with the SQL Query API, and it offers resources on how to operationalize it.

API endpoint

https://api.sql-query.cloud.ibm.com/

Running an SQL query

You can use the SQL Query service REST API to run queries and retrieve information about their status. This is especially helpful when writing code that automatically queries data.

SQL Query provides the following REST APIs:

  1. POST request to submit a new SQL job
  2. GET request to receive details about a specific SQL job
  3. GET request to receive a list of submitted SQL jobs

Before you can call any of the above REST APIs, create an IAM bearer token and have your SQL Query instance Cloud Resource Name (CRN) available. You find the CRN in the SQL Query instance dashboard that provides a copy button to get it into your clipboard.

Creating an IAM bearer token

The recommended method to retrieve an IAM token programmatically is to create an API key for your IBM Cloud identity and then use the IAM token API to exchange that key for a token. Each token is valid only for one hour, and after a token expires you have to create a new one if you want to continue using the API.

You can create a token in IBM Cloud or by using the IBM Cloud command line interface (CLI).

To create a token in the IBM Cloud:

  1. Log in to IBM Cloud and select Manage > Security > Platform API Keys.
  2. Create an API key for your own personal identity, copy the key value, and save it in a secure place. After you leave the page, you will no longer be able to access this value.
  3. With your API key, set up Postman or another REST API tool and run the command to the right.
  4. Use the value of the access_token property for your SQL Query API calls. Set the access_token value as the authorization header parameter for requests to the Watson Data APIs. The format is Authorization: Bearer <access_token_value_here>. For example:
    Authorization: Bearer eyJraWQiOiIyMDE3MDgwOS0wMDowMDowMCIsImFsZyI6IlJTMjU2In0...

To create a token by using the IBM Cloud CLI:

Follow the steps to install the CLI, log in to IBM Cloud, and get the token described here.

Curl command with API key to retrieve token

        curl "https://iam.cloud.ibm.com/identity/token" \
        -d "apikey=YOUR_API_KEY_HERE&grant_type=urn%3Aibm%3Aparams%3Aoauth%3Agrant-type%3Aapikey" \
        -H "Content-Type: application/x-www-form-urlencoded" \
        -H "Authorization: Basic Yng6Yng="

Response

        {
        "access_token": "eyJraWQiOiIyMDE3MDgwOS0wMDowMDowMCIsImFsZyI6...",
        "refresh_token": "zmRTQFKhASUdF76Av6IUzi9dtB7ip8F2XV5fNgoRQ0mbQgD5XCeWkQhjlJ1dZi8K...",
        "token_type": "Bearer",
        "expires_in": 3600,
        "expiration": 1505865282
        }

Error handling

This API uses standard HTTP response codes to indicate if a method completed successfully:

  • A 200 or 201 type response indicates success.
  • A 400 type response indicates an error in the specified parameters.
  • A 401 type response indicates an authorization error.
  • A 404 type response indicates that a resource related to this request was not found.

Rate limiting

Rate limits for API POST requests are enforced for each instance. If the number of POST requests for an instance reaches the request limit, no further requests are accepted until one of the SQL query jobs that are running for that instance finishes.

An HTTP status code of 429 indicates that the rate limit has been exceeded.

The number of requests that are allowed depends on your plan.

Methods

Run an SQL job

Runs an SQL job using the IBM Cloud SQL Query service and stores the result in a new CSV data set in IBM Cloud Object Storage. The FROM clause references rectangular data that is stored in a Parquet, CSV, or JSON file in IBM Cloud Object Storage. Click here for more information.

POST /sql_jobs
Request

Query Parameters

  • The cloud resource name (CRN) of the SQL query service instance.

SQL job specification

Example:
Response

Abridged information about an SQL job, including its identifier and processing status.

Status Code

  • Successful submission. Returns information about the SQL job, including its status and an identifier for future reference.

  • The request provided an invalid job specification or other invalid data. Returns details about the validation problem.

  • The request did not specify a valid bearer token for authentication.

  • You are not authorized to perform this action for the specified service instance.

  • The request required an unsupported output format. SQL Query produces JSON output with UTF-8 encoding. A request cannot be processed if its header specifies that this format is not accepted.

  • This instance is currently running its maximum number of query jobs. A new request can be accepted only after at least one of the currently running jobs has completed.

  • An internal error occurred while processing the request.

Example responses

Get information about recent SQL jobs

Returns information about recently submitted SQL jobs.

GET /sql_jobs
Request

Query Parameters

  • The cloud resource name (CRN) of the SQL query service instance.

Response

List of information about SQL jobs.

Status Code

  • Information about recently submitted SQL jobs. The list might be empty.

  • The request specified invalid data, for example, incorrect headers. Returns details about the validation problem.

  • The request did not specify a valid bearer token for authentication.

  • You are not authorized to perform this action for the specified service instance.

  • The request required an unsupported output format. SQL Query produces JSON output with UTF-8 encoding. A request cannot be processed if its header specifies that this format is not accepted.

  • An internal error occurred while processing the request.

Example responses

Get information about a specific SQL job

Returns information about the specified SQL job.

GET /sql_jobs/{job_id}
Request

Path Parameters

  • ID of the SQL job for which information is to be retrieved. This ID is returned when an SQL job is submitted, and when information about recently submitted SQL jobs is requested.

Query Parameters

  • The cloud resource name (CRN) of the SQL query service instance.

Response

Full information about an SQL job, including output or error information.

Status Code

  • Status of the specified SQL job.

  • The request specified invalid data, for example, incorrect headers. Returns details about the validation problem.

  • The request did not specify a valid bearer token for authentication.

  • You are not authorized to perform this action for the specified service instance.

  • No information was found for the specified job. Note that the system periodically deletes information about completed or failed jobs.

  • The request required an unsupported output format. SQL Query produces JSON output with UTF-8 encoding. A request cannot be processed if its header specifies that this format is not accepted.

  • An internal error occurred while processing the request.

Example responses