Gunther Oracle APEX und Datenbank Wiki

Getestet Oracle 19c und MS SQL Server 15 (2019)

Aufgabe: Aus einer MS SQL Datenbank soll auf eine Tabelle/View in einer Oracle 19c Datenbank zugegriffen werden.

D.h. die Verbindung muss von der MS SQL Datenbank nach ⇒ Oracle erfolgen.

In der anderen Richtung wird das Oracle Database Gateway dazu verwendet, siehe dazu mehr unter Oracle 12c RAC Real Applikation Cluster Datenbank über einen Datenbank Link mit einer MS SQL 2017 Datenbank verbinden - Oracle Database Gateway unter Oracle Linux 7 einsetzen

Lösung

Feature PolyBase des Microsoft SQL Servers verwenden.

Ablauf:

• Feature PolyBase auf dem MS SQL Server installieren und aktivieren
• Neusten Patch einspielen damit Polybase und SQL Server auch in der gleichen Version vorliegen!
• Zugang vom SQL Server System auf die Oracle Datenbank prüfen
  • Oracle Server Name, Listener Port, SID, Service Name , Oracle User + PWD ermitteln und testen das der MS SQL Server auch die Oracle Datenbank erreichen kann(FW beachten!)
• Datenbank Master Key anlegen (nur falls noch nicht erfolgt)
• Credential für die Oracle Datenbank in der MS SQL hinterlegen
• Connection Beschreibung hinterlegen
• External Table anlegen (Richtige Datentypen und Zeichensätze mappen!)

Ursprünglich für den Big Data Zugriff entwickelt bietet die Polybase Option ab der Version 19 des SQL Servers auch die Möglichkeit an relationale Datenbank System wie Oracle anzubinden.

PolyBase, ursprünglich für den Zugriff auf Big Data-Quellen wie Hadoop oder Azure Blob Storage entwickelt, wurde mit SQL Server 2019 erweitert, um auch relationale Datenbanken wie Oracle, Teradata und MongoDB anzubinden.

Wichtige Vorteile der Oracle-Anbindung mit PolyBase

• Direkter Zugriff auf Oracle-Daten, ohne dass eine separate ETL-Pipeline erforderlich ist
• Abfrage von Oracle-Tabellen mit T-SQL, als wären sie native SQL Server-Tabellen
• Leistungsoptimierung durch Push-Down-Prinzip, wodurch SQL-Operationen direkt in Oracle ausgeführt werden
• Unterstützung für verteilte Abfragen, um Daten aus mehreren Quellen (SQL Server, Oracle, Hadoop) zu kombinieren

Die Architektur von PolyBase besteht aus mehreren Komponenten, die zusammenarbeiten, um Abfragen auf externe Datenquellen zu optimieren.

Führt die T-SQL-Abfragen aus und koordiniert die Datenabfrage mit PolyBase.

Zentrale Steuerungskomponente von PolyBase. Zerlegt die SQL-Abfrage in kleinere Aufgaben und entscheidet, ob Teile der Abfrage in die externe Quelle verschoben werden (Push-Down-Optimierung). Koordiniert die Datenbewegung zwischen SQL Server und externen Systemen.

Eine definierte Verbindung zu einer externen Datenbank oder Datenquelle. Unterstützt verschiedene Quelltypen wie Oracle, Hadoop (HDFS), Azure Data Lake, MongoDB etc. und wird mit CREATE EXTERNAL DATA SOURCE in SQL Server konfiguriert.

Virtuelle Tabellen in SQL Server, die auf externe Datenquellen verweisen. Definiert mit CREATE EXTERNAL TABLE, sodass sie mit normalem T-SQL abgefragt werden können. Daten bleiben in der externen Quelle, SQL Server greift nur zur Abfragezeit darauf zu.

Verantwortlich für die Kommunikation mit externen relationalen Datenquellen (z. B. Oracle oder Teradata). Nutzt ODBC-Treiber, um SQL Server mit Oracle zu verbinden. Bewegt Daten zwischen SQL Server und der externen Quelle bei Bedarf.

Der SQL Server Query Optimizer entscheidet, ob die Abfrage an das externe System weitergeleitet wird. Falls möglich, wird die Verarbeitung direkt in Oracle, Hadoop oder der externen Quelle ausgeführt. Dies minimiert die Datenbewegung und verbessert die Performance.

1. T-SQL-Abfrage wird von SQL Server empfangen
2. PolyBase Engine überprüft die externe Tabelle und das externe System
   1. Query Optimizer entscheidet über Push-Down-Strategie
   2. Falls möglich, wird die Abfrage direkt in der externen Quelle ausgeführt.
   3. Falls nicht, werden Daten nach SQL Server verschoben und dort verarbeitet.
3. Daten werden in SQL Server integriert und das Ergebnis zurückgegeben

Installation von PolyBase über Setup des SQL Servers durchführen.

siehe ⇒ https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-installation?view=sql-server-ver15

Polybase als sys User aktiveren:

EXEC sp_configure 'polybase enabled', 1
RECONFIGURE

Wird das nicht durchgeführt, führt das beim anlegen „CREATE EXTERNAL DATA SOURCE“ zu einem „External data sources are not supported with type GENERIC“!

Am einfachsten kann eine Überprüfung der Oracle Umgebung erfolgen, wenn auf dem SQL Server mit dem Instant Client von Oracle oder SQLcl ein Connect auf die Zielumgebung durchgeführt wird.

Erst wenn diese Verbindung erfolgreich getestet ist, machen weitere Schritte überhaupt Sinn.

Wir brauchen folgende Informationen:

• Name des Servers
• Port des zugehörigen Listener
• SID der Datenbank (wird später für die External Table Beschreibung der angefragten Tabelle benötigt (SID.SCHEMA.TAB|View Name )!)
• Servicenamen für den Connect
• Zeichensatz der Datenbank

  SELECT parameter, VALUE FROM NLS_DATABASE_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET';

siehe dazu auch SQLcl - Das neue SQL*Plus in der Praxis

Der Master Key ist eine symmetrische Verschlüsselungsschlüssel in einer SQL Server-Datenbank, der für die sichere Verwaltung anderer Schlüssel und vertraulicher Daten verwendet wird. Damit wird auch später das Credential Objekt verschlüsselt. Ohne einen Master Key kann kein Credential in einer Datenbank erstellt werden. Der Schlüssel wird automatisch mit gesichert und muss im Regelfall nicht ausgelesen werden.

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'IchWerdeEsMirDiesmalAuchin2025#Aufschreiben';  

siehe https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-ver15

In der Datenbank, in der auch später die External Table angelegt werden soll wird der Oracle User und das Password des Users verschlüsselt mit dem Master Key hinterlegt.

CREATE DATABASE SCOPED CREDENTIAL OraSchnittstellenUser
WITH IDENTITY = 'HR'
    , SECRET = 'DasIstDasOraclePasswordDesUsersHR'
  ;

siehe dazu auch ⇒ https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver15

Für den Verknüpfung der Oracle Datenbank mit dem SQL Server wird einen Connection Beschreibung angelegt.

• LOCATION typ:serverhost:serverport wie 'oracle:gpidbservername:1521' - Service Name kann nicht angegeben werden!
• CONNECTION_OPTIONS = 'ServiceName=GPPSRV;Charset=UTF8' - Hier wird der Service Name und der Zeichensatz der DB angeben!
• CREDENTIAL = OraSchnittstellenUser - Unser zuvor erstelltes Credential Objekt

Befehl:

USE [SchnittstellenDB]
GO
 
CREATE EXTERNAL DATA SOURCE ORASCHNITTSTELLE_DATASOURCE
   WITH (
      LOCATION           = 'oracle://gpidbservername:1521'
   ,  CONNECTION_OPTIONS = 'ServiceName=GPPSRV;Charset=UTF8'
   ,  CREDENTIAL         = OraSchnittstellenUser
);
GO
 

Mögliche Connection Parameter siehe: https://learn.microsoft.com/de-de/sql/t-sql/statements/create-external-data-source-connection-options?view=sql-server-ver15

Übersicht siehe ⇒ https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated

Da PolyBase keine automatische Typenerkennung durchführt, müssen die Spaltentypen manuell definiert werden.

Die Schwierigkeit beim Anlegen der external Tabelle liegt dabei den richtigen Oracle zu MS SQL Datentyps und die richtigen Zeichensatz Einstellungen zu verwenden.

Wie VARCHAR2(N CHAR) (AL32UTF8) in NVARCHAR(N) usw. und den richten Spracheinstellungen wie „Latin1_General_CP1_CI_AS“ oder „Latin1_General_BIN“ etc.

Hier kann es hilfreich sein mit dem neusten Azure Data Studio das ganze über den Wizard für „external Tables “ durchzuführen, da hier doch recht viele Kombinationen möglich sind aber nur eine zum Ziel führt.

Um die exakten Datentypen aus Oracle zu erhalten, kann man folgende SQL-Abfragen verwenden:

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE  
FROM ALL_TAB_COLUMNS  
WHERE TABLE_NAME = 'V_PARTNER' AND OWNER = 'HR';

Mit den obigen Erkenntnissen kann dann die external Table erzeugt werden.

Befehl:

USE [SchnittstellenDB]
GO
 
CREATE EXTERNAL TABLE ORAS_Partner 
        (
             [ID]        DECIMAL(6,0)    NOT NULL
            ,[NAME]      NVARCHAR(60)    COLLATE Latin1_General_BIN NOT NULL
            ,[ORT]       NVARCHAR(180)   COLLATE Latin1_General_BIN NOT NULL 
            ,[VORGANG]   NVARCHAR(9)     COLLATE Latin1_General_BIN NOT NULL 
        )
        WITH (
               LOCATION    = 'GPI.HR.V_PARTNER'
            ,  DATA_SOURCE = ORASCHNITTSTELLE_DATASOURCE
        );

Auf das Mapping der Daten achten! Fehlermeldung beim Anlegen sorgfältig lesen und vorgeschlagen Zeichensatz verwenden:

Msg 105083, Level 16, State 1, Line 1
105083;The following columns in the user defined schema are incompatible with the external table schema for table ...
The detected external table schema is: ([ID] DECIMAL(6) NOT NULL
, [NAME] VARCHAR(60) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL
, [ORT] VARCHAR(180) COLLATE Latin1_General_100_BIN2_UTF8 ....
....

Statistiken spielen eine zentrale Rolle für die Query-Optimierung in SQL Server. Sie helfen dem Query Optimizer, die effizienteste Abfrageausführungsstrategie zu wählen. Da EXTERNAL TABLES in PolyBase keine lokalen Daten enthalten, sondern auf eine externe Datenquelle (z. B. Oracle, Hadoop, Azure) verweisen, benötigt SQL Server Statistiken, um bessere Abfragepläne zu erstellen.

CREATE STATISTICS ORAS_Partner_Stat  ON ORAS_Partner  ([ID], [NAME], [ORT], [VORGANG]) WITH fullscan;
GO

Leider können die Statistiken nicht für external Tables aktualisiert werden, es muss dazu ein manueller Job eingerichtet werden, der die Statistik löscht und neu anlegt.

Ein Update zu einem Fehler, das scheint in 2019 und 2022 so noch nicht zu funktionieren:

USE SchnittstellenDB;
GO
 
UPDATE STATISTICS ORAS_Partner_Stat WITH FULLSCAN;
GO
...
Message 46519, level 16, state 22
The object UPDATE Statistics isn't supported on External Table
...

aus der Doku: https://learn.microsoft.com/de-de/sql/t-sql/statements/create-statistics-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-4029181#limitations-and-restrictions

..
Das Aktualisieren von Statistiken bei externen Tabellen wird nicht unterstützt. 
Zum Aktualisieren einer Statistik müssen Sie die Statistik löschen und neu erstellen
..

siehe dazu https://medium.com/codex/how-to-maintain-sql-server-statistics-on-polybase-external-table-2eac36068120

Als Job:

USE [msdb]
GO
 
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @TYPE=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Aktualisiere Externe Tabellen', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Job zum täglichen Löschen und Neu Erstellen der Statistiken', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'GPI\ADMIN', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Lösche und Erstelle Statistiken] ***/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Lösche und Erstelle Statistiken', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=5, 
		@retry_interval=5, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'
USE [SchnittstellenDB]
GO
DROP STATISTICS [dbo].[ORAS_Partner].[ORAS_Partner_Stat]
GO
CREATE STATISTICS [ORAS_Partner_Stat] ON [dbo].[ORAS_Partner]([ID], [NAME], [ORT], [VORGANG]) with fullscan
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Täglicher Zeitplan 02:00 Uhr', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20250313, 
		@active_end_date=99991231, 
		@active_start_time=20000, 
		@active_end_time=235959, 
		@schedule_uid=N'c48686ad-30fa-43a6-9663-fa4a11c6c185'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

MS:

• https://learn.microsoft.com/de-de/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15&WT.mc_id=DP-MVP-4029181
• https://techcommunity.microsoft.com/blog/sqlserversupport/polybase-for-beginners-/1075336

Web:

• https://www.youtube.com/watch?v=l9Rc5nMuq48
• https://www.sqlshack.com/enhanced-polybase-sql-2019-external-tables-for-oracle-db/