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]

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

[#3]

Hi @cotopaul 

Basically, my flow looks like this: first I use QueryDataTable to download the view from the database, then I have the ConvertAvroToJson processor but I don't have anything filled in the AvroSchema field because everything except the UUID field is converted, in the database the UUID appears as a String

[#4]

@MWM, appears as a STRING is not the same as IT IS A STRING 🙂
Go in your database and check what data type your column has.

Besides that, download the file generated by QueryDatabaseTable and open it with an AVRO Reader and see what AVRO Schema has been generated and what avro type you have assigned for that specific column.

I am pretty certain that you are not working with strings here but you will get your confirmation once you check the above mentioned.

[#5]

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

[#6]

To see the content of the file, you can use NiFi. However, to see the generated schema, you will need an IDE like IntelliJ with the AVRO/Parquet plugin or you can search for an online avro reader and upload your data there.

[#7]

@cotopaul I used Intellij and have something like that:

{
"type" : "record",
"name" : "USERDATA",
"namespace" : "any.data",
"fields" : [ {
"name" : "Name",
"type" : [ "null", "string" ]
}, {
"name" : "Surname",
"type" : [ "null", "string" ]
}, {
"name" : "Mail",
"type" : [ "null", "string" ]
}, {
"name" : "UUID_1",
"type" : [ "null", "bytes" ]
}, {
"name" : "City",
"type" : [ "null", "string" ]
} ]
}

but the column in the data is apparently of type Stryng - it is not my database, I only have access to the views and download them

[#8]

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.

[#9]

@cotopaul how can I set this schem in AvroWriter?

[#10]

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.