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

问题描述

我想使用 Nifi ReplaceText 替换 '字符串值' 并输入该值作为 整数值.我不知道如何在 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 文件，其中包含几个带有整数的字段和几个作为总字符串值的字段.对于具有字符串值的字段，我想将其作为预定义的整数插入到我的数据库表中.例如:我的 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:

Field1、Field2、Field3、Field4、Field5

1,2,abc,45,约翰

23,12,pqr,28,山姆

98,75,lmn,87,史密斯

所以现在在 MySQL 表中，Field3 和 Field4 的数据类型为整数，因此在将 CSV 插入数据库之前，我想将字符串值更改为预定义的整数值，即对于 Field3 abc 值应作为 0 输入到 MySQL 表中，pqr 为 1，lmn 为 2 等等......类似地，Field4 中的值应作为 John 为 10，Sam 为 20 和 Smith 输入到 MySQL 表中作为 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:

Field1、Field2、Field3、Field4、Field5

1,2,0,45,10

23,12,1,28,20

98,75,2,87,30

我想我可以通过 ReplaceText 实现这一点.但是如果有更好的方法来实现这一点那就太好了.因为我的 CSV 将有 1000 多列，假设 50 多列将有一个字符串值，我需要处理该值并将其转换为预定义的整数值.是否有可能在 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 控制器服务

• 将自定义sql查询作为新属性添加到处理器

• 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 中的整数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！