create hive table from nested json file in NIFI

[#1]

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

[#2]

@Matt Burgess

[#3 · Accepted solution]

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).