Mark,
I am using a Docker image of apache/nifi:latest which I pulled about 2 months
ago. Image ID 0070c5c5d9fd. From the UI it says tag 1.15.3-RC1 and commit
753c311. I am guessing it must be this: GitHub - apache/nifi at
nifi-1.15.3-RC1<https://github.com/apache/nifi/tree/nifi-1.15.3-RC1>.
Just to let you know, I did come to a satisfactory solution that does a
convert, normalise, filter and coerce to a new schema using only two processors
and two services (plus a schema registry). I first use a query record which if
I work with the integer values means I have “select * from flowfile where
flag_s = 1”. Doing the filtering early feels like a better idea, as I am not
processing records I later discard. By using a CSV reader and a JSON writer I
get the conversion from CSV to JSON for free. And by working with the integers
I have no need for the union.
Then I use a JOLT transform to coerce the JSON into the form of the new schema.
At the same time I can switch the int values to booleans by using a
modify-overwrite. Here is the step, operating on the flag in its new position
(“Network.flags.s”), which uses a trick of having the integer value pick the
boolean out of a fixed array.
{
"operation":"modify-overwrite-beta",
"spec": {
"*": {
"Network": {
"flags": {
"*": "=elementAt(@(0),false,true)"
}
}
}
}
}
Regards,
Steve Hindmarch
From: Mark Payne <[email protected]>
Sent: 18 March 2022 14:44
To: [email protected]
Subject: Re: QueryRecord with Union type
Steve,
What version of nifi are you running? I’d tried that on the latest “main”
branch and it worked as expected.
Thanks
-Mark
On Mar 18, 2022, at 5:49 AM,
[email protected]<mailto:[email protected]> wrote:
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 <[email protected]<mailto:[email protected]>>
Sent: 17 March 2022 14:17
To: users <[email protected]<mailto:[email protected]>>
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,
[email protected]<mailto:[email protected]> 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
