Query's uitvoeren op Parquet-bestanden met behulp van een serverloze SQL-pool in Azure Synapse Analytics

Artikel
10/18/2023

In dit artikel leert u hoe u een query schrijft met behulp van een serverloze SQL-pool waarmee Parquet-bestanden worden gelezen.

Quickstart-voorbeeld

OPENROWSET met de functie kunt u de inhoud van het Parquet-bestand lezen door de URL naar uw bestand op te geven.

Parquet-bestand lezen

De eenvoudigste manier om de inhoud van uw PARQUET bestand te bekijken, is door een bestands-URL op te geven om te functioneren en parquet FORMATop te OPENROWSET geven. Als het bestand openbaar beschikbaar is of als uw Microsoft Entra-identiteit toegang heeft tot dit bestand, moet u de inhoud van het bestand kunnen zien met behulp van de query, zoals in het volgende voorbeeld:

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

Zorg ervoor dat u toegang hebt tot dit bestand. Als uw bestand is beveiligd met een SAS-sleutel of een aangepaste Azure-identiteit, moet u referenties op serverniveau instellen voor sql-aanmelding.

Belangrijk

Zorg ervoor dat u een UTF-8-databasesortering gebruikt (bijvoorbeeld Latin1_General_100_BIN2_UTF8) omdat tekenreekswaarden in PARQUET-bestanden zijn gecodeerd met behulp van UTF-8-codering. Een onjuiste overeenkomst tussen de tekstcodering in het PARQUET-bestand en de sortering kunnen onverwachte conversiefouten veroorzaken. U kunt de standaardsortering van de huidige database eenvoudig wijzigen met behulp van de volgende T-SQL-instructie: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;Zie Sorteringstypen die worden ondersteund voor Synapse SQL voor meer informatie over sorteringen.

Als u de Latin1_General_100_BIN2_UTF8 sortering gebruikt, krijgt u een extra prestatieverbeteringen ten opzichte van de andere sorteringen. De Latin1_General_100_BIN2_UTF8 sortering is compatibel met sorteerregels voor parquet-tekenreeksen. De SQL-pool kan sommige onderdelen van de Parquet-bestanden verwijderen die geen gegevens bevatten die nodig zijn in de query's (bestand/kolomsegment verwijderen). Als u andere sorteringen gebruikt, worden alle gegevens uit de Parquet-bestanden geladen in Synapse SQL en vindt het filteren plaats binnen het SQL-proces. De Latin1_General_100_BIN2_UTF8 sortering heeft extra prestatieoptimalisatie die alleen werkt voor Parquet en Cosmos DB. Het nadeel is dat u fijnmazige vergelijkingsregels kwijtraakt, zoals hoofdlettergevoeligheid.

Gebruik van gegevensbronnen

In het vorige voorbeeld wordt het volledige pad naar het bestand gebruikt. Als alternatief kunt u een externe gegevensbron maken met de locatie die verwijst naar de hoofdmap van de opslag en die gegevensbron en het relatieve pad naar het bestand in OPENROWSET functie gebruiken:

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

Als een gegevensbron is beveiligd met een SAS-sleutel of een aangepaste identiteit, kunt u de gegevensbron configureren met referenties binnen het databasebereik.

Schema expliciet opgeven

OPENROWSET hiermee kunt u expliciet opgeven welke kolommen u uit het bestand wilt lezen met behulp van WITH de component:

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

Belangrijk

Zorg ervoor dat u bepaalde UTF-8-sortering (bijvoorbeeld Latin1_General_100_BIN2_UTF8) opgeeft voor alle tekenreekskolommen in de WITH component of stel een UTF-8-sortering in op databaseniveau. Niet-overeenkomende tekstcodering in het bestand en de sortering van tekenreekskolommen kunnen onverwachte conversiefouten veroorzaken. U kunt eenvoudig de standaardsortering van de huidige database wijzigen met behulp van de volgende T-SQL-instructie: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; U kunt eenvoudig sortering instellen op de columtypen, bijvoorbeeld: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8Zie Sorteringstypen die worden ondersteund voor Synapse SQL voor meer informatie over sorteringen.

In de volgende secties ziet u hoe u query's kunt uitvoeren op verschillende typen PARQUET-bestanden.

Vereisten

De eerste stap is het maken van een database met een gegevensbron die verwijst naar het NYC Yellow Taxi-opslagaccount . Initialiseer vervolgens de objecten door een installatiescript uit te voeren op die database. Met dit installatiescript worden de gegevensbronnen, databasereferenties en externe bestandsindelingen gemaakt die in deze voorbeelden worden gebruikt.

Gegevensset

NyC Yellow Taxi-gegevensset wordt in dit voorbeeld gebruikt. U kunt query's uitvoeren op Parquet-bestanden op dezelfde manier als u CSV-bestanden leest. Het enige verschil is dat de FILEFORMAT parameter moet worden ingesteld op PARQUET. In dit artikel ziet u de details van het lezen van Parquet-bestanden.

Queryset parquet-bestanden

U kunt alleen de gewenste kolommen opgeven wanneer u query's uitvoert op Parquet-bestanden.

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;

Automatische schemadeductie

U hoeft de OPENROWSET WITH-component niet te gebruiken bij het lezen van Parquet-bestanden. Kolomnamen en gegevenstypen worden automatisch gelezen uit Parquet-bestanden.

Houd er rekening mee dat als u het aantal bestanden tegelijk leest, het schema, de kolomnamen en gegevenstypen worden afgeleid van de eerste bestandsservice uit de opslag. Dit kan betekenen dat sommige van de verwachte kolommen worden weggelaten, allemaal omdat het bestand dat door de service wordt gebruikt om het schema te definiëren, deze kolommen niet bevat. Als u het schema expliciet wilt opgeven, gebruikt u de COMPONENT OPENROWSET WITH.

In het volgende voorbeeld ziet u de mogelijkheden voor automatische schemadeductie voor Parquet-bestanden. Het retourneert het aantal rijen in september 2018 zonder een schema op te geven.

Notitie

U hoeft geen kolommen op te geven in de OPENROWSET WITH-component bij het lezen van Parquet-bestanden. In dat geval maakt de serverloze SQL-poolqueryservice gebruik van metagegevens in het Parquet-bestand en worden kolommen op naam gebonden.

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

Gepartitioneerde gegevens opvragen

De gegevensset in dit voorbeeld is onderverdeeld (gepartitioneerd) in afzonderlijke submappen. U kunt specifieke partities targeten met behulp van de bestandspadfunctie. In dit voorbeeld ziet u de ritbedragen per jaar, maand en payment_type voor de eerste drie maanden van 2017.

Notitie

De query van de serverloze SQL-pool is compatibel met het partitieschema 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;

Typetoewijzing

Voor parquet-typetoewijzing aan systeemeigen SQL-typecontroletypetoewijzing voor Parquet.

Volgende stappen

Ga naar het volgende artikel voor meer informatie over het uitvoeren van query's op geneste Parquet-typen.