How to report an error when a special character occurs when importing data from infobright _ MySQL

This article mainly introduces how to report errors when importing data using infobright. Infobright is an open-source MySQL data warehouse solution, for more information, see the community version of infobright. DML is not supported and DATA can only be imported using load data.

If the metadata contains special control characters, errors are often reported during the import process, which is annoying. There are two methods to cope with the policy:

1. before setting Reject File import, set @ BH_REJECT_FILE_PATH and @ BH_ABORT_ON_COUNT to ignore the number of 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 indicates 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. Therefore, the value of this option is in decimal format, for example, BH_ABORT_ON_THRESHOLD = 0.03 (3%)

2. specify the Terminator when exporting data. In addition, you can specify the Terminator when exporting data, and specify escape characters (such as \, ", and ') to be 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. Alternatively, set the line delimiter to another special identifier, 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 line feed identifier.