如何在“ExecuteGroovyScript”处理器中使用“DBCPConnectionPoolLookup”？

kjthegm6  于 2023-06-28  发布在  其他
关注(0)|答案(2)|浏览(91)

我想在“ExecuteGroovyScript”处理器中使用“DBCPConnectionPoolLookup”控制器服务。我设置了'database.name'，但是我得到了这个错误：

这是'ExecuteGroovyScript'配置：


我找到了一个有same problem的人，并解决了它here，但他解决问题的方法并不完整。
我想我应该使用CTL的回答部分所描述的this question，但我得到了这个错误：

这是我的配置：

请让我知道是什么问题？

update：我从代码中删除了try/catch，得到了以下错误：

使用SQL.db：

使用CTL.sql：

完整日志：

2019-10-17 22:27:12,685 ERROR [Timer-Driven Process Thread-10] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:474)
    at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
    at sun.reflect.GeneratedMethodAccessor1514.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
    at com.sun.proxy.$Proxy100.getConnection(Unknown Source)
    at org.apache.nifi.dbcp.DBCPService$getConnection.call(Unknown Source)
    at Scriptffffffffa8c699bc.run(Scriptffffffffa8c699bc.groovy:8)
    at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:449)
    at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
    at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:470)
    ... 21 common frames omitted
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
    at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:451)
    at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:365)
    at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
    ... 23 common frames omitted
2019-10-17 22:27:13,194 ERROR [Timer-Driven Process Thread-10] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:474)
    at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
    at sun.reflect.GeneratedMethodAccessor1514.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
    at com.sun.proxy.$Proxy100.getConnection(Unknown Source)
    at org.apache.nifi.dbcp.DBCPService$getConnection.call(Unknown Source)
    at Scriptffffffffa8c699bc.run(Scriptffffffffa8c699bc.groovy:8)
    at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:449)
    at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
    at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:470)
    ... 21 common frames omitted
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
    at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:451)
    at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:365)
    at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
    ... 23 common frames omitted
2019-10-17 22:31:58,045 WARN [Timer-Driven Process Thread-4] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] due to uncaught Exception: java.lang.ClassCastException: com.sun.proxy.$Proxy128 cannot be cast to org.apache.nifi.processors.groovyx.sql.OSql
java.lang.ClassCastException: com.sun.proxy.$Proxy128 cannot be cast to org.apache.nifi.processors.groovyx.sql.OSql
    at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onFinitSQL(ExecuteGroovyScript.java:371)
    at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:464)
    at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
2019-10-17 22:31:59,045 ERROR [Timer-Driven Process Thread-2] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=bb9d644c-016d-1000-5485-c915da52bbfb] org.apache.nifi.processor.exception.ProcessException: Attributes must contain an attribute name 'database.name': org.apache.nifi.processor.exception.ProcessException: Attributes must contain an attribute name 'database.name'
org.apache.nifi.processor.exception.ProcessException: Attributes must contain an attribute name 'database.name'
    at org.apache.nifi.dbcp.DBCPConnectionPoolLookup.getConnection(DBCPConnectionPoolLookup.java:124)
    at sun.reflect.GeneratedMethodAccessor1514.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
    at com.sun.proxy.$Proxy128.getConnection(Unknown Source)
    at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onInitSQL(ExecuteGroovyScript.java:339)
    at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:439)
    at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

SQL.db的脚本代码：

flowFile = session.get()
if(!flowFile) return
def RawData =flowFile.read().getText("UTF-8")
def JobId = flowFile.ExtractJobId
def params = [RawData,JobId]
def result = SQL.db.executeInsert("INSERT INTO ExtractFiles (RawData,JobId,CreateTimestamp,UpdateTimestamp) VALUES (?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)",params)
flowFile.'ExtractFileId' = result[0][0]
REL_SUCCESS << flowFile

CTL.sql的脚本代码：

import groovy.sql.Sql

flowFile = session.get()
if(!flowFile) return
def RawData =flowFile.read().getText("UTF-8")
def JobId = flowFile.ExtractJobId
def params = [RawData,JobId]
def myDbConnection = CTL.sql.getConnection(flowFile.getAttributes())
def myClientDb = new Sql(myDbConnection)
def result = myClientDb.executeInsert("INSERT INTO ExtractFiles (RawData,JobId,CreateTimestamp,UpdateTimestamp) VALUES (?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)",params)
flowFile.'ExtractFileId' = result[0][0]
REL_SUCCESS << flowFile
groovy

2条答案

按热度按时间
brc7rcf0

brc7rcf01#

我使用CTL对象并编辑代码来解决这个问题：

import groovy.sql.Sql

flowFile = session.get()
if(!flowFile) return
def dbConnection = CTL.db.getConnection(flowFile.getAttributes())
def clientDb = new Sql(dbConnection)
try {
    def RawData =flowFile.read().getText("UTF-8")
    def JobId = flowFile.ExtractJobId
    def params = [RawData,JobId]
    def result = clientDb.executeInsert("INSERT INTO ExtractFiles (RawData,JobId,CreateTimestamp,UpdateTimestamp) VALUES (?,?,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP)",params)
    flowFile.'ExtractFileId' = result[0][0]
    REL_SUCCESS << flowFile
} catch (Exception e) {
    throw e;
} finally {
    clientDb.close();
}

我添加了clientDb.close（），正如@daggett在评论中所说的那样。

赞(0）分享  回复(0）举报  2023-06-28
q1qsirdb

q1qsirdb2#

您需要在NIFI Groove脚本中执行SQL SELECT后关闭连接。

import org.apache.nifi.controller.ControllerService
import groovy.sql.Sql
def lookup = context.controllerServiceLookup
def dbServiceName = "PgSQLConnenctor"
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find {
    cs -> lookup.getControllerServiceName(cs) == dbServiceName
}
def conn = lookup.getControllerService(dbcpServiceId)?.getConnection()
   

flowFile = session.get()
if(!flowFile) return
try {
    csuid_message_ID = flowFile.getAttribute('message_id')
    csuid_spp = flowFile.getAttribute('spp')
    def sql = new Sql(conn)
    //TO THOMETHING
    sql.close() //<-- this solution for me !!!! We need to close connection.
}
赞(0）分享  回复(0）举报  2023-06-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com