Loading the data using the MySQL inforbright loader

Loading the data using the MySQL inforbright loader

For fastest LOAD results, use the Infobright Loader by setting the @bh_dataformat environment variable as described below:
To use the Infobright Loader with variable-length text in CSV format, enter:

mysql> set @bh_dataformat = ‘txt_variable’;

To use the Infobright Loader with binary data, enter the following command:

mysql> set @bh_dataformat = ‘binary’;

To return to the default MySQL Loader, set the data format to the standard MySQL format:

mysql> set @bh_dataformat = ‘mysql’;

Infobright Loader Syntax
To import your data using the high performance loader into an Infobright table, use the following load syntax (all other MySQL Loader syntax is not supported):

LOAD DATA INFILE '/full_path/file_name' 
INTO TABLE tbl_name
[FIELDS      [TERMINATED BY
'char']
[ENCLOSED BY 'char']
[ESCAPED BY 'char']   ]
; 






SET @BH_DATAFORMAT = 'txt_variable'; Use the BRIGHTHOUSE loader for the text or  csv file

SET @BH_REJECT_FILE_PATH = '/tmp/new_file_name'; All the rejected rows will go here

SET @BH_ABORT_ON_COUNT = 1000; Aborts the file load command when this number of lines in your input CSV file does not match the schema of the table you're loading into.  Use this value as -1 no limit. try -1 when you are loadinga huge file








-- creates the table in infobright using the previous table 



CREATE TABLE `tmptable` ENGINE=MySQL SELECT * FROM `maintable` WHERE 0 LIMIT 1;

 set @bh_dataformat = 'txt_variable';
 set @BH_REJECT_FILE_PATH = 'E:\\reject_file.txt';
SET @BH_ABORT_ON_COUNT = 1000;

---  run the actual load command 
LOAD DATA  INFILE 'E:\\srisat\\nny\\03_11_2015\\small_file.txt' INTO TABLE 09202014  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ;


or 

 
LOAD DATA  INFILE 'E:/srisat/nny/03_11_2015/file.txt' INTO TABLE 09202014  FIELDS TERMINATED BY '\t';