Polybase and External table

Question

Post reply

Polybase and External table

Robinson

SSCrazy

Points: 2194
More actions
April 20, 2021 at 3:33 pm

#3873542
Hi - we have 2 SQL Server 2019 instances, both with Polybase installed.
I created an external table in a database on instance A and pointed it to a table in a database on instance B.
Steps take in database on instance A:
- create master key in user database
- create credential
- create datasource using that credential
- create external table using that datasource
I ran the following script (modified to hide internal information, of course) on instance A:
```
use MyUserDatabase
go

create master key encryption by password = 'PASSWORD';
go

-- using a SQL Server Login on instance B named SQLUserRW
create database scoped credential INSTANCE_B_SQLUserRW with identity = 'SQLUserRW', secret = 'PASSWORD'
go

create external data source INSTANCE_B with (
    LOCATION = 'sqlserver://instance_b'
    , PUSHDOWN = ON
    , CREDENTIAL = INSTANCE_B_SQLUserRW
)
go

-- the external table is just pointing to database, schema and table 
-- with the same name but on a different SQL instance
CREATE external TABLE dbo.MY_TABLE (
    [ID] [varchar](40) NOT NULL
    , [TIME_STAMP] [datetime] NOT NULL
    -- more columns
) 
WITH
(
    location = 'MyUserDatabase.dbo.MY_TABLE'
    , DATA_SOURCE = INSTANCE_B
)
go
```
All the scripts to perform the steps above ran successfully but when I query the newly created external table, it just sits in executing state until I stop it. It doesn't error or timeout.
I'm finding that documentation and tutorials around this are pretty sparse in the sense that, while you can find documentation on setup, I haven't found much on troubleshooting.
Anybody have experience doing this that could provide some insight?
Thanks!
Rob

Viewing 5 posts - 1 through 4 (of 4 total)

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

sqlguru SSC-Addicted Points: 471 More actions · Answer 1

Can you check the polybase services on the second instance?

Robinson SSCrazy Points: 2194 More actions · Answer 2

Yes - both instances have 2 Polybase services each that are set to auto start. I can confirm all are currently running.

sqlguru SSC-Addicted Points: 471 More actions · Answer 3

On second instance. I think the Polybase Data movement service should be only running not the PolyBase Engine. It should be disabled. Can you restart the polybase Data movement services on the second instance and try?

Robinson SSCrazy Points: 2194 More actions · Answer 4

Was able to resolve the issue. I thought somebody had restarted the server after I installed the Polybase feature last week but I was wrong. I took a maintenance window and bounced the server this afternoon. My external table is now working properly. Success!