Köra frågor mot Parquet-filer med hjälp av en serverlös SQL-pool i Azure Synapse Analytics

Artikel
10/18/2023

I den här artikeln får du lära dig hur du skriver en fråga med hjälp av en serverlös SQL-pool som läser Parquet-filer.

Snabbstartsexempel

OPENROWSET med funktionen kan du läsa innehållet i parquet-filen genom att ange URL:en till filen.

Läs parquet-fil

Det enklaste sättet att se innehållet PARQUET i filen är att ange fil-URL:en för att OPENROWSET fungera och ange parquet FORMAT. Om filen är offentligt tillgänglig eller om din Microsoft Entra-identitet kan komma åt den här filen bör du kunna se innehållet i filen med hjälp av frågan som den som visas i följande exempel:

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

Kontrollera att du har åtkomst till den här filen. Om filen skyddas med SAS-nyckel eller anpassad Azure-identitet måste du konfigurera autentiseringsuppgifter på servernivå för sql-inloggning.

Viktigt!

Kontrollera att du använder en UTF-8-databassortering (till exempel Latin1_General_100_BIN2_UTF8) eftersom strängvärden i PARQUET-filer kodas med UTF-8-kodning. Ett matchningsfel mellan textkodningen i PARQUET-filen och sorteringen kan orsaka oväntade konverteringsfel. Du kan enkelt ändra standardsortering av den aktuella databasen med hjälp av följande T-SQL-instruktion: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Mer information om sortering finns i Sorteringstyper som stöds för Synapse SQL.

Om du använder Latin1_General_100_BIN2_UTF8 sorteringen får du ytterligare en prestandaökning jämfört med de andra sorteringarna. Sorteringen Latin1_General_100_BIN2_UTF8 är kompatibel med parquet-strängsorteringsregler. SQL-poolen kan eliminera vissa delar av parquet-filerna som inte innehåller data som behövs i frågorna (fil-/kolumnsegmentsrensning). Om du använder andra sorteringar läses alla data från parquet-filerna in i Synapse SQL och filtreringen sker i SQL-processen. Sorteringen Latin1_General_100_BIN2_UTF8 har ytterligare prestandaoptimering som endast fungerar för parquet och Cosmos DB. Nackdelen är att du förlorar detaljerade jämförelseregler som skiftlägeskänslighet.

Användning av datakälla

I föregående exempel används en fullständig sökväg till filen. Alternativt kan du skapa en extern datakälla med den plats som pekar på lagringens rotmapp och använda den datakällan och den relativa sökvägen till filen i OPENROWSET funktionen:

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

Om en datakälla skyddas med SAS-nyckel eller anpassad identitet kan du konfigurera datakällan med databasomfångsbegränsade autentiseringsuppgifter.

Ange uttryckligen schema

OPENROWSET gör att du uttryckligen kan ange vilka kolumner du vill läsa från filen med hjälp av WITH -satsen:

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

Viktigt!

Kontrollera att du är oförklarlig med att ange viss UTF-8-sortering (till exempel Latin1_General_100_BIN2_UTF8) för alla strängkolumner i WITH -satsen eller ange viss UTF-8-sortering på databasnivå. Matchningsfel mellan textkodning i filen och strängkolumnsortering kan orsaka oväntade konverteringsfel. Du kan enkelt ändra standardsortering av den aktuella databasen med hjälp av följande T-SQL-instruktion: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Du kan enkelt ange sortering på columtyperna, till exempel: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Mer information om sortering finns i Sorteringstyper som stöds för Synapse SQL.

I följande avsnitt kan du se hur du frågar efter olika typer av PARQUET-filer.

Förutsättningar

Ditt första steg är att skapa en databas med en datakälla som refererar till NYC Yellow Taxi Storage-kontot. Initiera sedan objekten genom att köra installationsskriptet på databasen. Det här installationsskriptet skapar datakällor, databasomfattningsautentiseringsuppgifter och externa filformat som används i dessa exempel.

Datamängd

NYC Yellow Taxi-datauppsättningen används i det här exemplet. Du kan fråga Parquet-filer på samma sätt som du läser CSV-filer. Den enda skillnaden är att parametern FILEFORMAT ska anges till PARQUET. Exempel i den här artikeln visar detaljerna för att läsa Parquet-filer.

Frågeuppsättning med parquet-filer

Du kan bara ange kolumner av intresse när du kör frågor mot Parquet-filer.

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;

Automatisk schemainferens

Du behöver inte använda OPENROWSET WITH-satsen när du läser Parquet-filer. Kolumnnamn och datatyper läse automatiskt från Parquet-filer.

Tänk på att om du läser antalet filer samtidigt kommer schemat, kolumnnamnen och datatyperna att härledas från den första filtjänsten som hämtas från lagringen. Detta kan innebära att vissa av de förväntade kolumnerna utelämnas, allt eftersom filen som används av tjänsten för att definiera schemat inte innehöll dessa kolumner. Om du uttryckligen vill ange schemat använder du OPENROWSET WITH-satsen.

Följande exempel visar funktionerna för automatisk schemainferens för Parquet-filer. Det returnerar antalet rader i september 2018 utan att ange ett schema.

Kommentar

Du behöver inte ange kolumner i OPENROWSET WITH-satsen när du läser Parquet-filer. I så fall använder serverlös SQL-poolfrågetjänst metadata i Parquet-filen och binder kolumner efter namn.

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

Fråga partitionerade data

Datauppsättningen som anges i det här exemplet är uppdelad (partitionerad) i separata undermappar. Du kan rikta in dig på specifika partitioner med hjälp av funktionen filepath. Det här exemplet visar prisbelopp per år, månad och payment_type för de tre första månaderna 2017.

Kommentar

Den serverlösa SQL-poolfrågan är kompatibel med Hive/Hadoop-partitioneringsschemat.

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;

Typmappning

För Parquet-typmappning till SQL-intern typ kontrollerar du typmappning för Parquet.

Nästa steg

Gå vidare till nästa artikel för att lära dig hur du kör frågor mot kapslade parquet-typer.

Dela via