You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Charlie Meyer <ch...@civitaslearning.com> on 2018/11/21 05:00:33 UTC
ExecuteSqlRecord, using SQL Server rowversion as max value column
Hi,
I'm attempting to do incremental fetch from a Microsoft SQL Server database
and would like to use rowversion [1] as my maximum value column. When I
configured the processor to use that column, it threw an exception [2]. Are
there known issues with using the rowversion for this purpose or
workarounds?
Thanks!
Charlie
[1]
https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017
[2]
https://github.com/apache/nifi/blob/d8d220ccb86d1797f56f34649d70a1acff278eb5/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java#L456
Re: ExecuteSqlRecord, using SQL Server rowversion as max value column
Posted by Charlie Meyer <ch...@civitaslearning.com>.
Sure thing and thanks for the quick response. I opened up an improvement
request [1]. For this table, I do not control the schema and unfortunately
this is the only column that meets the requirements for incremental
fetching. I'll keep looking into other possible workarounds pending Matt's
input.
thanks again!
[1] https://issues.apache.org/jira/browse/NIFI-5835
On Tue, Nov 20, 2018 at 11:13 PM Andy LoPresto <al...@apache.org> wrote:
> Hi Charlie,
>
> Looking at this issue briefly, it seems that the NiFi code explicitly
> lists the accepted datatypes which can be used, and rowversion is not
> enumerated. Therefore it throws an exception. I suggest you open a feature
> request on our Jira page to support this. While it seems proprietary to
> Microsoft SQL versions, it says on the documentation page:
>
> Is a data type that exposes automatically generated, unique binary numbers
> within a database. rowversion is generally used as a mechanism for
> version-stamping table rows. The storage size is 8 bytes. The rowversion data
> type is just an incrementing number and does not preserve a date or a time.
>
> I think we could handle this datatype the same way we handle INTEGER,
> SMALLINT, TINYINT (or TIMESTAMP, as that is the functional equivalent from
> MS SQL which is now deprecated) in that switch statement, as it is simply
> an incrementing 8 byte natural number. However, I would welcome input from
> someone like Matt Burgess to see if maybe there is a translation that can
> be done in the Microsoft-specific driver to a generic integer datatype
> before it reaches this logic. I would expect
> SQLServerResultSetMetaData#getColumnType(int column) to perform this
> translation; perhaps the version of the driver needs to be updated?
>
> For now, can you use a timestamp or other supported datatype to perform
> your incremental fetch?
>
>
> Andy LoPresto
> alopresto@apache.org
> *alopresto.apache@gmail.com <al...@gmail.com>*
> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
>
> On Nov 20, 2018, at 9:00 PM, Charlie Meyer <
> charlie.meyer@civitaslearning.com> wrote:
>
> Hi,
>
> I'm attempting to do incremental fetch from a Microsoft SQL Server
> database and would like to use rowversion [1] as my maximum value column.
> When I configured the processor to use that column, it threw an exception
> [2]. Are there known issues with using the rowversion for this purpose or
> workarounds?
>
> Thanks!
>
> Charlie
>
> [1]
> https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017
>
> [2]
> https://github.com/apache/nifi/blob/d8d220ccb86d1797f56f34649d70a1acff278eb5/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java#L456
>
>
>
Re: ExecuteSqlRecord, using SQL Server rowversion as max value column
Posted by Andy LoPresto <al...@apache.org>.
Hi Charlie,
Looking at this issue briefly, it seems that the NiFi code explicitly lists the accepted datatypes which can be used, and rowversion is not enumerated. Therefore it throws an exception. I suggest you open a feature request on our Jira page to support this. While it seems proprietary to Microsoft SQL versions, it says on the documentation page:
Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.
I think we could handle this datatype the same way we handle INTEGER, SMALLINT, TINYINT (or TIMESTAMP, as that is the functional equivalent from MS SQL which is now deprecated) in that switch statement, as it is simply an incrementing 8 byte natural number. However, I would welcome input from someone like Matt Burgess to see if maybe there is a translation that can be done in the Microsoft-specific driver to a generic integer datatype before it reaches this logic. I would expect SQLServerResultSetMetaData#getColumnType(int column) to perform this translation; perhaps the version of the driver needs to be updated?
For now, can you use a timestamp or other supported datatype to perform your incremental fetch?
Andy LoPresto
alopresto@apache.org
alopresto.apache@gmail.com
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
> On Nov 20, 2018, at 9:00 PM, Charlie Meyer <ch...@civitaslearning.com> wrote:
>
> Hi,
>
> I'm attempting to do incremental fetch from a Microsoft SQL Server database and would like to use rowversion [1] as my maximum value column. When I configured the processor to use that column, it threw an exception [2]. Are there known issues with using the rowversion for this purpose or workarounds?
>
> Thanks!
>
> Charlie
>
> [1] https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017 <https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017>
> [2] https://github.com/apache/nifi/blob/d8d220ccb86d1797f56f34649d70a1acff278eb5/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java#L456 <https://github.com/apache/nifi/blob/d8d220ccb86d1797f56f34649d70a1acff278eb5/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java#L456>