19
Data Engineering:Extract, Transform,and Load Using Talend Open Studio.
Hi there, welcome back to the data engineering series. I know most of you ask how do we do these so-called ETL Or ELT processes as a Data Engineer?
I've got you now, don't worry.
Today we will be going through the process of Extracting, Transforming, and Loading Data step by step from SQL server to Postgres. There are several ETL tools including, Talend, Pentaho, Informatica, Stitch, Xplenty, Alooma, Panapoly, Aws Glue, etc. I will introduce one ETL tool called Talend.
Introduction toTalend OpenStudio
It is important to use low code ETL tools. In this article, we will show how to install and get started with Talend. We will use Talend later to Migrate Data from MSSQL Server to Postgres.
To get started we download Talend Open Studio for Linux here. Note that you can also download for any Operating System in the same link.
After downloading you will receive multiple files, run the Linux file which has a .sh file. This is how it looks!
Launch Talend now. This is how it should open:
It's now time to get our hands dirty.
At the Talend Studio, we create a job design(on your left-hand side)then create tBDInput for SQL Server and tBDOutput for Postgres. These inputs and outputs DBs will be used during connections and migration. At this point, you can create a connection between the two databases.
Right Click on tBDInput Trigger the On subjob OK and drag it to tBDOutput to make the connection complete.
Ensure to enter correctly the database credentials i.e username, password and database name,table name , which hosts the table and database to migrate.
also
This shows the SQL server credentials to enter.After all the configuring and connecting both databases, it's time now to start moving our data from SQL server to Postgres. We write an SQL query to migrate the data. To start with we start with SQL server by using the following command which gets data from the users table:
This is used for migration and logging using the get_date function.
Now we now have to write an SQL query to insert data into Postgres.In the UI we create a table and drop it if it exists then perform the action of insertion to custdatademo table as shown;
For Postgres configuration on Talend.It's now time for us to run the job and migrate our data from MSSQL to Postgres.
Our job runs successfully!.
Now it's time where we have been waiting for so long and it's time to see our output in Postgres. Let's dive in fast and confirm our hard work.
The data has been MIgrated Successfully using Talend as you can see in PostgresAs you can see we have migrated the data successfully.Thank you for being with us on this long journey.
This is a complete Extract, Transform, and Load(ETL) using Talend.
Happy learning guys!
Article Written by :
WanjohiChristopher
19