question Re: create hive table from nested json file in NIFI in Support Questions

create hive table from nested json file in NIFI

tulasi_harishku — Fri, 11 May 2018 00:50:04 GMT

I have a very complex nested JSON file, with atleast six level of nested at some sections.

I am trying to load the nested JSON to HIVE table. Could anyone please help me how to do it???

My approaches:

1. Flatten the nested JSON file and load it(wasn't sure what processors to use in NIFI-trying to use JOLT but I am pretty new to it)

2. Load directly the JSON file to HIVE(without flattening)

Attached sample JSON file nestedjson.txt

Re: create hive table from nested json file in NIFI

tulasi_harishku — Fri, 11 May 2018 01:03:09 GMT

@Matt Burgess

Re: create hive table from nested json file in NIFI

mburgess — Mon, 21 May 2018 21:16:10 GMT

For approach #1, you could use the FlattenJson processor, you'll likely want to set the Separator property to "_" rather than the default "." since Hive adds the table name to each column in a ResultSet.

For approach #2, you could have a single column table (column of type String), then you'd query it with get_json_object (example here). Alternatively if you can map all the types (including the complex types like array, list, struct, etc.) to a Hive table definition, you could use a JSON SerDe to write the data (example here).