Use Polybase with ODBC to create external table

Question

Use Polybase with ODBC to create external table

blom0344

SSCrazy

Points: 2068
More actions
July 23, 2024 at 9:38 am

#4439068

I'm trying to use the installed Polybase service on an SQL 2019 server to create an external table by using and ODBC DSN.
The connection of the DSN is to a fairly exotic BBj server that hosts 3 databases.
Somehow I just do not seem to get the proper syntax for creating the external table.
I used this: (after setting up Database Scoped Credential and Master Key)
CREATE EXTERNAL DATA SOURCE CWB_POLYBASE
WITH ( LOCATION = 'odbc://192.168.253.37:2001',
CONNECTION_OPTIONS = 'Driver={BBj ODBC Driver};
ServerNode = TAXSYS_DIRECT_CWB:2001',
PUSHDOWN = ON,
CREDENTIAL = odbc );
where the ServerNode refers to the database name. I have no other option to point to the proper database , which puzzels me.
I would have expected to be able to point to the DSN itself (as that holds the database and other stuff)
Did anyone ever got an ODBC connection working using Polybase?

July 28, 2024 at 2:23 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 735513 More actions · Answer 1

Can you build a DSN That points here and connect? Something like: https://support.microsoft.com/en-us/office/administer-odbc-data-sources-b19f856b-5b9b-48c9-8b93-07484bfab5a7

I assume then you followed this: https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-odbc-generic?view=sql-server-ver16

Pablo Echeverria Right there with Babe Points: 719 More actions · Answer 2

In the external data source you don't specify the database name. It is in the external table that you specify the database name, for example:

-- SQL Server
CREATE EXTERNAL TABLE DatabasesExternal (
    name VARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS)
  WITH (LOCATION = 'master.sys.databases',
  DATA_SOURCE = SQLServerInstance);
-- Oracle
CREATE EXTERNAL TABLE [T1] (
   DECIMAL(38) NOT NULL,
  [RANDOM_INT] DECIMAL(38),
  [RANDOM_FLOAT] FLOAT(53))
WITH (
  LOCATION = '[ORCLCDB.localdomain].SYS.T1',
  DATA_SOURCE = external_data_source_name);
-- Teradata
CREATE EXTERNAL TABLE [TableC] (
  [MyKey] INT NOT NULL,
  [RandomInt] INT NOT NULL,
  [RandomFloat] DECIMAL(13, 2) NOT NULL)
WITH (
  LOCATION = 'TD_SERVER_DB.TableC',
  DATA_SOURCE = external_data_source_name);
-- MongoDB
CREATE EXTERNAL TABLE [MongoDbRandomData](
  [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [RandomData_friends_id] INT,
  [RandomData_tags] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS)
WITH (
  LOCATION='MyDb.RandomData',
  DATA_SOURCE=[MongoDb]);

Note it will depend on the ODBC if you can specify a 2-part name or a 3-part name. Also, posting the error messages can help with the troubleshooting.

blom0344 SSCrazy Points: 2068 More actions · Answer 3

I have 3 seperate DSN's on the SQL server instance , each pointing to a database on the BBj server. So I would have expected to point to the DSN name in the definition of the external datasource .

However, according to documentation the pointer is in the actual type of driver used, not the DSN

So, I guess this means that the external datasource definition only point at the general instance and that the exact database should be defined in the external table DDL?

blom0344 SSCrazy Points: 2068 More actions · Answer 4

The actual error message:

Msg 105082, Level 16, State 1, Line 21

105082;Generic ODBC error: NativeOdbcConnection.Open, error in OdbcConnectionCreate: SqlState: 08004, NativeError: 1, 'Error calling: SQLDriverConnect(this->_hdbc, NULL, (SQLWCHAR*)connectionString, SQL_NTS, NULL, 0, &cbConnStrOut, SQL_DRIVER_NOPROMPT), SQL return code: -1 | SQL Error Info: Error <1>: ErrorMsg: [BASIS][BBj ODBC Driver]Required connection information missing.Unable to connect. Connection string: DRIVER={BBj ODBC Driver};UID=odbc;PWD=YQOCK4Ctgrfu;servernode=TAXSYS_DIRECT_CWB:2001;SERVER=192.168.253.37;PORT=2001;| ConnectionPooling: 1 | Error calling: pConn->Create(connectionString, databaseName, useConnectionPooling, packetSize, connectionLoginTimeout, environmentSettings, transactionToken, transactionTokenSize, resourceGroupName, driverCapabilities, spid) | state: FFFF, number: 2, active connections: 1', Connection String: Driver={BBj ODBC Driver};servernode=TAXSYS_DIRECT_CWB:2001;uid=odbc;server=192.168.253.37;port=2001.

Pablo Echeverria Right there with Babe Points: 719 More actions · Answer 5

According to this: https://www.cdata.com/kb/tech/access-odbc-polybase.rst

You would put the DSN in the connection options:

CREATE EXTERNAL DATA SOURCE cdata_access_source
WITH (
LOCATION = 'odbc://SERVER_URL',
CONNECTION_OPTIONS = 'DSN=CData Access Sys',
-- PUSHDOWN = ON | OFF,
CREDENTIAL = access_creds
);

blom0344 SSCrazy Points: 2068 More actions · Answer 6

Pablo,

Following your syntax enabled me to define the external table.

But somehow is does not yet allow me to query it:

Msg 8680, Level 17, State 1, Line 1

Internal Query Processor Error: The query processor encountered an unexpected error during the processing of a remote query phase.

Pablo Echeverria Right there with Babe Points: 719 More actions · Answer 7

I googled a little bit and it could be the account doesn't have permissions, you need to dig further into the logs. Also, take a look here: https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-errors-and-possible-solutions?view=sql-server-ver16

blom0344 SSCrazy Points: 2068 More actions · Answer 8

The only thing in the logs is the message : Failed Stream Scan. The DSN is working when used in conjunction with a linked server query, so authentication should be no problem.

Pablo Echeverria Right there with Babe Points: 719 More actions · Answer 9

I found your same error message here: https://rafaeldontalgoncalez.com/2018/10/sql-server-2019-polybase-mongodb

Give me the results of this query: SELECT * FROM [DWDiagnostics].[dbo].[pdw_errors]

blom0344 SSCrazy Points: 2068 More actions · Answer 10

The error table contains 10.000 entries, but the following message seems the most relevant?:

Microsoft.SqlServer.DataWarehouse.Common.Security.MppSecurityException[107035:1]: 107035;Dms authorization failed due to [NOOT\nootbeheer] is not member of group [PdwDataMovementAccess]

at Microsoft.SqlServer.DataWarehouse.DataMovement.Common.Security.DMSSecurityProvider.RunSecurityRoutineGetNegotiateStream(Stream stream)

at Microsoft.SqlServer.DataWarehouse.DataMovement.Common.Security.DMSSecurityProvider.RunSecurityRoutine(Stream stream, Boolean encryptDataChannels)

at Microsoft.SqlServer.DataWarehouse.DataMovement.MessagingProtocol.ServerConnection.EstablishConnection()

blom0344 SSCrazy Points: 2068 More actions · Answer 11

I guess this indicates that if you change the account where the 2 services are running under AFTER getting them up or while installing, then the account that is used after the change lacks the proper group rights.

We'' check whether we can remedy this issue..

Pablo Echeverria Right there with Babe Points: 719 More actions · Answer 12

Yes that's correct, see here: https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-errors-and-possible-solutions?view=sql-server-ver16

It mentions: This error is likely due to changing the PolyBase service account. To change the service accounts for the PolyBase Engine and PolyBase Data Movement service, uninstall and reinstall the PolyBase feature.