Query join between SQL Server and Oracle tables? (aka PolyBase)
Certifications:
In the article we will see a magical way so that we can use in select queries data from external sources (without Linked Server) and more specifically from Oracle Database.
What is PolyBase
From the edition SQL Server 2016 the Microsoft she gave us her tool PolyBase so that we can with T-SQL to read data from external sources (other than SQL Server); In SQL Server 2016 this data could be Big Data which are stored in HADOOP ή Azure Blob Storage.
Now with him SQL Server 2019 we can read data located in and Oracle, Teradata, MongoDB and other sources SQL Server with the help of external tables.
It enables us to do pushdown data so that it is processed on the server where the data originates, increasing the performance of queries.
The external tables the only space they occupy in the instance of SQL Server, is the space of metadata. That is, the structure of the table we are creating.
* The differences between Linked Server and PolyBase can be read from here.
What is required
During the installation we should have chosen to install the feature as well PolyBase Query Service For External Data, if we had not done it, we should run the setup again and add it.
To make sure that the function has been activated in the instance, we also run the following query.
exec sp_configure @configname = 'polybase enabled', @configvalue = 1; RECONFIGURE;
The user who will create the SQL Server the external tables should have the following permissions:
- CREATE TABLE
- ALTER ANY SCHEMA
- ALTER ANY EXTERNAL DATA SOURCE
- ALTER ANY EXTERNAL FILE FORMAT
- CONTROL DATABASE
We'll need it too credentials her Oracle Database that have access to the tables we want.
The example
For starters we'll have to make one master key encryption if it doesn't already exist.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass123!';
We create a credential with its user information Oracle Database that we will use that will have access to these tables.
CREATE DATABASE SCOPED CREDENTIAL OracleCredential WITH IDENTITY = 'username', Secret = 'password;
Then we create it data source in the location, before the :// characters, we must define what technology the data comes from in our Oracle case, then the hostname/ip and the door of the instance. As credential we define the credential we created before.
CREATE EXTERNAL DATA SOURCE oracle_source
WITH (
LOCATION = 'oracle://oracle.dataplatform.gr:1521', --**
-- PUSHDOWN = ON | OFF,
CREDENTIAL = OracleCredential)
* PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
** Don't spam the hostname is the real one 🙂
It's time to create the external table, it should be compatible data type for each field as in source. As a location, we first define it service_name / name of the Oracle database (if we set the default) continuing with the schema and table name (note it is case sensitive). As data_source the data source that we created before.
CREATE EXTERNAL TABLE orclDEPT(
[DEPTNO] DECIMAL(2) NOT NULL,
[DNAME] VARCHAR(14) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL,
[LOC] VARCHAR(13) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL
)
WITH (
LOCATION='[orcl].SCOTT.DEPT',
DATA_SOURCE= oracle_source
);
In case any data type we don't define it correctly, it will return an error but it will return us at the end the information what data type the fields have in source.
Msg 105083, Level 16, State 1, Line 34
105083;The following columns in the user defined schema are incompatible
.
.
.
The detected external table schema is: ([DEPTNO] DECIMAL(2) NOT NULL, [DNAME] VARCHAR(14) COLLATE Latin1_General_100_BIN2_UTF8, [LOC] VARCHAR(13) COLLATE Latin1_General_100_BIN2_UTF8).
Now we are ready to read its data external table which we made:
* Attention with external tables we can only read and not update/write data .
select * from orclDEPT;
To increase its performance we can get statistics on the table key:
CREATE STATISTICS orclDEPTstats ON orclDEPT (DEPTNO) WITH FULLSCAN;
Connection of the external table with a table in SQL Server
Now let's see how easily we connect the table from Oracle to one of SQL Server.
We have created the following table in SQL Server:
create table dept_address( DEPTNO decimal(2), ADDRESS VARCHAR(15)) insert into dept_address values (10,'NOWHERE 10'), (20,'TOTHATPLACE 53'), (30,'STREET 12'), (40,'LONGWAYHOME 41') select * from dept_address
The connection of the two tables is very simply done with a join in the common field:
select od.DEPTNO,od.DNAME,od.LOC,da.ADDRESS from orclDEPT od inner join dept_address da on od.deptno = da.deptno
