Create hive table from JSON data

Question

I have a file with Json data which takes the below form:

Ex:

 {
    "Name": "xxxx",
    "Address": [{
        "Street": "aa",
        "City": "bbb"
    }, {
        "Street": "ccc",
        "City": "ddd",
        "Country": "eee"
    }]
}

The above Json is a valid Json. I want to create a hive table on top of data of above form using JsonSerde.

Answer 1

Create table with all possible fields defined. If field is not present in json, select will return NULL:

CREATE EXTERNAL TABLE your_table (
Name string,
Address array<struct<Street:string,City:string,Country:string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'

if you have names in JSON file which conflict with Hive reserved words then add mapping and rename names in table definition:

WITH SERDEPROPERTIES ('mapping.renamed_column'='original_column') and rename your table columns.

Put your file in the table location.

See also docs with some examples here: https://github.com/rcongiu/Hive-JSON-Serde