Udostępnij za pośrednictwem

Wykonywanie zapytań dotyczących plików magazynu za pomocą bezserwerowej puli SQL w usłudze Azure Synapse Analytics

  • Artykuł

Bezserwerowa pula SQL umożliwia wykonywanie zapytań o dane w usłudze Data Lake. Oferuje ona obszar powierzchni zapytań T-SQL, który obsługuje częściowo ustrukturyzowane i nieustrukturyzowane zapytania dotyczące danych. W przypadku wykonywania zapytań obsługiwane są następujące aspekty języka T-SQL:

Aby uzyskać więcej informacji na temat tego, co jest w porównaniu z tym, co nie jest obecnie obsługiwane, przeczytaj artykuł Omówienie bezserwerowej puli SQL lub następujące artykuły:

Omówienie

Aby zapewnić bezproblemową obsługę wykonywania zapytań dotyczących danych znajdujących się w plikach usługi Azure Storage, bezserwerowa pula SQL używa funkcji OPENROWSET z dodatkowymi możliwościami:

Wykonywanie zapytań względem plików PARQUET

Aby wysłać zapytanie do danych źródłowych Parquet, użyj formatu = "PARQUET":

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Zapoznaj się z artykułem Query Parquet files (Pliki Parquet zapytań), aby zapoznać się z przykładami użycia.

Wykonywanie zapytań o pliki CSV

Aby wysłać zapytanie do danych źródłowych CSV, użyj formatu = "CSV". Schemat pliku CSV można określić jako część OPENROWSET funkcji podczas wykonywania zapytań dotyczących plików CSV:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Istnieje kilka dodatkowych opcji, których można użyć do dostosowania reguł analizowania do niestandardowego formatu CSv:

  • ESCAPE_CHAR = 'char' Określa znak w pliku, który jest używany do ucieczki siebie i wszystkich wartości ograniczników w pliku. Jeśli znak ucieczki następuje po wartości innej niż sama lub którejkolwiek z wartości ograniczników, znak ucieczki zostanie porzucony podczas odczytywania wartości. Parametr ESCAPE_CHAR zostanie zastosowany niezależnie od tego, czy parametr FIELDQUOTE jest włączony, czy nie jest włączony. Nie będzie używany do ucieczki znaku cudzysłów. Znak cudzysłów musi zostać uniknięta innym znakiem cudzysłów. Znak cudzysłów może pojawić się w wartości kolumny tylko wtedy, gdy wartość jest hermetyzowana znakami cudzysłów.
  • FIELDTERMINATOR ='field_terminator' Określa terminator pola do użycia. Domyślny terminator pola to przecinek (",")
  • ROWTERMINATOR ='row_terminator' Określa terminator wierszy do użycia. Domyślny terminator wiersza jest znakiem nowego wiersza: \r\n.

Format usługi DELTA LAKE zapytań

Aby wysłać zapytanie do danych źródłowych usługi Delta Lake, użyj formatu = "DELTA" i odwołaj się do folderu głównego zawierającego pliki usługi Delta Lake.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Folder główny musi zawierać podfolder o nazwie _delta_log. Zapoznaj się z artykułem dotyczącym formatu usługi Delta Lake, aby zapoznać się z przykładami użycia.

Schemat pliku

Język SQL w usłudze Synapse SQL umożliwia zdefiniowanie schematu pliku w ramach OPENROWSET funkcji i odczytanie wszystkich lub podzestawu kolumn lub próba automatycznego określenia typów kolumn z pliku przy użyciu wnioskowania schematu.

Odczytywanie wybranego podzestawu kolumn

Aby określić kolumny, które chcesz odczytać, możesz podać opcjonalną klauzulę WITH w instrukcji OPENROWSET .

  • Jeśli istnieją pliki danych CSV, aby odczytać wszystkie kolumny, podaj nazwy kolumn i ich typy danych. Jeśli chcesz, aby podzbiór kolumn używał liczb porządkowych, aby wybrać kolumny z plików danych źródłowych według porządkowych. Kolumny będą powiązane z oznaczeniem porządkowym.
  • Jeśli istnieją pliki danych Parquet, podaj nazwy kolumn, które pasują do nazw kolumn w plikach danych źródłowych. Kolumny będą powiązane według nazwy.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows

Dla każdej kolumny należy określić nazwę kolumny i typ klauzuli .WITH Aby zapoznać się z przykładami, zapoznaj się z tematem Odczyt plików CSV bez określania wszystkich kolumn.

Wnioskowanie schematu

Pomijając klauzulę WITH z instrukcji OPENROWSET , możesz poinstruować usługę, aby automatycznie wykrywała (wnioskowanie) schematu z plików bazowych.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

Upewnij się, że odpowiednie typy danych wywnioskowanych są używane w celu uzyskania optymalnej wydajności.

Wykonywanie zapytań dotyczących wielu plików lub folderów

Aby uruchomić zapytanie T-SQL dla zestawu plików w folderze lub zestawie folderów podczas traktowania ich jako pojedynczej jednostki lub zestawu wierszy, podaj ścieżkę do folderu lub wzorca (przy użyciu symboli wieloznacznych) w zestawie plików lub folderów.

Obowiązują następujące zasady:

  • Wzorce mogą być wyświetlane w części ścieżki katalogu lub w nazwie pliku.
  • Kilka wzorców może być wyświetlanych w tym samym kroku katalogu lub nazwie pliku.
  • Jeśli istnieje wiele symboli wieloznacznych, pliki we wszystkich pasujących ścieżkach zostaną uwzględnione w wynikowym zestawie plików.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Zapoznaj się z tematem Query folders and multiple files (Foldery zapytań i wiele plików ), aby zapoznać się z przykładami użycia.

Funkcje metadanych pliku

Nazwa pliku, funkcja

Ta funkcja zwraca nazwę pliku, z którego pochodzi wiersz.

Aby wykonywać zapytania dotyczące określonych plików, przeczytaj sekcję Nazwa pliku w artykule Zapytanie dotyczące plików .

Zwracany typ danych to nvarchar(1024). Aby uzyskać optymalną wydajność, zawsze rzutuj wynik funkcji nazwy pliku do odpowiedniego typu danych. Jeśli używasz typu danych znaków, upewnij się, że jest używana odpowiednia długość.

Filepath, funkcja

Ta funkcja zwraca pełną ścieżkę lub część ścieżki:

  • Po wywołaniu bez parametru zwraca pełną ścieżkę pliku, z którego pochodzi wiersz.
  • Po wywołaniu z parametrem zwraca część ścieżki, która pasuje do symbolu wieloznakowego na pozycji określonej w parametrze . Na przykład wartość parametru 1 zwróci część ścieżki zgodnej z pierwszym symbolem wieloznacznymi.

Aby uzyskać dodatkowe informacje, przeczytaj sekcję Filepath (Ścieżka pliku) artykułu Query specific files (Zapytanie dotyczące plików ).

Zwracany typ danych to nvarchar(1024). Aby uzyskać optymalną wydajność, zawsze rzutuj wynik funkcji filepath do odpowiedniego typu danych. Jeśli używasz typu danych znaków, upewnij się, że jest używana odpowiednia długość.

Praca ze złożonymi typami i zagnieżdżonym lub powtarzającymi się strukturami danych

Aby umożliwić bezproblemowe korzystanie z danych przechowywanych w zagnieżdżonych lub powtarzających się typach danych, takich jak w plikach Parquet, bezserwerowa pula SQL dodała następujące rozszerzenia.

Zagnieżdżone lub powtarzające się dane projektu

Aby projektować dane, uruchom instrukcję SELECT w pliku Parquet zawierającym kolumny zagnieżdżonych typów danych. W danych wyjściowych zagnieżdżone wartości będą serializowane w formacie JSON i zwracane jako typ danych SQL varchar(8000).

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Aby uzyskać bardziej szczegółowe informacje, zapoznaj się z sekcją Project nested or repeated data ( Zagnieżdżone lub powtarzające się dane) artykułu Query Parquet nested types (Zagnieżdżone typy zapytań).

Uzyskiwanie dostępu do elementów z kolumn zagnieżdżonych

Aby uzyskać dostęp do zagnieżdżonych elementów z zagnieżdżonej kolumny, takiej jak struktura, użyj "notacji kropkowej", aby połączyć nazwy pól ze ścieżką. Podaj ścieżkę jako column_name w klauzuli OPENROWSET WITH funkcji.

Przykład fragmentu składni wygląda następująco:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ({'column_name' 'column_type',})
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

Domyślnie OPENROWSET funkcja pasuje do nazwy pola źródłowego i ścieżki z nazwami kolumn podanymi w klauzuli WITH. Dostęp do elementów znajdujących się na różnych poziomach zagnieżdżania w tym samym źródłowym pliku Parquet można uzyskać za pośrednictwem klauzuli WITH.

Zwracane wartości

  • Funkcja zwraca wartość skalarną, taką jak int, decimal i varchar, z określonego elementu, a w określonej ścieżce dla wszystkich typów Parquet, które nie są w zagnieżdżonej grupie Typ.
  • Jeśli ścieżka wskazuje element typu zagnieżdżonego, funkcja zwraca fragment JSON rozpoczynający się od górnego elementu na określonej ścieżce. Fragment JSON jest typu varchar(8000).
  • Jeśli nie można odnaleźć właściwości w określonym column_name, funkcja zwraca błąd.
  • Jeśli nie można odnaleźć właściwości w określonym column_path, w zależności od trybu ścieżki funkcja zwraca błąd w trybie ścisłym lub null w trybie lax.

Aby zapoznać się z przykładami zapytań, zapoznaj się z sekcją Access elements from nested columns (Elementy programu Access z zagnieżdżonych kolumn) w artykule Query Parquet nested types (Typy zagnieżdżone zapytań).

Uzyskiwanie dostępu do elementów z powtarzających się kolumn

Aby uzyskać dostęp do elementów z powtarzającej się kolumny, takiej jak element tablicy lub mapy, użyj funkcji JSON_VALUE dla każdego elementu skalarnego, który należy projektować i udostępniać:

  • Zagnieżdżone lub powtarzające się kolumny jako pierwszy parametr
  • Ścieżka JSON określająca element lub właściwość dostępu jako drugi parametr

Aby uzyskać dostęp do elementów innych niż skalarne z powtarzającej się kolumny, użyj funkcji JSON_QUERY dla każdego elementu nieskalarnego, który należy projektować i udostępniać:

  • Zagnieżdżone lub powtarzające się kolumny jako pierwszy parametr
  • Ścieżka JSON określająca element lub właściwość dostępu jako drugi parametr

Zobacz fragment składni poniżej:

    SELECT
       { JSON_VALUE (column_name, path_to_sub_element), }
       { JSON_QUERY (column_name [ , path_to_sub_element ]), )
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Przykłady zapytań umożliwiające uzyskiwanie dostępu do elementów z powtarzających się kolumn można znaleźć w artykule Query Parquet nested types (Typy zagnieżdżone zapytań).

Następne kroki

Aby uzyskać więcej informacji na temat wykonywania zapytań dotyczących różnych typów plików oraz tworzenia i używania widoków, zobacz następujące artykuły: