Run an SQL job
POST /sql_jobs
Runs an SQL job and stores the result either in IBM Cloud Object Storage or IBM® Db2® on Cloud. The `FROM` clause references rectangular data that is stored in Parquet, CSV, ORC, AVRO or JSON format in IBM Cloud Object Storage. For more information, see the <a href="https://cloud.ibm.com/docs/sql-query/sql-query.html#overview">SQL Query</a> overview documentation.
Request
Request Body
sql_job_specification
SQL job specification
statement
Required *
string
This is the SQL statement to be submitted. The table names specified in the FROM clause must correspond to objects in one or more IBM Cloud Object Storage instances or to table definitions specified in the database catalog. The INTO clause of the query indicates the endpoint, bucket, and (optionally) subfolder in IBM Cloud Object Storage or a Db2 table URI, in which the query result is to be stored. Alternatively, a Database Definition Language statement for table or index management can be specified. Within the specified target, each result is stored in a separate subfolder with a name that indicates the job ID. The job ID of a query is returned by the GET endpoint.
resultset_target
string
This field provides an alternative way to specify the target URI for a query. It is supported to preserve backward compatibility and will be removed in a future API version. Use the INTO clause of the SQL query to specify the target URI instead.
Path Parameters
Query Parameters
instance_crn
Required *
string
The cloud resource name (CRN) of the SQL query service instance.
Response
201 - Successful submission. Returns information about the SQL job, including its status and an identifier for future reference.
job_id
string
Identifier for an SQL job.
status
string
Execution status of an SQL job.
user_id
string
ID of the user who submitted an SQL job.
submit_time
string
Timestamp indicating when an SQL job was accepted by the service.
has_hints
boolean
Boolean indicating when an SQL job has an improvement hint.
400 - The request provided an invalid job specification or other invalid data. Returns details about the validation problem.
trace
string
Optional identification of the request that caused the error.
errors
array
List of errors that were encountered during API processing.
status_code
number
The HTTP status code for this response
401 -
403 -
406 -
429 - This instance is currently running its maximum number of SQL jobs. A new job can be accepted only after at least one of the currently running jobs completes.
trace
string
Optional identification of the request that caused the error.
errors
array
List of errors that were encountered during API processing.
status_code
number
The HTTP status code for this response
500 -
Get information about recent SQL jobs
GET /sql_jobs
Returns information about recently submitted SQL jobs.
Request
Path Parameters
Query Parameters
instance_crn
Required *
string
The cloud resource name (CRN) of the SQL query service instance.
Response
200 - Information about recently submitted SQL jobs. The list might be empty.
jobs
array
The SQL jobs.
400 -
401 -
403 -
406 -
500 -
Get information about a specific SQL job
GET /sql_jobs/{job_id}
Returns information about the specified SQL job.
Request
Path Parameters
job_id
Required *
string
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
instance_crn
Required *
string
The cloud resource name (CRN) of the SQL query service instance.
Response
200 - Status of the specified SQL job.
job_id
string
Identifier for an SQL job.
status
string
Execution status of an SQL job.
user_id
string
ID of the user who submitted an SQL job.
submit_time
string
Timestamp indicating when an SQL job was accepted by the service.
statement
string
The SQL query that the job processes.
plan_id
string
The service plan id of the instance.
resultset_format
string
Format of the query result.
resultset_location
string
A URI that indicates where the query result is stored. This URI can be used as input for another SQL query. The result comprises all objects that have a name with this URI as its prefix.
end_time
string
Timestamp indicating when the job finished processing.
rows_returned
number
Number of rows returned.
rows_read
number
Number of rows read.
bytes_read
number
Number of bytes read.
objects_skipped
number
Number of objects skipped using index management.
objects_qualified
number
Number of objects qualified using index management.
error
string
An error that was encountered while processing the job.
error_message
string
Detailed information about the error.
hints
array
Suggests possible optimizations for a query.
400 -
401 -
403 -
404 - No information was found for the specified job. Note that the system periodically deletes information about completed or failed jobs.
trace
string
Optional identification of the request that caused the error.
errors
array
List of errors that were encountered during API processing.
status_code
number
The HTTP status code for this response
406 -
500 -
List catalog tables
GET /tables
Retrieve a list of the first 100 tables that are defined for the given instance in the catalog.
Request
Path Parameters
Query Parameters
instance_crn
Required *
string
The cloud resource name (CRN) of the SQL query service instance.
name_pattern
string
A table name pattern for filtering the tables that should be listed. The pattern follows Hive syntax conventions and can include asterisks as wildcards and vertical bars to separate alternatives.
type
string
A table type for filtering the tables that should be listed, can be "table" or "view".
Response
200 - Names of defined catalog tables. The list might be empty.
tables
array
The table names.
tables_metadata
array
Metadata about the returned tables.
400 -
401 -
403 -
406 -
429 -
500 -
Get information about a specific catalog table
GET /tables/{table_name}
Returns information about the specified catalog table.
Request
Path Parameters
table_name
Required *
string
Name of the catalog table for which information is to be retrieved. Table names are case-insensitive and must only contain alphabetic and numeral characters, and underscore (_).
Query Parameters
instance_crn
Required *
string
The cloud resource name (CRN) of the SQL query service instance.
Response
200 - Information about the specified table.
name
string
The name of a catalog table.
type
string
The type of a catalog table (for example, "TABLE" or "VIEW").
columns
array
The columns of the table.
400 -
401 -
403 -
404 - The specified table is not in the catalog
trace
string
Optional identification of the request that caused the error.
errors
array
List of errors that were encountered during API processing.
status_code
number
The HTTP status code for this response