แก้ปัญหาข้อผิดพลาดในการนําเข้า Transact-SQL ด้วยไฟล์ข้อผิดพลาด

นําไปใช้กับ: < / c0 >< c1 / >คลังสินค้าใน Microsoft Fabric

บทความนี้อธิบายวิธีการแก้ไขปัญหาความล้มเหลวในการนําเข้าในรูปแบบการนําเข้า T-SQL

การนําเข้าไปยังคลังสินค้าโดยใช้ COPY INTO, BULK INSERT, OPENROWSET ฟังก์ชันใน CTAS, INSERT, UPDATEและ MERGE คําสั่งอาจล้มเหลวได้ด้วยเหตุผลหลายประการ ค่าไฟล์ต้นฉบับอาจไม่ตรงกับ Schema ตาราง ค่าที่ต้องการอาจขาดหายไป ตัวเลือกการนําเข้าอาจถูกกําหนดค่าไม่ถูกต้อง

คู่มือการแก้ไขปัญหานี้ใช้ข้อมูลการวินิจฉัยแถวที่ถูกปฏิเสธเพื่อแก้ไขความล้มเหลว บันทึกข้อผิดพลาดระดับแถว และตรวจสอบแถวที่ถูกปฏิเสธด้วยข้อมูลเมตาของข้อผิดพลาด

เมื่อตรวจสอบไฟล์ข้อผิดพลาดที่สร้างขึ้นโดย 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' );

คําสั่งนี้อาจล้มเหลวในการนําเข้าข้อมูลหากไฟล์ต้นฉบับไม่ตรงกับ Schema ตารางปลายทาง สาเหตุที่พบบ่อย ได้แก่ จํานวนคอลัมน์ที่ไม่ตรงกัน ชนิดข้อมูลที่เข้ากันไม่ได้ หรือค่าที่ไม่สามารถจัดเก็บไว้ในตารางเป้าหมายได้ ถ้าการนําเข้าพบค่าที่ไม่สามารถแปลงเป็น Schema ปลายทางได้ คําสั่งจะส่งกลับข้อผิดพลาดที่คล้ายกับต่อไปนี้:

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'.

ข้อผิดพลาดนี้บ่งชี้ว่าไม่สามารถแปลงแถวอย่างน้อยหนึ่งแถวเป็นชนิดคอลัมน์ปลายทางได้

ตรวจสอบข้อผิดพลาดด้วย 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 สําหรับแถวต้นทางที่ถูกปฏิเสธ

ค้นหาและสืบค้นแถวที่ถูกปฏิเสธ

ฐานข้อมูลจะเขียนข้อมูลข้อผิดพลาดไปยังลําดับชั้นของโฟลเดอร์ที่มีโครงสร้างภายใต้ตําแหน่งที่ตั้งของข้อผิดพลาดที่กําหนดค่าไว้ โฟลเดอร์เหล่านี้ช่วยให้คุณติดตามการดําเนินการที่เฉพาะเจาะจงและเชื่อมโยงการวินิจฉัยกับคําสั่งการนําเข้าหนึ่งคําสั่ง:

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'
);

โดยทั่วไปชุดผลลัพธ์จะประกอบด้วยหนึ่งแถวต่อเรกคอร์ดที่ถูกปฏิเสธ ตัวอย่างเช่น:

error.jsonlไฟล์มีออบเจ็กต์ JSON หนึ่งรายการต่อบรรทัด แต่ละวัตถุมีคุณสมบัติที่แสดงในตารางก่อนหน้า ตารางต่อไปนี้อธิบายรายละเอียดของคุณสมบัติแต่ละรายการ

ตรวจทานแถวที่ถูกปฏิเสธ

หลังจากตรวจทานข้อมูลการวินิจฉัยที่มีโครงสร้างแล้ว คุณจะตรวจสอบข้อมูลต้นฉบับที่ฐานข้อมูลนําเข้าไม่ได้ เอาต์พุตแถวที่ถูกปฏิเสธประกอบด้วยสําเนาของเร็กคอร์ดต้นทาง ซึ่งเก็บรักษาไว้ตรงตามที่ปรากฏในไฟล์อินพุต การวินิจฉัยแถวที่ถูกปฏิเสธจะสร้างไฟล์ที่มีเฉพาะเรกคอร์ดที่นําเข้าล้มเหลว:

• หากคุณนําเข้าไฟล์ CSV โดยใช้ COPY INTO (FILE_TYPE = 'CSV')เอาต์พุตที่ถูกปฏิเสธจะรวม row.csv ไฟล์ ไฟล์นี้ตรงกับโครงสร้างไฟล์ต้นฉบับและมีแถว CSV ต้นฉบับที่มีค่าไม่ถูกต้อง
• หากคุณนําเข้าไฟล์ JSONL โดยใช้ OPENROWSET(FORMAT = 'JSONL')เอาต์พุตที่ถูกปฏิเสธจะมี row.jsonl ไฟล์ ไฟล์นี้รักษาออบเจ็กต์ JSON ดั้งเดิมที่ทําให้เกิดความล้มเหลวในการนําเข้า

ใช้แฟ้มเหล่านี้เพื่อตรวจสอบสาเหตุของข้อผิดพลาด เช่น ค่าที่มีรูปแบบไม่ถูกต้อง ค่าที่ไม่คาดคิด NULL หรือแถวส่วนหัวที่ถูกแยกวิเคราะห์เป็นข้อมูลอย่างไม่ถูกต้อง

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

row.csvสคีมาตรงกับรูปร่าง CSV ต้นทาง และมีเฉพาะแถวที่นําเข้าไม่สําเร็จ

ตัวอย่างเอาต์พุตแถวที่ถูกปฏิเสธ:

จากข้อมูลการวินิจฉัยนี้ คุณจะระบุปัญหาการนําเข้าต่อไปนี้ได้

• แถวส่วนหัวในไฟล์ต้นฉบับถูกแยกวิเคราะห์อย่างผิดพลาดเป็นแถวข้อมูล เมื่อต้องการแก้ไข COPY INTO ใบแจ้งยอดควรใช้ตัวเลือกFIRSTROW = 2
• แถวในแฟ้มต้นฉบับสําหรับvendorIDคอลัมน์ (C1) มีค่า NULL แต่คอลัมน์ที่สอดคล้องกันในตารางปลายทางTaxiTripsถูกกําหนดเป็นNOT NULL
• แถวในแฟ้มต้นฉบับของ passengerCount คอลัมน์มีค่า (N/A) ที่ไม่ถูกต้องซึ่งไม่สามารถแปลงเป็นคอลัมน์ int ปลายทางได้

แก้ไขปัญหาการนําเข้าและนําเข้าข้อมูลอีกครั้ง

หลังจากระบุสาเหตุของการนําเข้าข้อมูลล้มเหลวแล้ว ให้แก้ไขปัญหาและนําเข้าข้อมูลที่ได้รับผลกระทบอีกครั้ง แนวทางการแก้ไขขึ้นอยู่กับที่มาของข้อผิดพลาด

แก้ไข Schema ตารางปลายทาง

ถ้าข้อมูลต้นฉบับไม่สอดคล้องกับ Schema ตารางปลายทาง ให้อัปเดตข้อกําหนดของตาราง การแก้ไขทั่วไป ได้แก่ การเปลี่ยนชนิดข้อมูลคอลัมน์หรือการลบข้อจํากัดที่จํากัด เช่น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