1

I am try to impletement following scenario. I take two values from database table and map them to flowfile content and if there a match, then add a new column to flowfile with database content.

In my database, I have :

locationID|storeID|siteName
___________________________
121       |  3232 | Colombo1
342       |  34343| Colombo2

In my flowfile I have only locationID and storeID

locationID,storeID
121,3232
342,34343

I want to match them with database records and add a new column to flowfile as siteName with respective data.

So final result should be:

locationID,storeID,siteName
121,3232,Colombo1
342,34343,Colombo2

For doing this I have tried QueryDatabaseTableRecord, but dont understand how to do this mapping thing with this.

Or do I have to use an external script for doing this?

EDIT : I use TimeScaleDB(Postgresql) database

Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94
  • 1
    Which database do you use? – BastienB Nov 18 '19 at 14:30
  • @BastienB sorry about my mistake. I have updated the question, – Sachith Muhandiram Nov 18 '19 at 14:37
  • you can use this new service in NiFi 1.10.0: [SimpleDatabaseLookupService](https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-lookup-services-nar/1.10.0/org.apache.nifi.lookup.db.SimpleDatabaseLookupService/index.html) – Behrouz Seyedi Nov 18 '19 at 14:44
  • 1
    I think you can use ```QueryDatabaseTable```. For that, you have to setup a connection in your dataFlow configuration. In the property "Additional WHERE clause", you can use your ```locationID``` and ```storeID``` like ```locationID LIKE {locationID} AND storeID LIKE {storeID}``` – BastienB Nov 18 '19 at 14:48
  • @BastienB Could you please provide a sample? I have tried, but output comes in binary format. – Sachith Muhandiram Nov 19 '19 at 04:32
  • @BehrouzSeyedi I use `nifi-1.9.2` – Sachith Muhandiram Nov 19 '19 at 04:44
  • Take a look at this [ticket](https://issues.apache.org/jira/browse/NIFI-2997) for read your binary result – BastienB Nov 19 '19 at 08:06
  • @BastienB so I should use `EncodeAttribute` or `EncodeContent` to convert values? – Sachith Muhandiram Nov 19 '19 at 08:28
  • It depends if your outpout is in an attribute or not. I think, it is in content. – BastienB Nov 19 '19 at 09:31
  • @BastienB Sorry, I use `nifi-1.9.2`, it doesnt any of these two processors. – Sachith Muhandiram Nov 19 '19 at 09:35
  • Ok so you can try to use ```ExecuteScript``` and translate the binary to UTF-8 or other encoding with a script. You can read [this documentation](https://community.cloudera.com/t5/Community-Articles/ExecuteScript-Cookbook-part-1/ta-p/248922) for understand the syntax to communicate with a script to NiFi. – BastienB Nov 19 '19 at 10:14

1 Answers1

2

You should extract LocationID and storeID and add them to flowFile attributes, then use ExecuteGroovyScript processor and add this script to Script Body:

flowFile = session.get()
if(!flowFile) return
def result = SQL.db.firstRow("SELECT siteName FROM TABLE WHERE locationID=$locationId AND storeID=$storeId")
if(result)
    flowFile.siteName = result[0]
else
    flowFile.siteName = 'NULL'
REL_SUCCESS << flowFile

ExecuteGroovyScript Configuration

This script gets siteName from database and adds it to flowFile attribute. You can use another processor to convert this attributes to CSV again.

Behrouz Seyedi
  • 306
  • 1
  • 18