The method of data importing and avoiding special characters in Infobright

First, import data

IEE also supports Mysqlloader and INSERT statements

2, Default Loader

Ice only supports IB Lorder

IEE default is the MySQL loader, which can be more fault tolerant, but slightly slower. For the fastest import, use the IB loader, do the following settings for the environment

The code is as follows	Copy Code
Mysql> Set @bh_dataformat = ' txt_variable '; – Use the IB loader to import variable fixed-length text in CSV format Set @bh_dataformat = ' binary '; – Binary files Set @bh_dataformat = ' MySQL '; – Use the MySQL Loader

3,ib Loader Grammar

IB only supports load data infile, other MySQL import methods do not support

The code is as follows	Copy Code
LOAD DATA INFILE '/full_path/file_name ' Into TABLE tbl_name [FIELDS [Terminated by ' char '] [Enclosed by ' char '] [Escaped by ' char '] ]; Close before importing Set autocommit=0; Upon completion COMMIT; Set autocommit=1;

4, Area Separator

. The zone separator is optional, and the default setting is

The code is as follows	Copy Code
CLAUSE DEFAULT VALUE FIELDS terminated by '; ' (semicolon) FIELDS enclosed by ' "' (double quote) FIELDS escaped by "(none)

5, Import Experience

A. When the number of imported table columns is large, modify the Brighthouse.ini loadermainheapsize

b Use concurrent Import

C Tolerance sort for txt_variables<binary<mysql

D Bh_loader does not support multiple delimiters

E Large amount of data, DLP is necessary to select

Second, import data to avoid special character problems

The former is in the community version of the Infobright, does not support the DML function, can only use the load data to import.

If there are special control characters in the metadata, it is annoying to make an error frequently in the import process. There are two approaches to coping strategies:

1. Before setting the REJECT file import, set @BH_REJECT_FILE_PATH and @BH_ABORT_ON_COUNT can ignore the number of failed import records, and save the records in the specified file
/** when the number of rows rejected reaches, abort process www.111cn.net **/

The code is as follows	Copy Code
Set @BH_REJECT_FILE_PATH = '/tmp/reject_file '; Set @BH_ABORT_ON_COUNT = 10;

Bh_abort_on_count set to-1, means never ignore. You can also set the Bh_abort_on_threshold option, which indicates that the maximum percentage of data is allowed to be ignored, so the value of this option is a decimal format, such as Bh_abort_on_threshold = 0.03 (for 3%)

2. Specify a terminator at export in addition, you can also make a terminator when exporting data and set which escape characters (,,, and so on) are ignored, such as:
Select Fields_list ... into outfile '/tmp/outfile.csv ' Fields terminated by ' | | ' Escaped by ' lines ' terminated by ' RN ' from mytable;1. Alternatively, set the line spacer to a different special identity, for example: Select Fields_list ... into outfile '/tmp/outfile . csv ' fields terminated by ' | | Escaped by ' lines terminated by ' $ $ $rn ' from mytable; Of course, in this case, the actual data row can not exist "$ $ $RN" This value, otherwise it will be treated as a newline identity.