question Re: Problem with trasform UUID to Strring in NIFI in Support Questions

Problem with trasform UUID to Strring in NIFI

MWM — Mon, 16 Oct 2023 06:47:05 GMT

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?

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Mon, 16 Oct 2023 13:41:15 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 10:29:18 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Tue, 17 Oct 2023 11:21:37 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 11:45:01 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Tue, 17 Oct 2023 11:57:38 GMT

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.

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 12:15:50 GMT

@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

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Tue, 17 Oct 2023 12:41:08 GMT

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.

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 13:19:05 GMT

@cotopaul how can I set this schem in AvroWriter?

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Tue, 17 Oct 2023 13:23:47 GMT

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.

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 13:36:24 GMT

@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\")"

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 13:43:31 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Tue, 17 Oct 2023 14:07:32 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 14:22:25 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

cotopaul — Tue, 17 Oct 2023 14:23:29 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

MWM — Tue, 17 Oct 2023 14:30:35 GMT

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

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

Re: Problem with trasform UUID to Strring in NIFI

MWM — Wed, 18 Oct 2023 07:58:51 GMT

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

Re: Problem with trasform UUID to Strring in NIFI

steven-matison — Mon, 23 Oct 2023 18:44:15 GMT

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