Reply
Highlighted
New Contributor
Posts: 1
Registered: ‎02-21-2018

How to create hive table out of JSON Schema

Hi,

 

I have JSON schema which is very deeply nested, how can we automatically create hive DDL out of JSON schema.

 

I did googling and all I am seeing how to create hive table out of JSON data.

 

Thanks,

Jai

Expert Contributor
Posts: 69
Registered: ‎11-24-2017

Re: How to create hive table out of JSON Schema

You can use  JSON Serde. You have to create the table with a structure that maps the structure of the json. 

For example:

 

data.json

{"X": 134, "Y": 55, "labels": ["L1", "L2"]}
{"X": 11, "Y": 166, "labels": ["L1", "L3", "L4"]}

create table

CREATE TABLE Point
(
    X INT,
    Y INT,
    labels ARRAY<STRING>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
LOCATION 'path/to/table';

Then you should upload your json file in the location path of the table, giving the right permissions and you are good to go.

 

 

Announcements