Support Questions
Find answers, ask questions, and share your expertise

create hive table from nested json file in NIFI

New Contributor

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

Super 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

New Contributor

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.