Open Studio for Big Data
Open Studio for Data Integration
Open Studio for ESB
Double-click tSQLTemplateAggregate to open
its Basic settings view.
- 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.
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
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.
- 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.
- Click the Function cell and select the operation to be carried on from the drop-down list.
- 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.
Click the SQL Template tab to open the
- Click the [+] button twice under the SQL Template List table to add two SQL templates.
- 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.
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.
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.