You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@nifi.apache.org by "Carlos Manuel Fernandes (DSI)" <ca...@cgd.pt> on 2016/08/11 15:19:32 UTC

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

RE: Exception on Processor ConvertJSONToSQL

Posted by "Carlos Manuel Fernandes (DSI)" <ca...@cgd.pt>.

Thanks Bryan and Peter,
I saw  you had resolved the issue.


Bryan , I hope  your idea about a pluggable approach to generate different output formats goes forward, because that away you maintain the code of convertors in one place and you can put the convertors in all processors who generate raw of data.   In the present the convertors are binded to the processors.



Carlos


From: Bryan Bende [mailto:bbende@gmail.com]
Sent: segunda-feira, 15 de Agosto de 2016 16:49
To: users@nifi.apache.org
Subject: Re: Exception on Processor ConvertJSONToSQL

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) <pw...@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.fernandes@cgd.pt<ma...@cgd.pt>]
Sent: Thursday, August 11, 2016 9:20 AM
To: users@nifi.apache.org<ma...@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

Re: Exception on Processor ConvertJSONToSQL

Posted by Bryan Bende <bb...@gmail.com>.

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) <pw...@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.
> fernandes@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
>
>
>
>
>
>
>
>
>
>
>
>
>

RE: Exception on Processor ConvertJSONToSQL

Posted by "Peter Wicks (pwicks)" <pw...@micron.com>.

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.fernandes@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