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.

Converting json to CSV giving blank using Nifi

Solved Go to solution
Highlighted

Converting json to CSV giving blank using Nifi

Rising Star

Hi,

I have my JSON file like mentioned below:


The flow for conversion for json to csv :

1. Get the file

2. Split json

3. Evaluate json path

4. Replace text

I want to see values in csv format of memberid and loanamnt and 'm getting blank for the two columns like "".""

Can someone help me in this?? Attached are the Screenshots of the processor properties.

64583-evaluate-json-property.png

64582-json-split-property.png


replace-txt-proprty.png
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Converting json to CSV giving blank using Nifi

Super Guru

@Vaibhav Kumar

The issue is with Failure,Original,Unmathed relationship as we need to auto terminate them or connect them to put email processor to get notification if something gone wrong.

Steps to Autoterminate relationships:-

Splitjson:-

  1. Delete all the connections that are feeding to EvaluateJsonpath from SplitJson processor
  2. RightClick on SplitJson Processor
  3. Goto Settings tab
  4. Click on Check box at Failure and Original relationships.

    62874-autoterminate-splijson.png
    then connect only split relationship to EvaluateJson Path processor.
    EvaluateJson Path:-
    1.Delete all existing relationships that are feeding to ReplaceText processor
    2.Right Click on EvalJsonPath Processor
    3.Goto Setting Tab
    4.Click on check boxes before Failure and Unmatched relationships
    5.Connect only the matched relationship to Replacetext processor
    ReplaceText Processor:-
    1.Delete all existing relationships that are going out from Replacetext processor

    2.Right Click on Replacetext Processor
    3.Goto Setting Tab
    4.Click on check boxes before Failure relationship
    5.Connect only the success relationship from Replacetext processor
      I have attached the xml flow below, save the xml flow and upload it to your NiFi instance and change the configs in GetFile Processor.

    If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

176561-jsontocsv.xml

View solution in original post

9 REPLIES 9
Highlighted

Re: Converting json to CSV giving blank using Nifi

Rising Star

Input file is present in the attachment section. Coudn't add it to the main setion.

Highlighted

Re: Converting json to CSV giving blank using Nifi

Super Guru
@Vaibhav Kumar

I have tried with your input Json data and processor configs,all your configs looks good. i'm able to get the flowfile with "1","1000" from replacetext processor.

Can you make sure the feeding connections to the processors are correct based on the below screenshot.

62870-configs.png

If the issue still exists then share your flow screenshot or xml so that we can help you ASAP.

Highlighted

Re: Converting json to CSV giving blank using Nifi

Rising Star

Add your reply is not working so have to post it as answer

@Shu Please find below the screenshot. I can see there is something different in the things highlighted in you image and mine. Can you suggest the changes i need to make?

64587-workflow.png

Highlighted

Re: Converting json to CSV giving blank using Nifi

Super Guru

@Vaibhav Kumar

The issue is with Failure,Original,Unmathed relationship as we need to auto terminate them or connect them to put email processor to get notification if something gone wrong.

Steps to Autoterminate relationships:-

Splitjson:-

  1. Delete all the connections that are feeding to EvaluateJsonpath from SplitJson processor
  2. RightClick on SplitJson Processor
  3. Goto Settings tab
  4. Click on Check box at Failure and Original relationships.

    62874-autoterminate-splijson.png
    then connect only split relationship to EvaluateJson Path processor.
    EvaluateJson Path:-
    1.Delete all existing relationships that are feeding to ReplaceText processor
    2.Right Click on EvalJsonPath Processor
    3.Goto Setting Tab
    4.Click on check boxes before Failure and Unmatched relationships
    5.Connect only the matched relationship to Replacetext processor
    ReplaceText Processor:-
    1.Delete all existing relationships that are going out from Replacetext processor

    2.Right Click on Replacetext Processor
    3.Goto Setting Tab
    4.Click on check boxes before Failure relationship
    5.Connect only the success relationship from Replacetext processor
      I have attached the xml flow below, save the xml flow and upload it to your NiFi instance and change the configs in GetFile Processor.

    If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

176561-jsontocsv.xml

View solution in original post

Highlighted

Re: Converting json to CSV giving blank using Nifi

Rising Star

@ShuAccepted.

It would be good if you can clarify what exactly happens at the background when we check/un-check relation checkboxes. I couldn't find a good description online.

Moreover while importing saved XML template it shows error that same template already exist. I think it has something related to PCG. Any comments on this??

Highlighted

Re: Converting json to CSV giving blank using Nifi

Super Guru

@Vaibhav Kumar,
i think while uploading template you are facing the below issue

62889-template-issue.png

if so that means you have already uploaded the template and again you are trying to upload the template that having same name again, at this time NiFi throws an already exists error.
To make sure what are the available templates in your NiFi instance then

go to top right corner Global Menu
Click on Templates Option

then you can view what are the available Templates in the NiFi instance.

For More details please refer to below link

https://docs.hortonworks.com/HDPDocuments/HDF3/HDF-3.0.0/bk_user-guide/content/templates.html

what exactly happens at the background when we check/un-check relation checkboxes ?

Splitjson:-

Lets assume your input json record

[
  {
 "member_id": 1,
 "loan_amnt": 5000
},{
 "member_id": 2,
 "loan_amnt": 1000
}
]

In split json processor we are having three relationships

1.split --> as we are having a json array with 2 records as shown above, this relation gets the splitted records i.e 2 in our case.

2.original --> as this relation states original i.e what ever the input json that split json processor that will be routed to this relation, in our case json array(i.e same as input record that shown above code snippet) that having 2 records in it.

3.failure --> the FlowFile is not valid JSON or the specified path does not exist), it will be routed to this relationship, mostly used to get notifications if the process failed.

So when we feed split,original,failure relations to EvaluateJsonPath processor

1.from split relation as we are having 2 splitted records and we can get member_id,loan_amnt attribute values 1,5000.. from these flowfiles.Replace text processor gets all the values in the csv file.

2.from original relation we are going to have our input json array message to EvaluateJsonPath processor for this flowfile we are not able to get the attribute values for member_id,loan_amnt because it is inside array, So for this flowfile we will have empty attribute values and when we did replace text processor we are going to have empty value for the attributes.

3.As we are having a valid json so we are not going to have any flowfile routed to failure relation.

Let us know if you having any other questions ..!!

Highlighted

Re: Converting json to CSV giving blank using Nifi

Rising Star

I can't see the add to reply option so osting it here again

@Shu Thanks for such a detailed explanation.

Even if i try to import the template with a changed template name during import it still shoes the same error.

64604-template-error.png

Re: Converting json to CSV giving blank using Nifi

Super Guru
@Vaibhav Kumar

we need to change the name tag in the xml file i.e open the .xml file in any editor like Notepad++ ..etc then

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<template encoding-version="1.2">
    <description></description>
    <groupId>3b738dba-0161-1000-c808-f7d38f21fcab</groupId>
    <name>176561_jsontoCSV</name>
    <snippet>

edit the name inside the .xml file then you can import same template again.

As NiFi keeps the name of the template based on the name tag in the .xml file, if we do rename for the xml file it won't change the name tag filename so NiFi able to see duplicated template names and throws already exists error.

Highlighted

Re: Converting json to CSV giving blank using Nifi

Rising Star
@Shu

Appreciate your efforts.

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