Hi John,
You need to add dynamic properties to DBCPConnectionPoolLookup controller
service. The name of the property corresponds to database.name attribute,
the value is the DBCPConnectionPool that can be looked up (by the name of
the property).
So the attribute database.name ties back to the name of the dynamic
property, not to the name of the DBCPConnectionPool component.
Please find attached my simple example. It contains only one
DBCPConnectionPool that can be looked up, but a second one can be added in
the same way.
Regards,
Peter Turcsanyi
On Fri, Oct 4, 2019 at 10:13 PM John Fak <johnfa...@gmail.com> wrote:
> Does someone have a basic example of using this ?
> I really dont understand it. And it seems useful.
>
> Does the attribute database.name tie back to the "Name" of the
> DBCPConnectionPool ?
>
> How do you pass that dynamically ? Via some updateattibute processor ?
>
> Be great to see a very basic flow on connecting to two or more sources
> dynamically.
>
>
>
>
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<template encoding-version="1.2">
<description></description>
<groupId>6a4bf1bf-016b-1000-1fda-2e79e336695e</groupId>
<name>DBCP</name>
<snippet>
<processGroups>
<id>47e5dacd-f1e4-30b4-0000-000000000000</id>
<parentGroupId>bd34a285-0243-32c3-0000-000000000000</parentGroupId>
<position>
<x>0.0</x>
<y>0.0</y>
</position>
<comments></comments>
<contents>
<connections>
<id>be2b62c1-c19c-3dec-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>47e5dacd-f1e4-30b4-0000-000000000000</groupId>
<id>5f285237-9d31-3f8b-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>47e5dacd-f1e4-30b4-0000-000000000000</groupId>
<id>32dcf1a4-9cea-3cd0-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>e14cd5b8-85ba-3fe2-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>47e5dacd-f1e4-30b4-0000-000000000000</groupId>
<id>c7e7e246-88f5-3b4d-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>47e5dacd-f1e4-30b4-0000-000000000000</groupId>
<id>5f285237-9d31-3f8b-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<controllerServices>
<id>042a173c-50cd-3376-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<bundle>
<artifact>nifi-record-serialization-services-nar</artifact>
<group>org.apache.nifi</group>
<version>1.10.0-SNAPSHOT</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>Schema Write Strategy</key>
<value>
<name>Schema Write Strategy</name>
</value>
</entry>
<entry>
<key>schema-cache</key>
<value>
<identifiesControllerService>org.apache.nifi.serialization.RecordSchemaCacheService</identifiesControllerService>
<name>schema-cache</name>
</value>
</entry>
<entry>
<key>schema-access-strategy</key>
<value>
<name>schema-access-strategy</name>
</value>
</entry>
<entry>
<key>schema-registry</key>
<value>
<identifiesControllerService>org.apache.nifi.schemaregistry.services.SchemaRegistry</identifiesControllerService>
<name>schema-registry</name>
</value>
</entry>
<entry>
<key>schema-name</key>
<value>
<name>schema-name</name>
</value>
</entry>
<entry>
<key>schema-version</key>
<value>
<name>schema-version</name>
</value>
</entry>
<entry>
<key>schema-branch</key>
<value>
<name>schema-branch</name>
</value>
</entry>
<entry>
<key>schema-text</key>
<value>
<name>schema-text</name>
</value>
</entry>
<entry>
<key>Date Format</key>
<value>
<name>Date Format</name>
</value>
</entry>
<entry>
<key>Time Format</key>
<value>
<name>Time Format</name>
</value>
</entry>
<entry>
<key>Timestamp Format</key>
<value>
<name>Timestamp Format</name>
</value>
</entry>
<entry>
<key>Pretty Print JSON</key>
<value>
<name>Pretty Print JSON</name>
</value>
</entry>
<entry>
<key>suppress-nulls</key>
<value>
<name>suppress-nulls</name>
</value>
</entry>
<entry>
<key>output-grouping</key>
<value>
<name>output-grouping</name>
</value>
</entry>
<entry>
<key>compression-format</key>
<value>
<name>compression-format</name>
</value>
</entry>
<entry>
<key>compression-level</key>
<value>
<name>compression-level</name>
</value>
</entry>
</descriptors>
<name>JsonRecordSetWriter</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>Schema Write Strategy</key>
</entry>
<entry>
<key>schema-cache</key>
</entry>
<entry>
<key>schema-access-strategy</key>
</entry>
<entry>
<key>schema-registry</key>
</entry>
<entry>
<key>schema-name</key>
</entry>
<entry>
<key>schema-version</key>
</entry>
<entry>
<key>schema-branch</key>
</entry>
<entry>
<key>schema-text</key>
</entry>
<entry>
<key>Date Format</key>
</entry>
<entry>
<key>Time Format</key>
</entry>
<entry>
<key>Timestamp Format</key>
</entry>
<entry>
<key>Pretty Print JSON</key>
</entry>
<entry>
<key>suppress-nulls</key>
</entry>
<entry>
<key>output-grouping</key>
</entry>
<entry>
<key>compression-format</key>
</entry>
<entry>
<key>compression-level</key>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.json.JsonRecordSetWriter</type>
</controllerServices>
<controllerServices>
<id>55ccf8ff-2a85-3c1d-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<bundle>
<artifact>nifi-dbcp-service-nar</artifact>
<group>org.apache.nifi</group>
<version>1.10.0-SNAPSHOT</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>Database Connection URL</key>
<value>
<name>Database Connection URL</name>
</value>
</entry>
<entry>
<key>Database Driver Class Name</key>
<value>
<name>Database Driver Class Name</name>
</value>
</entry>
<entry>
<key>database-driver-locations</key>
<value>
<name>database-driver-locations</name>
</value>
</entry>
<entry>
<key>kerberos-credentials-service</key>
<value>
<identifiesControllerService>org.apache.nifi.kerberos.KerberosCredentialsService</identifiesControllerService>
<name>kerberos-credentials-service</name>
</value>
</entry>
<entry>
<key>Database User</key>
<value>
<name>Database User</name>
</value>
</entry>
<entry>
<key>Password</key>
<value>
<name>Password</name>
</value>
</entry>
<entry>
<key>Max Wait Time</key>
<value>
<name>Max Wait Time</name>
</value>
</entry>
<entry>
<key>Max Total Connections</key>
<value>
<name>Max Total Connections</name>
</value>
</entry>
<entry>
<key>Validation-query</key>
<value>
<name>Validation-query</name>
</value>
</entry>
<entry>
<key>dbcp-min-idle-conns</key>
<value>
<name>dbcp-min-idle-conns</name>
</value>
</entry>
<entry>
<key>dbcp-max-idle-conns</key>
<value>
<name>dbcp-max-idle-conns</name>
</value>
</entry>
<entry>
<key>dbcp-max-conn-lifetime</key>
<value>
<name>dbcp-max-conn-lifetime</name>
</value>
</entry>
<entry>
<key>dbcp-time-between-eviction-runs</key>
<value>
<name>dbcp-time-between-eviction-runs</name>
</value>
</entry>
<entry>
<key>dbcp-min-evictable-idle-time</key>
<value>
<name>dbcp-min-evictable-idle-time</name>
</value>
</entry>
<entry>
<key>dbcp-soft-min-evictable-idle-time</key>
<value>
<name>dbcp-soft-min-evictable-idle-time</name>
</value>
</entry>
</descriptors>
<name>HSQLDBConnectionPool</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>Database Connection URL</key>
<value>jdbc:hsqldb:hsql://localhost/nifi</value>
</entry>
<entry>
<key>Database Driver Class Name</key>
<value>org.hsqldb.jdbc.JDBCDriver</value>
</entry>
<entry>
<key>database-driver-locations</key>
<value>/opt/hsqldb-2.5.0/hsqldb/lib/hsqldb.jar</value>
</entry>
<entry>
<key>kerberos-credentials-service</key>
</entry>
<entry>
<key>Database User</key>
<value>SA</value>
</entry>
<entry>
<key>Password</key>
</entry>
<entry>
<key>Max Wait Time</key>
<value>500 millis</value>
</entry>
<entry>
<key>Max Total Connections</key>
<value>8</value>
</entry>
<entry>
<key>Validation-query</key>
</entry>
<entry>
<key>dbcp-min-idle-conns</key>
<value>0</value>
</entry>
<entry>
<key>dbcp-max-idle-conns</key>
<value>8</value>
</entry>
<entry>
<key>dbcp-max-conn-lifetime</key>
<value>-1</value>
</entry>
<entry>
<key>dbcp-time-between-eviction-runs</key>
<value>-1</value>
</entry>
<entry>
<key>dbcp-min-evictable-idle-time</key>
<value>30 mins</value>
</entry>
<entry>
<key>dbcp-soft-min-evictable-idle-time</key>
<value>-1</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.dbcp.DBCPConnectionPool</type>
</controllerServices>
<controllerServices>
<id>c68505fa-f7fb-3f68-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<bundle>
<artifact>nifi-dbcp-service-nar</artifact>
<group>org.apache.nifi</group>
<version>1.10.0-SNAPSHOT</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>hsqldb</key>
<value>
<identifiesControllerService>org.apache.nifi.dbcp.DBCPService</identifiesControllerService>
<name>hsqldb</name>
</value>
</entry>
</descriptors>
<name>ConnectionPoolLookup</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>hsqldb</key>
<value>55ccf8ff-2a85-3c1d-0000-000000000000</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.dbcp.DBCPConnectionPoolLookup</type>
</controllerServices>
<processors>
<id>32dcf1a4-9cea-3cd0-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<position>
<x>-488.0</x>
<y>384.0</y>
</position>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.10.0-SNAPSHOT</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>File Size</key>
<value>
<name>File Size</name>
</value>
</entry>
<entry>
<key>Batch Size</key>
<value>
<name>Batch Size</name>
</value>
</entry>
<entry>
<key>Data Format</key>
<value>
<name>Data Format</name>
</value>
</entry>
<entry>
<key>Unique FlowFiles</key>
<value>
<name>Unique FlowFiles</name>
</value>
</entry>
<entry>
<key>generate-ff-custom-text</key>
<value>
<name>generate-ff-custom-text</name>
</value>
</entry>
<entry>
<key>character-set</key>
<value>
<name>character-set</name>
</value>
</entry>
<entry>
<key>database.name</key>
<value>
<name>database.name</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>File Size</key>
<value>0B</value>
</entry>
<entry>
<key>Batch Size</key>
<value>1</value>
</entry>
<entry>
<key>Data Format</key>
<value>Text</value>
</entry>
<entry>
<key>Unique FlowFiles</key>
<value>false</value>
</entry>
<entry>
<key>generate-ff-custom-text</key>
</entry>
<entry>
<key>character-set</key>
<value>UTF-8</value>
</entry>
<entry>
<key>database.name</key>
<value>hsqldb</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>5 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>GenerateFlowFile</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.GenerateFlowFile</type>
</processors>
<processors>
<id>5f285237-9d31-3f8b-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<position>
<x>272.0</x>
<y>488.0</y>
</position>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.10.0-SNAPSHOT</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Database Connection Pooling Service</key>
<value>
<identifiesControllerService>org.apache.nifi.dbcp.DBCPService</identifiesControllerService>
<name>Database Connection Pooling Service</name>
</value>
</entry>
<entry>
<key>sql-pre-query</key>
<value>
<name>sql-pre-query</name>
</value>
</entry>
<entry>
<key>SQL select query</key>
<value>
<name>SQL select query</name>
</value>
</entry>
<entry>
<key>sql-post-query</key>
<value>
<name>sql-post-query</name>
</value>
</entry>
<entry>
<key>Max Wait Time</key>
<value>
<name>Max Wait Time</name>
</value>
</entry>
<entry>
<key>esqlrecord-record-writer</key>
<value>
<identifiesControllerService>org.apache.nifi.serialization.RecordSetWriterFactory</identifiesControllerService>
<name>esqlrecord-record-writer</name>
</value>
</entry>
<entry>
<key>esqlrecord-normalize</key>
<value>
<name>esqlrecord-normalize</name>
</value>
</entry>
<entry>
<key>dbf-user-logical-types</key>
<value>
<name>dbf-user-logical-types</name>
</value>
</entry>
<entry>
<key>esql-max-rows</key>
<value>
<name>esql-max-rows</name>
</value>
</entry>
<entry>
<key>esql-output-batch-size</key>
<value>
<name>esql-output-batch-size</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Database Connection Pooling Service</key>
<value>c68505fa-f7fb-3f68-0000-000000000000</value>
</entry>
<entry>
<key>sql-pre-query</key>
</entry>
<entry>
<key>SQL select query</key>
<value>select * from users</value>
</entry>
<entry>
<key>sql-post-query</key>
</entry>
<entry>
<key>Max Wait Time</key>
<value>0 seconds</value>
</entry>
<entry>
<key>esqlrecord-record-writer</key>
<value>042a173c-50cd-3376-0000-000000000000</value>
</entry>
<entry>
<key>esqlrecord-normalize</key>
<value>false</value>
</entry>
<entry>
<key>dbf-user-logical-types</key>
<value>false</value>
</entry>
<entry>
<key>esql-max-rows</key>
<value>0</value>
</entry>
<entry>
<key>esql-output-batch-size</key>
<value>0</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>ExecuteSQLRecord</name>
<relationships>
<autoTerminate>true</autoTerminate>
<name>failure</name>
</relationships>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.ExecuteSQLRecord</type>
</processors>
<processors>
<id>c7e7e246-88f5-3b4d-0000-000000000000</id>
<parentGroupId>47e5dacd-f1e4-30b4-0000-000000000000</parentGroupId>
<position>
<x>1088.0</x>
<y>784.0</y>
</position>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.10.0-SNAPSHOT</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Log Level</key>
<value>
<name>Log Level</name>
</value>
</entry>
<entry>
<key>Log Payload</key>
<value>
<name>Log Payload</name>
</value>
</entry>
<entry>
<key>Attributes to Log</key>
<value>
<name>Attributes to Log</name>
</value>
</entry>
<entry>
<key>attributes-to-log-regex</key>
<value>
<name>attributes-to-log-regex</name>
</value>
</entry>
<entry>
<key>Attributes to Ignore</key>
<value>
<name>Attributes to Ignore</name>
</value>
</entry>
<entry>
<key>attributes-to-ignore-regex</key>
<value>
<name>attributes-to-ignore-regex</name>
</value>
</entry>
<entry>
<key>Log prefix</key>
<value>
<name>Log prefix</name>
</value>
</entry>
<entry>
<key>character-set</key>
<value>
<name>character-set</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Log Level</key>
<value>info</value>
</entry>
<entry>
<key>Log Payload</key>
<value>true</value>
</entry>
<entry>
<key>Attributes to Log</key>
</entry>
<entry>
<key>attributes-to-log-regex</key>
<value>.*</value>
</entry>
<entry>
<key>Attributes to Ignore</key>
</entry>
<entry>
<key>attributes-to-ignore-regex</key>
</entry>
<entry>
<key>Log prefix</key>
</entry>
<entry>
<key>character-set</key>
<value>UTF-8</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>LogAttribute</name>
<relationships>
<autoTerminate>true</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.LogAttribute</type>
</processors>
</contents>
<name>DBCP</name>
<variables/>
</processGroups>
</snippet>
<timestamp>07/12/2019 16:12:18 CEST</timestamp>
</template>
