In order to parse Json and create a SQL schema below are Steps to prepare:
- 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/