- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Useing a CSV file content in SQL condition
- Labels:
-
Apache NiFi
Created 11-29-2022 06:39 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I need to create a flow that update rows in my database based on csv file.
I have :
1- GetFile
status | client_id |
Active | 123 |
Inactive | 456 |
2- UpdateAttribute to update the Baobab_file (the schema is already in the AvroSchemaRegistry (collection)
3- SplitRecord to get each line
status | client_id |
Active | 123 |
4- ExecuteSQL : update table_client set current_status = status where id = client_id
How can I do this I tried ${status} and \status but no luck
Thank you
Created on 11-29-2022 09:27 AM - edited 11-29-2022 09:27 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,
I think after you split your csv you need to extract the values of both columns: status and client_id to attributes and then use in the ExecuteSQL processor, for that you need to :
1- convert the record to from CSV to JSON format using ConvertRecord Processor
2- use EvaluateJsonPath to extract both columns into defined attribute (dynamic properties). Make sure to set the Destination property to "flowfile-attribute".
After that you can reference those attribute in the SQL query as ${status} & ${client_id}, assuming thats how you called the attributes in step 2.
Another option if you dont want to use two processor , you can use ExtractText processor and provide regex to extract each value but you have to be careful how you define your regex for each value to make sure you are only pulling those values and nothing else.
Hope that helps.
If that answers your question please accept solution.
Thanks
Created on 11-29-2022 09:27 AM - edited 11-29-2022 09:27 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,
I think after you split your csv you need to extract the values of both columns: status and client_id to attributes and then use in the ExecuteSQL processor, for that you need to :
1- convert the record to from CSV to JSON format using ConvertRecord Processor
2- use EvaluateJsonPath to extract both columns into defined attribute (dynamic properties). Make sure to set the Destination property to "flowfile-attribute".
After that you can reference those attribute in the SQL query as ${status} & ${client_id}, assuming thats how you called the attributes in step 2.
Another option if you dont want to use two processor , you can use ExtractText processor and provide regex to extract each value but you have to be careful how you define your regex for each value to make sure you are only pulling those values and nothing else.
Hope that helps.
If that answers your question please accept solution.
Thanks
