Get data from json object inside csv using apache-nifi

Question

My csv contain

date,name,department
2020-2-4,sachith,{dep_name:computer,location:2323,3434}
2020-2-5,nalaka,{dep_name:engineering,location:3343,5454}

final csv should be like :

date,name,dep_name,lat,lot
2020-2-4,sachith,computer,2323,3434
2020-2-5,nalaka,engineering,3343,5454

here lat,lot are taken from location:3343,5454 data.

I have tried to use UpdateRecord processor for this. In it has some ${field.value:join(','):substringAfter('dep_name:')}

But its not working. How can I complete this using apache-nifi?

@daggett Yes, I was trying with groovy, but my groovy knowledge is not enough for this. — Sachith Muhandiram
– Sachith Muhandiram, Commented Feb 6, 2020 at 10:36
the problem that {dep_name:computer,location:2323,3434} is not a valid json... it should be as minimum like this: {dep_name:computer,location:[2323,3434]}. is it possible to fix this? — daggett
– daggett, Commented Feb 6, 2020 at 12:08
@daggett what if is its in {"dep_name":"computer","location":"2323,3434"}? This is valid, right? — Sachith Muhandiram
– Sachith Muhandiram, Commented Feb 6, 2020 at 12:11

daggett · Accepted Answer · 2020-02-06 13:16:50Z

2

plain groovy to test script in groovyConsole:

import groovy.json.*

def parser = new JsonSlurper().setType(JsonParserType.LAX) //LAX to accept strings without double-quotes

def w = System.out
new StringReader('''date,name,department
2020-2-4,sachith,{"dep_name":"computer","location":"2323,3434"}
2020-2-5,nalaka,{"dep_name":"engineering","location":"3343,5454"}''').withReader{r->
    r.eachLine{line, lineNum->
        if(lineNum==1){
            w<<line<<',lon,lat'<<'\n'
        }else{
            def row=line.split(',')          //split line by coma
            def json=row[2..-1].join(',')    //join back to string starting from 3rd element
            json = parser.parseText(json)
            w<<"${row[0]},${row[1]},${json.dep_name},${json.location}"<<'\n'
        }
    }
}

now the same script modified for nifi ExecuteGroovyScript processor:

import groovy.json.*

def ff=session.get()
if(!ff)return

def parser = new JsonSlurper().setType(JsonParserType.LAX)

ff.write{streamIn,streamOut->
    streamIn.withReader('UTF-8'){r->      //convert in stream to reader
        streamOut.withWriter('UTF-8'){w-> //convert out stream to writer
            //go line by line
            r.eachLine{line, lineNum->
                if(lineNum==1){
                    w<<line<<',lon,lat'<<'\n'        //for the first line just add some headers
                }else{
                    def row=line.split(',')          //split line by coma
                    def json=row[2..-1].join(',')    //join back to string starting from 3rd element
                    json = parser.parseText(json)
                    w<<"${row[0]},${row[1]},${json.dep_name},${json.location}"<<'\n'
                }
            }
        }
    }
}
REL_SUCCESS<<ff

edited Feb 6, 2020 at 13:16

answered Feb 6, 2020 at 12:29

daggett

29k3 gold badges52 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sachith Muhandiram Over a year ago

Failed to process session due to javax.script.ScriptException.... No Signature of method... Possible solutions: wait(),wait(long)..

daggett Over a year ago

I think you are not using ExecuteGroovyScript

Sachith Muhandiram Over a year ago

Yes, It was my mistake. What is the difference between ExecuteScript -> groovy and ExecuteGroovyScript

daggett Over a year ago

ExecuteGroovyScript has more groovy features. It compiled once (on code change). It supports start-stop event handling. Better error displaying. Finally it just newer )

Collectives™ on Stack Overflow

Get data from json object inside csv using apache-nifi

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related