Data Connection and Virtualization
Last updated
Was this helpful?
Last updated
Was this helpful?
This section will cover aspects of collecting data in Cloud Pak for Data. Specifically we will be connecting to different data sources and creating views against those data sources to create a single unified set of data assets that can be used in other modules of this workshop.
Note: To complete this section, an Admin or Data Engineer role needs to be assigned to your user account. The workshop instructor will assign this role as appropriate.
In this section, we will gather data from several tables across data sources. We will use data virtualization to access these tables and then create joined views against those virtualized tables.
To launch the data virtualization tool, go the (☰) navigation menu and under the Data section click on Data virtualization.
From the Data virtualization sub-menu at the top left of the page, click on the menu drop down list and choose Virtualize.
Several tables names will be displayed across any of the data sources that are included in the data virtualization server. You will notice that on the top of the panel, we can filter the tables being displayed by selecting the database type.
To simplify the search for tables you will use, click on the Schemas column header to sort the tables by Schema. Then find the tables we will be using for this workshop: APPLICANTFINANCIALDATA, APPLICANTPERSONALDATA and LOANS, which are under the CP4DCREDIT schema. Select the checkboxes next to these three tables, and then click on Add to cart followed by the View Cart button.
Note: You may need to page through the available tables by clicking on the right arrow at the bottom of the tables view. Or changing the number of
Items per pageto a larger value at the bottom left of the tables view.
The next panel prompts you to select where to assign the virtualized tables. Select the My virtualized data radio button and click the Virtualize button to add the virtualized tables to your data (we left the default values, so the tables will be virtualized under your own user schema with the same table names as the original tables).
A pop up dialog panel will indicate that the virtual tables have been created. Let's see the new virtualized tables by clicking the View my virtualized data button.
Note: You may receive a notification at the top of the page that the virtual assets were published to the catalog. Feel free to dismiss the notification by clicking on the
X
Now we're going to join the tables we previously virtualized, so we have a final merged set of data. It will be easier to do it here rather than in a notebook where we'd have to write code to handle three different data sets.
From the 'My virtualized data' page, Click on two of the virtualized tables (APPLICANTPERSONALDATA and APPLICANTFINANCIALDATA) and click the Join button.
To join the tables we need to pick a key that is common to both data sets. Here we choose to map CustomerID from the first table to CustomerID on the second table. Do this by clicking on one and dragging it to another. We will leave the default of having all the column names selected. When the line is drawn, click on the Next button.
In the next panel, although we could change the names of our columns, we will accept the existing names for our columns. Click the Next button to continue.
In the next panel we'll give our joined data view a unique name (to be consistent with SQL standards, pick an all uppercase name), choose someething like: XXXAPPLICANTFINANCIALPERSONALDATA (where XXX is your initials in all upper case). Also select the My virtualized data radio button and then click the Create view button to add the virtualized aggregate view to your data.
A pop up dialog panel will indicate that the join view creation has succeeded! Click on View my virutalized data button.
Repeat the same steps as above, but this time choose to join the new joined view you just created (XXXAPPLICANTFINANCIALPERSONALDATA) and the last virtualized table (LOANS), to create a new joined view that has all three tables. Click the Join button.
Again join the two tables by selecting/mapping the CustomerID from the first table to CustomerID on the second table. Do this by clicking on one and dragging it to another. We will leave the default of having all the column names selected. When the line is drawn, click on the Next button.
In the next panel, although we could change the names of our columns, we will accept the existing names for our columns. Click the Next button to continue.
In the next panel we'll give our joined data view a unique name (to be consistent with SQL standards, pick an all uppercase name), choose someething like: XXXAPPLICANTFINANCIALPERSONALLOANSDATA (where XXX is your initials in all upper case). Also select the My virtualized data radio button and then click the Create view button to add the virtualized aggregate view to your data.
A pop up dialog panel will indicate that the join view creation has succeeded! Click on View my virtualized data button.
From the My virtualized data page you should now see all three virtualized tables and two joined tables. Do not go to the next section until you have all the tables.
In this section we learned how to make connection to databases that contain our data, how to virtualize them, and how to use the virtualized data.
Remember that you can add data from different databases and servers if you need to. Moreover, you can virtualized these data from different sources together as well! The goal is to take care of bringing the data to the platform early on so all the data scientists can use it without reinventing the wheel while you keep full control of who has access to what data.
