Memecahkan masalah kesalahan pemasukan data Transact-SQL menggunakan file kesalahan

Diterapkan pada:✅ Gudang di Microsoft Fabric

Artikel ini menjelaskan cara memecahkan masalah kegagalan penyerapan dalam pola penyerapan T-SQL.

Penyerapan ke dalam gudang menggunakan fungsi COPY INTO, BULK INSERT, OPENROWSET dalam pernyataan CTAS, INSERT, UPDATE, dan MERGE dapat gagal karena beberapa alasan. Nilai file sumber mungkin tidak cocok dengan skema tabel. Nilai yang diperlukan mungkin hilang. Opsi penyerapan mungkin juga salah dikonfigurasi.

Panduan pemecahan masalah ini menggunakan informasi diagnostik baris yang ditolak untuk mengatasi kegagalan, menangkap kesalahan tingkat baris, dan memeriksa baris yang ditolak dengan metadata kesalahan.

Dengan memeriksa file kesalahan yang dihasilkan oleh COPY INTO dan perintah penyerapan lainnya, Anda dapat menentukan dengan tepat baris mana yang gagal diserap dan mengapa. Informasi ini membantu Anda mengidentifikasi masalah kualitas data atau menyesuaikan pengaturan penyerapan, memperbaiki data sumber, dan menjalankan ulang beban dengan percaya diri.

Important

Instruksi ini hanya berlaku untuk menyerap file CSV atau JSONL dengan menggunakan perintah Transact-SQL (COPY INTO, BULK INSERT, dan DML dengan OPENROWSET fungsi). File keluaran untuk baris yang ditolak tidak dibuat untuk alat pemasukan eksternal (seperti alur), file Parquet, atau saat memasukkan data dari titik akhir analitik SQL.

Membuat tabel target

Sebelum menjalankan perintah penyerapan, buat tabel tujuan dengan jenis dan NOT NULL batasan yang ketat sehingga Anda menangkap masalah konversi dan kualitas data lebih awal.

  1. Di ruang kerja Gudang Anda, buka gudang Anda.

  2. Pada tab Beranda , pilih Kueri SQL baru.

    Cuplikan layar bagian atas ruang kerja pengguna memperlihatkan tombol Kueri SQL Baru.

  3. Jalankan pernyataan berikut:

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

Anda dapat menggunakan beberapa metode yang didukung, termasuk menyerap dengan COPY INTO atau menyerap dengan Transact-SQL. Pilih metode penyerapan yang paling sesuai dengan sumber data, format, dan persyaratan otomatisasi Anda. Contoh COPY INTO berikut mengilustrasikan pola penyerapan umum untuk memuat data dari file eksternal ke dalam tabel.

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

Pernyataan ini dapat gagal menyerap data jika file sumber tidak cocok dengan skema tabel tujuan. Penyebab umum termasuk jumlah kolom yang tidak cocok, jenis data yang tidak kompatibel, atau nilai yang tidak dapat disimpan dalam tabel target. Jika penyerapan menemukan nilai yang tidak dapat dikonversi ke skema tujuan, pernyataan mengembalikan kesalahan yang mirip dengan yang berikut ini:

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

Kesalahan ini menunjukkan bahwa satu atau beberapa baris tidak dapat dikonversi ke jenis kolom tujuan.

Menyelidiki kesalahan dengan MAXERRORS dan ERRORFILE

Gunakan opsi berikut untuk melanjutkan penyerapan saat jumlah kesalahan tingkat baris di bawah ambang yang ditentukan dan untuk menyimpan detail diagnostik di lokasi tertentu.

  • MAXERRORS mengatur jumlah maksimum kegagalan tingkat baris yang ditoleransi selama penyerapan.
  • ERRORFILE menentukan tempat database menulis baris yang ditolak dan detail kesalahan.
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/'
);

Important

Konfigurasikan ERRORFILE di bawah lokasi penyimpanan yang sama yang digunakan untuk pembacaan file sumber, bukan di akun penyimpanan yang berbeda. Identitas yang digunakan untuk mengakses data sumber juga harus memiliki izin untuk membuat folder dan file di jalur kesalahan yang dikonfigurasi.

Operasi pemuatan hanya berhasil ketika jumlah baris yang ditolak lebih rendah dari MAXERRORS. Ketika kesalahan ditangkap, operasi pengambilan data mencatat:

  • error.jsonl untuk diagnostik terstruktur
  • row.csv untuk baris sumber yang ditolak

Menemukan dan menjalankan kueri pada baris yang ditolak

Database menulis informasi kesalahan ke hierarki folder terstruktur di bawah lokasi kesalahan yang dikonfigurasi. Folder ini membantu Anda melacak eksekusi tertentu dan menghubungkan diagnostik dengan satu pernyataan penyerapan:

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

Gunakan OPENROWSET untuk membaca diagnostik error.jsonl terstruktur sehingga Anda dapat mengidentifikasi nilai mana yang gagal, kolom tujuan mana yang terpengaruh, dan tempat baris yang gagal berasal:

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

Kumpulan hasil biasanya menyertakan satu baris per rekaman yang ditolak, misalnya:

Kesalahan kolom ColumnName Nilai IsOutputted File ErrorRowLocation
Kesalahan konversi data 1 vendorID vendorID 1 https://.../yellow/tripdata.csv 0
NULL dalam kolom yang tidak dapat diubah ke null 1 vendorID NOL 1 https://.../yellow/ytripdata.csv 399
Kesalahan konversi data 6 passengerCount N/A 1 https://.../yellow/yellow_tripdata.csv 519

File error.jsonl berisi satu objek JSON per baris. Setiap objek menyertakan properti yang tercantum dalam tabel sebelumnya. Tabel berikut ini menjelaskan setiap properti secara rinci.

kolom Description
Error Menyediakan pesan kesalahan yang menjelaskan mengapa nilai ditolak selama penyerapan.
Column Menentukan indeks kolom dalam file CSV sumber yang berisi nilai yang tidak dapat diserap. Pengindeksan kolom dimulai pada 1 untuk kolom pertama.
ColumnName Menentukan nama kolom tabel tujuan tempat nilai tidak dapat disimpan.
Value Nilai sumber yang tidak dapat dikonversi atau divalidasi.
IsOutputted Menunjukkan apakah baris dari file sumber yang berisi kesalahan yang dilaporkan juga ditulis ke file output baris yang ditolak (row.csv atau row.jsonl). Nilai 1 (atau true dalam file JSONL) berarti baris ditulis dalam error.csv, dan nilai 0 (atau false dalam file JSONL) berarti tidak.
File Mengidentifikasi file sumber asal baris yang ditolak. Nilai ini membantu Anda melacak data yang ditolak kembali ke file input asli untuk penyelidikan.
ErrorRowLocation Posisi offset byte dalam file sumber tempat kegagalan terjadi.

Meninjau baris yang ditolak

Setelah meninjau informasi diagnostik terstruktur, Anda dapat memeriksa data sumber asli yang tidak dapat diserap database. Output baris yang ditolak berisi salinan dari catatan sumber, yang dipertahankan persis seperti tampilannya dalam file input. Diagnostik baris yang ditolak menghasilkan file yang hanya berisi rekaman yang gagal diserap:

  • Jika Anda menyerap file CSV dengan menggunakan COPY INTO (FILE_TYPE = 'CSV'), output yang ditolak menyertakan row.csv file. File ini cocok dengan struktur file sumber dan berisi baris CSV asli dengan nilai yang tidak valid.
  • Jika Anda menyerap file JSONL dengan menggunakan OPENROWSET(FORMAT = 'JSONL'), output yang ditolak menyertakan row.jsonl file. File ini mempertahankan objek JSON asli yang menyebabkan kegagalan penyerapan.

Gunakan file-file ini untuk memvalidasi akar penyebab kesalahan, seperti nilai cacat, nilai tak terduga NULL , atau baris header yang salah diurai sebagai data.

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

row.csv Skema cocok dengan bentuk CSV sumber, dan hanya berisi baris yang gagal diserap.

Contoh output dari baris yang ditolak:

C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
vendorID startLat startLon endLat endLon passengerCount tripDistance fareAmount mtaTax totalAmount
NOL 40.7484 -73.9857 40.7549 -73.9840 2 1.40 9.00 0.50 13.20
1 40.7216 -74.0047 40.7359 -74.0036 N/A 1.80 11.00 0.50 15.90

Berdasarkan informasi diagnostik ini, Anda dapat mengidentifikasi masalah penyerapan berikut:

  • Baris header dalam file sumber salah diurai sebagai baris data. Untuk mengatasinya, pernyataan harus menggunakan opsi COPY INTOFIRSTROW = 2.
  • Baris dalam file sumber untuk vendorID kolom (C1) berisi NULL nilai, tetapi kolom terkait dalam tabel tujuan TaxiTrips didefinisikan sebagai NOT NULL.
  • Baris dalam file sumber untuk passengerCount kolom berisi nilai yang tidak valid (N/A) yang tidak dapat dikonversi ke kolom int tujuan.

Note

Proses yang sama berlaku saat Anda memeriksa baris yang ditolak dari input JSONL. Gunakan file row.jsonl untuk memeriksa rekaman yang ditolak.

Memperbaiki masalah penyerapan dan menyerap ulang data

Setelah Anda mengidentifikasi penyebab kegagalan penyerapan, perbaiki masalah dan serap ulang data yang terpengaruh. Pendekatan remediasi tergantung pada asal kesalahan.

Memperbaiki skema tabel tujuan

Jika data sumber tidak sesuai dengan skema tabel tujuan, perbarui definisi tabel. Perbaikan umum termasuk mengubah jenis data kolom atau menghapus batasan pembatasan seperti NOT NULL.

Dalam beberapa skenario, Anda mungkin perlu menghilangkan dan membuat ulang tabel tujuan sebelum menyerap ulang data.

Mengoreksi data sumber dan menyerap ulang file

Jika penyerapan gagal karena nilai yang tidak valid atau tidak konsisten dalam file sumber, perbaik nilai tersebut dan serap ulang data. Misalnya, ganti nilai tempat penampung seperti N/A dengan nilai kosong atau default yang valid.

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

Saat menyerap ulang data yang dikoreksi, gunakan jalur file eksplisit yang menunjuk ke file baru yang hanya berisi data yang dikoreksi, bukan jalur folder yang mereferensikan file asli. Pendekatan ini mencegah penyerapan ulang baris yang sebelumnya berhasil dimuat dan menghindari data duplikat.

Memproses ulang baris yang ditolak dengan menggunakan tabel penahapan

Anda dapat memuat baris yang ditolak ke dalam tabel penahapan, memperbaiki data dengan menggunakan pernyataan modifikasi data Transact-SQL, lalu menyerap ulang baris yang dikoreksi.

Pernyataan berikut CREATE TABLE AS SELECT memuat baris yang ditolak ke dalam tabel untuk diproses lebih lanjut:

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

Setelah Anda memperbaiki data, sisipkan baris yang dibersihkan ke dalam tabel tujuan.

Konten terkait

  • Last updated on