There is actually a pretty easy way to do this. When you perform the ExecuteSQL, you will have data in Avro format, and you will have an attribute named executesql.row.count. All you need to do to get it to csv is use a single SplitRecord processor. Set RecordReader to a simple AvroReader Controller Service. Leave it at default so Schema Access Strategy = Use Embedded Avro Schema. For the RecordWriter now you need to setup your CSVRecordSetWriter Controller Service. Here all you need to do is create the Avro Schema based on the column names from your sql query. See below for an example. For Records Per Split just set this to ${executesql.row.count}. Now the flowfile that comes out of your SplitRecord will be a single file, with a header row, and all other rows from your db query as a nice csv file you can send of to SFTP or S3. Example Avro Schema (nice thing here is you don't really care about type, since we are just putting into a csv, just use string so everything validates ok). {
"type" : "record",
"name" : "Sample",
"doc" : "Schema generated by Your's Truly",
"fields" : [{
"name" : "field_a_from_select_statement",
"type" : "string",
"default" : null
},{
"name" : "field_b_from_select_statement",
"type" : "string",
"default" : null
},{
"name" : "field_c_from_select_statement",
"type" : "string",
"default" : null
}]
}
... View more
