Search code examples
apache-nifi

Query Database Table with the values of csv in Apache NiFi

I have a csv file like this

col1,col2,col3
_______________
d1c1,d1c2,d1c3
d2c1,d2c2,d2c3
d3c1,d3c2,d3c3

And I have a database table table1 with below schema

col1,col4,col5
______________
d1c1,d4c2,d4c3
d2c1,d5c2,d5c3
d4c1,d6c2,d6c3

now, I want to query the database table1 with values of col1 of csv. Is there anyway to do this in nifi? I can extract the col1 from csv via QueryRecord processor but unable to route it to any database query processor.

Solution

  • Following flow worked for me.

    QueryRecord: for querying the csv file ("Select col1 from FLOWFILE"), don't use "\n" for record separator, use ", ".

    ExtractText for assigning variable(col.0) for values.

    ExecuteSQL for doing specific sql query "Select * from table1 where col1 in (${col.0})"

    enter image description here