Pozyskiwanie i transformacja danych z data lake za pomocą OPENROWSET i T-SQL

Dotyczy:✅ Magazyn w systemie Microsoft Fabric

Korzystając z funkcji OPENROWSET, możesz udostępnić pliki Parquet, CSV lub JSONL jako relacyjne źródło instrukcji ładowania danych T-SQL. Pliki mogą znajdować się w OneLake, Azure Data Lake Storage lub Azure Blob Storage. Poniższy przykład pobiera zawartość pliku Parquet, używając jego absolutnego adresu URL ADLS:

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

Ponieważ OPENROWSET zwraca dane jako zestaw wierszy, możesz użyć go jako źródła dowolnego obsługiwanego polecenia T-SQL ingestion, w tym:

  • CREATE TABLE AS SELECT (CTAS) lub SELECT ... INTO do utworzenia nowej tabeli magazynowej z jednego lub większej liczby plików, do których się odwołano.
  • INSERT ... SELECT do dołączenia zawartości pliku do istniejącej tabeli.
  • MERGE aby wstawić nowe wiersze, zaktualizować istniejące wiersze lub wykonać obie te czynności na podstawie danych z plików, do których się odwołano.

Oprócz pozyskiwania danych możesz zastosować dowolne transformacje T-SQL przed załadowaniem danych do tabeli. Na przykład możesz konwertować typy danych, wyprowadzać nowe wartości kolumn, filtrować wiersze, łączyć dane plikowe z istniejącymi tabelami oraz wykonywać agregacje w ramach procesu ingestii.

Prerequisites

  • Magazyn Fabric oraz uprawnienia do tworzenia lub modyfikowania tabel docelowych.
  • Dostęp do plików źródłowych używanych w zapytaniu hurtowni danych.

Stwórz tabelę za pomocą CTAS

Poniższa instrukcja CTAS odczytuje roczną partycję Parquet i tworzy tabelę faktów. Oprócz fizycznych kolumn źródłowych wyprowadza miarę biznesową i przechowuje ścieżkę pliku pochodzenia na potrzeby śledzenia pochodzenia danych:

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 i SalesAmount są obliczane podczas ładowania wierszy, a predykat ilości wyklucza nieprawidłowe transakcje. filepath() dodaje kompletne źródłowe URI do każdego wiersza docelowego, co pomaga prześledzić fakt do pliku, który go dostarczył.

Utwórz tabelę za pomocą SELECT INTO

Poniższa instrukcja tworzy tabelę importu klientów z wielu plików CSV. Czyści wartość źródłową i dodaje metadane zarówno pliku źródłowego, jak i czasu ładowania:

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;

Symbol wieloznaczny łączy wszystkie pasujące pliki CSV w jedno źródło relacyjne. HEADER_ROW = TRUE odczytuje nazwy kolumn z pierwszego wiersza każdego pliku. TRIM standaryzuje nazwy klientów, filepath() rejestruje pochodzenie wiersza oraz GETDATE() rejestruje, kiedy hurtownia przyjęła ten wiersz. Predykat klucza null zapobiega wnoszeniu nieużytecznych rekordów klientów do nowej tabeli.

Dodaj partię przyrostową z INSERT ... SELECT

Poniższy przykład ładuje wybrane partycje JSONL do istniejącej tabeli. Poza fizycznymi właściwościami kolejności, pobiera rok i miesiąc zakodowane w ścieżce folderu oraz rejestruje pochodzenie pliku i czas pozyskania:

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

Pierwszy i drugi symbol wieloznaczny odpowiadają wartościom folderów year i month. filepath(1) i filepath(2) udostępniają te wartości partycji jako dane znakowe, więc zapytanie rzutuje je na smallint przed zapisaniem jako OrderYear i OrderMonth. Polecenie filepath() bez argumentu przechowuje pełną ścieżkę źródłową. Znaki wieloznaczne pozwalają na ponowne użycie zapytania, natomiast predykaty wybierają aktualną partię i pozwalają elementowi OPENROWSET pominąć niepasujące foldery.

Synchronizuj partię plików z MERGE

Używaj, MERGE gdy partia plików może zawierać zarówno nowe, jak i zmienione wiersze. Poniższy przykład odczytuje aktualne pliki produktu, standaryzuje wartości opisowe i przenosi znacznik czasu modyfikacji źródła do dopasowanego rekordu:

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

Zapytanie aktualizuje produkt tylko wtedy, gdy plik zawiera nowszą wersję i wstawia identyfikatory produktu, które nie znajdują się w wymiarze. Upewnij się, że zbiór plików zwraca nie więcej niż jeden wiersz dla każdego ProductID. Dodawaj WHEN NOT MATCHED BY SOURCE THEN DELETE tylko wtedy, gdy ścieżka reprezentuje pełną, autorytatywną migawkę, a nie partię przyrostową.

Przeczytaj pliki z OneLake

Do okazjonalnych zapytań używaj pełnego adresu URL OneLake. Ten przykład ładuje roczną partycję i przechowuje URI OneLake przy każdej podróży taksówką:

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;

Adres URL identyfikuje przestrzeń roboczą, lakehouse, /Files obszar oraz folder partycjonowany. Symbol wieloznaczny miesiąca odczytuje wszystkie miesięczne pliki w 2026 roku, jednocześnie filepath() zachowując dokładnie ten plik OneLake, który wygenerował każdy wiersz docelowy. Dla zapytań wielokrotnego użytku zdefiniuj zewnętrzne źródło danych w korzeniu lakehouse, aby identyfikatory workspace i lakehouse były utrzymywane w jednym obiekcie:

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

SalesFilesRoot jest obiektem trwałym w aktualnej hurtowni i wymaga uprawnień do tworzenia zewnętrznych źródeł danych. Staje się to nazwą wielokrotnego użycia dla katalogu głównego OneLake, dzięki czemu zmiana lokalizacji może być wykonana raz, zamiast w każdym zapytaniu pozyskiwania danych.

Możesz wtedy użyć ścieżki względnej, zachowując takie samo zachowanie symboli wieloznacznych i partycji:

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

DATA_SOURCE łączy zapisany korzeń OneLake z relatywną /Files ścieżką. Dzięki temu zapytania ingestii danych koncentrują się na folderach, partycjach i logice transformacji, zamiast powtarzać identyfikatory lokalizacji.

Wybierz OPENROWSET lub COPY

Używaj OPENROWSET z instrukcją pozyskiwania T-SQL, gdy ładowanie obejmuje filtrowanie, sprzężenia, obliczenia, kształtowanie schematu lub synchronizację. Dla bezpośredniego ładowania zbiorczego z źródła do celu bez transformacji użyj instrukcji COPY.

Monitorowanie wprowadzania danych

Instrukcje ładowania plików pojawiają się wraz z innymi żądaniami T-SQL w historii/aktywności zapytań magazynu. Wykorzystaj query insights, aby sprawdzić szczegóły ich realizacji.