Polybase TSQL Compare Issues

%3CLINGO-SUB%20id%3D%22lingo-sub-2353611%22%20slang%3D%22en-US%22%3EPolybase%20TSQL%20Compare%20Issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2353611%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all.%26nbsp%3B%20Polybase%20newbie%20here%2C%20so%20hoping%20that%20I'm%20just%20missing%20something%20simple.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20configured%20Polybase%20between%20SQL%20Server%202019%20CU9%20and%20MySQL%20(5.7.12)%20via%20a%20generic%20ODBC%20driver.%26nbsp%3B%20Able%20to%20create%20the%20external%20table%20without%20issue%20and%20can%20do%20a%20basic%20select%20statement%20on%20the%20external%20table%20without%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20once%20I%20start%20to%20join%20that%20table%20with%20other%20tables%20and%20do%20where%20clauses%2C%20things%20start%20to%20get%20funky.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExternal%20table%20config%20code%20here%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20MASTER%20KEY%20ENCRYPTION%20BY%20PASSWORD%20%3D%20''%3B%0AGO%0ACREATE%20DATABASE%20SCOPED%20CREDENTIAL%20mySQL_creds%20WITH%20IDENTITY%20%3D%20''%2C%20SECRET%20%3D%20''%3B%0AGO%0ACREATE%20EXTERNAL%20DATA%20SOURCE%20%5BDataSource%5D%20%0AWITH%20(%20LOCATION%20%3D%20'odbc%3A%2F%2FsomecoolMySQLdbGoesHere.us-east-1.rds.amazonaws.com%3A3306'%0A%20%2C%20CONNECTION_OPTIONS%20%3D%20'DSN%3DMySQLODBCConn'%0A%20%2C%20PUSHDOWN%20%3D%20on%0A%20%2C%20CREDENTIAL%20%3D%20mySQL_creds%0A%20)%3B%0AGO%0ACREATE%20EXTERNAL%20TABLE%20%5Bp%5D.%5BAccount%5D%20%0A(%5BaccountID%5D%20nvarchar(32)%2C%20%5BaccountNumber%5D%20int%0A%2C%20firstname%20nvarchar(255)%20lastname%20nvarchar(255)%20%20%0A)%0AWITH%20(%20LOCATION%20%3D%20'account'%2CDATA_SOURCE%20%3D%20%5BDataSource%5D%20)%20%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20internal%20table%20looks%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5BAccounts%5D%0A(%5Baccount_id%5D%20%5Bnumeric%5D(20%2C%200)%20NOT%20NULL%0A%2C%20%5Bsponsor_id%5D%20%5Bnumeric%5D(20%2C%200)%20NULL%0A%2C%20%5Broman_name_2%5D%20%5Bnvarchar%5D(320)%20NULL%0A%20CONSTRAINT%20%5BPK_Distributors%5D%20PRIMARY%20KEY%20CLUSTERED%20(%5Baccount_id%5D%20ASC)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20when%20I%20join%2C%20I%20start%20to%20have%20challenges.%26nbsp%3B%20This%20works%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eselect%20top%2010%20pa.accountNumber%2C%20pa.firstname%2C%20pa.lastname%2C%20da.roman_name%0Afrom%20p.account%20as%20pa%0Ainner%20join%20dbo.account%20as%20da%20on%20pa.accountNumber%20%3D%20da.account_id%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20this%20doesn't%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20top%2010%20pa.accountNumber%2C%20pa.firstname%2C%20pa.lastname%2C%20da.roman_name%0Afrom%20p.account%20as%20pa%0Ainner%20join%20dbo.account%20as%20da%20on%20pa.accountNumber%20%3D%20da.account_id%0Awhere%20da.account_id%20%3D%20123456%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%20SQL%20Returns%20the%20error%3A%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3E%22Msg%207320%2C%20Level%2016%2C%20State%20110%2C%20Line%2016%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3ECannot%20execute%20the%20query%20%22Remote%20Query%22%20against%20OLE%20DB%20provider%20%22MSOLEDBSQL%22%20for%20linked%20server%20%22(null)%22.%20110802%3B%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EAn%20internal%20DMS%20error%20occurred%20that%20caused%20this%20operation%20to%20fail.%20Details%3A%20105082%3B%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EGeneric%20ODBC%20error%3A%20%5BMySQL%5D%5BODBC%208.0(a)%20Driver%5D%5Bmysqld-5.7.12%5D%20.%22%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESimilar%20issues%20occur%20regardless%20of%20how%20I%20format%20the%20WHERE%20clause%20or%20what%20side%20the%20WHERE%20clause%20is%20filtering.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20polybase%20logs%20do%20not%20appear%20to%20offer%20any%20additional%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPolybase_DMS_Errors.log%20shows%3A%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3E5%2F14%2F2021%2012%3A14%3A33%20AM%20%5BThread%3A780%5D%20%5BAbstractReaderWorker%3AErrorEvent%5D%20(Error%2C%20High)%3A%20QueryId%20QID449%20PlanId%20491d85e2-61e4-4c4a-8f0b-c07d6ec09f13%20StepId%203%3A%20%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3EMicrosoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException%5B105082%3A1%5D%3A%20105082%3BGeneric%20ODBC%20error%3A%20%5BMySQL%5D%5BODBC%208.0(a)%20Driver%5D%5Bmysqld-5.7.12%5D%20.%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Eat%20Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.ThrowMppSqlException(String%20message%2C%20NativeOdbcConnection%20connection)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Eat%20Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.DataReader.OdbcBufferReader.ReadBuffer(MemoryBuffer%20buffer)%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Eat%20Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.RoundRobinBufferReaderWorker.ReadAndSendData()%3C%2FEM%3E%3CBR%20%2F%3E%3CEM%3Eat%20Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.AbstractReaderWorker.Execute(Object%20status)%3C%2FEM%3E%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20snippet%20from%20the%20DWEgine_errors.log.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20got%20a%20clue%20as%20to%20what%20is%20going%20on%20or%20a%20way%20to%20track%20it%20down%20so%20I%20can%20figure%20this%20out%3F%26nbsp%3B%20I%20have%20a%20hard%20time%20believing%20we've%20created%20a%20system%20where%20we%20can't%20filter%20it%2C%20but%20can%20magically%20join%20to%20it.%26nbsp%3B%20Makes%20no%20sense.%26nbsp%3B%20Appreciate%20any%20assistance%20you%20can%20provide.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETenagra%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello all.  Polybase newbie here, so hoping that I'm just missing something simple. 

 

I've configured Polybase between SQL Server 2019 CU9 and MySQL (5.7.12) via a generic ODBC driver.  Able to create the external table without issue and can do a basic select statement on the external table without issue.

 

However, once I start to join that table with other tables and do where clauses, things start to get funky.  

 

External table config code here:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO
CREATE DATABASE SCOPED CREDENTIAL mySQL_creds WITH IDENTITY = '', SECRET = '';
GO
CREATE EXTERNAL DATA SOURCE [DataSource] 
WITH ( LOCATION = 'odbc://somecoolMySQLdbGoesHere.us-east-1.rds.amazonaws.com:3306'
	, CONNECTION_OPTIONS = 'DSN=MySQLODBCConn'
	, PUSHDOWN = on
	, CREDENTIAL = mySQL_creds
	);
GO
CREATE EXTERNAL TABLE [p].[Account] 
([accountID] nvarchar(32), [accountNumber] int
, firstname nvarchar(255) lastname nvarchar(255)  
)
WITH (	LOCATION = 'account',DATA_SOURCE = [DataSource] ) ;

 

My internal table looks like this:

CREATE TABLE [dbo].[Accounts]
([account_id] [numeric](20, 0) NOT NULL
, [sponsor_id] [numeric](20, 0) NULL
, [roman_name_2] [nvarchar](320) NULL
 CONSTRAINT [PK_Distributors] PRIMARY KEY CLUSTERED ([account_id] ASC)
)

 

Now, when I join, I start to have challenges.  This works:

select top 10 pa.accountNumber, pa.firstname, pa.lastname, da.roman_name
from p.account as pa
inner join dbo.account as da on pa.accountNumber = da.account_id

 

But this doesn't:

select top 10 pa.accountNumber, pa.firstname, pa.lastname, da.roman_name
from p.account as pa
inner join dbo.account as da on pa.accountNumber = da.account_id
where da.account_id = 123456

  SQL Returns the error:

"Msg 7320, Level 16, State 110, Line 16
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 110802;
An internal DMS error occurred that caused this operation to fail. Details: 105082;
Generic ODBC error: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.12] ."

 

Similar issues occur regardless of how I format the WHERE clause or what side the WHERE clause is filtering.

 

The polybase logs do not appear to offer any additional information. 

 

Polybase_DMS_Errors.log shows:

5/14/2021 12:14:33 AM [Thread:780] [AbstractReaderWorker:ErrorEvent] (Error, High): QueryId QID449 PlanId 491d85e2-61e4-4c4a-8f0b-c07d6ec09f13 StepId 3:
Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException[105082:1]: 105082;Generic ODBC error: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.12] .
at Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.ThrowMppSqlException(String message, NativeOdbcConnection connection)
at Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.DataReader.OdbcBufferReader.ReadBuffer(MemoryBuffer buffer)
at Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.RoundRobinBufferReaderWorker.ReadAndSendData()
at Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.AbstractReaderWorker.Execute(Object status)

 

Attached is the snippet from the DWEgine_errors.log.  

 

Anyone got a clue as to what is going on or a way to track it down so I can figure this out?  I have a hard time believing we've created a system where we can't filter it, but can magically join to it.  Makes no sense.  Appreciate any assistance you can provide.

 

Sincerely,

 

Tenagra

1 Reply

@Tenagra how did you generate the T-SQL for creating the external table? I've found issues when there's anything different, so a trick I use is to let Polybase tell me what data types it is seeing, for example "create external table p.Account (dummy int) with (location='account', data_source=[DataSource]);", then the error returned will tell me exactly what are the columns and collations I must use when creating the external table, so I just replace the dummy T-SQL with the one returned.

I'm guessing between the NUMERIC(20,0) and the INT, Polybase is trying to do a conversion within the SQL statement being sent to MySQL, which the ODBC is catching up and failing before sending the request to MySQL.

You can also try doing the conversion from NUMERIC(20,0) to INT before the table join and see what happens.