.::: Setup Polybase in SQL Server for External Table PostgreSQL :::.

 

Fungsi PolyBase di SQL Server untuk Sumber Eksternal
PolyBase di SQL Server memungkinkan Anda untuk mengakses data dari berbagai sumber eksternal, termasuk SQL Server lain dan PostgreSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC PostgreSQL
 

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL PGCred
WITH IDENTITY = 'admin', SECRET = 'admin'

3. Buat External Data Source postgresql (paling bawah)

CREATE EXTERNAL DATA SOURCE PostgresSource
WITH (
    LOCATION = 'odbc://localhost',
    CONNECTION_OPTIONS = 'DSN=PostgreSQL35W',
    CREDENTIAL = PGCred
);

CREATE EXTERNAL DATA SOURCE PostgresSource
WITH (
    LOCATION = 'odbc://10.10.10.7',  
    CONNECTION_OPTIONS = 'DSN=postgresql35w;uid=admin;pwd=admin;database=erpdb'
);

-- using this

CREATE EXTERNAL DATA SOURCE PostgresSource
WITH (
    LOCATION = 'odbc://10.10.10.7',  
 ---   CONNECTION_OPTIONS = 'DSN=postgresql35w;uid=admin;pwd=admin;'
    CONNECTION_OPTIONS = 'DSN=postgresql35w;uid=admin;pwd=admin;database=erpdb',
    CREDENTIAL = PGCred  -- WAJIB jika tidak hardcode user/pass di DSN
);

4. Buat External Table untuk Akses Data Misalkan ada tabel bernama customers di postgresql .

CREATE EXTERNAL TABLE edbCustomers (
    customer_id INT NOT NULL,
    customer_name NVARCHAR(100) NOT NULL,
    email NVARCHAR(100),
    phone NVARCHAR(15),
    address NVARCHAR(MAX),
    created_at DATETIME2(6)  -- Ubah dari DATETIMEOFFSET
)
WITH (
    LOCATION = 'customers',  -- nama tabel di PostgreSQL
    DATA_SOURCE = PostgresSource
);

5. check sys.database_scoped_credentials & external table

SELECT @@servername as ServerName, * FROM sys.database_scoped_credentials;

SELECT @@servername as ServerName,
    name AS ExternalTableName,
    object_id,type,type_desc,location,
    create_date,
    modify_date
FROM sys.external_tables;
 

-- configuration

EXEC sp_configure 'polybase enabled', 1;
RECONFIGURE;

SELECT SERVERPROPERTY('IsPolyBaseInstalled') AS Is_PolyBase_Installed;
EXEC sp_configure 'polybase enabled';

-- check configuration

-- Cek External Data Sources (Sumber Data PolyBase)

SELECT *  
FROM sys.external_data_sources;

SELECT * FROM sys.dm_exec_compute_nodes;
 

6. check result external table
-- SQL Server
SELECT * FROM [teguhth].[dbo].[edbCustomers]

-- PostgreSQL
select version();
SELECT * FROM customers;

 

7. check list table external

SELECT @@SERVERNAME as ServerName,
    s.name AS schema_name,
    t.name AS external_table_name
FROM
    sys.external_tables t
JOIN
    sys.schemas s ON t.schema_id = s.schema_id;

SELECT @@SERVERNAME as ServerName,
    s.name AS schema_name,
    t.name AS external_table_name,
    ds.name AS data_source_name,
    ds.location AS data_source_location,
    ds.type AS data_source_type,
    cr.name AS credential_name
FROM
    sys.external_tables t
JOIN
    sys.schemas s ON t.schema_id = s.schema_id
JOIN
    sys.external_data_sources ds ON t.data_source_id = ds.data_source_id
LEFT JOIN
    sys.database_scoped_credentials cr ON ds.credential_id = cr.credential_id;
 

8.     check list table external detail
SELECT
    @@SERVERNAME AS ServerName,
    ds.name AS data_source_name,
    ds.location AS data_source_location,
    ds.type AS data_source_type,
    cr.name AS credential_name,
    s.name AS schema_name,
    t.name AS external_table_name
FROM
    sys.external_data_sources ds
LEFT JOIN
    sys.database_scoped_credentials cr ON ds.credential_id = cr.credential_id
LEFT JOIN
    sys.external_tables t ON ds.data_source_id = t.data_source_id
LEFT JOIN
    sys.schemas s ON t.schema_id = s.schema_id;

SELECT
    @@SERVERNAME AS ServerName,
    ds.name AS data_source_name,
    ds.location AS data_source_location,
    ds.type AS data_source_type,
    cr.name AS credential_name,
    s.name AS schema_name,
    t.name AS external_table_name
FROM
    sys.external_data_sources ds
LEFT JOIN
    sys.database_scoped_credentials cr ON ds.credential_id = cr.credential_id
LEFT JOIN
    sys.external_tables t ON ds.data_source_id = t.data_source_id
LEFT JOIN
    sys.schemas s ON t.schema_id = s.schema_id;

SELECT
    @@SERVERNAME AS ServerName,
    ds.name AS data_source_name,
    ds.location AS data_source_location,
    ds.type AS data_source_type,
    cr.name AS credential_name,
    s.name AS schema_name,
    t.name AS external_table_name,
    o.create_date,
    o.modify_date
FROM
    sys.external_data_sources ds
LEFT JOIN
    sys.database_scoped_credentials cr ON ds.credential_id = cr.credential_id
LEFT JOIN
    sys.external_tables t ON ds.data_source_id = t.data_source_id
LEFT JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
    sys.objects o ON t.object_id = o.object_id;
 

9. check list table external detail with table location
 

SELECT
    @@SERVERNAME AS ServerName,
    ds.name AS data_source_name,
    ds.location AS data_source_location,
    ds.type AS data_source_type,
    cr.name AS credential_name,
    s.name AS schema_name,
    t.name AS external_table_name,
    t.location AS external_table_location, -- ini hanya jika SQL Server mendukungnya
    o.create_date,
    o.modify_date
FROM
    sys.external_data_sources ds
LEFT JOIN
    sys.database_scoped_credentials cr ON ds.credential_id = cr.credential_id
LEFT JOIN
    sys.external_tables t ON ds.data_source_id = t.data_source_id
LEFT JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
    sys.objects o ON t.object_id = o.object_id;
    
SELECT
    @@SERVERNAME AS ServerName,
    ds.name AS data_source_name,
    ds.location AS data_source_location,
    ds.type AS data_source_type,
    cr.name AS credential_name,
    s.name AS schema_name,
    t.name AS external_table_name,
    OBJECT_DEFINITION(t.object_id) AS external_table_definition,
    o.create_date,
    o.modify_date
FROM
    sys.external_data_sources ds
LEFT JOIN
    sys.database_scoped_credentials cr ON ds.credential_id = cr.credential_id
LEFT JOIN
    sys.external_tables t ON ds.data_source_id = t.data_source_id
LEFT JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
    sys.objects o ON t.object_id = o.object_id;