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 generally available.
Basic settings
Use an existing connection |
Select this check box and in the Component 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 across Job levels, see |
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 The Dynamic settings table is For examples on using dynamic parameters, see Scenario: Reading data from databases through context-based dynamic connections and Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic |
Scenario: Mapping data using a subquery
This scenario describes a Job that maps the data from two input tables PreferredSubject and CourseScore to the output table TotalScoreOfPreferredSubject using a subquery.

preferred subject data. To reproduce this scenario, you can load the data to the table from
a CSV file like the following. For how to load data to a Teradata table, see Scenario: Loading data into a Teradata database.
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. |
score data. To reproduce this scenario, you can load the data to the table from a CSV file
like the following. For how to load data to a Teradata table, see Scenario: Loading data into a Teradata database.
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 |
1 |
SeqID;StuName;PreferredSubject;TotalScore |
Dropping and renaming the components
-
Create a new Job and add the following components by typing their names in the
design workspace or dropping them from the Palette: two tELTTeradataInput
components, two tELTTeradataMap components, and
one tELTTeradataOutput component. -
Rename two tELTTeradataInput components to
PreferredSubject and CourseScore respectively, two tELTTeradataMap components to ELTSubqueryMap and ELTMap
respectively, and the tELTTeradataOutput
component to TotalScoreOfPreferredSubject.
Configuring the input components
-
Double-click PreferredSubject to open its
Basic settings view. -
In the Default Table Name field, enter an
input table name. In this example, it is PreferredSubject. -
Click the […] button next to Edit schema to define the schema of the input table
PreferredSubject in the schema
editor.Click the [+] button 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. -
Connect PreferredSubject to ELTMap using the Link > PreferredSubject
(Table) link. -
Double-click CourseScore to open its
Basic settings view. -
In the Default Table Name field, enter an
input table name. In this example, it is CourseScore. -
Click the […] button 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 CourseScore to ELTSubqueryMap using the Link > CourseScore (Table)
link.
Configuring the output component
-
Double-click TotalScoreOfPreferredSubject
to open its Basic settings view. -
In the Default Table Name field, enter an
output table name. In this example, it is TotalScoreOfPreferredSubject. -
Click the […] button next to Edit schema to define the schema of the output table
in the schema editor.Click the [+] button 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.
Configuring data mapping to generate a subquery
-
Click ELTSubqueryMap to open its Basic settings view.
Note that you do not need to specify the Teradata database connection
information in the ELTSubqueryMap component.
The connection information will be specified in the ELTMap component. -
Click the […] button next to ELT Teradata Map Editor to open its map
editor. -
Add the input table CourseScore by clicking
the [+] button 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 the [+]
button 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 ELTSubqueryMap to ELTMap using the Link > TotalScore (table1)
link. Note that the link is renamed automatically to TotalScore (Table_ref) since the output table TotalScore here is a reference table.
Mapping the input and output schemas
-
Right-click ELTMap, select Link > *New Output*
(Table) from the contextual menu and 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.
Fill in the Host, Database, Username, and
Password fields with the Teradata database
connection information. -
Click the […] button next to ELT Teradata 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.Do the same 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.Do the same for the Subject column. -
Drag the SeqID column in the input table
PreferredSubject and drop it to the
corresponding column in the output table.Do the same 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 ELTSubqueryMap 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
- Press Ctrl + S to save the Job.
-
Press F6 to run the Job.
The select statement is generated and the mapping data are written into the
output table.