Overview of Data Query

Knowledge Center > API > Data Query > Overview of Data Query

Overview of Data Query

The Data Query feature enables you to export data from your Zuora tenant by performing asynchronous, read-only SQL queries. You can use the REST API to submit queries, check the status of queries, and obtain the exported data.

Data Query does not support synchronous queries or non-read only SQL statements such as UPDATE, DELETE, INSERT, ALTER TABLE, and so on.

The Data Query feature is in Limited Availability. If you want to have access to the feature, submit a request at Zuora Global Support.

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.

Export Data Using Data Query

To export data from your Zuora tenant:

  1. Construct a SQL query.

    For example:

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

    See 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 JSONL format.

  3. Call Get data query job 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}
    ...
    

Limitations

Query Processing Limitations

  • Data queries can input a maximum of 1 million records per table, after filters have been applied.
  • Data queries can output a maximum of 100,000 records.
  • Data Query can simultaneously process a maximum of 5 queries per tenant. If 5 queries are currently being processed, Data Query will queue up to 10 additional queries to be processed. Any queries beyond the 10 queued queries will fail.
  • Data Query processes each query for a maximum of 1 hour.
  • Data Query allocates a maximum of 2 GB of memory to each query.

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.

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

  • SELECT chargeamount FROM InvoiceItem
    WHERE id = 'c92c8f9-61e3-1799-0161-e4d0317a088e'
    
    • Number of input records: 1
    • Number of output records: 1

    This query will succeed.

  • 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 because the number of output records is greater than 100,000.

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

    This query will fail because the number of input records is greater than 1 million.

Functional Limitations

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

Tags

Classifications

(not set)