Abfragen von Parquet-Dateien mithilfe eines serverlosen SQL-Pools in Azure Synapse Analytics

Artikel
03/17/2023

In diesem Artikel erfahren Sie, wie Sie mithilfe eines serverlosen SQL-Pools eine Abfrage schreiben, die Parquet-Dateien liest.

Schnellstartbeispiel

Mit der Funktion OPENROWSET können Sie den Inhalt einer Parquet-Datei lesen, indem Sie die URL zur Datei bereitstellen.

Lesen einer Parquet-Datei

Am einfachsten können Sie den Inhalt Ihrer PARQUET-Datei anzeigen, indem Sie der Funktion OPENROWSET die Datei-URL bereitstellen und Parquet als FORMAT angeben. Wenn die Datei öffentlich verfügbar ist oder Ihre Microsoft Entra-Identität auf diese Datei zugreifen kann, sollten Sie den Inhalt der Datei mithilfe einer Abfrage wie im folgenden Beispiel anzeigen können:

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

Stellen Sie sicher, dass Sie auf diese Datei zugreifen können. Wenn Ihre Datei mit einem SAS-Schlüssel oder einer benutzerdefinierten Azure-Identität geschützt ist, müssen Sie Anmeldeinformationen auf Serverebene für die SQL-Anmeldung einrichten.

Wichtig

Stellen Sie sicher, dass Sie eine UTF-8-Datenbanksortierung (z. B. Latin1_General_100_BIN2_UTF8) verwenden, da Zeichenfolgenwerte in Parquet-Dateien mit UTF-8 codiert sind. Ein Konflikt zwischen der Textcodierung in der Parquet-Datei und der Sortierung kann zu unerwarteten Konvertierungsfehlern führen. Die Standardsortierung der aktuellen Datenbank kann mit der folgenden T-SQL-Anweisung problemlos geändert werden: .ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Weitere Informationen zu Sortierungen finden Sie unter Sortierungstypen, die für Synapse SQL unterstützt werden.

Wenn Sie die Sortierung Latin1_General_100_BIN2_UTF8 verwenden, erhalten Sie einen zusätzlichen Leistungsschub im Vergleich zu den anderen Sortierungen. Die Sortierung Latin1_General_100_BIN2_UTF8 ist mit den Sortierregeln für Parkettzeichenfolgen kompatibel. Der SQL-Pool ist in der Lage, einige Teile der Parkettdateien zu eliminieren, die keine für die Abfragen benötigten Daten enthalten (file/column-segment pruning). Wenn Sie andere Sortierungen verwenden, werden alle Daten aus den Parkettdateien in Synapse SQL geladen und die Filterung erfolgt innerhalb des SQL-Prozesses. Die Sortierung Latin1_General_100_BIN2_UTF8 hat eine zusätzliche Leistungsoptimierung, die nur für Parquet und Cosmos DB funktioniert. Der Nachteil ist, dass Sie differenzierte Vergleichsregeln wie die Groß-/Kleinschreibung nicht beachten.

Datenquellennutzung

Im vorherigen Beispiel wird der vollständige Pfad zur Datei verwendet. Alternativ können Sie eine externe Datenquelle mit dem Speicherort erstellen, der auf den Stammordner des Speichers verweist, und diese Datenquelle sowie den relativen Pfad zur Datei in der Funktion OPENROWSET verwenden:

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

Wenn eine Datenquelle mit einem SAS-Schlüssel oder einer benutzerdefinierten Identität geschützt ist, können Sie die Datenquelle mit datenbankweit gültigen Anmeldeinformationen konfigurieren.

Explizites Angeben des Schemas

Bei OPENROWSET können Sie mit der WITH-Klausel explizit angeben, welche Spalten aus der Datei gelesen werden sollen:

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

Wichtig

Stellen Sie sicher, dass Sie explizit eine UTF-8-Sortierung (z. B. Latin1_General_100_BIN2_UTF8) für alle Zeichenfolgenspalten in der WITH-Klausel angeben, oder legen Sie eine UTF-8-Sortierung auf Datenbankebene fest. Ein Konflikt zwischen der Textcodierung in der Datei und der Sortierung der Zeichenfolgenspalte kann zu unerwarteten Konvertierungsfehlern führen. Die Standardsortierung der aktuellen Datenbank kann mithilfe der folgenden T-SQL-Anweisung problemlos geändert werden: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Sie können die Sortierung der Spaltentypen problemlos festlegen, zum Beispiel: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Weitere Informationen zu Sortierungen finden Sie unter Sortierungstypen, die für Synapse SQL unterstützt werden.

In den folgenden Abschnitten erfahren Sie, wie Sie verschiedene Typen von Parquet-Dateien abfragen.

Voraussetzungen

Der erste Schritt besteht in der Erstellung einer Datenbank mit einer Datenquelle, die auf das Speicherkonto NYC Yellow Taxi verweist. Initialisieren Sie dann die Objekte, indem Sie das Setupskript für diese Datenbank ausführen. Mit diesem Setupskript werden die Datenquellen, die für die gesamte Datenbank gültigen Anmeldeinformationen und externe Dateiformate erstellt, die in diesen Beispielen verwendet werden.

Dataset

In diesem Beispiel wird das Dataset NYC Yellow Taxi verwendet. Sie können Parquet-Dateien auf dieselbe Weise abfragen wie Sie CSV-Dateien lesen. Der einzige Unterschied besteht darin, dass der Parameter FILEFORMAT auf PARQUET festgelegt werden muss. In den Beispielen in diesem Artikel werden die Besonderheiten beim Lesen von Parquet-Dateien erläutert.

Abfragesatz von Parquet-Dateien

Bei der Abfrage von Parquet-Dateien können Sie nur die relevanten Spalten angeben.

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;

Automatischer Schemarückschluss

Sie brauchen die OPENROWSET WITH-Klausel nicht zu verwenden, wenn Sie Parquet-Dateien lesen. Spaltennamen und Datentypen werden automatisch aus Parquet-Dateien gelesen.

Beachten Sie, dass beim gleichzeitigen Lesen der Anzahl von Dateien das Schema, die Spaltennamen und Datentypen aus dem ersten Dateidienst abgeleitet werden, der aus dem Speicher abgerufen wird. Dies kann bedeuten, dass einige der erwarteten Spalten weggelassen werden, weil die vom Dienst zum Definieren des Schemas verwendete Datei diese Spalten nicht enthielt. Wenn Sie das Schema explizit angeben möchten, verwenden Sie die OPENROWSET WITH-Klausel.

Im folgenden Beispiel werden die Funktionen des automatischen Schemarückschlusses bei Parquet-Dateien gezeigt. Hierzu wird die Anzahl von Zeilen im September 2018 ohne Angabe eines Schemas zurückgegeben.

Hinweis

Beim Lesen von Parquet-Dateien müssen Sie in der OPENROWSET WITH-Klausel keine Spalten angeben. In diesem Fall verwendet der Abfragedienst des serverlosen SQL-Pools Metadaten in der Parquet-Datei und bindet Spalten nach Namen.

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

Abfragen von partitionierten Daten

Das in diesem Beispiel bereitgestellte Dataset ist in separate Unterordner aufgeteilt (partitioniert). Mithilfe der filepath-Funktion können Sie bestimmte Partitionen als Ziel verwenden. Dieses Beispiel zeigt die Fahrpreisbeträge nach Jahr, Monat und Zahlungsart für die ersten drei Monate des Jahres 2017.

Hinweis

Die Abfrage des serverlosen SQL-Pools ist kompatibel mit dem Hive/Hadoop-Partitionierungsschema.

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;

Typzuordnung

Informationen zur Parquet-Typzuordnung zum nativen SQL-Typ finden Sie unter Typzuordnung für Parquet.

Nächste Schritte

Wechseln Sie zum nächsten Artikel, um Informationen zum Abfragen geschachtelter Parquet-Typen zu erhalten.