Sdílet prostřednictvím

Virtualizace dat pomocí Azure SQL Database (Preview)

Týká se:Azure SQL Database

Funkce virtualizace dat ve službě Azure SQL Database umožňuje spouštět dotazy Transact-SQL (T-SQL) na soubory, které ukládají data v běžných datových formátech, jako je CSV (bez nutnosti používat text s oddělovači), Parquet a Delta (1.0). Tato data můžete dotazovat ve službě Azure Data Lake Storage Gen2 nebo Azure Blob Storage a kombinovat je s místně uloženými relačními daty pomocí spojení. Díky tomu můžete transparentně přistupovat k externím datům (v režimu jen pro čtení) a přitom je udržovat v původním formátu a umístění – označované také jako virtualizace dat.

Přehled

Virtualizace dat poskytuje dva způsoby dotazování souborů určených pro různé sady scénářů:

  • Syntaxe OPENROWSET – optimalizovaná pro ad hoc dotazování souborů. Obvykle se používá k rychlému prozkoumání obsahu a struktury nové sady souborů.
  • SYNTAXE CREATE EXTERNAL TABLE – optimalizovaná pro opakované dotazování souborů pomocí stejné syntaxe, jako kdyby byla data uložená místně v databázi. Externí tabulky vyžadují v porovnání se syntaxí OPENROWSET několik kroků přípravy, ale umožňují větší kontrolu nad přístupem k datům. Externí tabulky se obvykle používají pro analytické úlohy a vytváření sestav.

V obou případech musí být externí zdroj dat vytvořen pomocí syntaxe CREATE EXTERNAL DATA SOURCE T-SQL, jak je znázorněno v tomto článku.

Formáty souborů

Formáty souborů Parquet a text s oddělovači (CSV) jsou podporovány přímo. Formát souboru JSON je nepřímo podporován zadáním formátu SOUBORU CSV, ve kterém dotazy vrací každý dokument jako samostatný řádek. Řádky můžete dále analyzovat pomocí JSON_VALUE a OPENJSON.

Typy úložiště

Soubory se dají ukládat ve službě Azure Data Lake Storage Gen2 nebo Azure Blob Storage. K dotazování souborů potřebujete zadat umístění v určitém formátu a použít předponu typu umístění odpovídající typu externího zdroje a koncového bodu/protokolu, například následující příklady:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

Důležité

Vždy používejte předpony specifické pro koncový bod. Zadanou předponu typu umístění se používá k výběru optimálního protokolu pro komunikaci a k využití všech pokročilých funkcí nabízených konkrétním typem úložiště.

https:// Obecná předpona je podporována pouze pro BULK INSERT, ale ne pro jiné případy použití včetně OPENROWSET nebo EXTERNAL TABLE.

Začínáme

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ě:

--Quick query on a file stored in a publicly available storage account:
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 na základě sady výsledků prvního dotazu.

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.

Přístup k nepublikickým účtům úložiště

Uživatel, který je přihlášený ke službě Azure SQL Database, musí mít oprávnění pro přístup k souborům uloženým v nepřístupných účtech úložiště a dotazování na soubory. Postup autorizace závisí na tom, jak Azure SQL Database ověřuje úložiště. Typy ověřování a všechny související parametry nejsou k dispozici přímo s každým dotazem. Zapouzdřují se v objektu přihlašovacích údajů s oborem databáze uloženém v uživatelské databázi. Přihlašovací údaje používá databáze k přístupu k účtu úložiště při provádění dotazu.

Azure SQL Database podporuje následující typy ověřování:

  • Sdílený přístupový podpis (SAS)
  • Spravovaná identita
  • Předávací ověřování Microsoft Entra prostřednictvím identity uživatele

Sdílený přístupový podpis (SAS) poskytuje delegovaný přístup k souborům v účtu úložiště. SAS poskytuje podrobnou kontrolu nad typem přístupu, který udělujete, včetně intervalu platnosti, udělených oprávnění a přijatelného rozsahu IP adres. Jakmile se token SAS vytvoří, nedá se odvolat ani odstranit a umožní přístup až do vypršení jeho doby platnosti.

  1. Token SAS můžete získat několika způsoby:

  2. Udělte oprávnění ke čtení a výpisu prostřednictvím sdíleného přístupového podpisu pro přístup k externím datům. V současné době je virtualizace dat se službou Azure SQL Database jen pro čtení.

  3. Pokud chcete vytvořit přihlašovací údaje s vymezeným oborem databáze ve službě Azure SQL Database, musíte nejprve vytvořit hlavní klíč databáze, pokud ještě neexistuje. Hlavní klíč databáze je vyžadován v případě, že přihlašovací údaje vyžadují SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. Když se vygeneruje token SAS, obsahuje na začátku tokenu otazník (?). Pokud chcete token použít, musíte při vytváření přihlašovacích údajů odebrat otazník (?). Například:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Přístup k veřejnému úložišti prostřednictvím anonymních účtů

Pokud požadovaná datová sada umožňuje veřejný přístup (označovaný také jako anonymní přístup), nejsou vyžadovány žádné přihlašovací údaje, pokud je služba Azure Storage správně nakonfigurovaná, přečtěte si téma Konfigurace anonymního přístupu ke čtení pro kontejnery a objekty blob.

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

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://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       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á žlutá taxislužba záznamy otevřené datové sady.

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:

--Query all files with .parquet extension in folders matching name pattern:
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).

Ke kontrole výsledných datových typů dotazu použijte uloženou proceduru sp_describe_first_results_set, jako je tomu například v následujícím příkladu:

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ů:

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

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í s parametrem vrátí část cesty, která odpovídá zástupnému znaku na pozici určené v parametru. Například hodnota parametru 1 by vrátila část cesty, která odpovídá prvnímu zástupného 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

Zobrazení můžete vytvořit a použít k zabalení dotazů OPENROWSET, abyste mohli snadno použít základní dotaz:

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;

Zobrazení také umožňují vytváření sestav a analytické nástroje, jako je Power BI, využívat výsledky OPENROWSET.

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

--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 OPENROWSETexterní tabulky umožňují dotazování více souborů a složek pomocí zástupných znaků. Externí tabulky nepodporují odvození schématu.

Důležité informace o výkonu

Počet souborů nebo množství dat, které se dají dotazovat, není nijak omezený, ale výkon dotazů závisí na množství dat, formátu dat, způsobu uspořádání dat a složitosti dotazů a spojení.

Dotazování na dělená data

Data jsou často uspořádaná do podsložek označovaných také jako oddíly. Můžete dotazu zadat, aby četl pouze konkrétní složky a soubory. Tím se sníží počet souborů a množství dat, která dotaz potřebuje ke čtení a zpracování, což vede k lepšímu výkonu. Tento typ optimalizace dotazů se označuje jako vyřazení oddílů nebo odstranění oddílů. Můžete eliminovat oddíly z provádění dotazů pomocí metadata funkce filepath() v klauzuli dotazu WHERE.

Následující ukázkový dotaz čte datové soubory NYC Yellow Taxi pouze za poslední tři měsíce roku 2017:

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'
    )
WITH (
    vendorID INT
) 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;

Pokud uložená data nejsou rozdělená na oddíly, zvažte jejich rozdělení, aby se zlepšil výkon dotazů.

Pokud používáte externí tabulky, jsou podporovány funkce filepath() a filename(), ale ne v klauzuli WHERE.

Řešení problémů

Problémy se spouštěním dotazů obvykle způsobují, že Azure SQL Database nemá přístup k umístění souboru. Související chybové zprávy můžou hlásit nedostatečná přístupová práva, neexistující umístění nebo cestu k souboru, soubor používaný jiným procesem nebo adresář nelze uvést. Ve většině případů to znamená, že přístup k souborům jsou blokovány zásadami řízení provozu sítě nebo kvůli nedostatku přístupových práv. Toto je to, co by se mělo zkontrolovat:

  • Nesprávná nebo chybně zadaná cesta k umístění.
  • Platnost klíče SAS: Platnost klíče SAS může vypršena, která obsahuje překlep, počínaje otazníkem.
  • Povolená oprávnění ke klíči SAS: Čtení minimálně a Seznam , pokud se používají zástupné cardy.
  • Zablokovaný příchozí provoz v účtu úložiště. Zkontrolujte Správu pravidel virtuální sítě pro službu Azure Storage.
  • Přístupová práva spravované identity: Ujistěte se, že spravovaná identita služby Azure SQL Database má udělená přístupová práva k účtu úložiště.
  • Aby dotazy virtualizace dat fungovaly, musí být úroveň kompatibility databáze 130 nebo vyšší.

Omezení

  • Statistiky externích tabulek se v současné době ve službě Azure SQL Database nepodporují.
  • CREATE EXTERNAL TABLE AS SELECT V současné době není ve službě Azure SQL Database k dispozici.
  • Funkce zabezpečení na úrovni řádků není u externích tabulek podporovaná.
  • Pravidlo dynamického maskování dat nelze definovat pro sloupec v externí tabulce.
  • Spravovaná identita nepodporuje scénáře napříč tenanty, pokud je účet služby Azure Storage v jiném tenantovi, podporuje se sdílený přístupový podpis.

Známé problémy

Související obsah