Scenario 2: ELT using an Alias table

ELT Greenplum

author
Talend Documentation Team
EnrichVersion
6.5
EnrichProdName
Talend MDM Platform
Talend Open Studio for Big Data
Talend Data Fabric
Talend Open Studio for MDM
Talend Big Data Platform
Talend Real-Time Big Data Platform
Talend ESB
Talend Big Data
Talend Data Services Platform
Talend Data Integration
Talend Data Management Platform
Talend Open Studio for Data Integration
Talend Open Studio for ESB
task
Data Quality and Preparation > Third-party systems > ELT components > ELT Greenplum components
Data Governance > Third-party systems > ELT components > ELT Greenplum components
Design and Development > Third-party systems > ELT components > ELT Greenplum components
EnrichPlatform
Talend Studio

This scenario describes a Job that maps information from two input tables and an alias table, serving as a virtual input table, to an output table. The employees table contains employees' IDs, their department numbers, their names, and the IDs of their respective managers. The managers are also considered as employees and hence included in the employees table. The dept table contains the department information. The alias table retrieves the names of the managers from the employees table.

For more technologies supported by Talend, see Talend components.

  • Drop two tELTMysqlInput components, a tELTMysqlMap component, and a tELTMysqlOutput component to the design workspace, and label them to best describe their functionality.

  • Double-click the first tELTMysqlInput component to display its Basic settings view.

  • Select Repository from the Schema list, and define the DB connection and schema by clicking the three dot button preceding Edit schema.

    The DB connection is Talend_MySQL and the schema for the first input component is employees.

Note:

In this use case, all the involved schemas are stored in the Metadata node of the Repository tree view for easy retrieval. For further information concerning metadata, see Talend Studio User Guide.

  • Set the second tELTMysqlInput component in the same way but select dept as its schema.

  • Double-click the tELTMysqlOutput component to display its Basic settings view.

  • Select an action from the Action on data list as needed, Insert in this use case.

  • Select Repository as the schema type, and define the output schema in the same way as you defined the input schemas. In this use case, select result as the output schema, which is the name of the database table used to store the mapping result.

    The output schema contains all the columns of the input schemas plus a ManagerName column.

  • Leave all the other parameters as they are.

  • Connect the two tELTMysqlInput components to the tELTMysqlMap component using Link connections named strictly after the actual input table names, employees and dept in this use case.

  • Connect the tELTMysqlMap component to the tELTMysqlOutput component using a Link connection. When prompted, click Yes to allow the ELT Mapper to retrieve the output table structure from the output schema.

  • Click the tELTMysqlMap component and select the Component tab to display its Basic settings view.

  • Select Repository from the Property Type list, and select the same DB connection that you use for the input components.

    All the DB connection details are automatically retrieved.

  • Leave all the other parameters as they are.

  • Click the three-dot button next to ELT Mysql Map Editor or double-click the tELTMysqlMap component on the design workspace to launch the ELT Map editor.

    With the tELTMysqlMap component connected to the output component, the output table is displayed in the output area.

  • Add the input tables, employees and dept, in the input area by clicking the green plus button and selecting the relevant table names in the [Add a new alias] dialog box.

  • Create an alias table based on the employees table by selecting employees from the Select the table to use list and typing in Managers in the Type in a valid alias field in the [Add a new alias] dialog box.

  • Drop the DeptNo column from the employees table to the dept table.

  • Select the Explicit join check box in front of the DeptNo column of the dept table to set up an inner join.

  • Drop the ManagerID column from the employees table to the ID column of the Managers table.

  • Select the Explicit join check box in front of the ID column of the Managers table and select LEFT OUTER JOIN from the Join list to allow the output rows to contain Null values.

  • Drop all the columns from the employees table to the corresponding columns of the output table.

  • Drop the DeptName and Location columns from the dept table to the corresponding columns of the output table.

  • Drop the Name column from the Managers table to the ManagerName column of the output table.

  • Click on the Generated SQL Select query tab to display the SQL query statement to be executed.

  • Save your Job and press F6 to run it.

    The output database table result contains all the information about the employees, including the names of their respective managers.