Generating a SQL statement from two tables - Cloud - 8.0

ELT JDBC

Version
Cloud
8.0
Language
English
Product
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 Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > ELT components > ELT JDBC components
Data Quality and Preparation > Third-party systems > ELT components > ELT JDBC components
Design and Development > Third-party systems > ELT components > ELT JDBC components
Last publication date
2024-02-20
This article introduces a sample Job that generates a SQL statement from two tables using the tELTInput, tELTMap, and the tELTOutput components and then displays the generated SQL statement using tWarn.

To generate a SQL statement from multiple database tables, connect multiple tELTInput components to a tELTMap component as the input of the tELTMap component, with a database table specified in each of the tELTInput component; the tELTMap component is then connected to a tELTOutput component. You need to define table column mapping and mapping conditions in the ELT map editor of the tELTMap component, which are passed to the tELTOutput component as the output. The tELTOutput component generates the SQL statement according to the output of the tELTMap component.

The key settings of the components in this Job are as follows.

  • For tELTInput_1, Table_1 is specified as the input table. The table has three DECIMAL-type columns: COL1, COL2, and COL3.
  • For tELTInput_2, Table_2 is specified as the input table. The table has two DECIMAL-type columns: COLUMN1 and COLUMN2.
  • For tELTMap_1, the mapping among the table columns is set in the ELT map editor of the component, as shown in the following figure.
  • For tELTOutput_1, the key settings are shown in the following figure (pay attention to those in red frames).
    With all the above settings, the result SQL statement will be the one listed below. The result SQL statement will be stored in the QUERY variable of tELTOutput_1.
    INSERT INTO PUBLIC.Table_3(COL01,COL02,COL03,COL04)(
       SELECT Table_1.COL1, Table_1.COL2, Table_1.COL3, Table_2.COLUMN2
          FROM Table_1 INNER JOIN  Table_2 ON(Table_2.COLUMN1 = Table_1.COL1))
    Note: The actual SQL statement generated is subject to change without prior notice.
  • The tWarn_1 component displays the SQL statement generated in the console. To achieve this, enter ((String)globalMap.get("tELTOutput_1_QUERY")) in the Warn message field and optionally select Info from the Priority drop-down list.