sql server - Cannot connect Polybase to PostgreSQL

Question

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.

Thanks!!!

question from:https://stackoverflow.com/questions/65904854/cannot-connect-polybase-to-postgresql

Answer 1

Check that LOCATION is the name of the table, it may be case sensitive, and you can't specify the database neither the schema. Try connecting with the "postgres" user if you can. Also try specifying the IP instead of the hostname. I've used the ANSI driver, try with it as well: {PostgreSQL ANSI(x64)}. As a last resource, check the driver version, I tested with psqlodbc_12_02_0000-x64.zip.