0

I'm using MySQL 5.7.17 to import a ~50GB fixed width text file with over 1000 columns, but after 24 hours it still hasn't finished running. I'm using MyISAM to get around the InnoDB column limit, and also because I thought it would be faster than InnoDB for importing (source here: https://dbahire.com/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/).

Everything I'm importing is from a fixed width text file, and I'm adding all of the text file data to CHAR rows in a ~1200 column table like this:

CREATE TABLE bigFile.BigFileTest (
  `var1` CHAR(3),
  `var2` CHAR(5),
  `var3` CHAR(12),
  ...
) ENGINE=MyISAM CHARSET = latin1;

LOAD DATA LOCAL INFILE
'C:\\path\\to\\file'
INTO TABLE bigFile.BigFileTest
(@row)
SET `var1` = TRIM(SUBSTR(@row,1,3)),
  `var2` = TRIM(SUBSTR(@row,4,8)),
  `var3` = TRIM(SUBSTR(@row,9,20))
  ...
;

I'd definitely prefer to not have a ~1200 column table, of course, but it's a requirement for the data I'm working with and I'm unable to change it.

Is there any way to speed up the import process? I don't have much experience with database administration, so even basic changes you're all familiar with could be things I skipped. I'm using MySQL Workbench with the defaults for everything except I specified ENGINE=MyISAM (as shown above).

Thanks for your help!

0

MyISAM is probably as fast as InnoDB for this action.

I suggest you minimize the work during the LOAD and plan on doing TRIM, etc, after the data is loaded. That is, load the data into a staging table; then perform various conversions as you copy the data to the real table; then drop the staging table.

This 'staging' can also give you an easy way of splitting up the columns into tables. Or consolidating the columns into a single JSON string, or something else.

If you provide more details on the data and the transformations and the 1000+, I can be more specific.

More

Oh! Sounds like Punched cards. Or mag tape.. I would

  • Read the text into a single column declared as TEXT.
  • Probably I would also have another column: id INT UNSIGNED AUTO_INCREMENT NOT NULL.
  • Meanwhile, I would generate SUBSTR(t, 12, 23) clauses based on what the split up needs to be.
  • And decide how to build the schema. Are these medical records? Animal characteristics? Quiz results? What? Census data?
  • Are some of the "columns" repeated a lot? For example for census data, I might expect the first two columns to be "state" and have about 51 distinct 2-character values, then the next ~20 columns be the county, with about 3000 distinct values, etc. If so, then that definitely suggests a structure (states contain counties contain cities ...)
  • After deciding on the several tables to spread the data across, build SELECT queries to pull the data out and put them into the other tables.

Continuing with the example:

CREATE TABLE Cities (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL,
    state CHAR(2) NOT NULL,
    county VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    PRIMARY KEY(id),
    INDEX(state, city)
) ENGINE=InnoDB  CHARACTER SET ascii;

INSERT INTO Cities (state, county, city)
    SELECT DISTINCT
            SUBSTR(t, 1, 2) AS state,
            SUBSTR(t, 3, 20) AS county,
            SUBSTR(t, 23, 20) AS city
        FROM Raw;

After that, it gets trickier, because you want to use cities.id in whatever table comes next, rather than repeating the state+county+city again and again.

Get the idea? Want to share the type of data you have?

Sure it will take hours, probably even days. But the structured result will be better than a nearly useless regurgitation of the raw data.

Also, think about how you want to eventually query (SELECT) the data.

5
  • Thanks Rick. What details would be helpful for you to know? The data is in a ~50GB .txt file without any column delimiters and I'm trying to extract each field into a char column with a specific width as defined by an external specification. I know that column 1 is 5 chars, column 2 is 3 chars, etc. up to column 1200 and I'm trying to populate a blank table that I already created. I don't actually need to transform the data in any way other than splitting each newline-delimited row (from the text file) into individual fields in the database. Feb 3 '17 at 22:26
  • I added a bunch -- and sort of answered your "what details" question.
    – Rick James
    Feb 4 '17 at 4:50
  • Thanks again for your help and suggestions! I'm working with a consumer data set that's ~90% alphanumeric codes with some degree of repetition within them (e.g. 1 of 200 possible codes in each category), but no two columns repeat. There are also strings like names and addresses. I'm wary of splitting the data across tables, even though it would be much more organized and logical, because my client wants me to eventually return a single text file with all of the initial variables in their original order. If I split the data into several tables, is there an efficient way to recombine them? Feb 4 '17 at 6:03
  • Will re-JOINing be efficient -- depends on a lot of things. If the client wants the original data returned in the same format, then just save the file, don't bother copying to a database??
    – Rick James
    Feb 4 '17 at 23:55
  • That's a really good point. You're right, I don't even need a database for this if I just write some code to modify the file directly. Thanks again for all of your help. Feb 7 '17 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.