Erste Schritte mit PolyBase in SQL Server 2022

  • Artikel

Gilt für: SQL Server 2016 (13.x) – Windows und höhere Versionen VON 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. Installieren Sie PolyBase unter Windows , oder installieren Sie PolyBase unter Linux.
  2. Aktivieren Sie PolyBase bei Bedarf in sp_configure .
  3. Zugriff auf externe Netzwerke für den Zugriff auf öffentlich verfügbaren Azure Blob Storage unter pandemicdatalake.blob.core.windows.net und azureopendatastorage.blob.core.windows.net.

Beispieldatensätze

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, GROUP BY indem Sie diese anfügen und andere T-SQL-Klauseln basierend auf dem Resultset der ersten Abfrage anfügenWHERE.

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 Parkettdateien keine Metadaten über die maximale Zeichenspaltenlänge, sodass die Instanz sie als varchar(8000) ableiten lässt.

Verwenden Sie die sys.sp_describe_first_results_set gespeicherte Prozedur, um die resultierenden Datentypen Ihrer Abfrage zu überprüfen, z. B. das folgende Beispiel:

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

Beim Abfragen mehrerer Dateien oder Ordner können Sie Dateimetadaten lesen filepath() und filename() Einen Teil des Pfads oder vollständigen Pfads und Namens der Datei abrufen, von 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.

  • Wenn sie mit einem Parameter aufgerufen wird, gibt die filepath() Funktion einen Teil des Pfads zurück, der mit dem Platz übereinstimmt Karte an der im Parameter angegebenen Position. Der erste Parameterwert würde z. B. einen Teil des Pfads zurückgeben, der dem ersten Wild entspricht Karte.

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 zum Umschließen OPENROWSET von Abfragen erstellen, damit Sie die zugrunde liegende Abfrage problemlos wiederverwenden können. Ansichten ermöglichen außerdem Berichterstellungs- und Analysetools wie Power BI, um Ergebnisse von OPENROWSET zu 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, sodass die Abfrageumgebung fast identisch mit dem Abfragen 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 das Abfragen mehrerer Dateien und Ordner mithilfe von Wild Karte s. 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