Getting a sql schema from JSON

In order to parse Json and create a SQL schema below are Steps to prepare:

  1. Read the below:

https://github.com/quux00/hive-json-schema

2. prepare an example.json file preferably from the above URL

3. make sure you have java installed. and follow steps below

 

Steps to reconstruct :

git clone https://github.com/quux00/hive-json-schema.git

cd hive-json-schema/

mvn package

java -jar target/json-hive-schema-1.0-jar-with-dependencies.jar example.json example_table_name

 

example.json:

 

{
  "description": "my doc",
  "foo": {
    "bar": "baz",
    "quux": "revlos",
    "level1" : {
      "l2string": "l2val",
      "l2struct": {
        "level3": "l3val"
      }
    }
  },
  "wibble": "123",
  "wobble": [
    {
      "entry": 1,
      "EntryDetails": {
        "details1": "lazybones",
        "details2": 414
      }
    }
  ]
}

 

Expected output:

CREATE TABLE example_table_name (

  description string,

  foo struct<bar:string, level1:struct<l2string:string, l2struct:struct<level3:string>>, quux:string>,

  wibble string,

  wobble array<struct<entry:int, entrydetails:struct<details1:string, details2:int>>>)

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s