Problem with trasform UUID to Strring in NIFI

[#1]

Hi, I have the following problem - I download data from the database using QueryDatabaseTable, I receive the data in the avro-binary format, so then I use ConvertAvroToJson - everything converts nicely except the UUID field, I am left with the following json:

{
"Name":"Mary",
"Surname":"Smith",
"Mail":"m.smith@google.com",
"UUID":{
"bytes":"_쪨Ը¬I³Sñ])º\u000E•"
},
"City":"New York"
}

I tried to extract this UUID as an attribute and replace it with the :toString() method but it doesn't work, does anyone have an idea how to convert it to a string?

•  

[#2]

@cotopaul are you sure this property should look like this :

because it didn't work, UUID_1 now look like this : "UUID_1" : ":toString(/UUID_1,\"UTF-8\")"

[#3]

@cotopaul and without this property UUID now is like that:

[118, 90, 123, 96, -61, 100, -20, 76, -89, -95, -64, -105, -43, -106, -111, 69]

[#4]

The value for the property certainly should be like toString(/YOUR_COLUMN,"UTF-8"), without those backslashes. Have a look in the documentation for the expression language and you shall see.

As for how the data will look like, that is nothing you could change. Give it a try with the correct syntax, not the one that you wrote.

Maybe the data in your bytes column is stored encrypted or in another format. You should further discuss this topic with the owner of the view and understand how the view is built and how the data is stored in the column. Without those information, it is hard to establish the perfect way to extract the data correctly.

Another solution would be to use a python script and execute it on the content of the AVRO File to decode the bytes column into string and send the output forward into processing. (This is not something easy to implement, especially if you do not have sudo on the nifi machines).

[#5]

@cotopaul I write correct syntax but it didn't work 😕 But in UpdateRecord processor Replacement Value strategy should be on Literal Valu or Record Path Value?

[#6]

@MWM, when using toString(), it should stay on Record Path Value.

[#7]

@cotopaul when i change strategy to RecordPathValue i have something like this:

"UUID_1" : "����}ZM�]����!p",

[#8]

@cotopaul it turned pout that this type in databyse is RAW not vairchar

[#9]

@MWM @cotopaul   If you get the record reader/writer using the schema(s) you want,  you do not have to do any magic to convert values, it should just work.     Only use, inferSchema long enough to get the structure when you have none.  Then copy/paste it and use it as @cotopaul has described in place of InferSchema.  You can also use Schema Registry.  Make the edits you need to satisfy reader (upstream), writer (downstream) as they are sometimes needing minor adjustments like in this case.