Jay Bryan suggested setting the file delimiters in mysqlimport to nothing and it worked gracefully...
I am going to read into the other approach that you suggested since it seems more flexible... (mysqlimport --query=.......) I haven't done the big file yet... I did a smaller one that was 50 gigs, and it loaded fine after a few hours. (about 3) in a pc with tons of ram and scsi stripped drives. However each index take about 3-5 hours to create. For some reason it seems faster to add the indexes after importing the data. I don't understand why MySQL wants to copy the entire database over every time I add an index... It seems to me that it spends most of the time and resources coping the file over and relatively little processing time creating the actual index. Hmmm... I wonder whether there is a to streamline this process. In essence I am trying to streamline the entire database creation process. Let me paint my situation a little better. We are regularly supplied with fresh data form our providers. We basically use our MySQL server to generate list based on multiple criteria out of that data. Since the data is so big, I got to think every process very well before instructing the server to do anything since a error could cause me a few hours. Thanks a lot for you help... PS Let me know if you have any ideas on how to streamline the index creation process... Best Regards Ramon -----Original Message----- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 12:55 PM To: 'Ramon Arias'; [EMAIL PROTECTED] Subject: RE: mysqlimpor and fixed length files. [snip] The way that I have been doing it so far is by coverting the source data into tab delimited format with a 'C' utility that I wrote and then loading it using mysqlimport. However I am looking for a way to streamline that process. I am loading about 100 Gigs of data, so that extra step that I am trying to avoid requires a few hours of waiting an about 50 Gigs of extra disk space. Any suggestions? [/snip] 100 Gigs of data at a time? I cannot think of a way to sttreamline that process, it would seem that it is going to take up tons of time and tons of resources to do this...unless you have a Cray! :) By streamline, do you mean processes, time, or both? Here is an example of something that I have to monitor each day; A 2Gb file is 'pushed' to us each night (over a T1, so not a lot of time), At a set time each very early morning a CRON job runs on the server to start the parse routine, The parse routine (in this case an AWK script) checks for the existance of the file, it if exists it is parsed (as outlined in my AWK example) and imported to MySQL databases, Any temporary files are removed from the disk, The 'pushed file' is moved to another directory, A CRON checks that directory daily and TAR's up files within, The TAR is moved to another server where net-admin will do back-up (I think that the tape back-up process is more or less automagic), All files that were TAR's are removed from that directory who sits waiting for more 'pushed' files to move in. And it starts over again tonight. There is stuff written in scripts to e-mails with errors, notifications of completion, etc. Certain error reports generate a page to net-admin. It is a huge, yet streamlined process. It requires quite a bit of time each day (3-4 hours). The mysqlimport utility is fairly limited, if you could query each width delimited file something like; mysqlimport query="select substr(filename, 1, 10) as ID, substr(filename, 11, 32) as CustName into tblFOO" it would still take a long time, but it would be a single command line effort. I know of nothing like this. Jay Blanchard --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
