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.
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.
To export data from your Zuora tenant:
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.
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.
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 completed, failed, 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=...
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:
Vision
{"accountnumber":"A00253588","balance":230.0}
{"accountnumber":"A00253573","balance":125.0}
{"accountnumber":"A00255366","balance":199.95}
...
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'
This query will succeed.
SELECT * FROM InvoiceItem WHERE updateddate >= TIMESTAMP '2018-06-02 18:17:07 -07:00'
This query will fail because the number of output records is greater than 100,000.
SELECT * FROM InvoiceItem
This query will fail because the number of input records is greater than 1 million.