Vianetsintä Transact-SQL syöttövirheitä virhetiedostoissa

Sovellettavissa:✅ Varasto Microsoft Fabric

Tässä artikkelissa kuvataan, miten T-SQL:n syöttökuvioiden vastaanottovirheitä voidaan ratkaista.

Varastoon siirtyminen , COPY INTO, funktiolla , UPDATEINSERTCTASja MERGE lauseilla voi epäonnistua useista syistä. OPENROWSETBULK INSERT Lähdetiedoston arvot eivät välttämättä vastaa taulukon skeemaa. Vaaditut arvot saattavat puuttua. Myös nielemisvaihtoehdot voivat olla väärin konfiguroituja.

Tämä vianetsintäopas käyttää hylättyjen rivien diagnostiikkatietoja virheiden korjaamiseen, rivitason virheiden keräämiseen ja hylättyjen rivien tarkistamiseen virhemetatietojen kanssa.

Tarkastelemalla virhetiedostoja, jotka on tuottanut COPY INTO ja muita syöttökomentoja, voit tarkasti määrittää, mitkä rivit epäonnistuivat vastaanottamaan ja miksi. Tämä tieto auttaa tunnistamaan datan laatuongelmia tai säätämään syöttöasetuksia, korjaamaan lähdedatan ja suorittamaan latauksen uudelleen luottavaisin mielin.

Luo kohdetaulukko

Ennen kuin suoritat syöttökomentoja, luo kohdetaulu tiukoilla tyypeillä ja NOT NULL rajoituksilla, jotta tunnistat muunnos- ja tiedonlaatuongelmat ajoissa.

1. Avaa varastosi työtilassa.

2. Koti-välilehdeltä valitse Uusi SQL-kysely.

   

3. Suorita seuraava lausunto:

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

Voit käyttää useita tuettuja menetelmiä, kuten COPY INTO -menetelmällä taiTransact-SQL:n sisäänpääsyä. Valitse vastaanottomenetelmä, joka parhaiten sopii tietolähteeseesi, muotoosi ja automaatiovaatimuksiisi. Seuraava COPY INTO -esimerkki havainnollistaa yleistä vastaanottomallia, jossa datan lataaminen ulkoisista tiedostoista tauluun.

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

Tämä lause voi epäonnistua datan vastaanottamisessa, jos lähdetiedostot eivät vastaa kohdetauluskeemaa. Yleisiä syitä ovat sarakkeiden epäsopivuudet, yhteensopimattomat tietotyypit tai arvot, joita ei voi tallentaa kohdetaulukkoon. Jos vastaanotto kohtaa arvoja, joita ei voi muuntaa kohdeskeemaan, lause palauttaa virheen, joka muistuttaa seuraavaa:

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

Tämä virhe tarkoittaa, että yhtä tai useampaa riviä ei voi muuntaa kohdesaraketyypeiksi.

Tutki virheitä MAXERRORS- ja ERRORFILE-tiedostoissa

Käytä seuraavia vaihtoehtoja jatkaaksesi vastaanottoa, kun rivitason virheiden määrä on alle määritellyn kynnyksen ja tallentaaksesi diagnostiikkatiedot tiettyyn paikkaan.

• MAXERRORS asettaa suurimman siedetyn rivin tason vikojen määrän vastaanoton aikana.
• ERRORFILE Määrittelee, mihin tietokanta kirjoittaa hylätyt rivit ja virhetiedot.

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

Latausoperaatio onnistuu vain, kun hylättyjen rivien määrä on pienempi kuin MAXERRORS. Kun virheitä havaitaan, vastaanottotoiminto kirjoittaa:

• error.jsonl Rakenteelliseen diagnostiikkaan
• row.csv hylätyille lähderiveille

Etsi ja hae hylättyjä rivejä

Tietokanta kirjoittaa virhetiedot rakenteelliseen kansiohierarkiaan konfiguroidun virhesijainnin alle. Nämä kansiot auttavat jäljittämään tietyn suorituksen ja yhdistämään diagnostiikan yhteen vastaanottolauseeseen:

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

Käytä OPENROWSET rakenteellisen diagnostiikan lukemiseen error.jsonl , jotta voit tunnistaa, mikä arvo epäonnistui, mikä kohdesarake vaurioitui ja mistä epäonnistunut rivi on lähtöisin:

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

Tulosjoukko sisältää tyypillisesti yhden rivin per hylätty tietue, esimerkiksi:

Tiedosto error.jsonl sisältää yhden JSON-objektin per rivi. Jokainen olio sisältää edellisessä taulukossa mainitut ominaisuudet. Seuraava taulukko kuvaa kunkin ominaisuuden yksityiskohtaisesti.

Hylätyt rivit

Kun olet tarkastellut rakenteellista diagnostiikkatietoa, voit tarkastella alkuperäistä lähdedataa, jota tietokanta ei pystynyt vastaanottamaan. Hylätyn rivin tulos sisältää lähdetietueiden kopioita, jotka on säilytetty täsmälleen sellaisina kuin ne ilmestyivät syötetiedostoissa. Hylätyt rividiagnostiikka tuottaa tiedostoja, jotka sisältävät vain ne tietueet, joiden vastaanotto epäonnistui:

• Jos vastaanotat CSV-tiedostoja käyttämällä COPY INTO (FILE_TYPE = 'CSV'), hylätty tulos sisältää tiedoston row.csv . Tämä tiedosto vastaa lähdetiedostorakennetta ja sisältää alkuperäiset CSV-rivit virheellisillä arvoilla.
• Jos vastaanotat JSONL-tiedostoja käyttämällä OPENROWSET(FORMAT = 'JSONL'), hylätty tulos sisältää tiedoston row.jsonl . Tämä tiedosto säilyttää alkuperäiset JSON-objektit, jotka aiheuttivat syöttövirheitä.

Käytä näitä tiedostoja virheiden juurisyyn varmistamiseen, kuten väärismuotoiset arvot, odottamattomat NULL arvot tai virheellisesti jäsennetyt otsikkorivit.

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

Skeema row.csv vastaa lähde-CSV-muotoa, ja se sisältää vain rivejä, joiden vastaanotto epäonnistui.

Esimerkki hylätyn rivin tulosteesta:

Näiden diagnostisten tietojen perusteella voit tunnistaa seuraavat nielemisongelmat:

• Lähdetiedoston otsikkorivi on virheellisesti jäsennelty datariviksi. Tämän ratkaisemiseksi lauseessa COPY INTO tulisi käyttää vaihtoehtoa FIRSTROW = 2 .
• Lähdetiedoston rivi sarakkeelle vendorID (C1) sisältää NULL arvoja, mutta vastaava TaxiTrips sarake kohdetaulukossa määritellään muodossa NOT NULL.
• Sarakkeen lähdetiedoston passengerCount rivi sisältää virheellisen arvon (N/A), jota ei voi muuntaa kohde-int-sarakkeeksi .

Korjaa syöttöongelmat ja uudelleenvastaanotto data

Kun olet tunnistanut nielemisvirheiden syyn, korjaa ongelma ja vastaanota kyseiset tiedot uudelleen. Korjausmenetelmä riippuu siitä, mistä virhe on peräisin.

Korjaa kohdetauluskeema

Jos lähdedata ei vastaa kohdetauluskeemaa, päivitä taulun määritelmä. Yleisiä korjauksia ovat saraketietotyyppien muuttaminen tai rajoittavien rajoitteiden, kuten NOT NULL.

Joissain tapauksissa saatat joutua pudottamaan ja luomaan kohdetaulun uudelleen ennen datan uudelleenkäsittelyä.

Oikea lähdedata ja tiedostojen uudelleenvastaanotto

Jos syöttö epäonnistuu virheellisten tai epäjohdonmukaisten arvojen vuoksi lähdetiedostoissa, korjaa nämä arvot ja vastaanota data uudelleen. Esimerkiksi korvaa paikkamerkkiarvot, kuten N/A tyhjillä arvoilla tai kelvollisilla oletusarvoilla.

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

Kun vastaanotat korjattua dataa uudelleen, käytä eksplisiittistä tiedostopolkua, joka osoittaa uuteen tiedostoon, jossa on vain korjattuja tietoja, eikä kansiopolkuun, joka viittaa alkuperäisiin tiedostoihin. Tämä lähestymistapa estää aiemmin onnistuneesti ladattujen rivien uudelleenvastaanoton ja välttää päällekkäiset tiedot.

Hylätyt rivit käsitellään uudelleen käyttämällä vaiheitustaulua

Voit ladata hylätyt rivit staging-taulukkoon, korjata tiedot käyttämällä Transact-SQL datan muokkauslauseita ja sitten vastaanottaa korjatut rivit uudelleen.

Seuraava CREATE TABLE AS SELECT lauseke lataa hylätyt rivit taulukkoon jatkokäsittelyä varten:

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

Kun olet korjannut tiedot, lisää puhdistetut rivit kohdetauluun.

Liittyvä sisältö

• Tietojen vastaanottaminen COPY INTO -menetelmällä
• Tietojen käyttö Transact-SQL:n avulla