Use Data Query through User Interface

Create new data queries

You can create new data queries through Data Query UI to export data from your Zuora tenant.

To create a new data query:

1. Navigate to Platform > Data Query in the left navigation menu.
2. Click CREATE NEW DATA QUERY on the Data Query page.

3. Enter a SQL query into the text box on the Create Data Query page. See Constructing SQL Queries in Data Query for the supported SQL syntax, the available tables, and sample queries.

4. Set the query options on the Create Data Query page:

   • USE INDEX JOIN: select the USE INDEX JOIN checkbox if you want to use Index Join in your query. Index join is useful when you have a specific reference value in your WHERE clause to index another large table by. See Use Index Join for more information.

   • READ DELETED: select the READ DELETED checkbox if you want to retrieve only the deleted records. If you do not select the READ DELETED checkbox, the query retrieves only the non-deleted records.
     Note that if you select the deleted column in the SQL query, both non-deleted and deleted records will be retrieved regardless of whether you select this checkbox or not.

   • COMPRESSION: select the compression format for the returned query result from the picklist. The supported values are:

     • NONE
     • GZIP
     • ZIP

   • OUTPUT FORMAT: select the output format for the returned query result from the picklist. The supported values are:

     • JSON - Each row in the query results is a JSON object. The format of the query result files is JSON Lines.
     • CSV - Each row in the query results is a comma-separated list of values.
     • TSV - Each row in the query results is a tab-separated list of values.
     • DSV - Each row in the query results is a list of values separated by your custom delimiter. When you select DSV from the picklist, the COLUMN SEPARATOR field is displayed on the right side of the OUTPUT FORMAT field. Input your custom delimiter character into the COLUMN SEPARATOR field.

   • SOURCE: select the source that you query against. The possible values are:

     • LIVE - run the query against Zuora transactional databases, which are updated in near real-time.
     • ZUORA WAREHOUSE - run the query against Zuora Warehouse, which has better performance and fewer limitations than the Zuora transactional database. This option is available only if you have the Zuora Warehouse feature enabled in your tenant. For more information, see Zuora Warehouse.
       If this option is selected, you can see your warehouse size to the right of the drop-down list.
   • Click RUN QUERY to submit your new query. Upon completion of the new query, you can click DOWNLOAD to download the query result. To save your new query, click SAVE QUERY.

Save data queries

When you create a new data query through Data Query UI, you can save the query. You can then re-run the saved query in the future without constructing the SQL query again. You can access all the saved queries in your tenant.

To save your query:

1. Click SAVE QUERY upon completing the construction of your new query on the Create Data Query page.
2. In the Save Query dialog, input the name and description of the query into the QUERY NAME and DESCRIPTION fields respectively.
3. Click SAVE.

Note that the max length of a query string that you can save is 20,000.

To view your saved queries:

1. Navigate to Platform > Data Query.
2. Click the SAVED QUERIES tab on the Data Query page. The list of saved queries is displayed.

To run a saved query:

1. Click the query name of the saved query that you want to update in the list of saved queries.
2. Click RUN QUERY on the query detail page.

To update a saved query:

1. Click the query name of the saved query that you want to update in the list of saved queries.
2. Update the query on the query detail page.
3. Click UPDATE SAVED QUERY.

To delete a saved query:

1. Hover over the query that you want to delete in the list of saved queries.
2. Click the trash can icon on the right side of the query.
3. Click DELETE QUERY in the Delete Saved Query dialog.

View the data query history

You can view the data queries submitted in history through Data Query UI. If you are an administrator, you can view all the data queries submitted in history. Otherwise, you can only view the data queries that you submit in history.

To view the data query history:

1. Navigate to Platform > Data Query.
2. Click the HISTORY tab on the Data Query page. The last 1000 submitted data queries will be returned.
   You can find details of each query history in the table, such as the query expression, source, status, returned rows, compression status, warehouse size (applies only to Zuora Warehouse), and other information.

You can make the following operations upon a data query in history:

• Download the query result by clicking the download icon if the query job is completed.
• Cancel the query by clicking the cancel icon if the query job is in the submitted or in progress status. 
• Create a new data query by clicking the more icon and then clicking Create as New Data Query.

View objects in Schema Browser

You can use Data Query Schema Browser to view schemas of all tables available in your tenants. To access Schema Browser, click SHOW SCHEMA button on the Create Data Query page.

Schema Browser now supports the following features:

• Show a full list of all tables in your tenant. Click SHOW SCHEMA button on the Create Data Query page to display the Schema Browser pane with a full list of all Zuora data objects.

• Show all fields for an object. Click the expand/collapse icon on the left side of each object to view all its fields. When expanded, all the field names and field types are listed under the object.

• Search an object. Enter the object name in the search bar at the top of the Schema Browser pane and the list of result objects will be displayed underneath.
• Change the sorting of objects. Click the sorting icon on the right side of the search bar to select Sort A to Z or Sort Z to A.

You can click HIDE SCHEMA button on the Create Data Query page to hide the Schema Browser pane.