Knowledge Center

Knowledge Center > API > Aggregate Query API (AQuA) > Stateless and Stateful Modes

Stateless and Stateful Modes

AQuA supports a stateless mode and a stateful mode. In stateless mode, AQuA executes the queries as is. In stateful mode, AQuA establishes a continuous session among a series of requests. 

The AQuA Job Finder enables you to view all AQuA requests and results executed on your tenant up to the last 72 hours. AQuA results older than 72 hours are automatically purged from the system. Excessive usage of non statefull, full queries is not advised, and instead, stateful AQuA should be used.

Stateless and Stateful Modes

AQuA can be executed in the following modes:

Mode Description
Stateless

In Stateless mode, queries are executed as is. When running in Stateless mode, all Export ZOQL and ZOQL queries supplied in the API input body are executed in a sequential order.  All data which satisfies the query criteria is returned.

AQuA queries are carried out in Stateless mode when the following occurs:

  • The version is 1.0 

or

  • The version is 1.1 or 1.2, and Partner or Project is null
Stateful

In Stateful mode, AQuA establishes a continuous session across a series of requests.

AQuA queries are executed in Stateful mode when the version is 1.1 or 1.2, and both Partner and Project are not null.

You can specify a combination of Partner and Project as a unique identifier of a continuous AQUA session. Once the Partner and Project pair are supplied in the AQuA input, AQuA records the state of each AQUA call request.

Submit a request at Zuora Global Support to obtain a partner ID.

The first request executes queries against all data in the database, and returns all data that satisfies the query criteria.

Subsequent requests execute the queries against incremental data created or updated since the last AQuA session. Each request returns incremental data for only the object specified in the FROM clauses of the queries; changes made to joined objects are not returned.

The Job ID and Transaction Start Date are recorded with the AQuA request.  Each subsequent AQuA request with the same Partner ID and Project ID returns the data that satisfies the query criteria created or updated after the Transaction Start Date in the previous AQuA request.

Stateful AQuA is best used for continuous data integration between Zuora and a target system.

AQuA sessions with the same Tenant ID, Partner ID, and Project ID cannot run in parallel. When this occurs, they are executed sequentially in the order in which they were received. For example, if an AQuA job is running, and another AQuA job with same Tenant ID, Partner ID, and Project ID is submitted, the second job will not be executed until the first job is complete.

Automatic Switch Between Full Load and Incremental Load

When you first call AQuA in stateful mode, AQuA uses the combination of partner and project as the unique identifier of a new session. The results file of the first call contains a full load of data for each of the requested queries. If you make subsequent calls with the same values of partner and project, either requesting the same queries or without any queries specified, the results file contains an incremental load of data for each query.

However, if you change the SQL of a query while keeping partner and project the same, the results file contains a full load of data for the query that you changed. AQuA also returns a full load of data under the following conditions:

  •  A deleted column is introduced or dropped
  •  A deleted column name or type changes

If you introduce an additional query while keeping partner and project the same, AQuA initially returns a full load of data for the new query.

In every AQuA job status response, the field full indicates whether the returned data for a query is a full load or an incremental load.

Examples

Full Load for a New Query

For example, if you want to retrieve the data of two custom fields from Invoice object, that were not in the previous AQUA queries, you can add a new query "Select My_Custom1__c, My_Custom2__c From Invoice" in the AQUA queries. Stateful AQUA returns all records of Invoice object, containing these two custom fields.

Retrieve Deleted Data

The following use case provides an example of how to retrieve deleted data.

In addition to the query capabilities currently supported by ZOQL and Export ZOQL, AQUA can retrieve records that have been deleted. This capability is especially important if you use Stateful AQUA for continuous data integration.

JSON request:

{
    "format":"gzip",
    "version" : "1.1",
    "encrypted" : "none",
    "useQueryLabels" : "true",
    "partner" : "salesforce",
    "project" : "00170000011K3Ub",
    "name" : "submit_002",
    "queries" : [

{  "name" : "CreditBalanceAdjustment",
   "type" : "zoql",
   "query" : "select AccountId,AccountingCode,AdjustmentDate,Amount,CreatedDate,Id,Number,
              SourceTransactionType,Status,Type,UpdatedDate from CreditBalanceAdjustment"
},
{  "name" : "AccountingPeriod",
   "type" : "zoql",
   "query" : "select Id,StartDate,EndDate,FiscalYear,Name,Status,UpdatedDate from AccountingPeriod"
},
{  "name" : "ProductRatePlan",
   "type" : "zoql",
   "query" : "select Id, Description, Name, CreatedDate, EffectiveEndDate,
              EffectiveStartDate, ProductId, UpdatedDate from ProductRatePlan"
          
},
        {  "name" : "InvoiceAdjustmentQuery" ,
           "type" : "zoqlexport",
           "query" : "select AccountingCode,CancelledOn,CreatedDate,Id,ImpactAmount,   Invoice.Id,InvoiceNumber,AdjustmentNumber,AdjustmentDate,Amount,
Status,Account.Id,Type,ReasonCode,UpdatedDate from InvoiceAdjustment",
          "deleted" : {
                 "column" : "MyDeletedColumn",
                 "format"  : "Boolean" // Remember it is Boolean not boolean.
               }
        }
]
}

JSON response:

{
  "partner" : "salesforce",
  "project" : "00170000011K3Ub",
  "batches" : [ {
    "name" : "CreditBalanceAdjustment",
    "query" : "select AccountId,AccountingCode,AdjustmentDate,Amount,CreatedDate,Id,Number,SourceTransactionType,Status,Type,UpdatedDate from CreditBalanceAdjustment",
    "status" : "pending",
    "recordCount" : 0,
    "batchType" : "zoql",
    "batchId" : "2c9083c346f3830d0146f3867cc1056a"
  }, {
    "name" : "AccountingPeriod",
    "query" : "select Id,StartDate,EndDate,FiscalYear,Name,Status,UpdatedDate from AccountingPeriod",
    "status" : "pending",
    "recordCount" : 0,
    "batchType" : "zoql",
    "batchId" : "2c9083c346f3830d0146f3867cc3056b"
  }, {
    "name" : "ProductRatePlan",
    "query" : "select Id, Description, Name, CreatedDate, EffectiveEndDate, EffectiveStartDate, ProductId, UpdatedDate from ProductRatePlan",
    "status" : "pending",
    "recordCount" : 0,
    "batchType" : "zoql",
    "batchId" : "2c9083c346f3830d0146f3867cc5056c"
  }, {
    "deleted" : {
      "column" : "MyDeletedColumn",
      "format" : "Boolean"
    },
    "full" : true,
    "name" : "InvoiceAdjustmentQuery",
    "query" : "select  AccountingCode,CancelledOn,CreatedDate,Id,ImpactAmount,Invoice.Id,InvoiceNumber,
AdjustmentNumber,AdjustmentDate,Amount,Status,Account.Id,Type,ReasonCode,UpdatedDate from InvoiceAdjustment",
    "status" : "pending",
    "recordCount" : 0,
    "batchType" : "zoqlexport",
    "batchId" : "2c9083c346f3830d0146f3867cc7056d"
  } ],
  "name" : "submit_002",
  "id" : "2c9083c346f3830d0146f3867ca90569",
  "version" : "1.1",
  "format" : "GZIP",
  "status" : "submitted",
  "encrypted" : "none"
}

Omit Queries in Stateful Mode

For incremental loads, use the header part with empty queries.

{
    "format":"gzip",
    "version" : "1.1",
    "encrypted" : "none",
    "partner" : "salesforce",
    "project" : "00170000011K3Ub",
    "name" : "submit_002",
    "queries" : [
]
}

The following response would automatically be in incremental mode:

{
  "partner" : "salesforce",
  "project" : "00170000011K3Ub",
  "batches" : [ {
    "name" : "AccountingPeriod",
    "query" : "select Id,StartDate,EndDate,FiscalYear,Name,Status,UpdatedDate from AccountingPeriod",
    "recordCount" : 0,
    "batchType" : "zoql",
    "batchId" : "2c9083c346f3830d0146f38c93af0578"
  }, {
    "name" : "CreditBalanceAdjustment",
    "query" : "select AccountId,AccountingCode,AdjustmentDate,Amount,CreatedDate,Id,Number,SourceTransactionType,Status,Type,UpdatedDate from CreditBalanceAdjustment",
    "recordCount" : 0,
    "batchType" : "zoql",
    "batchId" : "2c9083c346f3830d0146f38c93b20579"
  }, {
    "deleted" : {
      "column" : "MyDeletedColumn",
      "format" : "Boolean"
    },
    "full" : false,
    "name" : "InvoiceAdjustmentQuery",
    "query" : "select  AccountingCode,CancelledOn,CreatedDate,Id,ImpactAmount,Invoice.Id,InvoiceNumber,
AdjustmentNumber,AdjustmentDate,Amount,Status,Account.Id,Type,ReasonCode,UpdatedDate from InvoiceAdjustment",
    "recordCount" : 0,
    "batchType" : "zoqlexport",
    "batchId" : "2c9083c346f3830d0146f38c93b6057a"
  }, {
    "name" : "ProductRatePlan",
    "query" : "select Id, Description, Name, CreatedDate, EffectiveEndDate, EffectiveStartDate, ProductId, UpdatedDate from ProductRatePlan",
    "recordCount" : 0,
    "batchType" : "zoql",
    "batchId" : "2c9083c346f3830d0146f38c93b9057b"
  } ],
  "name" : "submit_002",
  "id" : "2c9083c346f3830d0146f38c93a60577",
  "version" : "1.1",
  "format" : "GZIP",
  "status" : "submitted",
  "encrypted" : "none"
}

The empty query list submitted above is same as "query:"

[
{  "name" : "CreditBalanceAdjustment",
   "type" : "zoql",
   "query" : "select AccountId,AccountingCode,AdjustmentDate,Amount,CreatedDate,Id,Number,SourceTransactionType,Status,Type,UpdatedDate from CreditBalanceAdjustment"
},
{  "name" : "AccountingPeriod",
   "type" : "zoql",
   "query" : "select Id,StartDate,EndDate,FiscalYear,Name,Status,UpdatedDate from AccountingPeriod"
},
{  "name" : "ProductRatePlan",
   "type" : "zoql",
   "query" : "select Id, Description, Name, CreatedDate, EffectiveEndDate, EffectiveStartDate, ProductId, UpdatedDate from ProductRatePlan"
           
},
        {  "name" : "InvoiceAdjustmentQuery" ,
           "type" : "zoqlexport",
           "query" : "select AccountingCode,CancelledOn,CreatedDate,Id,ImpactAmount,Invoice.Id,InvoiceNumber,
AdjustmentNumber,AdjustmentDate,Amount,Status,Account.Id,Type,ReasonCode,UpdatedDate from InvoiceAdjustment",
          "deleted" : {
                 "column" : "MyDeletedColumn",
                 "format"  : "boolean" 
               }
        }
]
Last modified
12:25, 29 Apr 2017

Tags

Classifications

(not set)