Топ-100
 
CerebroSQL

Transfer data - lite (ETL)

Transfer data (lite) is a simple ETL mechanism for transferring data between different DBMS ( Oracle, PostgreSQL, MySQL, MSSQL ).

Creating a data transfer stream:

In the main window of the DB program -> Transfer data (lite)

Show transfer data.jpg

On the main manager, it is a collector of the ETL flow log.

To enable data transfer tracing, set the "Trace speed" check before starting the flow on the sheet.

Create a sheet and start a data transfer stream:

1. Press the "Create new list" button

2. Set up "Connect #1" connection

2.1 Go to the desired tab ( Oracle , PostgreSQL (including for PostgreSQL- based DBMSs, such as Green Plum ), MySQL (including for MySQL- based DBMSs, such as MariaDB ), MSSQL )

2.2 Fill in all fields

2.4 Press the "Connect" button. If the connection is successful, the inscription will change to "Connected" and all the fields of the "Connect #1" block will become inactive.

3. Set up "Connect #2" connection

3.1 Go to the desired tab ( Oracle , PostgreSQL (including for PostgreSQL- based DBMSs, such as Green Plum ), MySQL (including for MySQL- based DBMSs, such as MariaDB ), MSSQL )

3.2 Fill in all fields

3.4 Click the "Connect" button. If the connection is successful, the inscription will change to "Connected" and all the fields of the "Connect #2" block will become inactive.

Transfer data (ETL) - Connected database

4. Stream setting:

4.1 CommitCount - determines the size of the data portion (the number of lines loaded into the PC memory from the source database) after which insertion, the commit is made in the receiver database. It is recommended to set this value in the range from 300 to 3000 lines. Increasing the value increases the PC memory usage.

4.2 Select a mechanism (driver) that retrieves data from the source database:

4.2.1 DATASET (SQL) - the classic DataSet is used, which retrieves data using a SQL query

4.2.2 SQLSET (SQL) - a component is used that extracts data using an SQL query

4.2.2 SQLSET (TABLE) - the component is used. Input parameter name of the table data from which you want to transfer

4.3 IN Query - enter the text of the SQL query or the name of the table (if necessary, with the name of the schema in which it is located)

4.4 Loop (one line - one request, without the ";" at the end) - when this checkbox is set, the program treats each line in the IN Query field as a separate request to retrieve data. The lines are traversed in a loop and the data transfer is started in turn. IMPORTANT: the line must not end with the ";"

4.5 Select a mechanism (driver) that extracts data from the receiver database to obtain metadata about the table structure:

4.5.1 DATASET (SQL) - a classic DataSet is used that retrieves data using a SQL query

4.5.2 SQLSET (SQL) - a component is used that extracts data using a SQL query

4.5.2 SQLSET (TABLE) - component is used. Input parameter is the name of the metadata table to be retrieved

4.6 OUT Query - enter the text of the SQL query or the name of the table (if necessary, with the name of the schema in which it is located)

4.7 Direct - use a specialized data freeze mode.

4.8 Reind - previewing a dataset by a component

4.9 Optimize - optimize data reading speed

5. Press the " Run data transfer " button

To stop the flow, press the "Stop data transfer" button

Data transfer speed.

The speed of data transfer depends both on the selected DBMS between which the data migration process takes place, and on the performance of the disks.

 

Test case:

IN connection

DBMS: Postgres

Host: localhost

SSD disk

OUT connection:

DBMS: MS SQL Server

Host: virtual machine

Table: Created based on DBA_TABLES view

 

***** 2020.09.20 00:46:18 *****
Type: SQLSET (TABLE)
SQL: testtb3

Timed: 189.638
RowPerSec: 8623
Read: 1629693
Move: 1629693
Error: 0
End: 2020.09.20 00:49:28
***************************

Transfr data - Speed row
Transfer data - Speed mb