Delen via

Gegevensvirtualisatie met Azure SQL Managed Instance

  • Artikel

Van toepassing op: Azure SQL Managed Instance

Met de functie voor gegevensvirtualisatie van Azure SQL Managed Instance kunt u Transact-SQL-query's (T-SQL) uitvoeren op bestanden die gegevens opslaan in algemene gegevensindelingen 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.

Ook beschikbaar is CREATE EXTERNAL TABLE AS SELECT syntaxis voor Azure SQL Managed Instance, voor het exporteren van de resultaten van een T-SQL SELECT-instructie naar de Parquet- of CSV-bestanden in Azure Blob Storage of Azure Data Lake Storage (ADLS) Gen2 en het maken van een externe tabel boven op die bestanden.

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 en JSON_VALUE 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

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

Belangrijk

Het opgegeven locatietypevoorvoegsel wordt gebruikt om het optimale protocol voor communicatie te kiezen en gebruik te maken van geavanceerde mogelijkheden die door het specifieke opslagtype worden geboden. Het algemene https:// voorvoegsel wordt uitgeschakeld. Gebruik altijd eindpuntspecifieke voorvoegsels.

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:

  • Parquet: 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 verkennen van gegevenssets door WHERE, GROUP BY en andere componenten toe te voegen op basis van de resultatenset van de eerste query.

Als de eerste query op uw beheerde exemplaar mislukt, heeft dat exemplaar waarschijnlijk beperkte toegang tot Azure-opslagaccounts. Neem contact op met uw netwerkexpert om toegang in te schakelen voordat u query's kunt uitvoeren.

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 beheerd exemplaar, moet zijn gemachtigd om bestanden te openen en op te vragen die zijn opgeslagen in een niet-openbaar opslagaccount. Autorisatiestappen zijn afhankelijk van hoe het beheerde exemplaar wordt geverifieerd bij de opslag. Het type verificatie 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 Managed Instance ondersteunt de volgende verificatietypen:

Een beheerde identiteit is een functie van Microsoft Entra ID (voorheen Azure Active Directory) die Azure-services, zoals Azure SQL Managed Instance, biedt met een identiteit die wordt beheerd in Microsoft Entra ID. Deze identiteit kan worden gebruikt om aanvragen voor gegevenstoegang in niet-openbare opslagaccounts te autoriseren. Services zoals Azure SQL Managed Instance hebben een door het systeem toegewezen beheerde identiteit en kunnen ook een of meer door de gebruiker toegewezen beheerde identiteiten hebben. U kunt door het systeem toegewezen beheerde identiteiten of door de gebruiker toegewezen beheerde identiteiten gebruiken voor gegevensvirtualisatie met Azure SQL Managed Instance.

De Azure-opslagbeheerder moet eerst machtigingen verlenen aan de beheerde identiteit om toegang te krijgen tot de gegevens. Verwijs machtigingen aan de door het systeem toegewezen beheerde identiteit van het beheerde exemplaar op dezelfde manier als machtigingen worden verleend aan elke andere Microsoft Entra-gebruiker. Voorbeeld:

  1. Selecteer in Azure Portal op de pagina Toegangsbeheer (IAM) van een opslagaccount de optie Roltoewijzing toevoegen.
  2. Kies de ingebouwde Azure RBAC-rol opslagblobgegevenslezer . Dit biedt leestoegang tot de beheerde identiteit voor de benodigde Azure Blob Storage-containers.
    • In plaats van de beheerde identiteit de Azure RBAC-rol opslagblobgegevenslezer te verlenen, kunt u ook gedetailleerdere machtigingen verlenen voor een subset van bestanden. Alle gebruikers die toegang nodig hebben tot afzonderlijke bestanden lezen, moeten ook de machtiging Uitvoeren hebben voor alle bovenliggende mappen tot aan de hoofdmap (de container). Meer informatie over het instellen van ACL's in Azure Data Lake Storage Gen2.
  3. Selecteer Op de volgende pagina de optie Toegang totbeheerde identiteit toewijzen. + Selecteer leden en selecteer onder de vervolgkeuzelijst Beheerde identiteit de gewenste beheerde identiteit. Zie voor meer informatie Azure-rollen toewijzen met behulp van de Azure-portal.
  4. Vervolgens is het maken van de databasereferentie voor verificatie van beheerde identiteiten eenvoudig. Let op in het volgende voorbeeld dat 'Managed Identity' een in code vastgelegde tekenreeks is.
-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        CREDENTIAL = [MyCredential];
)

Query's uitvoeren op gegevensbronnen met 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 records voor geopende gegevenssets voor gele taxiritën 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 opvragen met de parquet-extensie in mappen. 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-deductie

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 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 opgeven met behulp van de component om de WITH 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 bestandsmetagegevens lezen filepath() en filename() een deel van het pad of het volledige pad en de naam ophalen van het bestand 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;
--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, 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 boven op de weergave moet worden gelezen en verwerkt wanneer deze worden gefilterd op een van deze 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 maken ook rapportage- en analysehulpprogramma's zoals Power BI mogelijk om resultaten van OPENROWSET.

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

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.

Prestatieoverwegingen

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 een beheerd exemplaar instrueren om alleen bepaalde mappen en bestanden op te vragen. 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 queryoptimalisatie wordt ook wel partitiesnoei of partitie-verwijdering 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 en filename() functies worden ondersteund, filepath() maar niet in de WHERE-component. U kunt nog steeds filteren filename op of filepath als u deze gebruikt in berekende kolommen. In het volgende voorbeeld ziet u dit:

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,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

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

statistieken

Het verzamelen van statistieken over uw externe gegevens is een van de belangrijkste dingen die u kunt doen voor queryoptimalisatie. Hoe meer het exemplaar weet over uw gegevens, hoe sneller het query's kan uitvoeren. De optimalisatiefunctie voor SQL Engine-query's is een optimalisatie op basis van kosten. Het vergelijkt de kosten van verschillende queryplannen en kiest vervolgens het abonnement met de laagste kosten. In de meeste gevallen wordt het plan gekozen dat het snelst wordt uitgevoerd.

Automatisch statistieken maken

Azure SQL Managed Instance analyseert binnenkomende gebruikersquery's voor ontbrekende statistieken. Als er statistieken ontbreken, worden met de queryoptimalisatie automatisch statistieken gemaakt voor afzonderlijke kolommen in het querypredicaat of joinvoorwaarde om de kardinaliteitschattingen voor het queryplan te verbeteren. Het automatisch maken van statistieken wordt synchroon uitgevoerd, zodat er mogelijk iets slechtere queryprestaties optreden als er statistieken ontbreken in uw kolommen. De tijd die nodig is om statistieken voor één kolom te maken, is afhankelijk van de grootte van de beoogde bestanden.

Handmatige statistieken van OPENROWSET

Statistieken met één kolom voor het OPENROWSET pad kunnen worden gemaakt met behulp van de sys.sp_create_openrowset_statistics opgeslagen procedure door de selectiequery door te geven met één kolom als parameter:

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Standaard gebruikt het exemplaar 100% van de gegevens die in de gegevensset worden geleverd om statistieken te maken. U kunt eventueel de grootte van de steekproef opgeven als een percentage met behulp van de TABLESAMPLE opties. Als u statistieken met één kolom voor meerdere kolommen wilt maken, voert u deze uit sys.sp_create_openrowset_statistics voor elk van de kolommen. U kunt geen statistieken met meerdere kolommen maken voor het OPENROWSET pad.

Als u bestaande statistieken wilt bijwerken, zet u ze eerst neer met behulp van de sys.sp_drop_openrowset_statistics opgeslagen procedure en maakt u deze vervolgens opnieuw met behulp van het sys.sp_create_openrowset_statisticsvolgende:

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Handmatige statistieken voor externe tabellen

De syntaxis voor het maken van statistieken voor externe tabellen lijkt op de syntaxis die wordt gebruikt voor gewone gebruikerstabellen. Als u statistieken voor een kolom wilt maken, geeft u een naam op voor het statistiekenobject en de naam van de kolom:

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

De WITH opties zijn verplicht en voor de steekproefgrootte zijn FULLSCAN de toegestane opties en SAMPLE n procent.

  • Als u statistieken met één kolom voor meerdere kolommen wilt maken, voert u deze uit CREATE STATISTICS voor elk van de kolommen.
  • Statistieken met meerdere kolommen worden niet ondersteund.

Problemen oplossen

Problemen met het uitvoeren van query's worden meestal veroorzaakt doordat het beheerde exemplaar 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 voor meer informatie en zorg ervoor dat toegang vanuit het VNet van het beheerde exemplaar is toegestaan.
  • Uitgaand verkeer dat is geblokkeerd op het beheerde exemplaar met behulp van beleid voor opslageindpunten. Uitgaand verkeer naar het opslagaccount toestaan.
  • Toegangsrechten voor beheerde identiteit: zorg ervoor dat de beheerde identiteit van het exemplaar toegangsrechten heeft voor het opslagaccount.
  • Het compatibiliteitsniveau van de database moet 130 of hoger zijn om gegevensvirtualisatiequery's te laten werken.

CREATE EXTERNAL TABLE AS SELECT (CETAS)

MET CREATE EXTERNAL TABLE AS SELECT (CETAS) kunt u gegevens uit uw met SQL beheerde exemplaar exporteren naar een extern opslagaccount. U kunt CETAS gebruiken om een externe tabel te maken boven op Parquet- of CSV-bestanden van Azure Blob Storage of Azure Data Lake Storage (ADLS) Gen2. CETAS kan ook de resultaten van een T-SQL SELECT-instructie parallel exporteren naar de gemaakte externe tabel. Er is mogelijk voor gegevensexfiltratierisico's met deze mogelijkheden, dus CETAS is standaard uitgeschakeld voor Azure SQL Managed Instance. Zie CREATE EXTERNAL TABLE AS SELECT (CETAS) om deze optie in te schakelen.

Beperkingen

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.

Gerelateerde inhoud