OPENROWSET BULK(Transact-SQL)

構文

SQL Server、Azure SQL Database、Fabric 内の SQL データベース、Azure SQL Managed Instance:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

Fabric Data Warehouse の構文

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |
   ESCAPECHAR = 'escape_char' |
   HEADER_ROW = [true|false] |
   PARSER_VERSION = 'parser_version' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ROWS_PER_BATCH = rows_per_batch

• <drive letter>:\<file path> ローカル ディスク上のファイルにアクセスするには
• \\<network-share\<file path> ネットワーク共有上のファイルにアクセスするには
• adls://<container>@<storage>.dfs.core.windows.net/<file path> Azure Data Lake Storage にアクセスするには
• abs://<storage>.blob.core.windows.net/<container>/<file path> Azure Blob Storage にアクセスするには
• s3://<ip-address>:<port>/<file path> s3 互換ストレージにアクセスするには

SQL Server 2017 (14.x) 以降では、 data_file を Azure Blob Storage に格納できます。 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。

• https://<storage>.blob.core.windows.net/<container>/<file path> Azure Blob Storage または Azure Data Lake Storage にアクセスするには
• https://<storage>.dfs.core.windows.net/<container>/<file path> Azure Data Lake Storage にアクセスするには
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> Azure Data Lake Storage にアクセスするには
• https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/<file path> - Microsoft FabricでOneLakeにアクセスする

Azure Data Lake Storage Gen2に保存されたデータにアクセスする際は、Blobエンドポイントの代わりに abfss://<container>@<storage>.dfs.core.windows.net/<file path> または https://<storage>.dfs.core.windows.net/<container>/<file path> URI形式を使用してください。 両者とも階層的な名前空間(HNS)を完全にサポートしており、これによりディレクトリの意味論、最適化されたファイル操作、POSIXスタイルのアクセス制御リスト(ACL)が可能になります。

対照的に、 blob 端点は階層的な名前空間の特徴を公開せず、すべてのパスをフラットオブジェクトキーとして扱います。 これにより、パフォーマンス低下、ディレクトリの挙動制限、Azure Data Lake Storage Gen2のファイルシステムのセマンティクスを期待するエンジンとの互換性の欠如が生じる可能性があります。

OPENROWSET(BULK)を使って、Microsoft FabricのOneLakeに保存されたファイル、特にFabric LakehouseのFilesフォルダから直接データを読み取ることができます。 この機能により、ADLS Gen2やBlob Storageなどの外部ステージアカウントの必要性がなくなり、Fabric権限を用いてワークスペース制御されたSaaSネイティブの取り込みが可能になります。 この機能では、次の機能がサポートされます。

• Lakehouses の Files フォルダーからの読み取り
• ワークスペースからウェアハウスへの読み込み (同じテナント内)
• Microsoft Entra ID を使用したネイティブ ID の適用

COPY INTOとOPENROWSET(BULK)の両方に適用される制限を確認してください。

パス内のファイル拡張子が.csv、.tsv、.parquet、.parq、.jsonl、.ldjson、.ndjsonで終わる場合は、FORMATオプションを指定する必要はありません。 例えば、 OPENROWSET(BULK) 関数は以下の例の拡張に基づいてフォーマットがパーケットであることを認識しています。

SELECT *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

ファイル パスが次のいずれかの拡張子で終わらない場合は、次のように FORMATを指定する必要があります。

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='PARQUET'
)

FORMATFILE

フォーマット ファイルの完全パスを指定します。 SQL Server では、次の 2 種類のフォーマット ファイルがサポートされます: XML と非 XML。

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

結果セット内の列タイプを定義するにはフォーマットファイルが必要です。 唯一の例外は SINGLE_CLOB、 SINGLE_BLOB、または SINGLE_NCLOBを指定する場合で、この場合はフォーマットファイルは必要ありません。

フォーマットファイルの詳細については、「Use a format file to bulk import data(SQL Server)」をご覧ください。

2017 SQL Server(14.x)からは、format_file_pathがAzure Blob Storageに移行できるようになります。 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE は、フォーマット ファイル パスのルートの場所を定義します。 このデータソースを使えば、 FORMATFILE オプションで相対パスを利用できます。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

CREATE EXTERNAL DATA SOURCEでフォーマットファイルデータソースを作成します。 ルートロケーションに加えて、その場所にあるファイルにアクセスするためのカスタム認証情報を定義することも可能です。

PARSER_VERSION

対象: Fabricデータウェアハウスのみ

ファイルの読み取り時に使用するパーサーのバージョンを指定します。 現在サポートされている CSV パーサーのバージョンは 1.0 と 2.0 です。

• PARSER_VERSION = 「1.0」
• PARSER_VERSION = 「2.0」

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='CSV',
      PARSER_VERSION = '2.0'
)

CSVパーサーバージョン2.0はパフォーマンス最適化されたデフォルトの実装ですが、バージョン1.0で利用可能なすべてのレガシーオプションやエンコーディングをサポートしているわけではありません。 OPENROWSETを使用すると、Fabric Data Warehouseはバージョン1.0のみをサポートするオプションを使うと自動的にバージョン1.0にフォールバックします。バージョンが明示的に指定されていなくてもです。 場合によっては、パーサーバージョン2.0で報告されたサポートされていない特徴によるエラーを解決するために、バージョン1.0を明示的に指定する必要があるかもしれません。

CSV パーサー バージョン 1.0 の詳細:

• 次のオプションはサポートされていません。HEADER_ROW。
• 既定のターミネータは、 \r\n、 \n 、 \rです。
• 行ターミネータとして \n (改行) を指定すると、自動的に \r (復帰) 文字が付加され、結果として行ターミネータが \r\nされます。

CSV パーサー バージョン 2.0 の詳細:

• すべてのデータ型がサポートされているわけではありません。
• 列の最大長は 8,000 文字です。
• 行の最大サイズの上限は 8 MB です。
• 次のオプションはサポートされていません: DATA_COMPRESSION。
• 引用符で囲まれた空の文字列 ("") は、空の文字列として解釈されます。
• DATEFORMAT SET その選択肢は認められません。
• 日付データ型でサポートされている形式:YYYY-MM-DD
• 時刻データ型でサポートされている形式:HH:MM:SS[.fractional seconds]
• datetime2 データ型でサポートされている形式:YYYY-MM-DD HH:MM:SS[.fractional seconds]
• 既定のターミネータは \r\n と \nです。

ESCAPE_CHAR

ファイル内のエスケープに使用される文字と、ファイル内のすべての区切り記号値を指定します。次に例を示します。

Place,Address,Icon
Empire State Building,20 W 34th St\, New York\, NY 10118,\\icons\\sol.png
Statue of Liberty,Liberty Island\, New York\, NY 10004,\\icons\\sol.png

エスケープ文字の後にそれ自体以外の値、またはいずれかの区切り記号の値が続く場合は、その値を読み取るときにエスケープ文字が削除されます。

ESCAPECHAR パラメーターは、FIELDQUOTEが有効かどうかに関係なく適用されます。 引用文字をエスケープするために使用されることはありません。 引用文字は、別の引用文字でエスケープする必要があります。 引用符文字は、値が引用符文字でカプセル化されている場合にのみ、列値内に表示できます。

次の例では、コンマ (,) と円記号 (\) がエスケープされ、 \, および \\として表されます。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ESCAPECHAR = '\'
);

HEADER_ROW

CSV ファイルに、他のデータ行と共に返してはならないヘッダー行を含めるかどうかを指定します。 ヘッダーを含む CSV ファイルの例を次の例に示します。

Place,Latitude,Longitude,Address,Area,State,Zipcode
Empire State Building,40.748817,-73.985428,20 W 34th St,New York,NY,10118
Statue of Liberty,40.689247,-74.044502,Liberty Island,New York,NY,10004

既定値は FALSE です。 Fabric Data Warehouseで PARSER_VERSION='2.0' サポートされています。 TRUE場合、列名は引数に従って最初の行FIRSTROW読み取られます。 TRUEとスキーマがWITHを使用して指定されている場合、列名のバインドは序数位置ではなく列名によって行われます。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    HEADER_ROW = TRUE
);

ORDER ( { column [ ASC |DESC ] } [ ,... n ] [ UNIQUE ] )

データ ファイル内のデータの並べ替え方法を指定するオプションのヒント。 既定では、一括操作はデータ ファイルが並べ替えられていないことを前提に実行されます。 クエリ オプティマイザーが順序を利用して、より効率的なクエリ プランを生成できる場合、パフォーマンスが向上する可能性があります。 次の一覧は、並べ替えを指定する場合に役立つ可能性がある例を示しています。

• クラスター化インデックスを持ち、クラスター化インデックス キーで行セット データが並べ替えられているテーブルに行を挿入する場合。
• 行セットを別のテーブルに結合するとき、並べ替え列と結合列が一致する場合。
• 並べ替え列で行セット データを集約する場合。
• 並べ替え列と結合列が一致するクエリの FROM 句で、行セットをソース テーブルとして使用します。

UNIQUE

データ ファイルに重複するエントリがないことを指定します。

データファイルの実際の行が指定された順序に従ってソートされていなかったり、 UNIQUE のヒントや重複キーが存在すると指定した場合、エラーが返されます。

ORDERを使う場合は列のエイリアスが必要です。 列のエイリアスリストは、 BULK 節がアクセスする派生テーブルを参照しなければなりません。 ORDER節で指定した列名は、この列の別名リストを指します。 大きな値型(varchar(max)、nvarchar(max)、varbinary(max)、xml)や大きなオブジェクト(LOB)型(テキスト、ntext、画像)の列を指定することはできません。

コンテンツ オプション

SINGLE_BLOB

varbinary(max)型の単一行の単一列行セットとしてdata_fileの内容を返します。

SINGLE_CLOB

data_fileをASCIIとして読み取り、現在のデータベースの照合を用いてvarchar(max)型の単一行・単一列の行セットとして内容を返します。

SINGLE_NCLOB

data_fileをUnicodeとして読み込み、現在のデータベースの照合を使ってnvarchar(max)型の単一行・単一列行セットとして内容を返します。

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

WITH schema

WITH スキーマは、OPENROWSET関数の結果セットを定義する列を指定します。 リターン OPENROWSET すべての列の列定義を含み、基礎となるファイルの列を結果セット内の列にバインドするマッピングルールを概説しています。

次に例を示します。

• country_region列はvarchar(50)型を持ち、同じ名前の基礎列を参照しています。
• date列はCSVやParquetの列、または異なる物理名を持つJSONLプロパティを参照します。
• cases列はファイルの3番目の列を参照しています。
• fatal_cases列は入れ子にされたParquetプロパティまたはJSONLサブオブジェクトを参照します。

SELECT *
FROM OPENROWSET(<...>) 
WITH (
        country_region varchar(50), --> country_region column has varchar(50) type and referencing the underlying column with the same name
        [date] DATE '$.updated',   --> date is referencing a CSV/Parquet column or JSONL property with a different physical name
        cases INT 3,             --> cases is referencing third column in the file
        fatal_cases INT '$.statistics.deaths'  --> fatal_cases is referencing a nested Parquet property or JSONL sub-object
     );

<Column_name>

結果の行セットで OPENROWSET 返される列の名前です。 OPENROWSET 同じ名前の基礎ファイル列からこの列のデータを読み込みますが、 <column_path> や <column_ordinal>でオーバーライドしない限り。 列名は列 名識別子のルールに従う必要があります。

<column_type>

結果セット内の列の T-SQL 型。 OPENROWSET 結果を返す際に基礎ファイルからこのタイプに変換します。 詳細については、「 Fabric Warehouse のデータ型」を参照してください。

<column_path>

Parquet などの複合型の入れ子になったフィールドを参照するために使用されるドット区切りのパス ( $.description.location.latなど)。

<column_ordinal>

これは、列の物理的インデックスを表し、その列が WITH 節の列に写るものです。

• OPENROWSET(BULK...)をINSERT文やMERGE文のソーステーブルとして使うことで、データファイルからテーブルに一括インポートできます。 詳細については、「BULK INSERTまたは OPENROWSET(BULK...) を使用してデータをSQL Serverにインポートする」を参照してください。
• INSERT文でOPENROWSET BULKオプションを使うと、BULK節はテーブルヒントをサポートしています。 TABLOCK などの通常のテーブル ヒントに加えて、BULK 句では、次の特殊なテーブル ヒントを使用できます: IGNORE_CONSTRAINTS (CHECK および FOREIGN KEY 制約のみ無視します)、IGNORE_TRIGGERS、KEEPDEFAULTS、KEEPIDENTITY。 詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。
• INSERT...SELECT * FROM OPENROWSET(BULK...) ステートメントの使用方法については、「データの一括インポートと一括エクスポート (SQL Server)」をご覧ください。 バルクインポートで実行される行挿入操作がトランザクションログに記録されるタイミングについては、「 最小限のログ・インバーク・インポートのPrerequisites」を参照してください。
• 完全なリカバリーモデルで OPENROWSET (BULK ...) データをインポートすると、ログの最適化はできません。

Microsoft Fabric Data Warehouse、以下の表はサポートされている機能をまとめています。

SQLCHAR、SQLNCHAR、または SQLBINARY データの一括インポート

OPENROWSET(BULK...) 特に指定しない場合、 SQLCHAR、 SQLNCHAR、または SQLBINARY の最大データ長が8,000バイトを超えないと仮定しています。 もしLOBデータフィールドにvarchar(max)、nvarchar(max)、またはvarbinary(max)のいずれかのオブジェクトが8,000バイトを超えるものを含むデータをインポートする場合、データフィールドの最大長さを定義したXMLフォーマットファイルを使う必要があります。 最大長さを指定するには、フォーマットファイルを編集して MAX_LENGTH 属性を宣言します。

SQLXML ドキュメントの一括エクスポートまたは一括インポート

SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマット ファイルのデータ型には次のいずれかを使用します。

A. OPENROWSETを使ってファイルをvarbinary(max)列にまとめてデータBULK INSERT

適用対象: SQL Server のみ。

次の例では、デモンストレーション用の小さなテーブルを作成し、Text1.txt ルート ディレクトリにある C: という名前のファイルから varbinary(max) 列にファイル データを挿入します。

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. フォーマット ファイルで OPENROWSET BULK プロバイダーを使用してテキスト ファイルから行を取得する

適用対象: SQL Server のみ。

次の例では、フォーマット ファイルを使用して、タブ区切りのテキスト ファイル values.txt から行を取得します。このテキスト ファイルには次のデータが含まれます。

1     Data Item 1
2     Data Item 2
3     Data Item 3

フォーマット ファイル values.fmt では、values.txt の列が次のように表されています。

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

このクエリは、そのデータを取得します。

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. フォーマット ファイルとコード ページを指定する

適用対象: SQL Server のみ。

次の例は、フォーマット ファイルとコード ページの両方のオプションを同時に使用する方法を示しています。

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. フォーマット ファイルを使用して CSV ファイルからデータにアクセスする

適用対象: SQL Server 2017 (14.x) 以降のバージョンのみ。

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. フォーマット ファイルなしで CSV ファイルからデータにアクセスする

適用対象: SQL Server のみ。

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

F. Azure Blob Storage に格納されているファイルからデータにアクセスする

適用対象: SQL Server 2017 (14.x) 以降のバージョンのみ。

SQL Server 2017 (14.x) 以降のバージョンでは、次の例では、Azure ストレージ アカウント内のコンテナーと、共有アクセス署名用に作成されたデータベース スコープ資格情報を指す外部データ ソースを使用しています。

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

資格情報と外部データ ソースの構成を含む完全な OPENROWSET 例については、「 Azure Blob Storage のデータへの一括アクセスの例を参照してください。

G. Azure Blob Storage に格納されているファイルからテーブルにインポートする

以下の例では、OPENROWSETコマンドを使って、SASキーを作成したAzure BlobストレージのCSVファイルからデータを読み込む方法を示しています。 Azure Blobのストレージ場所を外部データソースとして設定します。 このプロセスには、ユーザーデータベース内のマスターキーを使って暗号化された共有アクセス署名(SAS)を使用するデータベーススコープ認証情報が必要です。

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. 外部ソースにマネージド ID を使う

適用対象: Azure SQL Managed Instance と Azure SQL Database

以下の例はマネージドIDを使って認証情報を作成し、外部ソースを作成し、その外部ソースにホストされたCSVからデータを読み込みます。

まず資格情報を作成し、BLOB ストレージを外部ソースとして指定します。

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

次に、BLOB ストレージ上でホストされている CSV ファイルからデータを読み込みます。

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. OPENROWSET を使用して、S3 互換オブジェクト ストレージを使用して複数の Parquet ファイルにアクセスする

適用対象: SQL Server 2022 (16.x) 以降のバージョン。

以下の例は、異なる場所から複数のParquetファイルにアクセスし、すべてS3互換のオブジェクトストレージに保存されています。

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. OPENROWSET を使用して Azure Data Lake Gen2 から複数の Delta テーブルにアクセスする

適用対象: SQL Server 2022 (16.x) 以降のバージョン。

この例では、データ テーブル コンテナーの名前は Contoso で、Azure Data Lake Gen2 ストレージ アカウントにあります。

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. OPENROWSET を使用してパブリック匿名データセットのクエリを実行する

次の例では、公開されている NYC 黄色のタクシー乗車レコードのオープン データ セットを使用します。

最初にデータ ソースを作成します。

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

名前パターンに一致するフォルダー内 .parquet 拡張子を持つすべてのファイルに対してクエリを実行します。

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

A. Azure Blob Storage から Parquet ファイルを読み取る

次の例では、Parquet ファイルから 100 行を読み取る方法を確認できます。

SELECT TOP 100 * 
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

B. カスタム CSV ファイルを読み取る

以下の例では、ヘッダー行と行とフィールドを区切る明確に指定された終端文字を持つCSVファイルから行を読み取る方法が見られます。

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
 HEADER_ROW = TRUE,
 ROW_TERMINATOR = '\n',
 FIELD_TERMINATOR = ',');

C. ファイルの読み取り中にファイル列スキーマを指定する

次の例では、 OPENROWSET 関数が返す行のスキーマを指定します:

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') 
WITH (
        updated DATE
        ,confirmed INT
        ,deaths INT
        ,iso2 VARCHAR(8000)
        ,iso3 VARCHAR(8000)
        );

D. パーティション・データ・セットの読み取り

以下の例では、 filepath() 関数を使ってマッチしたファイルパスからURIの部分を読み取っています。

SELECT TOP 10 
  files.filepath(2) AS area
, files.*
FROM OPENROWSET(
BULK 'https://<storage account>.blob.core.windows.net/public/NYC_Property_Sales_Dataset/*_*.csv',
 HEADER_ROW = TRUE) 
AS files
WHERE files.filepath(1) = '2009';

E. JSONL ファイルの読み取り中にファイル列スキーマを指定する

次の例では、 OPENROWSET 関数の結果として返される行のスキーマを明示的に指定する方法を確認できます。

SELECT TOP 10 *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.dfs.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') 
WITH (
        country_region varchar(50),
        date DATE '$.updated',
        cases INT '$.confirmed',
        fatal_cases INT '$.deaths'
     );

JSONL ファイルの場合、列名がプロパティ内の列の物理名と一致しない場合は、型定義の後の JSON パスで物理名を指定できます。 複数のプロパティを使用できます。 たとえば、parquet 複合型または JSON サブオブジェクトで入れ子になったプロパティを参照 $.location.latitude 。

その他の例

• OPENROWSET 関数を使用してファイルの内容を参照する
• Fabric Data Warehouse のパフォーマンス ガイドライン
• Transact-SQL を使用してウェアハウスにデータを取り込む

A. OPENROWSETを使ってFabric LakehouseからCSVファイルを読み取ってください

この例では、OPENROWSETを使ってFabric Lakehouse上のCSVファイルを読み取っています。 ファイル名は customer.csv で、 Files/Contoso/ フォルダに保存されます。 データソースやデータベースのスコープ認証情報を提供しないため、Fabric SQLデータベースはEntra IDのコンテキストを使って認証します。

SELECT * FROM OPENROWSET 
( BULK ' abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/customer.csv' 
, FORMAT = 'CSV' 
, FIRST_ROW = 2 
) WITH 
(  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    CountryFull NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6) ) AS DATA 

B. OPENROWSETを使ってFabric Lakehouseからファイルを読み込み、新しいテーブルにデータを挿入します

この例では、 OPENROWSET を使って store.parquetという名前のParquetファイルからデータを読み込みます。 その後、 INSERT を使って新しいテーブルにデータを追加します。 Store。 パーケットファイルはFabric Lakehouseに設置されています。 データソースやデータベースの範囲認証情報を提供しないため、FabricのSQLデータベースはEntra IDのコンテキストを使って認証します。

SELECT * 
FROM OPENROWSET 
(BULK 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' )
 AS dataset; 

-- insert into new table 
SELECT * 
INTO Store 
FROM OPENROWSET 
(BULK 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' ) 
 AS STORE; 

その他の例

OPENROWSET(BULK...)の使用を示すその他の例については、次の記事を参照してください。

• データの一括インポートと一括エクスポート (SQL Server)
• XML ドキュメントの一括インポートと一括エクスポートの例 (SQL Server)
• データの一括インポート時の ID 値の保持 (SQL Server)
• 一括インポート中の NULL または既定値の保持 (SQL Server)
• データの一括インポートでのフォーマット ファイルの使用 (SQL Server)
• 文字形式を使用したデータのインポートまたはエクスポート (SQL Server)
• フォーマット ファイルを使用したテーブル列のスキップ (SQL Server)
• フォーマット ファイルを使用したデータ フィールドのスキップ (SQL Server)
• フォーマット ファイルを使用したテーブル列とデータ ファイル フィールドのマッピング (SQL Server)
• Azure SQL Managed Instances で OPENROWSET を使用してデータ ソースにクエリを実行する
• フィールドターミネータと行ターミネータを指定する (SQL Server)