Γίνεται query join ανάμεσα σε πίνακες SQL Server και Oracle; (a.k.a PolyBase)
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Στο άρθρο θα δούμε έναν μαγικό τρόπο ώστε να μπορούμε να χρησιμοποιήσουμε σε select queries δεδομένα από εξωτερικές πηγές (χωρίς Linked Server) και πιο συγκεκριμένα από Oracle Database.
Τι είναι η PolyBase
Από την έκδοση SQL Server 2016 η Microsoft μας έδωσε το εργαλείο της PolyBase ώστε να μπορούμε με T-SQL να διαβάζουμε δεδομένα από εξωτερικές πηγές (εκτός του SQL Server). Στον SQL Server 2016 τα δεδομένα αυτά μπορούσαν να είναι Big Data που είναι αποθηκευμένα σε HADOOP ή Azure Blob Storage.
Πλέον με τον SQL Server 2019 μπορούμε να διαβάσουμε δεδομένα που βρίσκονται και σε Oracle, Teradata, MongoDB και άλλες πηγές SQL Server με την βοήθεια των external tables.
Μας παρέχει τη δυνατότητα να κάνουμε pushdown δεδομένα ώστε η επεξεργασία τους να γίνεται στον server που προέρχονται τα δεδομένα αυξάνοντας την απόδοση των queries.
Τα external tables τον μόνο χώρο που καταλαμβάνουν στο instance του SQL Server, είναι ο χώρος των metadata. Δηλαδή του structure του πίνακα που δημιουργούμε.
* Τις διαφορές μεταξύ Linked Server και PolyΒase μπορούμε να τις διαβάσουμε από εδώ.
Τι απαιτείται
Θα πρέπει κατά την εγκατάσταση να είχαμε επιλέξει να εγκατασταθεί και η λειτουργία PolyBase Query Service For External Data, αν δεν το είχαμε κάνει θα πρέπει να ξανά τρέξουμε το setup και να το προσθέσουμε.
Για να σιγουρευτούμε ότι έχει ενεργοποιηθεί η λειτουργία στο instance τρέχουμε και το παρακάτω query.
exec sp_configure @configname = 'polybase enabled', @configvalue = 1; RECONFIGURE;
Ο χρήστης που θα δημιουργεί στον SQL Server τους external tables θα πρέπει να έχει τα εξής δικαιώματα:
- CREATE TABLE
- ALTER ANY SCHEMA
- ALTER ANY EXTERNAL DATA SOURCE
- ALTER ANY EXTERNAL FILE FORMAT
- CONTROL DATABASE
Θα χρειαστούμε επίσεις credentials της Oracle Database που έχουν πρόσβαση στους πίνακες που θέλουμε.
Το παράδειγμα
Για αρχή θα πρέπει να φτιάξουμε ένα master key encryption αν δεν υπάρχει ήδη.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass123!';
Φτιάχνουμε ένα credential με τα στοιχεία του χρήστη της Oracle Database που θα χρησιμοποιήσουμε που θα έχει πρόσβαση στους πίνακες αυτούς.
CREATE DATABASE SCOPED CREDENTIAL OracleCredential WITH IDENTITY = 'username', Secret = 'password;
Έπειτα δημιουργούμε το data source στο location πρέπει πριν τους χαρακτήρες :// να ορίσουμε από τι τεχνολογία προέρχονται τα δεδομένα στην περίπτωσή μας Oracle, έπειτα το hostname/ip και την πόρτα του instance. Ως credential ορίζουμε το credential που δημιουργήσαμε πριν.
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.
** Μην σπαμάρετε το hostname είναι το πραγματικό 🙂
Είναι η ώρα να δημιουργήσουμε των external table, θα πρέπει να έχει συμβατά data type για το κάθε πεδίο όπως είναι στο source. Σαν location ορίζουμε πρώτα το service_name / όνομα της βάσης Oracle (αν ορίσουμε το default) συνεχίζοντας με το schema και το όνομα του πίνακα (προσοχή είναι case sensitive). Ως data_source το data source που δημιουργήσαμε πριν.
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
);
Σε περίπτωση που κάποιο data type δεν το ορίσουμε σωστά, θα επιστρέψει error αλλά θας μας επιστρέψει στο τέλος την πληροφορία τι data type έχουν τα πεδία στο 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).
Πλέον είμαστε έτοιμοι να διαβάσουμε τα δεδομένα του external table που φτιάξαμε:
* Προσοχή με τους external table μπορούμε να κάνουμε μόνο read και όχι update/write δεδομένων .
select * from orclDEPT;
Για να αυξήσουμε την απόδοση του μπορούμε να πάρουμε στατιστικά στο κλειδί του πίνακα:
CREATE STATISTICS orclDEPTstats ON orclDEPT (DEPTNO) WITH FULLSCAN;
Σύνδεση του external table με ένα πίνακα στον SQL Server
Πάμε τώρα πόσο εύκολα συνδέουμε τον πίνακα από την Oracle με έναν του SQL Server.
Έχουμε φτιάξει τον παρακάτω πίνακα στον 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
Η σύνδεση των δύο πινάκων πολύ απλα γίνεται με ένα join στο κοινό πεδίο:
select od.DEPTNO,od.DNAME,od.LOC,da.ADDRESS from orclDEPT od inner join dept_address da on od.deptno = da.deptno
