Mark, Thank you for your response. I thought that was probably the case, but I tried a cast and it did not work. I got this error.
Query: select * from flowfile where cast(flag_s as boolean) = true Error: org.apache.calcite.sql.validate.SqlValidatorException: Cast function cannot convert value of type JavaType(class java.lang.Object) to type BOOLEAN By taking the union out of the input schema I could get the query to work, but I did find myself getting tangled up in managing various schemas so I am trying to use infer/inherit read/write services instead. I have inherited a very complex flow from a team that have long departed and am looking to simplify it to improve performance and maintainability. I need to convert from CSV/TSV to JSON, normalise fields, filter unwanted records, enrich with more JSON and finally publish to a customer defined schema, so I do need a few steps along the way. I am exploring each step in order to validate my redesign so I take your point about minimising the number of processes and will look again at combining steps in the query process, although I am also a fan of the JOLT transform as I have used that often in previous projects. Regards Steve Hindmarch From: Mark Payne <marka...@hotmail.com> Sent: 17 March 2022 14:17 To: users <users@nifi.apache.org> Subject: Re: QueryRecord with Union type Steve, Because your schema has a union, the SQL engine doesn’t really know how to interpret the data. So it interprets it as a “Java Object.” Essentially, it could be anything. But you can’t compare just anything to true - you need to compare a boolean to true. So you need to tell the SQL engine that the value you’re looking at is, in fact, a boolean. You can do that with a simple CAST() function in your SQL: SELECT * FROM FLOWFILE WHERE CAST(flag_s AS BOOLEAN) = true That should give you what you’re looking for. Also worth nothing - you mentioned that you’re using ConvertRecord and UpdateRecord before QueryRecord. 99% of the time, you should not be using ConvertRecord in conjunction with any other Record processor. Because the Record processors like UpdateRecord allow you to use any Record Reader, it doesn’t make sense to convert the data first using ConvertRecord - it’s just extra overhead. And, in fact, you may be able to eliminated the UpdateRecord, as well, as just use the SQL within QueryRecord to perform the transformation needed on the fly, rather than having another step to update the data, which requires reading the data, parsing it, updating it, serializing the data, writing the data. This may not be possible, depends on what you’re updating. But QueryRecord does support RecordPath expressions so it’s worth considering. Thanks -Mark On Mar 15, 2022, at 8:35 AM, stephen.hindma...@bt.com<mailto:stephen.hindma...@bt.com> wrote: I am having a play with QueryRecord to do some filtering but I have run across this problem. I have a schema for my records which includes a union type, so the relevant part of the schema is { "type":"record", "namespace":"blah", "name":"SimpleTraffic", "fields":[ {"name":"src_address","type":"string"}, {"name":"flag_s","type":["int","boolean"]} ] } This is because I am processing CSV records that look this, where 1 is true and 0 is false. 192.168.0.1,1 Into JSON that looks like this, using a ConvertRecord and an Update Record. {"src_address":"192.168.0.1","flag_s":true} Then I create a QueryRecord so I can filter out the cases where the flag is false. So I use this query. select * from flowfile where flag_s = true But I get this error org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '=' to arguments of type '<JAVATYPE(CLASS JAVA.LANG.OBJECT)> = <BOOLEAN>' Is this because the type is a Union type and the Calcite processor cannot work out which subtype it should be? Can I do anything to persuade the query to use an operator or a function on this field to make it usable? I have tried casting to Boolean or Char but no success. Or do I need to use two separate “before” and “after” schemas to eliminate the union? Regards Steve Hindmarch
