Gegevensvirtualisatie met Azure SQL Database (preview)

Van toepassing op:Azure SQL Database

Met de functie voor gegevensvirtualisatie van Azure SQL Database kunt u Transact-SQL (T-SQL)-query's uitvoeren op bestanden die gegevens opslaan in algemene gegevensindelingen, zoals CSV (zonder gebruik te hoeven maken van gescheiden tekst), Parquet en Delta (1.0). U kunt deze gegevens opvragen in Azure Data Lake Storage Gen2 of Azure Blob Storage en deze combineren met lokaal opgeslagen relationele gegevens met behulp van joins. Op deze manier hebt u transparant toegang tot externe gegevens (in de modus Alleen-lezen) terwijl u deze in de oorspronkelijke indeling en locatie houdt, ook wel gegevensvirtualisatie genoemd.

Overzicht

Gegevensvirtualisatie biedt twee manieren om query's uit te voeren op bestanden die zijn bedoeld voor verschillende sets scenario's:

• OPENROWSET-syntaxis : geoptimaliseerd voor ad-hocquery's van bestanden. Meestal gebruikt om snel de inhoud en de structuur van een nieuwe set bestanden te verkennen.
• CREATE EXTERNAL TABLE syntaxis : geoptimaliseerd voor terugkerende query's op bestanden met behulp van identieke syntaxis alsof gegevens lokaal zijn opgeslagen in de database. Voor externe tabellen zijn verschillende voorbereidingsstappen vereist in vergelijking met de OPENROWSET-syntaxis, maar is meer controle over gegevenstoegang mogelijk. Externe tabellen worden doorgaans gebruikt voor analytische workloads en rapportage.

In beide gevallen moet er een externe gegevensbron worden gemaakt met behulp van de T-SQL-syntaxis CREATE EXTERNAL DATA SOURCE , zoals in dit artikel wordt gedemonstreerd.

Bestandsindelingen

Parquet- en CSV-bestandsindelingen (scheidingstekens) voor tekst worden rechtstreeks ondersteund. De JSON-bestandsindeling wordt indirect ondersteund door de CSV-bestandsindeling op te geven waarbij query's elk document als een afzonderlijke rij retourneren. U kunt rijen verder parseren met JSON_VALUE en OPENJSON.

Opslagtypen

Bestanden kunnen worden opgeslagen in Azure Data Lake Storage Gen2 of Azure Blob Storage. Als u query's wilt uitvoeren op bestanden, moet u de locatie in een specifieke indeling opgeven en het locatietypevoorvoegsel gebruiken dat overeenkomt met het type externe bron en eindpunt/protocol, zoals de volgende voorbeelden:

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

Aan de slag

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:

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

U kunt doorgaan met het verkennen van gegevenssets door WHERE, GROUP BY en andere clausules toe te voegen op basis van de resultatenset van de eerste query.

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.

Toegang tot niet-openbare opslagaccounts

Een gebruiker die is aangemeld bij een Azure SQL Database, moet zijn gemachtigd om bestanden te openen en op te vragen die zijn opgeslagen in niet-openbare opslagaccounts. Autorisatiestappen zijn afhankelijk van hoe Azure SQL Database de opslag verifieert. De typen verificaties en eventuele gerelateerde parameters worden niet rechtstreeks bij elke query geleverd. Ze worden ingekapseld in het referentieobject met databasebereik dat is opgeslagen in de gebruikersdatabase. De referentie wordt door de database gebruikt voor toegang tot het opslagaccount wanneer de query wordt uitgevoerd.

Azure SQL Database ondersteunt de volgende verificatietypen:

• Gedeelde toegangshandtekening (SAS)
• Beheerde identiteit
• PassThrough-verificatie van Microsoft Entra via gebruikersidentiteit

Toegang tot openbare opslag via anonieme accounts

Als de gewenste gegevensset openbare toegang toestaat (ook wel anonieme toegang genoemd), is er geen referentie vereist zolang Azure Storage juist is geconfigureerd, raadpleegt u Anonieme leestoegang configureren voor containers en blobs.

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

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://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       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 dataset met ritrecords van gele taxi's 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:

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

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, dus het exemplaar beschouwt dit als varchar(8000).

Gebruik de opgeslagen sp_describe_first_results_set-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 met behulp van de functies filepath() en filename() bestandsmetagegevens lezen en een deel van het pad of het volledige pad en de naam van het bestand ophalen waarvan de rij in de resultatenset afkomstig is.

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

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, wordt een deel van het pad geretourneerd dat overeenkomt met het jokerteken op de positie die is opgegeven in de parameter. Parameterwaarde 1 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 en gebruiken om OPENROWSET-query's te verpakken, zodat u de onderliggende query eenvoudig opnieuw kunt gebruiken:

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;

Weergaven stellen rapportage- en analysehulpprogramma's zoals Power BI ook in staat om de resultaten van OPENROWSET te benutten.

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

--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 OPENROWSETbij externe tabellen kunt u query's uitvoeren op meerdere bestanden en mappen met behulp van jokertekens. Schemadeductie wordt niet ondersteund met externe tabellen.

Prestatie-overwegingen

Er is geen vaste limiet voor het aantal bestanden of de hoeveelheid gegevens waarop query's kunnen worden uitgevoerd, maar de queryprestaties zijn afhankelijk van de hoeveelheid gegevens, gegevensindeling, de manier waarop gegevens worden georganiseerd en complexiteit van query's en joins.

Gepartitioneerde gegevens opvragen

Gegevens worden vaak ingedeeld in submappen, ook wel partities genoemd. U kunt de query instrueren om alleen bepaalde mappen en bestanden te lezen. Dit vermindert het aantal bestanden en de hoeveelheid gegevens die de query nodig heeft om te lezen en te verwerken, wat resulteert in betere prestaties. Dit type van queryoptimalisatie wordt ook wel partitiesnoei of partitieverwijdering genoemd. U kunt partities verwijderen uit de uitvoering van query's met behulp van de metagegevensfunctie filepath() in de WHERE component van de query.

De volgende voorbeeldquery leest nyC Yellow Taxi-gegevensbestanden alleen voor de afgelopen drie maanden van 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;

Als uw opgeslagen gegevens niet zijn gepartitioneerd, kunt u overwegen deze te partitioneren om de queryprestaties te verbeteren.

Als u externe tabellen gebruikt, worden de functies filepath() en filename() ondersteund, maar niet in de WHERE clausule.

Problemen oplossen

Problemen met het uitvoeren van query's worden meestal veroorzaakt doordat Azure SQL Database geen toegang heeft tot de bestandslocatie. De gerelateerde foutberichten melden mogelijk onvoldoende toegangsrechten, niet-bestaande locatie of bestandspad, bestand dat wordt gebruikt door een ander proces of die map kan niet worden weergegeven. In de meeste gevallen geeft dit aan dat de toegang tot bestanden wordt geblokkeerd door beleid voor netwerkverkeersbeheer of vanwege een gebrek aan toegangsrechten. Dit is wat moet worden gecontroleerd:

• Onjuist of onjuist getypt locatiepad.
• Geldigheid van SAS-sleutel: deze kan verlopen zijn, met een typefout, te beginnen met een vraagteken.
• Toegestane SAS-sleutelmachtigingen: minimaal lezen en Lijst als jokertekens worden gebruikt.
• Het inkomende verkeer voor het opslagaccount is geblokkeerd. Controleer het beheren van regels voor virtuele netwerken voor Azure Storage.
• Toegangsrechten voor beheerde identiteit: zorg ervoor dat de beheerde identiteit van de Azure SQL Database toegangsrechten heeft voor het opslagaccount.
• Het compatibiliteitsniveau van de database moet 130 of hoger zijn om gegevensvirtualisatiequery's te laten werken.

Beperkingen

• Statistieken over externe tabellen worden momenteel niet ondersteund in Azure SQL Database.
• CREATE EXTERNAL TABLE AS SELECT Momenteel is deze niet beschikbaar in Azure SQL Database.
• De beveiligingsfunctie op rijniveau wordt niet ondersteund voor externe tabellen.
• Dynamische gegevensmaskeringsregel kan niet worden gedefinieerd voor een kolom in een externe tabel.
• Beheerde identiteit biedt geen ondersteuning voor scenario's voor meerdere tenants, als uw Azure Storage-account zich in een andere tenant bevindt, is Shared Access Signature de ondersteunde methode.

Bekende problemen

• Wanneer parameterisatie voor Always Encrypted is ingeschakeld in SQL Server Management Studio (SSMS), mislukken gegevensvirtualisatiequery's met Incorrect syntax near 'PUSHDOWN' een foutbericht.

Verwante inhoud

• OPENROWSET T-SQL
• CREËER EXTERNE TABEL
• CREËER EXTERN BESTANDSFORMAT
• MAAK EXTERNE GEGEVENSBRON AAN