A use case of system SQL templates

Talend Data Management Platform Studio User Guide

EnrichVersion
6.2
EnrichProdName
Talend Data Management Platform
task
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

As there are many common, standardized SQL statements, Talend Studio allows you to benefit from various system SQL templates.

This section presents you with a use case that takes you through the steps of using MySQL system templates in a Job that:

  • opens a connection to a Mysql database.

  • collects data grouped by specific value(s) from a database table and writes aggregated data in a target database table.

  • deletes the source table where the aggregated data comes from.

  • reads the target database table and lists the Job execution result.

To connect to the database and aggregate the database table columns:

Configuring a connection to a MySQL database

  1. Drop the following components from the Palette onto the design workspace: tMysqlConnection, tSQLTemplateAggregate, tSQLTemplateCommit, tMysqlInput, and tLogRow.

  2. Link tMysqlConnection to tSQLTemplateAggregate using a Trigger > On Subjob Ok connection.

  3. Do the same to link tSQLTemplateAggregate to tSQLTemplateCommit and link tSQLTemplateCommit to tMysqlInput.

  4. Link tMysqlInput to tLogRow using a Row > Main connection.

  5. Double-click tMysqlConnection to open its Basic settings view.

  6. In the Basic settings view, set the database connection details manually.

  7. Double-click tSQLTemplateCommit to open its Basic settings view.

  8. On the Database Type list, select the relevant database type, and from the Component List, select the relevant database connection component if more than one connection is used.

Grouping data, writing aggregated data and dropping the source table

  1. Double-click tSQLTemplateAggregate to open its Basic settings view.

  2. On the Database Type list, select the relevant database type, and from the Component List, select the relevant database connection component if more than one connection is used.

  3. Enter the names for the database, source table, and target table in the corresponding fields and define the data structure in the source and target tables.

    The source table schema consists of three columns: First_Name, Last_Name and Country. The target table schema consists of two columns: country and total. In this example, we want to group citizens by their nationalities and count citizen number in each country. To do that, we define the Operations and Group by parameters accordingly.

  4. In the Operations table, click the [+] button to add one or more lines, and then click the Output column cell and select the output column that will hold the counted data from the drop-down list.

  5. Click the Function cell and select the operation to be carried on from the drop-down list.

  6. In the Group by table, click the [+] button to add one or more lines, and then click the Output column cell and select the output column that will hold the aggregated data from the drop-down list.

  7. Click the SQL Template tab to open the corresponding view.

  8. Click the [+] button twice under the SQL Template List table to add two SQL templates.

  9. Click on the first SQL template row and select the MySQLAggregate template from the drop-down list. This template generates the code to aggregate data according to the configuration in the Basic settings view.

  10. Do the same to select the MySQLDropSourceTable template for the second SQL template row. This template generates the code to delete the source table where the data to be aggregated comes from.

    Note

    To add new SQL templates to an ELT component for execution, you can simply drop the templates of your choice either onto the component in the design workspace, or onto the component's SQL Template List table.

    Note

    The templates set up in the SQL Template List table have priority over the parameters set in the Basic settings view and are executed in a top-down order. So in this use case, if you select MySQLDropSourceTable for the first template row and MySQLAggregate for the second template row, the source table will be deleted prior to aggregation, meaning that nothing will be aggregated.

Reading the target database and listing the Job execution result

  1. Double-click tMysqlInput to open its Basic settings view.

  2. Select the Use an existing connection check box to use the database connection that you have defined on the tMysqlConnection component.

  3. To define the schema, select Repository and then click the [...] button to choose the database table whose schema is used. In this example, the target table holding the aggregated data is selected.

  4. In the Table Name field, type in the name of the table you want to query. In this example, the table is the one holding the aggregated data.

  5. In the Query area, enter the query statement to select the columns to be displayed.

  6. Save your Job and press F6 to execute it.

    The source table is deleted.

    A two-column table citizencount is created in the database. It groups citizens according to their nationalities and gives their total count in each country.