Microsoft Linked Server frozen view and pipeline

4 views
Skip to first unread message

Douglas Romero da Cruz

unread,
Nov 18, 2021, 12:41:59 PM11/18/21
to sdc-user
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

unread,
Nov 18, 2021, 1:50:22 PM11/18/21
to Douglas Romero da Cruz, sdc-user
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

unread,
Nov 19, 2021, 3:04:36 PM11/19/21
to sdc-user, bob, sdc-user, Douglas Romero da Cruz

Thanks Bob.
It's worked.

Regards,
Douglas. 
Reply all
Reply to author
Forward
0 new messages