ExecuteSQL returns empty avro file

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

ExecuteSQL returns empty avro file

Nabegh
When no records are returned from the SQL statement, ExecuteSQL returns an avro file that contains the schema with no data. Is this the expected behaviour?

Thanks.
Reply | Threaded
Open this post in threaded view
|

Re: ExecuteSQL returns empty avro file

Mark Payne
This is the expected behavior. There was a discussion on the dev@ mailing list about whether or not
it should output anything at all in that case. The case was made that it is sometimes beneficial to know
that the query did run and that no results were found.

If you want to filter out these results, you can use a RouteOnAttribute processor and route based on the
executesql.row.count attribute. For example, you can create a property named "at.least.one.row"
with a value of "${executesql.row.count:gt(0)}" and then auto-terminate the 'unmatched' relationship.
This will filter out any FlowFile that contains 0 rows.

Hope this helps!
-Mark

> On Aug 5, 2016, at 2:37 PM, Nabegh <[hidden email]> wrote:
>
> When no records are returned from the SQL statement, ExecuteSQL returns an
> avro file that contains the schema with no data. Is this the expected
> behaviour?
>
> Thanks.
>
>
>
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/ExecuteSQL-returns-empty-avro-file-tp12965.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Reply | Threaded
Open this post in threaded view
|

Re: ExecuteSQL returns empty avro file

Jeff
In reply to this post by Nabegh
Nabegh,

I took a quick look at the code, and it looks like that is the expected
behavior.  There should be a flowfile attribute "executesql.row.count" that
contains the number of rows.  You could always route on that attribute to
handle those empty result sets differently than ones that have one or more
rows of data in the resulting avro file.

On Fri, Aug 5, 2016 at 3:52 PM Nabegh <[hidden email]> wrote:

> When no records are returned from the SQL statement, ExecuteSQL returns an
> avro file that contains the schema with no data. Is this the expected
> behaviour?
>
> Thanks.
>
>
>
> --
> View this message in context:
> http://apache-nifi-developer-list.39713.n7.nabble.com/ExecuteSQL-returns-empty-avro-file-tp12965.html
> Sent from the Apache NiFi Developer List mailing list archive at
> Nabble.com.
>