Wykonywanie zapytań o pliki przy użyciu bezserwerowej puli SQL

  • 10 min

Pulę SQL bezserwerową można używać do wykonywania zapytań dotyczących plików danych w różnych typowych formatach plików, w tym:

  • Rozdzielany tekst, taki jak pliki wartości rozdzielane przecinkami (CSV).
  • Pliki notacji obiektów JavaScript (JSON).
  • Pliki Parquet.

Podstawowa składnia wykonywania zapytań jest taka sama dla wszystkich tych typów plików i jest oparta na funkcji SQL OPENROWSET; który generuje tabelaryczny zestaw wierszy na podstawie danych w co najmniej jednym pliku. Na przykład następujące zapytanie może służyć do wyodrębniania danych z plików CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

Funkcja OPENROWSET zawiera więcej parametrów, które określają takie czynniki jak:

  • Schemat wynikowego zestawu wierszy
  • Dodatkowe opcje formatowania dla rozdzielonych plików tekstowych.

Napiwek

Pełna składnia funkcji OPENROWSET znajduje się w dokumentacji usługi Azure Synapse Analytics.

Dane wyjściowe z zestawu OPENROWSET to zestaw wierszy, do którego należy przypisać alias. W poprzednim przykładzie wiersze aliasu są używane do nazywania wynikowego zestawu wierszy.

Parametr BULK zawiera pełny adres URL do lokalizacji w usłudze Data Lake zawierającej pliki danych. Może to być pojedynczy plik lub folder z wyrażeniem wieloznacznymi, aby filtrować typy plików, które powinny być uwzględnione. Parametr FORMAT określa typ zapytań dotyczących danych. Powyższy przykład odczytuje rozdzielany tekst ze wszystkich plików .csv w folderze plików .

Uwaga

W tym przykładzie przyjęto założenie, że użytkownik ma dostęp do plików w magazynie bazowym, jeśli pliki są chronione przy użyciu klucza SYGNATURy dostępu współdzielonego lub tożsamości niestandardowej, należy utworzyć poświadczenia o zakresie serwera.

Jak pokazano w poprzednim przykładzie, możesz użyć symboli wieloznacznych w parametrze BULK , aby uwzględnić lub wykluczyć pliki w zapytaniu. Na poniższej liście przedstawiono kilka przykładów tego, jak można tego użyć:

  • https://mydatalake.blob.core.windows.net/data/files/file1.csv: dołącz tylko file1.csv w folderze plików .
  • https://mydatalake.blob.core.windows.net/data/files/file*.csv: wszystkie pliki .csv w folderze plików o nazwach rozpoczynających się od "file".
  • https://mydatalake.blob.core.windows.net/data/files/*: wszystkie pliki w folderze plików .
  • https://mydatalake.blob.core.windows.net/data/files/**: wszystkie pliki w folderze plików i rekursywnie jego podfoldery.

Można również określić wiele ścieżek plików w parametrze BULK , oddzielając każdą ścieżkę przecinkami.

Wykonywanie zapytań dotyczących rozdzielonych plików tekstowych

Rozdzielane pliki tekstowe są typowym formatem plików w wielu firmach. Określone formatowanie używane w plikach rozdzielonych może się różnić, na przykład:

  • Z wierszem nagłówka i bez go.
  • Wartości rozdzielane przecinkami i tabulatorami.
  • Zakończenia linii stylu systemu Windows i Unix.
  • Niecytowane i cytowane wartości oraz znaki ucieczki.

Niezależnie od typu pliku rozdzielanego, którego używasz, można odczytywać z nich dane przy użyciu funkcji OPENROWSET z parametrem CSV FORMAT i innymi parametrami wymaganymi do obsługi określonych szczegółów formatowania danych. Na przykład:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION służy do określania, w jaki sposób zapytanie interpretuje kodowanie tekstu używane w plikach. Wersja 1.0 jest domyślna i obsługuje szeroką gamę kodowań plików, podczas gdy wersja 2.0 obsługuje mniej kodowań, ale oferuje lepszą wydajność. Parametr FIRSTROW służy do pomijania wierszy w pliku tekstowym, aby wyeliminować dowolny tekst preambuły bez struktury lub zignorować wiersz zawierający nagłówki kolumn.

Podczas pracy z rozdzielanymi plikami tekstowymi mogą być wymagane dodatkowe parametry:

  • FIELDTERMINATOR — znak używany do oddzielania wartości pól w każdym wierszu. Na przykład plik rozdzielany tabulatorami oddziela pola znakiem TAB (\t). Domyślnym terminatorem pola jest przecinek (,).
  • ROWTERMINATOR — znak używany do oznaczania końca wiersza danych. Na przykład standardowy plik tekstowy systemu Windows używa kombinacji powrotu karetki (CR) i zestawienia wiersza (LF), który jest wskazywany przez kod \n; podczas gdy pliki tekstowe w stylu system UNIX używają pojedynczego znaku informacyjnego wiersza, który można wskazać przy użyciu 0x0a kodu.
  • FIELDQUOTE — znak używany do ujęć w cudzysłów wartości ciągów. Na przykład, aby upewnić się, że przecinek w wartości pola adresu 126 Main St, apt 2 nie jest interpretowany jako ogranicznik pola, można ująć całą wartość pola w cudzysłowy w następujący sposób: "126 Main St, apt 2". Podwójny cudzysłów (") jest domyślnym znakiem cudzysłowu pola.

Napiwek

Aby uzyskać szczegółowe informacje o dodatkowych parametrach podczas pracy z rozdzielanymi plikami tekstowymi, zapoznaj się z dokumentacją usługi Azure Synapse Analytics.

Określanie schematu zestawu wierszy

Pliki tekstowe rozdzielane często zawierają nazwy kolumn w pierwszym wierszu. Funkcja OPENROWSET może użyć tej funkcji do zdefiniowania schematu dla wynikowego zestawu wierszy i automatycznego wnioskowania typów danych kolumn na podstawie wartości, które zawierają. Rozważmy na przykład następujący tekst rozdzielany:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Dane składają się z następujących trzech kolumn:

  • product_id (liczba całkowita)
  • product_name (ciąg)
  • list_price (liczba dziesiętna)

Poniższe zapytanie służy do wyodrębniania danych z poprawnymi nazwami kolumn i odpowiednio wnioskowanymi typami danych programu SQL Server (w tym przypadku INT, NVARCHAR i DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Parametr HEADER_ROW (który jest dostępny tylko w przypadku korzystania z analizatora w wersji 2.0) instruuje aparat zapytań, aby używał pierwszego wiersza danych w każdym pliku jako nazw kolumn, w następujący sposób:

product_id product_name list_price
123 Widget 12.9900
124 Gadżet 3.9900

Teraz rozważ następujące dane:

123,Widget,12.99
124,Gadget,3.99

Tym razem plik nie zawiera nazw kolumn w wierszu nagłówka; dlatego mimo że typy danych nadal mogą być wnioskowane, nazwy kolumn zostaną ustawione na C1, C2, C3 itd.

C1 C2 C3
123 Widget 12.9900
124 Gadżet 3.9900

Aby określić jawne nazwy kolumn i typy danych, można zastąpić domyślne nazwy kolumn i wywnioskowane typy danych, podając definicję schematu w klauzuli WITH , w następujący sposób:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

To zapytanie generuje oczekiwane wyniki:

product_id product_name list_price
123 Widget 12.99
124 Gadżet 3.99

Napiwek

Podczas pracy z plikami tekstowymi może wystąpić niezgodność z zakodowanymi danymi UTF-8 i sortowaniem używanym w bazie danych master dla bezserwerowej puli SQL. Aby rozwiązać ten problem, można określić zgodne sortowanie dla poszczególnych kolumn VARCHAR w schemacie. Aby uzyskać więcej informacji, zobacz wskazówki dotyczące rozwiązywania problemów.

Wykonywanie zapytań dotyczących plików JSON

JSON to popularny format dla aplikacji internetowych, które wymieniają dane za pośrednictwem interfejsów REST lub używają magazynów danych NoSQL, takich jak Azure Cosmos DB. Nie jest więc rzadkością utrwalanie danych jako dokumentów JSON w plikach w usłudze Data Lake na potrzeby analizy.

Na przykład plik JSON definiujący pojedynczy produkt może wyglądać następująco:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Aby zwrócić dane produktu z folderu zawierającego wiele plików JSON w tym formacie, można użyć następującego zapytania SQL:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Zestaw OPENROWSET nie ma określonego formatu dla plików JSON, dlatego należy użyć formatu CSV z parametrem FIELDTERMINATOR, FIELDQUOTE i ROWTERMINATOR ustawionym na 0x0b oraz schematem zawierającym jedną kolumnę NVARCHAR(MAX). Wynikiem tego zapytania jest zestaw wierszy zawierający jedną kolumnę dokumentów JSON, w następujący sposób:

Dok
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadżet","list_price": 3.99}

Aby wyodrębnić poszczególne wartości z formatu JSON, możesz użyć funkcji JSON_VALUE w instrukcji SELECT, jak pokazano poniżej:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

To zapytanie zwróci zestaw wierszy podobny do następujących wyników:

product price
Widget 12.99
Gadżet 3.99

Wykonywanie zapytań dotyczących plików Parquet

Parquet to powszechnie używany format przetwarzania danych big data w rozproszonym magazynie plików. Jest to wydajny format danych zoptymalizowany pod kątem kompresji i zapytań analitycznych.

W większości przypadków schemat danych jest osadzony w pliku Parquet, więc wystarczy określić parametr BULK ze ścieżką do plików, które chcesz odczytać, oraz parametr FORMAT parquet; w następujący sposób:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Wykonywanie zapytań dotyczących danych partycjonowanych

Często w usłudze Data Lake dane są dzielone między wiele plików w podfolderach, które odzwierciedlają kryteria partycjonowania. Dzięki temu systemy przetwarzania rozproszonego działają równolegle na wielu partycjach danych lub łatwo eliminują odczyty danych z określonych folderów na podstawie kryteriów filtrowania. Załóżmy na przykład, że musisz efektywnie przetwarzać dane zamówień sprzedaży i często trzeba filtrować na podstawie roku i miesiąca, w którym zostały złożone zamówienia. Dane można podzielić na partycje przy użyciu folderów, w następujący sposób:

  • /Zamówienia
    • /year=2020
      • /month=1
        • /01012020.parquet
        • /02012020.parquet
        • ...
      • /month=2
        • /01022020.parquet
        • /02022020.parquet
        • ...
      • ...
    • /year=2021
      • /month=1
        • /01012021.parquet
        • /02012021.parquet
        • ...
      • ...

Aby utworzyć zapytanie, które filtruje wyniki w celu uwzględnienia tylko zamówień dla stycznia i lutego 2020 r., możesz użyć następującego kodu:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Parametry ścieżki plików numerowanych w klauzuli WHERE odwołują się do symboli wieloznacznych w nazwach folderów w ścieżce BULK - więc parametr 1 jest * w nazwie folderu year=* i parametr 2 jest * w nazwie folderu month=* .