Azure Synapse Analytics でサーバーレス SQL プールを使用して Parquet ファイルのクエリを実行する

[アーティクル]
03/16/2023

この記事では、Parquet ファイルを読み取るサーバーレス SQL プールを使用してクエリを作成する方法について説明します。

クイックスタートの例

OPENROWSET 関数を使用すると、ファイルの URL を指定することによって、Parquet ファイルの内容を読み取ることができます。

parquet ファイルの読み取り

PARQUET ファイルの内容を確認する最も簡単な方法は、OPENROWSET 関数にファイルの URL を指定し、parquet の FORMAT を指定することです。ファイルが一般公開されている場合、または Microsoft Entra ID でこのファイルにアクセスできる場合は、次の例に示すようなクエリを使用して、ファイルの内容を表示することができます。

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

このファイルにアクセスできることを確認します。ファイルが SAS キーまたはカスタム Azure ID で保護されている場合は、SQL ログインのためのサーバーレベルの資格情報を設定する必要があります。

重要

PARQUET ファイル内の文字列値が UTF-8 エンコードを使用してエンコードされているため、UTF-8 データベース照合順序 (Latin1_General_100_BIN2_UTF8 など) を使用するようにしてください。 PARQUET ファイル内のテキストエンコードと照合順序が一致しないと、予期しない変換エラーが発生する可能性があります。現在のデータベースの既定の照合順序は、という T-SQL ステートメントを使用して簡単変更できます。ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;照合順序の詳細については、「Synapse SQL でサポートされる照合順序の種類」を参照してください。

Latin1_General_100_BIN2_UTF8 照合順序を使用すると、他の照合順序と比較して、さらにパフォーマンスが向上します。 Latin1_General_100_BIN2_UTF8 照合順序は、parquet 文字列の並べ替え規則と互換性があります。 SQL プールでは、クエリで必要なデータ (ファイル/列セグメントの排除) を含まない parquet ファイルの一部を削除できます。他の照合順序を使用すると、parquet ファイルのすべてのデータが Synapse SQL に読み込まれ、フィルター処理は SQL プロセス内で行われます。 Latin1_General_100_BIN2_UTF8 照合順序には、parquet と Cosmos DB でのみ機能する追加のパフォーマンス最適化があります。欠点は、大文字と小文字の区別のようなきめ細かい比較規則が失われる点です。

データソースの使用状況

前の例では、ファイルへの完全なパスを使用しています。別の方法として、ストレージのルートフォルダーを示す場所を持つ外部データソースを作成し、そのデータソースとファイルへの相対パスを 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

データソースが SAS キーまたはカスタム ID で保護されている場合は、データベーススコープ資格情報を使用してデータソースを構成できます。

スキーマを明示的に指定する

OPENROWSET を使用すると、WITH 句によってファイルから読み取る列を明示的に指定できます。

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

重要

必ず WITH 句の文字列の列すべてに対して何らかの UTF-8 照合順序 (Latin1_General_100_BIN2_UTF8 など) を明示的に指定するようにしてください。または、データベースレベルで何らかの UTF-8 照合順序を設定してください。ファイル内のテキストエンコードと文字列の列の照合順序が一致しないと、予期しない変換エラーが発生する可能性があります。現在のデータベースの既定の照合順序は、という T-SQL ステートメントを使用して容易に変更できます。ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; 列の型に照合順序を簡単に設定できます。例: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8照合順序の詳細については、「Synapse SQL でサポートされる照合順序の種類」を参照してください。

次のセクションでは、さまざまな種類の PARQUET ファイルに対してクエリを実行する方法について説明します。

前提条件

最初の手順は、NYC Yellow Taxi ストレージアカウントを参照するデータソースでデータベースを作成することです。次に、そのデータベースでセットアップスクリプトを実行して、オブジェクトを初期化します。このセットアップスクリプトにより、これらのサンプルで使用されるデータソース、データベーススコープの資格情報、および外部ファイル形式が作成されます。

データセット

NYC Yellow Taxi データセットがこのサンプルで使用されます。 CSV ファイルを読み取るのと同じ方法で、Parquet ファイルに対してクエリを実行できます。唯一の違いは、FILEFORMAT パラメーターを PARQUET に設定する必要があることです。この記事の例では、Parquet ファイルの読み取りについて詳細を示します。

Parquet ファイルのセットに対してクエリを実行する

Parquet ファイルに対してクエリを実行する場合は、目的の列だけを指定できます。

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;

自動スキーマ推論

Parquet ファイルを読み取るときに、OPENROWSET WITH 句を使用する必要はありません。列名とデータ型は、Parquet ファイルから自動的に読み取られます。

一度に多数のファイルを読み取る場合、スキーマ、列名とデータ型はストレージから取得した最初のファイルサービスから推測されることに注意してください。これは、スキーマを定義するためにサービスで使用されるファイルにこれらの列が含まれていなかったため、予想される列の一部が省略されていることを意味する場合があります。スキーマを明示的に指定するには、OPENROWSET WITH 句を使用してください。

次のサンプルは、Parquet ファイルを対象にした自動スキーマ推論機能を示しています。この場合はスキーマを指定しなくても 2018 年 9 月の行の数が返されます。

Note

Parquet ファイルを読み取るときに OPENROWSET WITH 句で列を指定する必要はありません。この場合、サーバーレス SQL プールのクエリサービスにより、Parquet ファイル内のメタデータが利用され、名前によって列がバインドされます。

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

パーティション分割されたデータに対してクエリを実行する

このサンプルで指定されたデータセットは、個別のサブフォルダーに分割 (パーティション分割) されます。 filepath 関数を使用して、特定のパーティションをターゲットにすることができます。この例では、2017 年の最初の 3 か月について、年、月、および payment_type 別の料金が示されています。

Note

サーバーレス SQL プールのクエリは、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;

型のマッピング

Parquet の型と SQL のネイティブ型のマッピングについては、Parquet の型マッピングに関するページを確認してください。

次のステップ

次の記事に進んで、Parquet の入れ子にされた型に対してクエリを実行する方法を学習してください。