Carlos/Peter, Thanks for reporting this issue. It seems IS_AUTOINCREMENT is causing problems in a couple of situations, I know there was another issue with Hive where they return IS_AUTO_INCREMENT rather than IS_AUTOINCREMENT.
We should definitely address this issue... would either of you be interested in contributing your fix as a patch or PR? The try/catch approach seems reasonable to me assuming there is no method on ResultSet to check if the column exists. As far as being able to remove ConvertAvroToJson if ExecuteSQL could produce JSON... I think we initially went with Avro to preserve the most information about the schema/types coming from the database, but I do agree that many time the next step is to immediately convert it to some other format. I think it would be ideal if there was a pluggable approach when generating the output of these result sets so that any processors dealing with rows of data could provide an option to the user to select the output format and choose from options like Avro, JSON, CSV. There likely needs to be some more design discussion around it and of course development time :) Thanks, Bryan On Mon, Aug 15, 2016 at 11:16 AM, Peter Wicks (pwicks) <pwi...@micron.com> wrote: > Carlos, > > > > I ran into this same error when querying Teradata. It looks like a lot of > databases don’t include this. > > I submitted a bug a couple weeks ago: https://issues.apache.org/ > jira/browse/NIFI-2356 > > > > I did something similar to your suggestion locally in a modified version > of the code. > > > > Regards, > > Peter > > > > > > > > *From:* Carlos Manuel Fernandes (DSI) [mailto:carlos.antonio. > fernan...@cgd.pt] > *Sent:* Thursday, August 11, 2016 9:20 AM > *To:* users@nifi.apache.org > *Subject:* Exception on Processor ConvertJSONToSQL > > > > Hi All, > > > > I am making some tests to move data from Db2 to Netezza using Nifi. If I > don’t use costume processors, it’s a indirect away : > > > > ExecuteSQL(on db2) -> ConvertAvroToJSON -> ConvertJSONToSQL -> PutSQL > (bulk on netezza) > > > > and this away, I have an Exception on ConvertJSONToSQL: > > org.netezza.error.NzSQLException: The column name IS_AUTOINCREMENT not > found. > > at org.netezza.sql.NzResultSet.findColumn(NzResultSet.java:266) > ~[nzjdbc.jar:na] > > at org.netezza.sql.NzResultSet.getString(NzResultSet.java:1407) > ~[nzjdbc.jar:na] > > at > org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263) > ~[na:na] > > at org.apache.nifi.processors.standard.ConvertJSONToSQL$ > ColumnDescription.from(ConvertJSONToSQL.java:678) > ~[nifi-standard-processors-0.7.0.jar:0.7.0] > > > > Netezza jdbc driver doesn’t implement *IS_AUTOINCREMENT *metadata column > ( the same is true for oracle driver). Probably the reason is Netezza and > Oracle don’t have incremental columns because they use Sequences for this > purpose. > > > > On possible solution it to put a try catch (isn’t beautiful) around > > final String autoIncrementValue = resultSet.getString("IS_AUTOINCREMENT"); > (ConvertJSONToSQL.java:678) > > and on the catch, put autoIncrementValue=’NO’ > > > > > > Besides this error , we can remove on step ConvertAvroToJSON in the > flow if ExecuteSQL is changed to generate optional > > Output: Avro or JSON. > > > > What you Think? > > > > Thanks > > > > Carlos > > > > > > > > > > > > >
