• notice
  • Congratulations on the launch of the Sought Tech site

Infobright imports data encounters special characters error analysis

Currently, I am using the community version of infobright, which does not support the DML function and can only import data using the LOAD DATA method.

If there are special control characters in the metadata, errors will often be reported during the import process, which is very annoying.There are two ways to deal with strategies:

1.Before setting the Reject File to import, set @BH_REJECT_FILE_PATH and @BH_ABORT_ON_COUNT to ignore how many failed import records, and save these records in the specified file

The code is as follows:
/** when the number of rows rejected reaches 10, abort process **/

set @BH_REJECT_FILE_PATH='/tmp/reject_file';

set @BH_ABORT_ON_COUNT=10;

If BH_ABORT_ON_COUNT is set to-1, it means that it will never be ignored.

You can also set the BH_ABORT_ON_THRESHOLD option, which indicates the maximum percentage of data allowed to be ignored, so the value of this option is in decimal format, for example, BH_ABORT_ON_THRESHOLD=0.03 (indicating 3%)

2.Specify the terminator when exporting.In addition, you can also specify the terminator when exporting data, and set which escape characters (\, ",', etc.) are ignored, for example:

The code is as follows:
select fields_list...into outfile'/tmp/outfile.csv' fields terminated by '||' ESCAPED BY'\\' lines terminated by'\r\n' from mytable;

3.Or, set the line separator to other special identifiers, for example: select fields_list… into outfile'/tmp/outfile.csv' fields terminated by'||' ESCAPED BY'\\' lines terminated by ' $$$$$\r\n' from mytable; Of course, in this case, the value "$$$$$\r\n" cannot exist in the actual data row, otherwise it will be regarded as a newline indicator.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+