Microsoft Linked Server frozen view and pipeline

[Douglas Romero da Cruz]

Hi guys.

I'm trying to ingest one table from a linked server in a local server that points to another remote database in the Azure environment.

The target is a big query in the google cloud environment.

For a small table, it works fine, preview and running.

For my biggest table with 160 million rows, it is taking forever till the pipeline crash. 

I'm using a multi-table JDBC component to get data from the source and Big Query to the destination, no transformation, simple data transfer pipeline.

Does anybody face something similar? 

I believe the reason is that Streamsets cannot get the source(azure) table metadata to identify which column is the primary key to defining an offset column, which I have tried to override with no success.

I'll appreciate any thoughts or advice.

Regards,

[Bob Plotts]

Hi Douglas, 

I think this problem can occur because some versions of the MS SQL JDBC Driver will buffer the entire result set in memory.  The rationale for this is described in this page: 

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-adaptive-buffering?view=sql-server-ver15

In part, the page mentioned above says this:

Normally, when the Microsoft JDBC Driver for SQL Server executes a query, the driver retrieves all of the results from the server into application memory. Although this approach minimizes resource consumption on the SQL Server, it can throw an OutOfMemoryError in the JDBC application for the queries that produce very large results.

To change this behavior and to buffer the result set on the server (not within SDC's JVM), fetching records as needed.  Add the following option to the “Additional JDBC Configuration Properties” fields in the JDBC tab. 

selectMethod = cursor

Thanks,

bob

--
You received this message because you are subscribed to the Google Groups "sdc-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sdc-user+u...@streamsets.com.
To view this discussion on the web visit https://groups.google.com/a/streamsets.com/d/msgid/sdc-user/2a277681-aa53-44e7-8565-230a5b9455a2n%40streamsets.com.

--

Bob Plotts Manager, Technical Support
EMAIL: b...@streamsets.com PRONOUNS: he, him, his

[Douglas Romero da Cruz]

Thanks Bob.

It's worked.

Regards,

Douglas.