Začínáme s PolyBase v SQL Serveru 2022

Platí pro: SQL Server 2016 (13.x) a novější verze ve Windows SQL Serveru 2017 (14.x) a novějších verzích v Linuxu

Tento článek vás provede kurzem práce s více složkami a soubory s PolyBase v SQL Serveru 2022 (16.x). Tato sada výukových dotazů ukazuje různé funkce PolyBase.

Virtualizace dat pomocí PolyBase v SQL Serveru umožňuje využít funkce souborů metadat k dotazování více složek, souborů nebo odstranění složek. Kombinace zjišťování schématu se složkami a odstraněním souborů je výkonná funkce, která sql umožňuje načíst pouze požadovaná data z libovolného účtu služby Azure Storage nebo řešení úložiště objektů kompatibilního s S3.

Požadavky

Před použitím PolyBase v tomto kurzu musíte:

1. Nainstalujte PolyBase ve Windows nebo nainstalujte PolyBase v Linuxu.
2. V případě potřeby povolte PolyBase v sp_configure.
3. Povolit přístup z externí sítě k veřejně dostupnému úložišti objektů blob Azure na pandemicdatalake.blob.core.windows.net a azureopendatastorage.blob.core.windows.net.

Ukázkové datové sady

Pokud s virtualizací dat začínáte a chcete rychle testovat funkce, začněte dotazováním veřejných datových sad dostupných v Azure Open Datasets, jako je datová sada Bingu COVID-19, která umožňuje anonymní přístup.

K dotazování datových sad Bingu COVID-19 použijte následující koncové body:

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

Pro rychlý start spusťte tento jednoduchý dotaz T-SQL, abyste získali první přehled o datové sadě. Tento dotaz používá OPENROWSET k dotazování souboru uloženého v veřejně dostupném účtu úložiště:

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;

Zkoumání sady dat můžete pokračovat připojením WHEREGROUP BY a dalšími klauzulemi T-SQL na základě sady výsledků prvního dotazu.

Pokud první dotaz selže v instanci SQL Serveru, pravděpodobně se zabrání síťovému přístupu k veřejnému účtu úložiště Azure. Než budete moct pokračovat v dotazování, obraťte se na odborníka na sítě a povolte přístup.

Jakmile se seznámíte s dotazováním veřejných datových sad, zvažte přepnutí na nepublikované datové sady, které vyžadují zadání přihlašovacích údajů, udělení přístupových práv a konfiguraci pravidel brány firewall. V mnoha reálných scénářích budete pracovat především s privátními datovými sadami.

Externí zdroj dat

Externí zdroj dat je abstrakce, která umožňuje snadné odkazování na umístění souboru napříč několika dotazy. Pokud chcete zadat dotazy na veřejná umístění, musíte při vytváření externího zdroje dat zadat vše, co je umístěním souboru:

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

Poznámka:

Pokud se zobrazí chybová zpráva 46530, External data sources are not supported with type GENERIC, zkontrolujte možnost PolyBase Enabled konfigurace v instanci SQL Serveru. Mělo by to být 1.

Spuštěním následujícího příkazu povolte PolyBase v instanci SQL Serveru:

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

Při přístupu k nepublikovaným účtům úložiště spolu s umístěním musíte také odkazovat na přihlašovací údaje s vymezeným oborem databáze s zapouzdřenými ověřovacími parametry. Následující skript vytvoří externí zdroj dat odkazující na cestu k souboru a odkazuje na přihlašovací údaje v oboru databáze.

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

Dotazování zdrojů dat pomocí OPENROWSET

Syntaxe OPENROWSET umožňuje okamžité ad hoc dotazování při vytváření pouze minimálního počtu potřebných databázových objektů.

OPENROWSETVyžaduje pouze vytvoření externího zdroje dat (a případně přihlašovací údaje) na rozdíl od přístupu externí tabulky, který vyžaduje formát externího souboru a samotnou externí tabulku.

Hodnota DATA_SOURCE parametru se automaticky předpíná k parametru BULK, aby se vytvořila úplná cesta k souboru.

Při použití OPENROWSET zadejte formát souboru, například v následujícím příkladu, který se dotazuje na jeden soubor:

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

Dotazování na více souborů a složek

Příkaz OPENROWSET také umožňuje dotazování více souborů nebo složek pomocí zástupných znaků v cestě BULK.

V následujícím příkladu se používá otevřená datová sada záznamů jízdy žlutými taxíky v NYC.

Nejprve vytvořte externí zdroj dat:

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

Teď můžeme dotázat všechny soubory s příponou .parquet ve složkách. Tady například budeme dotazovat pouze ty soubory, které odpovídají vzoru názvu:

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

Při dotazování více souborů nebo složek musí mít všechny soubory, ke kterým se přistupuje pomocí jediné OPENROWSET struktury (například stejný počet sloupců a datových typů). Složky se nedají rekurzivně přecházet.

Odvozování schémat

Automatické odvozování schématu vám pomůže rychle psát dotazy a zkoumat data, když neznáte schémata souborů. Odvození schématu funguje jenom se soubory parquet.

I když je to praktické, odvozené datové typy můžou být větší než skutečné datové typy, protože ve zdrojových souborech může být dostatek informací, aby se zajistilo použití příslušného datového typu. To může vést k nízkému výkonu dotazů. Například soubory parquet neobsahují metadata o maximální délce sloupce znaků, takže instance ji odvodí jako varchar(8000).

sys.sp_describe_first_results_set Pomocí uložené procedury zkontrolujte výsledné datové typy dotazu, například následující příklad:

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

Jakmile znáte datové typy, můžete je WITH pomocí klauzule určit, abyste zlepšili výkon:

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;

Vzhledem k tomu, že schéma souborů CSV nelze automaticky určit, musí být sloupce vždy zadány WITH pomocí klauzule:

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; 

Funkce metadat souborů

Při dotazování na více souborů nebo složek můžete použít filepath() a filename() funkce ke čtení metadat souborů a získání části cesty nebo úplné cesty a názvu souboru, ze kterého pochází řádek v sadě výsledků. V následujícím příkladu zadejte dotaz na všechny soubory a cestu k souboru projektu a informace o názvu souboru pro každý řádek:

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

• Při zavolání bez parametru filepath() vrátí funkce cestu k souboru, ze které řádek pochází. Při DATA_SOURCE použití vrátí OPENROWSETcestu vzhledem k objektu DATA_SOURCE, jinak vrátí úplnou cestu k souboru.

• Při zavolání pomocí parametru filepath() vrátí funkce část cesty, která odpovídá zástupné sadě na pozici zadané v parametru. Například první hodnota parametru by vrátila tu část cesty, která odpovídá prvnímu zástupnému znaku.

Funkci filepath() lze také použít k filtrování a agregaci řádků:

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;

Vytvoření zobrazení nad OPENROWSET

Můžete vytvořit zobrazení pro zabalení OPENROWSET dotazů, abyste mohli snadno použít základní dotaz. Zobrazení také umožňují nástrojům pro vytváření sestav a analýzu, jako je Power BI, využívat výsledky OPENROWSET.

Představte si například následující zobrazení založené na příkazu OPENROWSET.

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

Je také vhodné přidat sloupce s daty o umístění souboru do zobrazení pomocí filepath() funkce pro snadnější a výkonnější filtrování. Použití zobrazení může snížit počet souborů a množství dat, které dotaz nad zobrazením potřebuje ke čtení a zpracování při filtrování podle některého z těchto sloupců:

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; 

Externí tabulky

Externí tabulky zapouzdřují přístup k souborům, takže dotazování je téměř stejné jako dotazování místních relačních dat uložených v uživatelských tabulkách. Vytvoření externí tabulky vyžaduje, aby existovaly objekty externího zdroje dat a formátu externího souboru:

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

Po vytvoření externí tabulky ji můžete dotazovat stejně jako jakoukoli jinou tabulku:

SELECT TOP 10 * 
FROM tbl_TaxiRides; 

Stejně jako OPENROWSET umožňují externí tabulky dotazování více souborů a složek pomocí zástupných znaků. Externí tabulky nepodporují odvození schématu.

Externí zdroje dat

Další kurzy týkající se vytváření externích zdrojů dat a externích tabulek pro různé zdroje dat najdete v tématu PolyBase Transact-SQL referenční informace.

Další kurzy o různých externích zdrojích dat najdete v následujících kurzech:

• Hadoop
• Azure Blob Storage
• SQL Server
• Oracle
• Teradata
• MongoDB
• Obecné typy ODBC
• Úložiště objektů kompatibilní s S3
• Soubor CSV
• Tabulka Delta

Související obsah

• virtualizace dat pomocí PolyBase v SQL Serveru
• Nejčastější dotazy k PolyBase
• Aspekty výkonu v PolyBase pro SQL Server
• Konfigurace a zabezpečení PolyBase pro Hadoop