ELT and ETL are two different approaches for looking into data movement.
ELT
-Extract Load Transform
-Data is copied in the target staging without transformations
-Reduces load time as no transformation is applied while loading
-Preferred when you have high end database engine at Target
ETL
-Extract Transform Load
-Data is first transformed and then gets loaded into Target
-Load time is bit more than than that of ELT
-Preferred when you have small dataset
Talend Open Studio for Data Integration comes with ELT Component which supports multiple db connections.
ELT Component in Talend supports us to :
Problem Statement : Perform Self Join of table to find Product Category Name of each product.
This example uses tELTOracleMap component for demonstration.
Here we are using 3 ELT components Of Talend Open Studio which are closely related.
We have sample table PRODUCT_DETAILS with data:
PRODUCT_ID PRODUCT NAME PARENT_PRODUCT_ID
1 Salted Product NULL
2 All-Seasons Salt 1
3 Coarse Grind Garlic Powder null Blend 1
4 Nature Seasons Seasoning Blend 1
5 Chocolate Sandwich Cookies 10
. . .
. . .
23 Organic Spicy Heirloom Marinara Sauce 24
24 Sauce NULL
STEPS :
ELT
-Extract Load Transform
-Data is copied in the target staging without transformations
-Reduces load time as no transformation is applied while loading
-Preferred when you have high end database engine at Target
ETL
-Extract Transform Load
-Data is first transformed and then gets loaded into Target
-Load time is bit more than than that of ELT
-Preferred when you have small dataset
Talend Open Studio for Data Integration comes with ELT Component which supports multiple db connections.
ELT Component in Talend supports us to :
- Define alias for tables
- Provides all types of JOINS options
- Specify joining key/column
- Add WHERE clause, GROUP BY clause
- Preview generated SQL statement
Problem Statement : Perform Self Join of table to find Product Category Name of each product.
This example uses tELTOracleMap component for demonstration.
Here we are using 3 ELT components Of Talend Open Studio which are closely related.
- tELTOracleInput
- tELTOracleMap
- tELTOracleOutput
 |
| ELT Component Demo Job |
We have sample table PRODUCT_DETAILS with data:
PRODUCT_ID PRODUCT NAME PARENT_PRODUCT_ID
1 Salted Product NULL
2 All-Seasons Salt 1
3 Coarse Grind Garlic Powder null Blend 1
4 Nature Seasons Seasoning Blend 1
5 Chocolate Sandwich Cookies 10
. . .
. . .
23 Organic Spicy Heirloom Marinara Sauce 24
24 Sauce NULL
STEPS :
- Drag tELTOracleInput component and fill input table details. Notice that there are no db connection details at tOracleInput.
- Drag tELTOracleMap component. Give connection details of db under component tab of tELTOracleMap.
 |
| tELTOracleMap connection details |
- After that, double click to open ELT Oracle Map Editor. Click on add alias + button. Mention table and it's alias. This component allows to join multiple tables along with its multiple aliases. Here I have mentioned 2 aliases for the purpose of self join.
- Mention the joining key and joining condition by selecting operator.
- Select the type of join. Here type of join is left outer join.
 |
| tELTOracleMap Editor |
- Add output table + on right side. Drag columns into output table. On upper right you have option to add WHERE / GROUP BY clause.
- I have filtered rows where PRODUCT_NAME is null. You can preview the genarated sql statement below the mapping.
- Drag tELTOracleOutput and fill input table details. Connect tELTOracleMap to tELTOracleOutput.
- Run the job.
Output :
PRODUCT_ID PRODUCT_NAME PRODUCT_CATEGORY
3 Coarse Grind Garlic Powder null Blend Salted Product
2 All-Seasons Salt Salted Product
9 Mini Nilla Wafers Munch Pack Chocolate Products
8 Gluten Free All Natural Chocolate Chip Cookies Chocolate Products
. . .
. . .
11 Very Vanilla Soymilk Vanilla
17 Chicken Nugget Meal Chicken
No comments:
Post a Comment