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

 

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 MSSQL. Dengan PolyBase, Anda dapat menjalankan query T-SQL di SQL Server yang menggabungkan data dari sumber eksternal tersebut tanpa memindahkan data.

1. setup ODBC MSSQL

2. Buat Credential untuk Login

CREATE DATABASE SCOPED CREDENTIAL RemoteCred
WITH IDENTITY = 'admin', SECRET = 'admin';

3. Buat External Data Source mssql

CREATE EXTERNAL DATA SOURCE RemoteSqlServer
WITH (
    LOCATION = 'odbc://10.10.10.7:2433',
    CONNECTION_OPTIONS = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=10.10.10.7,2433;DATABASE=polybasedb;',
    CREDENTIAL = RemoteCred
);

4. Buat External Table untuk Akses Data Misalkan ada tabel bernama pembelian.

CREATE EXTERNAL TABLE pembelian_external (
    [KODE_PEMBELIAN] NCHAR(10) NOT NULL,
    [KODE_BARANG] NCHAR(6) NOT NULL,
    [KODE_CUSTOMER] NCHAR(6) NOT NULL,
    [TANGGAL_PEMBELIAN] DATE NULL,
    [JUMLAH_PEMBELIAN] DECIMAL(4) NULL
)
WITH (
    DATA_SOURCE = RemoteSqlServer,
    LOCATION = 'polybasedb.dbo.pembelian'
);

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;
SELECT @@servername, * FROM pembelian_external;
 

6. check result external table
-- SQL Server
SELECT @@servername, * FROM pembelian_external;

-- MSSQL destination
SELECT @@servername, *  from polybasedb.dbo.pembelian
 

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_desc 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_desc 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;