Build a PostgreSQL Interface for Zuora Data using the CData JDBC Driver

There are a vast number of PostgreSQL clients available on the Internet. From standard Drivers to BI and Analytics tools, PostgreSQL is a popular interface for data access. Using our JDBC Drivers, you can now create PostgreSQL entry-points that you can connect to from any standard client.

To access Zuora data as a PostgreSQL database, use the CData JDBC Driver for Zuora and a JDBC foreign data wrapper (FDW). In this article, we compile the FDW, install it, and query Zuora data from PostgreSQL Server.

Connect to Zuora Data as a JDBC Data Source

To connect to Zuora as a JDBC data source, you will need the following:

• Driver JAR path: The JAR is located in the lib subfolder of the installation directory.

• Driver class:

  cdata.jdbc.zuora.ZuoraDriver
  

• JDBC URL: The URL must start with "jdbc:zuora:" and can include any of the connection properties in name-value pairs separated with semicolons.

  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.

  Built-in Connection String Designer

  For assistance in constructing the JDBC URL, use the connection string designer built into the Zuora JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

  java -jar cdata.jdbc.zuora.jar
  

  Fill in the connection properties and copy the connection string to the clipboard.

  👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)
  

  A typical JDBC URL is below:

  jdbc:zuora:OAuthClientID=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;Tenant=USProduction;ZuoraService=DataQuery;InitiateOAuth=GETANDREFRESH;
  

Build the JDBC Foreign Data Wrapper

The Foreign Data Wrapper can be installed as an extension to PostgreSQL, without recompiling PostgreSQL. The jdbc2_fdw extension is used as an example (downloadable here).

1. Add a symlink from the shared object for your version of the JRE to /usr/lib/libjvm.so. For example:

   ln -s /usr/lib/jvm/java-6-openjdk/jre/lib/amd64/server/libjvm.so /usr/lib/libjvm.so
   

2. Start the build:

   make install USE_PGXS=1
   

Query Zuora Data as a PostgreSQL Database

After you have installed the extension, follow the steps below to start executing queries to Zuora data:

1. Log into your database.
2. Load the extension for the database:

   CREATE EXTENSION jdbc2_fdw;
   

3. Create a server object for Zuora:

   CREATE SERVER Zuora 
   FOREIGN DATA WRAPPER jdbc2_fdw OPTIONS (
   drivername 'cdata.jdbc.zuora.ZuoraDriver',
   url 'jdbc:zuora:OAuthClientID=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;Tenant=USProduction;ZuoraService=DataQuery;InitiateOAuth=GETANDREFRESH;',
   querytimeout '15',
   jarfile '/home/MyUser/CData/CData\ JDBC\ Driver\ for\ Salesforce MyDriverEdition/lib/cdata.jdbc.zuora.jar'); 
   

4. Create a user mapping for the username and password of a user known to the MySQL daemon.

   CREATE USER MAPPING for postgres SERVER Zuora OPTIONS (
   username 'admin', 
   password 'test');
   

5. Create a foreign table in your local database:

   postgres=# CREATE FOREIGN TABLE invoices (
   invoices_id text,
   invoices_Id text,
   invoices_BillingCity numeric) 
   SERVER Zuora OPTIONS (
   table_name 'invoices');
   

You can now execute SELECT commands to Zuora:

postgres=# SELECT * FROM invoices;