Question posted in
Some documentation to get you started can be found here.

Replace string value to integer value in NiFi within a same column – Apache

I want to replace a ‘string value‘ and enter that value as an Integer Value using Nifi ReplaceText. I do not know how to achieve this in NiFi.

So my scenario is as follows:

I will be having a CSV file with few fields with integers and few fields as total String values. For the fields that have a String value, I want to insert that as a pre-defined integer number into my database table. For Example:
My CSV looks like the following:

Field1, Field2, Field3, Field4,Field5

1,2,abc,45,John

23,12,pqr,28,Sam

98,75,lmn,87,Smith

So now in MySQL table, the Field3 and Field4 have datatype as Integer, so before inserting the CSV into the Database I want to Change the string value into Pre defined Integer value I.e. For Field3 abc value should be entered into the MySQL Table as 0, pqr as 1, lmn as 2 and so on…. similarly the value in Field4 should be entered in the MySQL table as John as 10, Sam as 20 and Smith as 30…so my above csv should be stored as follows in my database:

Field1, Field2, Field3, Field4,Field5

1,2,0,45,10

23,12,1,28,20

98,75,2,87,30

I think I can achieve this with ReplaceText. But if there is any better way to achieve this That would be great. Because My CSV will have 1000+ columns and let’s say 50+ columns would have a String value that I need to process and convert into a pre-defined integer value. Is it possible to achieve this in NiFi?

If yes then what configs to set and what will be the best approach?

Thank you!

Tags:

2

Answers


  1. 0

    Use QueryRecord processor and configure/enable Reader/Writer controller services

    • Add custom sql query as new property to the processor

    QueryRecord configs:

    select Field1,Field2,
    CASE WHEN Field3='abc' THEN '0'
         WHEN Field3='pqr' THEN '1'
         WHEN Field3='lmn' THEN '2'
    end Field3,
        Field4,
    CASE WHEN Field5='John' THEN '10'
         WHEN Field5='Sam' THEN '20'
         WHEN Field5='Smith' THEN '30'
    end Field5 
from FLowfile

    enter image description here

    The output flowfile from QueryRecord processor will have your desired result

    Field1,Field2,Field3,Field4,Field5
1,2,0,45,10
23,12,1,28,20
98,75,2,87,30

    Use this template for your reference to the above flow and QueryRecord processor uses Apache Calcite sql parser.

    Login or Signup to reply.
  2. 0

    What @Shu had suggested would work well but in case the number of lookups to be done is more or increasing, I would highly suggest to use the LookupRecord processor and configure it with a SimpleKeyValueLookupService for starters since your CASE statement would grow heavy and troublesome.

    So your flow can be altered to something like

    enter image description here

    For a look on how to use, take a look at the simple template provided here : https://gist.github.com/zenfenan/37eb1e4cd0e59a63e85b0400040720b6

    Useful links:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search