Import data in Infobright to avoid special character problems

Currently in use is the community version of the Infobright, does not support the DML function, can only be used in load data import.

If the metadata has special control characters, the import process often error, it is annoying. There are two approaches to coping with the strategy:

2. Before setting the REJECT file import, set @BH_REJECT_FILE_PATH and @BH_ABORT_ON_COUNT to ignore the number of failed import records and save those records in the specified file

   /** when the number of rows rejected reaches, abort process **/
   
   Set @BH_REJECT_FILE_PATH = '/tmp/reject_file ';
   
   Set @BH_ABORT_ON_COUNT = 10;
   
   Bh_abort_on_count is set to 1, which means never ignore.
   

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

4. Specify Terminator on Export in addition, you can also set a terminator when exporting data, and setting which escape characters are ignored (\, "," and so on), for example:

Select Fields_list to outfile '/tmp/outfile.csv ' fields terminated by ' | | ' escaped by ' \ \ ' lines terminated by ' \ r \ n ' from mytable;

1. Alternatively, set the line spacer to another special identity, 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 of "$$$$$\r\n" cannot exist in the actual data row, otherwise it will be treated as a newline identifier.

--------------------------------------Split Line--------------------------------------

Hall of Understanding ( http://zhishuedu.com Training is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, which mainly includes MySQL DBA combat optimization and Python Operation Development Course, which is the most conscientious and quality training course in the industry.

This article comes from " Lao ye teahouse "blog, be sure to keep this source http://imysql.blog.51cto.com/1540006/1879883

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1880060

Import data in infobright to avoid special character problems