Happy Business Starts Here

Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription?

Savvy Scholar

Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription?

Hello all,

 

This will be my first post, so I apologize in advance for any rough edges in formatting and the like.

 

The issue is that I need to load Zuora object data to a target Redshift database. There is an ETL process in place that grabs the Zuora data and loads it into Redshift. However, I am finding columns I expect to exist, as per the documentation (e.g. https://knowledgecenter.zuora.com/DC_Developers/G_SOAP_API/E1_SOAP_API_Object_Reference/Invoice), are missing. I am aware that it seems that the SOAP API linked is not under active development, but calls to the REST API's data exports using ZOQL (e.g. "select * from invoice") seem to return a set of fields/columns that align with what is linked.

 

Namely, in the case of objects such as Invoice and Subscription, there seems to be an AccountId field missing from the data given by Zuora. With the help of some external investigation, it seems that this column is missing because it is not denoted for export, unlike the rest of the fields on the object. Ultimately, my questions are twofold. First, can someone help me understand why it seems that AccountId is purposefully not reported as part of data exports? Secondly, the end goal of grabbing the Zuora data is to relate the objects together on the Redshift using usefuly queries, so how could one paint the picture of a given Account using all the Zuora data, if fields such as AccountId are missing, if that would even be possible?

 

One hiccup I imaginge causing a not insignificant gap in my understanding is that I do not have credentials to explore the Zuora UI itself, I am only able to solely interpret the data received from API calls and what understanding I can formulate from written documentation online.

 

I would greatly appreciate any insight on the matter. Thank you!

5 REPLIES 5
Guru

Re: Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription

I do not know the answer about the rest API, but wanted to share that the SOAP API is very reliable and is not going away soon. It just won't have new things added to it. So I would not be afraid to using it.

 

Our ETL job uses the Export Object to query the objects and gets results in a csv file zipped up.

Maggie Longshore
Zuora Documentation

Re: Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription

Hi @p3y8eYWuij,

 

This is a great question and you've hit on quite a complex topic. Unfortunately, the documentation in this area is a bit fragmented and it's not so easy to piece together what's going on.

 

We're currently preparing a turorial that provides an overview of data queries/exports in Zuora. Since the tutorial addresses both of your questions, I've included a preliminary version below. I hope you'll find it helpful! If you have any follow up questions or want to discuss anything in more detail, please don't hesitate to reply.

 

~ ~ ~

 

Zuora supports two different data query languages, with different use cases:

 

  • Query objects of a particular type - If you want to retrieve fields of a single type of object, you can run a ZOQL query. For example:

    select InvoiceDate, AccountId from Invoice
    


    This query selects InvoiceDate and AccountId for each Invoice. See below for an example of how to run the query via the REST API.

 

  • Export data from a data source - If you want to retrieve fields of multiple types of object with a single query, you must run an Export ZOQL query. Export ZOQL queries are very similar to ZOQL queries, but Export ZOQL queries select data from data sources instead of individual objects.

    Each data source represents a “base” type of object that is joined to related types of object. When you write an Export ZOQL query, you can select joined fields without needing to specify which types of object are joined.

    For example:

    select InvoiceDate, Account.Id, Account.Name from Invoice
    


    This query selects InvoiceDate for each Invoice, along with Id and Name for the Account that owns each Invoice. The query selects fields from the Invoice data source. See below for an example of how to run the query via the REST API.

    When Zuora constructs the Invoice data source, Zuora joins Accounts to Invoices according to the value of AccountId for each Invoice. That is, for each Invoice, Zuora joins the Account that satisfies Account.Id = AccountId. Instead of exposing both AccountId and Account.Id in the data source, Zuora only exposes Account.Id in the data source.

    See Describe object in the API Reference for how to determine which fields are exposed in data sources.

 

Example: Run a ZOQL Query

 

You can use Query to run a ZOQL query.

 

Send the following request:

 

POST /v1/action/query

{
  "queryString": "select InvoiceDate, AccountId from Invoice"
}

 

Response from Zuora:

 

{
  "records": [
    {
      "AccountId": "2c92c0f948f36bd90148f803e924768a",
      "InvoiceDate": "2018-08-13",
      "Id": "2c92c0855388c50301539b680c950e3a"
    },
    {
      "AccountId": "2c92c0f948f8997b0148f8c600275a58",
      "InvoiceDate": "2018-02-10",
      "Id": "2c92c0855388c50301539b680d910e6e"
    },
    ...
  ]
}

 

Notice that Zuora returns Id for each Invoice even though the query did not select Id.

 

Example: Run an Export ZOQL Query

 

You can use the AQuA API to run an Export ZOQL query:

 

  1. Send the following request:

    POST /v1/batch-query/
    
    {
      "format": "CSV",
      "queries": [
        {
          "type": "zoqlexport",
          "query": "select InvoiceDate, Account.Id, Account.Name from Invoice"
        }
      ]
    }
    


    Response from Zuora:

    {
      "batches": [
        {
          "status": "pending",
          "query": "select InvoiceDate, Account.Id, Account.Name from Invoice",
          ...
        }
      ],
      "status": "submitted",
      "id": "2c92c0f865419a280165563d462d5da8",
      "format": "CSV",
      ...
    }
    


    This response indicates that Zuora has started running the query and that the internal ID of the job is 2c92c0f865419a280165563d462d5da8.

  2. To retrieve the query results, first send the following request:

    GET /v1/batch-query/jobs/2c92c0f865419a280165563d462d5da8
    


    Response from Zuora:

    {
      "batches": [
        {
          "status": "completed",
          "query": "select InvoiceDate, Account.Id, Account.Name from Invoice",
          "recordCount": 148,
          "fileId": "2c92c08565243f640165563d46970df3",
          ...
        }
      ],
      "status": "completed",
      "id": "2c92c0f865419a280165563d462d5da8",
      "format": "CSV",
      ...
    }
    


    The value of fileId is the internal ID of the file that contains the query results.

  3. To download the query results, send the following Get files request:

    GET /v1/files/2c92c08565243f640165563d46970df3
    


    Response from Zuora:

    Invoice: Invoice Date,Account: ID,Account: Name
    2018-08-01,2c92c0f846f580ab014719bedfea14b9,ABC Company
    2018-07-01,2c92c0f946f58862014715624e782bfc,Cloud Services
    2018-05-20,2c92c0f8474c4d4c014756f6e5913796,North Finance
    ...
    

 

The AQuA API supports multiple simultaneous queries and is primarily intended to be used for data synchronization. The legacy operation CRUD: Create Export provides a slightly simpler way to run an Export ZOQL query, but this operation does not support the latest objects and fields.

Savvy Scholar

Re: Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription

Thank you so much for this informative and detailed reply. I believe I understand much better the distinction between the use cases of the two now. It seems that either approach (ZOQL or Export ZOQL) would be a valid means of leveraging the REST API to gather all of the data of interest together?

Zuora Documentation

Re: Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription

Not a problem - very happy to help!

 

Either ZOQL or Export ZOQL can work, depending on your use case.

 

Export ZOQL is more powerful, and also the best in terms of compatability. You can only use ZOQL to query objects that have been exposed through the legacy SOAP API, which means that the latest features such as Orders and Invoice Settlement are not supported in ZOQL queries. However, there are data sources for these features, so the features are supported in Export ZOQL queries.

 

The main scenario where ZOQL would be prefereable to Export ZOQL is if you want to run a query and have the results returned directly as JSON in the response body. You can use Query to do that, but Query only supports ZOQL. This approach is suitable if you want to fetch a small amount of data to, e.g., power a web UI.

 

Export ZOQL is more suited to fetching a large amount of data, and to perform synchronization with another system (you can use the AQuA API to do that). A disadvantage of running an Export ZOQL query is that it always requires multiple calls: one call to submit the query, a second call to check the status of the data export, and a third call to download the data.

 

Does that help?

 

I should add that you may well be able to gather the data you need using some REST API endpoints that are not related to ZOQL / Export ZOQL. Check out the API Reference to dig into the REST API endpoints that are available, and don't hesitate to post your use case in the API forum here in Community if you'd like to get input from subject matter experts.

Savvy Scholar

Re: Why is accountId not denoted as an exportable field for objects such as Invoice and Subscription

Thanks for the additional help. Your response was very helpful in determining the appropriate use cases. My particular case is an attempt to fetch a lot of data for export to a Redshift target, so it seems like the Export ZOQL calls might be the better suited of the two.