Erste Schritte mit PolyBase in SQL Server 2022

  • Artikel

Gilt für: SQL Server 2016 (13.x) Windows und höhere Versionen SQL Server 2017 (14.x) – Linux und höhere Versionen

Dieser Artikel führt Sie durch ein Lernprogramm zum Arbeiten mit mehreren Ordnern und Dateien mit PolyBase in SQL Server 2022 (16.x). Diese Reihe von Lernprogrammabfragen veranschaulicht verschiedene Features von PolyBase.

Die Datenvirtualisierung mit PolyBase in SQL Server ermöglicht es Ihnen, Metadatendateifunktionen zu nutzen, um mehrere Ordner, Dateien abzufragen oder Ordner zu beseitigen. Die Kombination von Schemaermittlung mit Ordner- und Dateilöschung ist eine leistungsstarke Funktion, mit der SQL nur die erforderlichen Daten aus einer beliebigen Azure Storage Account- oder S3-kompatiblen Objektspeicherlösung abrufen kann.

Voraussetzungen

Bevor Sie PolyBase in diesem Lernprogramm verwenden, müssen Sie:

  1. PolyBase unter Windows installieren oder PolyBase unter Linux installieren.
  2. PolyBase in sp_configure aktivieren, falls erforderlich.
  3. Erlauben Sie den externen Netzwerkzugriff auf den öffentlich verfügbaren Blob Storage von Azure bei pandemicdatalake.blob.core.windows.net und azureopendatastorage.blob.core.windows.net.

Beispieldatasets

Wenn Sie sich bisher noch nicht mit der Datenvirtualisierung beschäftigt haben und die Funktionalität schnell testen möchten, beginnen Sie mit der Abfrage öffentlicher Datasets, die in Azure Open Datasets zur Verfügung stehen. Dazu gehört beispielsweise das „Bing COVID-19“-Dataset, auf das anonym zugegriffen werden kann.

Verwenden Sie die folgenden Endpunkte, um die Bing COVID-19-Datasets abzufragen:

  • Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Führen Sie für einen Schnellstart diese einfache T-SQL-Abfrage aus, um erste Einblicke in den Datensatz zu erhalten. Diese Abfrage verwendet OPENROWSET, um eine Datei abzufragen, die in einem öffentlich verfügbaren Speicherkonto gespeichert ist:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet', 
 FORMAT = 'parquet' 
) AS filerows;

Sie können die Datensatzsuche fortsetzen, indem Sie basierend auf dem Ergebnissatz der ersten Abfrage WHERE, GROUP BY und andere T-SQL-Klauseln anfügen.

Wenn die erste Abfrage in Ihrer SQL Server-Instanz fehlschlägt, wird der Netzwerkzugriff wahrscheinlich auf das öffentliche Azure-Speicherkonto verhindert. Sprechen Sie mit Ihrem Netzwerkexperten, um den Zugriff zu ermöglichen, bevor Sie mit der Abfrage fortfahren können.

Wenn Sie mit dem Abfragen öffentlicher Datensätze vertraut sind, sollten Sie mit nicht öffentlichen Datensätzen fortfahren, die Anmeldeinformationen, Zugriffsrechte und das Konfigurieren von Firewallregeln benötigen. In vielen realen Szenarios arbeiten Sie in erster Linie mit privaten Datensätzen.

Externe Datenquelle

Bei einer externen Datenquelle handelt es sich um eine Abstraktion, die eine einfache Referenzierung eines Dateispeicherorts über mehrere Abfragen ermöglicht. Zum Abfragen öffentlicher Speicherorte müssen Sie beim Erstellen einer externen Datenquelle nur den Dateispeicherort angeben:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

Hinweis

Wenn Sie eine Fehlermeldung 46530 erhalten, External data sources are not supported with type GENERIC, überprüfen Sie die Konfigurationsoption PolyBase Enabled in Ihrer SQL Server-Instanz. Diese sollte 1 lauten.

Führen Sie Folgendes aus, um PolyBase in Ihrer SQL Server-Instanz zu aktivieren:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

Wenn Sie auf nicht öffentliche Speicherkonten zugreifen, müssen Sie sowohl auf den Speicherort als auch auf datenbankbezogene Anmeldeinformationen mit gekapselten Authentifizierungsparametern verweisen. Das folgende Skript erstellt eine externe Datenquelle, die auf den Dateipfad zeigt und verweist auf eine datenbankbezogene Anmeldeinformationen.

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

Abfragen von Datenquellen mit OPENROWSET

Die OPENROWSET-Syntax ermöglicht sofortige Ad-hoc-Abfragen, wobei nur eine minimale Anzahl von Datenbankobjekten erstellt werden muss.

OPENROWSET erfordert lediglich die Erstellung der externen Datenquelle (und möglicherweise der Anmeldeinformationen), wohingegen beim Ansatz mit externen Tabellen ein externes Dateiformat und die externe Tabelle selbst benötigt werden.

Der Wert des Parameters DATA_SOURCE wird automatisch dem Parameter BULK vorangestellt, um den vollständigen Pfad zur Datei zu bilden.

Geben Sie bei Verwendung von OPENROWSET das Format der Datei an, wie im folgenden Beispiel, das eine einzelne Datei abfragt:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Abfragen mehrerer Dateien und Ordner

Mit dem Befehl OPENROWSET können auch mehrere Dateien oder Ordner abgefragt werden, indem Platzhalter im BULK-Pfad verwendet werden.

Das folgende Beispiel verwendet das offene Dataset mit NYC Yellow Taxi-Fahrtenaufzeichnungen:

Erstellen Sie zunächst die externe Datenquelle:

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Jetzt können wir alle Dateien mit der Erweiterung .parquet in Ordnern abfragen. Hier werden beispielsweise nur die Dateien abfragt, die einem Namensmuster entsprechen:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Bei der Abfrage mehrerer Dateien oder Ordner müssen alle Dateien, auf die mit einem einzelnen OPENROWSET zugegriffen wird, die gleiche Struktur aufweisen (z. B. die gleiche Anzahl von Spalten und Datentypen). Ordner können nicht rekursiv durchlaufen werden.

Schemarückschluss

Mithilfe des automatischen Schemarückschlusses können Sie auch ohne Kenntnis des Dateischemas schnell Abfragen schreiben und Daten untersuchen. Der Schemarückschluss funktioniert nur mit Parquet-Dateien.

Die abgeleiteten Datentypen sind zwar praktisch, können aber größer sein als die tatsächlichen Datentypen, da in den Quelldateien möglicherweise genügend Informationen vorhanden sind, um sicherzustellen, dass der richtige Datentyp verwendet wird. Dies kann die Abfrageleistung beeinträchtigen. Beispielsweise enthalten Parquet-Dateien keine Metadaten zur maximalen Länge von Zeichenspalten, deshalb leitet die Instanz diese als varchar(8000) ab.

Verwenden Sie die gespeicherte Prozedur sys.sp_describe_first_results_set, um die resultierenden Datentypen Ihrer Abfrage zu überprüfen, wie im folgenden Beispiel gezeigt:

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc';

Sobald Sie die Datentypen kennen, können Sie sie mithilfe der WITH-Klausel angeben, um die Leistung zu verbessern:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

Da das Schema von CSV-Dateien nicht automatisch bestimmt werden kann, müssen Spalten immer mit der WITH-Klausel angegeben werden:

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows;

Datei-Metadatenfunktionen

Bei der Abfrage mehrerer Dateien oder Ordner können Sie mit den Funktionen filepath() und filename() Dateimetadaten lesen und einen Teil oder den vollständigen Pfad und Namen der Datei abrufen, aus der die Zeile im Resultset stammt. Im folgenden Beispiel fragen Sie alle Dateien und Projektdateipfade und Dateinameninformationen für jede Zeile ab:

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows;

  • Beim Aufruf ohne Parameter gibt die Funktion filepath() den Dateipfad zurück, aus dem die Zeile stammt. Bei Verwendung von DATA_SOURCE in OPENROWSET wird der Pfad relativ zu DATA_SOURCE zurückgegeben, andernfalls der vollständige Dateipfad.

  • Wird die Funktion filepath() mit einem Parameter aufgerufen, gibt sie den Teil des Pfades zurück, der mit dem Platzhalter an der im Parameter angegebenen Position übereinstimmt. Der erste Parameterwert würde z. B. den Teil eines Pfads zurückgeben, der dem ersten Platzhalterzeichen entspricht.

Die Funktion filepath() kann auch zum Filtern und Aggregieren von Zeilen verwendet werden:

SELECT 
 r.filepath() AS filepath 
 ,r.filepath(1) AS [year] 
 ,r.filepath(2) AS [month] 
 ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
DATA_SOURCE = 'NYCTaxiExternalDataSource', 
FORMAT = 'parquet' 
 ) AS r 
WHERE 
 r.filepath(1) IN ('2017') 
 AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
 r.filepath() 
 ,r.filepath(1) 
 ,r.filepath(2) 
ORDER BY 
 filepath;

Erstellen einer Ansicht oberhalb von OPENROWSET

Sie können Ansichten erstellen und verwenden, um OPENROWSET-Abfragen zu umschließen, sodass die zugrunde liegende Abfrage problemlos wiederverwendet werden kann. Darüber hinaus können Berichts- und Analysetools wie Power BI mithilfe von Sichten die Ergebnisse von OPENROWSET nutzen.

Betrachten Sie beispielsweise die folgende Ansicht basierend auf einem OPENROWSET-Befehl:

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Es ist ebenfalls praktisch, einer Ansicht mithilfe der Funktion filepath() Spalten mit Daten zum Dateispeicherort hinzuzufügen, um eine einfachere und leistungsfähigere Filterung zu ermöglichen. Durch die Verwendung von Sichten kann die Anzahl der Dateien und die von der Abfrage oberhalb der Ansicht zu lesende und zu verarbeitende Datenmenge verringert werden, wenn nach einer dieser Spalten gefiltert wird:

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Externe Tabellen

Externe Tabellen kapseln den Zugriff auf Dateien, wodurch die Abfrage fast identisch mit der Abfrage lokaler relationaler Daten ist, die in Benutzertabellen gespeichert sind. Zum Erstellen einer externen Tabelle müssen die externe Datenquelle und die externen Dateiformatobjekte vorhanden sein:

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
CREATE EXTERNAL TABLE tbl_TaxiRides( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
);

Nachdem die externe Tabelle erstellt wurde, können Sie sie wie jede andere Tabelle abfragen:

SELECT TOP 10 * 
FROM tbl_TaxiRides;

Genau wie OPENROWSET ermöglichen externe Tabellen die Abfrage mehrerer Dateien und Ordner mithilfe von Platzhaltern. Schemainference wird bei externen Tabellen nicht unterstützt.

Externe Datenquellen

Weitere Lernprogramme zum Erstellen externer Datenquellen und externer Tabellen für eine Vielzahl von Datenquellen finden Sie unter PolyBase Transact-SQL-Referenz.

Weitere Lernprogramme zu verschiedenen externen Datenquellen finden Sie unter:

Zugehöriger Inhalt