Gegevensvirtualisatie (preview)

Van toepassing op:✅SQL-database in Microsoft Fabric

Belangrijk

Deze functie is beschikbaar als preview-versie.

Met gegevensvirtualisatie in SQL Database in Fabric kunt u query's uitvoeren op externe gegevens die zijn opgeslagen in OneLake met behulp van T-SQL.

Met de syntaxis van gegevensvirtualisatie kunt u Transact-SQL query's (T-SQL) uitvoeren op bestanden die gegevens opslaan in algemene gegevensindelingen in OneLake. U kunt deze gegevens combineren met lokaal opgeslagen relationele gegevens met behulp van joins. Met gegevensvirtualisatie hebt u transparant toegang tot externe gegevens in de modus Alleen-lezen, terwijl u deze in de oorspronkelijke indeling en locatie houdt.

Syntaxis

Fabric SQL Database ondersteunt de volgende mogelijkheden voor gegevensvirtualisatie:

• DATABASESPECIFIEKE REFERENTIES MAKEN
• MAAK EXTERNE GEGEVENSBRON AAN
• CREËER EXTERN BESTANDSFORMAT
• CREËER EXTERNE TABEL
• OPENROWSET (BULK)
• SELECTEER IN .. VANUIT OPENROWSET
• Metagegevensfuncties: bestandsnaam(), filepath(), sp_describe_first_result_set()

Authenticatie

Verificatie bij Fabric Lakehouses maakt gebruik van passthrough-verificatie van Microsoft Entra ID.

Voor toegang tot bestanden vanuit Fabric OneLake moet de identiteit van de gebruiker zijn gemachtigd voor zowel de Lakehouse- als de bestandslocatie.

Permissions

Gebruikers moeten leestoegang hebben tot het bestand of de map in OneLake, gehandhaafd via een passthrough voor Microsoft Entra ID.

Ondersteunde bestandstypen

• Parquet
• CSV
• 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 behulp van JSON_VALUE en OPENJSON.

Ondersteunde gegevensbronnen

Alleen Fabric Lakehouse wordt momenteel systeemeigen ondersteund. OneLake-snelkoppelingen kunnen echter worden gebruikt om uit te breiden naar verschillende externe bronnen, zoals Azure Blob Storage, Azure Data Lake Gen2, Dataverse, Amazon S3, Amazon S3 Compatibel, Google Cloud Storage, openbare HTTPS en meer.

Zie Gegevensbronnen samenvoegen met OneLake-snelkoppelingen voor meer informatie over Fabric-snelkoppelingen.

De ABFSS-bestandslocatie van een lakehouse vinden

Om een Fabric Lakehouse-databron te maken, moet je workspace-ID, tenant en lakehouse-ID opgeven. De ABFSS-bestandslocatie van een lakehouse vinden:

1. Ga naar de Fabric-portal.
2. Navigeer naar uw Lakehouse.
3. Navigeer naar de gewenste maplocatie.
4. Selecteer ... en vervolgens Eigenschappen.
5. Kopieer het ABFS-pad, dat er ongeveer zo uitziet: abfss://<workspace ID>@<Tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/.

Beperkingen

• Externe CSV-tabellen moeten worden opgevraagd met behulp van schema-gekwalificeerde namen, dbo.Customer_CSVbijvoorbeeld.
• BULK INSERT wordt momenteel alleen ondersteund wanneer deze wordt gebruikt in combinatie met OPENROWSET (BULK).

Voorbeelden

De volgende voorbeeldscripts gebruiken een Fabric Lakehouse met de naam Cold_Lake die als host fungeert voor contoso-opslag en klantgegevens in Parquet- en CSV-bestanden.

Eén. Een query uitvoeren op een Parquet-bestand met OPENROWSET

In het volgende voorbeeld ziet u hoe OPENROWSET u voorbeeldgegevens ophaalt uit een Parquet-bestand.

SELECT TOP 100 *  
FROM OPENROWSET(  
    BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/customer.parquet',  
    FORMAT = 'parquet'  
) AS customer_dataset;  

B. Query's uitvoeren op een CSV-bestand met OPENROWSET

In het volgende voorbeeld ziet u hoe OPENROWSET u voorbeeldgegevens ophaalt uit een CSV-bestand.

SELECT *  
FROM OPENROWSET(  
    BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/customer.csv',  
    FORMAT = 'CSV',  
    FIRST_ROW = 2  
) WITH (  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    Country_Region_Full NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6)  
) AS customer_dataset; 

C. Externe gegevensbron maken

In het volgende voorbeeld ziet u hoe u een externe gegevensbron maakt om externe tabellen en opdrachten te vereenvoudigen, zoals OPENROWSET:

CREATE EXTERNAL DATA SOURCE [Cold_Lake] 
WITH ( 
LOCATION = 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/'); 

Als u een externe gegevensbron hebt gemaakt, kunt u bijvoorbeeld het volgende vereenvoudigen OPENROWSET:

-- USING DATA SOURCE WITH OPENROWSET 
SELECT TOP 100 * FROM OPENROWSET 
(BULK '/customer.parquet' 
, FORMAT = 'parquet' 
, DATA_SOURCE = 'Cold_Lake' ) 
 AS Customer_dataset; 

-- USING DATA SOURCE WITH OPENROWSET 
SELECT TOP 100 *  
FROM OPENROWSET(  
    BULK '/customer.csv',  
    FORMAT = 'CSV',  
    DATA_SOURCE = 'Cold_Lake', 
    FIRST_ROW = 2  
) WITH (  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    Country_Region_Full NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6)  
) AS customer_dataset; 

D. Externe tabel maken voor Parquet

In het volgende voorbeeld ziet u hoe u een externe bestandsindeling instelt en vervolgens een externe tabel maakt die specifiek is bedoeld voor parquet-gegevens.

CREATE EXTERNAL FILE FORMAT Parquetff WITH (FORMAT_TYPE=PARQUET); 

CREATE EXTERNAL TABLE [ext_product]( 
    [ProductKey] [int] NULL, 
    [ProductCode] [nvarchar](255) NULL, 
    [ProductName] [nvarchar](500) NULL, 
    [Manufacturer] [nvarchar](50) NULL, 
    [Brand] [nvarchar](50) NULL, 
    [Color] [nvarchar](20) NULL, 
    [WeightUnit] [nvarchar](20) NULL, 
    [Weight] DECIMAL(20, 5) NULL, 
    [Cost] DECIMAL(20, 5) NULL, 
    [Price] DECIMAL(20, 5) NULL, 
    [CategoryKey] [int] NULL, 
    [CategoryName] [nvarchar](30) NULL, 
    [SubCategoryKey] [int] NULL, 
    [SubCategoryName] [nvarchar](50) NULL) 
WITH 
(LOCATION = '/product.parquet' 
,DATA_SOURCE = [Cold_Lake] 
,FILE_FORMAT = Parquetff); 
 
SELECT * FROM [dbo].[ext_product] 

E. Externe tabel maken voor CSV

In het volgende voorbeeld ziet u hoe u een externe bestandsindeling instelt en een externe tabel maakt die specifiek is bedoeld voor CSV-gegevens.

CREATE EXTERNAL FILE FORMAT [CSVFileFormat]  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,  
    FORMAT_OPTIONS (  
        FIELD_TERMINATOR = ',',  
        FIRST_ROW = 2  
    )  
); 
 
CREATE EXTERNAL TABLE ext_customer_csv ( 
    CustomerKey INT NOT NULL,  
    GeoAreaKey INT NOT NULL,  
    StartDT DATETIME2 NOT NULL, 
    EndDT DATETIME2 NOT NULL, 
    Continent VARCHAR(50) NOT NULL, 
    Gender VARCHAR(10) NOT NULL, 
    Title VARCHAR(10) NOT NULL,  
    GivenName VARCHAR(100) NOT NULL,  
    MiddleInitial VARCHAR(2) NOT NULL,  
    Surname VARCHAR(100) NOT NULL, 
    StreetAddress VARCHAR(200) NOT NULL, 
    City VARCHAR(100) NOT NULL, 
    State VARCHAR(100) NOT NULL, 
    StateFull VARCHAR(100) NOT NULL, 
    ZipCode VARCHAR(20) NOT NULL,  
    Country_Region CHAR(2) NOT NULL 
    ) 
WITH (  
LOCATION = '/customer.csv' 
, DATA_SOURCE = Cold_Lake 
, FILE_FORMAT = CSVFileFormat 
); 

SELECT * FROM [dbo].[ext_customer_csv]; 

F. Gegevens opnemen met BEHULP van OPENROWSET

In het volgende voorbeeld ziet u hoe OPENROWSET u gegevens kunt opnemen in een nieuwe tabel:

SELECT * 
INTO tb_store 
FROM OPENROWSET 
(BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' )
 AS STORE;

Voor een bestaande tabel INSERT INTO kan worden gebruikt om de tabel te vullen vanuit OPENROWSET:

INSERT INTO tb_store  
SELECT TOP 100 * FROM OPENROWSET 
(BULK ' abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/contoso/store.parquet' 
, FORMAT = 'parquet' ) 
 AS STORE; 

G. Metagegevensfuncties gebruiken - sp_describe_first_result_set

De functie sp_describe_first_result_set kan worden gebruikt in combinatie met OPENROWSET (BULK) het maken van een schatting van het externe bestandsschema. U kunt het schema voor de CREATE TABLE of CREATE EXTERNAL TABLE instructies en voor verdere gegevensverkenning identificeren.

De sp_describe_first_result_set functie maakt gebruik van een voorbeeld van de gegevens om het schema te schatten. Als het voorbeeld niet representatief is, kunnen er onnauwkeurige resultaten worden weergegeven. Als het schema al bekend is, specificeer het via WITH clausule.

EXEC sp_describe_first_result_set N'  
   SELECT * FROM OPENROWSET(  
      BULK ''abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/store.parquet'',  
      FORMAT = ''parquet''  
   ) AS DATA'; 

Zie sp_describe_first_result_set()voor meer informatie.

H. Metagegevensfuncties gebruiken - bestandsnaam() en filepath()

Fabric SQL Database maakt ook filename() en filepath() functies beschikbaar voor het verkennen van mappen en bestanden, en voor het maken van dynamische query's, die tevens kunnen worden gebruikt voor virtuele kolommen in combinatie met OPENROWSET voor gegevensbestanden in meerdere submappen.

In het volgende voorbeeld worden alle Parquet-bestanden en de locatie ervan weergegeven.

SELECT 
  r.filename() as file_name
, r.filepath() as full_path 
FROM OPENROWSET
   (BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/*/*.parquet',  
    FORMAT = 'parquet'  
   ) AS r 
GROUP BY r.filename(), r.filepath() 
ORDER BY file_name;  

Zie bestandsnaam() en filepath() voor meer informatie.

Verwante inhoud

• Gegevensbronnen samenvoegen met OneLake-snelkoppelingen
• OPENROWSET (BULK)