xSQL Data Export and Import Operations

In addition the database backup/restore commands, xSQL provides commands to save and load database snapshots, class data and metadata. These operations can also be performed through built-in SQL functions.

For an overview see page xSQL Commands

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

eXtremeDB provides Feed Handlers that receive market data using industry standard frameworks and store this data in an eXtremeDB database. (See the eXtremeDB Feed Handlers page for further details.)

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.

 

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 SQL select statements.