tELTTeradataMap
Builds the SQL SELECT statement using the table schema(s) provided by one or more
tELTTeradataInput components.
The three ELT Teradata components are closely related,
in terms of their operating conditions. These components should be used to handle
Teradata database table schemas to generate SQL statements, including clauses, which are
to be executed in the database output table defined.
tELTTeradataMap Standard properties
These properties are used to configure tELTTeradataMap running in the Standard Job framework.
The Standard
tELTTeradataMap component belongs to the ELT family.
The component in this framework is available in all Talend
products.
Basic settings
Use an existing connection |
Select this check box and in the Component List click the relevant connection component to Note: When a Job contains the parent Job and the child Job, if you
need to share an existing connection between the two levels, for example, to share the connection created by the parent Job with the child Job, you have to:
For an example about how to share a database connection |
ELT Teradata Map editor |
The ELT Map editor allows you to define the output schema as well as |
Style link |
Select the way in which links are displayed.
Auto: By default, the links between the
Bezier curve: Links between the schema
Line: Links between the schema and the This option slightly optimizes performance. |
Property type |
Either Built-in or Repository. |
 |
Built-in: No property data stored |
 |
Repository: Select the Repository |
Host |
Database server IP address |
Database |
Name of the database |
Username and Password |
DB user authentication data. To enter the password, click the […] button next to the |
Advanced settings
Query band |
Select this check box to use the Teradata Query Banding feature to add metadata to the query Once selecting the check box, the Query Band parameters This check box actually generates the SET QUERY_BAND FOR SESSION statement with the key/value This check box is not available when you have selected the Using an |
tStatCatcher Statistics |
Select this check box to collect log data at the component |
Global Variables
Global Variables |
ERROR_MESSAGE: the error message generated by the A Flow variable functions during the execution of a component while an After variable To fill up a field or expression with a variable, press Ctrl + For further information about variables, see |
Usage
Usage rule |
tELTTeradataMap is used along with a Note:
The ELT components do not handle actual data flow but only schema |
Dynamic settings |
Click the [+] button to add a row in the table The Dynamic settings table is For examples on using dynamic parameters, see Reading data from databases through context-based dynamic connections and Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic |
Mapping data using a subquery
The sample Job described in this scenario maps the data from two input tables,
PreferredSubject and CourseScore, to the
output table, TotalScoreOfPreferredSubject, using a
subquery.
Prerequisite
Ensure that you have added an Oracle database connection in the Metadata > Db Connections section prior to creating the Job. For more information, see the
Centralizing database metadata section of the Talend Data Integration Studio User
Guide.
The Standard Job and the Prejob design
In this scenario, design the Standard Job such as the following:
preferred subject data. To reproduce this scenario, you can load the following data to
the Oracle table from a CSV
file:
1 2 3 4 |
SeqID;StuName;Subject;Detail 1;Amanda;art;Amanda prefers art. 2;Ford;science;Ford prefers science. 3;Kate;art;Kate prefers art. |
subject score data. To reproduce this scenario, you can load the following data to the
Oracle table from a CSV
file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SeqID;StuName;Subject;Course;Score;Detail 1;Amanda;science;math;85;science score 2;Amanda;science;physics;75;science score 3;Amanda;science;chemistry;80;science score 4;Amanda;art;chinese;85;art score 5;Amanda;art;history;95;art score 6;Amanda;art;geography;80;art score 7;Ford;science;math;95;science score 8;Ford;science;physics;85;science score 9;Ford;science;chemistry;80;science score 10;Ford;art;chinese;75;art score 11;Ford;art;history;80;art score 12;Ford;art;geography;85;art score 13;Kate;science;math;65;science score 14;Kate;science;physics;75;science score 15;Kate;science;chemistry;80;science score 16;Kate;art;chinese;85;art score 17;Kate;art;history;80;art score 18;Kate;art;geography;95;art score |
TotalScoreOfPreferredSubject does not contain any
data:
1 |
SeqID;StuName;PreferredSubject;TotalScore |
Creating the Prejob
Create the
Prejob that contains the data that you wish to load to the Oracle table.
See the Prejob design image in The Standard Job and the Prejob design
section.
-
Create a
Standard Job. -
Add the following components:
- Prejob
- two tFixedFlowInput components
- two tOracleOutput components
- two tOracleInput components
- one tCreateTable component
- two tLogRow components
-
Configure the first tFixedFlowInput component:
-
Select the tFixedFlowInput component to display
the Basic settings view. -
Select Use Inline Content(delimited file) from
the Mode options. -
Add the following data to the Content
field:1231;Amanda;art;Amanda prefers art.2;Ford;science;Ford prefers science.3;Kate;art;Kate prefers art. -
Click … next to the Edit
Schema field to open the Schema
Editor. -
Add four columns with the following names and corresponding
parameters:
-
Select the tFixedFlowInput component to display
-
Configure the second tFixedFlowInput component:
- Repeat steps 3a and 3b.
-
Add the following data to the Content
field:1234567891011121314151617181;Amanda;science;math;85;science score2;Amanda;science;physics;75;science score3;Amanda;science;chemistry;80;science score4;Amanda;art;chinese;85;art score5;Amanda;art;history;95;art score6;Amanda;art;geography;80;art score7;Ford;science;math;95;science score8;Ford;science;physics;85;science score9;Ford;science;chemistry;80;science score10;Ford;art;chinese;75;art score11;Ford;art;history;80;art score12;Ford;art;geography;85;art score13;Kate;science;math;65;science score14;Kate;science;physics;75;science score15;Kate;science;chemistry;80;science score16;Kate;art;chinese;85;art score17;Kate;art;history;80;art score18;Kate;art;geography;95;art score -
Click … next to the Edit
Schema field to open the Schema
Editor. -
Add six columns with the following names and corresponding
parameters:
-
Select the first tOracleOutput component to open the
Basic settings view.-
Select Repository from the Property
Type drop-down list. -
Specify the Oracle database connection the you have previously added by
clicking …. This automatically populates the
database information in the fields provided.
Repeat step 6 and steps 6a-6b to configure the second
tOracleOutput component. -
Select Repository from the Property
-
Select the tCreateTable component to open the
Basic settings view.-
Select Oracle from the Database
Type drop-down list. -
Select Repository from the Property
Type drop-down list. -
Specify the Oracle database connection that you have previously added
by clicking …. This automatically populates the
database information in the fields provided. -
Enter TotalScoreOfPreferredSubject in the
Table Name field. -
Select Drop table if exists and create from the
Table Action drop-down list. -
Click … next to the Edit
schema field to open the Schema
editor. -
Add four columns with the following corresponding names and
parameters:
-
Select Oracle from the Database
Adding the components
-
Add the following components by typing their names in the design
workspace or dropping them from the Palette:- twotELTOracleInput components
- two tELTOracleMap components
- one tELTOracleOutput component
- one tOracleInput
component - one tLogRow component
-
Rename the tELTOracleMap
components to SubqueryMap and ELTMap.
Configuring the input components
- Select the first tELTOracleInput component to display the Basic settings tab.
-
Enter "PreferredSubject" in
the Default Table Name field. -
Click […] next to Edit
schema to define the schema of the input table PreferredSubject in the schema editor. -
Click [+] to
add four columns:- SeqID with the DB Type
set to INTEGER - StuName, Subject, and Detail with the DB Type set to VARCHAR
Click OK to validate these
changes and close the schema editor. - SeqID with the DB Type
- Connect the first tELTOracleInput component to the second tELTOracleMap component using the Link > PreferredSubject(Table).
- Select the second tELTOracleInput component to display the Basic settings tab.
-
Enter "CourseScore" in the
Default Table Name field. -
Click […] next to Edit
schema to define the schema of the input table CourseScore in the schema editor. -
Click the [+] button to add six columns:
- SeqID and Score with the DB Type set to INTEGER
- StuName, Subject, Course, and Detail with
the DB Type set to VARCHAR
Click OK to validate these changes and close the schema editor. -
Connect the second tELTOracleInput component to the first
tELTOracleMap component using the Link > CourseScore(Table).
Configuring the output component
-
Select the tELTOracleOutput component to display the
Basic settings view. -
Enter "TotalScore
OfPreferredSubject" in the Default Table
Name field. -
Click […] next to Edit
schema to define the schema of the output table in the schema
editor. -
Click [+] to
add four columns:- SeqID and TotalScore with the DB Type set to INTEGER
- StuName and PreferredSubject with the DB Type set to
VARCHAR
Click OK to validate these changes and close the schema editor. -
Click Sync columns to
sychronize the Input and Output tables of the
tELTOracleOutput component.
Configuring data mapping to generate a subquery
-
Click the SubqueryMap component (next to the second tELTOracleInput) to open
its Basic settings view.Note: Specify the Oracle database connection information in the second
ELTMap component in the Job. -
Click […] next to ELT Oracle
Map Editor to open its map editor. -
Add the input table CourseScore by clicking [+] in the upper left corner of the map editor and then selecting the
relevant table name from the drop-down list in the pop-up dialog box. -
Add an output table by clicking [+] in the upper right corner of the
map editor and then entering the table name TotalScore in the corresponding field in the pop-up dialog box. -
Drag StuName, Subject, and Score columns in the input table and then drop them to
the output table. -
Click the Add filter row button in the upper right corner of the
output table and select Add an
other(GROUP…) clause from the pop-up menu. Then in the Additional other clauses (GROUP/ORDER
BY…) field displayed, enter the clause GROUP BY CourseScore.StuName, CourseScore.Subject.Add the aggregate function SUM for the column
Score of the output table by changing the
expression of this column to SUM(CourseScore.Score). -
Click the Generated SQL Select query for ‘table1’ output tab at
the bottom of the map editor to display the corresponding generated SQL
statement.This SQL query will appear as a subquery in the
SQL query generated by the ELTMap
component. -
Click OK to validate these changes and close
the map editor. -
Connect the first SubqueryMap to ELTMap using
the Link > TotalScore (table1) link. Note that the link is renamed automatically to TotalScore (Table_ref) since the
output table TotalScore is a reference
table.
Mapping the input and output schemas
- Right-click ELTMap and select Link > *New Output* (Table) from the contextual menu.
-
Click TotalScoreOfPreferredSubject. In the pop-up dialog box, click
Yes to get the schema
from the target component. - Click ELTMap to open its Basic settings view.
-
Select Repository from
the Property Type drop-down list. Specify the Oracle database you previously
added to automatically propagate the database connection information. -
Click […] next to ELT Oracle Map Editor to open its map
editor. -
Add the input table PreferredSubject by clicking the [+] button in the upper left corner of the map
editor and selecting the relevant table name from the drop-down list in the
pop-up dialog box.Repeat the step to add another input table
TotalScore. -
Drag the StuName column in the input table
PreferredSubject and drop it to the corresponding column in the
input table TotalScore. Then select the
Explicit join check
box for the StuName column in the input
table TotalScore.Repeat the step for the Subject column. -
Drag the SeqID column in the input table PreferredSubject and drop it to the corresponding column in the
output table.Repeat the step to drag the StuName and Subject columns in the input table PreferredSubject and the Score column in the input table TotalScore and drop them to the corresponding column in the
output table. -
Click the Generated SQL Select query for “table2” output
tab at the bottom of the map editor to display the corresponding generated SQL
statement.The SQL query generated in the SubqueryMap component appears as a subquery in
the SQL query generated by this component. Alias will be automatically added for
the selected columns in the subquery. -
Click OK to validate these changes and close the map
editor.
Executing the Job
execute the Job.
data are written into the output table.