Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

create hive table from nested json file in NIFI

Solved Go to solution

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

Accepted Solutions
Highlighted

Re: create hive table from nested json file in NIFI

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
Highlighted

Re: create hive table from nested json file in NIFI

New Contributor
Highlighted

Re: create hive table from nested json file in NIFI

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

Don't have an account?
Coming from Hortonworks? Activate your account here