Data swap in a large database

Question

I have 2.8 billion records in a table. The table uses INFOBRIGHT engine run on mysql installation. I have a few incorrect entries in the table and would like to get them corrected.

Table Test has 350 odd columns. I had like to swap data from column P1 to column P3 for a few records (not all). The approach that I had planned to carry to migrate data is as follows

Extract data from table Test to a CSV file using the INTO OUTFILE functionality of mysql
Delete the unwanted records from the table.
Import the CSV data using the LOAD DATA INFILE and use the SET clause to move the data from P1 to P3 (empty string for P1 and SET P1=P3)

The approach seemed to make sense, until I realized that INFOBRIGHT does not support the SET clause as mentioned here

Excerpts from the link below

The SET construct is supported by the MySQL loader found in the standard MySQL download but not by the Infobright loader included in ICE. I was able to actually execute a load using the SET statement; what’s interesting is that it will run but the SET gets ignored by Infobright.

Question

Is there a easier way to do this?
Of course, I can edit the CSV file. But for 2.8 billion records, I would like to have a sure shot way of doing it. Any tested scripts appreciated.
I would not want to use the mysql loader and load data in to MYISAM table, because of the sheer size of data involved. Any faster approaches there?

Why not make a temporary table, and run UPDATE query using that temp table and your llive table to swap data? Or try using swap in place? — Muhammed, Mar 19 '16 at 16:46
tell us if yor infobright version supports update/insert/delete or not? — Saic Siquot, Mar 19 '16 at 16:47
Update will be much slower hence ruled out. Idea is to have minimum outage — TheMonkWhoSoldHisCode, Mar 19 '16 at 16:50

score 1 · Answer 1 · answered Apr 08 '16 at 15:06

1

Infobright don't allow SET while importing from file but you can choose mysql loader for loading the file. By default infobright use it's own loader but you can choose mysql loader to load file and then you can use SET.

You can set mysql loader using: set @bh_dataformat = 'mysql';

I don't know how much mysql loader will be slower than IB loader. I have loaded ~60GB file having ~60 columns in 1.5 hours.

answered Apr 08 '16 at 15:06

jmrp

65
3
8

Infobrighrt loader is twice as fast as mysql. IEE defaults with mysql loader per documentation. But after tests it seems it defaults to Infobright loader. I had used similar approach that you mentioned. There is another way to load data using Infobright IEE. There is a special data processor called DLP processor which preprocessors data. So loading data is way too faster. – TheMonkWhoSoldHisCode Apr 08 '16 at 15:18

Data swap in a large database

1 Answers1