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

10 分

サーバーレス SQL プールを使用して、次のような一般的に使用されているさまざまなファイル形式のデータファイルに対してクエリを実行できます。

コンマ区切り値 (CSV) ファイルなどの区切りテキスト。
JavaScript Object Notation (JSON) ファイル。
Parquet ファイル。

クエリの基本的な構文は、これらすべての種類のファイルで同じであり、OPENROWSET SQL 関数に基づいて構築されます。OPENROWSET 関数では、1 つ以上のファイルのデータから表形式の行セットが生成されます。たとえば、次のクエリを使用すると、CSV ファイルからデータを抽出できます。

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

OPENROWSET 関数には、次のような要素を決定する他のパラメーターも含まれます。

生成される行セットのスキーマ
区切りテキストファイルのその他の書式設定オプション。

ヒント

OPENROWSET 関数の完全な構文については、Azure Synapse Analytics のドキュメントを参照してください。

OPENROWSET によって行セットが出力されますが、これには別名を割り当てる必要があります。前の例では、生成される行セットの別名として、rows という名前が使用されています。

BULK パラメーターには、データレイク内の、データファイルが含まれている場所への完全な URL が含まれます。これには、個別のファイルまたはフォルダーを指定できます。フォルダーを指定する場合、含める必要があるファイルの種類をフィルター処理するためのワイルドカード式を使用します。 FORMAT パラメーターによって、クエリを実行するデータの種類を指定します。上記の例では、files フォルダー内のすべての .csv ファイルから区切りテキストを読み取ります。

Note

この例では、ユーザーが基になるストア内のファイルにアクセスできることを前提としています。ファイルが SAS キーまたはカスタム ID で保護されている場合、サーバースコープ資格情報を作成する必要があります。

前の例で示したように、BULK パラメーターでワイルドカードを使用して、クエリ内のファイルを含めたり除外したりすることができます。次の一覧は、これを使用する方法のいくつかの例を示しています。

https://mydatalake.blob.core.windows.net/data/files/file1.csv: files フォルダー内の file1.csv のみを含めます。
https://mydatalake.blob.core.windows.net/data/files/file*.csv: files フォルダー内の "file" で始まる名前を持つすべての .csv ファイル。
https://mydatalake.blob.core.windows.net/data/files/*: files フォルダー内のすべてのファイル。
https://mydatalake.blob.core.windows.net/data/files/**: files 内とそのサブフォルダー内 (再帰的) のすべてのファイル。

BULK パラメーターでは、各ファイルパスをコンマで区切って、複数のパスを指定することもできます。

区切りテキストファイルのクエリ

区切りテキストファイルは、多くの企業で一般的に使用されているファイル形式です。区切りテキストで使用される特定の書式設定は、次のような点で異なる場合があります。

ヘッダー行の有無。
コンマ区切り値とタブ区切り値。
Windows スタイルと UNIX スタイルの行の終わり。
引用符なしと引用符付きの値、およびエスケープ文字。

使用している区切りファイルの種類に関係なく、csv FORMAT パラメーター、およびデータの特定の書式設定の詳細を処理するために必要なその他のパラメーターを指定した OPENROWSET 関数を使用して、ファイルからデータを読み取ることができます。例:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

PARSER_VERSION は、ファイルで使用されているテキストエンコードをクエリで解釈する方法を決定するために使用されます。バージョン 1.0 が既定であり、幅広いファイルエンコードがサポートされます。これに対して、バージョン 2.0 では、サポートされるエンコードは少なくなりますが、パフォーマンスが向上します。 FIRSTROW パラメーターは、テキストファイル内の行をスキップする、非構造化プリアンブルテキストを削除する、または列見出しを含む行を無視するために使用されます。

区切りテキストファイルを操作する場合に必要となる可能性があるその他のパラメーターとしては、次のものがあります。

FIELDTERMINATOR - 各行でフィールド値を区切るために使用される文字。たとえば、タブ区切りファイルでは、TAB (\t) 文字でフィールドが区切られます。既定のフィールドターミネータはコンマ (,) です。
ROWTERMINATOR - データの行の末尾を示すために使用される文字。たとえば、標準の Windows テキストファイルでは、コード \n で示されるキャリッジリターン (CR) と改行 (LF) の組み合わせが使用されます。一方、UNIX スタイルのテキストファイルでは、コード 0x0a を使用して示すことができる 1 つの改行文字が使用されます。
FIELDQUOTE - 引用符付きの文字列値を囲むのに使用される文字。たとえば、アドレスフィールド値 126 Main St, apt 2 のコンマがフィールド区切り記号として解釈されないようにするには、"126 Main St, apt 2" のようにフィールド値全体を引用符で囲むことができます。二重引用符 (") は、既定のフィールド引用符文字です。

ヒント

区切りテキストファイルを操作する場合のその他のパラメーターの詳細については、Azure Synapse Analytics のドキュメントを参照してください。

行セットスキーマの指定

区切りテキストファイルでは、最初の行に列名が含まれるのが一般的です。 OPENROWSET 関数では、これを使用して、生成される行セットのスキーマを定義でき、列に含まれる値に基づいて列のデータ型を自動的に推論できます。たとえば、次の区切りテキストについて考えてみましょう。

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

データは、次の 3 つの列で構成されます。

product_id (整数)
product_name (文字列)
list_price (10 進数)

次のクエリを使用すると、正しい列名と適切に推論された SQL Server データ型 (この場合は INT、NVARCHAR、DECIMAL) を使用してデータを抽出できます

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

HEADER_ROW パラメーター (パーサーバージョン 2.0 を使用する場合にのみ使用できます) は、次のように、各ファイル内のデータの最初の行を列名として使用するようにクエリエンジンに指示します。

product_id	product_name	list_price
123	ウィジェット	12.9900
124	ガジェット	3.9900

次に、次のデータについて考えてみましょう。

123,Widget,12.99
124,Gadget,3.99

今度は、ファイルのヘッダー行に列名が含まれていません。この場合でもデータ型を推論することはできますが、列名は、C1、C2、C3 などに設定されます。

C1	C2	C3
123	ウィジェット	12.9900
124	ガジェット	3.9900

明示的な列名とデータ型を指定するには、次のように WITH 句でスキーマ定義を指定することにより、既定の列名と推論されたデータ型をオーバーライドできます。

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

このクエリでは、期待された結果が生成されます。

product_id	product_name	list_price
123	ウィジェット	12.99
124	ガジェット	3.99

ヒント

テキストファイルを操作するときに、UTF-8 でエンコードされたデータと、サーバーレス SQL プールのマスター データベースで使用される照合順序との間で互換性の問題が発生する場合があります。これを克服するために、スキーマ内の個々の VARCHAR 列に互換性のある照合順序を指定できます。詳細については、トラブルシューティングに関するガイダンスを参照してください。

JSON ファイルのクエリの実行

JSON は、REST インターフェイスを介してデータを交換したり、Azure Cosmos DB などの NoSQL データストアを使用したりする Web アプリケーションで一般的な形式です。このため、分析のためにデータレイク内のファイルに JSON ドキュメントとしてデータを保持することは珍しくありません。

たとえば、個々の製品を定義する、次のような JSON ファイルがあるとしましょう。

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

この形式の複数の JSON ファイルを含むフォルダーから製品データを返すには、次の SQL クエリを使用できます。

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET には JSON ファイル固有の形式がないため、FIELDTERMINATOR、FIELDQUOTE、ROWTERMINATOR を 0x0b に設定した csv 形式と、単一の NVARCHAR (MAX) 列を含むスキーマを使用する必要があります。このクエリの結果は、次のように JSON ドキュメントの 1 つの列を含む行セットです。

ドキュメント
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

JSON から個々の値を抽出するには、次に示すように、SELECT ステートメントで JSON_VALUE 関数を使用できます。

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

このクエリでは、次に示す結果のような行セットが返されます。

product	Price
ウィジェット	12.99
ガジェット	3.99

Parquet ファイルに対するクエリ

Parquet は、分散ファイルストレージでのビッグデータ処理に一般的に使用される形式です。これは、圧縮と分析クエリ用に最適化された効率的なデータ形式です。

ほとんどの場合、データのスキーマは Parquet ファイルに埋め込まれているため、次のように、読み取るファイルへのパスを指定した BULK パラメーターと、FORMAT パラメーターとして parquet を指定するだけで済みます。

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

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

データレイクでは、パーティション分割条件を反映するサブフォルダー内の複数のファイルにデータを分割してパーティション分割するのが一般的です。これにより、分散処理システムで、データの複数のパーティションで並列処理したり、フィルター条件に基づいて特定のフォルダーからのデータの読み取りを簡単に除外したりすることができます。たとえば、販売注文データを効率的に処理する必要があり、多くの場合、注文が行われた年と月に基づいてフィルター処理する必要があるとします。フォルダーを使用して、データを次のようにパーティション分割できます。

/orders
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

結果をフィルター処理して 2020 年 1 月と 2 月の注文のみを含むクエリを作成するには、次のコードを使用できます。

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

WHERE 句の番号付きファイルパスパラメーターは BULK パス内のフォルダー名のワイルドカードを参照するため、パラメーター 1 は year=* フォルダー名の * で、パラメーター 2 は month=* フォルダー名の * です。

続行

区切りテキスト ファイルのクエリ

行セット スキーマの指定

JSON ファイルのクエリの実行

Parquet ファイルに対するクエリ

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

フィードバック

区切りテキストファイルのクエリ

行セットスキーマの指定