SQL Server 的自定义处理器 + DBCPConnectionPool:未加载驱动程序 jar [英] Custom processor + DBCPConnectionPool for SQL Server : driver jar not loaded
问题描述
我创建了一个控制器服务来连接到测试数据库.
I have created a controller service to connect to a test db.
我有一个自定义处理器，可以从 SQL Server 读取数据，模拟测试、构建和部署到 NiFi 成功.处理器出错，可能是 nar 依赖范围有问题，或者...?
I have a custom processor that reads data from SQL Server, the mock tests, the build and the deployment to NiFi succeed. The processor runs into error, maybe the nar dependency scope is at fault or ... ?
我不确定处理器和 nar 项目的 pom 如下:
I am unsure The pom for processor and the nar projects are as follows :
处理器 pom.xml
processor pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>com.datalake</groupId>
<artifactId>CDCNiFi</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<artifactId>nifi-NiFiCDCPoC-processors</artifactId>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.apache.nifi</groupId>
<artifactId>nifi-api</artifactId>
</dependency>
<dependency>
<groupId>org.apache.nifi</groupId>
<artifactId>nifi-utils</artifactId>
</dependency>
<dependency>
<groupId>org.apache.nifi</groupId>
<artifactId>nifi-dbcp-service-api</artifactId>
</dependency>
<dependency>
<groupId>org.apache.nifi</groupId>
<artifactId>nifi-processor-utils</artifactId>
</dependency>
<!-- Third-party -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.1.0.jre8</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.8.7</version>
</dependency>
<!-- Testing & Cross-cutting concerns -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.nifi</groupId>
<artifactId>nifi-mock</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
</project>
nar pom.xml
nar pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>com.datalake</groupId>
<artifactId>CDCNiFi</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<artifactId>nifi-NiFiCDCPoC-nar</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>nar</packaging>
<properties>
<maven.javadoc.skip>true</maven.javadoc.skip>
<source.skip>true</source.skip>
</properties>
<dependencies>
<dependency>
<groupId>com.datalake</groupId>
<artifactId>nifi-NiFiCDCPoC-processors</artifactId>
<version>1.0-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>org.apache.nifi</groupId>
<artifactId>nifi-standard-services-api-nar</artifactId>
<type>nar</type>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.1.0.jre8</version>
<scope>runtime</scope>
</dependency>
</dependencies>
</project>
问题:
SQL Server JDBC jar 可能没有在运行时加载，app.log 有以下错误:
The SQL Server JDBC jar is , probably, not loaded at runtime, the app.log has the following error :
2017-03-09 15:04:06,074 ERROR [Timer-Driven Process Thread-1] c.s.d.processors.SQLServerCDCProcessor SQLServerCDCProcessor[id=ad9de403-015a-1000-2b40-7efbfdb049b1] Process or SQL exception in <configure logger template to pick the code location>
2017-03-09 15:04:06,080 ERROR [Timer-Driven Process Thread-1] c.s.d.processors.SQLServerCDCProcessor
org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:252) ~[na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_71]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_71]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_71]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_71]
at org.apache.nifi.controller.service.StandardControllerServiceProvider$1.invoke(StandardControllerServiceProvider.java:177) ~[na:na]
at com.sun.proxy.$Proxy89.getConnection(Unknown Source) ~[na:na]
at com.datalake.processors.SQLServerCDCProcessor.onTrigger(SQLServerCDCProcessor.java:244) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.1.jar:1.1.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_71]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_71]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_71]
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1429) ~[na:na]
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371) ~[na:na]
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) ~[na:na]
at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:249) ~[na:na]
... 19 common frames omitted
Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ~[na:1.8.0_71]
at org.apache.nifi.nar.InstanceClassLoader.findClass(InstanceClassLoader.java:117) ~[nifi-nar-utils-1.1.1.jar:1.1.1]
at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[na:1.8.0_71]
at org.apache.nifi.nar.InstanceClassLoader.loadClass(InstanceClassLoader.java:98) ~[nifi-nar-utils-1.1.1.jar:1.1.1]
at org.apache.nifi.nar.InstanceClassLoader.loadClass(InstanceClassLoader.java:82) ~[nifi-nar-utils-1.1.1.jar:1.1.1]
at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1420) ~[na:na]
... 22 common frames omitted
在控制器服务中，我没有看到我的自定义处理器引用组件"(它说没有引用组件")
In the controller service, I do not see my custom processor in 'Referencing Components'(it says 'no referencing components')
现在，我已经硬编码了数据库驱动程序位置"，但在生产，我希望控制器从类路径(不知何故，这可能吗?)
Right now, I have hard-coded the 'Database Driver Location(s)' but in production, I would like the controller to pick up that from the classpath(somehow, is it possible?)
*****编辑-1*****按照马亨德拉的建议，我做了以下事情:
*****Edit-1***** As suggested by Mahendra, I did the following :
没有对现有的 pom.xml 文件进行任何更改，将依赖项 (sqljdbc4, 4.0) 添加到两者中，构建和部署成功，但 jdbc 驱动程序错误仍然存在
Without any changes to the existing pom.xml files, added the dependency (sqljdbc4, 4.0) to both, the build and deployment succeeded but the jdbc driver error persisted
数据库驱动程序位置"保留为file:///".同样的错误
The 'Database Driver Location(s)' kept as 'file:///'. Same error
在 NiFi/lib 中添加了 sqljdbc4.jar 并且驱动程序错误消失了(得到其他错误，但这些错误将在不同的线程中处理).注意控制器服务仍然没有将处理器显示为引用组件".
Added the sqljdbc4.jar in NiFi/lib and the driver error vanished(getting other errors but those will be taken in a different thread). Note that the controller service still doesn't show the processor as 'referenced component'.
Java 版本
java -versionjava版本1.8.0_121"Java(TM) SE 运行时环境(构建 1.8.0_121-b13)Java HotSpot(TM) 64 位服务器 VM(构建 25.121-b13，混合模式)
java -version java version "1.8.0_121" Java(TM) SE Runtime Environment (build 1.8.0_121-b13) Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)
有没有办法避免在lib中添加sqljdbc4.jar，我的意思是像fasterxml等第三方依赖项运行正常，这是我的配置问题还是sql server maven repos的问题.?
Isn't there a way to avoid adding the sqljdbc4.jar in lib, I mean third-party dependencies like fasterxml etc. are behaving properly, is this an issue with my configuration or some problem with the sql server maven repos. ?
推荐答案
@kaliyug,
编辑 1:
1.如果你声明的DB类名是"com.microsoft.sqlserver.jdbc.SQLServerDriver".你应该在pom中添加下面的maven依赖，如下所示.
1.if you declared DB class name is "com.microsoft.sqlserver.jdbc.SQLServerDriver".You should add below maven dependency in pom like below.,
<dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>sqljdbc4</artifactId> <version>4.2</version> </dependency>或者，如果您想在生产环境中使用，您可以在控制器服务中指定驱动程序数据库位置，如下所示.
Or If you want's to use in production environment you can specify driver database location in controller service like below.
file:///C:\DBLocation\sqljdbc42.jar3.或者你也可以直接将"sqljdbc42.jar"复制到NiFi\Lib目录中.复制jar后，你应该重启NiFi服务.在运行时,SQL jar 在 Nifi\Lib 目录中搜索 DB ClassName 时自动加载.
3.Or you can just copy the "sqljdbc42.jar" directly into NiFi\Lib directory.After copied the jar,you should restart NiFi Services.At runtime ,SQL jar automatically Loaded when it search for DB ClassName in Nifi\Lib directory.
如果您遇到任何问题，请告诉我.
Please let me know if you have face any issues.
这篇关于SQL Server 的自定义处理器 + DBCPConnectionPool:未加载驱动程序 jar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
