Sql-server – Polybase with Cloudera 5.9

The fundamental issue is: Which server processes the query?

When using a linked server query, the query is executed on the LOCAL server and uses the linked server to access data on the remote server. (Linked servers are subject to several limitations, some caused by running through the Distributed Transaction Coordinator (DTC).)

When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server. (You will notice that in OPENQUERY the query uses a three-part name, since it all runs on the remote server, rather than the four-part name used for the linked server.)

A simple exposure to the limitations can be found in this thread: http://www.sqlservercentral.com/Forums/Topic948316-338-1.aspx

It mentions network topology, data types, type of remote server, the code in the query, etc.

EDIT: See more on the Guidelines for Using Distributed Queries at: http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx

The subheading Other Guidelines mentions: "To create the best query plans ..., the query processor must have data distribution statistics from the linked server. ... If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server."

If your connection to the remote server does not have access to those statistics that might cause a poor plan. Ideally not an inaccurate plan, but the query processor is imperfect.

i know it's been a while since the question but i found a possible solution.

This link provides an explanation and examples of how to use the connections available in the connection manager enabled for the Script Component in the SSIS https://msdn.microsoft.com/en-us/library/ms136060.aspx

The portion of code that we are interested is this:

IDTSConnectionManager100 connMgr;
HttpClientConnection100 hcc;

IDTSComponentMetaData100 compMetadata = this.ComponentMetaData; //Just for output purposes.       

connMgr = this.Connections.MyWebServer;

hcc = (HttpClientConnection100)cm.AcquireConnection(null);
compMetadata.FireInformation(1, "Message: ", "URL: " + hcc.ServerURL + " User: " + hcc.ServerUserName + " Pwd: " + hcc.GetServerPassword(), "", 0, ref fail);