Skip to main content
Version: 3.14
Features and pricing

Importing Existing Tables to ScalarDB by Using ScalarDB Schema Loader

You might want to use ScalarDB (e.g., for database-spanning transactions) with your existing databases. In that case, you can import those databases under the ScalarDB control using ScalarDB Schema Loader. ScalarDB Schema Loader automatically adds ScalarDB-internal metadata columns in each existing table and metadata tables to enable various ScalarDB functionalities including transaction management across multiple databases.

Before you begin​

warning

You should carefully plan to import a table to ScalarDB in production because it will add transaction metadata columns to your database tables and the ScalarDB metadata tables. In this case, there would also be several differences between your database and ScalarDB, as well as some limitations.

What will be added to your databases​

  • ScalarDB metadata tables: ScalarDB manages namespace names and table metadata in a namespace (schema or database in underlying databases) called 'scalardb'.
  • Transaction metadata columns: The Consensus Commit transaction manager requires metadata (for example, transaction ID, record version, and transaction status) stored along with the actual records to handle transactions properly. Thus, this tool adds the metadata columns if you use the Consensus Commit transaction manager.
note

This tool only changes database metadata. Thus, the processing time does not increase in proportion to the database size and usually takes only several seconds.

Requirements​

Set up Schema Loader​

To set up Schema Loader for importing existing tables, see Set up Schema Loader.

Run Schema Loader for importing existing tables​

You can import an existing table in JDBC databases to ScalarDB by using the --import option and an import-specific schema file. To import tables, run the following command, replacing the contents in the angle brackets as described:

java -jar scalardb-schema-loader-<VERSION>.jar --config <PATH_TO_SCALARDB_PROPERTIES_FILE> -f <PATH_TO_SCHEMA_FILE> --import
  • <VERSION>: Version of ScalarDB Schema Loader that you set up.
  • <PATH_TO_SCALARDB_PROPERTIES_FILE>: Path to a properties file for ScalarDB. For a sample properties file, see database.properties.
  • <PATH_TO_SCHEMA_FILE>: Path to an import schema file. For a sample, see Sample import schema file.

If you use the Consensus Commit transaction manager after importing existing tables, run the following command separately, replacing the contents in the angle brackets as described:

java -jar scalardb-schema-loader-<VERSION>.jar --config <PATH_TO_SCALARDB_PROPERTIES_FILE> --coordinator

Sample import schema file​

The following is a sample schema for importing tables. For the sample schema file, see import_schema_sample.json.

{
  "sample_namespace1.sample_table1": {
    "transaction": true
  },
  "sample_namespace1.sample_table2": {
    "transaction": true
  },
  "sample_namespace2.sample_table3": {
    "transaction": false
  }
}

The import table schema consists of a namespace name, a table name, and a transaction field. The transaction field indicates whether the table will be imported for transactions or not. If you set the transaction field to true or don't specify the transaction field, this tool creates a table with transaction metadata if needed. If you set the transaction field to false, this tool imports a table without adding transaction metadata (that is, for a table using the Storage API).

Data-type mapping from JDBC databases to ScalarDB​

The following table shows the supported data types in each JDBC database and their mapping to the ScalarDB data types. Select your database and check if your existing tables can be imported.

MySQLScalarDBNotes
bigintBIGINTSee warning 1 below.
binaryBLOB
bitBOOLEAN
blobBLOBSee warning 2 below.
charTEXTSee warning 2 below.
doubleDOUBLE
floatFLOAT
intINT
int unsignedBIGINTSee warning 2 below.
integerINT
longblobBLOB
longtextTEXT
mediumblobBLOBSee warning 2 below.
mediumintINTSee warning 2 below.
mediumtextTEXTSee warning 2 below.
smallintINTSee warning 2 below.
textTEXTSee warning 2 below.
tinyblobBLOBSee warning 2 below.
tinyintINTSee warning 2 below.
tinyint(1)BOOLEAN
tinytextTEXTSee warning 2 below.
varbinaryBLOBSee warning 2 below.
varcharTEXTSee warning 2 below.

Data types not listed in the above are not supported. The following are some common data types that are not supported:

  • bigint unsigned
  • bit(n) (n > 1)
  • date
  • datetime
  • decimal
  • enum
  • geometry
  • json
  • numeric
  • set
  • time
  • timestamp
  • year
warning

  1. The value range of BIGINT in ScalarDB is from -2^53 to 2^53, regardless of the size of bigint in the underlying database. Thus, if the data out of this range exists in the imported table, ScalarDB cannot read it.

  2. For certain data types noted above, ScalarDB may map a data type larger than that of the underlying database. In that case, You will see errors when putting a value with a size larger than the size specified in the underlying database.

  3. The maximum size of BLOB in ScalarDB is about 2GB (precisely 2^31-1 bytes). In contrast, Oracle blob can have (4GB-1)*(number of blocks). Thus, if data larger than 2GB exists in the imported table, ScalarDB cannot read it.

  4. ScalarDB does not support Oracle float columns that have a higher precision than DOUBLE in ScalarDB.

  5. ScalarDB does not support Oracle numeric(p, s) columns (p is precision and s is scale) when p is larger than 15 due to the maximum size of the data type in ScalarDB. Note that ScalarDB maps the column to BIGINT if s is zero; otherwise ScalarDB will map the column to DOUBLE. For the latter case, be aware that round-up or round-off can happen in the underlying database since the floating-point value will be cast to a fixed-point value.

Use import function in your application​

You can use the import function in your application by using the following interfaces: