Udostępnij za pośrednictwem

Wykonywanie zapytań dotyczących plików magazynu przy użyciu bezserwerowej puli SQL

  • Artykuł

Bezserwerowa pula SQL umożliwia wykonywanie zapytań o dane w usłudze Data Lake. Oferuje ona obszar powierzchni zapytań języka Transact-SQL (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 obecnie obsługiwane, zapoznaj się z omówieniem bezserwerowej puli SQL lub następującymi artykułami:

Omówienie

Aby zapewnić bezproblemowe środowisko wykonywania zapytań dotyczących danych znajdujących się w plikach usługi Azure Storage, bezserwerowa pula SQL używa funkcji OPENROWSET z większą częścią możliwości:

Wykonywanie zapytań względem plików PARQUET

Aby wysłać zapytanie do danych źródłowych Parquet, użyj polecenia FORMAT = '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

Przykłady użycia można znaleźć w temacie Query Parquet files (Tworzenie zapytań w plikach Parquet).

Wykonywanie zapytań o pliki CSV

Aby wysłać zapytanie do danych źródłowych CSV, użyj polecenia FORMAT = 'CSV'. Schemat pliku CSV można określić w ramach 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óre mogą służyć do dostosowywania 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 jest stosowany niezależnie od tego, czy FIELDQUOTE parametr jest włączony, czy nie jest włączony. Nie służy do ucieczki od 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, który ma być używany. Domyślnym terminatorem pola jest przecinek (,).
  • ROWTERMINATOR ='row_terminator' Określa terminator wierszy do użycia. Domyślny terminator wierszy jest znakiem nowego wiersza (\r\n).

Format usługi DELTA LAKE zapytań

Aby wykonywać zapytania dotyczące danych źródłowych usługi Delta Lake, użyj FORMAT = 'DELTA' folderu głównego zawierającego pliki usługi Delta Lake i odwołaj się do niego.

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. Przykłady użycia można znaleźć w temacie Query Delta Lake (v1) files (Pliki usługi Delta Lake w wersji 1).

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ć klauzulę opcjonalną WITH w instrukcji OPENROWSET .

  • Jeśli istnieją pliki danych CSV, podaj nazwy kolumn i ich typy danych, aby odczytać wszystkie kolumny. 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 są 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 są 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, zobacz Odczytywanie plików CSV bez określania wszystkich kolumn.

Wnioskowanie schematu

Pomijając klauzulę WITH z instrukcji OPENROWSET , możesz poinstruować usługę, aby automatycznie wykryła (wywnioskować) schemat 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 znajdują się w wynikowym zestawie plików.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Przykłady użycia można znaleźć w temacie Query folders and multiple files (Foldery zapytań i wiele plików).

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 są 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ć więcej informacji, zobacz sekcję Project nested or repeated data (Projekt zagnieżdżony lub powtarzające się dane) artykułu Query Parquet nested types (Typy zagnieżdżone zapytania).

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ę w column_nameWITH postaci klauzuli OPENROWSET 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 zawartych na różnych poziomach zagnieżdżania w tym samym źródłowym pliku Parquet można uzyskać przy użyciu klauzuli WITH .

Zwracane wartości

  • Funkcja zwraca wartość skalarną, taką jak int, decimali varchar, z określonego elementu, a w określonej ścieżce dla wszystkich typów Parquet, które nie są w grupie Typ zagnieżdżony.
  • 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ślonej column_namefunkcji , funkcja zwróci błąd.
  • Jeśli nie można odnaleźć właściwości w określonym column_pathtrybie , w zależności od trybu ścieżki funkcja zwraca błąd w trybie ścisłym lub null w trybie lax.

Przykłady zapytań można znaleźć w sekcji Odczyt właściwości z kolumn obiektów zagnieżdżonych 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ż elementy z powtarzającej się kolumny, użyj funkcji JSON_QUERY dla każdego elementu innego niż niepodseklarowy, 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 następujący fragment składni:

    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ń).

Powiązana zawartość

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: