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 allgemein als Datenvirtualisierung bezeichnet wird, ermöglicht es den Daten, an seinem ursprünglichen Speicherort zu bleiben, kann aber von einer SQL Server-Instanz mit T-SQL-Befehlen wie jeder anderen Tabelle abgefragt werden. Dieses Feature verwendet PolyBase-Connectors und minimiert die Notwendigkeit des Kopierens von Daten über ETL-Prozesse.

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

Weitere Informationen zur Datenvirtualisierung, 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 einer 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 datenbankbezogener Anmeldeinformationen

Der Datenbankmasterschlüssel in der Benutzerdatenbank ist erforderlich, um den geheimen Anmeldeinformationsbereich der Datenbank zu verschlüsseln. delta_storage_dsc 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

Anmeldeinformationen mit Datenbankbereich 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 CREATE EXTERNAL DATA SOURCE.

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 wird der Ordner "Datentabelle" benannt 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 Delta-Tabellendaten in SQL Server zu virtualisieren. Die Spalten müssen definiert und stark typiert werden. Während externe Tabellen mehr Aufwand zum Erstellen haben, bieten sie auch zusätzliche Vorteile beim Abfragen einer externen Datenquelle mit OPENROWSET. Sie können Folgendes ausführen:

  • Stärken der Definition der Datentypisierung für eine bestimmte Spalte
  • Definieren der Nullierbarkeit
  • DEFINIEREN DER SORTIERUNG
  • Erstellen von Statistiken für eine Spalte zur Optimierung der Qualität des Abfrageplans
  • Erstellen sie ein differenzierteres Modell in SQL Server, um den Datenzugriff zu verbessern, um Ihr Sicherheitsmodell zu verbessern.

Weitere Informationen finden Sie unter CREATE EXTERNAL TABLE.

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 Delta-Tabellendateien befinden sich in /delta/Delta_yob/ der externen Datenquelle für dieses Beispiel ist S3-kompatibler Objektspeicher, der zuvor unter der Datenquelle s3_edskonfiguriert wurde. PolyBase kann den Delta-Tabellenordner oder die absolute Datei selbst als LOCATION verwenden, die sich in delta/Delta_yob/_delta_log/00000000000000000000.jsonder Datei befindet.

-- 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 Delta-Tabelle zeigt, wird die für die Partitionierung verwendete Spalte beim Abfragen der externen Tabelle zurückgegeben NULL . 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, da es einen QUERYTRACEON Abfragehinweis gibt, der der Delta-Dateimetadatenabfrage hinzugefügt werden kann und für die die Ausführung der Serverrolle erforderlich ist sysadmin . Wenn dies der Fall ist, können Sie das Problem beheben, indem Sie das Ablaufverfolgungskennzeichnung 14073 global aktivieren und dadurch verhindern, dass der Abfragehinweis hinzugefügt wird.

Zugehöriger Inhalt