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

10 分

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

コンマ区切り値 (CSV) ファイルなどの区切りテキスト。
JavaScript オブジェクト表記 (JSON) ファイル。
Parquet ファイル。

クエリの基本的な構文は、これらすべての種類のファイルで同じであり、OPENROWSET SQL 関数に基づいて構築されています。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 からの出力は、エイリアスを割り当てる必要がある行セットです。前の例では、エイリアス行を使用して、結果の行セットに名前を付けます。

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

注

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

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

https://mydatalake.blob.core.windows.net/data/files/file1.csv: ファイル フォルダーに file1.csv のみを含めます。
https://mydatalake.blob.core.windows.net/data/files/file*.csv: ファイルフォルダー内 のすべての .csv ファイルで、名前が "file" で始まります。
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 に設定し、1 つの NVARCHAR(MAX) 列を含むスキーマで csv 形式を使用する必要があります。このクエリの結果は、次のような 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

このクエリは、次の結果のような行セットを返します。

製品	価格
ウィジェット	12.99
ガジェット	3.99

Parquet ファイルのクエリ

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

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

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

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

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

/詻
- /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 句の番号付き filepath パラメーターは、パラメーター 1 が year=* フォルダー名の * -so BULK パス内のフォルダー名のワイルドカードを参照し、パラメーター 2 は month=* フォルダー名の * です。

フィードバック

このページはお役に立ちましたか?