SQL Server - BULK INSERT

提供：MochiuWiki : SUSE, EC, PCB
2024年11月10日 (日) 20:03時点におけるWiki (トーク | 投稿記録)による版 (→‎BULK INSERTの例)
ナビゲーションに移動 検索に移動

概要

BULK INSERT文は、SQL Serverで大量のデータを高速にテーブルに取り込むための機能である。
通常のINSERT文と比較して、効率的にデータを取り込むことができる。

 -- 使用例
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
 );


重要な特徴として、BULK INSERT文はトランザクションログへの書き込みを最小限に抑えることで高速な処理を実現している。
これは、大量データの取り込み時に大きな効果を発揮する。

データソースとしては、カンマ区切り (CSV) や タブ区切り (TSV) 等の形式のテキストファイルが使用できる。
FIELDTERMINATORオプションは列の区切り文字、ROWTERMINATORオプションは行の区切り文字を指定する。

パフォーマンスに影響を与える重要な要素として、以下に示すような設定がある。

 WITH
 (
    BATCHSIZE = 1000,
    TABLOCK,
    CHECK_CONSTRAINTS = OFF,
    FIRE_TRIGGERS = OFF
 )


これらのオプションを適切に設定することにより、パフォーマンスを向上させることができる。
特に、TABLOCKオプションは、テーブルレベルでのロックを取得することにより、行レベルのロックによるオーバーヘッドを避けることができる。

※注意
BULK INSERT文を使用する場合は、ファイルに対する適切なアクセス権限が必要となる。
また、データファイルの形式とテーブルのスキーマが正確に一致している必要がある。

エラーハンドリングについては、ERRORFILEオプションを使用することにより、取り込みに失敗した行を別ファイルに出力して後から確認することができる。

 WITH
 (
    ERRORFILE = 'C:\Errors\error.txt',
    MAXERRORS = 10
 )


BULK INSERT文は、大量データの取り込みに非常に効果的なツールであるが、適切な設定と事前準備が重要となる。


使用例

カンマ区切りファイルのインポート

# CSVファイルの内容
John,Doe,30,New York
Jane,Smith,25,Los Angeles



 BULK INSERT Employees
 FROM 'D:\Data\employees.csv'
 WITH
 (
    FIRSTROW = 2,                     /* ヘッダー行をスキップ */
    FIELDTERMINATOR = ',',            /* フィールド区切り文字 */
    ROWTERMINATOR = '\n',             /* 行区切り文字 */
    MAXERRORS = 2,                    /* 許容するエラーの最大数 */
    ERRORFILE = 'D:\Data\errors.txt'  /* エラーログファイル */
 );


タブ区切りファイルのインポート (日付フォーマット指定)

# TSVファイルの内容
OrderID    OrderDate    CustomerID    Amount
1001    2024-01-01    CUST001    1500.50



 BULK INSERT Orders
 FROM 'D:\Data\orders.txt'
 WITH
 (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '\t',     /* タブ区切り */
    ROWTERMINATOR = '\n',
    FORMAT = 'CSV',             /* CSVフォーマットを使用 */
    DATE_FORMAT = 'yyyy-MM-dd'  /* 日付フォーマットを指定 */
 );


固定長フォーマットファイルのインポート

# データ例 (各フィールドが固定長)
ID  NAME      DEPT
001 JOHN      SALES
002 MARY      IT



 BULK INSERT Employees
 FROM 'D:\Data\employees_fixed.txt'
 WITH
 (
    DATAFILETYPE = 'char',
    FORMATFILE = 'D:\Data\format.fmt',  /* フォーマットファイルを指定 */
    CODEPAGE = '65001',                 /* UTF-8エンコーディング */
    KEEPNULLS                           /* NULL値を保持 */
 );


XMLフォーマットファイルを使用したインポート

 /* より詳細なデータマッピングが必要な場合 */
 
 BULK INSERT Products
 FROM 'D:\Data\products.txt'
 WITH
 (
    FORMATFILE = 'D:\Data\products_format.xml',
    CHECK_CONSTRAINTS,                           /* 制約チェックを有効化 */
    FIRE_TRIGGERS,                               /* トリガーを発火 */
    KEEPIDENTITY                                 /* ID列の値を保持 */
 );


バッチ処理によるBULK INSERT

 /* 大量データを分割してインポートする場合 */
 
 BULK INSERT LargeTable
 FROM 'D:\Data\large_data.csv'
 WITH
 (
    BATCHSIZE = 1000,          /* バッチサイズを指定 */
    ROWS_PER_BATCH = 10000,    /* バッチあたりの行数 */
    TABLOCK,                   /* テーブルロックを使用 */
    ORDER (OrderColumn ASC)    /* データの順序を指定 */
 );


文字コードに関する使用例

以下の例では、CSVファイル (カンマ区切り) をインポートしている。

 /* 文字コードがShift-JISの場合 */
 
 BULK INSERT T_Table
         FROM 'D:\UnicodeFile.txt'
         WITH (
                 DATAFILETYPE    = 'char',
                 FIELDTERMINATOR = ','
         );
 GO



 /* 文字コードがUNICODEの場合 (SQL Server 2014 R2以前) */
 /* ただし、SQL Server 2014 R2以前では、下記のクエリを実行しても UTF-8形式のファイルは取り込めない */
 /* (UTF-8固有の文字が無いUTF-8形式のファイルはインポートできる) */
 BULK INSERT T_Table
         FROM 'D:\UnicodeFile.txt'
         WITH (
                 DATAFILETYPE    = 'widechar',
                 FIELDTERMINATOR = ','
         );
 GO



 /* 文字コードがUTF-8の場合 (SQL Server 2014 R2以降) */
 /* SQL Server 2014 R2以降では、UTF-8形式のファイルをインポートすることが出来る */
 BULK INSERT T_Table
         FROM 'D:\UnicodeFile.txt'
         WITH (
                 DATAFILETYPE    = 'char',
                 CODEPAGE        = '65001',
                 FIELDTERMINATOR = ','
         );
 GO



エラーハンドリング

 /* 基本的なエラーハンドリングの設定 */
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    ERRORFILE = 'C:\Errors\error.txt',
    MAXERRORS = 10,
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
 );


  • ERRORFILEオプション
    エラーが発生した行の詳細情報を指定されたファイルに出力する。
    このファイルには、エラーの発生した行番号、エラーの種類、具体的なエラーメッセージ等が記録される。

  • MAXERRORSオプション (重要)
    許容するエラーの最大数を指定する。
    この数を超えるとBULK INSERT処理全体が中止される。
    例えば、MAXERRORSオプションを10に設定した場合、11個目のエラーが発生した時点で処理が停止する。

  • CHECK_CONSTRAINTSオプション
    データの検証に関するエラーハンドリングの設定である。
    このオプションをONに設定する場合、制約違反のチェックが行われる。
    ただし、パフォーマンスへの影響があるため、データの信頼性が確保されている場合はOFFにする。

  • KEEPNULLSオプション
    空の文字列をNULLとして扱うかどうかを制御する。
    これにより、NULL制約に関連するエラーを適切に処理することができる。


また、詳細なエラー情報を取得する場合は、TRY-CATCHブロックを使用する。

 /* BULK INSERTの基本的なエラーハンドリング設定 */
 /* ERRORFILEにエラー内容を出力し、最大10件のエラーまで許容 */
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    /* エラー情報を出力するファイルパスを指定 */
    ERRORFILE = 'C:\Errors\error.txt',
    /* 許容するエラーの最大数。この数を超えると処理が中止される */
    MAXERRORS = 10,
    /* ヘッダ行をスキップし、2行目からデータとして取り込む */
    FIRSTROW = 2,
    /* 列の区切り文字をカンマに指定 */
    FIELDTERMINATOR = ',',
    /* 行の区切り文字を改行に指定 */
    ROWTERMINATOR = '\n'
 );
 
 /* TRY-CATCHブロックを使用したより詳細なエラーハンドリング */
 BEGIN TRY
    BULK INSERT TargetTable
    FROM 'C:\Data\source.txt'
    WITH
    (
       /* エラー情報の出力先を指定 */
       ERRORFILE = 'C:\Errors\error.txt',
       /* 10件までのエラーを許容 */
       MAXERRORS = 10
    );
 END TRY
 BEGIN CATCH
    /* エラー発生時に詳細情報を取得 */
    SELECT 
       /* エラー番号を取得 */
       ERROR_NUMBER() AS ErrorNumber,
       /* エラーメッセージの詳細を取得 */
       ERROR_MESSAGE() AS ErrorMessage,
       /* エラーの重要度を取得 (0-25の値) */
       ERROR_SEVERITY() AS ErrorSeverity,
       /* エラーの状態コードを取得 */
       ERROR_STATE() AS ErrorState;
 END CATCH


実務では、エラーファイルの内容を定期的に監視して、必要に応じて修正アクションを取ることが推奨される。
エラーファイルには、以下に示すような情報が含まれる。

  • エラーが発生した行番号
  • エラーの発生した列
  • エラーの種類 (データ型の不一致、制約違反等)
  • 具体的なエラーメッセージ


エラー発生時のロールバック動作についても理解が重要である。
BATCHSIZEオプションを使用している場合、エラーが発生したバッチのみがロールバックされて、それ以前に正常に取り込まれたデータは保持される。

 /* ターゲットテーブルに対するBULK INSERT設定 (NULL値とバッチ処理の制御) */
 
 BULK INSERT TargetTable
 FROM 'C:\Data\source.txt'
 WITH
 (
    /* 空の文字列をNULLとして扱うのではなく、空文字として保持 */
    /* このオプションを指定しない場合、空文字列は自動的にNULLに変換される */
    KEEPNULLS,
 
    /* 一度に処理する行数を5000行に設定 */
    /* メモリ使用量とパフォーマンスのバランスを取るために使用 */
    /* 大きすぎる値はメモリ消費が増加し、小さすぎる値は処理時間が増加 */
    BATCHSIZE = 5000,
 
    /* テーブルの制約チェックを有効化 */
    /* 外部キー制約、CHECK制約、一意性制約などのすべての制約を確認 */
    /* パフォーマンスは低下するが、データの整合性が保証される */
    CHECK_CONSTRAINTS,
 
    /* エラー発生時の情報を出力するファイルを指定 */
    ERRORFILE = 'C:\Errors\error.txt',
 
    /* 最大エラー数を設定 (この数を超えると処理が中止) */
    MAXERRORS = 10,
 
    /* データファイルの区切り文字設定 */
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
 
    /* ヘッダ行をスキップ */
    FIRSTROW = 2
 )
 
 /* エラーハンドリングのためのTRY-CATCHブロック */
 BEGIN TRY
    /* 上記のBULK INSERT文を実行 */
    /* 注意: EXEC内のBULK INSERT文は、上記のWITHブロックと同じ設定を使用 */
    EXEC('
       BULK INSERT TargetTable
       FROM ''C:\Data\source.txt''
       WITH
       (
          KEEPNULLS,
          BATCHSIZE = 5000,
          CHECK_CONSTRAINTS,
          ERRORFILE = ''C:\Errors\error.txt'',
          MAXERRORS = 10,
          FIELDTERMINATOR = '','',
          ROWTERMINATOR = ''\n'',
          FIRSTROW = 2
       )
    ');
 
    /* 正常終了時のログ出力 */
    PRINT 'BULK INSERT successfully completed';
 END TRY
 BEGIN CATCH
    /* エラー情報の取得と出力 */
    SELECT 
       GETDATE() AS ErrorTime,             /* エラー発生時刻 */
       ERROR_NUMBER() AS ErrorNumber,      /* エラー番号 */
       ERROR_SEVERITY() AS ErrorSeverity,  /* エラーの重要度 */
       ERROR_STATE() AS ErrorState,        /* エラーの状態 */
       ERROR_PROCEDURE() AS ErrorProc,     /* エラーが発生したプロシージャ */
       ERROR_LINE() AS ErrorLine,          /* エラーが発生した行番号 */
       ERROR_MESSAGE() AS ErrorMessage;    /* エラーメッセージの詳細 */

    /* エラーのログテーブルへの挿入 (オプション) */
    INSERT INTO ErrorLog (
       ErrorTime, 
       ErrorNumber, 
       ErrorMessage
    )
    VALUES (
       GETDATE(),
       ERROR_NUMBER(),
       ERROR_MESSAGE()
    );
 END CATCH;


ただし、実務では、以下に示すように動的なパラメータを使用することが一般的である。
変数を使用することにより柔軟な運用が可能になり、また、パラメータ化することで再利用性も高まる。

 /* 動的パラメータの使用 */
 
 DECLARE 
    @FilePath NVARCHAR(500)  = 'C:\Data\source.txt',
    @ErrorPath NVARCHAR(500) = 'C:\Errors\error.txt',
    @BatchSize INT = 5000,
    @MaxErrors INT = 10,
    @BulkCmd NVARCHAR(MAX);
 
 /* 動的なBULK INSERT文を構築 */
 SET @BulkCmd = '
    BULK INSERT TargetTable
    FROM ''' + @FilePath + '''
    WITH
    (
       KEEPNULLS,
       BATCHSIZE       = ' + CAST(@BatchSize AS NVARCHAR(10)) + ',
       CHECK_CONSTRAINTS,
       ERRORFILE       = ''' + @ErrorPath + ''',
       MAXERRORS       = ' + CAST(@MaxErrors AS NVARCHAR(10)) + ',
       FIELDTERMINATOR = '','',
       ROWTERMINATOR   = ''\n'',
       FIRSTROW        = 2
    )';
 
 BEGIN TRY
    /* 構築したBULK INSERT文を実行 */
    EXEC(@BulkCmd);
 
    /* 正常終了時のログ出力 */
    PRINT 'BULK INSERT successfully completed';
 END TRY
 BEGIN CATCH
    /* エラー情報の取得と出力 */
    SELECT 
       GETDATE()         AS ErrorTime,      /* エラー発生時刻 */
       ERROR_NUMBER()    AS ErrorNumber,    /* エラー番号 */
       ERROR_SEVERITY()  AS ErrorSeverity,  /* エラーの重要度 */
       ERROR_STATE()     AS ErrorState,     /* エラーの状態 */
       ERROR_PROCEDURE() AS ErrorProc,      /* エラーが発生したプロシージャ */
       ERROR_LINE()      AS ErrorLine,      /* エラーが発生した行番号 */
       ERROR_MESSAGE()   AS ErrorMessage;   /* エラーメッセージの詳細 */

    /* エラーのログテーブルへの挿入 (オプション) */
    INSERT INTO ErrorLog (
       ErrorTime, 
       ErrorNumber, 
       ErrorMessage
    )
    VALUES (
       GETDATE(),
       ERROR_NUMBER(),
       ERROR_MESSAGE()
    );
 END CATCH;


また、上記で使用しているオプションのカスタムログテーブルを以下に示す。

 CREATE TABLE ErrorLog (
    ErrorTime DATETIME,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(4000)
    /* 必要に応じて他のカラムも追加 */
 );



「http://mochiuwiki.e2.valueserver.jp/index.php?title=SQL_Server_-_BULK_INSERT&oldid=12062」から取得