Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

create hive table from nested json file in NIFI

avatar

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

1 ACCEPTED SOLUTION

avatar
Master Guru

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

View solution in original post

2 REPLIES 2

avatar

avatar
Master Guru

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