ScenarioCurrently we have a few servers that are storing historical data that will eventually be migrated into Oracle. Two things need to happen until we can finally decommission them: 1) export the data to CSV so it can be imported in bulk into Oracle and 2) keep the data online so it can be queried as needed until the migration is finalized. I thought it would be interesting if we could solve both issues simultaneously and decided to try the CSV engine. Here's a description of the process.
Understanding CSV EngineThe CSV engine, as far as I can tell, was and example storage engine that was included with earlier MySQL versions to illustrate the storage engine API. Since the data is stored in plain text files there are some limitations that need to be considered before using it:
- No support for indexes
- No NULL columns are allowed
Exporting Data To CSVSo, my first step was to determine what would it take to export data from a regular table to CSV. These are the basic steps:
1. Create the seed for the CSV table based on an existing table
CREATE TABLE test LIKE test_csv;
If you feel adventurous, use CREATE TABLE LIKE ... SELECT ... in which case you may be able to skip the next 2 steps. The engine for the new table will be redefined at the end of the process.
2. Get rid of the indexes
ALTER TABLE test_csv DROP PRIMARY KEY, DROP KEY test_idx;
Modify this statement to include all existing indexes.
3. Get rid of NULL columns
ALTER TABLE test_csv MODIFY test_char VARCHAR(10) NOT NULL DEFAULT '', MODIFY test_date TIMESTAMP NOT NULL DEFAULT '0000-00-00';
The DEFAULT values need to be reviewed so the application makes no mistake that these should be NULL. Numeric values could be tricky since you may not find a suitable replacement for NULL. Adapt to your particular case.
4. Convert to CSV
ALTER TABLE test_csv ENGINE CSV;
This step will create an empty CSV file in the schema data directory.
5. Export the data
INSERT INTO test_csv SELECT * FROM test WHERE ...
This would allow you to export the portion of the data from an existing table into the CSV table/file.
At this point your data is all stored in a CSV file called test_csv.CSV under the data subdirectory that corresponds to the schema and the table can be queried as any other regular MySQL table, which is what I was looking for at the beginning of the project.
You could even update the table. If you need to load this file to any other application, just copy the file.
Keep in mind that we are talking about regular text files, so they are not adequate for big number of rows and frequent write operations.
Importing Data From CSVIf you have to import some CSV data from another application, as long as the data formatted properly, you can just create an empty table with the right columns and then copy the CSV file with the proper table name. Example:
use testIn the OS shell:
CREATE TABLE test_import LIKE test_csv;
cp data.csv /var/lib/mysql/test/test_import.CSVNow if you do: SELECT * FROM test_import LIMIT 2; it should show you the data on the first 2 lines of the CSV file.
Import Data ExampleMany banks allow you to export the list of transactions from your online statement as a CSV file. You could easily use this file as explained above to consult and/or manipulate the data using regular SQL statements.
ConclusionThe CSV engine provides a very flexible mechanism to move data in and out of regular text files as long as you have proper access the data directories. You can easily generate these tables from existing data. You can also easily manipulate the data with any editor and (re) import it at will. I see it as an invaluable tool to move around information, especially in development and testing environments.
Do you have interesting use cases? I'd like to hear about them.