how to define condition based text replacement in 'replaceText' processor

AbhishekSingh — Wed, 20 Jul 2022 11:26:45 GMT

Hi,

I want to modify the ddl query table name. I am using the replaceText processor to modify the flowfile query table name. Currently I am able to achieve it because I have one source DB and only one table name I have changed in destination DB.

Like sourceDBName: nrpsubscriotion and tableName: status -> destinationDBName: nrpresport and tableName: subscription_status.

Now I have two source DB(1. nrpsubscriotion, 2. nrpuserorg) and both have same tableName: status. Now Source DB 2 , tableName in destination DB nrpreport has tableName: user_org_status.

I have stuck here , I am not getting how can define a condition to replce a tableName in DDL based on schema name and ddl query able name:

It should map like below:

1. SourceDBName: nrpsubscriotion and tableName: status -> destinationDBName: nrpresport and tableName: subscription_status.

Flow File input:

{
"type" : "ddl",
"timestamp" : 1658314685000,
"binlog_gtidset" : "e75d07af-eb37-11ec-9d1d-a86daa745b08:1-206",
"database" : "nrpsubscription",
"table_name" : null,
"table_id" : null,
"query" : "ALTER TABLE `status` ADD COLUMN `is_test` VARCHAR(255) NULL AFTER `code`"
}

Expected output:

{
"type" : "ddl",
"timestamp" : 1658314685000,
"binlog_gtidset" : "e75d07af-eb37-11ec-9d1d-a86daa745b08:1-206",
"database" : "nrpsubscription",
"table_name" : null,
"table_id" : null,
"query" : "ALTER TABLE `subscription_status` ADD COLUMN `is_test` VARCHAR(255) NULL AFTER `code`"
}

2. SourceDBName: nrpuserorg and tableName: status -> destinationDBName: nrpreport and tableName: user_org_status.

{
"type" : "ddl",
"timestamp" : 1658314686000,
"binlog_gtidset" : "e75d07af-eb37-11ec-9d1d-a86daa745b08:1-207",
"database" : "nrpuserorg",
"table_name" : null,
"table_id" : null,
"query" : "ALTER TABLE `status` ADD COLUMN `is_test` VARCHAR(255) NULL AFTER `code`"
}

Expected output:

{
"type" : "ddl",
"timestamp" : 1658314686000,
"binlog_gtidset" : "e75d07af-eb37-11ec-9d1d-a86daa745b08:1-207",
"database" : "nrpuserorgdb",
"table_name" : null,
"table_id" : null,
"query" : "ALTER TABLE `user_org_status` ADD COLUMN `is_test` VARCHAR(255) NULL AFTER `code`"
}

Re: how to define condition based text replacement in 'replaceText' processor

steven-matison — Wed, 20 Jul 2022 12:14:47 GMT

@AbhishekSingh I believe you can accomplish this with some very complicated Expression Language chaining. It is possible to have many Expression Languages combined into one expression. Reference:

https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html

I do not have time to prove this out, but in theory, you would combine lesson learned in your other post (expression language to change the database.table with Replace, ifElse and maybe Equals

to get your desired outcome.

question how to define condition based text replacement in 'replaceText' processor in Support Questions

how to define condition based text replacement in 'replaceText' processor

Re: how to define condition based text replacement in 'replaceText' processor