Insert a String into another String with the Query Record Processor

[#1]

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

[#2 · Accepted solution]

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