1

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Cheater
  • 435
  • 1
  • 4
  • 16

1 Answers1

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

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Probably, my question is misleading. But, infact I need to create a table structure which accepts an Array of different data types. I read this is not possible. But, needed a work around for this. – Cheater Jun 12 '17 at 21:29