Configuring the components - 7.3

ELT Oracle
Version
7.3
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > ELT components > ELT Oracle components
Data Quality and Preparation > Third-party systems > ELT components > ELT Oracle components
Design and Development > Third-party systems > ELT components > ELT Oracle components
Last publication date
2024-02-21

Procedure

  1. Double-click the tELTOracleInput component to display its Basic settings view.
  2. Select Repository from the Schema list and click the [...] button preceding Edit schema.
  3. Select your database connection and the desired schema from the Repository Content dialog box.
    The selected schema name appears in the Default Table Name field automatically.
    • In this use case, the database connection is Talend_Oracle and the schema is new_customers.
    • In this use case, the input schema is stored in the Metadata node of the Repository tree view for easy retrieval. For further information concerning metadata, see Talend Studio User Guide.
    • You can also select the input component by dropping the relevant schema from the Metadata area onto the design workspace and double-clicking tELTOracleInput from the Components dialog box. Doing so allows you to skip the steps of labeling the input component and defining its schema manually.
  4. Click the tELTOracleMap component to display its Basic settings view.
  5. Select Repository from the Property Type list, and select the same database connection that you use for the input components.
    Remember: All the database details are automatically retrieved. Leave the other settings as they are.
  6. Double-click the tELTOracleMap component to launch the ELT Map editor for setting up the data transformation flow.
    Add the input table by clicking the [+] button on the upper left corner of the ELT Map editor and selecting the relevant table name in the pop-up dialog box.
    In this use case, the only input table is new_customers.
  7. Select all the columns in the input table and drop them to the output table.
  8. Click the Generated SQL Select query tab to display the query statement to be executed.
    Click OK to validate the ELT Map settings and close the ELT Map editor.
  9. Double-click the tELTOracleOutput component to display its Basic settings view.
    1. From the Action on data list, select MERGE.
    2. Click the Sync columns button to retrieve the schema from the preceding component.
    3. Select the Use Merge Update check box to update the data using Oracle's MERGE function.
  10. In the table that appears, select the check boxes for the columns you want to update.
    In this use case, youupdate all the data according to the customer ID. Therefore, select all the check boxes except the one for the ID column.
    Warning: The columns defined as the primary key cannot and must not be made subject to updates.
  11. Select the Use Merge Insert check box to insert new data while updating the existing data by leveraging the OracleMERGE function.
  12. In the table that appears, select the check boxes for the columns into which you want to insert new data.
    In this use case, insert all the new customer data. Therefore, select all the check boxes by clicking the Check All check box.
  13. Fill the Default Table Name field with the name of the target table already existing in your database. In this example, fill in customers_merge.
  14. Leave the other parameters as they are.