Converting and Masking Zendesk JSON to a database (Oracle)

September 17, 2019

For this post we will mask and convert Zendesk JSON data to Oracle. We will use Sonra’s obfuscation tool Paranoid (open source) and Sonra’s data warehouse automation tool for XML, JSON, and industry data standards Flexter.
Flexter is available as a as an enterprise edition. The enterprise edition can be installed in the cloud or on-premise.

Zendesk

Zendesk is a customer support platform that enables you to connect to your customers to all channels (phone, chat, email, social media or any other channel). It allows you to easy track all support requests, answer questions quickly and monitor customer service agent’s effectiveness.
Zendesk offers all-in-one suite in which all core products are included. Those are:

  • Zendesk Support
  • Zendesk Guide
  • Zendesk Chat
  • Zendesk Talk

Exporting Zendesk JSON data with API

Zendesk offers two options to perform a full data export.

  • First option is that if you are an administrator of the account and you have Plus/Professional or Enterprise plans, you can do XML export in your Admin tab.
  • Second option is to export with the API. You can access the API if you are on Regular/Team or Starter/Essential Plans

For this showcase we will export tickets data with the API. We will run an incremental export, which incrementally extracts data since our last export. This is useful to limit the amount of data we need to export.
We will use the Curl command to export tickets data:

Masking Zendesk JSON data

To mask data we have to provide a path to our file and a path to an output location. Paranoid will create the folder automatically, no need to create it first.


Next we check our file. Masking was successful

Next we zip our masked file and continue processing it with Flexter.

Processing masked JSON with Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Oracle can be done in a few simple steps.
Step 1 – Authenticate
Step 2 – Define Source Connection (Upload or S3) for Source Data (JSON/XML)
Step 3 – Define your Target Connection, e.g. Snowflake, Redshift, SQL Server, Oracle etc.
Step 4 – Convert your XML/JSON from Source to Target Connection

Step 1 – Authenticate

To get an access_token you need to make a call to /oauth/token with Authorization header and 3 form parameters:

  • username=YOUR_EMAIL
  • password=YOUR_PASSWORD
  • grant_type=password

You will get your username and password from Sonra when you sign up for the service.

Example of output

Step 2 – Define Source Connection (Upload) for Source Data (Zendesk JSON)

In a second step we upload the Zendesk JSON source data

Example of output

Step 3 – Define Target Connection (Oracle)

Since we don’t have a Source Schema we skip the optional step of defining a Source Schema.
We define our Target connection. We give the Target Connection a name and supply various connection parameters to the Oracle database.

Example of output

Step 4 – Convert JSON data from Source Connection (Upload) to Target Connection (Oracle)

In a final step we convert Zendesk JSON data. Data will be written to Oracle Target Connection.

Example of output

Example of ER Diagram


You can download the ER Diagram for our Zendesk JSON file here.
Next we will run an SQL Query basic info of all tickets

Conclusion

We have masked Zendesk JSON data and converted it to Oracle tables in a few simple steps. We did in a matter of minutes what would normally take a few hours or even days.
Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.
If you have any questions please refer to the Flexter FAQ section. You can also request a demo of Flexter or reach out to us directly.