Udostępnij przez

Wykonywanie zapytań względem zewnętrznych plików typu data lake

Dotyczy:✅ końcowego punktu analizy SQL i magazynu danych w usłudze Microsoft Fabric

Usługa Fabric Data Warehouse i punkt końcowy analizy SQL umożliwiają wykonywanie zapytań dotyczących danych przechowywanych w plikach w usłudze Data Lake przy użyciu kodu Transact-SQL (T-SQL).

Udostępniają znany obszar powierzchni zapytań T-SQL, który obsługuje wykonywanie zapytań dotyczących danych ustrukturyzowanych, częściowo ustrukturyzowanych i nieustrukturyzowanych. W usłudze Fabric Data Warehouse i punkcie końcowym analizy SQL można wykonywać zapytania dotyczące różnych formatów plików, takich jak Parquet, CSV i JSONL.

OPENROWSET

Uproszczona składnia OPENROWSET funkcji to:

OPENROWSET( BULK '{file path}', [ options...] )
   [ WITH ( {result set column schema} ) ]

OPENROWSET program obsługuje trzy sposoby określania lokalizacji pliku:

  • Ścieżka bezwzględna zawierająca pełny adres URL do pliku, który chcesz odczytać. Jest to najprostsze podejście, gdy znasz dokładną lokalizację pliku.
  • Ścieżka względna ze źródłem danych — ścieżka względna jest dołączana do lokalizacji głównej zdefiniowanej w źródle danych w celu utworzenia pełnej ścieżki. Źródło danych może wskazywać zewnętrzny adres URL zewnętrznego magazynu danych (na przykład Azure Blob, ADLS Gen2) lub główny adres URL Lakehouse na platformie Fabric.
  • Ścieżka względna rozpoczynająca się od /Files — ta opcja działa tylko w przypadku wykonywania zapytań względem usługi Lakehouse za pośrednictwem punktu końcowego analizy SQL. Folder /Files reprezentuje obszar plików Lakehouse i można użyć ścieżek względnych bez definiowania źródła danych.

Pliki można umieścić w jednej z następujących opcji przechowywania:

  • Azure Data Lake Storage (ADLS) — skalowalna, hierarchiczna usługa magazynu w chmurze zoptymalizowana pod kątem obciążeń analizy danych big data.
  • Azure Blob Storage — usługa magazynu obiektów ogólnego przeznaczenia do przechowywania dużych ilości danych bez struktury, takich jak pliki, obrazy i dzienniki.
  • Fabric OneLake — natywny magazyn danych jeziorowych dla usługi Microsoft Fabric, który zapewnia ujednolicone, logiczne jezioro danych dla wszystkich obciążeń usługi Fabric. Usługa OneLake umożliwia również pośredni dostęp do danych przechowywanych w lokalizacjach zewnętrznych, w tym:
    • Amazon S3 — usługa magazynu obiektów zapewniana przez usługi Amazon Web Services.
    • Google Cloud Storage (GCS) — usługa magazynu obiektów dla platformy Google Cloud Platform.
    • SharePoint — usługa wspólnego przechowywania dokumentów i plików na platformie Microsoft 365.
    • OneDrive — osobista usługa magazynu w chmurze dla plików na platformie Microsoft 365.

Te możliwości umożliwiają elastyczne, oparte na języku T-SQL wykonywanie zapytań na danych przechowywanych w wielu systemach magazynowania w chmurze i SaaS, eliminując konieczność wcześniejszego pozyskiwania lub przekształcania danych przed analizą.

Odpowiednie opcje OPENROWSET

Użyj opcji w pliku , OPENROWSET aby opisać format pliku źródłowego i kontrolować sposób analizowania danych. Te ustawienia są szczególnie ważne w przypadku rozdzielanych plików tekstowych, w których należy zdefiniować sposób oddzielania wierszy i kolumn.

Dostępne są typowe opcje:

  • FIELDTERMINATOR, ROWTERMINATORi FIELDQUOTE — określ znaki oddzielające pola i wiersze. Te opcje zapewniają dokładne analizowanie kolumn i rekordów. Obsługują one również wartości cytowane w rozdzielanych plikach, aby zachować tekst zawierający ograniczniki.
  • HEADER_ROW i FIRSTROW — wskazuje, czy plik zawiera wiersz nagłówka i zdefiniuj wiersz, który powinien być traktowany jako pierwszy wiersz danych.
  • CODEPAGE - Ustaw kodowanie znaków, aby poprawnie interpretować znaki specjalne i tekst inny niż ASCII.

Te opcje zapewniają elastyczność pracy z różnymi formatami plików i zapewniają poprawne odczytywanie danych niezależnie od różnic w strukturze lub kodowaniu.

Klauzula opcjonalna WITH w programie OPENROWSET umożliwia zdefiniowanie jawnego schematu dla danych. Udostępnianie schematu jest przydatne, gdy nie chcesz OPENROWSET automatycznie wnioskować schematu z plików bazowych. Użyj klauzuli WITH , aby zastąpić automatyczne wnioskowanie schematu i zdefiniować dokładnie sposób projekcji danych w formie tabelarycznej.

Przypadki użycia OPENROWSET

Aby zapewnić wygodne przeprowadzanie zapytań w miejscu dla danych przechowywanych w usłudze Data Lake, usługa Fabric Data Warehouse i punkt końcowy analizy SQL używają funkcji OPENROWSET do odwoływania się do plików i odczytywania ich zawartości.

Funkcja OPENROWSET oferuje zaawansowane możliwości wykonywania zapytań dotyczących plików, w tym:

Wykonywanie zapytań na plikach PARQUET z użyciem funkcji OPENROWSET

Parquet to format pliku kolumnowego zoptymalizowany pod kątem obciążeń analitycznych. Przechowuje dane według kolumn, a nie wierszy, co umożliwia wydajną kompresję, zmniejszenie operacji we/wy i szybszą wydajność zapytań, zwłaszcza w przypadku wykonywania zapytań względem podzestawu kolumn.

Funkcja OPENROWSET umożliwia łatwy i intuicyjny dostęp do plików Parquet bezpośrednio z kodu T-SQL.

Aby wysłać zapytanie do pliku Parquet, podaj adres URL pliku Parquet przy użyciu OPENROWSET funkcji :

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

Funkcja OPENROWSET zwraca każdy wiersz z pliku Parquet jako wiersz w zestawie wyników. Kolumny w zestawie wyników są zgodne ze schematem pliku Parquet.

Aby znaleźć przykłady użycia, zobacz Zapytania dotyczące plików Parquet.

Wykonywanie zapytań dotyczących plików rozdzielanych za pomocą zestawu OPENROWSET

Format tekstowy rozdzielany to tekstowy format pliku, który często stosuje się do wymiany danych i lekkiego przechowywania danych.

  • Dane są uporządkowane w wierszach oddzielonych terminatorem wierszy.
  • Wiele komórek w każdym wierszu jest rozdzielonych separatorem pól.
  • Wartości w komórkach są oddzielone ogranicznikami, takimi jak przecinki lub karty.

Rozdzielane pliki są powszechnie obsługiwane i proste do tworzenia w wielu systemach i narzędziach.

Najczęściej używanym formatem rozdzielanym jest format wartości rozdzielanych przecinkami (CSV). W pliku CSV wiersze są rozdzielane przez nowy wiersz, a wartości według przecinka opcjonalnie ujęte w cudzysłowy podwójne. Istnieje jednak wiele odmian, takich jak wartości rozdzielane tabulatorami (TSV) i inne formaty o niestandardowych separatorach.

Za pomocą programu OPENROWSETmożna uzyskiwać dostęp do plików rozdzielanych bezpośrednio z języka T-SQL w prosty i elastyczny sposób. Takie podejście umożliwia wykonywanie zapytań w miejscu bez konieczności uprzedniego ładowania danych do tabel bazy danych.

Aby wysłać zapytanie do pliku rozdzielanego, podaj adres URL pliku i zdefiniuj odpowiednie opcje analizowania podczas korzystania z OPENROWSET funkcji:

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.csv');

Funkcja OPENROWSET zwraca każdy wiersz z pliku rozdzielanego jako wiersz w zestawie wyników. Kolumny w zestawie wyników są zgodne ze strukturą rozdzielanego pliku.

Można dostosować sposób analizowania rozdzielonych plików tekstowych (takich jak CSV, TSV lub inne warianty), określając opcje, takie jak terminatory pól, terminatory wierszy, znaki ucieczki i inne ustawienia związane z formatem, aby dopasować strukturę plików.

Przykłady użycia można znaleźć w temacie Query delimited text files (Rozdzielane pliki tekstowe zapytań).

Wykonywanie zapytań dotyczących plików JSONL przy użyciu zestawu OPENROWSET

JSON Lines (JSONL) to rozdzielany wierszami format pliku częściowo ustrukturyzowanego, w którym każdy wiersz zawiera prawidłowy obiekt JSON. Ta struktura sprawia, że format JSONL jest szczególnie odpowiedni dla obciążeń strumieniowych, danych zdarzeń i obciążeń związanych z dołączaniem, ponieważ nowe rekordy można zapisywać wydajnie bez przepisywania całego pliku.

Za pomocą OPENROWSET funkcji można wykonywać zapytania dotyczące plików JSONL bezpośrednio z języka T-SQL. Możesz analizować dane przesyłane strumieniowo i stale generowane bez konieczności ich wcześniejszego ładowania do tabel bazy danych.

Aby wysłać zapytanie do pliku JSONL, podaj adres URL pliku podczas korzystania z OPENROWSET funkcji:

SELECT * FROM
OPENROWSET( BULK '/mysubfolder/data.jsonl', DATA_SOURCE='MyStorage');

Podczas wykonywania zapytań dotyczących plików JSON Lines (JSONL) każdy obiekt JSON w pliku jest traktowany jako oddzielny wiersz w zestawie wyników.

Każda właściwość w obiekcie JSON jest zwracana jako pojedyncza kolumna, umożliwiając naturalny widok relacyjny danych JSON rozdzielanych wierszami.

Przykłady użycia można znaleźć w temacie Zapytania dotyczące plików JSONL.

Odczyt schematu

Usługa Fabric Data Warehouse umożliwia deweloperom SQL stosowanie schematu w czasie wykonywania zapytań podczas odczytywania danych bezpośrednio z plików przechowywanych w usłudze Data Lake.

To podejście do odczytu schematu umożliwia zachowanie danych w oryginalnym formacie, podczas gdy jego struktura jest definiowana dynamicznie podczas wykonywania zapytań. Można wybrać między dwoma modelami schema-on-read:

  • Automatyczne wnioskowanie schematu, w którym sieć szkieletowa analizuje zawartość pliku i automatycznie określa nazwy kolumn i typy danych.
  • Jawna definicja schematu, w której schemat jest w pełni zdefiniowany w zapytaniu w celu kontrolowania nazw kolumn i typów danych.

Automatyczne wnioskowanie schematu

Automatyczne wnioskowanie schematu umożliwia wykonywanie zapytań o pliki bez podawania jawnej definicji schematu.

Usługa Fabric Data Warehouse i punkt końcowy analizy SQL automatycznie badają pliki źródłowe w celu identyfikowania nazw kolumn i typów danych. Używają metadanych na poziomie plików, takich jak nagłówki plików Parquet, lub analizują reprezentatywne przykłady danych dla formatów, takich jak CSV i JSONL.

W przypadku pominięcia klauzuli WITH z instrukcji OPENROWSET usługa Fabric Data Warehouse automatycznie analizuje pliki bazowe i uzyskuje nazwy kolumn i typy danych w czasie wykonywania zapytań.

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

Takie podejście jest szczególnie przydatne w przypadku szybkiej eksploracji, ewoluujących schematów lub scenariuszy, w których struktura plików jest zarządzana poza magazynem danych. Wywnioskując schemat dynamicznie, można skoncentrować się na wysyłaniu zapytań dotyczących danych bez uprzedniego definiowania lub utrzymywania stałego schematu.

Jawna definicja schematu

W przypadku jawnej definicji schematu deweloperzy SQL kontrolują sposób mapowania danych plików na kolumny relacyjne, określając nazwy kolumn, typy danych i, jeśli ma to zastosowanie, pozycje kolumn w plikach źródłowych.

To podejście zapewnia precyzyjne i przewidywalne mapowanie podczas wykonywania zapytań dotyczących plików przy użyciu polecenia OPENROWSET.

Aby zdefiniować schemat, dodaj klauzulę opcjonalną WITH do instrukcji OPENROWSET .

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.parquet') 
WITH (
      Column1 int, 
      Column2 varchar(20),
      Column3 varchar(max)
);

Użyj jawnej definicji schematu, gdy potrzebujesz ścisłej kontroli nad mapowaniem kolumn i typami danych lub podczas pracy z ewoluującymi lub luźno ustrukturyzowanymi plikami źródłowymi.

Wykonywanie zapytań dotyczących wielu plików lub folderów za pomocą zestawu OPENROWSET

Aby wykonywać zapytania dotyczące danych w wielu plikach lub folderach, określ ścieżkę pliku zawierającą co najmniej jeden znak wieloznaczny (*).

Za pomocą symboli wieloznacznych pojedyncze zapytanie T-SQL może działać w dynamicznym zestawie plików, które pasują do wzorca nazewnictwa lub katalogu.

W przypadku używania symboli wieloznacznych w ścieżkach plików obowiązują następujące reguły:

  • Symbol * wieloznaczny reprezentuje jeden lub wiele znaków i można go używać w ścieżkach katalogu, a także w nazwach plików.
  • Można określić wiele * symboli wieloznacznych w tej samej ścieżce lub nazwie pliku, aby dopasować złożone wzorce.
  • Gdy ścieżka kończy się cyklicznymi symbolami wieloznacznymi (na przykład /**), zestaw wyników zapytania zawiera wszystkie pliki znajdujące się w określonym folderze głównym i jego podfolderach.

W poniższym przykładzie pokazano, jak używać wzorców wieloznacznych w ścieżce pliku w celu wykonywania zapytań dotyczących wielu plików Parquet między folderami w jednym zapytaniu:

SELECT * FROM
OPENROWSET( BULK '/myroot/*/mysubfolder/*.parquet', DATA_SOURCE='MyStorage');

Funkcje metadanych pliku

OPENROWSET Zawiera funkcje metadanych, których można użyć do uzyskiwania dostępu do informacji o plikach, których wykonujesz zapytanie:

  • Funkcja filename() zwraca nazwę pliku, z którego pochodzą poszczególne wiersze. Ta funkcja służy do filtrowania lub wykonywania zapytań dotyczących określonych plików. Aby uzyskać lepszą wydajność, rzutuj wynik na odpowiedni typ i długość danych.
  • Funkcja filepath() zwraca ścieżkę pliku, z której pochodzą poszczególne wiersze.
    • Bez parametrów zwraca pełną ścieżkę/identyfikator URI pliku.
    • Za pomocą parametru zwraca segment ścieżki zgodny z określoną pozycją symbolu wieloznacznego.

W poniższym przykładzie pokazano zapytanie, które pobiera zawartość pliku wraz z pełnym identyfikatorem URI i nazwą pliku dla każdego pliku, zwracając tylko pliki z /year=2025/month=10 folderu.

SELECT 
 rows.filepath(),
 rows.filename(),
 rows.filepath(2) AS [month],
 rows.*
FROM 
OPENROWSET(
  BULK 'https://myaccount.dfs.core.windows.net/myroot/year=2025/month=*/*.parquet'
  ) AS rows
WHERE rows.filepath(1) = '2025'

Aby uzyskać więcej informacji na temat filepath() i filename() funkcji, zobacz Funkcje metadanych plików.

Treści powiązane

  • Last updated on