エラーファイルを使用した Transact-SQL 取り込みエラーのトラブルシューティング

適用対象:✅ Warehouse in Microsoft Fabric

この記事では、T-SQL インジェスト パターンでのインジェスト エラーのトラブルシューティング方法について説明します。

COPY INTO、BULK INSERT、OPENROWSET、およびCTASステートメントのINSERT、UPDATE、MERGE関数を使用したウェアハウスへのインジェストは、いくつかの理由で失敗する可能性があります。 ソース ファイルの値がテーブル スキーマと一致しない可能性があります。 必要な値が見つからない可能性があります。 インジェスト オプションも正しく構成されていない可能性があります。

このトラブルシューティング ガイドでは、拒否された行の診断情報を使用してエラーを解決し、行レベルのエラーをキャプチャし、エラー メタデータを使用して拒否された行を検査します。

COPY INTOやその他のインジェスト コマンドによって生成されたエラー ファイルを調べることで、取り込みに失敗した行とその理由を正確に特定できます。 この情報は、データ品質の問題を特定したり、インジェスト設定を調整したり、ソース データを修正したり、負荷を確実に再実行したりするのに役立ちます。

ターゲット テーブルを作成する

インジェスト コマンドを実行する前に、変換とデータ品質の問題を早期にキャッチできるように、厳密な型と NOT NULL 制約を持つ変換先テーブルを作成します。

1. ウェアハウスワークスペースで、ウェアハウスを開きます。

2. [ ホーム ] タブで、[ 新しい SQL クエリ] を選択します。

   

3. 次のステートメントを実行します。

   DROP TABLE IF EXISTS dbo.TaxiTrips;
   GO
   CREATE TABLE dbo.TaxiTrips
   (
       vendorID         int    NOT NULL,
       startLat         float  NOT NULL,
       startLon         float  NOT NULL,
       endLat           float  NOT NULL,
       endLon           float  NOT NULL,
       passengerCount   int    NOT NULL,
       tripDistance     float  NOT NULL,
       fareAmount       float  NOT NULL,
       mtaTax           float  NOT NULL,
       totalAmount      float  NOT NULL
   );
   

COPY INTO での取り込みやTransact-SQL での取り込みなど、サポートされている複数の方法を使用できます。 データ ソース、形式、自動化の要件に最適なインジェスト方法を選択します。 次の COPY INTO の例は、外部ファイルからテーブルにデータを読み込むための一般的なインジェスト パターンを示しています。

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
WITH ( FILE_TYPE = 'CSV' );

ソース ファイルが変換先テーブル スキーマと一致しない場合、このステートメントはデータの取り込みに失敗する可能性があります。 一般的な原因としては、列数の不一致、互換性のないデータ型、ターゲット テーブルに格納できない値などがあります。 インジェストで変換先スキーマに変換できない値が検出された場合、ステートメントは次のようなエラーを返します。

Msg 13812, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage)
for row starting at byte offset 0, column 1 (vendorID).
Underlying data description:
file 'https://....blob.core.windows.net/Files/yellow/tripdata.csv'.

このエラーは、1 つ以上の行を変換先の列の種類に変換できないことを示します。

MAXERRORS と ERRORFILE を使用してエラーを調査する

行レベルのエラーの数が定義されたしきい値を下回った場合にインジェストを続行し、指定した場所に診断の詳細を格納するには、次のオプションを使用します。

• MAXERRORS は、インジェスト中に許容される行レベルのエラーの最大数を設定します。
• ERRORFILE は、拒否された行とエラーの詳細をデータベースが書き込む場所を指定します。

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
WITH (
    FILE_TYPE = 'CSV',
    MAXERRORS = 10,
    ERRORFILE = 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
);

読み込み操作は、拒否された行の数が MAXERRORSよりも小さい場合にのみ成功します。 エラーがキャプチャされると、インジェスト操作は次のように書き込みます。

• error.jsonl 構造化診断用
• row.csv 拒否されたソース行の場合

拒否された行を検索してクエリを実行する

データベースは、構成されたエラーの場所の下にある構造化フォルダー階層にエラー情報を書き込みます。 これらのフォルダーは、特定の実行をトレースし、診断を 1 つのインジェスト ステートメントに関連付けるのに役立ちます。

ERRORFILE/
+-- _rejectedrows/
    +-- <timestamp>/
        +-- <statement_id>/
            +-- error.jsonl
            +-- row.csv or rows.jsonl

OPENROWSETを使用して、error.jsonlの構造化診断を読み取り、失敗した値、影響を受けた宛先列、および失敗した行の発生場所を特定できます。

SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/error.jsonl'
);

通常、結果セットには、拒否されたレコードごとに 1 行が含まれます。次に例を示します。

error.jsonl ファイルには、1 行に 1 つの JSON オブジェクトが含まれています。 各オブジェクトには、前の表に示したプロパティが含まれています。 次の表では、各プロパティについて詳しく説明します。

拒否された行を確認する

構造化された診断情報を確認したら、データベースが取り込めなかった元のソース データを調べることができます。 拒否された行の出力には、ソース レコードのコピーが含まれています。入力ファイルに表示されたとおりに保持されます。 拒否された行の診断では、インジェストに失敗したレコードのみを含むファイルが生成されます。

• COPY INTO (FILE_TYPE = 'CSV')を使用して CSV ファイルを取り込む場合、拒否された出力にはrow.csv ファイルが含まれます。 このファイルはソース ファイルの構造と一致し、無効な値を持つ元の CSV 行が含まれています。
• OPENROWSET(FORMAT = 'JSONL')を使用して JSONL ファイルを取り込む場合、拒否された出力にはrow.jsonl ファイルが含まれます。 このファイルは、インジェストエラーの原因となった元の JSON オブジェクトを保持します。

これらのファイルを使用して、正しくない値、予期しない NULL 値、データとして誤って解析されたヘッダー行など、エラーの根本原因を検証します。

SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/row.csv'
);

row.csv スキーマはソース CSV 図形と一致し、インジェストに失敗した行のみが含まれます。

「rejected-row」行の出力例:

この診断情報に基づいて、次のインジェストの問題を特定できます。

• ソース ファイル内のヘッダー行が誤ってデータ行として解析されます。 解決するには、 COPY INTO ステートメントで FIRSTROW = 2 オプションを使用する必要があります。
• vendorID列 (C1) のソース ファイル内の行にはNULL値が含まれていますが、変換先TaxiTripsテーブル内の対応する列はNOT NULLとして定義されます。
• passengerCount列のソース ファイルの行に、変換先の N/A 列に変換できない無効な値 () が含まれています。

インジェストの問題を修正し、データを再取り込みする

インジェスト エラーの原因を特定したら、問題を修正し、影響を受けるデータを再取り込みます。 修復方法は、エラーの発生元によって異なります。

変換先テーブルのスキーマを修正する

ソース データが宛先テーブル スキーマに準拠していない場合は、テーブル定義を更新します。 一般的な修正には、列データ型の変更や、 NOT NULLなどの制限付き制約の削除が含まれます。

一部のシナリオでは、データを再取り込みする前に、コピー先テーブルを削除して再作成することが必要になる場合があります。

ソース データの修正とファイルの再取り込み

ソース ファイル内の無効な値または一貫性のない値が原因でインジェストが失敗した場合は、それらの値を修正し、データを再取り込みします。 たとえば、 N/A などのプレースホルダー値を空の値や有効な既定値に置き換えます。

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/tripdata_corrected.csv'
WITH ( FILE_TYPE = 'CSV' );

修正されたデータを再取り込みする場合は、元のファイルを参照するフォルダー パスではなく、修正されたデータのみを含む新しいファイルを指す明示的なファイル パスを使用します。 この方法では、以前に正常に読み込まれた行を再取り込みできなくなり、重複するデータが回避されます。

ステージング テーブルを使用して拒否された行を再処理する

拒否された行をステージング テーブルに読み込み、Transact-SQL データ変更ステートメントを使用してデータを修正してから、修正された行を再取り込みできます。

次の CREATE TABLE AS SELECT ステートメントは、拒否された行をテーブルに読み込み、さらに処理します。

CREATE TABLE TaxiTrip_RejectedRows AS
SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/row.csv'
);

データを修正したら、消去された行をコピー先テーブルに挿入します。

関連するコンテンツ

• COPY INTO を使用してデータを取り込む
• Transact-SQL を使用してデータを取り込む