Adobe Suite Queries API

openapi: 3.2.0
info:
  title: Query Service Queries API
  description: "Use the Adobe Experience Platform Query Service API to query Platform data with standard SQL. With this serverless tool, you can join any datasets in the [Experience Platform Data Lake](https://experienceleague.adobe.com/docs/experience-platform/landing/license/data-management-best-practices.html#understanding-adobe-experience-platform-data-storage) and capture the query results as a new dataset for use in reporting, Data Science Workspace, or for ingestion into Real-time Customer Profile.\n\n**Related Documentation**:\n  * [Query Service documentation](http://www.adobe.com/go/query-service-home-en)\n\n**Visualize API calls with Postman (a free, third-party software)**:\n  * [Query Service API Postman collection on GitHub](https://github.com/adobe/experience-platform-postman-samples/blob/master/apis/experience-platform/Query%20Service%20API.postman_collection.json)\n  * [Video guide for creating the Postman environment](https://video.tv.adobe.com/v/28832)\n  * [Steps for importing environments and collections in Postman](https://learning.getpostman.com/docs/postman/collection_runs/using_environments_in_collection_runs/)\n\n**API paths**:\n  * PLATFORM Gateway URL: https://<span>platform.adobe.io/\n  * Base path for this API: /data/foundation/query\n  * Example of a complete path: https://<span>platform.adobe.io/data/foundation/query/queries\n\n**Required headers**:\n  * All calls require the headers `Authorization`, `x-gw-ims-org-id`, and `x-api-key`. For more information on how to obtain these values, see the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).\n  * All resources in Experience Platform are isolated to specific virtual sandboxes. All requests to Platform APIs require the header `x-sandbox-name` whose value is the all-lowercase name of the sandbox the operation will take place in (for example, \"prod\"). See the [sandboxes overview](https://adobe.com/go/sandbox-overview-en) for more information.\n  * All requests with a payload in the request body (such as POST, PUT, and PATCH calls) must include the header `Content-Type` with a value of `application/json`.\n\n**API error handling**:\n  * Refer to the Experience Platform API troubleshooting guide for [FAQs](https://experienceleague.adobe.com/docs/experience-platform/landing/troubleshooting.html?lang=en#faq), [API status codes](https://experienceleague.adobe.com/docs/experience-platform/landing/troubleshooting.html?lang=en#api-status-codes), and [request header errors](https://experienceleague.adobe.com/docs/experience-platform/landing/troubleshooting.html?lang=en#request-header-errors).\n  * Refer to the Query Service troubleshooting guide for [common query related FAQs](https://experienceleague.adobe.com/docs/experience-platform/query/troubleshooting-guide.html)."
  version: '1.0'
servers:
- url: //{environment}.adobe.io/data/foundation/query
  variables:
    environment:
      default: platform
      enum:
      - platform
      - platform-stage
tags:
- name: Queries
  description: The queries endpoint uses standard SQL to query data held in Adobe Experience Platform. For example, you can join any number of datasets in the data lake and capture the results as a new dataset.
paths:
  /queries:
    get:
      tags:
      - Queries
      summary: List queries
      operationId: listQueries
      description: This request provides a list of queries for your organization.
      parameters:
      - name: Authorization
        in: header
        description: The access token which can be copied from your Experience Platform integration, prefixed with "Bearer " (for example, `Bearer {ACCESS_TOKEN}`). For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-gw-ims-org-id
        in: header
        description: The Organization ID which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-api-key
        in: header
        description: The API key which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-sandbox-name
        in: header
        description: The name of the sandbox in which the operation will take place. See the [sandboxes overview documentation](https://adobe.com/go/sandbox-overview-en) for more information.
        required: true
        schema:
          type: string
      - name: x-request-id
        in: header
        description: An optional header that serves as a tracking ID for the request. If one is not provided on the request, the server generates one automatically.
        schema:
          type: string
      - name: User-Agent
        in: header
        description: The type of the client making the HTTP request.
        schema:
          type: string
      - name: orderby
        in: query
        description: The field that specifies the order of results. The supported fields are `created` and `updated`. Prepend the property name with `+` for ascending and `-` for descending order. The default is `-created`. Note that the plus sign (`+`) has to be escaped with `%2B`. For example `%2Bcreated` is the value for an ascending created order.
        schema:
          type: string
      - name: limit
        in: query
        description: Restrict the maximum number of records that should be returned for a given request.
        schema:
          type: integer
      - name: start
        in: query
        description: The timestamp used with `orderby` to order the returned results.
        schema:
          type: string
      - name: property
        in: query
        description: "Comma-separated filters. Multiple filters separated by commas can be passed. The examples below are given with unsafe HTML characters for clarity, but all content MUST be HTML escaped.\n\n\nThe following list of properties allow filtering with all operators: `created`, `updated`, `state`, `id`.\n\n\nThe following is a list of supported operators: `>` (greater than) `<` (less than), `>=` (greater than or equal to), `<=` (less than or equal to) `==` (equal to), `!=` (not equal to), `~` (contains)\n\n\nExamples with filters that support all operators:\n * Get query with a match on id:\n `id==6ebd9c2d-494d-425a-aa91-24033f3abeec`\n * Get queries created after a given timestamp, and with a specific state:\n `created>=2017-04-05T13:30:00Z,state==IN_PROGRESS`\n Filtering is also allowed with limited operators on the following properties:\n * The `referenced_datasets` and `userId` property. This filter only allows the `==` equal to operator, and up to 10 dataset IDs can be included by using the `||` logical OR operator. \n Example: `referenced_datasets==aabbcc||ddeeff`\n * The SQL property. This filter only allows the contains operator (`~`), and only one SQL string WITHOUT a comma (`,`) can be included.\n Example: `sql~SELECT c1`\n * the `templateId` property: This filter only allows the equal to operator (`==`), and only one template ID can be included.\n Example: `templateId=3e61c592-05de-422e-99be-24712c07fc29`\n * the `templateName` property: This filter only allows the contains operator (`~`), and only one templateName can be included.\n Example: `templateName~sample-template-name` \n * The `client` property. This filter only allows the `==` equal to operator, and only one SQL string WITHOUT a comma (`,`) can be included.\n Example: `client==API`\n * The `scheduleId` and `scheduleRunId` properties. These filters only allow the `==` equal to operator.\n Example: `scheduleId==bWFudWFsX18yMDE4LTEwLTEyVDA0OjM2OjE5LjkzNzI2Nw==,scheduleRunId==manual__2018-10-12T05:11:40.816520`\n Example of a complex search with multiple filters:\n * Get me queries that utilize two specific datasets, submitted by a particular user, and that include the string `SELECT c1` in the SQL body: `referenced_datasets==aabbcc||ddeeff,userId=1234@AdobeId,sql~SELECTc1`"
        schema:
          type: string
      - name: excludeSoftDeleted
        in: query
        description: Whether to include any queries that have been soft deleted. Defaults to true. Soft-deleting stops the query from being returned on GET requests to the `/queries` endpoint but does not delete it from the system.
        schema:
          type: boolean
      - name: excludeHidden
        in: query
        description: Whether to include any low-level queries issued as part of the Postgres protocol that were not user driven. Examples include CURSOR definitions, FETCH, and Metadata queries. `true` is the default value.
        schema:
          type: boolean
      - name: isPrevLink
        in: query
        description: '`isPrevLink` is used in pagination. Results are sorted using the `created` timestamp and the `orderby` property.  When navigating 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.'
        schema:
          type: boolean
      responses:
        200:
          x-summary: Success
          description: Query List Response
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/query_list'
        401:
          x-summary: Unauthorized
          description: Unauthorized
          content: {}
        404:
          x-summary: Not found
          description: Not Found
          content: {}
        '500':
          x-summary: Internal server error
          description: There was an internal server error. Try the request again in a few minutes, and contact Adobe Support if the problem persists.
          content: {}
    post:
      tags:
      - Queries
      summary: Create a query
      operationId: createQuery
      description: This request creates a new query configured by the values provided in the payload. The query required in the request body can be either an SQL statement or a template ID.
      parameters:
      - name: x-gw-ims-org-id
        in: header
        description: The Organization ID which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-api-key
        in: header
        description: The API key which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-sandbox-name
        in: header
        description: The name of the sandbox in which the operation will take place. See the [sandboxes overview documentation](https://adobe.com/go/sandbox-overview-en) for more information.
        required: true
        schema:
          type: string
      - name: x-request-id
        in: header
        description: An optional header that serves as a tracking ID for the request. If one is not provided on the request, the server generates one automatically.
        schema:
          type: string
      - name: User-Agent
        in: header
        description: The type of the client making the HTTP request.
        schema:
          type: string
      - name: Content-Type
        in: header
        description: The type of content being sent in the body of the request. The value should be 'application/json'.
        required: true
        schema:
          type: string
      requestBody:
        description: The request body for creating a query.
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/query_def'
        required: true
      responses:
        202:
          x-summary: Query status
          description: Query status
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/query'
        400:
          x-summary: Bad request
          description: Bad Request. The payload may be malformed, or the SQL statement has been found to be syntactically incorrect.
          content: {}
        401:
          x-summary: Unauthorized
          description: Unauthorized
          content: {}
        '500':
          x-summary: Internal server error
          description: There was an internal server error. Try the request again in a few minutes, and contact Adobe Support if the problem persists.
          content: {}
      x-codegen-request-body-name: query
  /queries/{queryid}:
    get:
      tags:
      - Queries
      summary: Retrieve a query
      operationId: retrieveQuery
      description: This endpoint provides the query information that relates to the query ID provided in the request.
      parameters:
      - name: Authorization
        in: header
        description: The access token which can be copied from your Experience Platform integration, prefixed with "Bearer " (for example, `Bearer {ACCESS_TOKEN}`). For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-gw-ims-org-id
        in: header
        description: The Organization ID which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-api-key
        in: header
        description: The API key which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-sandbox-name
        in: header
        description: The name of the sandbox in which the operation will take place. See the [sandboxes overview documentation](https://adobe.com/go/sandbox-overview-en) for more information.
        required: true
        schema:
          type: string
      - name: x-request-id
        in: header
        description: An optional header that serves as a tracking ID for the request. If one is not provided on the request, the server generates one automatically.
        schema:
          type: string
      - name: User-Agent
        in: header
        description: The type of the client making the HTTP request.
        schema:
          type: string
      - name: queryid
        in: path
        description: The query ID for the resource that you want to retrieve.
        required: true
        schema:
          type: string
      responses:
        200:
          x-summary: Success
          description: Query status
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/query'
        401:
          x-summary: Unauthorized
          description: Unauthorized
          content: {}
        404:
          x-summary: Not found
          description: Query not found
          content: {}
        '500':
          x-summary: Internal server error
          description: There was an internal server error. Try the request again in a few minutes, and contact Adobe Support if the problem persists.
          content: {}
    patch:
      tags:
      - Queries
      summary: Request cancellation or query soft delete
      operationId: cancelQuery
      description: This request cancels the execution of the query that relates to the query ID provided in the request.
      parameters:
      - name: Authorization
        in: header
        description: The access token which can be copied from your Experience Platform integration, prefixed with "Bearer " (for example, `Bearer {ACCESS_TOKEN}`). For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-gw-ims-org-id
        in: header
        description: The Organization ID which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-api-key
        in: header
        description: The API key which can be copied from your Experience Platform integration. For more information on how to obtain this value, visit the documentation covering making API calls in the [authentication tutorial](http://www.adobe.com/go/platform-api-authentication-en).
        required: true
        schema:
          type: string
      - name: x-sandbox-name
        in: header
        description: The name of the sandbox in which the operation will take place. See the [sandboxes overview documentation](https://adobe.com/go/sandbox-overview-en) for more information.
        required: true
        schema:
          type: string
      - name: x-request-id
        in: header
        description: An optional header that serves as a tracking ID for the request. If one is not provided on the request, the server generates one automatically.
        schema:
          type: string
      - name: User-Agent
        in: header
        description: The type of the client making the HTTP request.
        schema:
          type: string
      - name: queryid
        in: path
        description: Query ID
        required: true
        schema:
          type: string
      - name: Content-Type
        in: header
        description: The type of content being sent in the body of the request. The value should be 'application/json'.
        required: true
        schema:
          type: string
      requestBody:
        description: Use JSON Patch formatting to cancel or soft delete a query.
        content:
          application/json:
            schema:
              type: object
              properties:
                op:
                  type: string
                  enum:
                  - cancel
                  - soft_delete
                  description: The type of operation to perform on the resource.
        required: true
      responses:
        202:
          x-summary: Success
          description: Query termination request submitted successfully
          content: {}
        401:
          x-summary: Unauthorized
          description: Unauthorized
          content: {}
        404:
          x-summary: Not found
          description: Not Found
          content: {}
        409:
          x-summary: Failed
          description: Query termination request failed because the job might already have terminated
          content: {}
        '500':
          x-summary: Internal server error
          description: There was an internal server error. Try the request again in a few minutes, and contact Adobe Support if the problem persists.
          content: {}
      x-codegen-request-body-name: operation
components:
  schemas:
    _page:
      type: object
      properties:
        orderby:
          type: string
          description: The property by which this page is ordered.
          example: -created
        start:
          type: string
          description: The first value, in sorted order, of the orderby property on this page.
          example: '2021-12-06T22:00:17.362Z'
        next:
          type: string
          description: The start value for the next page.
          example: '2022-11-22T00:52:34.839Z'
        property:
          type: string
          description: The list of properties by which the result is filtered, if any. If the `property` query parameter is set when the query is issued, then it is returned in the `_page` object.
          example: created>=2021-04-05T13:30:00Z,state==IN_PROGRESS
        count:
          type: integer
          description: The number of items on the page.
          example: 26
      description: Pagination information for this resource. It contains a `count` property for the number of resources returned.
    query_links:
      type: object
      description: A list of links that provide more information on a query.
      properties:
        soft_delete:
          type: object
          properties:
            href:
              type: string
              description: The URI to call in order to soft-delete this query.
              example: https://platform.adobe.io/data/foundation/query/queries/217e32dc-d186-427f-b541-65bc34f2b6ed
            method:
              type: string
              description: The HTTP method to use with the `href`.
              example: PATCH
            body:
              type: string
              description: The JSON body to send to the `href`.
              example:
                \"op\": \"soft_delete\"
          description: Soft-deleting stops the query from being returned on GET requests to the `/queries` endpoint but does not delete it from the system.
        cancel:
          type: object
          description: Cancels the query run.
          properties:
            href:
              type: string
              description: The URI of this resource.
              example: https://platform.adobe.io/data/foundation/query/queries/219e31bc-b186-427f-d541-65dc34f2b6ed
            method:
              type: string
              description: The HTTP method to use with the `href`.
              example: PATCH
            body:
              type: string
              description: The JSON body to send to the `href`.
              example:
                \"op\": \"cancel\"
        referenced_datasets:
          type: array
          description: Datasets utilized by this query, if any. Note that these datasets are resolved when the query is in state `IN_PROGRESS`.
          items:
            type: object
            properties:
              id:
                type: string
                description: The ID of the referenced dataset.
                example: 5f7e5169ad81ea16bba6b222
              href:
                type: string
                description: The catalog-owned URI of this resource.
                example: https://platform.adobe.io/data/foundation/query/queries/217e32dc-d186-427f-b541-65bc34f2b6ed
        created_dataset:
          type: object
          properties:
            id:
              type: string
              description: The ID of the created dataset.
              example: 5f7e5169ad81ea16bba6b222
            href:
              type: string
              description: Catalog-owned URI related to this dataset.
              example: https://platform.adobe.io/data/foundation/query/queries/217e32dc-d186-427f-b541-65bc34f2b6ed
          description: If this query is a CTAS query, the created dataset is exposed under this object.
        schedule:
          type: object
          properties:
            id:
              type: string
              description: The ID of the schedule that created this query.
              example: 5f7e5169ad81ea16bba6b222
            href:
              type: string
              description: The URI to call to get details for the schedule that triggered this query.
              example: https://platform.adobe.io/data/foundation/query/queries/217e32dc-d186-427f-b541-65bc34f2b6ed
            method:
              type: string
              description: The HTTP method to use with the `href`.
              example: GET
          description: If this query was triggered by a schedule, then this object will be defined.
        schedule_run:
          type: object
          properties:
            id:
              type: string
              description: The ID of the `schedule_run` instance associated with this query.
              example: 5f7e5169ad81ea16bba6b222
            href:
              type: string
              description: The URI to call to get details for the `schedule_run` instance. associated with this query.
              example: https://platform.adobe.io/data/foundation/query/queries/217e32dc-d186-427f-b541-65bc34f2b6ed
            method:
              type: string
              description: The HTTP method to use with the `href`.
              example: GET
          description: If this query was triggered by a schedule, then this object will be defined.
    query_list:
      type: object
      properties:
        queries:
          description: A list of queries for your organization.
          items:
            $ref: '#/components/schemas/query'
        _page:
          $ref: '#/components/schemas/_page'
        _links:
          $ref: '#/components/schemas/query_list_links'
        version:
          type: integer
          description: The REST API version of this resource.
      description: List of queries
    next:
      type: object
      properties:
        href:
          type: string
          description: A link to the next page of results.
          example: https://platform.adobe.io/data/foundation/query/queries/alert-subscriptions?orderby=-created&page=2
      description: Pre-constructed URI for the next page of results, if any exist.
    query:
      type: object
      description: A list of queries and their associated information.
      properties:
        id:
          type: string
          description: The unique identifier for the query.
          example: 0094d000-8062-4e6a-6fdb-05606704f08f
        request:
          $ref: '#/components/schemas/query_def'
        effectiveSQL:
          type: string
          description: If the query is modified from the original, as in the case of a conversion to a CTAS statement using `ctasParameters`, this property will be reflected with the actual issued SQL.
          example: SELECT SUM(test_segment.count_of_profiles) COUNT FROM acmesbox1.profile_agg.test_segment LEFT OUTER JOIN acmesbox1.profile_agg.adwh_dim_segments ON test_segment.segment_id = adwh_dim_segments.segment_id WHERE test_segment.segment_id = 67176434 and test_segment.merge_policy_id = 1914981602 and test_segment.date_key = '2022-11-09';
        isCTAS:
          type: boolean
          description: This confirms whether the original SQL statement matches a CTAS query pattern or whether the query included a `ctasDataset` name that effectively turned the query into a CTAS statement.
          example: false
        isInsertInto:
          type: boolean
          description: This confirms whether the original SQL statement matches an INSERT INTO query pattern or whether the query included a dataset name under `insertIntoParameters` that effectively turned the query into an INSERT INTO statement.
          example: false
        state:
          type: string
          description: The current state of this query.
          enum:
          - SUBMITTED
          - IN_PROGRESS
          - SUCCESS
          - FAILED
          - KILLED
          - TIMEOUT
          example: SUCCESS
        created:
          type: string
          description: The time in UTC when the query was `SUBMITTED`.
          example: '2022-11-09T18:45:31.256Z'
        updated:
          type: string
          description: The time in UTC when the query was updated.
          example: '2022-11-09T18:45:31.256Z'
        elapsedTime:
          type: integer
          description: The time that has passed in milliseconds since the query state changed to `IN_PROGRESS`, or total time between the `IN_PROGRESS` state and a terminal state.
          example: 0
        rowCount:
          type: integer
          description: The total number of data rows returned in a result set, or persisted in the created dataset. This is meaningful only when query is in the `SUCCESS` state.
          example: 0
        templateName:
          type: string
          description: The name of the query template if the query was created using a query template.
          example: Sample Query Template for ACME Data
        userId:
          type: string
          description: The ID of the user who created this object.
          example: '{USER_ID}'
        client:
          type: string
          description: A string that identifies the client application that generated this query.
          example: Acme
        copyURI:
          type: string
          description: If the query is a `COPY` with a state of `SUCCESS`, this field holds the URI where the data was written.
          example: https://acme.org/acme-central/rev/48ea452803907f2575d81021e8678634e8067fc2/browser/app/profile/acme.js#575
        errors:
          type: array
          description: Information about errors.
          items:
            $ref: '#/components/schemas/error'
        _links:
          $ref: '#/components/schemas/query_links'
        version:
          type: integer
          description: The REST API version of this resource.
    query_def:
      type: object
      properties:
        dbName:
          type: string
          description: The name of the database you are making the query to. The value for `dbName` should take the format of `{SANDBOX}:{DATABASE}.{SCHEMA}`.
          example: acmesbox1:acmeacceldb:accmeaggschema
        sql:
          type: string
          description: The SQL query statement. You can either use standard SQL (such as `SELECT * FROM t1;`) or include parameter replacements. To use a parameter replacement in the SQL you must prepend the parameter key with a `$`. For example, `$key`, and provide the parameters used in the SQL as JSON key value pairs in the `queryParameters` field. The values passed here will be the default parameters used in the template. If you want to override these parameters, you must override them in the POST request.
          example: SELECT $key FROM $key1 WHERE $key > $key2;
        queryParameters:
          type: object
          properties:
            key:
              type: string
              description: A set of two linked data items. The key is a unique identifier for some item of data, and the value, is either the data that is identified or a pointer to the location of that data.
              example: value
            key1:
              type: string
              description: A set of two linked data items. The key is a unique identifier for some item of data, and the value, is either the data that is identified or a pointer to the location of that data.
              example: value1
            key2:
              type: string
              description: A set of two linked data items. The key is a unique identifier for some item of data, and the value, is either the data that is identified or a pointer to the location of that data.
              example: value2
          description: A key value pairing to replace values in the SQL statement. These are required if you are using parameter replacements. No value type checking will be done on these key value pairs.
        templateId:
          type: string
          description: The unique identifier for the query created and saved using a POST request to the `/query-templates` endpoint.
          example: 0094d000-9062-4e6a-8fdb-05606805f08f
        name:
          type: string
          description: An optional human friendly name for the query.
          example: Sample Query Template for ACME Data
        description:
          type: string
          description: An optional comment on the intended purpose of the query.
          example: This query returns all account details.
        insertIntoParamete

# --- truncated at 32 KB (34 KB total) ---
# Full source: https://raw.githubusercontent.com/api-evangelist/adobe-suite/refs/heads/main/openapi/adobe-suite-queries-api-openapi.yml