The following sections walk through calls you can make using the /queries
endpoint in the Query Service API. Each call includes the general API format, a sample request showing required headers, and a sample response.
You can retrieve a list of all queries for your organization by making a GET request to the /queries
endpoint.
API format
GET /queries
GET /queries?{QUERY_PARAMETERS}
{QUERY_PARAMETERS}
: (Optional) Parameters added to the request path which configure the results returned in the response. Multiple parameters can be included, separated by ampersands (&
). The available parameters are listed below.Query parameters
The following is a list of available query parameters for listing queries. All of these parameters are optional. Making a call to this endpoint with no parameters will retrieve all queries available for your organization.
Parameter | Description |
---|---|
orderby |
Specifies the field by which to order results. The supported fields are created and updated . For example, orderby=created will sort results by created in ascending order. Adding a - before created (orderby=-created ) will sort items by created in descending order. |
limit |
Specifies the page size limit to control the number of results that are included in a page. (Default value: 20) |
start |
Specify an ISO format timestamp to order the results. If no start date is specified, the API call will return the oldest created query first, then continue to list more recent results. ISO timestamps allow for different levels of granularity in the date and time. The basic ISO timestamps take the format of: 2020-09-07 to express the date September 7, 2020. A more complex example would be written as 2022-11-05T08:15:30-05:00 and corresponds to November 5, 2022, 8:15:30 am, US Eastern Standard Time. A timezone can be provided with a UTC offset and is denoted by the suffix “Z” (2020-01-01T01:01:01Z ). If no timezone is provided, it defaults to zero. |
property |
Filter results based on fields. The filters must be HTML escaped. Commas are used to combine multiple sets of filters. The supported fields are created , updated , state , and id . The list of supported operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), == (equal to), != (not equal to), and ~ (contains). For example, id==6ebd9c2d-494d-425a-aa91-24033f3abeec will return all queries with the specified ID. |
excludeSoftDeleted |
Indicates whether a query which has been soft deleted should be included. For example, excludeSoftDeleted=false will include soft deleted queries. (Boolean, default value: true) |
excludeHidden |
Indicates whether non-user driven queries should be displayed. Having this value set to false will include non-user driven queries, such as CURSOR definitions, FETCH, or metadata queries. (Boolean, default value: true) |
isPrevLink |
The isPrevLink query parameter is used for pagination. Results of the API call are sorted using their created timestamp and the orderby property. When navigating the pages of results, isPrevLink is set to true when paging backwards. It reverses the order of the query. See “next” and “prev” links as examples. |
Request
The following request retrieves the latest query created for your organization.
curl -X GET https://platform.adobe.io/data/foundation/query/queries?limit=1 \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-sandbox-name: {SANDBOX_NAME}'
Response
A successful response returns HTTP status 200 with a list of queries for the specified organization as JSON. The following response returns the latest query created for your organization.
{
"queries": [
{
"isInsertInto": false,
"request": {
"dbName": "prod:all",
"sql": "SELECT *\nFROM\n accounts\nLIMIT 10\n"
},
"state": "SUCCESS",
"rowCount": 0,
"errors": [],
"isCTAS": false,
"version": 1,
"id": "9957bd7f-2244-4fd5-91bc-077d7df1d8e5",
"elapsedTime": 28,
"updated": "2019-12-06T22:00:17.390Z",
"client": "Adobe Query Service UI",
"userId": "{USER_ID}",
"created": "2019-12-06T22:00:17.362Z",
"_links": {
"self": {
"href": "https://platform.adobe.io/data/foundation/query/queries/9957bd7f-2244-4fd5-91bc-077d7df1d8e5",
"method": "GET"
},
"soft_delete": {
"href": "https://platform.adobe.io/data/foundation/query/queries/9957bd7f-2244-4fd5-91bc-077d7df1d8e5",
"method": "PATCH",
"body": "{ \"op\": \"soft_delete\"}"
},
"referenced_datasets": [
{
"id": "5b2bdd32230d4401de87397c",
"href": "https://platform.adobe.io/data/foundation/catalog/dataSets/5b2bdd32230d4401de87397c"
}
]
}
}
],
"_page": {
"orderby": "-created",
"start": "2019-12-06T22:00:17.362Z",
"next": "2019-08-01T00:14:21.748Z",
"count": 1
},
"_links": {
"next": {
"href": "https://platform.adobe.io/data/foundation/query/queries?orderby=-created&start=2019-08-01T00:14:21.748Z"
},
"prev": {
"href": "https://platform.adobe.io/data/foundation/query/queries?orderby=-created&start=2019-12-06T22:00:17.362Z&isPrevLink=true"
}
},
"version": 1
}
You can create a new query by making a POST request to the /queries
endpoint.
API format
POST /queries
Request
The following request creates a new query, with an SQL statement provided in the payload:
curl -X POST https://platform.adobe.io/data/foundation/query/queries \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'Content-Type: application/json' \
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-sandbox-name: {SANDBOX_NAME}' \
-d '{
"dbName": "prod:all",
"sql": "SELECT account_balance FROM user_data WHERE user_id='$user_id';",
"queryParameters": {
user_id : {USER_ID}
}
"name": "Sample Query",
"description": "Sample Description"
}
The request example below creates a new query using an existing query template ID.
curl -X POST https://platform.adobe.io/data/foundation/query/queries \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'Content-Type: application/json' \
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-sandbox-name: {SANDBOX_NAME}' \
-d '{
"dbName": "prod:all",
"templateID": "f7cb5155-29da-4b95-8131-8c5deadfbe7f",
"name": "Sample Query",
"description": "Sample Description"
}
Property | Description |
---|---|
dbName |
The name of the database you are creating a SQL query for. |
sql |
The SQL query you want to create. |
name |
The name of your SQL query. |
description |
The description of your SQL query. |
queryParameters |
A key value pairing to replace any parameterized values in the SQL statement. It is only required if you are using parameter replacements within the SQL you provide. No value type checking will be done on these key value pairs. |
templateId |
The unique identifier for a pre-existing query. You can provide this instead of an SQL statement. |
insertIntoParameters |
(Optional) If this property is defined, then this query will be converted into an INSERT INTO query. |
ctasParameters |
(Optional) If this property is defined, this query will be converted into a CTAS query. |
Response
A successful response returns HTTP status 202 (Accepted) with details of your newly created query. Once the query is finished activating and has successfully run, the state
will change from SUBMITTED
to SUCCESS
.
{
"isInsertInto": false,
"request": {
"dbName": "prod:all",
"sql": "SELECT * FROM accounts;",
"name": "Sample Query",
"description": "Sample Description"
},
"state": "SUBMITTED",
"rowCount": 0,
"errors": [],
"isCTAS": false,
"version": 1,
"id": "4d64cd49-cf8f-463a-a182-54bccb9954fc",
"elapsedTime": 0,
"updated": "2020-01-08T21:47:46.865Z",
"client": "API",
"userId": "{USER_ID}",
"created": "2020-01-08T21:47:46.865Z",
"_links": {
"self": {
"href": "https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc",
"method": "GET"
},
"soft_delete": {
"href": "https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc",
"method": "PATCH",
"body": "{ \"op\": \"soft_delete\"}"
},
"cancel": {
"href": "https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc",
"method": "PATCH",
"body": "{ \"op\": \"cancel\"}"
}
}
}
You can use the value of _links.cancel
to cancel your created query.
You can retrieve detailed information about a specific query by making a GET request to the /queries
endpoint and providing the query’s id
value in the request path.
API format
GET /queries/{QUERY_ID}
Property | Description |
---|---|
{QUERY_ID} |
The id value of the query you want to retrieve. |
Request
curl -X GET https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-sandbox-name: {SANDBOX_NAME}'
Response
A successful response returns HTTP status 200 with detailed information about the specified query.
{
"isInsertInto": false,
"request": {
"dbName": "prod:all",
"sql": "SELECT * FROM accounts;",
"name": "Sample Query",
"description": "Sample Description"
},
"state": "SUBMITTED",
"rowCount": 0,
"errors": [],
"isCTAS": false,
"version": 1,
"id": "4d64cd49-cf8f-463a-a182-54bccb9954fc",
"elapsedTime": 0,
"updated": "2020-01-08T21:47:46.865Z",
"client": "API",
"userId": "{USER_ID}",
"created": "2020-01-08T21:47:46.865Z",
"_links": {
"self": {
"href": "https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc",
"method": "GET"
},
"soft_delete": {
"href": "https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc",
"method": "PATCH",
"body": "{ \"op\": \"soft_delete\"}"
},
"cancel": {
"href": "https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc",
"method": "PATCH",
"body": "{ \"op\": \"cancel\"}"
}
}
}
You can use the value of _links.cancel
to cancel your created query.
You can request to cancel or soft delete a specified query by making a PATCH request to the /queries
endpoint and providing the query’s id
value in the request path.
API format
PATCH /queries/{QUERY_ID}
Parameter | Description |
---|---|
{QUERY_ID} |
The id value of the query you want to perform the operation on. |
Request
This API request uses the JSON Patch syntax for its payload. For more information on how JSON Patch works, please read the API fundamentals document.
curl -X PATCH https://platform.adobe.io/data/foundation/query/queries/4d64cd49-cf8f-463a-a182-54bccb9954fc \
-H 'Authorization: Bearer {ACCESS_TOKEN}' \
-H 'Content-Type: application/json',
-H 'x-gw-ims-org-id: {ORG_ID}' \
-H 'x-api-key: {API_KEY}' \
-H 'x-sandbox-name: {SANDBOX_NAME}'
-d '{
"op": "cancel"
}'
Property | Description |
---|---|
op |
The type of operation to perform on the resource. Accepted values are cancel and soft_delete . To cancel the query, you must set the op parameter with the value cancel . Note that the soft delete operation stops the query from being returned on GET requests but does not delete it from the system. |
Response
A successful response returns HTTP status 202 (Accepted) with the following message:
{
"message": "Query cancel request received",
"statusCode": 202
}