sql-server - Apache NiFi :Convert JSONtoSQL (Oracle Database)

Question

我想使用 ConvertJSONtoSQL 处理器将 JSON 字符串转换为 SQL 语句。 示例:JSON 字符串 -

{"cpuwait":"0.0","servernamee":"mywindows","cpusys":"5.3","cpuidle":"77.6","datee":"29-SEP-2016","timee":"00:01:33","cpucpuno":"CPU01","cpuuser":"17.1"}

oracle db 中的表结构 -

CREATE TABLE cpu (
  datee varchar2(15) DEFAULT NULL,
  timee varchar2(10) DEFAULT NULL,
  servernamee varchar2(20) DEFAULT NULL,
  cpucpuno varchar2(4) DEFAULT NULL,
  cpuuser varchar2(5) DEFAULT NULL,
  cpusys varchar2(5) DEFAULT NULL,
  cpuwait varchar2(5) DEFAULT NULL,
  cpuidle varchar2(5) DEFAULT NULL
);

用于 MySQL 数据库的配置:

数据库连接url:jdbc:mysql://localhost:3306/testnifi

数据库驱动程序类名:com.mysql.jdbc.Driver

我使用(DBCP连接池)JDBC url、用户名和密码成功连接到MySQL。ConvertJSONtoSQL 处理器在那里成功工作，我得到有效的 sql 插入语句作为输出。但是当我对 Oracle 数据库尝试同样的操作时，我得到了

ERROR [Timer-Driven Process Thread-6] o.a.n.p.standard.ConvertJSONToSQL java.sql.SQLException: Stream has already been closed

我的 Oracle 数据库连接配置:

我在google中搜索了该错误，但我发现当在数据库表中使用长数据类型但我没有使用它们时，就会出现此错误。我浏览了 ConvertJSONtoSQL 处理器的源代码(以下堆栈跟踪)并尝试在 Eclipse 中实现相同的代码，但没有收到任何错误，我可以连接到数据库并进行查询。

那么我的配置有什么错误吗？

Nifi 版本 - 0.7.0/1.0(我在两个版本中都遇到相同的错误)

java版本-java8

Oracle DB 版本 - Oracle Database 11g Express 版

完整的堆栈跟踪:

2016-10-19 07:10:06,557 ERROR [Timer-Driven Process Thread-6] o.a.n.p.standard.ConvertJSONToSQL java.sql.SQLException: Stream has already been closed at oracle.jdbc.driver.LongAccessor.getBytesInternal(LongAccessor.java:156) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.LongAccessor.getBytes(LongAccessor.java:126) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.LongAccessor.getString(LongAccessor.java:201) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.T4CLongAccessor.getString(T4CLongAccessor.java:427) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1251) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494) ~[ojdbc6.jar:11.2.0.1.0] at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263) ~[na:na] at org.apache.nifi.processors.standard.ConvertJSONToSQL$ColumnDescription.from(ConvertJSONToSQL.java:677) ~[nifi-standard-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processors.standard.ConvertJSONToSQL$TableSchema.from(ConvertJSONToSQL.java:621) ~[nifi-standard-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processors.standard.ConvertJSONToSQL.onTrigger(ConvertJSONToSQL.java:267) ~[nifi-standard-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-0.7.0.jar:0.7.0] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.7.0_40] at java.util.concurrent.FutureTask.runAndReset(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.7.0_40] at java.lang.Thread.run(Unknown Source) [na:1.7.0_40

Answer 1

这似乎是 Oracle 驱动程序中的一个错误。请参阅:

1. https://blog.jooq.org/2015/12/30/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/
2. Hibernate custom type to avoid 'Caused by: java.sql.SQLException: Stream has already been closed'

第 2 项给了我解决方法。基本上在 bootstrap.conf 中添加以下参数:

java.arg.xx=-Doracle.jdbc.useFetchSizeWithLongColumn=true