How to use ConvertRecord and CSVRecordSetWriter to output header (with no data) in Apache NiFi?

Question

I'm working with Apache NiFi to pull data from a database table and use the ConvertRecord to change the fetched records from avro to CSV file using AvroReader and CSVRecordSetWriter.

My process works fine when at least one row comes back from the database.

However, when the ExecuteSQL processor outputs a FlowFile containing 0 records (the FlowFile still contains the metadata/avro schema) - ConvertRecord gives me a 0-byte FlowFile output. My settings in the CSVRecordSetWriter service are set to output the header.

Is there a way to have ConvertRecord output a FlowFile that contains just the the header line in this circumstance?

One question: ExecuteSQL -> CovertRecord is good but can you use QueryDatabaseRecord instead since that combines both of them together? — Sivaprasanna Sethuraman
– Sivaprasanna Sethuraman, Commented Sep 14, 2018 at 5:43

notNull · Accepted Answer · 2018-09-13 21:29:59Z

3

ExecuteSql Processor adds executesql.row.count(Contains the number of rows returned in the select query) attribute to the flow.

You can check value of the attribute(executesql.row.count) and take decision do we need to use ConvertRecord processor or not.

Flow:

Executesql(success)--> RouteOnAttribute(add new property ${executesql.row.count:equals(0)}) 
     --> matched --> ReplaceText //Add Header
     --> unmatched --> ConvertRecord //convert to csv format

if matched then use ReplaceText processor to overwrite the existing flowfile content with header.

if Unmatched that means you are having some rows of data in the flowfile content so use ConvertRecord processor and prepare csv data.

edited Sep 13, 2018 at 21:29

answered Sep 13, 2018 at 21:17

notNull

31.8k4 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mattyb Over a year ago

This same capability will be offered as a property in ConvertRecord in NiFi 1.8.0 (via NIFI-5382).

thinice Over a year ago

@mattyb I was looking at that but it wasn't 100% clear if it would get that header to populate

thinice Over a year ago

I considered an approach like that, I wanted to keep things closer to the query (as I have many of these in a template situation); As a kind of 'workaround' I ended up using a UNION SELECT null as 'Foo' pattern, and then doing a regex replace with ReplaceText. A little extra work but seems to work. I look forward to 1.8.0 to see if that simplifies this.

thinice Over a year ago

@Shu when you say "content with header" are you saying the replacement text is a static string of the header, or is there some slick way to extrapolate the Avro schema metadata into the CSV compliant delimited format?

notNull Over a year ago

@thinice one way is using Static string of the header (or) another way is to use ExtractAvroMetaData processor and extract avro.schema from the avro data file then use ReplaceText the content of flowfile with avro.schema value. The second way is dynamic based on the avro data file's avro.schema Metadata Keys

Collectives™ on Stack Overflow

How to use ConvertRecord and CSVRecordSetWriter to output header (with no data) in Apache NiFi?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related