Converting and Masking Zendesk JSON to Oracle

Uli Bethke Flexter, JSON, Oracle

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 as a Service (FaaS).

FaaS follows a pay as you go model. Flexter is also available as a free edition and 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 FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Oracle can be done in a few simple steps. For more details please refer to the FaaS API documentation.

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

Convert XML/JSON automatically to a Database, Text, or Hadoop

No manual coding
Cut cost by up to 80%

Find out more

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.

You can try out the free version of Flexter online.

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.

 

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.