How to store the output of a query to one text file but getting the Error and data is in orc format

[#1]

Hi All,

I want to store the output of a query to one text file but getting the Error and data is in orc format.

Example:

[ixdi@wplcd-dse1 AP_POC]$ more ap_poc_track_maxdate.txt
Objavro.schema▒{"type":"record","name":"NiFi_ExecuteSQL_Record","namespace":"any.data","fields":[{"name":"MAX_CREATE_DATE","type":["null","string"]}]}racle.sql.TIMEMP@75ee4c11E▒E▒K▒X▒▒W▒\7
[ixdi@wplcd-dse1 AP_POC]$ more ap_data_track_load_ts.orc
Objavro.schema▒{"type":"record","name":"NiFi_ExecuteSQL_Record","namespace":"any.data","fields":[{"name":"MAX_CREATE_DATE","type":["null","string"]}]}oracle.sql.TIMAMP@5490a008▒<=<q▒s▒H=IQc▒

One more thing I want to know is How Notify Processor will work? It will notify to continue next processor once upstream processor success or it will process the data of upstream connection.

Please help me to resolve this.

Thank you in Advance.

Regards,

Jyoti

[#2]

@Jyoti Ambi

ExecuteSQL processor always return results in Avro Format, so once you get results in avro format then you need to use

ConvertRecord processor 
Record Reader --> Avro Reader //reads the incoming avro format flowfile contents
Record Writer --> CsvRecordSetWriter //write the output results in csv format

then use PutFile processor to store the output success flowfiles from ConvertRecord processor.

To configure this ConverRecord processor please refer to below links

https://blogs.apache.org/nifi/entry/record-oriented-data-with-nifi

https://community.hortonworks.com/articles/115311/convert-csv-to-json-avro-xml-using-convertrecord-p...

https://community.hortonworks.com/questions/167066/how-to-split-the-csv-files-into-multiple-files-ba...

2.Wait and Notify processors are work together i.e wait processor Routes incoming FlowFiles to the 'wait' relationship until a matching release signal is stored in the distributed cache from a corresponding Notify processor. When a matching release signal is identified, a waiting FlowFile is routed to the 'success' relationship, with attributes copied from the FlowFile that produced the release signal from the Notify processor.

How wait and notify processors works?

http://ijokarumawak.github.io/nifi/2017/02/02/nifi-notify-batch/

[#3]

@Jyoti Ambi

I think your Avro Reader configs looks correct and in CsvSetwriter change configs as mentioned in below screenshot

Schema Access Strategy as we are ingeriting the record schema from content of flowfile so i have setup that.

As i haven't mentioned any Schema Registry value because we don't want to get schema from any of the registries as we are having schema available with record we are inheriting that record schema.

Avro Reader Configs:-

If you are still having issues then share us sample data like 10 records in csv(with header) (or) json format, so that we can recreate your scenario and help you out to solve your issue.

[#4]

@Shu

I used ConvertRecord processor but getting below error

My FlowFile has the following start structure.
Obj^A^B^Vavro.schema<8a>^B{"type":"record","name":"customer_crm_summary","namespace":"any.data","fields":[{"name":"device_tac_code","type":["null","string"]}]}
I would think this seems all ok but the the AvroReader fails with the Exception below SchemaNotFoundException

avroreader.pngcsvrecordsetwriter.png

Please help.

[#5]

@Shu

I am unable to see Schema Access Strategy --->Inherit Record Schema.

In my query just I am extracting the MAX(CREATE_DATE) from one table and want store it in file

schemaaccessstrategy.png

Today posted one more question , kindly help..

https://community.hortonworks.com/questions/175820/i-want-to-give-where-condition-in-querydatabaseta...

[#6]

@Jyoti Ambi

Instead of doing all this convertrecord processor choose either of below methods.

Method1:-
Executesql Processor properties

SQL Query:-

select MAX(CREATE_DATE) CREATE_DATE from one table

Then use ConvertAvrotoJSON processor so we are converting Avro data to json

Configs:-

Your data would be like this

[{"CREATE_DATE": "2018-10-12 09:09:09"}]

Then use EvaluateJsonPath Processor to get the create_date value as flowfile content

Configs:-

Output flowfile content from evaljsonpath processor would be

2018-10-12 09:09:09

Then you can use PutFile processor to store your file into Local directory.

Flow:-

(or)

Method2:-

If you want header while keeping file into your directory,
then in EvaluateJsonpath processor change the property

Destination

flowfile-attribute

Then add ReplaceText processor

Configs:-

Now we are creating new contents of flowfile by keeping header as CREATE_DATE and in new line we are keeping our create_date attribute value (i.e. 2018-10-12 09:09:09)

Output:-

CREATE_DATE
2018-10-12 09:09:09

Then use PutFile processor to store above file into your local.
Flow:-

Executesql --> ConvertAvrotoJSON --> EvaluateJSONpath(destination as flowfile attribute) --> Replace text -->putfile