Updating Oracle DB entries - 6.4

ELT Oracle

author
Talend Documentation Team
EnrichVersion
6.4
EnrichProdName
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 Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
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
EnrichPlatform
Talend Studio

This scenario is based on the data aggregation scenario, Aggregating table columns and filtering. As the data update action is available in Oracle DB, this scenario describes a Job that updates particular data in the agg_result table.

For more technologies supported by Talend, see Talend components.

  • As described in Aggregating table columns and filtering, set up a Job for data aggregation using the corresponding ELT components for Oracle DB, tELTOracleInput, tELTOracleMap, and tELTOracleOutput, and execute the Job to save the aggregation result in a database table named agg_result.

Note:

When defining filters in the ELT Map editor, note that strings are case sensitive in Oracle DB.

  • Launch the ELT Map editor and add a new output table named update_data.

  • Add a filter row to the update_data table to set up a relationship between input and output tables: owners.ID_OWNER = agg_result.ID_OWNER.

  • Drop the MAKE column from the cars table to the update_data table.

  • Drop the NAME_RESELLER column from the resellers table to the update_data table.

  • Add a model enclosed in single quotation marks, A8 in this use case, to the MAKE column from the cars table, preceded by a double pipe.

  • Add Sold by enclosed in single quotation marks in front of the NAME_RESELLER column from the resellers table, with a double pipe in between.

  • Check the Generated SQL select query tab to be executed.

  • Click OK to validate the changes in the ELT Mapper.

  • Deactivate the tELTOracleOutput component labeled Agg_Result by right-clicking it and selecting Deactivate Agg_Result from the contextual menu.

  • Drop a new tELTOracleOutput component from the Palette to the design workspace, and label it Update_Data to better identify its functionality.

  • Connect the tELTOracleMap component to the new tELTOracleOutput component using the link corresponding to the new output table defined in the ELT Mapper, update_data in this use case.

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

  • From the Action on data list, select Update.

  • Check the schema, and click Sync columns to retrieve the schema structure from the preceding component if necessary.

  • In the WHERE clauses area, add a clause that reads agg_result.MAKE = 'Audi' to update data relating to the make of Audi in the database table agg_result.

  • Fill the Default Table Name field with the name of the output link, update_data in this use case.

  • Select the Use different table name check box, and fill the Table name field with the name of the database table to be updated, agg_result in this use case.

  • Leave the other parameters as they are.

  • Save your Job and press F6 to run it.

    The relevant data in the database table is updated as defined.