Support Questions
Find answers, ask questions, and share your expertise

NiFI 1.3 - How to do a sql record for one field in a Record?

Super Guru

I want to lookup a value in an existing Hive table (or anything JDBC).

So I have some_id in my record, I want to replace this in output with some_value obtained by select some_value from some_table where some_id = /some_id


Super Collaborator

Hi @Timothy Spann,

You can perform this by two methods.

1. Using the Distributed cache Service

2. Database Lookup using the ExecuteSQL Processor

DistributedMapCache Service(Server and Client services) can be used to lookup the values for the input records,

have to have two flows :

To update the cache data (Key(some_id) and Value (Some_Value)) using PutDistributedMapCache processor which update the some_id,some_value pairs from hive to Cache.

The existing flow can lookup the keys (Some_Id) Using the FetchDistributedMapCache processor

The limitation of this approach is that, if you have too many (tens of millions) of values to look up the cache get bulky and started causing the problems.

On the other side this approach is pretty quick than every time go to database and fetch the data for each record.

Using Execute-SQL Processor :

update the some_id into flow file attribute and pass to ExecuteSQL processor with query

Select some_value From db.tbl where some_id=${some_id}

the Down side of this would be overtime this hits the database may get really slow considering the amount of hits you are going to perform from Database.

Hope this Helps !!

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.