Happy Business Starts Here

Rest API's to connect Zuora to Pentaho

Valued Scholar

Rest API's to connect Zuora to Pentaho

Hello All

 

I'm new to Zuora and Pentaho , I need to get data from Zuora to SQL Server using Pentaho , can any one share details in steps to follow,Like

 

1 ) What kind of access do I need in Zuora to start with API's

2) Which is the best way to automate data transfer from Zuora to SQL Server

3) How to connect Zuora using Pentaho.

 

Thanks

 

5 REPLIES 5
Zuora Staff

Re: Rest API's to connect Zuora to Pentaho

1 ) What kind of access do I need in Zuora to start with API's

Any login account to a Zuora tenant can be used to work with the APIs, you can opt for an OAuth token as an alternative, but if you go the uname/pw route we recommend what we call an 'API Only' account which can only access Zuora through the APIs and does not have a password that expires (regular logins grant you access to the API but will likely need to be reset after 3 months or whatever period your Zuora admistrator chose). You can find out more about API only accounts here:

https://knowledgecenter.zuora.com/CF_Users_and_Administrators/A_Administrator_Settings/Manage_Users/...

The OAuth option is described here:

https://knowledgecenter.zuora.com/CF_Users_and_Administrators/A_Administrator_Settings/Manage_Users#...

 

2) Which is the best way to automate data transfer from Zuora to SQL Server

Our AQuA interface was expressly designed to give customers the ability to export their Zuora tenant data in bulk. Please note we don't provide a SQL interface, all data access is done through our object model and the APIs or UI. Compared ot the regular query APIs AQuA adds support for you to export 'just the new stuff since my last export' as well as to export stuff that was 'deleted' by users - internally we use a soft delete approach and so the AQuA api is able to still pull these objects out, there's a flag indicating they were deleted. An overview of AQuA and how to get going with it is here:

https://knowledgecenter.zuora.com/DC_Developers/T_Aggregate_Query_API/AA_AQuA_API_Introduction

This is an asynchronous interface, so be prepared to poll or receive a notification when your queries have completed. And while you can query as frequently as you'd like most Zuora txns are tracked using a date, the invoice date, the payment date, and so pulling exports more often than once a day really needs a compelling use case. If instantaneous notification of Zuora changes is required by other systems, this isn't the way get that information, consider our event notification framework instead:

https://knowledgecenter.zuora.com/CF_Users_and_Administrators/Notifications

 

3) How to connect Zuora using Pentaho.

Sorry but I'm not familiar with Pentaho and so I can't directly answer this. But assuming your goal is to have a copy of Zuora data in Pentaho, then nightly exports using AQuA's REST API invoked from Pentaho or a script is likely the best approach. Zuora processes over 90M API calls a day, that's all API calls, not just AQuA, but this interface is robust and well utilized. 

 

There is sample code included in the KC links I've shared but obviously you can post additional questions here or with Support. Don't forget Z-University which has lots of self paced training on Zuora including access Zuora through our APIs. 

 

Hope this helps, but this can be a broad topic! Post away if you have further questions.

Valued Scholar

Re: Rest API's to connect Zuora to Pentaho

Thanks a lot for this information. This is very helpfull, still not clear at my end (as mentioned new to this kind of tech).

 

I have a access to Zuora in my company , to use API do I need to a separate account like developer account pls confirm , when I got to IT they are asking what kind of access do u want ..pls let me know .

 

AQuA interface - Do I need to get a separate access to this interface - pls let me know . 

 

Our company wants to all API from pentaho..can any one tell me how to use Zuora API's in any ETL tool to extract data and load into SQL Server.

 

If it's not ETL tool , what's the best way to transfer data automatically from Zuora to SQL Server , pls suggest 

Zuora Staff

Re: Rest API's to connect Zuora to Pentaho

To start with ask your IT for a regular Zuora tenant login to your company's Zuora Sandbox tenant (production access can wait until you're ready), that's all you need to start with. That login should allow you to access the Zuora UI and the same username and password can be used for any API calls. When you migrate your code to production you'll likely want an API Only set of credentials, but if you just want to get going and start development a sandbox tenant login is all you need. You'll need the UI access to create test data, review reports of changes you may have made and the same login credentials can be fed into the REST API calls as the apiAccessKeyId and apiSecretAccessKey. Or you can configure a OAuth token if you prefer. And when I say 'regular' Zuora tenant login, you won't need to be a tenant admin, you just need to be able to create billing accounts, subscriptions, do bill runs and maybe add a product or two to the product catalog.

 

The AquA interface is REST based and the login I just mentioned can access it.

 

There are several ETL tools that have Zuora connectors and as a Zuora employee I'm not going to recommend one over the others, your company likely already has one or more ETL tools, if they have a Zuora connector I'd start there. Else you should be able to get the REST functionality in the ETL tool to query Zuora. And the ETL tool should support automated regular data pulls from Zuora.

 

But I've seen customers do all of this with a python script, or ruby or java or .net, ETL is entirely optional. There's sample code throughout the developer documentation at developer.zuora.com as well as on github.

Valued Scholar

Re: Rest API's to connect Zuora to Pentaho

Thanks again.

 

Can you pls let me know where to get Zuora connectors .

 

Thanks

Valued Scholar

Re: Rest API's to connect Zuora to Pentaho

#! C:\Users\abc\AppData\Local\Programs\Python\Python36-32\python.exe

 

import httplib2

 


httplib2.debuglevel = 1


h = httplib2.Http('.cache')

 

 

This is the python code which I used to connect to Zuora from Pentaho

*************************************************************************
login_headers =
{"Content-Type": "application/json","apiAccessKeyId": 'me.email@test.com',
"apiSecretAccessKey": 'passwd',}

 

services_login_url = "https://services999.zuora.com/apps/home.do"

 


# login request


resp, content = h.request(services_login_url, "POST", headers=login_headers)

 

Z_access_cookie = resp["set-cookie"]

update_headers =

{"Content-Type": "application/json","Cookie": resp["set-cookie"]}
print('ABCDEF')


*************************************************************************
This is the result after running above code - Can any one explain if my code is able to connect to Zuora or not.

2018/06/06 19:57:27 - General - Logging plugin type found with ID: CheckpointLogTable
2018/06/06 19:57:40 - RepositoriesMeta - Reading repositories XML file: C:\Users\abcd\.kettle\repositories.xml
2018/06/06 19:57:47 - RepositoriesMeta - Reading repositories XML file: C:\Users\abcd\.kettle\repositories.xml
2018/06/06 19:57:57 - Carte - Installing timer to purge stale objects after 1440 minutes.
2018/06/06 19:58:22 - Spoon - Spoon
2018/06/06 19:58:54 - Spoon - Starting job...
2018/06/06 19:58:54 - testtest - Start of job execution
2018/06/06 19:58:54 - testtest - Starting entry [services_rest_login_snippet.py]
2018/06/06 19:58:54 - services_rest_login_snippet.py - Running on platform : Windows 7
2018/06/06 19:58:54 - services_rest_login_snippet.py - Executing command : cmd.exe /C "C:\Users\abcd\Desktop\Me\SQLServer\JE\Testing\services_rest_login_snippet.py"
2018/06/06 19:58:55 - services_rest_login_snippet.py - (stdout) connect: (services999.zuora.com, 443)
2018/06/06 19:58:55 - services_rest_login_snippet.py - (stdout) send: b'POST /apps/home.do HTTP/1.1\r\nHost: services999.zuora.com\r\nContent-Length: 0\r\ncontent-type: application/json\r\napiaccesskeyid: me.email@test.com\r\napisecretaccesskey: passwd\r\nuser-agent: Python-httplib2/0.11.3 (gzip)\r\naccept-encoding: gzip, deflate\r\n\r\n'
2018/06/06 19:58:55 - services_rest_login_snippet.py - (stdout) reply: 'HTTP/1.1 302 Found\r\n'
2018/06/06 19:58:55 - services_rest_login_snippet.py - (stdout) header: Content-Type header: Date header: Location header: Connection header: Set-Cookie header: Content-Length ABCDEF
2018/06/06 19:58:55 - testtest - Finished job entry [services_rest_login_snippet.py] (result=[true])
2018/06/06 19:58:55 - testtest - Job execution finished
2018/06/06 19:58:55 - Spoon - Job has ended.
****************************************************************************************************

If this is connecting to Zuora successfully , what are the next steps to start using API's to get data from
account objects to a file. Can any one share some line of code here pls