Skip to main content
Shanoj
Shanoj
Silver ⭐️⭐️
March 5, 2023
Question

How to transform Oracle database output to desired format using Apache NiFi?

How can I use Apache NiFi to transform the current output of my Oracle database to the desired output format, as shown below? Desier out should be as mention below Avro format


Current output:

{ "type":"record", "name":"nifiRecord", "namespace":"org.apache.nifi", "fields":[ {"name":"AGENT_CODE", "type":["null","string"]}, {"name":"CORR_ACC_NO", "type":["null","string"]}, {"name":"LOCAL_ACC_NO", "type":["null","string"]} ] }

Desired output:

{ "type": "record", "name": "Bank", "namespace": "com.xyz.model.vo", "fields":[ {"name": "instance", "type": "string"}, {"name": "agent_code", "type": "string"}, {"name": "corr_acc_no", "type": "long"}, {"name": "local_acc_no", "type": "string"} ] }


What steps do I need to take in NiFi to achieve this transformation and output the results to the desired format? especially for changing { "type": "record", "name": "Bank", "namespace": "com.xyz.model.vo"

3 replies

SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
March 5, 2023

Hi,

 

You have few options like UpdateRecord Processor or JoltJsonTrnasformation. I recommend the UpdateRecord , for more information on how to use please refer to :

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.12.1/org.apache.nifi.processors.standard.UpdateRecord/

https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.12.1/org.apache.nifi.processors.standard.UpdateRecord/additionalDetails.html

It should be straight forward if you use the "Replace with Literal" strategy.

If that helps please accept solution

Thanks

 

"

 

 

Translate
Shanoj
ShanojAuthor
Silver ⭐️⭐️
March 5, 2023

Still, I am not able to replace  
{ "type":"record", "name":"nifiRecord", "namespace":"org.apache.nifi", 

to 

{ "type": "record", "name": "Bank", "namespace": "com.xyz.model.vo", "fields":

Translate
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
March 6, 2023

I apologize, I forgot that you are talking about an Avro format and not json. In this I think you need to pass your own avro schema. You can first convert Avro to Json using ConvertAvroToJSON processor , then use ConvertRecrod which can be configured as follows:

 

SAMSAL_0-1678063001801.png

 

In the AvroRecordWrite , you can set your schema with the proper record\namespace values. For Example in my case I used the "User Schema Text" strategy and either provide the schema directly in the Schema Text Property or reference it using flowfile attribute ${avro.schema} using Expression Language.

 

SAMSAL_1-1678063208276.png

 

The Schema Text :

 

{
	"type": "record",
	"name": "bank",
	"namespace": "org.xyz.com",
	"fields": [
		{
			"name": "AGENT_CODE",
			"type": [
				"null",
				"string"
			]
		},
		{
			"name": "CORR_ACC_NO",
			"type": [
				"null",
				"string"
			]
		},
		{
			"name": "LOCAL_ACC_NO",
			"type": [
				"null",
				"string"
			]
		}
	]
}

 

Hope that helps

Translate
Terms & ConditionsAccessibility statement