POLYBASE and PostgresSQL

Question

Hi everbody!

Trying to setup a connection to a PostgresSQL server with MSSQL Polybase. Today we use Linked Server to withdraw data from the Postgresdatabases into MSSQL and it works fine. But there is some functionality with Polybase that would solve some program issus regarding joining etc, and therefore Polybase is the solution. As long it works! ;-)

But I dont get it to work. And I can't find any real help with Google.

This is the code;

CREATE DATABASE SCOPED CREDENTIAL PG_EXAMPLE WITH IDENTITY = 'pgUSER', Secret = 'verylongpassword';

CREATE EXTERNAL DATA SOURCE PG_EXAMPLE_DATA
WITH ( LOCATION = 'odbc://PG_SERVERNAME:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)}',
PUSHDOWN = ON,
CREDENTIAL = PG_EXAMPLE);

Trying to create a external table:

CREATE EXTERNAL TABLE databas(
namn [nvarchar](255) NULL,
datorid [nvarchar](255) NULL
) WITH (
LOCATION='exampel_databas_on_PGserver',
DATA_SOURCE=PG_EXAMPLE_DATA
);

ERROR MESSAGE

Msg 105082, Level 16, State 1, Line 10
105082;Generic ODBC error: Error while executing the query .

Can anybody spread some light here, what I'm doing wrong. Somebody perhaps tried and got it to work??

Any help and suggestion is very mutch appreciated.

Answer 1

I assume that it is the CREATE EXTERNAL TABLE statement that fails.

The LOCATION clause looks funny. I don't know much about Postgres, but the location in this case should resolve to a table. Here is a sample statement from my environment, where the external table is on another SQL Server instance:

CREATE EXTERNAL TABLE [dbo].[centos_details]
(
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [decimal](10, 2) NOT NULL,
    [Quantity] [smallint] NOT NULL
)
WITH (DATA_SOURCE = [centos],LOCATION = N'Northgale_utf8.dbo.[Order Details]')

I would expect a similar three-part notation on Postgres.

Answer 2

Hi @Erber ,

I am not familiar with PostgresSQL, but I search this from google. We are using following steps to create an External Data Source for PostgreSQL Data.

1. Creating a Master Encryption Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
2. Create credentials for the external data source connected to PostgreSQL data CREATE DATABASE SCOPED CREDENTIAL postgresql_creds
   WITH IDENTITY = 'username', SECRET = 'password';
   3. Create an external data source for PostgreSQL with PolyBase
   CREATE EXTERNAL DATA SOURCE cdata_postgresql_source
   WITH (
   LOCATION = 'odbc://SERVERNAME[:PORT]',
   CONNECTION_OPTIONS = 'DSN=CData PostgreSQL Sys',
   -- PUSHDOWN = ON | OFF,
   CREDENTIAL = postgresql_creds
   );
   4. Use CREATE EXTERNAL TABLE statements to link to PostgreSQL data from your SQL Server instance
   CREATE EXTERNAL TABLE Orders(
   ShipName nvarchar NULL,
   ShipCity nvarchar NULL,
   ...
   ) WITH (
   LOCATION='Orders',
   DATA_SOURCE=cdata_postgresql_source
   );

Please refer to Connect to PostgreSQL as an External Data Source using PolyBase and PolyBase and Postgres to get more detail information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hej Erland!
It was the CREATE EXTERNAL TABLE that failed. But after numerals try/error I've got it to work. Sort of any way. I've succeeded to create a External Table. After changing the LOCATION to 'database_name.table' it worked. I also updated the driver just to be sure.
First I've got Msg 105083, Level 16, State 1, Line 38
105083;The following columns in the user defined schema are incompatible with the external table schema for table.....etc. This error was just that the datatypes wasn't matchning. This errormessage was the first indication that I've received data from the Postgres machine since I was able to read the matching column name datatype. So it was easy to then match correct. I was then able to create the table. But then trying to do a select from the new external table I've got the error Msg 7320, Level 16, State 110, Line 9
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)", 105082;Generic ODBC error: ERROR: relation "database_name.table" does not exist;

Answer 4

I played around with this, and I was able to set up an external table for a table in my Postgres database in Azure.

You have a DSN in your definition of the external data source, but I don't see the definition of the DSN but I think the problem is that you have not specified the database, and in that case the driver appears to take the username as the database name.

Here is how I set up my external data source:

CREATE EXTERNAL DATA SOURCE postsommar
 WITH ( LOCATION = 'odbc://postsommar.database.windows.net',
 CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};Database=postgres',
 PUSHDOWN = ON,
 CREDENTIAL = postgres_user);

And here is the external table:

CREATE EXTERNAL TABLE mypostgrestest (
    id_number int NOT NULL,
    name_t nvarchar(50) NOT NULL
) WITH (LOCATION = 'mytest',
        DATA_SOURCE=postsommar)

Initially, I did not have the Database part in the connection options, and this was confusing, because when I specified LOCATION as postsommar.mytable that was successful, but when running a SELECT against the table that failed with the same error as you got.

That is certainly illogical, but the entire location postgres.postsommar is illogical - it should be postgres.public.postsommar. However, that produced the error "The specified LOCATION string 'postgres.public.mytest' could not be parsed."