Can upload data to a file (mysql bulk uploads) read compressed files?

Question

I have a large xml file that I want to upload to mysql. Its about 20 GB uncompressed, but I think I can compress it to about 25% of its original size, and then load it into a compressed table.

I know that I can compress the data in the database itself, but can it read the compressed files during the bulk upload process?

Edit: compressed, I did not mean its .gz.tar file or anything else. I mean, when I create a file in Java or C ++, I output it as gzip, so the file itself is .csv or .xml and keeps the correct structure, but the elements in it (each line) are compressed.

If this is not possible, can I do something like bulk upload, but somehow filter it through a program that decompresses the contents? I thought to open the file in C and compress it by loading it into mysql. The problem is that I want to do this in a bulk insert, not in millions of individual inserts.

Answer 1

You must DEFINED to use LOAD DATA INFILE for this. Insertions can be an order of magnitude slower than LOAD DATA INFILE, especially in large files.

Unfortunately, MySQL does not directly support downloading compressed files (of any kind, as far as I know). However, if you look at the LOAD DATA INFILE link above, there is a trick to loading data directly from a pipe into a table (just find the page for "pipe"). I assume that you can unzip the file and output it to MySQL using this method, but compressing / decompressing will obviously be slower than downloading the uncompressed file directly.

Answer 2

$ mkfifo --mode=0666 /tmp/namedPipe $ gzip --stdout -d file.gz > /tmp/namedPipe 

Then load the uncompressed data into a MySQL table like this:

 LOAD DATA INFILE '/tmp/namedPipe' INTO TABLE tableName; 

Answer 3

Do not use `--mode = -666 'unless you want every other user on your system to be able to write to the pipe. Use '--mode = -600' and hold the handset in your local home directory to reduce the risk of unauthorized access.