Access Oracle database data from SQL Server using PolyBase

USE master;

GO

-- Verify PolyBase is Installed

SELECT SERVERPROPERTY('IsPolyBaseInstalled') AS IsPolyBaseInstalled;

GO

-- Check PolyBase configuration

EXEC sp_configure 'polybase enabled';

GO

/* -- if needed:

EXEC sp_configure 'show advanced options',1;

RECONFIGURE;

GO

EXEC sp_configure 'polybase enabled',1;

RECONFIGURE;

GO

*/

USE test_db; /* USER DATABASE */

GO

-- Check whether a Database Master Key exists

SELECT *

FROM sys.symmetric_keys

WHERE name = '##MS_DatabaseMasterKey##';

GO

-- List all database scoped credentials

SELECT

    credential_id,

    name,

    credential_identity,

    create_date,

    modify_date

FROM sys.database_scoped_credentials

ORDER BY name;

GO

-- Create Database Master Key in User Database if needed

USE test_db;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<$345>FeQ2@S3PEm+87B';

GO

-- Verify

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##';

GO

-- Create Database Scoped Credential

CREATE DATABASE SCOPED CREDENTIAL OracleCred WITH IDENTITY = 'POLYBASE_READ', SECRET = '<$345>FeQ2@S3PEm+87B';

GO

-- Verify

SELECT * FROM sys.database_scoped_credentials;

GO

-- Create External Data Source

CREATE EXTERNAL DATA SOURCE OracleDS WITH (

    LOCATION = 'oracle://ORACLESERVER:1600',

    CONNECTION_OPTIONS = 'ServiceName=ORACLESERVER.domain.com',

    CREDENTIAL = OracleCred

);

GO

-- Verify

SELECT * FROM sys.external_data_sources;

GO

-- Create External Table

CREATE EXTERNAL TABLE MODELDB_GLOBAL_RMS_ISSUE

(

    RMS_ID      DECIMAL(38,0) NOT NULL,

    ISSUE_ID    CHAR(10) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL,

    FROM_DT     DATETIME2(0) NOT NULL,

    THRU_DT     DATETIME2(0) NOT NULL

)

WITH

(

    LOCATION = '[ORACLESERVER.domain.com].MODELDB_GLOBAL.RMS_ISSUE',

    DATA_SOURCE = OracleDS

);

GO

-- Verify (check Actual Execution Plan XML, see whether 'pushdown' occurred)

SELECT TOP 10 * FROM MODELDB_GLOBAL_RMS_ISSUE;

-- USAGE

select * into #modeldb_global__rms_issue from MODELDB_GLOBAL_RMS_ISSUE;

SELECT COUNT(1) FROM #modeldb_global__rms_issue;

SELECT TOP 10 * FROM #modeldb_global__rms_issue;