mrcsparker / nifi-sqllookup-services-bundle Goto Github PK

View Code? Open in Web Editor NEW

19.0 3.0 11.0 615 KB

NIFI controllers for SQL record and attributes lookups with built-in caching

Home Page: https://mrcsparker.github.io/nifi-sqllookup-services-bundle

License: Apache License 2.0

Java 100.00%

nifi nifi-processors nifi-processor sql jdbc java

nifi-sqllookup-services-bundle's Introduction

Apache NiFi SQL Lookup Service

Apache NiFi SQL Lookup Service

About

NiFI SQL Lookup Service is a SQL-based lookup service for Apache NiFi. It allows you to enrich your flowfiles with any jdbc-compliant data store, including:

PostgreSQL
Oracle
MySQL
MS SQL Server
SQLite
... and many more

It includes both LookupRecord and LookupAttribute controllers.

These controllers were designed to be flexible and fast.

Simple Setup

Download Apache NiFi
Compile and install nifi-sqllookup-bundle

> cd nifi-sqllookup-bundle
> mvn package
> cp ./nifi-sqllookup-services-nar/target/nifi-sqllookup-services-nar-1.15.0.nar /NIFI_INSTALL/lib/
> cp ./nifi-sqllookup-services-api-nar/target/nifi-sqllookup-services-api-nar-1.15.0.nar /NIFI_INSTALL/lib/

Start NiFi

SQL Query Support

This service supports multiple query types:

Named Parameters: SELECT name FROM foo WHERE value = :value
SQL IN queries: SELECT name FROM foo WHERE value IN(:values)
Multiple lookup values: SELECT name FROM foo WHERE value IN(:values) AND sequence = :sequence AND catalog = :catalog

Caching

The goal of this service is to return values quickly. It has a built-in cache so that your database doesn't get overwhelmed.

This is configurable in the controller settings.

Supported caches

These caches are all built-in to this service. Select your preferable cache in the controller settings.

Caffeine - default cache.
Cache2k
Guava

You can also select the number of items that you want to cache. The caches all keep the most accessed items available by default.

If you don't know which to choose, just go with the default.

Latest release

https://github.com/mrcsparker/nifi-sqllookup-services-bundle/releases/latest

Articles on using NiFi lookup services

nifi-sqllookup-services-bundle's People

Contributors

Stargazers

Watchers

Forkers

deem0n drdebian phillipjohnson shanelenagh luakt motazalratta d4rkstar grptx fliot aperepel harryat

nifi-sqllookup-services-bundle's Issues

Last few commits do not build

Hi, your last commits do all not build. See also:
https://travis-ci.org/mrcsparker/nifi-sqllookup-services-bundle

Using the 2 Month old "1.6.0-4" (the lastest one which can be build)

SQLRecordLookupService and SQLLookupService show up in the "LookupRecord" Processor, although one of them should be available in the "LookupAttribute" Processor

SQL Lookup Service doesn't work with DBCPConnectionPoolLookup

DBCPConnectionPoolLookup is not able to select a DBCPConnectionPool based on the database.name attribute from the flowfile.

SQLLookup Service must provide ability to plugin a Cache Provider as well as "no-cache" option

we can use other-lookup service for this.
optionally to ignore cache, at the loss of performance

How to use this service in Lookup attribute?

Hi!
I would like to use this service in LookupAttribute processor to find an Agent ID based on Agent Name and Agent Version that I get from FlowFile.

I've tried using this service in LookupAttribute but I don't know how to provide those two values to the service through the processor.

In your example you say that this service supports named parameters, for example:

SELECT name FROM foo WHERE sequence = :sequence AND catalog = :catalog

So I've tried putting it in the service with following query:

SELECT agent_id FROM agent WHERE agent_name = :agent_name AND agent_version = :agent_version

I've tried many ways of trying to provide values for these two parameters, but couldn't get it to work with LookupAttribute.
Then I've tried to simplify the SQL query, just to see if I can get it to work and used:

SELECT agent_id FROM agent WHERE agent_name = :agent_name

In LookupAttribute I've defined the property agent _id and given it various values such as "agent_name", "${agent_name}", "agent_name=${agent_name}", "agent_name,${agent_name}", and more, but it didn't work, every time I got following error when I tried running the LookupAttribute processor:

LookupAttribute[id=d9aa39f8-4bc7-1fe5-a4e4-9130dd75dfbf] Processing halted: yielding [1 sec]: org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'agent': No value registered for key 'agent_name'

I've tried looking through source code to figure out how to fix this, but I have no idea.
Could you help me?

SQLLookUp controllers do not take context attributes - such as flowFile attributes passed

I needed to make SQL query used by the processor a template. like select source, ${id_column} from ${table} where ${id_column} = : , based on in coming flowFile. This was not possible, as these controllers did not override second form of lookup : lookup(coordinates,context) - call
Made do with my own fork.
I feel this is a useful feature. Great work! I really appreciate its help in my project