Acquisire e trasformare i dati del data lake con OPENROWSET e T-SQL

si applica a:✅ Magazzino di dati in Microsoft Fabric

Utilizzando la funzione OPENROWSET, puoi esporre file Parquet, CSV o JSONL come sorgente relazionale per le istruzioni di ingestione T-SQL. I file possono trovarsi in OneLake, Azure Data Lake Storage o Archiviazione BLOB di Azure. Il seguente esempio recupera il contenuto di un file Parquet usando il suo URL ADLS assoluto:

SELECT *
FROM OPENROWSET(
    BULK 'https://<storage-account>.dfs.core.windows.net/<container>/<folder>/<file-name>.parquet'
) AS source;

Poiché OPENROWSET restituisce i dati come un rowset, puoi usarli come sorgente di qualsiasi istruzione di ingestione T-SQL supportata, inclusi:

  • CREATE TABLE AS SELECT (CTAS) oppure SELECT ... INTO per creare una nuova tabella warehouse da uno o più file di riferimento.
  • INSERT ... SELECT per aggiungere contenuti di file a una tabella esistente.
  • MERGE per inserire nuove righe, aggiornare le righe esistenti, o entrambe le cose, basandosi sui dati nei file di riferimento.

Oltre all'ingestione, puoi applicare trasformazioni T-SQL arbitrarie prima di caricare i dati in una tabella. Ad esempio, puoi convertire i tipi di dati, derivare nuovi valori di colonna, filtrare le righe, unire i dati dei file con tabelle esistenti ed eseguire aggregazioni come parte del processo di ingestione.

Prerequisites

  • Un magazzino Fabric e il permesso di creare o modificare le tabelle di destinazione.
  • Accesso di lettura ai file sorgente utilizzati in una query del data warehouse.

Crea una tabella con CTAS

La seguente dichiarazione CTAS legge una partizione annuale Parquet e crea una tabella fact. Oltre alle colonne sorgente fisiche, deriva una misura aziendale e memorizza il percorso file di origine per la lineage:

CREATE TABLE dbo.FactOnlineSales
AS
SELECT
    source.SalesOrderID, source.ProductID, CAST(source.OrderDateTime AS date) AS OrderDate,
    source.Quantity, CAST(source.Quantity * source.UnitPrice AS decimal(18, 2)) AS SalesAmount, CAST(source.filepath() AS varchar(1024)) AS SourceFilePath
FROM OPENROWSET(
    BULK 'https://<storage-account>.dfs.core.windows.net/<container>/sales/year=2026/*.parquet'
) WITH (
    SalesOrderID bigint,
    ProductID int,
    OrderDateTime datetime2(6),
    Quantity int,
    UnitPrice decimal(18, 2)
) AS source
WHERE source.Quantity > 0;

OrderDate e SalesAmount vengono calcolati mentre le righe sono caricate, e il predicato di quantità esclude transazioni non valide. filepath() aggiunge l'URI sorgente completa a ogni riga di destinazione, il che aiuta a tracciare un fatto fino al file che lo ha fornito.

Crea una tabella con SELECT INTO

La seguente istruzione crea una tabella di importazione cliente da più file CSV. Pulisce un valore sorgente e aggiunge sia metadati del file sorgente che metadati al tempo di caricamento:

SELECT
    source.CustomerID, TRIM(source.CustomerName) AS CustomerName, source.Region, CAST(source.filepath() AS varchar(1024)) AS SourceFilePath,
    CAST(GETDATE() AS datetime2(6)) AS IngestionTime
INTO dbo.CustomerImport
FROM OPENROWSET(
    BULK 'https://<storage-account>.blob.core.windows.net/<container>/customers/*.csv',
    HEADER_ROW = TRUE
) AS source
WHERE source.CustomerID IS NOT NULL;

Il carattere jolly combina tutti i file CSV corrispondenti in un'unica fonte relazionale. HEADER_ROW = TRUE legge i nomi delle colonne dalla prima riga di ogni file. TRIM Standardizza i nomi dei clienti, filepath() registra la provenienza della riga e GETDATE() registra quando il data warehouse ha acquisito la riga. Il predicato null-key impedisce che i record clienti inutilizzabili entrino nella nuova tabella.

Aggiungi un batch incrementale con INSERT ... SELECT

Il seguente esempio carica partizioni JSONL selezionate in una tabella esistente. Oltre alle proprietà dell'ordine fisico, acquisisce l'anno e il mese codificati nel percorso della cartella e cattura la provenienza del file e il tempo di ingestione:

INSERT INTO dbo.Sales
    (OrderID, CustomerID, OrderDate, Region, TotalAmount,
     OrderYear, OrderMonth, SourceFilePath, IngestionTime)
SELECT
    source.OrderID, source.CustomerID, source.OrderDate, source.Region, source.TotalAmount,
    CAST(source.filepath(1) AS smallint), CAST(source.filepath(2) AS smallint), source.filepath(), GETDATE()
FROM OPENROWSET(
    BULK 'https://<storage-account>.dfs.core.windows.net/<container>/orders/year=*/month=*/*.jsonl'
) AS source
WHERE source.filepath(1) = '2026'
  AND source.filepath(2) IN ('07', '08', '09');

Il primo e il secondo carattere jolly corrispondono ai valori della cartella year e month. filepath(1) e filepath(2) espongono quei valori di partizione come dati di tipo carattere, così che la query li converte in smallint prima di memorizzarli come OrderYear e OrderMonth. filepath() Senza un argomento memorizza il percorso sorgente completo. I caratteri jolly mantengono la query riutilizzabile, mentre i predicati selezionano il lotto corrente e permettono OPENROWSET di saltare cartelle non corrispondenti.

Sincronizza un file batch con MERGE

Da usare MERGE quando un batch di file può contenere sia righe nuove che righe modificate. Il seguente esempio legge i file di prodotto attuali, standardizza i valori descrittivi e porta un timestamp di modifica della sorgente nella corrispondenza:

MERGE dbo.DimProductFromFiles AS target
USING (
    SELECT
        source.ProductID, TRIM(source.ProductName) AS ProductName,
        source.Category, source.ModifiedDate
    FROM OPENROWSET(
        BULK 'https://<storage-account>.dfs.core.windows.net/<container>/products/current/*.parquet'
    ) AS source
) AS source
ON target.ProductKey = source.ProductID
WHEN MATCHED AND source.ModifiedDate > target.ModifiedDate THEN
    UPDATE SET ProductName = source.ProductName, Category = source.Category,
        ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductKey, ProductName, Category, ModifiedDate)
    VALUES (source.ProductID, source.ProductName, source.Category, source.ModifiedDate);

La query aggiorna un prodotto solo quando il file contiene una versione più recente e inserisce ID prodotto che non sono nella tabella dimensione. Assicurati che il file set restituisca non più di una riga per ciascun ProductID Aggiungi WHEN NOT MATCHED BY SOURCE THEN DELETE solo quando il percorso rappresenta una snapshot autorevole completa, non un batch incrementale.

Leggi file da OneLake

Usa l'URL completo di OneLake per le query occasionali. Questo esempio carica una partizione annuale e memorizza l'URI OneLake ad ogni corsa in taxi:

INSERT INTO dbo.TaxiTrips
    (TripID, PickupDateTime, TripDistance, TotalAmount, SourceFilePath)
SELECT
    source.TripID, source.PickupDateTime, source.TripDistance,
    source.TotalAmount, source.filepath()
FROM OPENROWSET(
    BULK 'https://onelake.dfs.fabric.microsoft.com/<workspace-id>/<lakehouse-id>/Files/year=2026/month=*/*.parquet'
) AS source;

L'URL identifica lo spazio di lavoro, il lakehouse, /Files l'area e la cartella partizionata. Il carattere jolly del mese legge tutti i file mensili del 2026, mentre filepath() preserva il file OneLake esatto che ha prodotto ogni riga di destinazione. Per le query riutilizzabili, definire una sorgente di dati esterna alla radice del lakehouse in modo che gli identificatori di workspace e lakehouse vengano mantenuti in un unico oggetto:

CREATE EXTERNAL DATA SOURCE SalesFilesRoot
WITH (
    LOCATION = 'https://onelake.dfs.fabric.microsoft.com/<workspace-id>/<lakehouse-id>'
);

SalesFilesRoot è un oggetto persistente nel warehouse corrente e richiede il permesso per creare fonti di dati esterne. Diventa un nome riutilizzabile per la radice OneLake, così che un cambio di posizione possa essere effettuato una sola volta invece che in ogni query di ingestione.

Puoi quindi usare un percorso relativo mantenendo lo stesso comportamento di wildcard e partizione:

CREATE TABLE dbo.TaxiTrips AS
SELECT *
FROM OPENROWSET(
    BULK '/Files/year=2026/month=*/*.parquet',
    DATA_SOURCE = 'SalesFilesRoot'
) AS source;

DATA_SOURCE combina la radice OneLake memorizzata con il percorso relativo /Files . Questo mantiene le query di ingestione focalizzate su cartelle, partizioni e logica di trasformazione invece che su identificatori di posizione ripetuti.

Scegli OPENROWSET o COPY

Da utilizzare OPENROWSET con un'istruzione di ingestione T-SQL quando il carico include filtraggio, joins, calcoli, modellazione dello schema o sincronizzazione. Per un carico diretto dall'origine alla destinazione senza trasformazioni, usa l'istruzione COPY.

Monitorare l'acquisizione

Le istruzioni di ingestione dei file appaiono insieme ad altre richieste T-SQL nella cronologia/attività delle query del warehouse. Usa query insights per ispezionare i dettagli delle loro esecuzioni.