Migrating Data Between Databases Using DbVisualizer
Data importing and exporting are important in database development and data analysis because they allow for the integration of data from various originations, making it possible to work with a larger and more diverse set of data. This can be crucial in many fields, such as business, where data from multiple sources is needed to make well-informed decisions.
Data import is the process of bringing in data from an external source into a software system. Data import can involve a variety of exercises, such as converting data into a compatible format, cleaning and transforming data, loading data into a database, etc.
Data export on the other hand, is the process of taking data that is currently stored within a software program or system and conveying it to an external destination.
Setting Up
For the purpose of this article, we’re going to utilize MySQL and PostgreSQL database management systems. To set up both database connections, we will need to first install both DBMS systems and then connect them to DbVisualizer.
Setting Up MySQL
To set up MySQL, navigate to the MySQL download page to download the recommended installer for your operating system. Use these instructions: Windows instruction, macOS instruction, Linux instruction to help you install MySQL on Windows, macOS, and Linux operating systems respectively.
Setting Up Postgres
To install Postgres, navigate to the Postgres download page and download the recommended installer for your operating system. You can follow this tutorial for installation on macOS, this installation tutorial to help you install Postgres on a Windows operating system, and this tutorial for installation on a Linux operating system.
Setting Up DbVisualizer
To install DbVisualizer, navigate to the DbVisualizer download page to download the recommended installer for your operating system.
Run the download installer to install DbVisualizer on your computer. Open DbVisualizer once it has been installed, and you will be prompted to create a database connection.
Connecting to Postgres
Step 1: Run the command below in the terminal of your computer to confirm the successful installation of the Postgres on your computer.
When the prompt opens, set a password for PostgreSQL from the prompt given.
Now, let us create a database called CPIforecast which will hold records of the average price change over time of consumer goods and services. Run the command below in the terminal of your computer to create the database.
To check if the database has been created successfully in the list of databases, run the command below.
You can verify that the CPIforecast database has been created.
A view of the newly created Postgres database.
↓
↑ A view of the newly created Postgres database.
Step 2: Start the DbVisualizer software and click on the “Create a Connection” button.
Creating a database connection in DbVisualizer.
↓
↑ Creating a database connection in DbVisualizer.
Searching and selecting Postgres driver in DbVisualizer
↓
↑ Searching and selecting Postgres driver in DbVisualizer
Step 3: Fill in the database connection input fields with the appropriate credentials.
- Name: CPI Forecast
- Database: cpiforecast
- Database Userid: postgres
- Database Password: Your Postgres password
Click on “Connect” after filling the required fields to successfully connect to the database.
Object view tab for the Postgres connection.
↓
↑ Object view tab for the Postgres connection.
Connecting to MySQL
To create your MySQL database connection, open the MySQL window and follow the steps below.
Step 1: Click on the “Start MySQL Server” button and enter your operating user password to start the server.
Step 2: Open the terminal window of your computer and run the command below to open the MySQL prompt.
When the prompt opens, enter the password used during the MySQL installation process.
Step 3: Now, let us create a database called ‘CPIforecast Annex’ by running the command below in the terminal and pressing ‘enter’.
To confirm if the database has been created, run the command below.
We can see that the “cpiforecastannex” database has been created.
A view of the newly created MySQL database.
↓
↑ A view of the newly created MySQL database.
Step 4: Let us now connect the “cpiforecastannex” created to DbVisualizer. Under the “Databases” tab in the DbVisualizer window, click on the “create new database connection button”. Search and select ‘MySQL 8’ from the list of drivers.
Creating the new database connection.
↓
↑ Creating the new database connection.
Step 5: Fill in the database connection input fields with the appropriate credentials.
- Name: ‘CPI forecast Annex’
- Database: ‘cpiforecastannex’
- Database Userid: ‘root’
- Database Password: password for root user.
Click on “Connect” after filling the required fields to successfully connect to the database.
Object view tab for MySQL connection.
↓
↑ Object view tab for MySQL connection.
Now, you have more than one database connected to DbVisualizer.
Importing Data Table Into Postgres Using DbVisualizer
Before you can migrate data from one database to another, you will typically have to import data into one database before migrating it to another. The process of migration typically involves extracting data from the source database, transforming it if necessary, and then loading it into the target database.
Step 1: Navigate to this CPI Forecast file and download the dataset.
Step 2: Next, Open the database connection CPI Forecast tab tree as shown below.
Opening database connection tab tree.
↓
↑ Opening database connection tab tree.
Open the database tab tree.
Opening CPI Forecast database tab tree.
↓
↑ Opening CPI Forecast database tab tree.
Inside the Databases tab tree, open the cpi forecast database tab tree.
Opening the cpi forecast tab tree.
↓
↑ Opening the cpi forecast tab tree.
Inside the cpi forecast database tab tree, open the schemas tab tree.
Opening the schemas tab tree.
↓
↑ Opening the schemas tab tree.
Inside the schemas tab tree, open the public tab tree.
Opening the public tab tree.
↓
↑ Opening the public tab tree.
Step 3: Right-click on the ‘Tables’ and select ‘Import Table Data’ from the menu. Navigate to the location of the downloaded CPI forecast data set on your local computer and click on “open.”
Selecting and opening CPI Forecast data file.
↓
↑ Selecting and opening CPI Forecast data file.
Step 4: Click on “Next” until you get to the final import window as shown below.
Making necessary effects in the final import window.
↓
↑ Making necessary effects in the final import window.
Change the table name to “CPIForecastData.” Since SQL does not allow spaces in table names, let us rename the column name to something with no spaces by omitting all spaces in the “Changes in Consumer Price Indexes for food, 2020 through 2023” column name.
Step 5: Click on the ‘Next’ button and the ‘import’ button to import the table data into the Postgres database.
Importing CPI Forecast table data.
↓
↑ Importing CPI Forecast table data.
You should see success in the import log as shown below.
Import success.
↓
↑ Import success.
Once it has been imported, right-click on the table tab and click on the “Refresh Objects Tree” option. Click on the Data tab to see the table data as shown below.
Imported CPI Forecast data table.
↓
↑ Imported CPI Forecast data table.
Importing the Data From PostgreSQL Into MySQL
Since both Postgres and MySQL databases are connected, let us navigate to the schema and table we want to export from in the Postgres database. In this case, our table is ‘cpiforecastdata.’
Step 1: Right-click on the table and select “Export Data” from the context menu.
Exporting table data into MySQL from Postgres.
↓
↑ Exporting table data into MySQL from Postgres.
Step 2: In the Export Wizard, select the ‘SQL’ as the output format, select ‘SQL commander’ as the output destination and check the ‘GENERATE CREATE’ option.
Implementing required settings.
↓
↑ Implementing required settings.
Step 3: Click on the export button and close the export window. This will open up the SQL commander as shown below.
Queries for creating tables in the target database.
↓
↑ Queries for creating tables in the target database.
Step 4: Since we want to export the data to another database, let us select the target database connection. In this case, our target database (MySQL) is named as “CPI Forecast Annex”
Selecting connection where you want to export the table data.
↓
↑ Selecting connection where you want to export the table data.
Step 5: From the DbVisualizer menu bar, click on ‘SQL Commander’ and select Execute to execute the SQL commands. You should see success in the DbVisualizer log.
Export log showing table data export success.
↓
↑ Export log showing table data export success.
Once it has been exported, navigate to the MySQL database tree, locate the table tab tree, right click on the table tab and refresh to see the exported table as shown below.
Exported table data in MySQL.
↓
↑ Exported table data in MySQL.
We can see that the table data we imported into our Postgres database has been successfully exported into our MySQL database. Migrating data between databases is an important step in data analysis because it allows for the integration of data from multiple sources. This can be useful in a variety of ways, such as: data warehousing, data integration, data distribution, data replication. The ability to migrate data between databases is critical for organizations that need to make sense of large and complex data sets. It allows them to easily integrate and analyze data from different sources, which in turn can lead to better decision-making, improved efficiency, and increased competitiveness.
Conclusion
In this blog, we have learnt how to migrate data from one database to another while making use of DbVisualizer. This is an important factor in data analysis for organizations that make sense of large and complex data. We hope you enjoyed working with DbVisualizer.
If you're looking for an all-in-one database management tool, look no further than DbVisualizer. With its easy-to-use interface and powerful features, you can easily connect to and manage multiple databases, including MySQL, PostgreSQL, Oracle, and more. Plus, with its free trial and free version options, there's no risk in giving it a try. Upgrade your database management game with DbVisualizer. Try it out today!
