In this article, we will see how to query a table in PostgreSQL by defining it as an external table in SQL Server.
First, we need to introduce our user that we use on the Postgresql side to Polybase and create a credential so that we can use it later.
|
1 2 |
CREATE DATABASE SCOPED CREDENTIAL postgresql_credential WITH IDENTITY = 'polybaseuser', SECRET = 'farukerdem.com'; |
After the Credential is created, we need to create a Data Source for the PostgreSQL connection.
|
1 2 3 4 5 6 7 |
CREATE EXTERNAL DATA SOURCE PostgreSQL_ds WITH ( LOCATION = 'odbc://20.56.93.246:5432', CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};Database=frk', -- PUSHDOWN = ON | OFF, CREDENTIAL = postgresql_credential ); |
We created the Data Source that we will use for the connection and created our credential for this data source connection.
Next, we need to create our table in PostgreSQL on the SQL server.
|
1 2 3 4 5 |
CREATE EXTERNAL TABLE postgresql_ex ( a int , b nvarchar(20) ) WITH (LOCATION = 'postgresql_table', DATA_SOURCE=PostgreSQL_ds) |
We created our external table, but what you need to pay attention to here is that the columns we defined on the postgresql and sql server side must match each other.
As seen above, when we query our table in postgresql from SQL Server, we see that our data is coming.
