Indtag og transformér lake-data med OPENROWSET og T-SQL

Gælder for:✅ Warehouse i Microsoft Fabric

Ved at bruge funktionen OPENROWSET kan du eksponere Parquet-, CSV- eller JSONL-filer som en relationel kilde til T-SQL-indlæsningssætninger. Filerne kan være i OneLake, Azure Data Lake Storage eller Azure Blob Storage. Følgende eksempel henter indholdet af en Parquet-fil ved at bruge dens absolutte ADLS-URL:

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

Fordi OPENROWSET data returneres som et rækkesæt, kan du bruge det som kilde til enhver understøttet T-SQL-indlæsningssætning, herunder:

  • CREATE TABLE AS SELECT (CTAS) eller SELECT ... INTO at oprette en ny lagertabel ud fra en eller flere refererede filer.
  • INSERT ... SELECT for at tilføje filindhold til en eksisterende tabel.
  • MERGE for at indsætte nye rækker, opdater eksisterende rækker eller begge dele baseret på data i de refererede filer.

Ud over at indlæse kan du anvende vilkårlige T-SQL-transformationer, før du indlæser dataene i en tabel. For eksempel kan du konvertere datatyper, udlede nye kolonneværdier, filtrere rækker, sammenlægge fildata med eksisterende tabeller og udføre aggregeringer som en del af indlæsningsprocessen.

Forudsætninger

  • Et Fabric-lager og tilladelse til at oprette eller ændre destinationstabellerne.
  • Læseadgang til kildefilerne, der bruges i en lagerforespørgsel.

Opret en tabel med CTAS

Følgende CTAS-erklæring læser en årlig Parquet-partition og skaber en faktatabel. Ud over fysiske kildekolonner udleder den et forretningsmål og gemmer den oprindelige filsti for 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 og SalesAmount beregnes, mens rækkerne indlæses, og mængdeprædikatet udelukker ugyldige transaktioner. filepath() tilføjer den komplette kilde-URI til hver destinationsrække, hvilket hjælper med at spore en kendsgerning tilbage til filen, der leverede den.

Opret en tabel med SELECT INTO

Følgende sætning opretter en kundeimporttabel ud fra flere CSV-filer. Den renser en kildeværdi og tilføjer både kildefil- og load-time metadata:

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;

Jokeren kombinerer alle matchende CSV-filer i én relationel kilde. HEADER_ROW = TRUE læser kolonnenavne fra første række i hver fil. TRIM standardiserer kundenavne, filepath() registrerer rækkelinje og GETDATE() registrerer, hvornår lageret har indtaget rækken. Null-nøgle-prædikatet forhindrer ubrugelige kundeposter i at komme ind i den nye tabel.

Tilføj en inkrementel batch med INSERT ... SELECT

Følgende eksempel indlæser udvalgte JSONL-partitioner i en eksisterende tabel. Udover de fysiske ordensegenskaber indlæser den året og måneden, der er kodet i mappestien, og fanger filens oprindelse og indtastningstid:

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

Det første og andet wildcard matcher year og-mappeværdierne month . filepath(1) og filepath(2) eksponere disse partitionsværdier som tegndata, så forespørgslen kaster dem til smallint før de gemmes som OrderYear og OrderMonth. filepath() uden argument gemmer den komplette kildesti. Jokerkarterne holder forespørgslen genanvendelig, mens prædikaterne vælger det aktuelle batch og tillader OPENROWSET at springe ikke-matchende mapper over.

Synkroniser en filbatch med MERGE

Brug MERGE når en filbatch kan indeholde både nye og ændrede rækker. Følgende eksempel læser de aktuelle produktfiler, standardiserer beskrivende værdier og medfører et tidsstempel for kildemodifikation i matchet:

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

Forespørgslen opdaterer kun et produkt, når filen indeholder en nyere version, og indsætter produkt-ID'er, der ikke er i dimensionen. Sørg for, at filsættet returnerer højst én række for hver ProductID. Tilføj WHEN NOT MATCHED BY SOURCE THEN DELETE kun, når stien repræsenterer et komplet autoritativt snapshot, ikke en inkrementel batch.

Læs filer fra OneLake

Brug hele OneLake-URL'en til lejlighedsvise forespørgsler. Dette eksempel indlæser en årlig partition og gemmer OneLake-URI'en ved hver taxatur:

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;

URL'en identificerer arbejdsområdet, lakehouse, /Files området og den opdelte mappe. Månedens wildcard læser alle månedlige filer for 2026, mens filepath() den bevarer den præcise OneLake-fil, der producerede hver destinationsrække. For genanvendelige forespørgsler, definer en ekstern datakilde ved lakehouse-roden, så workspace- og lakehouse-identiceller vedligeholdes i ét objekt:

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

SalesFilesRoot er et vedvarende objekt i det nuværende warehouse og kræver tilladelse til at oprette eksterne datakilder. Det bliver et genanvendeligt navn for OneLake-roden, så en lokationsændring kan foretages én gang i stedet for i hver eneste indlæsningsforespørgsel.

Du kan derefter bruge en relativ sti, mens du bevarer samme wildcard- og partitionsadfærd:

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

DATA_SOURCE kombinerer den lagrede OneLake-rod med den relative /Files sti. Dette holder indlæsningsforespørgsler fokuseret på mapper, partitioner og transformationslogik i stedet for gentagne lokationsidentinere.

Vælg OPENROWSET eller COPY

Brug OPENROWSET med en T-SQL-indlæsningssætning, når belastningen inkluderer filtrering, joins, beregninger, skemaformning eller synkronisering. For en direkte kilde-til-destination bulk-belastning uden transformationer, brug COPY-sætningen.

Overvågning af indtagelse

Filindlæsningssætninger vises sammen med andre T-SQL-anmodninger i lagerets forespørgselshistorik/aktivitet. Brug forespørgselsindsigter til at inspicere deres eksekveringsdetaljer.

Relateret indhold

  • Sidst opdateret den