Using the Import and Export Utilities

Oracle NoSQL Database contains an import/export utility to extract and load table based data, raw key/value based data, and large object data. You can use the import/export utility, available through kvtool.jar, to:

Export table data from Oracle NoSQL Database and store the data as JSON formatted files on a local (or network mounted) file system.
Import ad-hoc JSON data generated from a relational database or other sources, and JSON data generated via MongoDB strict export.
Export data and metadata from one or more existing Oracle NoSQL Database tables, raw key/value based data, and large object data to a compact binary format.
Read data from, or write data to files in the file system.
Import one or more tables into an Oracle NoSQL Database.
Restart from a checkpoint if an import or export fails before completion.

The import/export utility allows you to configure rich error handling semantics and data transformations. Other configurable options include logging, check-pointing, and restarting from failure points.

For more information, see import and export.

Import and Export Functionality

The import/export utility allows you to:

Import data files containing binary, JSON or MongoDB JSON format into Oracle NoSQL Database.
Export data from Oracle NoSQL Database to data files in binary or JSON format.

When you export data from Oracle NoSQL Database, the import/export utility creates an "export package" (a directory structure) that contains schema metadata and table data. This package is self-contained and you can use it to re-create the state of one or more tables on any Oracle NoSQL Database instance. When you import data into Oracle NoSQL Database, you can run the utility against this "export package", or any directory that contains files with data formats supported by the import/export utility.

Both import and export operations execute as online operations. These operations run against an Oracle NoSQL Database data store that is servicing read and write requests. Hence, when you export data, the export utility does not force a synchronization point or obtain a global lock that guarantees a consistent table state across all the shards in your Oracle NoSQL Database data store. If you wish to capture a consistent state for your tables, it is recommended that you pause any ongoing write operations (from your application) that modifies the state of your tables during the export operation. During an import operation, you can configure the import to ignore overwrite of any record that already exists in your data store.

Understanding Data Sources and Data Targets (Sinks)

When working with the import/export utility, you should understand the concept of "source", "sink", and data format. You use the import/export utility to transfer data from the configured source to the configured sink, optionally transforming the data (based upon configuration options explained later) before inserting the data into the configured sink. The "direction" of the transfer defines how sources and sinks are utilized. The data format describes how you want the import/export tool to interpret the data being read from a source or sink.

When you export, the source type is always Oracle NoSQL Database (where you extract data from) and the sink is the recipient of that data. When you import, the source type is currently limited to a file and the sink is always Oracle NoSQL Database. In subsequent releases, you will be able to use source type as Oracle Cloud Storage and other cloud storage providers.

For example, you may want to import some textual JSON data files into Oracle NoSQL Database. In this case, your source type is LOCAL (indicating that the data is stored in files) and the sink type is NoSQLDB. The data format is JSON.

Note:

You can configure several data formats for sources and sinks in the import/export utility’s configuration file.

Importing Data

Use the import utility to import a package containing binary or JSON data and schema that was created with the export utility. By using the import utility, you can also import JSON data that was extracted from MongoDB or other sources such as a relational database. When you run the import/export utility to import data, the sink (or target of the import) is always Oracle NoSQL Database, while the source format (JSON, binary, or MongoDB) can be configured.

When you import data from an export package, the import utility loads data for one or more tables whose data exist within the known structure of the export package. When you import data that was not created by export, the import utility only supports loading into a single table. If you need to load multiple tables with data that was not created by export, it is advisable to store the data for each table in a separate directory structure and run the import utility once for each table to be loaded. Modify the path and tableName parameters for each table in the JSON config file. See the section below for a detailed description of the parameters available in the JSON config file.

Exporting Data

When you use the export option, the import/export utility creates a package that contains:

Data from your table(s)
Schema definition metadata of your table(s)
Logging information

When you use the import option, you may supply the same package to the import/export utility to automatically create and load your tables from the contents of the export package.

You can export all data and metadata to either a local or network mounted filesystem. In future releases, you can export your data to the Oracle Storage Cloud or other cloud storage providers.

You can optionally use the import/export utility to export individual tables instead of the entire data store. This utility writes:

Application created data (excluding security data).
Schema metadata:
- DDL for recreating the table(s).
- DDL for recreating indexes.
Time to live information for every table record.

Import/export utility does not allow you to:

Export security data (such as user definition)
Export Oracle NoSQL Database deployment information (such as cluster topology)
Incrementally export data (such as export from a given moment in time)
Export derived information (such as index data and key distribution statistics data). You must recreate derived information upon data import.

For export operations, Oracle highly recommends that the target of the export operation be an encrypted file system such as dm-crypt.

Examples

Export the entire contents of Oracle NoSQL Database data store and place the export package into the /users/oracle/kvstore_export directory. Supply two helper hosts, sn1 and sn2, so that the export can try and contact any host that is reachable.
```
java –Xmx64m –Xms64m -jar KVHOME/lib/kvtool.jar export \
    -export-all -store kvstore -helper-hosts sn1:5000,sn2:5000 \
    -config export_config
```
The export_config file resides in the current working directory and the file content is as below:
```
{
    "path": "/users/oracle/kvstore_export"
}
```
Import all data from the export package created in example 1 into a different Oracle NoSQL Database data store. Since you are using the export package created by the export utility, all tables will be automatically created if they do not already exist.
```
java –Xmx64m –Xms64m -jar KVHOME/lib/kvtool.jar import \
    -import-all -store other_kvstore -helper-hosts sn10:5000 \
    -config import_config
```
The import_config file resides in the current working directory and the file content is as below:
```
{
    "path": "/users/oracle/kvstore_export"
}
```

Export 3 tables in JSON format by placing the exported data into the /users/oracle/json_table_data directory.

java –Xmx64m –Xms64m -jar KVHOME/lib/kvtool.jar export \
    -table table1, table2, table3 -store kvstore -helper-hosts sn1:5000,sn2:5000 \
    -config export_config -format JSON

The export_config file resides in the current directory and the file content is as below:

{
    "path": "/users/oracle/json_table_data"
}

Import 3 tables that were exported in example 3 to a different Oracle NoSQL Database data store. Use checkpoints to be able to restart the import from where it left, if it fails.
```
java –Xmx64m –Xms64m -jar KVHOME/lib/kvtool.jar import \
    -store other_kvstore -helper-hosts sn10:5000 \
    -config import_config -table table1, table2, table3 \
    -status /users/oracle/checkpoint_dir –format JSON
```
The import_config file resides in the current working directory and the file content is as below:
```
{
    "path": "/users/oracle/json_table_data"
}
```

Run import on data files that contain data from a MongoDB strict export while not transferring a sensitive attribute in the data entitled "social_security_number". Store all of the errors and progress information for the import in /users/oracle/mongo_db_import_logs.

java –Xmx64m –Xms64m -jar KVHOME/lib/kvtool.jar import \
    -external -store other_kvstore \
    -helper-hosts sn10:5000 -config import_config \
    -status /users/oracle/checkpoint_dir –format MONGODB_JSON

The import_config file resides in the current working directory and the file content is as below:

{
    "path": "/users/oracle/my_mongodb_data",
    "ignoreFields": "social_security_number",
    "errorOutput": "/users/oracle/mongo_db_import_logs"
}