Happy Business Starts Here

Highlighted
Valued Scholar

Zuora Query Language- Where to Start

Hello All

 

I'm new to Zuora - we have a requirement to download the data from zuora to SQLServer - I see we can use ZOQL just like SQL - I'm good in SQL but no idea where to start with ZOQL ..is it just like SQL window in SQL Server ..what kind of access I need to have in Zuora in order to start working on ZOQL ..

 

Any idea on how I can use ZOQL in any ETL tool like SSIS or Pentaho ..Pls suggest ASAP ..

4 REPLIES
Guru

Re: Zuora Query Language- Where to Start

Hi @myzuora

 

A couple places to get you going:

 

https://knowledgecenter.zuora.com/DC_Developers/K_Zuora_Object_Query_Language

 

https://knowledgecenter.zuora.com/DC_Developers/M_Export_ZOQL

 

If you are bulk exporting large portions of your data, you should look at Export ZOQL and the AQuA API which allows incremental access of the data:

 

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

 

 

 

Valued Scholar

Re: Zuora Query Language- Where to Start

Thanks . I went through these links .

 

My question is very basic (sorry to ask again) - if I want to write ZOQL where to write , is there any SQL kind of window in zuora ..pls suggest ..

 

I need to use Pentaho as an ETL tool to gather data from Zuora , pls suggest or give a simple java , Python or Json script to connect to zuora using API and get data into SQL Server ..

 

Thanks

Savvy Scholar

Re: Zuora Query Language- Where to Start

There are no interfaces for writing ZOQL or ExportZOQL in Zuora's website. The closest you can come is the Datasources or Reporting if you need a UI experience.

 

We have found using stateful AQuA is efficient for bringing Zuora data into warehouse for processing.

 

Getting an AQuA job posted in Python is pretty simple (the ZOQL is the value of the query key in the query1 dict):

 

 

import requests

payload = {'apiAccessKeyId':'un'
            ,'apiSecretAccessKey':'pw'}

conn = requests.post('https://rest.zuora.com/v1/connections', data = payload)

query1 = {"name" : "SubscriptionWithAccount"
        ,"query" : '''Select Id, Name, Account.Id, Account.Name from Subscription'''
        ,"type" : "zoqlexport"}

query_json = {
    "format" : "csv",
    "version" : "1.0",
    "name" : "Example",
    "encrypted" : "none",
    "useQueryLabels" : 'true',
    "partner" : '',
    "project" : '',
    "dateTimeUtc" : 'true',
    "queries"  : [query1]
    }

post_query = requests.post('https://rest.zuora.com/v1/batch-query/',json= query_json, cookies=conn.cookies)

 

 

To do stateful AQuA you need to use version 1.1 or higher and set up a partner id with Zuora Support. https://knowledgecenter.zuora.com/DC_Developers/T_Aggregate_Query_API/BA_Stateless_and_Stateful_Mode...

 

To get the job results you need to either use the notification service or get from the endpoints at https://knowledgecenter.zuora.com/DC_Developers/T_Aggregate_Query_API/C_Get_Job_ID until you see the status change to completed. You can also go to https://www.zuora.com/apps/BatchQuery.do if you want a UI interface to download the file from.

 

To download the file you use the endpoints at https://knowledgecenter.zuora.com/DC_Developers/T_Aggregate_Query_API/D_Get_File_Download

 

Good luck with your integration.

Valued Scholar

Re: Zuora Query Language- Where to Start

Thanks  . I used your code and steps , I think it's success - Here is the sample code and message from Pentaho - Can you pls give a sample to get data from Zuora just from 1 object example account - I want to read data from that object and store in SQL Server .

 

********************

#! 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