First, check whether the field separator and line break are correct, and then check whether the maximum size of a single data piece exceeds the size specified by gpfdist -m, and if so, adjust the value of gpfdist -m and restart gpfdist
b. invalid input sytax for type numeric:“”
This scenario occurs in the data file, where "\N" stands for NULL.
In order to automatically parse "\N" in the table, "NULL E'\\N'" is set to the external table, but if the value of numeric corresponding to the foreign table is an empty string, this error will appear.
How to solve it, the external table changes the numeric type to the text type, adds a conversion when reading, and converts the empty string to null and imports it into the internal table.
For example:
insert into schema.table select col1,cast (nullif(col2,'') as numeric(10,2)),col3 from schema.table_ext;
c. missing date for column “xxxx”
First, check whether the separators and line breaks are correct, and second, check whether the number of fields in the external table is the same as that in the actual data file,Finally,in the error table, view the error data and check whether any escape characters appear at the end of the text and the delimiter recognition fails
d. invalid byte sequence for encoding “UTF8”: 0xb1
Check whether the file encoding is UTF8, and the essential cause of this similar problem is that the file encoding is GB18080, which is inconsistent with the encoding of the actual external table.
To solve the problem, make sure that the created external table supports other non-UTF8 encodings, and then specify it.
There is another possibility, please refer to the number g
e. invalid byte sequence for encoding “UTF8”: 0x00
Oracle accepts strings with '\0' in the middle for storage, and automatically truncates the content when it is displayed on various interfaces.
So the fundamental solution is to truncate the '0' of the field in the program code along with the garbled characters.
For example:
str.trim().split('\u0000')[0];
f. unterminated CSV quoted field
" is the default quotation character in a CSV file. It quotes fields that contains delimiters and cannot be unbalanced。
Use the QUOTE option to specify a different quote character: like QUOTE E'\u0007'