Step 3: Creating and loading view into a final reporting table

Change Data Capture

author
Talend Documentation Team
EnrichVersion
6.4
EnrichProdName
Talend Data Services Platform
Talend Data Integration
Talend Data Fabric
Talend Big Data
Talend Big Data Platform
Talend Data Management Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
task
Data Quality and Preparation > Third-party systems > Database components > Change Data Capture
Data Governance > Third-party systems > Database components > Change Data Capture
Design and Development > Third-party systems > Database components > Change Data Capture
EnrichPlatform
Talend Studio
In this step, a view created as on the top of the base table employee and the external table employee_extnl, so that only the latest data is shown. This view would need to be then loaded into a final reporting table which will be accessed by the subscribers of the data.

Procedure

Use the following SQL:
CREATE VIEW employee_view AS

SELECT t1.* FROM

 

(SELECT * FROM employee

    UNION ALL

    SELECT * FROM employee_extnl) t1

JOIN

    (SELECT employeeid, max(record_datetime) max_modified FROM

        (SELECT * FROM employee

        UNION ALL

        SELECT * FROM employee_extnl) t2

    GROUP BY employeeid) s

ON t1.employeeid = s.employeeid AND t1.record_datetime = s.max_modified;

The results from the view are as follow:

The view generates only the latest data.