- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Help with UpdateRecord or QueryRecord
- Labels:
-
Apache NiFi
Created 06-14-2023 02:17 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi to all,
hope someone can point me on the right direction.
I have something like that:
[ {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490010",
"shipment_number" : ""
}, {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490011",
"shipment_number" : ""
}, {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490012",
"shipment_number" : ""
} ]
I need to update only a specific record with a met condition.
Speaking on a SQL language it should be something like that:
UPDATE FLOWFILE SET shipment_number = '${Shipment_Number_Attribute} ' WHERE barcode = '${Barcode_Attribute}'
Unfortunately this statement in QueryRecord doesn't work, maybe UPDATE not supported?
Moreover is there any way to achieve the same with UpdateRecord?
Many thanks for any help.
Appreciate.
Created 06-16-2023 11:14 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You either have to select each column one by one to have the same column name you are trying to update in the case statement, or select * but create different column for the case statement and then use jolt transformation to write the value back to the original column.
Created 06-16-2023 11:26 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You have to create a separate case statement for each column you are trying to update similar to what is done for the shipment number.
Created 06-14-2023 03:14 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Where is the barcode attribute coming from?
Created 06-15-2023 01:29 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Barcode is an attribute coming from each fragment...different on any fragment.
Created on 06-15-2023 09:36 AM - edited 06-15-2023 09:37 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Ray82 Yes, you can achieve this with UpdateRecord. You will need to provide record reader/writer with schema of your upstream and downstream. Then in UpdateRecord you explicitly add properties (+) for each record value you want to update versus using a SQL statement like QueryRecord.
Here are some useful community posts on this topic:
Created 06-15-2023 01:31 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sure I Kwon this....the question is how to add a where clause in Updare Record or QueryRecord .
In QueryRecord I ve tried an Update statement with no luck.
Will giving me always an error.
Created on 06-15-2023 02:27 PM - edited 06-15-2023 02:28 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I dont think what you are trying to do can be achieved with UpdateRecord, the reason being that the UpdateRecord you have to define a record path where the specific record\attribute have to be static and cannot take any variable like a flowfile attribute. For example lets say you want to update record with barcode "C337287V28490011" , then record path property would be defined as the following which would work :
/*[/barcode='C337287V28490012']/shipment_number
However, you cannot define the record path like this:
/*[/barcode='${Barcode_Attribute}']/shipment_number
The only way I can think of where you can accomplish this in one processor is to use the JoltTransformationJSON processor. Jolt is a transformation language for json and its very powerful. In this processor you set the "Jolt Specification" property with the following spec:
[
{
"operation": "shift",
"spec": {
"*": {
"barcode": {
"${Barcode_Attribute}": {
"$": "[&3].barcode",
"@(2,date)": "[&3].date",
"#${Shipment_Number_Attribute}": "[&3].shipment_number",
"@(2,reference)": "[&3].reference"
},
"*": {
"$": "[&3].barcode",
"@(2,date)": "[&3].date",
"@(2,shipment_number)": "[&3].shipment_number",
"@(2,reference)": "[&3].reference"
}
}
}
}
}
]
Notice how you can use Expression Language in the Jolt Spec where you can specify flowfile attributes as well. For more information on Jolt Spec you can refer to the following:
https://jolt-demo.appspot.com/#inception
https://intercom.help/godigibee/en/articles/4044359-transformer-getting-to-know-jolt
If that helps please accept solution,
Thanks
Created 06-15-2023 03:09 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Update:
Actually, I just found out that you can achieve what you need using QueryRecord processor by defining the query Relation Property with the following query:
select reference,barcode,"date",
case when barcode = '${Barcode_Attribute}' then '${Shipment_Number_Attribute}'
else shipment_number end as shipment_number
from flowfile
The Json Jolt transformation above still works but its more complicated and probably less efficient.
Hope that helps
Created 06-16-2023 06:44 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Well, i'm not sure this query is exactly what i'm searching for.
Speaking in a human way this is what i need to do:
Search the element with barcode=${barcode_attribute} and if found update corresponding "shipment_number" value with ${shipment_number_attribute}, otherwise leave it as it is.
So this:
[ {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490010",
"shipment_number" : ""
}, {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490011",
"shipment_number" : ""
}, {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490012",
"shipment_number" : ""
} ]
would becomes as below:
[ {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490010",
"shipment_number" : "0000023/SH"
}, {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490011",
"shipment_number" : ""
}, {
"reference" : "PB",
"date" : "20230613",
"barcode" : "C337287V28490012",
"shipment_number" : ""
} ]
Trying your query seems that does not update shipment_number field.
Hope this can explain better the need.
Many Thanks
Created 06-16-2023 07:05 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Created 06-16-2023 07:19 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Ray82
Its hard to predict how this is going to work without understanding the full data flow you have, so I had to come up with simple flow to test the query which worked for me as expected. Please see the my flow below and each processor configuration to get the expected result:
Data Flow:
1) Generate Input Josn: GenerateFlowFile processor to produce the Input json as you provided and provide the query attributes.
2) QueryRecord: Run the Query provided above to generate expected result:
Notice that you need to connect the QueryRelationship "QueryRel" to downstream processor to get the expected output and not the original relationship:
After running the DataFlow above and getting the output in the QueryRel relationship queue , the output looks like as follows:
Notice to get the out put as an array the JsonRecordWrite in the QueryRecord RecordWriter is configured as the following:
Like I said in the beginning everything depends at the end on your overall dataflow and how you are getting the data , attributes and in what format. The details above hopefully can guide you in case you are missing something or figure out what is making this not working in your case. Also be aware that Im using version 1.20.0 so also depending what version you are using the behavior can be different.
Hope that helps.