Insert a String into another String with the Query Record Processor

MarioFRS — Fri, 16 Sep 2022 13:57:08 GMT

Hey Everyone,

i would like insert a string into another string with the query Record Processor with the sql function STUFF but i run into an error. Anyone have an Idea how i can adjust the sql statemant or how i can this string manipulation with the nifi expression language

I want to insert a colon in a column with the time of day

from that	to the
0015	00:15

This is my input Data from an json file

[ { "store_id" : 1011, "date" : 20220915, "start_time" : "0015", "start_end" : "0015", "out" : 0 }, { "store_id" : 1011, "date" : 20220915, "start_time" : "0030", "start_end" : "0030", "out" : 0 }]

This is my Sql expression

SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE

This is the error message i got

QueryRecord[id=46658fcd-0183-1000-0000-000015e8db65] Unable to query StandardFlowFileRecord[uuid=641e1c71-ff9b-45dc-a948-e527eff450f6,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1663335669504-61321, container=default, section=905], offset=280506, length=7780],offset=0,name=aa02839a-3f71-4e8d-860a-9ecee9ba745f,size=7780] due to java.sql.SQLException: Error while preparing statement [SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE]: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature STUFF(, , , ) ↳ causes: org.apache.calcite.runtime.CalciteContextException: From line 4, column 3 to line 4, column 29: No match found for function signature STUFF(, , , ) ↳ causes: java.sql.SQLException: Error while preparing statement [SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE] ↳ causes: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Error while preparing statement [SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE]

That is the expected output as a csv file

store_id	date	start_time	start_end	out
1001	20220915	00:15	00:15	0

Thanks for help

Re: Insert a String into another String with the Query Record Processor

SAMSAL — Sun, 18 Sep 2022 18:01:31 GMT

Hi,

It doesnt seem like "Stuff" function is recognized function through the QueryRecord sql. I think you have two options:

1- If you are dumping this data into a database where you can use the stuff function there then delegate this to SQL before storing\processing the data.

2- Instead of trying to use QueryRecord processor I would try and use the JoltTransformJson with the following spec:

[ { "operation": "modify-overwrite-beta", "spec": { "*": { "tempStart": "=split('', @(1,start_time))", "tempEnd": "=split('', @(1,start_end))", "start_time": "=concat(@(1,tempStart[0]),@(1,tempStart[1]),':',@(1,tempStart[2]),@(1,tempStart[3]))", "start_end": "=concat(@(1,tempEnd[0]),@(1,tempEnd[1]),':',@(1,tempEnd[2]),@(1,tempEnd[3]))" } } }, { "operation": "remove", "spec": { "*": { "temp*": "" } } } ]

Not sure how this will perform with large dataset but its worth testing.

Hope that helps, if it does please accept solution.

Thanks

question Re: Insert a String into another String with the Query Record Processor in Support Questions

Insert a String into another String with the Query Record Processor

Re: Insert a String into another String with the Query Record Processor