Data Engineering

Getting a sql schema from JSON

Omid

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';



——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/



Leave a Reply