October 26, 2023
Summary: Data Migration in DBeaver provides the functionality to transfer data between different databases or between tables within the same database. This tutorial walks you through the steps to execute a data migration using DBeaver.
Table of Contents
Step 1 Define the data source
To initiate the data migration, you need to select your data source. Follow the steps below:
- Navigate to the Database Navigator.
- Select one or multiple tables that you want to export.
- Right-click to open the context menu.
- Choose Export Data from the options.
Tip: You can export data from a custom SQL query. Execute the query and then choose Export Data from the results context menu.
Step 2 Define data transfer target type
After selecting the data source, the next step is to specify the type of destination for the data transfer. Choose Database type as the data transfer target and press Next.
Step 3 Tables mapping
Once you have chosen Database as the transfer target type, the next step involves mapping data. This process includes specifying options, selecting the target container, and setting other configurations. Configure your data mapping settings and press Next to proceed to the next step.
Available options
Buttons:
Icon | Option | Description |
---|---|---|
Browse | Allows selection of target container. | |
Configure | Opens additional settings. For details, see the Configure section. | |
Preview data | Shows a preview of the data to be transferred. | |
Up | Moves the selected mapping closer to the head of the queue. | |
Down | Moves the selected mapping closer to the tail of the queue. | |
Mapping Rules | Choose how new tables and column names are transformed when transferring data. For details, see Mapping Rules section. | |
Auto assign | Automatically populates the Target column based on the source. | |
Choose… | Specify the target container where the data will be transferred in. |
Fields:
Option | Description |
---|---|
Target container | Defines the database or schema for the data transfer. For more details, see the Target Container section. |
Source | Displays the names of selected tables and their columns. |
Target | Shows the names of destination tables. |
Mapping | Lists the available actions for data transfer. For more details, see the Mapping section. |
Transform | Allows for the transformation of column values during the data transfer. For more details, see the Transform section. |
Target container and target table
Specify target container
Press the Choose… button to select the container where the data will be transferred.
Define a target table
You can specify the target table where the data will be transferred in multiple ways:
-
Manual Entry: Click on a cell in the Target column and manually enter the name of the table where you wish to transfer the data.
-
Drop-down list: Use the drop-down list next to the Target column to choose among the following options:
- Pre-existing table names.
- Skip: Skips the data transfer for this table.
- Browse: Opens the Choose Target Table window
-
Browse button: Alternatively, press the Browse button to select a table from the existing tables in the target container.
Mapping
To change the mapping type, click a cell in the Mapping column of Table mapping dialog box and select the required mapping type.
Action | Description |
---|---|
Create | Transfers source data into a newly created table or column in the target container. |
Skip | Does not transfer the source data. |
Existing | Transfers source data to an existing table in the target container. |
Recreate | Recreate the table, which means that the available data, keys, indexes, and other possible entities of the existing table will be lost. |
Tip: If the cells are marked with , it means that in the target table, there are no columns with matching names, otherwise the names will be filled in automatically.
Transform column values
You may also want to transform the values of some columns during the transfer. To do that, define column transformers by clicking on corresponding cells in the Transform column. You can choose one of three options:
Option | Description |
---|---|
Set to NULL | All values in the corresponding column are set to null . |
Constant | Sets column value to a constant value. |
Expression | Uses JEXL expressions to calculate the column’s value. |
Configure
Click the Configure button to open the Configure metadata structure window. This window allows you to delve into additional settings are distributed across the following tabs:
Column mapping
By navigating to the Column mapping, you can explore detailed mapping between the source and target columns.
Element | Description |
---|---|
Source Column | Contains names of columns existing in the selected source table. |
Source Type | Lists the data types assigned to the columns in the selected source table. |
Target Column | Contains names of columns in the target table where the data from the source column will be transferred. |
Target Type | Lists the data types that will be assigned to the columns in the target table. |
Mapping | Contains the list of actions to be applied to the data on data transfer. |
Transform | Displays transformations for the data in a column during the transfer. |
Important: Data types that are supported in the source database may not be supported in the target, and vice versa. To set a data type for a target column, click the cell in the Target Type column and choose from the dropdown list.
Table properties
By selecting the Table properties tab, you can modify properties of the target table, such as:
Property | Description |
---|---|
Tablespace | Specifies the tablespace for the target table. |
Partition By | Sets the partitioning for the target table. |
Comment | Allows you to add comments to the target table. |
Note: The availability of these settings may vary depending on the database and version you are using.
Target DDL
By selecting the Target DDL tab, you can view the SQL script that will be executed during the data transfer.
If you’re exporting data to a new table or recreating an existing one, the tab will display the necessary SQL statements. If not, the DDL tab will remain empty.
Mapping Rules
Clicking the Mapping Rules button opens a window that provides options for customizing how new tables and column names are transformed during the data transfer.
In the window, the following settings are available:
Option | Description |
---|---|
Name case | Sets the letter casing for table and column names. Choices include Default, Upper case, and Lower case. |
Replace spaces | Determines how spaces in table and column names are handled. Options are: Do not replace, Replace with underscore, and Remove, convert to CamelCase. |
Max data type length | Sets the maximum length for data types. |
Save current changes to global settings | If checked, saves the current mapping rules to global settings. Otherwise, they will be saved at the data source settings level. |
Global settings | Opens the global settings window for Names Mapping Rules. Alternatively, to open global settings, go to Window -> Preferences -> Connections -> Data Transfer |
In addition to using the Global settings button, you can also access these settings by navigating to Window -> Preferences -> Connections -> Data Transfer.
Important: After modifying the Mapping Rules settings, you will be prompted to confirm your changes. Modifying the Mapping Rules may result in the loss of names that were already changed.
Keyboard Shortcuts
The following keyboard shortcuts for easy navigation within the mapping table area of the Tables mapping tab are supported:
Shortcut | Action |
---|---|
Up | Move one row up. |
Down | Move one row down. |
Right | Expand list of source table columns. |
Left | Collapse list of source table columns. |
Space | Auto-assigns the target. |
Del | Sets mapping type to skip. |
Step 4 Extraction settings
After setting up your table mappings, the next step is to define how the data will be extracted from the source. The Extraction settings tab offers various options to optimize this process. Configure these settings to suit your specific data transfer requirements and press Next.
Option | Description |
---|---|
Maximum threads | Defines the number of threads to be used for data transfer. |
Extract type | Choose Single Query for smaller data loads. Use By Segments for larger data migrations. You can also set the Segment Size value when this option is selected. |
Open new connection(s) | If checked, a new connection is established, ensuring data transfer does not affect other database operations. |
Select row count | Enables a progress bar to display data migration status. |
Fetch size | Indicates the number of rows fetched per server round trip, affecting extraction performance. |
Step 5 Data load settings
After configuring the extraction settings, you’ll need to specify how the data will be loaded into the target database. The Data load settings tab provides a range of options to control this part of the process. Adjust the settings according to your needs and then press Next.
Data load settings tab defines how the extracted data will be pushed to the target. The following options are available.
Option | Description |
---|---|
Transfer auto-generated columns | Fill in or skip columns marked with the “autogenerated” status. Some databases accept values in such columns, while others will throw a syntax error. |
Truncate target table(s) before load | Select this checkbox only if you want all the data to be cleared from the target table. Be very careful with this option! |
Disable referential integrity checks during the transfer | Disabling constraints in the target table. This setting prevents database errors by temporarily disabling the constraints. However, please note that not all databases support this functionality. |
Replace method | Read our guide on Data Import and Replace to learn more about the replacing method option. |
Open new connection(s) | Use this option to speed up data transfer. If selected, a new connection will be opened and the data transfer will not interfere with other calls to the database where data is being transferred to. |
Use transactions | This option allows you to speed up the data transfer and to define the number of rows for each transaction by setting the Commit after insert of parameter. |
Do Commit after row insert | Performing a commit after a certain number of inserted rows. This setting specifies that a commit operation should be executed after a specified number of rows have been inserted into the table. |
Use multi-row value insert | Use multi-row insert with extended values number for higher performance. Database-specific setting. |
Skip bind values during insert | This option can drastically increase performance for some drivers like Redshift by skipping a process of binding values and setting them directly, but it opens up a vulnerability to SQL injections. Not recommended if you are not sure of imported file contents. |
Disable batches | Select this checkbox if you want to disable the use of batch imports. The import will be made row by row. Enabling this function will show all import errors, but make the import process slower. |
Ignore duplicate rows errors | In the import process, if a database encounters a duplicate key from the import row in the target table, such errors are ignored, and the import operation continues without failure. |
Use bulk load | Bypasses transaction settings and loads the entire dataset using the native tool provided by the database. |
Open table editor on finish | If selected, the table editor is to be opened when data transfer is finished. |
Show finish message | If selected, a notification message will be shown when the transfer is finished. |
Send results by E-Mail | Sends data transfer results by E-Mail on finish. |
Step 6 Confirm
The final step before initiating the data transfer is the Confirm tab. This tab provides a summary of all configurations set up in the previous steps. It serves as a last review to ensure all settings are correct.
Here is a table summarizing the configurations:
Option | Description |
---|---|
Source Container | Displays the source container from which data will be pulled. |
Source | Shows the specific source within the container. |
Target Container | Displays the target container to which data will be pushed. |
Target | Shows the specific target within the container. |
Source Settings | Summarizes settings related to data extraction from the source. |
Target Settings | Summarizes settings related to data loading into the target. |
These settings are final and cannot be changed at this stage. If all configurations are correct, click Finish to start the data transfer.
Tip: You can save these configurations for future use and create a task related to the data transfer operation by selecting Save task. This option consolidates your settings into a reusable task.
Upon clicking Proceed, the data transfer process will initiate.
Step 7 Export completion notification
If there are no errors, you will see a notification window indicating the successful completion of the export task. You can continue working with your database during the export process, as it will be performed in the background.