Migrate data from SQL Server to OmniFabric

This document will guide you on how to migrate data from Oracle to OmniFabric.

Oracle is currently the most widely used commercial database in the world. Its usage scenarios and popularity rank first in DBEngine all year round. OmniFabric can also support the scenario of migrating data from Oracle. According to the size of Oracle data, this article still recommends using online and offline modes for migration.

Data type difference

There are many differences between OmniFabric and Oracle's built-in data types. Other types can replace some differences, and some cannot be supported temporarily. The specific list is as follows:

Oracle	OmniFabric
varchar2	replace with text
nchar/nvarcahr	replace with char/varchar
NUMBER(3,0), NUMBER(5,0)	replace with smallint
NUMBER(10,0)	replace with int
NUMBER(38,0)	replace with bitint
NUMBER(n,p) (p>0)	replace with decimal(n,p)
binary_float/binary_double	replace with float/double
long	replace with text
long raw	replace with blob
raw	replace with varbinary
clob/nclob	replace with text
bfile	Not supported yet
rowid/urowid	Not Supported
user-defined types	Not supported yet
any	Not supported yet
xml	Not supported yet
spatial	Not supported yet

Online Migration

This chapter will guide you to use third-party tools - DBeaver to migrate data from Oracle to OmniFabric.

Through DBeaver, the source data is obtained in batches, and then the data is inserted into the target database as INSERT. If an error is reported during the migration process that the heap space is insufficient, please try to adjust the size of each batch of fetched and inserted data.

Applicable scenarios: scenarios where the amount of data is small (recommended less than 1GB), and the migration speed is not sensitive.
The recommended configuration of the springboard machine where DBeaver is installed: RAM 16GB or more.

Preparation

Springboard machine with a graphical interface: it can connect to the source of Oracle and the target of OmniFabric.
Data Migration Tool: Download DBeaver on the springboard machine.

Step 1: Migrate table structure

Here we take the TPCH dataset as an example and migrate the 8 tables of the TPCH dataset from Oracle to OmniFabric.

Open DBeaver, select the table to be migrated from Oracle, right-click and select Generate SQL > DDL Click Copy, first copy this SQL to a text editor for text editing Name the filer as oracle_ddl.sql and save it locally on the springboard machine.

Use the following command to replace keywords not supported by OmniFabric in the oracle_ddl.sql file:

# The commands executed by the Linux system are as follows:
sed -i '/CHECK (/d' /YOUR_PATH/oracle_ddl.sql
sed -i '/CREATE UNIQUE INDEX/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(3,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(5,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(10,0)/int/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(38,0)/bigint/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER/decimal/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/VARCHAR2/varchar/g' /YOUR_PATH/oracle_ddl.sql

# The commands executed by the MacOS system are as follows:
sed -i '' '/CHECK (/d' /YOUR_PATH/oracle_ddl.sql
sed -i '' '/CREATE UNIQUE INDEX/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(3,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(5,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(10,0)/int/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(38,0)/bigint/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER/decimal/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/VARCHAR2/varchar/g' /YOUR_PATH/oracle_ddl.sql

Connect to OmniFabric and create a new database and table in OmniFabric:

create database tpch;
use tpch;
source '/YOUR_PATH/oracle_ddl.sql'

Step 2: Migrate data

Open DBeaver, select the table to be migrated from Oracle, right-click and select Export Data:
In the Conversion Target > Export Target window, select Database, click Next; in the Table Mapping window, select Target Container, and select the OmniFabric database for the target container tpch:
In the Extraction Settings and Data Loading Settings windows, set the number of selected extractions and inserts. To trigger OmniFabric's direct write S3 strategy, it is recommended to fill in 5000:
After completing the settings, DBeaver starts to migrate the data, and after completion, DBeaver will prompt that the migration is successful.

Step 3: Check the data

After the migration is complete, the data can be inspected as follows:

Use select count(*) from <table_name> to confirm whether the data volume of the source database and target databases' data volume is consistent.
Compare the results through related queries; you can also refer to the TPCH query examples to compare the results.

Offline Migration

This chapter will guide you through importing to OmniFabric through offline files.

Applicable scenarios: scenarios with a large amount of data (more significant than 1GB) and sensitive to migration speed.

Preparation

Springboard machine with a graphical interface: it can be connected to the source end of Oracle and the target end of OmniFabric.
Data Migration Tool: Download DBeaver to the springboard machine.

Step 1: Migrate table structure

Here we take the TPCH dataset as an example and migrate the 8 tables of the TPCH dataset from Oracle to OmniFabric.

Open DBeaver, select the table to be migrated from Oracle, right-click and select Generate SQL > DDL > Copy, first copy this SQL to a text editor, and name the text editor oracle_ddl.sql, saved locally on the springboard machine.

Use the following command to replace keywords that OmniFabric does not support in the oracle_ddl.sql file:

# The commands executed by the Linux system are as follows:
sed -i '/CHECK (/d' /YOUR_PATH/oracle_ddl.sql
sed -i '/CREATE UNIQUE INDEX/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(3,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(5,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(10,0)/int/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER(38,0)/bigint/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/NUMBER/decimal/g' /YOUR_PATH/oracle_ddl.sql
sed -i 's/VARCHAR2/varchar/g' /YOUR_PATH/oracle_ddl.sql

# The commands executed by the MacOS system are as follows:
sed -i '' '/CHECK (/d' /YOUR_PATH/oracle_ddl.sql
sed -i '' '/CREATE UNIQUE INDEX/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(3,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(5,0)/smallint/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(10,0)/int/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER(38,0)/bigint/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/NUMBER/decimal/g' /YOUR_PATH/oracle_ddl.sql
sed -i '' 's/VARCHAR2/varchar/g' /YOUR_PATH/oracle_ddl.sql

Connect to OmniFabric and create a new database and table in OmniFabric:

create database tpch;
use tpch;
source '/YOUR_PATH/oracle_ddl.sql'

Step 2: Migrate data

OmniFabric has two data migration methods to choose from: INSERT and LOAD DATA. When the amount of data is greater than 1GB, it is recommended to use LOAD DATA first, followed by INSERT.

LOAD DATA

Use DBeaver to export the Oracle data table to CSV format first, and use OmniFabric's parallel loading function to migrate the data to OmniFabric:

Open DBeaver, select the table to be migrated, right-click and select Export Data to export the Oracle data table as a CSV format file:
In the Conversion Goals > Export Goals window, select CSV and click Next:

Connect to OmniFabric and import the exported CSV data into OmniFabric:

mysql> load data infile '/{filepath}/lineitem.txt' INTO TABLE lineitem FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/nation.txt' INTO TABLE nation FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/part.txt' INTO TABLE part FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/customer.txt' INTO TABLE customer FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/orders.txt' INTO TABLE orders FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/supplier.txt' INTO TABLE supplier FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/region.txt' INTO TABLE region FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';
mysql> load data infile '/{filepath}/partsupp.txt' INTO TABLE partsupp FIELDS TERMINATED BY '|' lines TERMINATED BY '\n' parallel 'true';

For more operation examples of LOAD DATA, see the Bulk Load documentation.

INSERT

The INSERT statement needs to use DBeaver to export the logical statement first and then import it into OmniFabric:

Use DBeaver to export data: Open DBeaver, select the table to be migrated from Oracle, right-click, and select Export Data > SQL. To ensure that OmniFabric directly writes to S3 when inserting, it is recommended to insert parameters in batches The number of data rows per statement is set to 5000:
On the OmniFabric side, execute the SQL file:
```
source '/YOUR_PATH/oracle_data.sql'
```

For more examples of INSERT operations, see the Insert Data documentation.

Step 3: Check the data

After the migration is complete, the data can be inspected as follows:

Use select count(*) from <table_name> to confirm whether the data volume of the source database and target databases' data volume is consistent.
Compare the results through related queries; you can also refer to the TPCH query examples to compare the results.