Share via

Aan de slag met PolyBase in SQL Server 2022

Van toepassing op: SQL Server 2016 (13.x) en latere versies op Windows SQL Server 2017 (14.x) en latere versies op Linux

In dit artikel wordt u begeleid bij het werken met meerdere mappen en bestanden met PolyBase in SQL Server 2022 (16.x). In deze reeks zelfstudiequery's worden verschillende functies van PolyBase gedemonstreerd.

Met gegevensvirtualisatie met PolyBase in SQL Server kunt u gebruikmaken van functies voor metagegevensbestanden om query's uit te voeren op meerdere mappen, bestanden of het verwijderen van mappen uit te voeren. De combinatie van schemadetectie met map- en bestandsverwijdering is een krachtige mogelijkheid waarmee SQL alleen de vereiste gegevens kan ophalen uit een Azure Storage-account of S3-compatibele oplossing voor objectopslag.

Vereiste voorwaarden

Voordat u PolyBase in deze zelfstudie gebruikt, moet u het volgende doen:

  1. Installeer PolyBase op Windows of installeer PolyBase op Linux.
  2. Schakel PolyBase in sp_configure indien nodig.
  3. Externe netwerktoegang toestaan voor toegang tot openbaar beschikbare Azure Blob-opslag op pandemicdatalake.blob.core.windows.net en azureopendatastorage.blob.core.windows.net.

Voorbeeldgegevenssets

Als u niet bekend bent met gegevensvirtualisatie en u snel functionaliteit wilt testen, begint u met het opvragen van openbare gegevenssets die beschikbaar zijn in Azure Open Datasets, zoals de Bing COVID-19-gegevensset die anonieme toegang toestaat.

Gebruik de volgende eindpunten om een query uit te voeren op de Bing COVID-19-gegevenssets:

  • Parket: 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

Voer deze eenvoudige T-SQL-query uit om eerst inzicht te krijgen in de gegevensset. Deze query maakt gebruik van OPENROWSET om een query uit te voeren op een bestand dat is opgeslagen in een openbaar beschikbaar opslagaccount:

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;

U kunt doorgaan met verkennen van gegevenssets door andere T-SQL-componenten toe te WHEREGROUP BY voegen op basis van de resultatenset van de eerste query.

Als de eerste query op uw SQL Server-exemplaar mislukt, wordt netwerktoegang waarschijnlijk voorkomen voor het openbare Azure-opslagaccount. Neem contact op met uw netwerkexpert om toegang in te schakelen voordat u kunt doorgaan met het uitvoeren van query's.

Zodra u bekend bent met het uitvoeren van query's op openbare gegevenssets, kunt u overschakelen naar niet-openbare gegevenssets waarvoor referenties zijn vereist, toegangsrechten verlenen en firewallregels configureren. In veel praktijkscenario's werkt u voornamelijk met privégegevenssets.

Externe gegevensbron

Een externe gegevensbron is een abstractie waarmee u eenvoudig kunt verwijzen naar een bestandslocatie in meerdere query's. Als u een query wilt uitvoeren op openbare locaties, moet u alleen opgeven wanneer u een externe gegevensbron maakt, de bestandslocatie:

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

Opmerking

Als u een foutbericht 46530 ontvangt, External data sources are not supported with type GENERIC, controleert u de configuratieoptie PolyBase Enabled in uw SQL Server-exemplaar. Dit moet 1 zijn.

Voer het volgende uit om PolyBase in te schakelen in uw SQL Server-exemplaar:

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

Wanneer u niet-openbare opslagaccounts opent, samen met de locatie, moet u ook verwijzen naar een databasereferentie met ingekapselde verificatieparameters. Met het volgende script wordt een externe gegevensbron gemaakt die verwijst naar het bestandspad en verwijst naar een referentie binnen het databasebereik.

--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]);

Query's uitvoeren op gegevensbronnen met BEHULP van OPENROWSET

De OPENROWSET-syntaxis maakt direct ad-hocquery's mogelijk terwijl alleen het minimale aantal databaseobjecten wordt gemaakt dat nodig is.

OPENROWSET vereist alleen het maken van de externe gegevensbron (en mogelijk de referentie) in plaats van de externe tabelbenadering, waarvoor een externe bestandsindeling en de externe tabel zelf zijn vereist.

De DATA_SOURCE parameterwaarde wordt automatisch voorafgegaan door de PARAMETER BULK om het volledige pad naar het bestand te vormen.

Wanneer u OPENROWSET de indeling van het bestand opgeeft, zoals het volgende voorbeeld, waarmee een query wordt uitgevoerd op één bestand:

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

Query's uitvoeren op meerdere bestanden en mappen

Met de OPENROWSET opdracht kunt u ook query's uitvoeren op meerdere bestanden of mappen met behulp van jokertekens in het PAD BULK.

In het volgende voorbeeld wordt gebruikgemaakt van de open data set van gele taxi ritten in NYC:

Maak eerst de externe gegevensbron:

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

Nu kunnen we alle bestanden met .parquet-extensies in mappen raadplegen. Hier voeren we bijvoorbeeld alleen query's uit op die bestanden die overeenkomen met een naampatroon:

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

Bij het uitvoeren van query's op meerdere bestanden of mappen, moeten alle bestanden die met de ene OPENROWSET zijn geopend, dezelfde structuur hebben (zoals hetzelfde aantal kolommen en gegevenstypen). Mappen kunnen niet recursief worden doorkruist.

Schema-afleiding

Met automatische schemadeductie kunt u snel query's schrijven en gegevens verkennen wanneer u geen bestandsschema's kent. Schemadeductie werkt alleen met Parquet-bestanden.

Hoewel dit handig is, kunnen uitgestelde gegevenstypen groter zijn dan de werkelijke gegevenstypen, omdat er mogelijk voldoende informatie in de bronbestanden is om ervoor te zorgen dat het juiste gegevenstype wordt gebruikt. Dit kan leiden tot slechte queryprestaties. Parquet-bestanden bevatten bijvoorbeeld geen metagegevens over de maximale lengte van tekenkolommen, zodat het exemplaar deze afgeeft als varchar(8000).

Gebruik de sys.sp_describe_first_results_set opgeslagen procedure om de resulterende gegevenstypen van uw query te controleren, zoals in het volgende voorbeeld:

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

Zodra u de gegevenstypen kent, kunt u deze specificeren met de WITH clausule om de prestaties te verbeteren.

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;

Omdat het schema van CSV-bestanden niet automatisch kan worden bepaald, moeten kolommen altijd worden opgegeven met behulp van de WITH component:

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;

Bestandsmetagegevensfuncties

Wanneer u query's uitvoert op meerdere bestanden of mappen, kunt u de functies filepath() en filename() gebruiken om bestandmetagegevens te lezen en een deel van het pad of het volledige pad en de naam van het bestand waaruit de rij in de resultaatset afkomstig is op te halen. In het volgende voorbeeld voert u een query uit op alle bestanden en projectbestandspaden en bestandsnaaminformatie voor elke rij:

--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;

  • Wanneer de functie zonder parameter wordt aangeroepen, retourneert de filepath() functie het bestandspad waaruit de rij afkomstig is. Wanneer DATA_SOURCE wordt gebruikt in OPENROWSET, retourneert het het pad ten opzichte van de DATA_SOURCE, anders retourneert het volledige bestandspad.

  • Wanneer deze wordt aangeroepen met een parameter, retourneert de filepath() functie een deel van het pad dat overeenkomt met het jokerteken op de positie die is opgegeven in de parameter. De eerste parameterwaarde retourneert bijvoorbeeld een deel van het pad dat overeenkomt met het eerste jokerteken.

De filepath() functie kan ook worden gebruikt voor het filteren en samenvoegen van rijen:

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;

Weergave maken boven op OPENROWSET

U kunt weergaven maken om query's in te pakken OPENROWSET , zodat u de onderliggende query eenvoudig opnieuw kunt gebruiken. Weergaven maken ook rapportage- en analysehulpprogramma's zoals Power BI mogelijk om resultaten van OPENROWSET te gebruiken.

Denk bijvoorbeeld aan de volgende weergave op basis van een OPENROWSET opdracht:

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

Het is ook handig om kolommen met de bestandslocatiegegevens toe te voegen aan een weergave met behulp van de filepath() functie, zodat u eenvoudiger en beter kunt filteren. Het gebruik van weergaven kan het aantal bestanden verminderen en de hoeveelheid gegevens die de query die de weergave gebruikt moet lezen en verwerken, vooral wanneer deze wordt gefilterd op een van die kolommen.

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 bevatten toegang tot bestanden, waardoor de query-ervaring bijna identiek is aan het uitvoeren van query's op lokale relationele gegevens die zijn opgeslagen in gebruikerstabellen. Voor het maken van een externe tabel moeten de externe gegevensbron- en externe bestandsindelingsobjecten bestaan:

--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 
);

Zodra de externe tabel is gemaakt, kunt u er net als elke andere tabel een query op uitvoeren:

SELECT TOP 10 * 
FROM tbl_TaxiRides;

Net als BIJ OPENROWSET kunnen externe tabellen query's uitvoeren op meerdere bestanden en mappen met behulp van jokertekens. Schemadeductie wordt niet ondersteund met externe tabellen.

Externe gegevensbronnen

Zie PolyBase Transact-SQL referentie voor meer zelfstudies over het maken van externe gegevensbronnen en externe tabellen voor verschillende gegevensbronnen.

Voor meer tutorials over verschillende externe gegevensbronnen, bekijk:

Verwante inhoud

  • Last updated on