# 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 the 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 `restore` several 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 xSQL User's Guide sections for further explanation and examples of xSQL functions.

> The next step in this Tutorial will demonstrate how to use some of the powerful eXtremeSQL analytics functions in xSQL.