I'm interested in building a system that allows efficient loading of JSON stored in a Hadoop\EMR-based system to a relational data warehouse. I've already collected large amounts of JSON data in the Hadoop system and while analyzing it with Pig (and the Elephant Bird library specifically) has been useful, I now need to consider a process by which I can define and execute a job to transform large swaths of the JSON into a Data Warehouse (Postgres, Redshift, etc). The need for this is enable rapid generation of large number of reports.

At it's simplest case, it would be something like this JSON:

      'id': 123,
      'name': 'foo'
      'id': 456,
      'name': 'bar'

Would be transformed into a table like this

| id   | name |
| 123  | foo  |
| 456  | bar  |

Let's assume that the decision to put everything in Hadoop and store it as JSON has been made and can't be changed so as to exclude NoSQL choices like Mongo, etc. We've been using Pig so far and although it works I keep suspecting there might be established solutions to do things like this that I haven't discovered.

Paid software is an option as long as it's a good fit. The volume of work to be done is large, so anything that gives a decent times saving would be worth considering.


HP Vertica has a feature called 'flex tables' for processing non-tabular data. You use their json parser to load the files to the flex tables. Then you can keep the data in flex or 'promote' it to a regular table for that sweet structured query speed.

Note that the Vertica community edition is free, up to a terabyte of flex table data, and another terabyte of regular (structured) table data.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.