在同一列的NiFi中将字符串值替换为整数值

问题描述

我要替换一个'字符串值'，然后使用Nifi ReplaceText将其输入为 Integer Value .我不知道如何在NiFi中实现这一目标.

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.

所以我的情况如下:

我将拥有一个CSV文件，其中包含几个带有整数的字段和几个字段作为总String值.对于具有String值的字段，我想将其作为预定义的整数插入到我的数据库表中.例如:我的CSV如下所示:

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:

1,2， abc ，45， John

23,12， pqr ，28，山姆

23,12,pqr,28,Sam

98,75， lmn ，87， Smith

98,75,lmn,87,Smith

所以现在在MySQL表中，Field3和Field4的数据类型为Integer，因此在将CSV插入数据库之前，我想将字符串值更改为Predefined Integer value即对于Field3 abc值，应在MySQL表中输入0，pqr为1，lmn为2，依此类推....类似地，在MySQL表中，Field4中的值应输入John为10，Sam为20和Smith.作为30 ...因此我的上述csv应该如下存储在我的数据库中:

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:

1,2， 0 ，45， 10

23,12， 1 ，28， 20

23,12,1,28,20

98,75， 2 ，87， 30

98,75,2,87,30

我认为我可以使用ReplaceText实现此目的.但是，如果有更好的方法可以实现这一点，那就太好了.因为我的CSV将具有1000+列，并且假设50+列将具有我需要处理并转换为预定义整数值的String值.是否可以在NiFi中实现这一目标?

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?

谢谢！

推荐答案

使用 QueryRecord 处理器并配置/启用 Reader/Writer 控制器服务

• 将custom sql query作为新属性添加到处理器

• Add custom sql query as new property to the processor

QueryRecord配置:

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

QueryRecord处理器的输出流文件将具有您想要的结果

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

使用此模板，以供您参考上述流程，并 QueryRecord 处理器使用 Apache Calcite sql解析器.

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

这篇关于在同一列的NiFi中将字符串值替换为整数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！