Dela via

Datavirtualisering med Azure SQL Database (förhandsversion)

gäller för:Azure SQL Database

Med datavirtualiseringsfunktionen i Azure SQL Database kan du köra Transact-SQL frågor (T-SQL) på filer som lagrar data i vanliga dataformat som CSV (utan behov av att använda avgränsad text), Parquet och Delta (1.0). Du kan köra frågor mot dessa data i Azure Data Lake Storage Gen2 eller Azure Blob Storage och kombinera dem med lokalt lagrade relationsdata med hjälp av kopplingar. På så sätt kan du transparent komma åt externa data (i skrivskyddat läge) samtidigt som du behåller dem i dess ursprungliga format och plats , även kallat datavirtualisering.

Översikt

Datavirtualisering ger två sätt att köra frågor mot filer som är avsedda för olika uppsättningar scenarier:

  • OPENROWSET-syntax – optimerad för ad hoc-frågekörning av filer. Används vanligtvis för att snabbt utforska innehållet och strukturen för en ny uppsättning filer.
  • CREATE EXTERNAL TABLE syntax – optimerad för repetitiv frågekörning av filer med identisk syntax som om data lagrades lokalt i databasen. Externa tabeller kräver flera förberedelsesteg jämfört med OPENROWSET-syntaxen, men ger mer kontroll över dataåtkomst. Externa tabeller används vanligtvis för analytiska arbetsbelastningar och rapportering.

I båda fallen måste en extern datakälla skapas med T-SQL-syntaxen SKAPA EXTERN DATAKÄLLA , vilket visas i den här artikeln.

Filformat

Filformat för parquet och avgränsad text (CSV) stöds direkt. JSON-filformatet stöds indirekt genom att ange CSV-filformatet där frågor returnerar varje dokument som en separat rad. Du kan parsa rader ytterligare med hjälp av JSON_VALUE och OPENJSON.

Lagringstyper

Filer kan lagras i Azure Data Lake Storage Gen2 eller Azure Blob Storage. Om du vill fråga efter filer måste du ange platsen i ett visst format och använda platstypprefixet som motsvarar typen av extern källa och slutpunkt/protokoll, till exempel följande exempel:

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

Viktigt!

Använd alltid slutpunktsspecifika prefix. Det angivna platstypsprefixet används för att välja det optimala protokollet för kommunikation och för att utnyttja alla avancerade funktioner som erbjuds av den specifika lagringstypen.

Det allmänna https:// prefixet stöds bara för BULK INSERT, men inte för andra användningsfall, inklusive OPENROWSET eller EXTERNAL TABLE.

Kom igång

Om du är nybörjare på datavirtualisering och snabbt vill testa funktioner börjar du med att fråga offentliga datauppsättningar som är tillgängliga i Azure Open Datasets, till exempel datauppsättningen COVID-19 i Bing som tillåter anonym åtkomst.

Använd följande slutpunkter för att hämta data från datauppsättningarna i Bing COVID-19.

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

För en snabbstart kör du den här enkla T-SQL-frågan för att få första insikter om datauppsättningen. Den här frågan använder OPENROWSET för att fråga en fil som lagras i ett offentligt tillgängligt lagringskonto:

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

Du kan fortsätta datamängdsutforskningen genom att lägga till WHEREoch GROUP BY andra satser baserat på resultatuppsättningen för den första frågan.

När du har bekantat dig med att fråga offentliga datamängder kan du byta till icke-offentliga datauppsättningar som kräver att du anger autentiseringsuppgifter, beviljar åtkomsträttigheter och konfigurerar brandväggsregler. I många verkliga scenarier kommer du främst att arbeta med privata datamängder.

Åtkomst till icke-offentliga lagringskonton

En användare som är inloggad i en Azure SQL-databas måste ha behörighet att komma åt och göra förfrågningar mot filer som lagras i konton för icke-offentlig lagring. Auktoriseringssteg beror på hur Azure SQL Database autentiserar lagringen. Typerna av autentiseringar och eventuella relaterade parametrar tillhandahålls inte direkt med varje fråga. De kapslas in i det databasomfattande autentiseringsobjektet som lagras i användardatabasen. Autentiseringsuppgifterna används av databasen för att komma åt lagringskontot varje gång frågan körs.

Azure SQL Database stöder följande autentiseringstyper:

  • Signatur för delad åtkomst (SAS)
  • Hanterad identitet
  • Microsoft Entra-direktautentisering via användaridentitet

En signatur för delad åtkomst (SAS) ger delegerad åtkomst till filer i ett lagringskonto. SAS ger detaljerad kontroll över vilken typ av åtkomst du beviljar, inklusive giltighetsintervall, beviljade behörigheter och acceptabelt IP-adressintervall. När SAS-token har skapats kan den inte återkallas eller tas bort, och den ger åtkomst tills dess giltighetsperiod upphör att gälla.

  1. Du kan hämta en SAS-token på flera sätt:

  2. Bevilja läs- och listbehörigheter via SAS för åtkomst till externa data. För närvarande är datavirtualisering med Azure SQL Database skrivskyddad.

  3. Om du vill skapa en databasomfattande autentiseringsuppgift i Azure SQL Database måste du först skapa databashuvudnyckeln, om det inte redan finns någon. En huvudnyckel för databasen krävs när autentiseringsuppgifterna kräver SECRET.

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

  4. När en SAS-token genereras innehåller den ett frågetecken (?) i början av token. Om du vill använda token måste du ta bort frågetecknet (?) när du skapar en autentiseringsuppgift. Till exempel:

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

Åtkomst till offentlig lagring via anonyma konton

Om den önskade datauppsättningen tillåter offentlig åtkomst (kallas även anonym åtkomst) krävs inga autentiseringsuppgifter så länge Azure Storage är korrekt konfigurerat, se Konfigurera anonym läsåtkomst för containrar och blobar.

Extern datakälla

En extern datakälla är en abstraktion som gör det enkelt att referera till en filplats i flera frågor. För att söka offentliga platser behöver du endast ange filplatsen när du skapar en extern datakälla.

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

När du kommer åt icke-offentliga lagringskonton, tillsammans med platsen, måste du också referera till en databasomfattande autentiseringsuppgift med inkapslade autentiseringsparametrar. Följande skript skapar en extern datakälla som pekar på filsökvägen och refererar till en databasomfattande autentiseringsuppgift.

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

Fråga datakällor med OPENROWSET

OPENROWSET-syntaxen möjliggör omedelbar ad hoc-frågor samtidigt som du bara skapar det minimala antalet databasobjekt som krävs.

OPENROWSET kräver endast att du skapar den externa datakällan (och eventuellt autentiseringsuppgifterna) i motsats till metoden för extern tabell, vilket kräver ett externt filformat och själva den externa tabellen .

Parametervärdet DATA_SOURCE läggs automatiskt till i BULK-parametern för att bilda den fullständiga sökvägen till filen.

När du använder OPENROWSET anger du formatet för filen, till exempel följande exempel, som frågar en enda fil:

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

Fråga flera filer och mappar

Kommandot OPENROWSET gör det också möjligt att köra frågor mot flera filer eller mappar med hjälp av jokertecken i BULK-sökvägen.

I följande exempel används New York Citys öppna datamängd för gula taxiresor.

Skapa först den externa datakällan:

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

Nu kan vi fråga alla filer med .parquet-tillägget i mappar. Här frågar vi till exempel bara de filer som matchar ett namnmönster:

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

När du gör förfrågningar mot flera filer eller mappar måste alla filer som nås med ett enda OPENROWSET ha samma struktur (till exempel samma antal kolumner och datatyper). Det går inte att bläddra igenom mappar rekursivt.

Schemahärledning

Automatisk schemainferens hjälper dig att snabbt skriva frågor och utforska data när du inte känner till filscheman. Schemainferens fungerar bara med parquet-filer.

Även om det är praktiskt kan härledda datatyper vara större än de faktiska datatyperna eftersom det kan finnas tillräckligt med information i källfilerna för att säkerställa att rätt datatyp används. Detta kan leda till dåliga frågeprestanda. Parquet-filer innehåller till exempel inte metadata om maximal kolumnlängd för tecken, så instansen härleder den som varchar(8000).

Använd den lagrade proceduren sp_describe_first_results_set och kontrollera de resulterande datatyperna för frågan, som i följande exempel:

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

När du känner till datatyperna kan du sedan ange dem med hjälp av WITH -satsen för att förbättra prestanda:

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;

Eftersom schemat för CSV-filer inte kan fastställas automatiskt måste kolumner alltid anges med hjälp av WITH -satsen:

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;

Filmetadata funktioner

När du ställer frågor mot flera filer eller mappar kan du använda filepath() och filename() funktioner för att läsa filmetadata och få en del av sökvägen eller hela sökvägen och namnet på filen som raden i resultatuppsättningen härstammar från.

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

När funktionen filepath() anropas utan en parameter returnerar den filvägen som raden kommer från. När DATA_SOURCE används i OPENROWSETreturneras sökvägen i förhållande till DATA_SOURCE, annars returneras en fullständig filsökväg.

När den anropas med en parameter returneras en del av sökvägen som matchar jokertecknet på den position som anges i parametern. Parametervärdet 1 returnerar till exempel en del av sökvägen som matchar det första jokertecknet.

Funktionen filepath() kan också användas för att filtrera och aggregera rader:

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;

Skapa vy ovanpå OPENROWSET

Du kan skapa och använda vyer för att omsluta OPENROWSET-frågor så att du enkelt kan återanvända den underliggande frågan:

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

Det är också praktiskt att lägga till kolumner med filplatsdata i en vy med hjälp av filepath() funktionen för enklare och mer högpresterande filtrering. Med hjälp av vyer kan du minska antalet filer och mängden data som frågan ovanpå vyn behöver läsa och bearbeta när den filtreras av någon av dessa kolumner:

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;

Vyer gör det också möjligt för rapporterings- och analysverktyg som Power BI att använda resultatet av OPENROWSET.

Externa tabeller

Externa tabeller kapslar in åtkomst till filer som gör frågeupplevelsen nästan identisk med att köra frågor mot lokala relationsdata som lagras i användartabeller. För att skapa en extern tabell krävs att externa datakällor och externa filformatobjekt finns:

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

När den externa tabellen har skapats kan du fråga den precis som andra tabeller:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Precis som OPENROWSET kan du med externa tabeller köra frågor mot flera filer och mappar med hjälp av jokertecken. Schemainferens stöds inte med externa tabeller.

Prestandaöverväganden

Det finns ingen hård gräns för antalet filer eller mängden data som kan efterfrågas, men frågeprestanda beror på mängden data, dataformat, hur data organiseras och komplexiteten för frågor och kopplingar.

Sök i partitionerade data

Data organiseras ofta i undermappar som även kallas partitioner. Du kan instruera frågan att endast läsa vissa mappar och filer. Detta minskar antalet filer och mängden data som frågan behöver läsa och bearbeta, vilket ger bättre prestanda. Den här typen av frågeoptimering kallas partitionsrensning eller partitionseliminering. Du kan eliminera partitioner från frågekörning med hjälp av metadatafunktionen filepath() i WHERE frågans sats.

Följande exempelfråga läser datafiler för NYC Yellow Taxi endast för de tre sista månaderna 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;

Om dina lagrade data inte är partitionerade kan du överväga att partitionera dem för att förbättra frågeprestandan.

Om du använder externa tabeller filepath() och filename() funktioner stöds men inte i WHERE -satsen.

Felsökning

Problem med frågekörning orsakas vanligtvis av att Azure SQL Database inte kan komma åt filplatsen. De relaterade felmeddelandena kan rapportera otillräckliga åtkomsträttigheter, icke-existerande plats eller filsökväg, fil som används av en annan process eller att katalogen inte kan visas. I de flesta fall indikerar detta att åtkomsten till filer blockeras av principer för nätverkstrafikkontroll eller på grund av bristande åtkomsträttigheter. Detta är vad som ska kontrolleras:

  • Fel eller felstavad platssökväg.
  • SAS-nyckelns giltighet: den kan ha upphört att gälla och innehålla ett stavfel, med början med ett frågetecken.
  • Tillåtna SAS-nyckelbehörigheter: Läs som minimum, och Lista ifall jokertecken används.
  • Blockerad inkommande trafik på lagringskontot. Kontrollera Hantera regler för virtuella nätverk för Azure Storage.
  • Åtkomsträttigheter för hanterad identitet: Kontrollera att den hanterade identiteten för Azure SQL Database har beviljats åtkomstbehörighet till lagringskontot.
  • Databasens kompatibilitetsnivå måste vara 130 eller högre för att datavirtualiseringsfrågor ska fungera.

Begränsningar

  • För närvarande stöds inte statistik för externa tabeller i Azure SQL Database.
  • CREATE EXTERNAL TABLE AS SELECT Är för närvarande inte tillgängligt i Azure SQL Database.
  • Säkerhetsfunktionen på radnivå stöds inte med externa tabeller.
  • Det går inte att definiera regeln för dynamisk datamaskering för en kolumn i en extern tabell.
  • Hanterad identitet stöder inte scenarier mellan klientorganisationer. Om du har ett Azure Storage-konto i en annan klientorganisation är signaturen för delad åtkomst den metod som stöds.

Kända problemområden

Relaterat innehåll