Virtualisieren der Delta-Tabelle mit PolyBase

  • Artikel

Gilt für: SQL Server 2022 (16.x) und höhere Versionen

SQL Server 2022 (16.x) kann Daten direkt aus einem Delta-Tabellenordner abfragen. Dieses Konzept, das gemeinhin als Datenvirtualisierung bezeichnet wird, ermöglicht es, dass die Daten am ursprünglichen Speicherort verbleiben und dennoch wie jede andere Tabelle von einer SQL Server-Instanz mit T-SQL-Befehlen abgefragt werden können. Diese Funktion verwendet PolyBase-Connectors und minimiert die Notwendigkeit, Daten über ETL-Prozesse zu kopieren.

Im folgenden Beispiel wird der Delta-Tabellenordner in Azure Blob Storage gespeichert und über OPENROWSET oder eine externe Tabelle aufgerufen.

Weitere Informationen zur Datenvirtualisierung finden Sie unter Einführung in die Datenvirtualisierung mit PolyBase.

Vorkonfiguration

1. Aktivieren von PolyBase in sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Erstellen Sie eine Benutzerdatenbank

In dieser Übung wird eine Beispieldatenbank mit Standardeinstellungen und -speicherorten erstellt. Sie verwenden diese leere Beispieldatenbank, um mit den Daten zu arbeiten und die bereichsbezogenen Anmeldeinformationen zu speichern. In diesem Beispiel wird eine neue leere Datenbank mit dem Namen Delta_demo verwendet.

CREATE DATABASE [Delta_demo];

3. Erstellen eines Hauptschlüssels und einer datenbankabhängigen Berechtigung

Der Datenbankhauptschlüssel in der Benutzerdatenbank wird benötigt, um das datenbankbezogene Berechtigungsgeheimnis delta_storage_dsc zu verschlüsseln. In diesem Beispiel befindet sich die Delta-Tabelle in Azure Data Lake Storage Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Erstellen einer externen Datenquelle

Datenbankbezogene Anmeldeinformationen werden für die externe Datenquelle verwendet. In diesem Beispiel befindet sich die Delta-Tabelle in Azure Data Lake Storage Gen2. Verwenden Sie daher das Präfix adls und die Identitätsmethode SHARED ACCESS SIGNATURE. Weitere Informationen zu den Connectors und Präfixen, einschließlich neuer Einstellungen für SQL Server 2022 (16.x), finden Sie unter EXTERNE DATENQUELLE ERSTELLEN.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Wenn Ihr Speicherkonto beispielsweise delta_lake_sample und der Container sink heißt, lautet der Code:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Verwenden von OPENROWSET für den Zugriff auf die Daten

In diesem Beispiel heißt der Datenflussordner Contoso.

Da die externe Datenquelle Delta_ED einer Containerebene zugeordnet ist. Der Contoso-Delta-Tabellenordner befindet sich in einem Stammverzeichnis. Um eine Datei in einer Ordnerstruktur abzufragen, stellen Sie eine Ordnerzuordnung relativ zum LOCATION-Parameter der externen Datenquelle bereit.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Abfragen von Daten mit einer externen Tabelle

CREATE EXTERNAL TABLE kann auch verwendet werden, um die Daten der Deltatabelle in SQL Server zu virtualisieren. Die Spalten müssen definiert und stark typisiert werden. Externe Tabellen sind zwar aufwändiger zu erstellen, bieten aber auch zusätzliche Vorteile gegenüber der Abfrage einer externen Datenquelle mit OPENROWSET. Sie können Folgendes ausführen:

  • Stärken der Definition der Datentypisierung für eine bestimmte Spalte
  • Definieren der NULL-Zulässigkeit
  • Definieren der SORTIERUNG
  • Erstellen von Statistiken für eine Spalte zur Optimierung der Qualität des Abfrageplans
  • Erstellen Sie ein detaillierteres Modell innerhalb von SQL Server für den Datenzugriff, um Ihr Sicherheitsmodell zu verbessern

Weitere Informationen finden Sie unter EXTERNE TABELLE ERSTELLEN.

Im folgenden Beispiel wird dieselbe Datenquelle verwendet.

1. Erstellen eines externen Dateiformats

Um die Formatierung der Datei zu definieren, ist ein externes Dateiformat erforderlich. Externe Dateiformate werden auch aufgrund der Wiederverwendbarkeit empfohlen. Weitere Informationen finden Sie unter CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Erstellen einer externen Tabelle

Die Deltatabellendateien befinden sich unter /delta/Delta_yob/ und die externe Datenquelle für dieses Beispiel ist ein S3-kompatibler Objektspeicher, der zuvor unter der Datenquelle s3_eds konfiguriert wurde. PolyBase kann den Delta-Tabellenordner oder die absolute Datei selbst als LOCATION verwenden, die sich unter delta/Delta_yob/_delta_log/00000000000000000000.json befinden würde.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Begrenzungen

Wenn Sie eine externe Tabelle erstellen, die auf eine partitionierte Deltatabelle verweist, gibt die für die Partitionierung verwendete Spalte bei der Abfrage der externen Tabelle NULL zurück. Wenn Sie jedoch eine OPENROWSET-Abfrage verwenden, wird der Spaltenwert korrekt zurückgegeben. Um diese Einschränkung zu umgehen, erstellen Sie eine Ansicht für die OPENROWSET-Abfrage, und fragen Sie dann die Ansicht ab, um die partitionierten Spaltenwerte korrekt zurückzugeben.

Beim Abfragen einer externen Delta-Tabelle treten möglicherweise die folgenden Fehler auf:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Dies kann passieren, weil es einen Abfragehinweis QUERYTRACEON gibt, der der Metadatenabfrage für die Delta-Datei hinzugefügt werden kann und für dessen Ausführung die Serverrolle sysadmin erforderlich ist. In diesem Fall können Sie das Problem beheben, indem Sie die Ablaufverfolgungsflag 14073 global aktivieren. Dies verhindert, dass der Abfragehinweis hinzugefügt wird.

Zugehöriger Inhalt