tELTMap
Uses the tables provided as input to feed the parameter in the built SQL statement.
The statement can include inner or outer joins to be implemented between tables or
between one table and its aliases.
The three ELT components are closely related, in terms of
their operating conditions. These components should be used to handle DB schemas to
generate Insert statements, including clauses, which are to be executed in the DB output
table defined.
Note that it is highly recommended to use
the ELT components for a specific type of database (if any) instead of the ELT
components. For example, for Teradata, it is recommended to use the tELTTeradataInput, tELTTeradataMap and tELTTeradataOutput components instead.
tELTMap Standard properties
These properties are used to configure tELTMap running in the
Standard Job framework.
The Standard
tELTMap 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 Map Editor |
The ELT Map editor allows you to define the output schema and |
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.
|
JDBC URL |
The JDBC URL of the database to be used. For |
Driver JAR |
Complete this table to load the driver JARs needed. To do |
Class name |
Enter the class name for the specified driver between double |
Username and Password |
DB user authentication data. To enter the password, click the […] button next to the |
Mapping |
Specify the metadata mapping file |
Advanced settings
Additional JDBC parameters |
Specify additional connection properties for the DB connection you are |
tStatCatcher Statistics |
Select this check box to gather the Job processing metadata at a Job |
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 |
tELTMap is used along with tELTInput and tELTOutput. Note that the Output link to be used with these Note:
Note that the ELT components do not handle actual data flow but |
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 |
Aggregating Snowflake data using context variables as table and connection names
two source tables STUDENT and TEACHER to one target table FULLINFO
using the ELT components. In this example, set all input and output table names and connection
names to context variables.
Creating the Job

-
A new Job has been created and the context variables
SourceTableS with the value
STUDENT, SourceTableT with
the value TEACHER, and
TargetTable with the value
FULLINFO have been added to the Job. For more
information about how to use context variables, see the related
documentation about using contexts and variables. -
The source table STUDENT with three columns,
SID and TID of
NUMBER(38,0) type and SNAME of VARCHAR(50) type, has
been created in Snowflake, and the following data has been written into the
table.12345678910#SID;SNAME;TID11;Alex;2212;Mark;2313;Stephane;2114;Cedric;2215;Bill;2116;Jack;2317;John;2218;Andrew;23 -
The source table TEACHER with three columns,
TID of NUMBER(38,0) type and
TNAME and TPHONE of
VARCHAR(50) type, has been created in Snowflake, and the following data has
been written into the table.12345#TID;TNAME;TPHONE21;Peter;+86 1581234345622;Michael;+86 1317896453223;Candice;+86 13923187456
-
Add a tSnowflakeConnection component, a
tSnowflakeClose component, two
tELTInput components, a
tELTMap component, and a
tELTOutput component to your Job. -
On the Basic setting view of the first
tELTInput component, enter the name of the first
source table in the Default Table Name field. In this
example, it is the context variable
context.SourceTableS. -
Repeat step 2 to set the value of the default table name for
the second tELTInput component and the
tELTOutput component to context.SourceTableT and context.TargetTable respectively. -
Link the first tELTInput component to the
tELTMap component using the Link > context.SourceTableS (Table) connection. -
Link the second tELTInput component to the
tELTMap component using the Link > context.SourceTableT (Table) connection. -
Link the tELTMap component to the
tELTOutput component using the Link > *New Output* (Table) connection. The link will be renamed automatically to
context.TargetTable (Table). -
Link the tSnowflakeConnection component to the
tELTMap component using a Trigger > On Subjob Ok connection. -
Link the tELTMap
component to the tSnowflakeClose
component.
Connecting to Snowflake
Snowflake.
- Double-click the tSnowflakeConnection component to open its Basic settings view.
-
In the Account field, enter
the account name assigned by Snowflake. -
In the Snowflake Region field, select the region where the
Snowflake database locates. -
In the User Id and the
Password fields, enter the authentication
information accordingly.Note that this user ID is your user login name. If you do not know your user login
name yet, ask the administrator of your Snowflake system for details. -
In the Warehouse field,
enter the name of the data warehouse to be used in Snowflake. -
In the Schema field, enter
the name of the database schema to be used. -
In the Database field, enter
the name of the database to be used.
Configuring the input components
-
Double-click the first tELTInput component to open its Basic
settings view. -
Click the […] button
next to Edit schema and in the schema
dialog box displayed, define the schema by adding three columns, SID and TID of INT type and SNAME of VARCHAR type. -
Select Mapping Snowflake
from the Mapping drop-down list. -
Repeat the previous steps to configure the second tELTInput component, and define its schema by
adding three columns, TID of INT type and
TNAME and TPHONE of VARCHAR type.
Configuring the output component
- Double-click the tELTOutput component to open the Basic settings view.
-
Select Create table from the Action on
table drop-down list to create the target table. -
Select the Table name from connection name is variable
check box. -
Select Mapping Snowflake from the
Mapping drop-down list.
Configuring the map component for aggregating Snowflake data
-
Click the tELTMap component to open its Basic
settings view. -
Select the Use an existing connection check box and from
the Component List displayed, select the connection
component you have configured to open the Snowflake connection. -
Select Mapping Snowflake from the
Mapping drop-down list. -
Click the […] button next to ELT Map
Editor to open its map editor. -
Add the first input table context.SourceTableS 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. -
Do the same to add the second input table
context.SourceTableT. -
Drag the column TID from the first input table
context.SourceTableS and drop it onto the
corresponding column TID in the second input table
context.SourceTableT. -
Drag all columns from the input table
context.SourceTableS and drop them onto the output
table context.TargetTable in the upper right panel. -
Do the same to drag two columns TNAME and
TPHONE from the input table
context.SourceTableT and drop them onto the bottom of
the output table. When done, click OK to close the map
editor. -
Click the Sync columns button on the Basic
settings view of the tELTOutput component
to set its schema.
Closing the Snowflake connection
connection to Snowflake.
-
Double-click the tSnowflakeClose
component to open the Component
tab. -
From the Connection Component drop-down list, select the
component that opens the connection you need to close,
tSnowflakeConnection_1 in this example.
Executing the Job
- Press Ctrl + S to save the Job.
-
Press F6 to execute the Job.
As shown above, Talend Studio
executes the Job successfully and inserts eight rows into the target
table.You can then create and run another Job to retrieve data from
the target table by using the tSnowflakeInput component and the tLogRow component. You will find that the
aggregated data are displayed on the console as shown in below
screenshot.For more information about how to retrieve data from
Snowflake, see Writing data into and reading data from a Snowflake table.
Related scenarios
-
Mapping data using a subquery, a related scenario using subquery