How to replace NIFI attribute value extracted from ExecuteSQL with NULL

0
votes

I am using apache NIFI ExecuteSQL processor to extract data from Oracle db. I have the fields X,Y and Z extracted, now I would like to replace the values for column X to NULL and load the data to HDFS. Can anyone suggest, which processor should be used after ExecuteSQL processor to achieve this and what changes have to be configured in the processor?

1
apache-nifi

1 Answers

1
votes

Method-1:(Easiest)

Use ExecuteSQL processor and add cast value to X to NULL while getting data from the OracleDB.

Example:

Select Cast(NULL as String) as X,Y,Z from <schema_name>.<tab_name> //change the syntax as necessary..

Method-2:

You can use Record oriented processors like QueryRecord (or) UpdateRecord processors and define Record reader/Writer controller services to read and write the data in your required format.

1.Using QueryRecord processor:

Add new Apache Calcite SQL query:

Select Cast(NULL as String) as X,Y,Z from FLOWFILE

2.Using UpdateRecord processor:

Choose

Replacement Value Strategy as Literal Value

Add new property 

/X

with value as 

${literal("NULL")}