ToC path:

Step 6: Exporting and Importing Data

It can be important, especially when using in-memory databases, to save data to or load data from persistent storage; i.e. disk based files. There are a number of commands to do this. Also when using persistent databases, it may be desirable to use the backup command to save a snapshot of its current state.

Save and Load Class Data

To save and/or load the data for an entire class (table) to/from an external file, use the saveclass and loadclass commands which have the following syntax:
 
    XSQL>saveclass <file-path> <table-name>
    XSQL>loadclass <file-path> <table-name>
     
To demonstrate this, from the samples/xsql/scripts/financial directory, start xSQL with the following command to create and display the Account table:
     
    xsql -size 100m -i -f account.sql
    select * from Account;
    userid  name    lastlog
    ------------------------------------------------------------------------------
    101     Dennis Hamer    01/23/2015 14:15:03
    102     Edith Jackson   02/12/2015 19:30:16
    103     Eric Harmon     02/14/2015 09:34:47
     
    Selected records: 3
     
Now perform the following commands to save and load the Account table's data:
 
    XSQL>saveclass account.txt Account
    XSQL>delete from Account;
    XSQL>select * from Account;
    userid  name    lastlog
    ------------------------------------------------------------------------------
     
    Selected records: 0
     
    XSQL>loadclass account.txt Account
    XSQL>select * from Account;
    userid  name    lastlog
    ------------------------------------------------------------------------------
    101     Dennis Hamer    01/23/2015 14:15:03
    102     Edith Jackson   02/12/2015 19:30:16
    103     Eric Harmon     02/14/2015 09:34:47
     
    Selected records: 3
     
Note that after deleting the data we verified that the table is empty; then after loading from account.txt, the data is restored.

Saving and loading a database image

To save an image of an in-memory database (transient objects) to a specified file use the save command, which has syntax:
 
    XSQL>save <file-path>
     
For example:
     
    XSQL>save account_db.img
     
To open (load) a database that has been saved as an image file, it is necessary to also have saved the metadata that precisely describes the database:
     
    XSQL>savemeta account_db.cfg
     
Now we can use the xSQL option -image <filename> to load the database from a specified file. For example:
     
        xsql -size 100m -i -c account_db.cfg -image account_db.img
         
(Note that the metadata specifications for xSQL must be identical to those used to save the image, whether the image file was created by xSQL or any of the eXtremeSQL APIs).

Saving Metadata and Dictionary information

It may be necessary to open a persistent database created through an application with xSQL, or another eXtremeSQL application. In order to be able to do so, xSQL must use the exact same metadata (runtime options, page sizes, devices, database parameters and dictionary) as for the original database. (Note that applications can use the mco_db_save_metadata() function in the C API or equivalent methods in the C++, Java, C# or Python APIs to save the database metadata.) The metadata is saved in an xSQL configuration file in JSON format. Consequently the metadata can then be loaded via the configuration file at startup as shown above.

To save the database schema from xSQL into a specified file:
 
    XSQL>savedict <file-path>
     
Backup of an entire database

The following commands are provided to backup and restore in-memory or persistent databases:
 
    XSQL>backup [TO] file_name [FULL|INCREMENTAL|AUTO] [[AS] label] [WITH CIPHER=key [',' COMPRESSION=level ]];
     
Note that the file_name argument is required; the other parameters are optional.

The restore command is used to apply a backup to the current database from a given backup file.
 
    XSQL>restore [FROM] file_name [[TO] label] [WITH CIPHER=key];
     
If it is necessary to restore a database from several consecutive backups, it has to be done by calling the restoreseveral times in the chronological order.

To list the content of a backup file use the SQL function “backup <backup-file>” to print out the backup labels and some generic backup information to the console:
 
    XSQL>select * from backup <backup-file>;
     
Notes on database backup

The size of the backup file could be equal to or exceed the actual size of the database. This is because backup frames contain overhead -- the increments can be larger than actual size of modified data.

While the database is being backed up, it is available for both read and write operations with the exception of the short period of time at the end of the backup process. At the end of each backup, the database is locked and becomes unavailable for updates. This is transparent to applications; a write transaction would wait for the database to become available. Note that it is quite possible to perform a continuous backup in a loop. For example, using bash:
 
    !/bin/bash
    While true; do
        xsql ... "BACKUP ..."
    done
     
The restore from a backup file requires exclusive access to the database.

The backup protocol maintains database integrity and does not allow restoring the database from an out-of-order backup.

Importing from CSV files

Often it is useful to import data from external CSV files. This can be done via the import command, which has syntax:
 
    XSQL>import TABLE CSV-FILE [(use|skip) header] [commit N]
     
Note that the header line indicates the column names to be imported if use is specified, otherwise if skip is specified all of the table columns are expected in each line of the import data; commit N, if present, specifies the transaction blocking factor, the number of lines of input for each database commit.

The possible delimiter characters are defined by the string “; , | \t “. The first one of these characters encountered in the input stream will be used as the delimiter for the following data. Following are some example input strings explaining how they will be interpreted:
 
    A;B,C;D|E - the ';' will be used as delimiter, there are three fields: 'A', 'B,C' and 'D|E'
     
    A,B,C;D|E - the ';' will be used as delimiter, there are three fields: 'A', 'B' and 'C;D|E'
     
    A|B,C;D|E - the '|' will be used as delimiter, there are three fields: 'A', 'B,C;D'  and 'E'
     
For example, the sample file account.csv could be imported as follows:
 
    XSQL>import Account account.csv skip header
    Import of 4 records completed in 0 milliseconds
    XSQL>select * from Account;
    userid  name    lastlog
    -----------------------------------------------
    101     Dennis Hamer    01/23/15 14:15:03
    102     Edith Jackson   02/12/15 19:30:16
    103     Eric Harmon     02/14/15 09:34:47
    104     James Taylor    03/30/15 19:24:27
    105     Jackson Brown   04/02/15 09:17:11
    106     Eric Clapton    04/14/15 23:59:07
    107     Van Morrison    04/20/15 20:22:18
     
    Selected records: 7
     
Some more elaborate examples of importing data from CSV files with various formats (including NYSE quotes and CME Trades) are presented in the xSQL User's Guide sections. (Also please refer to this User's Guide for several more advanced features of xSQL.)

And note that there are also SQL functions to perform the data import and backup functions described above. For example, to backup a persistent database:
 
    select backup('copy.dbs', 'copy.log', 1);
     
or to save a snapshot of an in-memory database:
 
    select save_snapshot('saved.img');
 
Please refer to the eXtremeSQL User's Guide sections for further explanation and examples of eXtremeSQL functions.