Wykonywanie zapytań względem plików Parquet przy użyciu bezserwerowej puli SQL w usłudze Azure Synapse Analytics

  • Artykuł

W tym artykule dowiesz się, jak napisać zapytanie przy użyciu bezserwerowej puli SQL, która odczytuje pliki Parquet.

Przykład z przewodnika Szybki start

OPENROWSET funkcja umożliwia odczytywanie zawartości pliku parquet przez podanie adresu URL do pliku.

Odczyt pliku parquet

Najprostszym sposobem wyświetlenia zawartości PARQUET pliku jest podanie adresu URL pliku do OPENROWSET działania i określenia parquet FORMAT. Jeśli plik jest publicznie dostępny lub jeśli tożsamość firmy Microsoft Entra może uzyskać dostęp do tego pliku, powinna być widoczna zawartość pliku przy użyciu zapytania, takiego jak pokazany w poniższym przykładzie:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Upewnij się, że masz dostęp do tego pliku. Jeśli plik jest chroniony przy użyciu klucza sygnatury dostępu współdzielonego lub niestandardowej tożsamości platformy Azure, należy skonfigurować poświadczenia na poziomie serwera na potrzeby logowania sql.

Ważne

Upewnij się, że używasz sortowania bazy danych UTF-8 (na przykład Latin1_General_100_BIN2_UTF8), ponieważ wartości ciągów w plikach PARQUET są kodowane przy użyciu kodowania UTF-8. Niezgodność między kodowaniem tekstu w pliku PARQUET a sortowaniem może spowodować nieoczekiwane błędy konwersji. Domyślne sortowanie bieżącej bazy danych można łatwo zmienić przy użyciu następującej instrukcji języka T-SQL: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Aby uzyskać więcej informacji na temat sortowania, zobacz Typy sortowania obsługiwane dla usługi Synapse SQL.

Jeśli używasz sortowania Latin1_General_100_BIN2_UTF8 , uzyskasz dodatkowy wzrost wydajności w porównaniu z innymi sortowaniami. Latin1_General_100_BIN2_UTF8 Sortowanie jest zgodne z regułami sortowania ciągów parquet. Pula SQL może wyeliminować niektóre części plików parquet, które nie będą zawierać danych potrzebnych w zapytaniach (oczyszczanie pliku/segmentu kolumny). Jeśli używasz innych sortowania, wszystkie dane z plików parquet zostaną załadowane do usługi Synapse SQL, a filtrowanie odbywa się w ramach procesu SQL. Latin1_General_100_BIN2_UTF8 Sortowanie ma dodatkową optymalizację wydajności, która działa tylko dla parquet i Cosmos DB. Wadą jest to, że tracisz szczegółowe reguły porównania, takie jak bez uwzględniania wielkości liter.

Użycie źródła danych

Poprzedni przykład używa pełnej ścieżki do pliku. Alternatywnie możesz utworzyć zewnętrzne źródło danych z lokalizacją wskazującą folder główny magazynu i użyć tego źródła danych oraz ścieżki względnej do pliku w OPENROWSET funkcji:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

Jeśli źródło danych jest chronione przy użyciu klucza sygnatury dostępu współdzielonego lub tożsamości niestandardowej, możesz skonfigurować źródło danych przy użyciu poświadczeń o zakresie bazy danych.

Jawne określanie schematu

OPENROWSET Umożliwia jawne określenie kolumn, które mają być odczytywane z pliku przy użyciu WITH klauzuli :

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Ważne

Upewnij się, że explicilty określasz sortowanie UTF-8 (na przykład Latin1_General_100_BIN2_UTF8) dla wszystkich kolumn ciągu w WITH klauzuli lub ustaw sortowanie UTF-8 na poziomie bazy danych. Niezgodność między kodowaniem tekstu w sortowaniu kolumn pliku i ciągu może spowodować nieoczekiwane błędy konwersji. Domyślne sortowanie bieżącej bazy danych można łatwo zmienić przy użyciu następującej instrukcji języka T-SQL: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Sortowanie można łatwo ustawić na typach kolarzy, na przykład: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Aby uzyskać więcej informacji na temat sortowania, zobacz Typy sortowania obsługiwane dla usługi Synapse SQL.

W poniższych sekcjach przedstawiono sposób wykonywania zapytań dotyczących różnych typów plików PARQUET.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych ze źródłem danych, które odwołuje się do konta magazynu NYC Yellow Taxi . Następnie zainicjuj obiekty, wykonując skrypt instalacyjny w tej bazie danych. Ten skrypt instalacyjny utworzy źródła danych, poświadczenia o zakresie bazy danych i zewnętrzne formaty plików, które są używane w tych przykładach.

Zestaw danych

W tym przykładzie używany jest zestaw danych żółtych taksówek w nowym jorku. Możesz wysyłać zapytania do plików Parquet w taki sam sposób, jak w przypadku odczytywania plików CSV. Jedyną różnicą FILEFORMAT jest to, że parametr powinien być ustawiony na PARQUETwartość . Przykłady w tym artykule pokazują specyfikę odczytywania plików Parquet.

Zestaw zapytań dotyczących plików parquet

Podczas wykonywania zapytań dotyczących plików Parquet można określić tylko interesujące kolumny.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Automatyczne wnioskowanie schematu

Podczas odczytywania plików Parquet nie trzeba używać klauzuli OPENROWSET WITH. Nazwy kolumn i typy danych są automatycznie odczytywane z plików Parquet.

Należy pamiętać, że jeśli odczytujesz jednocześnie liczbę plików, schemat, nazwy kolumn i typy danych zostaną wywnioskowane z pierwszej usługi plików pobieranej z magazynu. Może to oznaczać, że niektóre oczekiwane kolumny zostaną pominięte, ponieważ plik używany przez usługę do zdefiniowania schematu nie zawiera tych kolumn. Aby jawnie określić schemat, użyj klauzuli OPENROWSET WITH.

W poniższym przykładzie przedstawiono funkcje automatycznego wnioskowania schematu dla plików Parquet. Zwraca liczbę wierszy we wrześniu 2018 r. bez określania schematu.

Uwaga

Podczas odczytywania plików Parquet nie trzeba określać kolumn w klauzuli OPENROWSET WITH. W takim przypadku bezserwerowa usługa zapytań puli SQL będzie używać metadanych w pliku Parquet i powiązać kolumny według nazwy.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Wykonywanie zapytań dotyczących danych partycjonowanych

Zestaw danych podany w tym przykładzie jest podzielony (podzielony na partycje) na oddzielne podfoldery. Określone partycje można kierować przy użyciu funkcji filepath. W tym przykładzie przedstawiono kwoty taryf według roku, miesiąca i payment_type w ciągu pierwszych trzech miesięcy 2017 r.

Uwaga

Zapytanie bezserwerowej puli SQL jest zgodne ze schematem partycjonowania Hive/Hadoop.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Mapowanie typów

W przypadku mapowania typu Parquet na natywny typ SQL mapowanie typu sprawdzania dla parquet.

Następne kroki

Przejdź do następnego artykułu, aby dowiedzieć się, jak wykonywać zapytania dotyczące typów zagnieżdżonych Parquet.