Problem with trasform UUID to Strring in NIFI

@MWM,

How did you define the schema you are using to fetch the AVRO Data and how did you define the schema for writing AVRO to JSON? What column type is the UUID in your database?

@cotopaul thank you for your answer, can you tell how can i open this file in AvroReader-should I do it in Nifi?Sorry for my question but I am totally new in NIFI...

well there you go, UUID is bytes and not string. That is the reason why your data gets displayed like that, when transformed into JSON. You need to convert the bytes into CHAR when extracting the data from the view, if you need the data as string.

What you could try to do is add an UpdateRecord processor, in which you define an AVRO Reader and an AVRO Writer. In the AVRO Writer you set the schema you mentioned above, just that instead of bytes for UUID_1 you will write string. Next, in the processor, you add a new property with the same name as you affected column, starting with "/". For the value, you use NiFi's Expression Language to transform the bytes into string: toString(/YOUR_COLUMN_IN_BYTES_FORMAT, "UTF-8").

If that works, you can modify the flow and instead of writing the data with the AVRO Writer, you can modify the flow and set JSON directly and skip the step for ConvertAvroToJson.

Open the Controller Service for AvroRecordSetWriter, and in the field Schema Access Strategy, switch from Inherit Record Schema to Use 'Schema Text' Property. Once you select this option, a new property will be added, named Schema text. In the Value field for this property add the AVRO Schema.

@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]

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).

@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?

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

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

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

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