Converting Asana JSON to CSV on S3

Uli Bethke Flexter, XML

In this article we will show you how to use Sonra’s data warehouse automation solution for complex XML and JSON documents, Flexter. We will process Asana JSON that we got from Asana’s API and convert the data to TSV text files on AWS S3 object storage.

Flexter is a Spark application written in Scala. For this blog post we will use the managed cloud version of Flexter. Flexter SaaS is built on top of container technology. You can make calls to its endpoints via a RESTful API. It currently supports Snowflake, S3, and Redshift as sinks. We will add onto this list over time and will also add calls to APIs to transform JSON and XML on the fly. This will let you generate a relational data model from any API in seconds.

Flexter SaaS is currently available for beta testing. If you are interested please reach out to us.

Asana

Asana is one of the best and largest web and mobile project management applications made to help teams manage their work. In Asana teams can create projects, tasks, assign those tasks to people, put due dates and add a lot more of other information.

Asana is used in many organizations and industries of different sizes. It can be addapted to any organization’s specific workflows and processes.

S3

Amazon Simple Storage Service that provides object storage through a web service interface.

Amazon S3 can be employed to store any type of object which allows for uses like storage for Internet applications, backup and recovery, disaster recovery, data archives, data lakes for analytics, and hybrid cloud storage. In its service-level agreement. In this scenario we store the text files that Flexter generates on S3. From there you can process the data further or download it.

Exporting data with Asana API

We have created a sample project for this showcase. We have tasks and their subtasks, different tags, due dates etc.

First step for exporting data would be to find the ID of the project. We will do that by going to Asana API page, and selection Projects in a dropdown.

In next step we select what we want to GET

Then we submit our request and we will get our Project ID in the response

When we are done with this, we change our GET request to “GET /projects/:project/tasks

We select all the information we need

We put our Project ID

And then we submit our request, which will provide a JSON file

To save our data, we can open it in browser by clicking on “open raw response”

And then saving our data as JSON

We can now start processing. You can download JSON file we used for this post here.

Processing data with Flexter API

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

Step 1 - Authenticate

Step 2 - Create a File Source

Step 3 - Generate schema (target data model)

Step 4 - Define your sink, e.g. S3

Step 5 - Process your XML/JSON data

Now we can go through steps and process our data.

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
Example of output

Step 2 - Create a source

A source is the location and type of your source documents. Sources are referenced in the next step when we create the target data model.

Currently, we support file upload and S3 as location for sources.

Three different types of documents are supported.

  • XML
  • XSD
  • JSON

The type of source determines which algorithm will be used to process the data.

Example of output

Step 3 - Generating target data model

In this step we create our Schema (target data model).

You can generate Schema based on :

  • XML Source entry ( Source with type xml )
  • XML Source entry + XSD Source entry ( Sources with types xml and xsd accordingly )
  • JSON Source files ( Source with type json )
Example of output

Example of ER Diagram

You can download the full ER Diagram for our Asana sample file from here.

Step 4 - Creating a sink

A sink is a connection to a target data store. In this step we create a connection to S3.

Use this endpoint to create an S3 Sink, if you want to save the data processing output to an S3 Bucket.

It requires 2 mandatory parameters name, path, and 1 optional parameter roleArn:

  • The parameter name is a unique name of Sink, which you will use in other calls as a reference to it.
  • Parameter path is a full path to S3 Object, it can be bucket/folder
  • (Optional) Parameter roleArn is a name of AWS Role with List/Read/Write to a provided path in your AWS Account, that you should create for Flexter AWS Account.
Example of output

Step 5 - Processing Asana JSON data

In this step we will process our data.

This endpoint is used to create a Data Processing entry. It requires 2 mandatory parameters and 1 optional parameter:

  • Parameter schema is a reference to a Schema entry
  • Parameter source is a reference to an XML Source
  • (Optional) Parameter sink is a reference to a Sink, if you want the output of processing to be saved somewhere, you should specify a Sink reference, otherwise the output will be saved in the Sonra cloud for download
Example of output
And now we can check our data in our S3 storage. You can download output data here.

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

No manual coding
Cut cost by up to 80%

Find out more

Conclusion

We have processed JSON data with ease. Flexter auto-generated the target schema, the target tables, the mappings from JSON elements to target table attributes, and globally unique foreign key relationships. Last but not least we automatically processed the JSON data into CSV/TSV files on S3. We did in a matter of minutes what would normally take a few days.

You can try out Flexter online.

Find answers to FAQs on our website.

We have recently released a SaaS version of the product and are looking for beta testers.

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.