Skip to main content

Overview of Data Query

Zuora

Overview of Data Query

The Data Query feature enables you to export data from your Zuora tenant by performing asynchronous, read-only SQL queries. Because of the flexibility of SQL, you can use a single data query to retrieve data from multiple objects that have not been pre-joined by Zuora. See the following features of Data Query:

  • Data Query supports the SQL-based job to query all your tenant’s data.
  • Data Query provides easy-to-use POST and GET API.
  • Data Query provides User Interface for you to submit queries through the simple text box under Platform > Data Query.
  • Data Query supports the encryption feature when using personal RSA key pairs.
  • Data Query does not support synchronous queries. All queries are asynchronous.
  • Data Query only supports the read-only SQL statements. It does not support non-read only SQL statements such as UPDATE, DELETE, INSERT, ALTER TABLE, and so on.

Typically you can use Data Query in case that you:

  • Query from tables that are not available in other data extraction applications
  • Make more JOINs than Data Source Exports or Reporting can support
  • Prefer using SQL functions rather than ZOQL
  • Create Workflows that need to work with data 

Using Data Query

You can use Data Query through API, User Interface, and Workflow tasks.

When you submit a data query, you can select to run the query in either of the following modes:

  • Data Query Live: run data queries against Zuora live transactional databases
  • Data Query Unlimited: run data queries against an optimized, replicated database at 12 hours freshness for high volume extraction use cases

    Data Query Unlimited is an Early Adopter feature.

The default mode is Data Query Live. 

Using Data Query through API

You can use the Data Query API to submit queries, check the status of queries, and obtain the exported data.

To export data from your Zuora tenant through Data Query API:

  1. Construct a SQL query.

    For example:

    SELECT accountnumber, balance FROM Account WHERE Account.balance > 100
    

    See Constructing SQL Queries in Data Query for the supported SQL syntax, the available tables, and sample queries.

  2. Call Submit data query to submit the query and create a query job. Provide the query in the request body.

    You can request the query results in CSV, TSV, DSV or JSON format.

  3. Call Get data query job in the API or check your query listed on the Data Query page to track the status of the query job.

    Depending on the complexity of the query, you may need to call "Get data query job" several times, until the value of queryStatus in the response body is completedfailed, or cancelled.

    When the query job is complete, you can obtain the URL of the query results from the response body. For example:

    https://example.s3.us-west-2.amazonaws.com/3a3e85c4-96e7-486b-ae02-827120104301_24921638725108715.json?X-Amz-Security-Token=...

  4. Download the query results.

    Each row in the query results contains the requested fields of an object in your Zuora tenant. For example:

    accountnumber,balance
    A00253588,230.0
    A00253573,125.0
    A00255366,199.95
    ...
    

    If you requested the query results in JSON format, each row in the query results is a JSON object. The query results are not wrapped in a JSON array. For example:

    {"accountnumber":"A00253588","balance":230.0}
    {"accountnumber":"A00253573","balance":125.0}
    {"accountnumber":"A00255366","balance":199.95}
    ...
    

Using Data Query through User Interface

You can create new data queries, save data queries, and view the query history through Data Query UI. See Using Data Query through User Interface for the details.

Using Data Query through Workflow Tasks

To export data from your Zuora tenant through Workflow tasks, see Retrieve: Data Query.

Limitations

Query Processing Limitations

See the query processing limitations when data queries run in Live or Unlimited mode.

Data Query Unlimited is an Early Adopter feature.

Definition of limitation Data Query Live limitation value Data Query Unlimited limitation value
The maximum number of input records per table after filters have been applied

1,000,000

No limit

The maximum number of output records 500,000 500,000

The maximum number of simultaneous queries per tenant

The Data Query jobs in the in_progress status are counted towards the simultaneous query limit per tenant. However, you will not immediately get a failure when you submit a query after you reach the simultaneous query limit. This query is added to the queue if the queue is not full. See the queued query limitation below for more information.

5

1

The maximum number of queued queries after reaching the limitation of simultaneous queries per tenant

The Data Query jobs submitted after you reach the simultaneous query limit are added to the queue. You will encounter failures in submitting the query when you exceed the limit of queued queries. The Data Query jobs in the accepted status are counted towards the queued query limit.

10

0

The maximum processing time for each query in hours 1 1
The maximum size of memory allocated to each query in GB 2 2
The maximum number of characters in a query string submitted through API or Workflow  1,000,000 1,000,000
The maximum number of characters in a query string submitted through Data Query UI 20,000 20,000

When querying large transactional tables such as InvoiceItem, CreditMemoItem, DebitMemoItem, and TaxationItem, you should include additional filtering logic in the WHERE clause to ensure that the number of input records is less than 1 million. If the number of input records is greater than 1 million, the query will fail.

Examples for Query Processing Limitations in Data Query Live

For example, suppose that the InvoiceItem table contains a total of 1,200,000 records and the Subscription table contains a total of 100,000 records, and you submit the following data queries:

  • SELECT * FROM InvoiceItem
    
    • Number of input records: 1,200,000
    • Number of output records: 0

    This query will fail.

  • SELECT * FROM InvoiceItem 
    WHERE UpdatedDate >= TIMESTAMP '2018-06-02 18:17:07 -07:00'
    
    • Number of input records: 150,000
    • Number of output records: 150,000

    This query will fail.

  • SELECT * FROM InvoiceItem 
    WHERE UpdatedDate >= TIMESTAMP '2018-06-02 18:17:07 -07:00' LIMIT 100000
    
    • Number of input records: 150,000
    • Number of output records: 100,000

    This query will succeed.

  • SELECT * FROM InvoiceItem, Subscription 
    WHERE InvoiceItem.id = 'c92c8f9-61e3-1799-0161-e4d0317a088e'
    
    • Number of input records in InvoiceItem table: 1
    • Number of input records in Subscription table: 100,000
    • Number of output records: 100,000

    This query will succeed.

  • SELECT * FROM InvoiceItem 
    JOIN Subscription ON Subscription.id = InvoiceItem.subscriptionid 
    WHERE InvoiceItem.id = 'c92c8f9-61e3-1799-0161-e4d0317a088e' 
    
    • Number of input records in InvoiceItem table: 1
    • Number of input records in Subscription table: 100,000
    • Number of output records: 1

    This query will succeed.

Functional Limitations

  • Data Query does not support foreign currency conversion. All monetary values are exported in the original transaction currency.