How to Build an ETL App for Zuora Data in Python with CData Connect AI
The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData Connect AI Python SDK and the petl framework, you can build Zuora-connected applications and pipelines for extracting, transforming, and loading Zuora data. This article shows how to connect to Connect AI and use petl to extract, transform, and load Zuora data.
The Connect AI Python SDK (cdata-connect-ai) is a DB-API 2.0 (PEP 249) compliant client, so petl can read directly from the SDK connection with etl.fromdb. There is no driver to install per source: connect with a Personal Access Token and build your pipeline.
Connect to Zuora in Connect AI
CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.
- Log into Connect AI, click Sources, and then click Add Connection
- Select "Zuora" from the Add Connection panel
-
Enter the necessary authentication properties to connect to Zuora.
Zuora uses the OAuth standard to authenticate users. See the online Help documentation for a full OAuth authentication guide.
Configuring Tenant property
In order to create a valid connection with the provider you need to choose one of the Tenant values (USProduction by default) which matches your account configuration. The following is a list with the available options:
- USProduction: Requests sent to https://rest.zuora.com.
- USAPISandbox: Requests sent to https://rest.apisandbox.zuora.com"
- USPerformanceTest: Requests sent to https://rest.pt1.zuora.com"
- EUProduction: Requests sent to https://rest.eu.zuora.com"
- EUSandbox: Requests sent to https://rest.sandbox.eu.zuora.com"
Selecting a Zuora Service
Two Zuora services are available: Data Query and AQuA API. By default ZuoraService is set to AQuADataExport.
DataQuery
The Data Query feature enables you to export data from your Zuora tenant by performing asynchronous, read-only SQL queries. We recommend to use this service for quick lightweight SQL queries.
Limitations- The maximum number of input records per table after filters have been applied: 1,000,000
- The maximum number of output records: 100,000
- The maximum number of simultaneous queries submitted for execution per tenant: 5
- The maximum number of queued queries submitted for execution after reaching the limitation of simultaneous queries per tenant: 10
- The maximum processing time for each query in hours: 1
- The maximum size of memory allocated to each query in GB: 2
- The maximum number of indices when using Index Join, in other words, the maximum number of records being returned by the left table based on the unique value used in the WHERE clause when using Index Join: 20,000
AQuADataExport
AQuA API export is designed to export all the records for all the objects ( tables ). AQuA query jobs have the following limitations:
Limitations- If a query in an AQuA job is executed longer than 8 hours, this job will be killed automatically.
- The killed AQuA job can be retried three times before returned as failed.
- Click Save & Test
- Navigate to the Permissions tab and update the user-based permissions.
Generate a Personal Access Token (PAT)
The Python SDK authenticates to Connect AI with your account email and a Personal Access Token (PAT). It is best practice to create a separate PAT for each application to maintain granularity of access.
- Click the Gear icon () at the top right of the Connect AI app to open the Settings page.
- On the Settings page, go to the Access Tokens section and click Create PAT.
- Give the PAT a name and click Create.
- The PAT is only visible at creation, so copy it and store it securely.
Install Required Modules
Install the SDK and the petl framework using the pip utility:
pip install cdata-connect-ai pip install petl
Build an ETL App for Zuora Data in Python
Once the required modules are installed, you are ready to build the ETL app. Code snippets follow, but the full source code is available at the end of the article.
First, import the modules and connect to Connect AI with your account email and PAT:
import petl as etl
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
Create a SQL Statement to Query Zuora
Use SQL to create a statement for querying Zuora. In this article, we read data from the Invoices entity. Identifiers are three-part: <Connection>.<Schema>.<Table>, where the connection name defaults to the source name (for example, Zuora1).
sql = (
"SELECT Id, BillingCity "
"FROM [Zuora1].[Zuora].[Invoices] "
"WHERE BillingState = 'CA'"
)
Extract, Transform, and Load the Zuora Data
With a connection and query in hand, use petl to extract, transform, and load the Zuora data. In this example, we extract Zuora data, sort the data by the BillingCity column, and load the data into a CSV file.
table1 = etl.fromdb(conn, sql) table2 = etl.sort(table1, 'BillingCity') etl.tocsv(table2, 'invoices_data.csv')
Zuora is a read-only source in Connect AI, so this pipeline can extract and transform Zuora data but not load rows back. Close the connection when the extract is complete:
conn.close()
With the CData Connect AI Python SDK, you can work with Zuora data just like you would with any database, including direct access to data in ETL packages like petl.
More Information and Free Trial
Now you can pipe live Zuora data through petl using the CData Connect AI Python SDK. For more information on connecting to Zuora (and hundreds of other data sources), visit the Connect AI page. Sign up for a free trial and start building data pipelines for live Zuora data in Python.
Full Source Code
import petl as etl
import cdata_connect_ai
conn = cdata_connect_ai.connect(
username="[email protected]",
password="<your_pat>",
)
sql = (
"SELECT Id, BillingCity "
"FROM [Zuora1].[Zuora].[Invoices] "
"WHERE BillingState = 'CA'"
)
table1 = etl.fromdb(conn, sql)
table2 = etl.sort(table1, 'BillingCity')
etl.tocsv(table2, 'invoices_data.csv')
conn.close()