Apache NiFi QueryRecord SELECT Static Alias Column

Question

I want to import a file that has the following Avro schema assigned using Apache NiFi:

{
   "type" : "record",
   "namespace" : "SomeSpaceName",
   "name" : "SampleFile",
   "fields" : [
     { "name" : "PersonName" , "type" : "string" },
     { "name" : "PersonType" , "type" : "string" }
   ]
}

When I use the QueryRecord processor I want to have a static field in the output file so I can import it into MongoDB. The query is:

SELECT LOWER(PersonName) as _id,
'Male' as gender
FROM flowfile

The problem is Calcite will not add the new static field properly. It adds the name successfully but the new gender field only contains the first letter of the word:

| _id  | gender |
|------|--------|
| Eric | M      |
| Bill | M      |
| Chad | M      |

Answer 1

Make sure QueryRecord processor writer avro schema have _id,gender fields included in it.

Writer Avro Schema:

{
   "type" : "record",
   "namespace" : "SomeSpaceName",
   "name" : "SampleFile",
   "fields" : [
     { "name" : "_id" , "type" : ["null","string"] },
     { "name" : "gender" , "type" : ["null","string"] }
   ]
}