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

[アーティクル]
06/01/2023

サーバーレス SQL プールを使用すると、データレイク内のデータに対してクエリを実行できます。これには、半構造化と非構造化のデータのクエリに対応する T-SQL クエリ領域が用意されています。クエリでは、次の T-SQL の側面がサポートされています。

大部分の SQL 関数と演算子を含む、完全な SELECT 領域。
CREATE EXTERNAL TABLE AS SELECT (CETAS) は、外部テーブルを作成し、次に並行して、Transact-SQL SELECT ステートメントの結果を Azure Storage にエクスポートします。

現在サポートされているものとされていないものの詳細については、サーバーレス SQL プールの概要に関する記事、または以下の記事をご覧ください。

ストレージアクセスの開発に関する記事では、外部テーブルと OPENROWSET 関数を使用してストレージからデータを読み取る方法について説明しています。
ストレージアクセスの制御に関する記事では、SAS 認証を使用するか、ワークスペースのマネージド ID を使用して、Synapse SQL からストレージにアクセスできるようにする方法について説明しています。

概要

Azure Storage ファイルに格納されているデータに対するインプレースクエリのスムーズな実行をサポートするために、サーバーレス SQL プールでは、次の追加機能を備えた OPENROWSET 関数が使用されます。

複数のファイルまたはフォルダーに対してクエリを実行する
PARQUET ファイル形式
CSV および区切りテキストに対してクエリを実行する (フィールドターミネータ、行ターミネータ、エスケープ文字)
DELTA LAKE 形式
選択した列のサブセットの読み取り
スキーマ推論
filename 関数
filepath 関数
複合型と入れ子または繰り返しのデータ構造を操作する

PARQUET ファイルに対してクエリを実行する

Parquet ソースデータに対してクエリを実行するには、FORMAT = 'PARQUET' を使用します。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

使用例については、Parquet ファイルに対するクエリの実行に関する記事を参照してください。

CSV ファイルに対してクエリを実行する

CSV ソースデータに対してクエリを実行するには、FORMAT = 'CSV' を使用します。 CSV ファイルに対してクエリを実行するときに、OPENROWSET 関数の一部として CSV ファイルのスキーマを指定できます。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

解析規則をカスタム CSV 形式に合わせて調整するために使用できる追加オプションがいくつかあります。

ESCAPE_CHAR = 'char' は、ファイル内でそれ自体とすべての区切り記号の値をエスケープするために使用するファイル内の文字を指定します。エスケープ文字の後にそれ自体以外の値、またはいずれかの区切り記号の値が続く場合は、その値を読み取るときにエスケープ文字が削除されます。 ESCAPE_CHAR パラメーターは、FIELDQUOTE が有効かどうかに関係なく適用されます。引用文字をエスケープするために使用されることはありません。引用文字は、別の引用文字でエスケープする必要があります。引用文字は、値が引用文字で囲まれている場合にのみ、列の値の中で使用できます。
FIELDTERMINATOR ='field_terminator' は、使用するフィールドターミネータを指定します。既定のフィールドターミネータはコンマ (" , ") です。
ROWTERMINATOR ='row_terminator' は、使用する行ターミネータを指定します。既定の行ターミネータは、改行文字 \r\n です。

DELTA LAKE 形式に対してクエリを実行する

Delta Lake ソースデータに対してクエリを実行するには、FORMAT = 'DELTA' を使用し、Delta Lake ファイルを含むルートフォルダーを参照します。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

ルートフォルダーには、_delta_log というサブフォルダーを含める必要があります。使用例については、記事「Delta Lake 形式に対してクエリを実行する」を参照してください。

ファイルスキーマ

Synapse SQL の SQL 言語を使用すると、ファイルのスキーマを OPENROWSET 関数の一部として定義し、列のすべてまたはサブセットを読み取ることができます。また、スキーマの推論を使用して、ファイルから列の型を自動的に決定することもできます。

選択した列のサブセットを読み取る

読み取る列を指定するには、OPENROWSET ステートメント内にオプションの WITH 句を指定できます。

CSV データファイルがある場合、すべての列を読み取るには、列名とそのデータ型を指定します。列のサブセットが必要な場合は、序数を使用して、元のデータファイルから序数で列を選択します。列は、序数の指定によってバインドされます。
Parquet データファイルがある場合は、元のデータファイル内の列名と一致する列名を指定します。列は名前によってバインドされます。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows

列ごとに、列の名前と型を WITH 句で指定する必要があります。サンプルについては、すべての列を指定せずに CSV ファイルを読み取ることに関する記事を参照してください。

スキーマ推論

OPENROWSET ステートメントから WITH 句を省略することで、基になるファイルからスキーマを自動検出 (推論) するようにサービスに指示できます。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

最適なパフォーマンスが得られる適切な推定データ型が使用されていることを確認してください。

複数のファイルまたはフォルダーに対してクエリを実行する

1 つのフォルダーまたは一連のフォルダー内にある一連のファイルを 1 つのエンティティまたは行セットとして扱いながら、それらに対して T-SQL クエリを実行するには、フォルダーのパス、または一連のファイルやフォルダーのパターン (ワイルドカードを使用) を指定します。

次の規則が適用されます。

パターンは、ディレクトリパスの一部またはファイル名に使用できます。
同じディレクトリステップまたはファイル名に複数のパターンを使用できます。
複数のワイルドカードがある場合は、一致するすべてのパス内のファイルが、結果のファイルセットに含められます。

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

使用例については、フォルダーと複数のファイルに対するクエリの実行に関する記事を参照してください。

ファイルメタデータ関数

filename 関数

この関数は、行の生成元のファイル名を返します。

特定のファイルに対してクエリを実行するには、特定のファイルに対するクエリの実行に関する記事の「Filename」セクションをお読みください。

戻り値のデータ型は nvarchar(1024) です。最適なパフォーマンスを確保するために、filename 関数の結果は必ず適切なデータ型にキャストしてください。文字データ型を使用する場合は、適切な長さが使用されていることを確認します。

filepath 関数

この関数は、完全パスまたはパスの一部を返します。

パラメーターを指定せずに呼び出された場合、行の生成元である完全なファイルパスを返します。
パラメーターを指定して呼び出されると、パラメーターで指定された位置にあるワイルドカードと一致するパスの一部を返します。たとえば、パラメーター値 1 は、最初のワイルドカードと一致するパスの一部を返します。

追加情報については、特定のファイルに対するクエリの実行に関する記事の「Filepath」セクションをお読みください。

戻り値のデータ型は nvarchar(1024) です。最適なパフォーマンスを確保するために、filepath 関数の結果は必ず適切なデータ型にキャストしてください。文字データ型を使用する場合は、適切な長さが使用されていることを確認します。

複合型と入れ子または繰り返しのデータ構造を操作する

(Parquet ファイルなどの) 入れ子または繰り返しのデータ型に格納されているデータのスムーズな操作を可能にするために、サーバーレス SQL プールに次の拡張機能が追加されています。

入れ子にされたデータまたは繰り返しのデータを射影する

データを射影するには、入れ子にされたデータ型の列を含む Parquet ファイルに対して SELECT ステートメントを実行します。出力時、入れ子にされた値は JSON にシリアル化され、varchar(8000) SQL データ型として返されます。

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

詳細については、Parquet の入れ子にされた型に対するクエリの実行に関する記事の「入れ子または繰り返しのデータを射影する」セクションを参照してください。

入れ子にされた列から要素にアクセスする

入れ子にされた列 (構造体など) から入れ子にされた要素にアクセスするには、"ドット表記" を使用して、フィールド名をパスに連結します。 OPENROWSET 関数の WITH 句に column_name としてパスを指定します。

構文フラグメントの例を次に示します。

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ({'column_name' 'column_type',})
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

既定で、OPENROWSET では、ソースフィールドの名前とパスを WITH 句に指定された列名と一致させます。同じソース Parquet ファイル内異なる入れ子レベルに含まれている要素には、WITH 句を使用してアクセスできます。

戻り値

関数では、入れ子にされた型グループに含まれていないすべての Parquet の型について、指定した要素から、および指定したパスで、int、decimal、varchar などのスカラー値が返されます。
パスが、入れ子にされた型の要素を指している場合、関数は、指定されたパスの先頭の要素から始まる JSON フラグメントを返します。 JSON フラグメントの型は varchar(8000) です。
指定された column_name でプロパティが見つからない場合、関数はエラーを返します。
指定された column_path でプロパティが見つからない場合、パスモードに応じて、関数は、strict モードのときはエラーを、lax モードのときには null を返します。

クエリのサンプルについては、Parquet の入れ子にされた型に対するクエリの実行に関する記事の「入れ子にされた列から要素にアクセスする」セクションを参照してください。

繰り返される列から要素にアクセスする

配列やマップの要素など、繰り返される列から要素にアクセスするには、射影する必要があるすべてのスカラー要素に対して JSON_VALUE 関数を使用し、次を指定します。

最初のパラメーターとして、入れ子にされた、または繰り返される列
2 番目のパラメーターとして、アクセスする要素またはプロパティを指定する JSON パス

繰り返される列から非スカラー要素にアクセスするには、射影する必要があるすべての非スカラー要素に対して JSON_QUERY 関数を使用し、次を指定します。

最初のパラメーターとして、入れ子にされた、または繰り返される列
2 番目のパラメーターとして、アクセスする要素またはプロパティを指定する JSON パス

次の構文フラグメントをご覧ください。

    SELECT
       { JSON_VALUE (column_name, path_to_sub_element), }
       { JSON_QUERY (column_name [ , path_to_sub_element ]), )
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

繰り返される列から要素にアクセスするためのクエリのサンプルについては、Parquet の入れ子にされた型に対するクエリの実行に関する記事をご覧ください。

クエリのサンプル

さまざまな型のデータに対するクエリの詳細については、サンプルクエリを参照してください。

ツール

クエリを発行するために必要なツール: - Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

デモのセットアップ

最初の手順として、クエリを実行するデータベースを作成します。次に、そのデータベースでセットアップスクリプトを実行して、オブジェクトを初期化します。

このセットアップスクリプトにより、データソース、データベーススコープの資格情報、これらのサンプルでデータの読み取りに使用される外部ファイル形式が作成されます。

Note

このデータベースは、実際のデータではなくメタデータを表示するためにのみ使用されます。使用するデータベース名を書き留めておきます。後で必要になります。

CREATE DATABASE mydbname;

提供されるデモデータ

デモデータには次のデータセットが含まれています。

NYC タクシー - イエロータクシーの移動記録 - CSV および Parquet 形式のパブリック NYC データセットの一部
CSV 形式での母集団データセット
入れ子になった列を含むサンプルの Parquet ファイル
JSON 形式の書籍

フォルダーパス	説明
/csv/	CSV 形式のデータの親フォルダー
/csv/population/ /csv/population-unix/ /csv/population-unix-hdr/ /csv/population-unix-hdr-escape /csv/population-unix-hdr-quoted	異なる CSV 形式の人口データファイルを含むフォルダー。
/csv/taxi/	CSV 形式の NYC パブリックデータファイルを含むフォルダー
/parquet/	Parquet 形式のデータの親フォルダー
/parquet/taxi	Parquet 形式の NYC パブリックデータファイル。Hive または Hadoop パーティション構成を使用して年別および月別に分割されています。
/parquet/nested/	入れ子になった列を含むサンプルの Parquet ファイル
/json/	JSON 形式のデータの親フォルダー
/json/books/	書籍データを含む JSON ファイル

次のステップ

さまざまなファイルの種類に対してクエリを実行する方法、およびビューの作成と使用の詳細については、次の記事を参照してください。

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

概要