To move data from a generic MySQL database to InfiniDB, a couple of different methods can be used. First, a user can use the MySQL SELECT INTO OUTFILE command to spool a set of table output to a flat file. For example, if a user wanted to write out data from a table named ‘t1’ with two columns into a file that was delimited by the pipe (‘|’) character, they could do the following:
Once the MySQL file has been written, it can be moved to the InfiniDB import directory and imported into InfiniDB by using the cmimport utility.
Another strategy is to make use of free open source ETL (extract-transform-load) tools to move data from one MySQL server to a InfiniDB server. Tools are available from Pentaho, Jaspersoft, Talend, and others.
A third way of quickly moving data from an entire table and importing it into InfiniDB is by use of MySQL’s CSV storage engine (available in MySQL 5.1 and above). The Comma-Separated-Value storage engine stores data for a table in a flat file where the columns are separated in the file by commas. A user can take an existing MySQL table on a non-InfiniDB installation, alter the table to use the CSV engine instead of its current engine, copy the table file to the InfiniDB server, and then alter the table back to its original engine. The only caveat is the CSV storage engine does not support NULLABLE column definitions so the columns must not have NULL constraints tied to them.
Once the user has the table file in comma separated format on the InfiniDB server, they can then move it to the InfiniDB import directory and use the cmimport utility to load it into InfiniDB.
