Handling Import Errors

Scenarios

Handle errors that occurred during data import.

Querying Error Information

Errors that occur when data is imported are divided into data format errors and non-data format errors.

  • Data format error

    When creating a foreign table, specify LOG INTO error_table_name. Data format errors occurring during the data import will be written into the specified table. You can run the following SQL statement to query error details:

    SELECT * FROM error_table_name;

    Table 1 lists the columns of the error_table_name table.

    Table 1 Columns in the error_table_name table

    Column

    Type

    Description

    nodeid

    integer

    ID of the node where an error is reported

    begintime

    timestamp with time zone

    Time when a data format error is reported

    filename

    character varying

    Name of the source data file where a data format error occurs

    If you use GDS for importing data, the error information includes the IP address and port number of the GDS server.

    rownum

    bigint

    Number of the row where an error occurs in a source data file

    rawrecord

    text

    Raw record of the data format error in the source data file

    detail

    text

    Error details

  • Non-data format error

    A non-data format error leads to the failure of an entire data import task. You can locate and troubleshoot a non-data format error based on the error message displayed during data import.

Handling data import errors

Troubleshoot data import errors based on obtained error information and the description in the following table.

Table 2 Handling data import errors

Error Information

Cause

Solution

missing data for column "r_reason_desc"

  1. The number of columns in the source data file is less than that in the foreign table.

  2. In a TEXT format source data file, an escape character (for example, \) leads to delimiter or quote mislocation.

    Example: The target table contains three columns as shown in the following command output. The escape character (\) converts the delimiter (|) into the value of the second column, causing loss of the value of the third column.

    BE|Belgium\|1

  1. If an error is reported due to missing columns, perform the following operations:

    • Add the r_reason_desc column to the source data file.

    • When creating a foreign table, set the parameter fill_missing_fields to on. In this way, if the last column of a row in the source data file is missing, it is set to NULL and no error will be reported.

  2. Check whether the row where an error occurred contains the escape character (\). If the row contains such a character, you are advised to set the parameter noescaping to true when creating a foreign table, indicating that the escape character (\) and the characters following it are not escaped.

extra data after last expected column

The number of columns in the source data file is greater than that in the foreign table.

  • Delete the unnecessary columns from the source data file.

  • When creating a foreign table, set the parameter ignore_extra_data to on. In this way, if the number of columns in a source data file is greater than that in the foreign table, the extra columns at the end of rows will not be imported.

invalid input syntax for type numeric: "a"

The data type is incorrect.

In the source data file, change the data type of the columns to be imported. If this error information is displayed, change the data type to numeric.

null value in column "staff_id" violates not-null constraint

The not-null constraint is violated.

In the source data file, add values to the specified columns. If this error information is displayed, add values to the staff_id column.

duplicate key value violates unique constraint "reg_id_pk"

The unique constraint is violated.

  • Delete the duplicate rows from the source data file.

  • Run the SELECT statement with the DISTINCT keyword to ensure that all imported rows are unique.

    INSERT INTO reasons SELECT DISTINCT * FROM foreign_tpcds_reasons;

value too long for type character varying(16)

The column length exceeds the upper limit.

In the source data file, change the column length. If this error information is displayed, reduce the column length to no greater than 16 bytes (VARCHAR2).

last updated: 2024-11-13 15:46 UTC - commit: bdcb01d62e4f1de672b662d88ef6cb8eb3a27a29