xSQL Data Export and Import Operations

Database Online Backup

The following xSQL 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 ]];
     

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 name> file. If it is necessary to restore a database from several consecutive backups, it has to be done by calling the restore several times in the chronological order:

 
    XSQL>restore [FROM] file_name [[TO] label] [WITH CIPHER=key];
     

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

1. 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 the actual size of modified data.

2. 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
     

3. The restore from a backup requires exclusive access to the database.

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

Database snapshots

To save an image of an in-memory database (transient objects) to a specified file xSQL provides the following command:

 
    XSQL>save <file-path>
     

Note that it might be necessary to open a persistent database created by xSQL (or another embedded SQL 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 in 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 the xSQL configuration file in the JSON format. To save the database metadata from xSQL into a specified file:

 
    XSQL>savemeta <file-path>
     

To open a database that has been saved as an image file, use the xSQL option “-image <filename>” to load the database from a specified file. (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 APIs - C/C++. Java, C# or Python).

Suppose that xSQL is used to save a database snapshot and metadata as follows:

 
    XSQL>save db.img
    XSQL>savemeta metadata.cfg
     

Consequently the metadata and database snapshot can be loaded using a command line like the following:

 
    xsql -c metadata.cfg -image db.img
     

Saving and Loading individual classes

It is sometimes desirable to export/ import only data for an individual database class. The following commands are provided for this purpose:

 
    XSQL>saveclass <file-path> <table-name>
    XSQL>loadclass <file-path> <table-name>
     

Saving the database schema

To save a copy of the currently loaded database schema from xSQL into a specified file:

 
    XSQL>savedict <file-path>
     

Importing from external files or feeds

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. For example, in the following input strings:

 
    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'
     

If you have purchased the eXtremeDB for HPC you can perform a simple sample CSV import by following this tutorial guide. xSQL allows importing data from external files with various formats, including text files with fixed-length records (eg. from historical NYSE quotes) and comma-delimited variable-length records (eg. from CME Trades).

Please view this link for a detailed explanation and examples of the import operation.

Also note that the eXtremeDB for HPC includes Feed Handlers for streaming live securities data from various sources.

Batch Insert for Persistent Databases

Batch insert with partial sorting of inserted objects can greatly increase both insert and select speed for disk objects with a single index. If objects are inserted in more or less sorted order, then the locality of references for B-Tree pages increases significantly during the insert. And search operation with this index becomes much faster because the objects are stored mostly in the same order as they are present in the index. Please view this link for more details on the SQL syntax for implementing a batch insert.

To enable batch insert with xSQL, the sql_optimizer_params option batch_insert_buffer_size is specified in the configuration file as follows:

 
    sql_optimizer_params : {
        batch_insert_buffer_size : 4096,
    },
     

Built-in SQL Functions

Note that, in addition to the xSQL commands described above, it is possible to call eXtremeSQL built-in functions to perform these export / import operations using standard SQL select statements.