A PolyBase használatának első lépései az SQL Server 2022-ben

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók Windows SQL Server 2017-en (14.x) és újabb verziók Linuxon

Ez a cikk végigvezet az SQL Server 2022 (16.x) PolyBase használatával folytatott több mappával és fájllal történő munka oktatóanyagán. Ez az oktatóanyag-lekérdezések a PolyBase különböző funkcióit mutatják be.

Az SQL Server PolyBase-beli adatvirtualizálása lehetővé teszi a metaadat-fájlfüggvények kihasználását több mappa, fájl lekérdezéséhez vagy a mappák eltávolításához. A sémafelderítés és a mappa- és fájleliszorítás kombinációja egy hatékony képesség, amely lehetővé teszi, hogy az SQL csak a szükséges adatokat kérje le bármely Azure Storage-fiókból vagy S3-kompatibilis objektumtárolási megoldásból.

Előfeltételek

Az oktatóanyagban a PolyBase használata előtt a következőket kell tennie:

1. Telepítse a PolyBase-t Windows rendszeren , vagy telepítse a PolyBase-et Linuxon.
2. Szükség esetén engedélyezze a PolyBase-t sp_configure .
3. Külső hálózati hozzáférés engedélyezése a nyilvánosan elérhető Azure Blob Storage eléréséhez a következő helyen pandemicdatalake.blob.core.windows.net : és azureopendatastorage.blob.core.windows.net.

Mintaadatkészletek

Ha még nem ismerkedik az adatvirtualizálással, és szeretné gyorsan tesztelni a funkciókat, kezdje az Azure Open Datasetsben elérhető nyilvános adatkészletek lekérdezésével, például a névtelen hozzáférést lehetővé tévő Bing COVID-19-adatkészlettel.

A Bing COVID-19-adatkészletek lekérdezéséhez használja a következő végpontokat:

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

Első lépésként futtassa ezt az egyszerű T-SQL-lekérdezést az adatkészlet első elemzéséhez. Ez a lekérdezés OPENROWSET használatával kérdez le egy nyilvánosan elérhető tárfiókban tárolt fájlt:

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;

Az adatkészletek feltárását az első lekérdezés eredményhalmaza alapján hozzáfűzve WHEREGROUP BY és más T-SQL-záradékokkal folytathatja.

Ha az első lekérdezés meghiúsul az SQL Server-példányon, a hálózati hozzáférés valószínűleg nem lesz elérhető a nyilvános Azure-tárfiókhoz. Mielőtt folytatná a lekérdezést, forduljon a hálózatkezelési szakértőhöz a hozzáférés engedélyezéséhez.

Miután megismerkedett a nyilvános adatkészletek lekérdezésével, érdemes lehet olyan nem nyilvános adatkészletekre váltani, amelyek hitelesítő adatok megadását, hozzáférési jogosultságok megadását és tűzfalszabályok konfigurálását igénylik. Számos valós forgatókönyvben elsősorban magánadatkészletekkel fog működni.

Külső adatforrás

A külső adatforrás egy absztrakció, amely lehetővé teszi a fájlhely egyszerű hivatkozását több lekérdezésre. A nyilvános helyek lekérdezéséhez a külső adatforrás létrehozásakor csak a fájl helyét kell megadnia:

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

Megjegyzés:

Ha a 46530-at jelző hibaüzenet jelenik meg, External data sources are not supported with type GENERIC, ellenőrizze az SQL Server-példány konfigurációs beállítását PolyBase Enabled . Ennek kell lennie 1.

Futtassa az alábbiakat a PolyBase engedélyezéséhez az SQL Server-példányban:

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

A nem nyilvános tárfiókok elérésekor és hivatkozásakor hivatkoznia kell egy adatbázis hatókörébe tartozó hitelesítő adatra, amely beágyazott hitelesítési paramétereket tartalmaz. A következő szkript létrehoz egy külső adatforrást, amely a fájl elérési útjára mutat, és hivatkozik egy adatbázis-hatókörű hitelesítő adatokra.

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

Adatforrások lekérdezése AZ OPENROWSET használatával

Az OPENROWSET szintaxis azonnali alkalmi lekérdezést tesz lehetővé, miközben csak a szükséges minimális számú adatbázis-objektumot hozza létre.

OPENROWSET csak a külső adatforrás (és esetleg a hitelesítő adatok) létrehozását igényli, szemben a külső tábla megközelítésével, amely külső fájlformátumot és magát a külső táblát igényli.

A DATA_SOURCE paraméter értéke automatikusan hozzáadódik a BULK paraméterhez, hogy a fájl teljes elérési útját képezze.

Ha a fájl formátumát adja meg, használja a OPENROWSET formátumot, például az alábbi példa, amely egyetlen fájlt lekérdez:

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

Több fájl és mappa lekérdezése

A OPENROWSET parancs lehetővé teszi több fájl vagy mappa lekérdezését helyettesítő karakterek használatával a BULK elérési úton.

Az alábbi példa a NYC sárga taxiút rekordjainak nyitott adatkészletét használja:

Először hozza létre a külső adatforrást:

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

Most már lekérdezhetjük a mappákban lévő .parquet kiterjesztésű összes fájlt. Itt például csak a névmintának megfelelő fájlokat kérdezzük le:

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

Több fájl vagy mappa lekérdezésekor az egyetlen OPENROWSET fájlhoz hozzáférő összes fájlnak ugyanazzal a struktúrával kell rendelkeznie (például ugyanannyi oszlopnak és adattípusnak). A mappákat nem lehet rekurzív módon bejárni.

Sémakövetkeztetés

Az automatikus sémakövetkeztetés segít a lekérdezések gyors írásában és az adatok feltárásában, ha nem ismeri a fájlsémákat. A sémakövetkeztetés csak parquet-fájlokkal működik.

Bár kényelmes, a kikövetkeztetett adattípusok nagyobbak lehetnek a tényleges adattípusoknál, mivel a forrásfájlokban elegendő információ lehet a megfelelő adattípus használatához. Ez gyenge lekérdezési teljesítményt okozhat. A parquet-fájlok például nem tartalmaznak metaadatokat a karakteroszlopok maximális hosszáról, ezért a példány varchar(8000)-ként következtet rá.

sys.sp_describe_first_results_set A tárolt eljárással ellenőrizheti a lekérdezés eredményként kapott adattípusait, például a következő példát:

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

Miután megismerte az adattípusokat, a záradék használatával megadhatja őket a WITH teljesítmény javítása érdekében:

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;

Mivel a CSV-fájlok sémája nem határozható meg automatikusan, az oszlopokat mindig a WITH záradék használatával kell megadni:

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; 

Fájl metaadat-függvényei

Több fájl vagy mappa lekérdezésekor használhatja a filepath() és filename() függvényeket a fájl metaadatainak olvasására, és hogy megtudja az értéket az elérési út vagy a teljes elérési út és a fájl neve tekintetében, amelyből az eredményhalmaz sora származik. Az alábbi példában az egyes sorok összes fájljának és projektfájljának elérési útját és fájlnevét lekérdezheti:

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

• Ha paraméter nélkül hívjuk meg, a filepath() függvény visszaadja annak a fájlnak az elérési útját, amelyből a sor származik. Amikor DATA_SOURCE a rendszer használja OPENROWSET, az a fájl elérési útját adja vissza a DATA_SOURCEfájlhoz viszonyítva, ellenkező esetben a teljes fájl elérési útját adja vissza.

• Ha paraméterrel hívjuk meg, a függvény az filepath() elérési út azon részét adja vissza, amely megfelel a paraméterben megadott helyettesítő karakternek. Az első paraméter értéke például az elérési útnak az első helyettesítő karakternek megfelelő részét adja vissza.

A filepath() függvény sorok szűrésére és összesítésére is használható:

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;

Nézet létrehozása az OPENROWSET tetején

Nézeteket hozhat létre a lekérdezések köréOPENROWSET, hogy könnyen újra felhasználhassa a mögöttes lekérdezést. A nézetek lehetővé teszik a jelentéskészítést és elemzési eszközöket, például a Power BI-t az OPENROWSET eredményeinek felhasználásához.

Vegyük például a következő nézetet egy OPENROWSET parancs alapján:

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

A funkcióval a könnyebb és hatékonyabb szűrés érdekében a fájlhelyadatokat tartalmazó oszlopokat is hozzáadhatja egy filepath() nézethez. A nézetek használata csökkentheti a fájlok számát és a nézetre épülő lekérdezés által elolvasni és feldolgozni szükséges adatok mennyiségét, amikor ezeket a oszlopokat használva szűrjük.

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; 

Külső táblák

A külső táblák a fájlokhoz való hozzáférést tartalmazzák, így a lekérdezési élmény szinte megegyezik a felhasználói táblákban tárolt helyi relációs adatok lekérdezésével. Külső tábla létrehozásához a külső adatforrás és a külső fájlformátum objektumainak létezniük kell:

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

A külső tábla létrehozása után ugyanúgy kérdezheti le, mint bármely más táblát:

SELECT TOP 10 * 
FROM tbl_TaxiRides; 

Az OPENROWSET-hez hasonlóan a külső táblák is lehetővé teszik több fájl és mappa lekérdezését helyettesítő karakterek használatával. A sémakövetkeztetés nem támogatott külső táblák esetén.

Külső adatforrások

További oktatóanyagok a külső adatforrások és külső táblák különböző adatforrásokhoz való létrehozásáról: PolyBase Transact-SQL referencia.

További oktatóanyagok a különböző külső adatforrásokról:

• Hadoop
• Azure Blob-tároló
• SQL Server
• Oracle
• Teradata
• MongoDB
• ÁLTALÁNOS ODBC-típusok
• S3-kompatibilis objektumtároló
• CSV
• Delta tábla

Kapcsolódó tartalom

• Adatvirtualizálás a PolyBase használatával az SQL Serveren
• Gyakori kérdések a PolyBase-ben
• Teljesítményre vonatkozó szempontok az SQL Server PolyBase-ben
• PolyBase-konfiguráció és biztonság a Hadoophoz