Useing a CSV file content in SQL condition

Question

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

SAMSAL · Accepted Answer

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

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.