Query Data using Query API V2

Query API V2 supports SQL query in ANSI standard. The results return as an array of records. The expected input when calling this API is free form SQL. The input objects include data stream, profile and engagement data model objects, and unified data model objects. You can use Query API V2 to support a variety of use cases, which includes large volume data reads, external application integration, or interactive on demand querying on the data lake.

We recommend using Query API V1 for smaller extract queries. You can also use the API when you want to fetch a limited set of data by specifying offsets and limits.
  • Cursor pagination is supported.
  • GET and POST API calls are supported.
  • The POST endpoint is used to make the first request. The POST endpoint returns data along with batchId for next batch using cursor pagination.
  • The GET endpoint is used for subsequent requests for the same query.
  • The GET endpoint requires a batchId as a parameter.
  • Pagination is forward cursor only, which means the same batchId couldn’t be used more than one time.
  • As subsequent GET calls need nextBatchId value from last call, hence the query execution is done serially.
  • There’s no explicit limit on the number of nested subqueries.
  • There’s no explicit limit on the number of joins.
  • There’s no explicit limit on the number of filters.
  • There’s no explicit limit on the number of column projections.
  • There’s no explicit limit on the data types.
  • In the response a _done_boolean flag is exposed. If the flag is set to true, then there are no more records to be queried. If the flag is set to false, then the next page can be retrieved using the next batchId.
  • You must fetch the complete result set generated by the query within one hour.
  • The time interval between two batch calls must not exceed three minutes.
  • When using aggregate functions, the results computed are of number types instead of string types.

The response contains a queryId. The ID remains the same irrespective of POST and GET calls.

Your orgs must be provisioned with Data Cloud licenses and the users must be assigned to appropriate roles for having full access to objects in the Data Cloud. Refer to User Roles and Permission Sets in Data Cloud before setting up the Connected App.

Set Up a Connected App to discover new insights about your customers.

Refer to Getting Started page to acquire the token.

We recommend that you include key qualifier fields in all table joins for queries submitted through the Query API V2. Make sure to use the COALESCE() function to get a null-safe join. When key qualifiers aren’t configured on data lake object fields, the value for the key qualifier field is null. In such cases, use the COALESCE() function to achieve desired results. This sample query covers the usage of COALESCE() function.

  • POST /api/v2/query

    Use the POST endpoint of Query API V2 to make the first request with the SQL query. The POST endpoint returns data along with batchId for the next batch.

  • GET /api/v2/query/{nextBatchId}

    Returns the next batch of data from the initial POST request.

See Also