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

 

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

1. setup ODBC MariaDB
 

2. Buat Credential untuk Login

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

3. Buat External Data Source MariaDB

CREATE EXTERNAL DATA SOURCE MariadbSource
WITH (
    LOCATION = 'odbc://10.10.10.7:3306',
    CONNECTION_OPTIONS = 'Driver={MariaDB ODBC 3.2 Driver};Server=10.10.10.7;Database=erpdb;',
    CREDENTIAL = MariadbCred
);

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

CREATE EXTERNAL TABLE MariadbCustomers (
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),   -- Hapus UNIQUE constraint di sini
    phone VARCHAR(15)
)
WITH (
    LOCATION = 'customers',  -- Nama tabel di MariaDB
    DATA_SOURCE = MariadbSource
);

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].[MariadbCustomers]

-- MariaDB
select *,@@hostname,@@version 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;

    SELECT *  
FROM sys.external_data_sources;
 

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;