Tutorial Qlik Replicate Demo – MySQL

Database-to-Database Replication

Step 1 – MySQL Source Configuration

The first thing we need to do is create a source endpoint. We do this by clicking the Manage Endpoint Connections button at the top of the screen.

And you will see this window.

From there, click on Add New Endpoint Connection link or the + New Endpoint Connection button at the top of the screen.

Once you do that you will see this window:

We will now create a MySQL source endpoint:

• Replace the text New Endpoint Connection 1 with something more descriptive like MySQL Source,
• make sure the Source radio button is selected,
• and then select MySQL from the dropdown selection box.

You will notice as we proceed that the content of the configuration window is context-sensitive.

Fill in the blanks as indicated in the images above:

• Server: mysqldb
• Port: 3306
• User: root
• Password: eI3chl835qMRWt2F
• Security/SSL Mode: None

and then click on Test Connection. Your screen should look like the following, indicating that your connection succeeded.

Assuming so, click Save and the configuration of your MySQL source endpoint is complete. Click Close to close the window.

For more details about using MySQL as a source, please review the section “Using a MyQL-Based Database as a Source” in Chapter 8 “Adding and Managing Source Endpoints” of the Qlik Replicate User Guide

Database-to-Database Replication

Step 2 – Postgres Target Configuration

Next we need to configure our Postgres target endpoint. The process is much the same as you saw with the MySQL source, and once again you will note that the configuration process is context-sensitive as we move along.

As before, the first step in the configuration process is to tell Replicate that we want to create a new endpoint. If you are back on the main window, you will need to click on Manage Endpoint Connections button.

and then press the + New Endpoint Connection button.

and you will see a window that resembles this:

We will now create a PostgreSQL Target endpoint:

• Replace the text New Endpoint Connection 1 with something more descriptive like Postgres Target,
• make sure the Target radio button is selected,
• and then select PostgreSQL from the dropdown selection box.

Fill in the blanks as indicated in the image above:

• Host: postgresdb
• Port: 5432
• User: qlik
• Password: eI3chl835qMRWt2F
• Database: qlikdb
• Security/SSL Mode: disable

and then click on Test Connection. Your screen should look like the following, indicating that your connection succeeded.

Assuming so, click Save and the configuration of your Postgres target endpoint is complete. Click Close to close the window.

For more details about using PostgreSQL as a target, please review the section “Using a PostgreSQL-Based as a Target” in Chapter 9 “Adding and Managing Target Endpoints” of the Qlik Replicate User Guide

Database-to-Database Replication

Step 3 – Configure Your Task

Now that we have configured our MySQL source and Postgres target endpoints, we need to tie them together in what we call a Replicate task. In short, a task defines the following:

• A source endpoint
• A target endpoint
• The list of tables that we want to capture
• Any transformations we want to make on the data

To get started, we need to create a task. Click on the + New Task button at the top of the screen.

Once you do, a window like this will pop up:

Give this task a meaningful name like MySQL to Postgres. For this task we will take the defaults:

• Name: MySQL to Postgres
• Unidirectional
• Full Load: enabled (Blue highlight is enabled; click to enable / disable.)
• Apply Changes: enabled (Blue highlight is enabled; click to enable / disable.)
• Store Changes: disabled (Blue highlight is enabled; click to enable / disable.)

Once you have everything set, press OK to create the task. When you have completed this step you will see a window that looks like this:

Qlik Replicate is all about ease of use. The interface is point-and-click, drag-and-drop. To configure our task, we need to select a source endpoint (MySQL) and a target endpoint (Postgres). You can either drag the MySQL Source endpoint from the box on the left of the screen and drop it into the circle that says Drop source endpoint here, or you can click on the arrow that appears just to the right of the endpoint when you highlight it.

Repeat the same process for the Postgres Target endpoint. Your screen should now look like this:

Our next step is to select the tables we want to replicate from MySQL into Postgres. Click on the Table Selection... button in the top center of your browser.

and from there select the testdrive schema.

Enter % where it says Table: and press the Search button. This will retrieve a list of all the tables in the testdrive schema.

Note: entering % is not strictly required. By default, Qlik Replicate will search for all tables (%) if you do not limit the search.

and then press the >> button to move all of the tables from the Results list into the Selected Tables list. Note that we also had the option of simply wildcarding all tables, or selectively choosing tables from the Results list.

Database-to-Database Replication

Step 4 – Configure a Transformation

The transformation we will create in this section will:

• Add a new column fullName to the Player target table
• Populate that column with values from other columns in the table.

To get started, double click on the Player table in the Selected Tables frame.

Then click on the Transform button.

And click on Add Column.

Call the new column fullName,

and change its default type from STRING(50) to WSTRING(100). You can get a dropdown of valid types you can choose by clicking the type column next to fullName.

Note though, that the default precision of the WSTRING type is 50 … we will need more space than that due to the width of some of the data we will be working with, so you will need to type WSTRING(100) into the type column.

Now we need to define the transformation we want to use to populate the column. Click on fx to bring up the trasformation Expression Builder.

The expression we are going to build will involve concatenating three columns, nameGiven, nameFirst, and nameLast into a column called fullName. Start by double clicking nameGiven which will put $nameGiven into the expression builder.

Now press the concatenation operator || and add the string `’ (‘.

and repeat those steps with nameFirst and nameLast until you have an expression that looks like

$nameGiven||’ (‘||$nameFirst||’) ‘||$nameLast

Note that you could just as easily have typed the the expression in directly, or even copied and pasted it from this web page.

Now click the Parse Expression button and enter the data:

• $nameGiven: first middle
• $nameFirst: nickname
• $nameLast: lastname

and press the Test Expression button. This process allows us to put test values in the columns that make up the expression and validate that the output looks as we expect it to.

Assuming your output looks like the output indicated (first middle (nickname) lastname), your transformation is correct. Press OK to save the transfomration and return to the previous screen.

You will note below that we can see that there is now an expression next to the fullName column. If you hover your mouse over that column you will be able to inspect the transformation without having to click into it. Press OK again to return to the previous screen.

Notice the word (Changed) next to the testdrive.Player column. This indicates that a transformation has been defined that alters the state of the data as it moves from the source to the target.

That is it for configuration. We are now ready to save our task and run it. Press Save at the top left of the window and then press Run.

Database-to-Database Replication

Step 5 – Run Your Task

After you press Run, Replicate will automatically switch from Designer mode to Monitor mode. You will be able to watch the status of the full load as it occurs, and then switch to monitoring change data capture as well.

When Full Load is complete, click on the Completed bar to display the tables. There is DML activity running in the background. Click on the Change Processing tab to see it in action.

If you would like to explore the data that we have delviered to Postgres, click on the folowing link:

This link will open PGAdmin in another window in your browser. Log in with:

• User: pgadmin@qlik.com
• Password: eI3chl835qMRWt2F

From there, click on > Servers in the navigation pane.

And then double-click on Test Drive PostgresDB which will open a window for you to enter the password to log into the server.

• Enter the password: eI3chl835qMRWt2F

This will log you into the database. From there you can navigate to:

Databases => qlik => Schemas => testdrive => Tables

Feel free to explore the structure, etc. associated with the tables we have created. If you look at the structure for the Player table, you will see that the additional column, fullName that we added to the table as a part of our transformation is present in the table here.

Now we will enter a couple of queries. Click on Tools at the top of the screen and select Query Tool to open the query editor.

Now type

select count(*) from testdrive."Player";

Note the quotes around Player and that it begins with a capital ‘P’. The schemas are case-sensitive, so we need the quotes in order to find the table in the query. Highlight that line and press the lightning bolt at the top of the screen to execute. This will give you the count of rows the Player table. That vaule should be displayed in the Data Output area at the bottom of the screen.

Next, we will query a few rows from the Player table to examine the data. Type

select * from testdrive."Player" limit 5;

Once again, highlight that query statement and press the lightning bolt at the top of the screen to execute it. Five rows should be returned in the Data Output area at the bottom of the screen.

If you scroll all the way to the right, you will see the fullName column that we created during the transformation. You will also note that the values set in that column are just as we coded them to look in the transformation.

When you have seen enough, you can declare Victory! for this part of the Test Drive. Press Stop in the top left corner of the Replicate console to end the task. After pressing Stop and clicking Yes in the confirmation dialog, click on the TASKS tab at the top of the screen. This will return you to the main window.

Summary

You just:

• Defined access and authentication into a source and a target database
• Defined the source tables you want to create and keep in sync on the target
• Configured the MySQL to Postgres task
• Configured a transformation that added a new column to the Player table
• Captured initial data from the source without while maintaining business continuity (DML activity was going on in the background to simulate users working on the source database)
• Automatically created the target tables
• Loaded the target tables
• Captured all new transactions which were happening while the initial load was running
• Automatically began the process to apply net new data to the target once the target was loaded
• Observed change data being recorded as it is sent to and applied at the target

All that in 5 easy steps!

You can now move on to the next part of the Test Drive.