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

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

Przykład szybkiego startu

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

Odczyt pliku "parquet"

Najprostszym sposobem na wyświetlenie zawartości pliku PARQUET jest podanie adresu URL pliku do funkcji OPENROWSET i określenie formatu parquet FORMAT. Jeśli plik jest publicznie dostępny lub jeśli tożsamość Microsoft Entra ma dostęp do tego pliku, powinieneś móc zobaczyć zawartość pliku, używając zapytania podobnego do tego przedstawionego 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 dla 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 jest w stanie wyeliminować niektóre części plików Parquet, które nie zawierają danych potrzebnych do zapytań (przycinanie plików/segmentów kolumn). Jeśli używasz innych sortowań, wszystkie dane z plików parquet zostaną załadowane do Synapse SQL, a filtrowanie odbywać się będzie w ramach procesu SQL. Latin1_General_100_BIN2_UTF8 Sortowanie ma kolejną 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 nieuwzględnianie 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, którego lokalizacja wskazuje na folder główny magazynu, i wykorzystać je oraz ścieżkę względną do pliku w funkcji OPENROWSET.

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 SAS lub tożsamości niestandardowej, możesz skonfigurować źródło danych przy użyciu poświadczenia skojarzonego z zakresem 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 jawnie 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 pliku a sortowaniem kolumny ciągu znaków 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 typów kolumn można łatwo ustawić, 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 magazynowego 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ą jest to, że parametr FILEFORMAT powinien być ustawiony na wartość PARQUET. Przykłady w tym artykule pokazują specyfikę odczytywania plików Parquet.

Utwórz zapytanie dla zestawu 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

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

Należy pamiętać, że jeśli odczytujesz wiele plików jednocześnie, schemat, nazwy kolumn i typy danych zostaną wywnioskowane z pierwszego pliku pobranego 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

Nie trzeba określać kolumn w klauzuli OPENROWSET WITH podczas odczytywania plików Parquet. W takim przypadku usługa zapytań puli SQL bez serwera wykorzysta metadane w pliku Parquet i powiąże 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 na oddzielne podkatalogi. Można celować w określone partycje 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 sprawdź mapowanie typów dla Parquet.

Następny krok

Jak wykonywać zapytania dotyczące typów zagnieżdżonych w Parquet