Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor

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`"
}

 

 

1 REPLY 1

avatar

@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.